The ADO.Net Connection Object

Chris Kemp

Either in connected or disconnected mode, the first thing one needs to do is to connect to the database(s). This is accomplished in ADO.net by creating a connection object that points to the subject database.     The properties of the connection object are:  Connection string  A string used to connect to the database. Connection Timeout  The number of seconds till a connection times out (Read Only) Database  Returns the database name as specified in connection string (Read Only) DataSource  Returns the source attribute as specified in connection string (Read Only) ServerVersion  Returns version of connected server. State  Returns state of current database in integers. Values can be Closed, Connecting, Open, Executing, Fetching, Broken Provider  Returns the value of provider attribute as specified in connection string (Read Only) (OleDb Only) PacketSize  Returns size in bytes of network packets (SQL Server only) WorkstationID Identifies client, as specified in connection string (Read Only) In the above table, the only property that is NOT read only is the connection string. Some folks say that it is the connection string that is the most difficult aspect of ADO and ADO.Net. If so, it is an easily learned one. A typical connection string consists of 4 items: The Provider, which specifies the name of the underlying OLEDB provider. Appropriate values are SQLOLEDB (for SQLServer), Microsoft.Jet.OLEDB.4.0 (for Microsoft Access) and MSDORA (for Oracle); The Data Source attribute, which shows the location of the database. It can be a path on a network, or the IP address of a machine on the net; The UserID and Password, which grant access permission to the database; The Initial Catalog, which specifies the name of the database in the data source. Here are some common configurations: For SQL Server –  Data Source=Jupiter;Initial Catalog=pubs;User Id=ElmerFudd;Password=wabbitt; Server=Jupiter;Database=pubs;Trusted_Connection=True;Connection Timeout = 10 Data Source= 200.192.23.155;Network Library=Wiley3301;Initial Catalog=pubs;User ID=ElmerFudd;Password=wabbitt; C#:  using System.Data.SqlClient;  objqlConnection oSQLConn = new SqlConnection();oSQLConn.ConnectionString=connectstring;;oSQLConn.Open();  obj VB.NET: Imports System.Data.SqlClient Dim objSQLConn As SqlConnection = New SqlConnection() objSQLConn.ConnectionString="connectstring" objSQLConn.Open()  For Oracle:  Provider=OraOLEDB.Oracle ;Data Source=mydatabase;User Id=ElmerFudd;Password=wabbitt; Provider=OraOLEDB.Oracle;Data Source= mydatabase;OSAuthent=1;  C#:  using System.Data.OracleClient; OracleConnection objOracleConn = new OracleConnection(); objOracleConn.ConnectionString = my connectionstring; objOracleConn.Open();    VB.NET:  Imports System.Data.OracleClient Dim objOracleConn As OracleConnection = New OracleConnection() objOracleConn.ConnectionString = myconnectionstring objOracleConn.Open()  For MS Access:  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pathnamebiblio.mdb;User Id=ElmerFudd;Password=wabbitt;  Notice that the last instruction in the code using the method 'open()'. After the connection has been made, and the data retrieved, you need to close the connection using the connection method 'close()'. This should be done within an 'if' statement which first checks whether the connection is, in fact, open: If ( objConnection.state and ConnectionState.Open) 0 Then  objConnection.Close  End If  Note that the state property is '0' if the connection is already closed. Testing for a closed connection is necessary to prevent an error when you are invoking the 'close' method. The connection objects methods are:  Open Opens connection Close Closes connection BeginTransaction Begins database transaction ChangeDatabase Changes the name of database connected to CreateCommand Creates a command object GetOleDbSchemaTable Returns schema tables and associated restricted columns ReleaseObjectPool Shared method which allows closing of connection pool when last connection is closed   Exception Handling  All ADO connection procedures should be protected with a Try/Catch Block. When dealing with a connection to another server, this is especially important to let your users know that it was the connection that failed, rather than the application code.   Try connSQLNorthwind.ConnectionString = _ "Server=Jupiter;Database=pubs;Trusted_Connection=True;Connection Timeout = 10"  Catch ExSQL As System.Data.SqlClient.SqlException Dim strErrorMsg As String Dim strerror As System.Data.SqlClient.SqlError

For Each strerror In ExSQL.Errors

Select Case strerror.Number Case 17 strErrorMsg = "Missing server" Case 4060 strErrorMsg = "Missing database" Case 18456 strErrorMsg = "Missing user name or password" Case Else strErrorMsg = strerror.Message End Select

MessageBox.Show(sErrorMsg, "SQL Server Error: " & strerror.Number, MessageBoxButtons.OK MessageBoxIcon.Error) Next

 Catch ExcpInvOp As System.InvalidOperationException

MessageBox.Show("Close the connection first!", _ "Invalid Operation MessageBoxButtons.OK, MessageBoxIcon.Error)

Catch Excp As System.Exception ' generic exception handler

MessageBox.Show(Excp.Message, "Unexpected Exception MessageBoxButtons.OK, MessageBoxIcon.Error)

 End Try    Resources  • Information on ADO.Net Course This resource provides information on ADO.Net course  • Article on Microsoft .NET This is a useful resource that discusses .Net technology in detail.

Chris Kemp is a well knoen author who writes best quality articles on IT, Software, Programming, etc. For further details please visit the site www.paladn.com

Posted by World Best Articles.com :: 1:38 AM :: ---------------oOo---------------