Using and SQL database

[JeffInTexas]

Beginner question for programming database application.

If I develop a C# app that uses an SQL database and I intend to distribute the app to different users as a stand-alone desktop app, will all these users need to have SQL Express installed on their machines, or will my app work natively with the Windows version that resides on their computer by default?

The idea here is that I do not want to develop an app using a database that would require me to have all of my intended users download add-ons to their systems, nor do I want to have to do that for them. I want my app to install automatically, generate their empty database, and just "go." I don't want any going elsewhere to get add-ons, because my target users are not IT sophisticated, and I don't want the added pain of having to do all these things for them.

I know it's a basic question, but I need to start off in the right direction, and choice of database is an important one. I would like to use something that works well with relations and indices and gets along nicely and easily with Vis. Studio and C#.

FYI, it's a rinky-dink app, and so security issues are no big deal.

Thanks.

[Silent Sojourner]

You can use a single sql server database installed on your server and put the connection string in your app's config file.

[JeffInTexas]

Thanks, SJ. I'm glad you're around here.

Just to be sure, are you saying the following:

1. End user does not need SQL "anything" on his machine,

2. From within my app, I can cause a new mdf database to be created on end user's machine and define all its tables and columns,

3. From there, when EU installs my app (and only my app) and runs it, it will create the mdf with the tables and columns I directed from within my app,

4. If, in the future (which will happen, I am sure), I need to change the structure of a table to add some columns, or add a new table, etc., I can write code in my app that changes the mdf structure, and when the EU starts the new version of my app that I give him, it will do all the table conversion as well....

.... All of this capability without the end user needing anything other than Windows and my app.?

Thanks.

[Silent Sojourner]

That depends on what kind of application you want.
If you want a client/server app, common practice is that you have one single db server and the clients share that database. In this way the clients need not install sql "anything" but .net framework. If you modify your db structure, all clients will see it instantly.
If you want local storage on each client's machine, you need to use sql server compact edition which is attached to your application. In this way each client will have his separate database and needs to have sql server compact edition runtime (basically a bundle of dll files) installed on his machine. The dlls can be deployed easily using click-once deployment, though. If you want to modify your db structure, you need to deploy your app again.

[JeffInTexas]

Excellent. I think I'm getting it. Yes, this will not be a client/server situation. Every end user will install the app on his stand-alone machine. Of course, it is possible that his environment might be a small, local network.

So, okay, is it possible that I can make my app automatically go out and install Compact for my end users to where they do not have to do so themselves? If so, are there no configuration settings that my end users will have to make with regard to Compact? I don't mind making my app go out and download Compact on their machines; I just don't want it being a 30-minute task, nor do I want the end user to have to do anything whatsoever with regard to working with Compact. If it "invisibly" is put on his machine and he has to do nothing but run my program, I am good.

Is that the case? Sounds like it is, but I want to confirm, and then, I'm a "go."

Thanks again!

[Silent Sojourner]

You can use click-once deployment:
http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx

[JeffInTexas]

Excellente! Now that that is decided, I guess I can probably skip much of the reading that was ahead of me in the "Windows Server 2008 For Dummies" book I just bought.

However, there are some SQL things I need to know. For example, I think SQL databases store procedures and such. When I used to program, databases were just flat files. The stored all their data in fields (now columns). They could be only 1 "table." Each table was its own flat file, and if you needed it, you opened the other flat file associated with it.

Now, they store procedures and apparently some other useful stuff that makes programmer's lives better. Can you tell me what topics in this regard that I should focus on when learning how to use my new SQL database?

I am sure my Dummies book covers those topics, but it also seems to cover a lot of topics that will have no relevance to what I am doing. I do not plan to use Server 2008, except on my own computer for times when I need to look a database contents while debugging.

[Silent Sojourner]

Sadly sql server compact edition does not support sproc (http://social.msdn.microsoft.com/For...7-d3adfc85336d)

[JeffInTexas]

No problem. Don't need it.

I might repost this next question as a new topic, but here's where I'm at:

I boned up on setting up databases in SQL Server Management Studio 2008. Easy as pie.

But, no matter what I try, I cannot get access from my C# project to the database. It says I do not have permission to access it. I have tried various ways but mostly tried the set up Add New Data Source wizard in the Visual C# Studio.

All I want is a simple database for a small office environment. No security is needed at all. I can't even get into from C# by setting SQL Server users in Server Studio to log in using plain vanilla Windows Authentication, where obviously the user will have administrative privileges on his own PC.

Any ideas? If I need to be more descriptive, let me know what information I can dig up and where I can get it for you. Once I can get connected from within my C# project, I can start flying.

P.S.: Oh, and BTW, can I use the same one-click install deployment method to install the DLL's the end users will need in order to use a Server Express (and not Compact) database?

Thanks.

[JeffInTexas]

Well, I got past the "doesn't have permission" problem. Apparently, you have to actually detach the subject database from SQL Server Studio to close it. But now, after I choose the database in C# Add Data Source wizard, once I try to test the connection, it just hangs.

Argh!!!

[JeffInTexas]

Good God Almighty and Glory Hallelujah! Hours and hours and hours to finally arrive at this:

metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string='Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Test.mdf";Integrated Security=True;Connect Timeout=30;User Instance=False'

It works. I had to change to User Instance=False.

I hate fixing things without knowing and understanding the fix. Can anyone explain all of these parameters in the string to me?

Woohooo!!!!!! I think I'll sleep tonight, rather than stay up all night like last night. Wow! What a relief! It was such an obscure fix.

[Arjay]

Might be a little late here, but IMO the data sources wizard puts too much goo in there.

Check out using the SqlConnection class for opening database connection.

Try the following connection string:

var connStr = "Server=(local)\SqlExpress;DataBase=Test;Integrated Security=TRUE";

In code it could be something like...

using( var cn = new SqlConnection( connStr ) )
{
  cn.Open();
  using( var cmd = new SqlCommand( query or sproc, cn ) )
  {
    var reader = cmd.ExecuteReader( );

    while( reader.Read( ) )
    {
       // use the reader
    }
  }
}

Of course there are other approaches like using a data adapter.

[JeffInTexas]

Thank you, Arjay. You are not too late. I need to get a decent grip of the various methods that are available to me, along with their pros and cons and the syntax with which you invoke them. Are you pretty versed in that?

I am told the Entity Framework is pretty much the best alternative, and yet, I find no takers in my post in this forum concerning how to use it. Probably not a lot of database people around this place.....

Thanks, again, and seriously, if you are a database guy, I'd love to talk further.

[Arjay]

I do a fair amount of database work on an enterprise level. My preference is to strill use stored procedures as the interface between sql and c#.

[JeffInTexas]

Arjay, you just might be the person I've been looking for to steer me right. Let be describe briefly where I am at in the curve and what I am looking for (and even willing to pay for).

I completely understand the idea of relational databases and setting them up. I used to be a database programmer in the early 1990's. Things have changed since then and so has the jargon.

They now have categories dealing with DataSet, Entity, etc. Apparently, when you set them up, you have to access them in C# using various methods, depending on whether you want DataSet or Entity, etc. So, it's like you use a different namespace, depending on your chosen method, to get all the appropriate functionality to use to access your data.

Something like, for example, new MyDatabaseEntities().

From there, it apparently opens the database, but now, you have to know what it returns. Is it a DataSet, a List, an Entity? I think it's an Entity.

Then, once you know that, you have to know what methods/classes this new Entity uses. For example, if you want to set a relation (or is it load a relation that you already set up?), you have to know what type of return you get. Is it a List, a DataSet, or what?

Plus, you need to know how the system itself handles the data. Are you dealing with a copy of it in memory, or are you dealing with the database itself? If you are dealing with only a copy, when do changes get saved to the physical database, or maybe the question should be, "What is the best way to time all the saves?"

These things are ideas I need to familiarize myself with. C# books and SQL books simply fail to do address the questions and they are a waste of time.

I would even be happy to PM you my contact information, so we could get together, AND.... I'd even be happy to pay you if you want to spend maybe an hour or two with me so I can quickly get up to speed. I understand program logic flow extremely well. You will not need to worry that I don't understand programming. I understand it very, very well. I just need to get a decent grasp on how modern day database implementation is set up and handled as far as getting to your data and performing queries, appends and saves to the database.

Paying you for an hour or two would be far more efficient than trying to find a good book or internet article. The fact is that, for me, I don't even know the jargon well enough to know which books or articles will do me any good. So, consequently, I find myself wasting a lot of time. I would think $100 or so to a tutor would be money well spent.

Thanks.