Teach Yourself Visual Studio® .NET 2003 in 21 Days [Electronic resources] نسخه متنی

Connecting to a Database

To work with any database, the first thing you must do is connect to it. In ADO.NET, you use the Connection object to connect to a database. When accessing SQL Server, the SqlConnection class of the System.Data.SqlClient namespace contains the connection object that you use to connect to a SQL Server database. The OleDbConnection class of the System.Data.OleDb namespace contains the connection object for OLE DB data sources.

When connecting to a database, you must specify the server that the database resides on, the database name, and the authentication information for the database. There are several variations of the connection string, so you might see different syntax based on who wrote the connection string code and the type of authentication used if the database is SQL Server.

The following code gives you an example of each of the following types of connection string options:

• Connecting to a Microsoft Access database using the OleDbConnection class




• Connecting to a .NET Framework software development kit (SDK) sample Microsoft data engine (MSDE) database with integrated security




• Connecting to a SQL Server database with integrated security




• Connecting to a SQL Server database passing the user ID and password in the connection string

' Connect to a Microsoft Access database using the
' System.Data.OleDb.OleDbConnection class
Dim strOleDb As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=..\Northwind.mdb"
Dim cnOleDb As New OleDbConnection()
cnOleDb.ConnectionString = strOleDb
cnOleDb.Open()
' Connect to the MSDE SDK database using  the
' System.Data.SqlClient.SqlConnection class
' with Integrated Security
Dim strMSDE As String = _
"Server=(local)\NetSDK;DataBase=Northwind;" _
& "Integrated Security=SSPI"
Dim cnMSDE As New SqlConnection()
cnMSDE.ConnectionString = strMSDE
cnMSDE.Open()
' Connect to a local SQL Server database using  the
' System.Data.SqlClient.SqlConnection class
' with Integrated Security
Dim strSQL As String = _
"Server=localhost;DataBase=Northwind;" & _
"Integrated Security=SSPI"
Dim cnSQL As New SqlConnection()
cnSQL.ConnectionString = strSQL
cnSQL.Open()
' Connect to a local SQL Server database using  the
' System.Data.SqlClient.SqlConnection class
' passing userid and password in the connection string
Dim strSQL1 As String = _
"Server=localhost;DataBase=Northwind;" & _
"uid=sa;pwd=password"
Dim cnSQL1 As New SqlConnection()
cnSQL1.ConnectionString = strSQL1
cnSQL1.Open()

// Connect to a Microsoft Access database using the
// System.Data.OleDb.OleDbConnection class
string strOleDb;
strOleDb =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\Northwind.mdb";
OleDbConnection cnOleDb = new OleDbConnection();
??? Author added parenthesis after OleDbConnection - Jason
cnOleDb.ConnectionString = strOleDb;
cnOleDb.Open();
// Connect to the MSDE SDK database using  the
// System.Data.SqlClient.SqlConnection class
// with Integrated Security
string strMSDE;
strMSDE =
@"Server=(local)\NetSDK;DataBase=Northwind;Integrated Security=SSPI";
SqlConnection cnMSDE = new SqlConnection;()
??? Author: added parenthesis after SqlConnection
cnMSDE.ConnectionString = strMSDE;
cnMSDE.Open();
// Connect to a local SQL Server database using  the
// System.Data.SqlClient.SqlConnection class
// with Integrated Security
string strSQL;
strSQL =
@"Server=localhost;DataBase=Northwind;Integrated Security=SSPI";
sqlConnection cnSQL = new SqlConnection();
??? Author: added parenthesis after SqlConnection
cnSQL.ConnectionString = strSQL;
cnSQL.Open();
// Connect to a local SQL Server database using  the
// System.Data.SqlClient.SqlConnection class
// passing userid and password in the connection string
string strSQL1;
strSQL1 = @"Server=localhost;DataBase=Northwind;uid=sa;pwd=";
SqlConnection cnSQL1 = new SqlConnection();
cnSQL1.ConnectionString = strSQL1;
cnSQL1.Open();

The connection string differences are not that noticeable. You can see the difference between OleDbConnection and SqlConnection is that with SQL Server, you pass the server name (in this case, localhost), the database name, and either the integrated security constant or the actual user ID and password. With the OleDbConnection, you specify the physical path to the database, and most importantly, the OLE DB provider name. Based on the type of OLE DB provider you're using, the connection string might vary. For example, using Microsoft Access, you must specify the physical path to the database. If you're using Oracle or SQL Server 6.5, you must specify a connection string similar to SQL 7.0 or SQL 2000.

Table 10.1 lists the common properties of a SqlConnection object and an OleDbConnection object.

Tip

When you open a Connection object, you must always explicitly close it. Calling Close or Dispose on a Connection object ensures that the connection is sent back to the connection pool. Connection pooling is automatic in .NET, so you gain performance by closing connections and letting .NET send the connection back to the pool. When you attempt to create a new connection, resources are consumed on the server. To avoid the overhead of creating a new connection for each call on the database server, the .NET managed providers keep a pool of connections available for the next connection attempt to the server, thus avoiding the overhead of connection creation on the server. To keep you honest, the ExecuteReader method of the Command object actually enables you to close the connection with the CommandBehavior.CloseConnection parameter when you execute the command.

The SqlConnection and OleDbConnection classes are overloaded, so you can also pass the connection string when you create the connection as the following Visual Basic .NET code demonstrates:

Dim cn As New SqlConnection( _
"Server=localhost;DataBase=Northwind;" _
& "uid=sa;pwd=password")

Note

In the remainder of the examples, you use the .NET Framework SDK MSDE database that's installed with the Quick Start tutorials that come with Visual Studio .NET. If you haven't installed the Quick Start tutorials that are part of the .NET Framework SDK, navigate to \Program Files\Microsoft Visual Studio .NET\FrameworkSDK\StartHere and follow the instructions on how to install the SDK files.

Now that you can connect to a database, you can start writing some code that retrieves data.

Note

You'll be using the System.Data.SqlClient namespace and the System.Data.OleDb namespace in the code you write for the rest of the day. Make sure that you remember to alias these namespaces with the Imports or using statement at the top of your class files as you follow along with the code examples.