Читать книгу Visual Basic 2010 Coding Briefs Data Access - Kevin Ph.D. Hough - Страница 6

Managing the Database Connections

In order to manage the database connections, we must be able to make a connection to the database, and to disconnect from the database.

In Coding Briefs, we don’t want to keep a persistent connection to the database. Instead, we want to open the database, perform our select or execute action, and then close the database.

Opening a Database Connection

To perform an action against the database, we have to open a connection to the database. When we open a database connection, we can access the objects of the database by making calls to the database connection.

The following code is used to open a database connection.

Listing 1: Opening a Database Connection

Public Sub DBConnect(

ByVal connectionString As String,

ByRef errorParms() As Object)

Try

SqlConn =

New SqlConnection(connectionString)

SqlConn.Open()

Catch

' Return the error to the calling program.

errorParms(0) = 999

errorParms(1) = Err.Description

errorParms(2) =

System.Reflection.MethodBase.GetCurrentMethod.Name

End Try

End Sub

The code shown in Listing 1 opens a connection to the database.

The procedure DBConnect accepts one parameter, the connection string, and thereby allows us to open different databases based on the connection string that is passed to DBConnect.

A SqlConnection object represents a unique session to a SQL Server data source, and it is used together with a SqlDataAdapter and a SqlCommand to increase performance when connecting to a Microsoft SQL Server database.

•First, we set the variable SQLConn to a SQLConnection, and use the connection string that is passed to the procedure

•Next, we open the connection with the Open command

Closing a Database Connection

When a SqlConnection goes out of scope, it won't be closed, so we have to explicitly close the connection by calling Close or Dispose. Close and Dispose will both close a connection to an open database;however, Close is the preferred method of closing any open connection.

The code shown below in Listing 2 can be used to close a database connection.

Listing 2: Closing a Database Connection

Public Sub DBDisconnect(ByRef errorParms As Object)

Try

SqlConn.Close()

SqlConn = Nothing

SqlCmd = Nothing

Catch

' Return the error to the calling program.

errorParms(0) = 999

errorParms(1) = Err.Description

errorParms(2) =

System.Reflection.MethodBase.

GetCurrentMethod.Name

End Try

End Sub

The code in Listing 2 closes the database connection and sets the connection and the command objects to nothing, therefore, releasing the resources. Managing database connections is an important part of a good data access framework.

In the next section, we will develop the code that we will use to select records in Coding Briefs.