Connections using the SQL Server .NET Data Provider

Connection Syntax

First up, creating a connection to a data store requires the instantiation of a connection object. Code for doing this is characterized by three features:

• The namespaces containing the appropriate ADO.NET classes must be imported
• A string variable to hold some information necessary for making the connection is created and filled
• The connection object is instantiated

Then, once a connection object has been created, the code that makes use of the connection tends to look like this:

• The connection is opened
• Using the connection, data is read from or written to the data store, or a SQL statement is executed
• The connection is closed

Returning to the start of that list, you won't be surprised to discover that the ADO.NET classes, like all of the classes in the .NET Framework class library, are organized into namespaces. A few moments ago, we saw that ADO.NET provides three ways to access data: the SQL Server .NET data provider, the OLE DB .NET data provider, and the ODBC .NET data provider. Accordingly, there are three namespaces, as follows:

• System.Data.SqlClient contains the classes that comprise the SQL Server .NET data provider
• System.Data.OleDb contains the classes that comprise the OLE DB .NET data provider
• System.Data.ODBC contains the classes that comprise the ODBC .NET data provider (note that, as already mentioned, this provider must be downloaded and installed separately)

In addition to these, we'll almost always find ourselves making use of the System. Data namespace, which contains a large number of classes and other entities that form the 'backbone' of ADO.NET. The classes in this namespace are not specific to a particular data provider.

Note: In ADO.NET, the word "provider" has been overloaded. In this section, we've been talking about .NET data providers, which are entirely synonymous with the namespaces that implement them - System.Data.OleDb is the OLE DB .NET data provider. The other kind of provider is the one that appears in the architecture diagrams, and will appear in our connection strings. These providers handle the specific task of translating data from its format in the data source, to the format in which it's manipulated by ADO.NET SQL Connection Syntax Earlier in the chapter, we talked about the three programmatic steps necessary to create an ADO.NET connection object. In summary, these were: Import the appropriate namespaces Create and fill a connection string Instantiate the connection object Starting at the top of that list, writing an ASP.NET page that connects to a SQL Server database requires classes from two .NET Framework namespaces: the high-level, more general System.Data (which we'll use in every example we consider), and the task-specific System.Data.SqlClient. In other words, every SQL Server-accessing ASPX file you ever write will start like this: <%@ Import namespace="System.Data" %>; <%%@ Import namespace="System.Data.SqlClient" %>;     Next, in order to create a connection object, we need first to specify a connection string. When it comes to creating a connection object for a SQL Server database (and therefore for an MSDE database), we need to provide at least three pieces of information: The name of the server that holds our database The name of the database that we want to use Whether the connection to the database we're using is secure These requirements result in the creation of a connection string that looks something like this, where the items in italics will be replaced with specific values in actual code: String strConnection = "server=MyServer; database=MyDatabase; " & _ "integrated security=/ false>;" Finally, once we have the connection string, we can create the connection object. For SQL Server, this object will be an instance of the System.Data.SqlClient.SqlConnection class, and its creation is as easy as this: SqlConnection objConnection = New SqlConnection(strConnection)   Once created, using a SqlConnection object is a matter of opening it, performing operations on it, and closing it. While that's not our focus in this chapter, it would be hard to prove that everything's working properly without doing something with the data to which we've gained access, so we'll do precisely that in the following example.