Distribute Database w/ Application

[jalbao]

I am currently in the learning process of how to build database applications with VB.NET and SqlServer.

I have a basic project that has a form that allows the user to insert data into my SqlServer db - it all works fine.

My question is in regards to distributing this app to a different machine. For some odd reason the book that I am going thru to teach me all of this stuff doesn't go over distributing a .net app along with a sql server database.

I am totally lost on what I have to do to get the database schema (I don't want the test data that I have inserted during development) off of my dev machine and into the compiled executable of the app.

My main questions can be boiled down to:
1. How do I include the SqlServer db in the distributable app?
2. How will the client machine know where the database is once it is installed?
3. Should I use ODBC - OLE - relative path - other?

TIA


For the curious - the book I'm using is:
"Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .NET by Rick Dobson"

 

[chix001]

Tooled,

SQL Server is NOT distributable like Microsoft Access. That's the beauty of it - you load and keep your data in one place, and point all of your applications (.NET + Access, C++, whatever) to it, through OLE or ODBC.

If you're using VB.NET I have found that the easiest (albeit not too secure) way of connecting to a SQL server is by putting the connection string in the web.config file (or app.config file for a Windows application) and then referencing that in your application. This way you can change from a development data environment to a production data environment without having to recompile the application.

OLE is much faster than ODBC if you're using VB.NET and SQL Server 2000 (others may work well, too, but this is my only point of reference).

Also, if you use OLE, then any distribution of the application doesn't have to be modified to point to the database - if you use ODBC, you'll have to set up ODBC connections on each machine. You can do it automatically, but I still find that it's more painful than OLE.

Hope this helps,

CHIX001

 

[jalbao]

Thanks for the reply chix001 - Your reply does help in some regards.

It made me realize that the the data provider that I am using is (apperantly) a little different than what I am used to - I am used to working with ODBC in database web applications.

The data provider that I am using in my vb.net app is the sql server .net data provider.

According to what I have read, the sql server .net data provider connects directly to the sql server instance.

Herein lies my confusion.

In my code for connecting to the sql server instance, my connection string looks like:
Data Source=(local);Database=myTestDB;user id=sa; password=test;

As you can see, the Data Source value is "local". So when I run the app on my dev box the app is able to connect to the db because the data source is local.

When I compile the app and run it on a client machine, I want the db to be installed locally on the client machine. But apperantly, the "Data Source = (local); .. " doesn't cut it. As of now, when I compile and install the app onto a client machine and then attempt to connect to the db, the connection to the db fails.

I'm thinking that I somehow need to specify in my code the location of the datasource as it is on the client - this is what I do not know how to do.

Because of your reply, chix001, I can restate my initial question a little more specifically:

When using the Sql Server Data Provider, how should I code my connection string so that, after the app is compiled, the connection can be established on a computer other than the development computer?

I apologize if I sound like an idiot, but all this stuff is totally foreign to me.

Thanks for any pointers

 

[DotNetDoc]

The SQL Server 2000 Desktop Engine (MSDE 2000) is a data engine built and based on core SQL Server technology. With support for single- and dual-processor desktop computers, MSDE 2000 is a reliable storage engine and query processor for desktop extensions of enterprise applications. The common technology base shared between SQL Server and MSDE 2000 enables developers to build applications that can scale seamlessly from portable computers to multiprocessor clusters. This is free to distribute with your apps ( Not all clients can afford an SQL Server licence)

You can find more info here:

http://www.microsoft.com/sql/msde/productinfo/default.asp

As far as installs are concerned you can create all your DB installation infomation using scripts. You can build this into your package.

You could use either the registry or an outside file to hold the location of your db.




DotNetDoc
M.C.S.D.

http://www.DotNetDoc.com

---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb
-----------------------------------
If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[jalbao]

DotNetDoc,

So then are you implying that MSDE 2000 is not technically a Sql Server?

If this is true, then I'm thinking that I cannot use the Sql Server .NET data provider for a stand alone application where each client has it's own database installation.

Am I correct??

 

[DotNetDoc]

No what I am saying is that it is not only "A" SqlServer but it is Microsoft's SQL Server. MSDE 2000 is the FREE distributable version of SQL Server. The SQL data provider will work with it. It is a Microsoft Product.

It has some limitations. (Does not come with server tools(Enterprise MGR, Query analyzer etc..),10 concurent users (accessing data at the EXACT same time, DB Size etc...) Which should be detailed out in the link I gave you.

MSDE is great for either a single user app or a small to medium business app because it is FREE and Freely distribuitalbe.
If a client needs to upgrade to the Full SQL2000 version in the future for any reason you do not need to change any code.





DotNetDoc
M.C.S.D.

http://www.DotNetDoc.com

---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb
-----------------------------------
If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[RiverGuy]

To send your DataBase along to the client with your app:


1. The easiest method is to make sure they have SQL Server, MSDE, or access to one. Once you can connect to one, you send a DDL statement to the server as a command. You can script your database in Enterprise Manager by right-clicking on the database, all tasks, generate sql script.

2. The second method would be to physically detach the .mdf file from your database, and it to your setup package. Then execute an sp_AttachDB stored procedure in your code to attach the .mdf file to the client's server of choice.

Regardless, as stated above, the client must either have an SQL instance installed on their machine, or specify one to connect to.