SQL and C#

VisualStudio.SQLAndC History

Hide minor edits - Show changes to output

Added lines 60-61:
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.
Changed lines 70-71 from:
You are no ready to actually run some queries. See [[#Queries | Querying the Database ]].
to:
You are now ready to actually run some queries. See [[#Queries | Querying the Database ]].
Added lines 60-61:
For more information on the connection string, see [[http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson02.aspx]]
Added lines 1-110:
(:title SQL and C# :)
%define=box padding-left=1em padding-right=1em margin='3px 3px 0'%
%define=yellowbox box bgcolor=#fdfaea border='1px solid #ffad80'%
%define=redbox box bgcolor=#fff3f3 border='1px solid #ffc9c9'%
%define=bluebox box bgcolor=#f4fbff border='1px solid #a1cae6'%
%define=skybox box bgcolor=#f8fcff border='1px solid #aaaaaa'%
%define=greybox box bgcolor=#fbfbfb border='1px solid #aaaaaa'%
%define=greenbox box bgcolor=#e6f3e5 border='1px solid #8fd586'%
%define=whitebox box bgcolor=#ffffff border='1px solid #999999'%
\\
Databases are extremely powerful tools for the storage and retrieval of data. C# makes interacting with SQL databases fairly painless.

>>redbox<<
!!! Contents

*[[#SQL | Starting a SQL Server]]
*[[#Connect | Connecting to a SQL Server]]
*[[#Queries | Querying the Database ]]

>><<

[[#SQL]]
>>greenbox<<
!!! 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 [[http://msdn.microsoft.com/vstudio/express/sql/download/ | SQL 2005 Express Edition]] which is free and easy to use.

>><<

[[#Connect]]
>>yellowbox<<

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

* First, you'll need to include it in the program namespace:

@@using System.Data.SqlClient;@@

* Now you simply need to create a new instance of the SqlConnection object:

@@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.

Now actually connect:

@@myConnection.Open();@@

which can throw an exception.

You are no ready to actually run some queries. See [[#Queries | 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.

>><<

[[#Queries]]
>>skybox<<
!!! 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@@

>><<

>>greybox<<
For an example, see: Attach:SQLExample

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