ADO.NET 101: SQL Connection

• Download demo project - 64.2 Kb

On Connecting to SQL Databases

This article meant as a tutorial deals only with connecting to databases on a SQL2000 server. Various connection options are discussed and their implementation details are shown. Data retrieval and data manipulation will be discussed in a future article. Only very simple, but basic code to test the connection is used, and each case uses a different ASPX page. This article is self-contained and complete, a copy of the projects in zip format is provided for download.

Database support in ADO.NET

Data can be retrieved from a variety of databases which include the following:

• MS SQL Server 2000 - Enterprise
• MSDE - limited to 2GB
• MS Access - 2000 and other versions
• Exchange
• Windows Active Directory
• Oracle
• MySQL, etc.
• Flat files

Provider Types in ADO.NET

All though a variety of databases may be examined or manipulated, the basic data source providers are as shown in this screenshot of the Data window in the VS.NET IDE:

The basic supported types in the IDE are:

• SQL
• OleDB
• ODBC, and
• Oracle

Out of these, only the managed providers SQL and OleDB are provided under the ADO.NET scheme. .NET providers for other types such as ODBC and Oracle can be downloaded from other sources. Among these multiple ways of accessing the data, some are better than others.

SQLData provider is the fastest, if the database is on an SQL 2000 server. If it is a SQL 6.5 server, the .NET managed provider [SQLData provider] is not suited because it does not support SQL 6.5. ODBCData provider is more suited for relational databases, while OleDB works with a great many different types including, relational, and non-relational databases. Obviously, managed SQL data provider is much faster than OleDB data provider if you are using a SQL 2000 server database.

Although basically ADO.NET [out of the box] works with only two managed providers, OleDB providers are available in plenty for a great many databases, as seen here:

Consideration with regard to Connection Scenarios

Before any type of data access can be made, the most important step is to make a connection to the database. Given the various .NET providers, it is important to use the correct and appropriate "language of connection" which is really the correct construction of what is called the "ConnectionString". Simply stated, the ConnectionString consists of a set of database related information separated by semi-colons. This string describes briefly, on which database Server the Database is located, the name of the database queried, and whether or not correct authentication and permissions to view objects are in place, besides a few other details.

It is also important to consider other factors such as the location of the database relative to the calling application, whether it is a client/server or a multi-tier application, whether it is crossing operating system and network boundaries, etc.

Connection Examples

• Creating a Connection to the database using the Visual Studio IDE
• Creating a Connection using the SQLConnetion control in Tools
• Creating a ODBC Connection to the Server
• Creating an OleDb Connection
• Connecting to database on a Workgroup machine
• Connecting to the Database using code
• Connection using a Connection String stored in a Configuration File
• Connection using Dynamic Properties of the SQLConnection Control

Creating a Connection Using the Server Explorer

The connection procedure is explained using an example. A new web project called 'Connecting' is created and a web form called 'SQLConnect.aspx' is added after removing the default aspx page, as shown here. Also shown is the 'Server Explorer' that shows the 'Servers' as well as 'Data Connections'.

Right click on the 'Data Connections' node and pick up 'Add Connection' as shown here:

This brings up the 'Data Link Properties' window with objects in the 'Connection' tab exposed, where you would make appropriate selections, either by typing in, browsing, or making selection from drop-down boxes. First, you make a selection of a database server [SQL 2000 Server] from among many servers you might have. It is a good practice to 'refresh' the 'servers' before picking-one from the drop-down. In this example, a SQL 2000 Server on the local machine is addressed.

Next, you choose the method of 'logging-on' to this machine. It could be 'Windows Integrated Authentication', or it could be the one that requires a 'Username' and 'password' for authentication on the SQL 2000 server. Using Windows authentication is usually a much better option as Windows operating system offers superior capabilities as security features, auditing, monitoring etc. In this example, Windows authentication is used, which automatically 'grays-out' the username and password fields.

Finally, you choose the database from among the many databases you might find on this server. This database is sometimes called the 'initial catalog'. In this example, the 'pubs' database is chosen. Here, it is possible to test your connection as well.

