Making an ADO.NET connection to a database with C#

This example uses the ADO.NET class library to connect to a SQL Server database and is based on a method described in Visual C# 2010  Step by Step by John Sharp.

The database

The database created for this example, msnotes, is a simple database for storing notes with their own categories. It has two tables joined by a foreign key:

CREATE TABLE categories(
catidx INT IDENTITY(1,1) PRIMARY KEY,
category CHAR(20));

CREATE TABLE notes(
noteidx INT IDENTITY(1,1) PRIMARY KEY,
note_cat INT,
note_data VARCHAR(400),
FOREIGN KEY(note_cat) REFERENCES categories(catidx));

Connecting to a database

First we need to specify the namespace which provides access to the ADO.NET classes we need:

using System.Data;
using System.Data.SqlClient;

In the body of the program an SqlConnection object will be created to connect to the database:

SqlConnection conn = new SqlConnection();

To open a connection, the ConnectionString property of an SqlConnection class object needs to be set. To construct the string the SqlConnectionStringBuilder class can be used. This example uses Windows Authentication to avoid having to provide or ask for a connection password. The calls to connect and query the database can return an SqlException so will be placed in a try.. catch block:

try
{
// construct a connection string to the database
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = “.\\MYINSTANCE“;
builder.InitialCatalog = “msnotes”;
builder.IntegratedSecurity = true;
conn.ConnectionString = builder.ConnectionString;

The SqlConnection object is now initialized and a connection can be established:

    // Connect
conn.Open();

Querying Data

This example queries data from the notes table with a simple join to look up a category value from the categories table. An SqlCommand object is created and initialized with the connection object and the query string:

    // create an SQL statement
SqlCommand sqlStmt = new SqlCommand();
sqlStmt.Connection = conn;
sqlStmt.CommandType = CommandType.Text;
sqlStmt.CommandText =
“SELECT category, note_data FROM categories, notes ” +
“WHERE notes.note_cat = categories.catidx”;

To process the data from the query an SqlDataReader object is used. The Read method is called until it returns 0 to indicate no more rows:  

    // run the command
SqlDataReader dataReader = sqlStmt.ExecuteReader();
while (dataReader.Read())
{
string catName = dataReader.GetString(0);
string note = dataReader.GetString(1);

This example was written in console application so the Console.WriteLine will be used to display the data:

        Console.WriteLine(“Category: {0}\nNote: {1}\n”, catName, note);
}
dataReader.Close();
}

 

Error Handling

Error handling is performed in the catch block:

 

catch (SqlException e)
{
Console.WriteLine(“Error accessing database: {0}”, e.Message);
}

Freeing Resources

Closing the connection and any other cleanup can be done in a finally block:

 

finally
{
conn.Close();
}

Advertisements
This entry was posted in Computers and Internet, Databases. Bookmark the permalink.

2 Responses to Making an ADO.NET connection to a database with C#

  1. Suma says:

    good one!

  2. Suma says:

    Wish you were using mysql or I was using SQL server!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s