Connecting to the Database

I l @ ve RuBoard

You can start working with ADO.NET by creating a new .NET project. First, launch Visual Studio.NET. Then, select "New Project" on the start screen. Choose "Visual C# Projects" for the project type, and "ASP.NET Web Application" for the template. Name the project "ado_net_by_example", and click "Ok". At this point, Visual Studio will create the initial project structure. Select the Project, Add Web Form menu command, and name the web form SqlCommand.aspx. Add a Label control to the Web Form by double-clicking the Label icon in the toolbox. In the Solution Explorer, right-click on SqlCommand.aspx, and select "View Code". You are now in the position to start writing ADO.NET code.

The first step in working with the database is establishing a connection. Add the code in Listing 4.1 to the Page_Load method.

Listing 4.1 Connecting to a SQL database.

 // // Connect to the database // SqlConnection cn = new SqlConnection(     "server=localhost;" +     "database=pubs;" +     "uid=sa;" +     "password="); cn.Open(); 

As you can see, the constructor to the connection object takes a connection string as an argument. This connection string contains all the information needed to connect to a specific database. In this case, the connection string specifies the network name of the database server, the actual database on that server, and the authentication information. A connection to the database is actually established when the open method is called.

You should also note the data type of the connection object. Here, we are using something called a " SqlConnection ". This object is specific for talking to SQL Server databases. The other connection object available is the OleDbConnection object, which is built on top of the legacy ActiveX Data Objects.

This concept of having database-specific connection objects falls under the Microsoft term "Managed Provider."

ADO.NET does away with the "one size fits all" approach. Instead, it is preferable to create an instance of a connection object that is specific to the database that you want to connect to. The database-specific managed providers allow you to take advantage of functionality that is specific to a database. Table 4.3 lists some of the SQL specific properties of the SqlConnection class.

Table 4.3. SqlConnection Properties and Methods ”Specific to SqlConnection

Properties/Methods	Description
ServerVersion	Gets the version number of the SQL Server that you are connected to
IsolationLevel	The SQL Server transaction isolation level
SaveTransaction	Saves a point in the transaction that can be rolled back to

The other advantage of database-specific providers is performance. If you go through ODBC, or OLE-DB, you are going through some generic middleware that attempts to make all databases look the same. This saves you from having completely different APIs for all the databases, but at the expense of performance. If, on the other hand, you use the SqlConnection object to talk to SQL Server, it will talk to SQL Server in a direct, efficient way, without any middleware layer. For this reason, you should always use a database-specific provider when possible rather than the generic OleDb provider.

I l @ ve RuBoard