When you click "OK" to the 'Data Link Properties', you will establish a connection to the database server as seen by the fully qualified name for this connection: XPHTEK.pubs.dbo [name of the server, name of the database, and the identity of the user].

Now, right click on the connection you established and look at its properties. This screenshot shows the properties for this connection:

The next screen shows the ConnectString [not the same as ConnectionString] for this connection:

If you now expand the nodes [by clicking on the + symbol] of the 'Connection', you will see the various objects available, such as tables, views, stored procedures, etc. as shown here. Here, the 'Tables' node is expanded to expand further the 'employee' sub-node to reveal the 'Columns'.

Dragging a Table From the Server Explorer to the Web Form

You can drag a table from the 'Server Explorer' to the 'Design' plane of the web form. This adds two objects [SQLConnection1 and SQLDataAdapter1] to the web form's tray, as shown here:

Now, right click the SQLConnection1 object to study its properties. The discussion of SQLDataAdapter1 will be in a future note. This brings up the Properties of the SQLConenction1 object as shown; the connection class is shown as System.Data.SqlClient.SqlConnection.

Here is a copy of the 'ConnectionString' property so produced. This is just a sub-set of the 'ConnectString' which we saw earlier.

The SQLConnect.aspx page has nothing more than this one connection. It has just a simple code in its Page-Load event as shown here, to verify that a connection is indeed made:

