Share via

SSE and Visual Studio ** Xcopy Mindshift **

  • Article

Adding databases with SSE (SQL Server Express) to Visual Studio

At the announcment for Visual Studio and SSE, they talked about the Xcopy functionality. I should explain a bit about what this means in practical terms for Visual Studio. Most everyone is used to creating a new database by going to “Add new database“ from the Server Explorer. This approach will still work. (Although you need to use the instance name of SQLExpress. [So, type “.\SQLExpress“ for the server name.] and there is a bug in the WebExpress SKU where this doesn't work. But, it does work in the other SKUs])

In fact, if you see something like this:

“Visual Web Developer 2005 Express Edition Beta, This feature is not supported
by Microsoft Visual Web Developer Express“

it's probably because you didn't specify the “.\SQLExpress” instance name.

But, this isn't the preferred way. The preferred way is to simply copy the database into your project. So, from the project menu, right click and choose “Add new item“ or “Add existing item“. In either case the MDF (and LDF if present) is copied into your project. Then a connection is automatically opened for you in Server Explorer. So, you can directly party on the database structure.

SSE supports a connection style where you do not specify the logical database name. You simply leave it blank in the connection string and one is generated for you automatically on the fly (based on the unique path of the MDF in the directory structure). When your app closes down (app domain), the MDF is auto-closed which leaves the MDF in a copyable state.

So, you can build your app and directly reference the MDF that will be in the same directory as your .exe. When you hit F5, the project system builds your .exe/dlls and copies the MDF to the output directory. When your app runs, it directly references the MDF that is in same directory as your exe (or relative to it). When your app spins down and the app domain closes, both your executables and your database are Xcopyable (mailable, zippable, whatever ...) to the destination of your choice.

Note that when you debug, the same thing happens. The exes are built in the debug directory and the MDF is copied to the debug directory as well. So, when you are debugging your app, it's hitting the version of the MDF that's in the debug directory. There is an option in the project system to *not* keep copying the MDF to the output dir each time that you can configure. But, you need it there at least on the first build so that the app can reference it as it will when it's deployed.

When you deploy, you have two options. 1.) You can Xcopy. And, you should Xcopy and pick up both the MDF and LDF files. The VS project system does some work to manage the LDF for you automatically behind the scenes. and 2.) you can use the explicit deploy features. Note that you have the option of bundling up both the framework and SSE with your application.

Anyway, thought I'd better get this out on a blog so that people can see how to use SSE in the “file style“ format and so that they know about the SQLExpress instance name.

Lance

Comments

  • Anonymous
    June 29, 2004
    Very nice post explaining the x-copy options in SQL Server Express

  • Anonymous
    June 29, 2004
    Yet more Express info, the tools view

  • Anonymous
    June 30, 2004
    what happens when you xcopy an application across to a machine that doesn't have Sql Server Express (or its big brother) installed?

    I'm guessing the app dies a horrible death with a Database/DSN not found type error?

  • Anonymous
    June 30, 2004
    The comment has been removed

  • Anonymous
    June 30, 2004
    The comment has been removed

  • Anonymous
    June 30, 2004
    A database product to allow Application to enable the "XCopy" secnario may be a gold mine to one but trash to the other.

    "XCopy" is just a term (probably not a good one), don't limit your imagination on just the local machine. "XCopy" also mean that it is "downloadable", "moveable", "disconnectable"....

    As we approach to information explosion stage. Many application secnario is no longer limit to local machine only.

    I can give you the one case study: the following link is to a Fidlity Active Traider program

    http://personal.fidelity.com/global/search/resultsindex.shtml?quser=Active+Trader

    Fidelity.com provide a greate service to all their customer a program which can almost real-time to monitor the invester's account stock, fund, watchlist and etc.

    To achieve this goal, consider most customer have only modem, DSL, Cable modem at from home. The issue is to constantly downloading a tons of data to the client machine become unpractical. Second, a "typical" investor will only constantly track limit amount of stock/fund/watch list they currently care. Which make the data a invester need become pretty local.

    For example, if he/she invest at MSFT stock, today he logon, he will see MSFT 4 month of stock movement information. The next day he/she login again, he still want to see 4 month accmulate stock information. What the difference between the yesterday's MSFT 4 months stock informtion vs today MSFT 4 months stock information --- it should only need 1 more day informtions not to download everything again just to jam up the bandwidth. This is not only apply to stock information, but also the customer's investment profile, investment history,etc.

    Additionally The customer Account information, invest profile, investment history, etc.. should be store "securly" and only one person should can see and use it.

    In addition. customer like to have a crash proof database that can hold such information locally (say even if he had problem to connect to internet) he can still see his all investment information up to last download.

    Only he should have the access to this "personal" data...

    All lead to the same direction, you need a "downloadable" database (see the download button on the above link?) which will achieve all the requirement analysis above here.

    So the Sever side feed the inforamtion, client cache up on the "local" personal database with great security and only download on daily basis.

    As I mention before, "XCopy" is just a term, don't limit your imagination on just the local machine. "XCopy" also mean that it can be "downloadable", "moveable", "disconnectable".... As the example here for a web application that it can be a client side assistant. That's what Lance point out -- the "mind shift" needed to open lot of possibility.....

    - my 2 + 10 cents

    -Ming

    PS. Highly recommand Fidelity Active Trader program. I use it and love it. If you are a Fidelity customer but doesn't have it -- you miss a great service they have.

  • Anonymous
    June 30, 2004
    Sorry, the correct link To Fidelity Active Traider is
    http://personal.fidelity.com/accounts/activetrader/content/atwintro.shtml

    -Ming

  • Anonymous
    July 01, 2004
    The Xcopy scenarios are very useful for the quick start scenarios. Those who are just learning and those who want something quick and dirty. The Xcopy connection style is useful well beyond these scenarios as well. In Visual Studio, it's a "no think" deployment scenario. Just add new or existing items to your project and you're good to go. The connection is added to your app, and the connection is ready in Server Explorer.

    In general, Xcopy will take less thinking time to use, period. The user doesn't even need to see the connection string. (Note: SSE is not supported on Win9x)

    Also, the MDF is copied, by default, to the same dir as the .exe. However, it need only be in a dir that is specifiable relative to the .exe. (in which case you'd specify the relative path in the connection string) So, in the case of webapps for instance, they actually do write to a DATA dir that is below the .exe. But, Xcopy works the same with web as well.

  • Anonymous
    July 30, 2004
    Thank you! Thank you! I have spent several days now trying to find this information, and you put it in a concise, easy to understand terms. It is exactly what I needed to know.

  • Anonymous
    April 23, 2006
    The one that explains the XCopy options in SQL Server Express.

  • Anonymous
    April 05, 2008
    PingBack from http://copyrightrenewalsblog.info/vs-data-teams-weblog-sse-and-visual-studio-xcopy-mindshift/

  • Anonymous
    July 10, 2008
    PingBack from http://tina.infovideoclub.info/vbscriptbackupprofilexcopy.html