Using Database Connections C# Help

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 familiar with the .NET connection classes: 01 DbConnect ion and Sql Connection, Figure 26-1 shows two of the connection classes and includes the class hierarchy.

Figure 26-1

This is a significant change from .NET versions 1.0 and 1.1;however, in practice, using the connection class (and other classes in ADO.NET) is backward compatible.

The examples in this chapter use theNorthwind database, which is installed with the .NET Framework SDKsamples, The following code snippet illustrates how to create, open, and close a connection to the Northwind database:

The connection string should be very familiar to you if you have used ADO or OLEDBbefore – 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) – This denotes the database server to connect to. SQLServer permits a number of separate database server instances to be running on the same machine, and here you are connecting to the default SQLServer instance, If you are using SQL Express, change the server part to server=. /sqlexpress.

¤ integrated security=SSPI – This uses Windows Authentication to connect to the database,N which is highly recommended over using a username and password within the source code.

¤ database=Northwind – This describes the database instance to connect to; each SQLServer process can expose several database instances.

In case you forget the format of database connection strings (as many of us do now
and then), the following URl is very handy: www.csharpaid.com.

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 are finished, the connection can be closed.

SQLServer has another mode of authentication – it can use Windows-integrated security, so that the credentials supplied at logon are passed to SQLServer, This is accomplished by removing the uid and pwd portions of the connection string, and adding in Integrated Securi ty=SSPI.

In the download code available for this chapter, you will find the file Login cs, which 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.

Managing Connection Strings

In the initial release of .NET,it was up to the developer to manage the database connection strings, often done by storing a connection string in the application configuration file or, more commonly, hard-coded somewhere within the application itself.

With .NET 2.0, you now have a predefined way to store connection strings, and even use database connections in a type-agnostic manner – for example, it would now be possible to write an application and then plug in various database providers, all without altering the main application.

To define a database connection string, you should use the <connectionStrings> section of the configuration file. Here, you can specify a name for the connection and the actual database connection string parameters; in addition, you can also specify the provider for this connection type. Here is an example:

You same connection string in the other examples.

Once the database connection information has been defined within the configuration file,you then need to utilize this within the application, You will most likely want to create a method such as the foIlowing to retrieve a database connection based on the name of the connection:

This code reads the named connection string section (using the ConnectioonStringSettings class), and then requests a provider factory from the base DbProviderFactories class, This uses the Provider Name property, which was set to System. Data. SqlClient’ in the application configuration file, You might be wondering how this maps to the actual factory class used to generate a database connection for SQLServer- in this case, it should utilize the SqlClientFactory class from System .Data. SqlClient. Youwill need to add a reference to the System. Configuration assembly in order to resolve the ConfigurationManager class used in the preceding code.

If you look into the machine. config file for .NET2.0,you may notice the DbProviderFactories section- this maps the alias names (such as ‘System. Data. SqlClient’) to the factory object for that type of database, The following shows an abridged copy of the information within that file:

This just shows the entry for the SqlClient provider – there are other entries for Odbc,OleDb,Oracle, and also SqlCE.

So, in the example,the DbProviderFactory class just looks up the factory class from the machine configuration settings, and uses that concrete factory class to instantiate the connection object, In the case of the SqlClientFactory class, all this does is construct an instance of SqlConnection and return this to the caller.

This may seem like a lot of unnecessary work to obtain a database connection, and indeed it is if your application is never going to run on any other database than the one it was designed for If, however, you use the preceding factory method and also use the generic Db· classes (such as DbConnection, DbCominand, and DbDataReader), you will future-proof the application, and any move in the future to another database system will be fairly simple.

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 release resources as early as possible to avoid starvation of resources.

This is all too apparent when writing code that accesses a database because 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, hurting application performance considerably, Closing database connections should be considered mandatory, so this section shows how to structure your code to minimize the risk of leaving a resource open.

You have 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 use try …catch…finally blocks, and ensure that you close any open connections within the finally block, Here is a short example:

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 the finally clause, so something less prone to vagaries in coding style might be worthwhile.

Also, you might 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#, the debate on how .NET uses nondeterministic destruction became very heated.

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 because the destructor was the ideal place to close the resource if the user had forgotten to do so. A c++ destructor is called whenever an object goes out of scope – so, for instance, if an exception was raised and not caught, all destructors would be called.

With C# and the other managed languages, there is no concept of automatic, deterministic destruction. Instead, there is the garbage collector, which disposes 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.

In this instance, the using clause ensures that the database connection is closed, regardless of how the block is exited.

Looking at the IL code for the Dispose () me~d of the connection classes, all of them check the current state of the connection object, and if open will call the Close () method, A great tool for browsing .NET assemblies is Reflector (available at www.csharpaid.com), This tool permits you to view the IL code for any .NET method and will also reverse-engineer the IL into C# source code, so you can easily see what a given method is doing.

When programming, you should use at least one of these methods, and probably both. Wherever you acquire resources, it is good practice to use the using statement; even though we all mean to write the Close () statement, sometimes we forget, and in the face of exceptions the using clause does the right thing, There is no substitute for good exception handling either, so in most instances, it is best to use both methods together, as in the following example:

Note that this example called Close (),which isn’t strictly necessary, because the using clause will ensure that this is done anyway However, you should ensure that any resources such as this are released as soon as possible – you might have more code in the rest of the block, and there is no point locking a resource unnecessarily.

In addition, if an exception is raised within the using block, the IDisposable. Dispose method will be called on the resource guarded by the using clause, which in this example ensures that the database connection is always closed, This produces easier-to-read code than having to ensure you close a connection within an exception clause, You might also note that the exception is defined as a SqlException rather than the catch-all Exception type – always try to catch as specific an exception as possible and let all others that are not explicitly handled rise up the execution stack.

In conclusion, if you are writing a class that wraps a resource, whatever that resource may be, always implement the IDisposable interface to close the resource, That way anyone coding with your class can use the using () statement and guarantee that the resource will be cleared up.

Often when there is more than one update to be made to the database, these updates must be performed within the scope of a transaction, It is common in code to find a transaction object being passed around to many methods that update the database, however in .NET 2.0 and above the TransactionScope class has been added which is defined within the System Transactions assembly, This can vastly simplify writing transactional code because you can compose several transactional methods within a transaction scope, and the transaction will be flowed to each of these methods as necessary.

The following sequence of code initiates a transaction on a SQLServer connection:

Here the transaction is explicitly marked as complete by using the scope. Complete () method, In the absence of this call, the transaction will be rolled back so that no changes are made to the database.

When you use a transaction scope you can optionally choose the isolation level for commands executed within that transaction, The level determines how changes made in one database session are viewed by another, Not all database engines support all of the four levels presented in the following table.

The SQLServer default isolation level, ReadCom:nit ted, is a good compromise between data coherence and data availability because fewer locks are required on data than in RepeatableRead or Serializable modes, However, situations exist where the isolation level should be increased, and so within .NET you can simply begin a transaction with a different level from the default. There are no hard-and-fast rules as to which levels to pick – that comes with experience.

If you are currently using a database that does not support transactions, it is well worth changing to a database that does. Once I was working as a trusted employee and had been given complete access to the bug database, I typed what I thought was delete from bug where id=99999, but in fact had typed a < rather than an =. I deleted the entire database of bugs (except the one I wanted to!), Luckily for me, our IS team backed up the database on a nightly basis and we could restore this, but a rollback command would have been much easier.