Using Database Connections
In order to access the database, you need to provide connection parameters, such as the machine that the database is running on, and possibly your login credentials. Anyone who has worked with ADO will be immediately familiar with the .NET connection classes, OleDbConnection and SqlConnection:

Connection Interfaces

The following code snippet illustrates how to create, open, and close a connection to the Northwind database. In the examples within this chapter I use the Northwind database, which is installed with the .NET Framework SDK samples:

using System.Data.SqlClient;

	string source = "server=(local)\\NetSDK;" +
		"uid=QSUser;pwd=QSPassword;" +
		"database=Northwind";
	
	SqlConnection conn = new SqlConnection(source);
	conn.Open();
	
	// Do something useful
	
	conn.Close();

The connection string should be very familiar to you if you've ever used ADO or OLE DB before - indeed, you should be able to cut and paste from your old code if you use the OleDb provider. In the example connection string, the parameters used are as follows. The parameters are delimited by a semicolon in the connection string.

• server=(local)\\NetSDK - This denotes the database server to connect to. SQL Server permits a number of separate database server processes to be running on the same machine, so here we're connecting to the NetSDK processes on the local machine.
• uid=QSUser - This parameter describes the database user. You can also use User ID.
• pwd=QSPassword - And this is the password for that user. The .NET SDK comes with a set of sample databases, and this user/password combination is added during the installation of the .NET samples. You can also use Password.
• database=Northwind - This describes the database instance to connect to - each SQL Server process can expose several database instances.

The example opens a database connection using the defined connection string, and then closes that connection. Once the connection has been opened, you can issue commands against the data source, and when you're finished, the connection can be closed.

SQL Server has another mode of authentication - it can use Windows integrated security, so that the credentials supplied at logon are passed through to SQL Server. This is catered for by removing the uid and pwd portions of the connection string , and adding in Integrated Security=SSPI.

In the download code available for this chapter, you will find a file Login.cs that simplifies the examples in this chapter. It is linked to all the example code, and includes database connection information used for the examples; you can alter this to supply your own server name, user, and password as appropriate.

This by default uses Windows integrated security; however, you can change the username and password as appropriate. Now that we know how to open connections, before we move on we should consider some good practices concerning the handling of connections.

Using Connections Efficiently
In general, when using "scarce" resources in .NET, such as database connections, windows, or graphics objects, it is good practice to ensure that each resource is closed after use. Although the designers of .NET have implemented automatic garbage collection, which will tidy up eventually, it is necessary to actively release resources as early as possible.

This is all too apparent when writing code that accesses a database, as keeping a connection open for slightly longer than necessary can affect other sessions. In extreme circumstances, not closing a connection can lock other users out of an entire set of tables, considerably hurting application performance. Closing database connections should be considered mandatory, so this section shows how to structure your code so as to minimize the risk of leaving a resource open.

There are two main ways to ensure that database connections and the like are released after use.

Option One - try/catch/finally
The first option to ensure that resources are cleaned up is to utilize try_catch_finally blocks, and ensure that you close any open connections within the finally block. Here's a short example:

try {
	// Open the connection
	conn.Open();
	// Do something useful
} catch (Exception ex) {
	// Do something about the exception
	} finally {
	// Ensure that the connection is freed
	conn.Close();
}

Within the finally block you can release any resources you have used. The only trouble with this method is that you have to ensure that you close the connection - it is all too easy to forget to add in the finally clause, so something less prone to vagaries in coding style might be worthwhile.

Also, you may find that you open a number of resources (say two database connections and a file) within a given method, so the cascading of try�catch�finally blocks can sometimes become less easy to read. There is however another way to guarantee resource cleanup - the using statement.

Option Two - The using Block Statement
During development of C#, .NET's method of clearing up objects after they are no longer referenced using nondeterministic destruction became a topic of very heated discussion. In C++, as soon as an object went out of scope, its destructor would be automatically called. This was great news for designers of resource-based classes, as the destructor was the ideal place to close the resource if the user had forgotten to do so. A C++ destructor is called in any and every situation when an object goes out of scope - so for instance if an exception was raised and not caught, all objects with destructors would have them called.

With C# and the other managed languages, there is no concept of automatic, deterministic destruction - instead there is the garbage collector, which will dispose of resources at some point in the future. What makes this nondeterministic is that you have little say over when this process actually happens. Forgetting to close a database connection could cause all sorts of problems for a .NET executable. Luckily, help is at hand. The following code demonstrates how to use the using clause to ensure that objects that implement the IDisposable interface (discussed in Chapter 2) are cleared up immediately the block exits.

string source = "server=(local)\\NetSDK;" +
	"uid=QSUser;pwd=QSPassword;" +
	"database=Northwind";

	using ( SqlConnection conn = new SqlConnection ( source ) )

