From iLab Cookbook

VisualStudio: SQL and C#


Databases are extremely powerful tools for the storage and retrieval of data. C# makes interacting with SQL databases fairly painless.

Contents

Starting a SQL Server

A version of SQL Server 2005 comes with Visual Studio .NET 2005. More information on getting started later.

You may wish to download SQL 2005 Express Edition which is free and easy to use.

To connect to a SQL server, you use the SqlClient libraries.

using System.Data.SqlClient;

SqlConnection myConnection = new SqlConnection(..);

where we replace the ".." with a string that describes the properties of the SQL server.

The name of my server is CS-DD928D0CB525\SQLEXPRESS, that is, the name of my machine--backslash--the name of the SQL server being run.

The name of the database I wish to access is "master". It probably will be for you as well, unless you expressly added a new database.

Here's what the objection creation looks like:

SqlConnection myConnection = new SqlConnection("server=CS-DD928D0CB525\\SQLEXPRESS;" +

                                       "Trusted_Connection=yes;" +
                                       "database=master;" +
                                       "connection timeout=30");

Usually, if the connection is trusted, you won't need a password/user name. There are other options, but for our purposes, this ought to suffice. The timeout value sets time until discount in the case of timeout.

If you actually need to log on to the server (like you are accesses the database remotely) this is slightly complicated and actually requires doing a little bit of database management. It is easier to set up a local sql server and have the SQL-enhanced Shared Dictionary access it.

For more information on the connection string, see http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson02.aspx

Now actually connect:

myConnection.Open();

which can throw an exception.

You are now ready to actually run some queries. See Querying the Database.

Finally, when you are ready to close it:

myConnection.Close();

It is my understanding that you should always close your database connection.

Querying the Database

So you are connected to the Database. How do you actually get at the data?

It turns out to be very simple: all queries, regardless if they insert data, create tables, delete, or return values operate in the same manner. First you need a SqlCommand object.

SqlCommand myCommand = new SqlCommand("select * from dogs", myConnection);

Where the first argument of the constructor is the SQL query you wish to execute and the second is an open SqlConnection. In this example, I am trying to get all values out of the dogs table. This does not, in and of itself, actually execute the query. You must execute it.

SqlDataReader myReader = myCommand.ExecuteReader();

This executes the query and returns the response to the reader. This throws an exception.

Suppose I wish to print out the breeds of all the dogs that are in the table:

while (myReader.Read()) Console.WriteLine(myReader["Breed"].ToString());

This selects the "Breed" column from each row in order and prints it out.

After you are done and ""before any other queries can be executed"" you must first close the current reader:

myReader.Close();

You are now free to create new SqlCommands, or modify the existing object via SqlCommand.CommandText

For an example, see: Attach:SQLExample Δ

If you have more questions, feel free to ask Mark Watson.

Retrieved from http://grouplab.cpsc.ucalgary.ca/cookbook/index.php/VisualStudio/SQLAndC
Page last modified on January 08, 2007, at 03:09 PM