Private Sub Page_Load(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    SqlConnection1.Open()
    If SqlConnection1.State = ConnectionState.Open Then
        Label4.Text = "SQL Connection is open"
    Else
        Label4.Text = "SQL Connection is closed"
    End If
End Sub

The page as seen on IE is shown here:

Add a web page to the project called the 'SQLConnectionControl.aspx'. Drag a SQLConnection1 data control from the tool box. This adds 'SQLConnection' object to the tray below the 'Design' plane. Open up the properties for this control by right clicking this control.

The Properties window shows the various items, click on the 'ConnectionString' item as shown here, and click on '<New Connection...>'. Alternatively, you may choose an existing connection if you find it suitable. This brings up the 'Data Link Properties' dialogue as before.

For the sake of argument, we made yet another connection to the same database on the same server, but this is not a recommended practice as Connections are very valuable resources.

The SQLConnectionControl.aspx does not have much else except for this small code in its page_Load event, to verify that the connection was indeed made. In this code, a property of the connection object called the Connection State is used. Connection state with a value of 1 means connection is open, and 0 is for closed connections.

 Private Sub Page_Load(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    SqlConnection1.Open()
    Label3.Text = "Server Connection State= " & SqlConnection1.State
End Sub

The SQLConnectionControl.aspx page is rendered on IE as shown here:

Creating a ODBC DSN Using Database ODBC Data Source Administrator

ODBC [Open Data Base Connectivity] was extensively used in the past to connect to legacy databases, and there are a number of 'Providers' for this type of connection. In the Visual Studio IDE of this machine, you have seen earlier that there are ODBC and Oracle database objects besides the out of the box SQL and OLEDB type objects.

An ODBC connection can be established from within the Visual Studio IDE similar to the SQLConnection Control discussed earlier, or it is possible to create an ODBC connection using the Window's Control Panel interface [Start ->Control Panel->Administrative Tools ->Data Sources] as shown in this next screenshot. Using this tool, a new data source called 'ODBCSQL' was created as shown:

The details of this ODBC connection which really follows a 'Wizard' is shown here. It is also recommended to test this connection. The 'File DSN' so created is stored as 'ODBCSQL.dsn' and will be referred to as such in the Visual Studio IDE.

Adding a ODBCConnection Object to the Web page

Click on the ODBCConnection control in the Visual Studio's IDE and drop it on the web page [called ODBCSql.aspx]. As in the case of the SQLConnection object, an ODBC connection icon is added to the tray and its property window can be opened by right clicking this icon as shown:

Now, if you click on the Connection string item in this property window, a 'Select Data Source' window will open, showing the 'File Data Source' tab. Here in, you should find the 'ODBCSQL.dsn' you created earlier. Choosing this item by highlighting and clicking OK adds the necessary language for the connection string.

The ODBC connection is the only database related item on the ODBCSQL.aspx page. It has the following code in its Page_load event to verify that the connection is indeed made:

Private Sub Page_Load(ByVal sender As System.Object, _
         ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    OdbcConnection1.Open()
    Label3.Text = "Server Connection State=" & OdbcConnection1.State
End Sub

Create a web page, OleDBSQl.aspx, and drag the OleDBConnection object to the 'Design' plane. This adds an icon to the tray below the 'Design' plane, and its properties are accessed by right clicking this icon.

In the Properties window, clicking on the 'ConnectionString' item brings up the Data Link Properties window wherein you make appropriate choices similar to the one you made during setting up the connection string for the SqlConnection object. The connection string from this procedure is shown in the next screen shot. There appears to be no difference between this OleDbConnectionString and the ConnectString obtained using the 'Add Connection' routine discussed earlier.

Again, the OleDbSQL.aspx does not have any other controls except for a short code in the Page_load event to verify that the connection is working as shown here:

Private Sub Page_Load(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    OleDbConnection1.Open()
    Label3.Text = "Server Connection State=" & OleDbConnection1.State
End Sub

This page when opened in a browser shows the following:

The work group in this case involves a SQL 2000 Server on a machine called XPHTEK [the SQL 2000 Server is also named XPHTEK] on which the VS.NET has created a web application hosted on the IIS 5.0 server. The web page is trying to connect to a workgroup machine called NECHOST to access a SQL 2000 Server called NECHOST. The NECHOST SQL Server is registered on the XPHTEK's SQL Server. NECHOST and XPHTEK are connected by a router.

A web page called SQLWorkGroup.aspx is created on the localhost of the XPHTEK machine. A SQLConnection control is dropped on the 'Design' plane of this web form. As discussed previously, the SQLConnection Properties window is opened to configure the connection string. In the Data Link Properties window, the workgroup SQL Server NECHOST is chosen. The connection that requires a username and password is made. This shows the Data Link Properties page.

Although a SQL Server authentication is made, the connection string does not have the password information. Any attempt to connect to the server will fail, because the authentication information is insufficient.

The 'Allow Saving Password' can be checked in the Data Link Properties to allow saving the password but it will save connection string in clear text as shown by this option:

For the purposes of this example, the Connection String in the Properties window of SQLConnection1 was modified to include the password [hidden by xxxxx] in the text included in this page [not in the connection string]. This page has a short code in the page_load event to verify the connection is made.

Private Sub Page_Load(ByVal sender As System.Object, _
         ByVal e As System.EventArgs) Handles MyBase.Load
   'Put user code to initialize the page here
   SqlConnection1.Open()
   SqlConnection1.Close()
   Label2.Text = "Server Connetion State= " & SqlConnection1.State
End Sub
'Sqlconnection1.State=0 means connection closed.

This page when viewed using IE renders as follows:

A web page called 'SQLConnectvb.aspx' is added to the project. A textbox, a button, and a table with four labels with their text properties set to empty, are inserted into the table cells. The page should appear as shown:

This page has the following code:

Private Sub Button1_Click(ByVal sender As System.Object, _
           ByVal e As System.EventArgs) Handles Button1.Click
    Label1.Text = ""
    Label2.Text = ""
    Try
        Dim conStr As New SqlClient.SqlConnection
        conStr.ConnectionString = "workstation id=XPHTEK;" & _
          "packet size=4096;integrated security=SSPI;data source=XPHTEK;" & _
          "persist security info=false;" & "initial catalog= " & TextBox1.Text & _
          & ";"
        Response.Write("Connection string:  " & conStr.ConnectionString)
        conStr.Open()
        If conStr.State = ConnectionState.Open Then
            Label1.Text = "SQLConnection conStr is Open"
            conStr.Close()
        ElseIf conStr.State = ConnectionState.Closed Then
            Label1.Text = "SQLConnection conStr is closed"
        End If
    Catch sqlxcp As SqlClient.SqlException
        Label2.Text = sqlxcp.ToString
        Label3.Text = sqlxcp.Message
        Label4.Text = sqlxcp.Source
        Label5.Text = sqlxcp.Number
    Finally
    End Try
End Sub

A Try..Catch..Finally block is added to show how the exceptions are handled. Any database in the SQL 2000 Server can be connected as the value typed into the textbox is passed to the database name in the connection string. Valid database names are "pubs", "Northwind" etc. Any spelling errors or non-existent database should show up as exceptions, and details with regard to the Number, Message, and Source properties of the exception should be seen in the table cells. The next two screenshots show this page when a valid database name is entered, and when a invalid database name is entered.

Valid Database Name

An Invalid Database Name

Connection string information can be stored in an external, persisted file like a configuration file. ASP.NET has the web.config file for web applications. There are advantages to be gained by using a configuration file, like going over from a test setup to a production set up where the name of the server may change.

In this example, a connection string will be stored in the web.config file, and the code on the web page will use this setting to connect to a database.

Add a folder to your project called, DynConfig. To this folder, add a web page called, Dynamic.aspx. Also add a web.config file [Menu item Project->Add New Item]. Delete the default settings and add the following XML items as shown. This works on the basis of key, value pairs stored in the configuration file.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="books"
      value="Server=localhost;integrated security=SSPI;database=Biblio"/>
  </appSettings>
</configuration>

To the Dynamic.aspx page, add a textbox to show the connection string, a label to show the server state, and a button to initiate the connection as shown:

The code-behind of this page [Dynamic.aspx.vb] is as shown below:

Private Sub Button1_Click(ByVal sender As System.Object, _
             ByVal e As System.EventArgs) Handles Button1.Click
    Dim strBook As String
    strBook = ConfigurationSettings.AppSettings("books")
    Dim dynSQl As New SqlClient.SqlConnection
    dynSQl.ConnectionString = strBook
    dynSQl.Open()
    TextBox1.Text = strBook
    If dynSQl.State = 1 Then
        Label1.Text = "Server State is Open"
    Else
        Label1.Text = "Server State is Closed"
    End If
    dynSQl.Close()
End Sub
End Class

The "key" is retrieved and its value is passed to the variable strBook. The new SQLConnection uses this ConnectionString. Although this example has only one "key", multiple "key" value pairs can be stored and called dynamically.

This page is rendered on IE as shown, with the button click activated:

The web.config file stored information can also be used by placing a 'SQLConnection' control from the 'Tools'. In the Properties window of the SQLConnection control, you need to configure the DynamicProperties in the Configuration node. By clicking the ConnectionString item of this node, a dialog box pops up asking for 'Mapping' instruction between the "key" in the configuration file and SQLConnection1.ConnectionString. This is shown in the next screenshot together with the elements on this 'DynamicIDE.aspx' page on which the SQLConnection was configured.

This page has only a textbox for the connection string, a label for the server state information, and a button to initiate the activity. The rendered page on IE is shown here:

The code for this page is shown here:

 Private Sub Button1_Click(ByVal sender As System.Object, _
           ByVal e As System.EventArgs) Handles Button1.Click
    SqlConnection1.Open()
    TextBox1.Text = SqlConnection1.ConnectionString
    Label1.Text = "Server State is:" & SqlConnection1.State
End Sub

On Connection Pooling in SQL Connections

Connection pooling helps reduce the overhead of creating multiple connections when users try to access the same database for the same data in a multi-user intranet type of usage. Creating a pool and using dormant connections when not used, optimizes their use. For OLEDB, ODBC and OracleDB, the connection pooling is handled automatically by the provider. In the case of SQL connection, the connection pooling is managed implicitly. For a new connection which has the same connection string as an existing connection, no new pool is created, but for a connection string different from the existing one, a new pool is created. Each unique connection string creates a connection pool, and multiple connection objects are created and added to the pool so that a minimum pool requirement is satisfied. More connections are allowed till the maximum pool size is reached. Max pool size, Min Pool size values can be added to the connection string. A SQL connection request first tries to get a connection from the pool [assuming an unused, dormant one exists]. If no available connection exists, the new request will have to wait in a queue for the release of a usable connection. Connections are released when the close() or dispose() methods are called.