Note: The classes and interfaces used for data access in the .NET Framework will be introduced as the chapter continues. I will mainly concentrate on the Sql classes when connecting to the database, because the Framework SDK samples install an MSDE database (SQL Server). In most cases the OleDb classes mimic exactly the Sql code.

Using Database Connections
In order to access the database, you need to provide connection parameters, such as the machine that the database is running on, and possibly your login credentials. Anyone who has worked with ADO will be immediately familiar with the .NET connection classes, OleDbConnection and SqlConnection:

Connection Interfaces

The following code snippet illustrates how to create, open, and close a connection to the Northwind database. In the examples within this chapter I use the Northwind database, which is installed with the .NET Framework SDK samples:

using System.Data.SqlClient;

	string source = "server=(local)\\NetSDK;" +
		"uid=QSUser;pwd=QSPassword;" +
		"database=Northwind";
	
	SqlConnection conn = new SqlConnection(source);
	conn.Open();
	
	// Do something useful
	
	conn.Close();

The connection string should be very familiar to you if you've ever used ADO or OLE DB before - indeed, you should be able to cut and paste from your old code if you use the OleDb provider. In the example connection string, the parameters used are as follows. The parameters are delimited by a semicolon in the connection string.

• server=(local)\\NetSDK - This denotes the database server to connect to. SQL Server permits a number of separate database server processes to be running on the same machine, so here we're connecting to the NetSDK processes on the local machine.
• uid=QSUser - This parameter describes the database user. You can also use User ID.
• pwd=QSPassword - And this is the password for that user. The .NET SDK comes with a set of sample databases, and this user/password combination is added during the installation of the .NET samples. You can also use Password.
• database=Northwind - This describes the database instance to connect to - each SQL Server process can expose several database instances.

The example opens a database connection using the defined connection string, and then closes that connection. Once the connection has been opened, you can issue commands against the data source, and when you're finished, the connection can be closed.

SQL Server has another mode of authentication - it can use Windows integrated security, so that the credentials supplied at logon are passed through to SQL Server. This is catered for by removing the uid and pwd portions of the connection string , and adding in Integrated Security=SSPI.

In the download code available for this chapter, you will find a file Login.cs that simplifies the examples in this chapter. It is linked to all the example code, and includes database connection information used for the examples; you can alter this to supply your own server name, user, and password as appropriate.

This by default uses Windows integrated security; however, you can change the username and password as appropriate. Now that we know how to open connections, before we move on we should consider some good practices concerning the handling of connections.

Using Connections Efficiently
In general, when using "scarce" resources in .NET, such as database connections, windows, or graphics objects, it is good practice to ensure that each resource is closed after use. Although the designers of .NET have implemented automatic garbage collection, which will tidy up eventually, it is necessary to actively release resources as early as possible.

This is all too apparent when writing code that accesses a database, as keeping a connection open for slightly longer than necessary can affect other sessions. In extreme circumstances, not closing a connection can lock other users out of an entire set of tables, considerably hurting application performance. Closing database connections should be considered mandatory, so this section shows how to structure your code so as to minimize the risk of leaving a resource open.

There are two main ways to ensure that database connections and the like are released after use.

Option One - try/catch/finally
The first option to ensure that resources are cleaned up is to utilize try_catch_finally blocks, and ensure that you close any open connections within the finally block. Here's a short example:

try {
	// Open the connection
	conn.Open();
	// Do something useful
} catch (Exception ex) {
	// Do something about the exception
	} finally {
	// Ensure that the connection is freed
	conn.Close();
}

Within the finally block you can release any resources you have used. The only trouble with this method is that you have to ensure that you close the connection - it is all too easy to forget to add in the finally clause, so something less prone to vagaries in coding style might be worthwhile.

Also, you may find that you open a number of resources (say two database connections and a file) within a given method, so the cascading of try�catch�finally blocks can sometimes become less easy to read. There is however another way to guarantee resource cleanup - the using statement.

Option Two - The using Block Statement
During development of C#, .NET's method of clearing up objects after they are no longer referenced using nondeterministic destruction became a topic of very heated discussion. In C++, as soon as an object went out of scope, its destructor would be automatically called. This was great news for designers of resource-based classes, as the destructor was the ideal place to close the resource if the user had forgotten to do so. A C++ destructor is called in any and every situation when an object goes out of scope - so for instance if an exception was raised and not caught, all objects with destructors would have them called.

With C# and the other managed languages, there is no concept of automatic, deterministic destruction - instead there is the garbage collector, which will dispose of resources at some point in the future. What makes this nondeterministic is that you have little say over when this process actually happens. Forgetting to close a database connection could cause all sorts of problems for a .NET executable. Luckily, help is at hand. The following code demonstrates how to use the using clause to ensure that objects that implement the IDisposable interface (discussed in Chapter 2) are cleared up immediately the block exits.

string source = "server=(local)\\NetSDK;" +
	"uid=QSUser;pwd=QSPassword;" +
	"database=Northwind";

	using ( SqlConnection conn = new SqlConnection ( source ) )