The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Express VS2005 IDE C#

I have spent almost two weeks on the following problem. I have searched high and low and I am currently severly gritting my teeth as I cannot find an answer OR anyone that has actually done what I wish to do and can talk from experience.

I want to create a new project using the VS2005 IDE and it will use an SQL Express Server db. The end project will be distributed to users who have no idea what SQL Server Express is, nor do they care. All configuration, setup etc needs to be "out-of-the-box" so to speak and without a tech service person attending.

Seems straightforward enough. I can and have done it using an MS Access db.

There appears to be two ways of creating your db. One way is best described by me as the "normal" SQL Server way ie Create the database in Server Manager (or in the VSIDE if you like) under the Express Server tree. You then access the db in your program by using a "simplified" connection string eg:"Data Source = .\\SQLEXPRESS;Initial Catalog = Practice1; Integrated Security = True";

The second way seems to be the "new/fantastic/everyone uses/ms recommends/bells & whistles/your stupid if you don't" way of attaching a .mdf file to to SQLExpress and use the VS2005 IDE to it's full potential. The connection string that works is "@Data Source = .\SQLEXPRESS;Integrated Security = True;User Instance = True;AttachDbFileName =@"D:\VS2005 Projects\MyCourts\MyCourts\Resources\mycourts.mdf"; As far as I can tell there is NO OTHER Connection string that works so how you reference the distrubuted db file in a distributed program is beyond my grasp.

I have never distrubuted (or written a ui program based on SQL Server) and am concerned that the first method may require a huge learning curve as far as distributing and creating the db whilst the second method is touted as being as simple as distributing the .mdf file as you do with ms access. I am more than happy to learn but from all the hype I think I must be missing something obvious to everyone but me.

If there is anyone with experience (rather than theory because I think I've read almost everything on the net) that can help me - I would be greatly obliged.

I guess what I basically want is the connection string to read the db as it is distrubuted - not on my development machine.

And NO - IT's NOT A BLOODY ASP.NET PROJECT!!!

Thanks.
Glen Harvy Send private email
Friday, March 23, 2007
 
 
The following approach works fine for me (and yes, I also scoured the net for days before I managed to make it work):

1. Add an app.config file to your project. It should look something like this (never posted XML here before, so I can only hope it will look OK):

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="MyProgram.Properties.Settings.MyConnectionString" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>


2. The exact same setting must be present on the Settings tab in your project's properties (in this case, Name: MyConnectionString, Type: (Connection string), Scope: Application, Value: Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True). I can't remember if it is created automatically based on the app.config file, if not, do it by hand.

3. Once this is done and your app builds successfully, you can deploy an *.exe.config file containing a different data source path that will be picked up automatically when your app is run on the client machine.

Of course this example connects to the local SQL Server so you will need to change the connection string, but I think it should work with simple mdf files, too.

Hm?
Alan Send private email
Friday, March 23, 2007
 
 
...and by the way, I used typed DataSets in this solution, generated automatically by VS 2005 from the db schema, and the auto-generated code is able to change the connection string based on app.config settings.
Alan Send private email
Friday, March 23, 2007
 
 
Thanks for your suggestion.

After over a week of search I just found this article http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx which I think fixes my problem - at least I now have a connection string that works.

I achieved in an around about way like you have ie by using the string generated by VS2005 itself and located in Properties.Settings.Default.<ConnectionString>.ToString() .

I've archived this for reference when I deploy :-)

Thanks.
Glen Harvy Send private email
Friday, March 23, 2007
 
 
You're welcome, Glen.

I read the blog entry you linked, and I think you will need to use both fixes. My solution is required to be able to change the connection string at all (since in my experience the code emitted by the typed DataSet generator is wired to *only* look at the Properties.Settings config section for the connection string, no matter what other docs/blogs/articles say - if this is not configured the way I described, the DataSet init code defaults to the design time connection string built into your exe). Also, you will need the local database fix from the MSDN blog entry to actually be able to specify a relative path in the connection string for your client machine.

So, good luck, hope it'll all work fine :)

(BTW, I was literally going to tear out that meager remains of my hair when I finally tried this as a last resort, hehe ;)
Alan Send private email
Friday, March 23, 2007
 
 
Have you considered using SQL Server Compact Edition for your application? It's not for everybody but you mentioned using Access so it could be a good fit. SQL Server Compact Edition can be embedded as a single DLL that is very small. It's for single-user applications only because it isn't a real server database product. This may or may not suit your needs. It sounds like you are hoping for a pretty simple installation which SSCE gives you.

Here is a link comparing the two SQL Server editions that probably interest you the most.

http://www.microsoft.com/sql/editions/compact/sscecomparison.mspx
dood mcdoogle
Friday, March 23, 2007
 
 
Hi Alan,

Thanks again. So that I don't waste another week trying to find THE fix can you please point me to the "Also, you will need the local database fix from the MSDN blog entry"

Thanks.
Glen Harvy Send private email
Friday, March 23, 2007
 
 
Thanks dood,

I'll check it out.
Glen Harvy Send private email
Friday, March 23, 2007
 
 
I meant the fix YOU linked in from MSDN in your post ;)

http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx

Just wanted to make my reply a little shorter by omitting "the fix your link points to", but looks like it came up too short... sorry for that.

Saturday, March 24, 2007
 
 
...and it was me again, forgetting to reenter my name and email (sigh).
Alan Send private email
Saturday, March 24, 2007
 
 
For me, it depends on what is in the database.  If there isn't much data (like maybe zip code lookup tables) in the initial db, then try having the setup program create the database:

CREATE DATABASE xyz
GO
USE xyz
CREAT TABLE Table1 (col1 int, col2 int)

etc....

Then create a conection string as normal since the database will be there.

If there is a lot of initial data, then ship a starter db with the app and use
sp_attachdb to load it in SQL Express.  After attaching it this way, it will stay there and you can access it normally from this point forward.

To be really professional, make to setup components, one for the app and one for the database to make it easier to install the app on one computer and the db on another.
JSmith Send private email
Sunday, March 25, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz