By Matt Newman, 22 Aug 2004
| 4.54 (150 votes) |
Top of Form
In this article I plan to demonstrate how to insert and read data from a SQL Server or MSDE database. This code should work on both SQL Server , I am using 2000, and MSDE. I am using Visual Studio 2002, but this should work with Visual Studio 2003, Web Matrix, and the command line SDK. This code should work with both C# applications and C# web applications and webservices. This code does not compile on the FreeBSD with Rotor [^]. Background
Part of my current project required me too store and retrieve information from a database. I decided to use C# as my target language since I am currently reading Inside C# Second Edition [^] by Tom Archer [^], which by the way is a must have book. However I could not find any examples that were clear and just generic accesing SQL Server with C#. Using the code
I did not include a sample application because the code provide within the article can really be dropped in and should work with no problem. Also through out the article I will refer to SQL Server, MSDE is a free version of SQL Server that does not have some of the GUI tools and has a few other limits such as database size. This code will work on both without problem. Making the Love Connection
There is no real voodoo magic to creating a connection to a SQL Server assuming it is properly setup, which I am not going to go into in this article, in fact .NET has made working with SQL quite easy. First step is add the SQL Client namespace: Collapse | Copy Code
Then we create a SqlConnection and specifying the connection string. Collapse | Copy Code
SqlConnection myConnection = new SqlConnection("user id=username;" +
"database=database; " +
"connection timeout=30"); Note: line break in connection string is for formatting purposes only SqlConnection.ConnectionString
The connection string is simply a compilation of options and values to specify how and what to connect to. Upon investigating the Visual Studio .NET help files I discovered that several fields had multiple names that worked the same, like Password and Pwd work interchangeably. I have not included all of the options for SqlConnection.ConnectionString at this time. As I get a chance to test and use these other options I will include them in the article. User ID
The User ID is used when you are using SQL Authentication. In my experience this is ignored when using a Trusted_Connection, or Windows Authentication. If the username is associated with a password Password or Pwd will be used. "user id=userid;"
Password or Pwd
The password field is to be used with the User ID, it just wouldn't make sense to log in without a username, just a password. Both Password and Pwd are completely interchangeable. "Password=validpassword;"-or-
Data Source or Server or Address or Addr or Network Address
Upon looking in the MSDN documentation I found that there are several ways to specify the network address. The documentation mentions no differences between them and they appear to be interchangeable. The address is an valid network address, for brevity I am only using the localhost address in the examples. "Data Source=localhost;"
Integrated Sercurity or Trusted_Connection
Integrated Security and Trusted_Connection are used to specify wheter the connnection is secure, such as Windows Authentication or...