SQL Server connection, query, and update

# Region "namespace" </p> <p> Imports System. data <br/> Imports System. data. sqlClient </p> <p> # End Region </p> <p> ''' <summary> <br/> ''' connect to the base class' <br/> ''' </summary> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Public MustInherit Class BaseDBConnection <br/> Implements IDisposable </p> <p> # Region "constructor" </p> <p> ''' <summary> <br/> ''' constructor <br/> ''' </ summary> <br/> ''' <param name = "ConnectionString"> connection string </param> <br/> ''' <remarks> 2010-02-05 </remarks> <br /> Public Sub New (ByVal ConnectionString As String) <br/> Open (ConnectionString) <br/> End Sub </p> <p> # End Region </p> <p> # Region "member variable" </p> <p> ''' <summary> <br/> ''' connection object <br/> ''' </summary> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Private _ connection As SqlConnection </p> <p> ''' <summary> <br/> ''' command object <br/> ''' </summary> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Private _ command As SqlCommand </p> <p> ''' <summary> <br/> ''' transaction object <br/> ''' </summary> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Private _ transaction As SqlTransaction </p> <p> ''' <summary> <br/> ''' connection string <br/> ''' </summary> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Private _ connectionString As String </p> <p> # End Region </p> <p> # Region "member attributes" </p> <p> ''' <summary> <br/> ''' connection string <br/> ''' </summary> <br/> ''' <value> string </value> <br/> ''' <returns> connection string </returns> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Protected ReadOnly Property ConnectionString () as String </p> <p> Get <br/> Return _ connectionString <br/> End Get <br/> End Property </p> <p> ''' <summary> <br/> ''' timeout value for SQL statement execution <br/> ''' </summary> <br/> ''' <value> integer </value> <br/> ''' <returns> SQL statement execution timeout </returns> <br/> ''' <remarks> 2010-02-06 </remarks> <br/> protected Property Timeout () as Integer <br/> Get <br/> Return _ command. commandTimeout <br/> End Get <br/> Set (ByVal value As Integer) <br/> _ command. commandTimeout = value <br/> End Set <br/> End Property </p> <p> ''' <summary> <br/> ''' connection status <br/> ''' </summary> <br/> ''' <value> enumeration </value> <br/> ''' <returns> connection status </returns> <br/> ''' <remarks> 2010-02-06 </remarks> <br/> Protected ReadOnly Property ConnectionState () as ConnectionState <br/> Get <br/> Return _ connection. state <br/> End Get <br/> End Property </p> <p> # End Region </p> <p> # Region "process function" </p> <p> ''' <summary> <br/> ''' clear parameters <br/> ''' </summary> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Protected Sub ClearParameter () <br/> _ command. parameters. clear () <br/> End Sub </p> <p> ''' <summary> <br/> ''' add parameters <br/> ''' </summary> <br/> ''' <param name = "ParameterName"> parameter name </param> <br/> ''' <param name = "SqlDbType"> parameter type </param> <br/> ''' <param name = "Value"> Value </param> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> protected Sub AddParameter (_ <br/> ByVal ParameterName As String, _ <br/> ByVal SqlDbType As SqlDbType, _ <br/> ByVal Value As Object) </p> <p> _ command. parameters. add (ParameterName, SqlDbType ). value = Value </p> <p> End Sub <br/> ''' <summary> <br/> ''' add parameters <br/> ''' </ summary> <br/> ''' <param name = "ParameterName"> parameter name </param> <br/> ''' <param name = "SqlDbType"> parameter type </param> <br/> ''' <param name = "Size"> parameter Size </param> <br/> ''' <param name = "Value"> value </param> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Protected Sub AddParameter (_ <br/> ByVal ParameterName As String, _ <br/> ByVal SqlDbType As SqlDbType, _ <br/> ByVal Size As Integer, _ <br/> ByVal Value As Object) </p> <p> _ command. parameters. add (ParameterName, SqlDbType, Size ). value = Value </p> <p> End Sub </p> <p> ''' <summary> <br/>'' '</summary> <br/> ''' <param name = "dtResult"> result set used to store data </param> <br/> ''' <param name = "strSqlBun"> SQL statement </param> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Protected Sub Fill (ByVal dtResult As DataTable, byVal strSqlBun As String) </p> <p> Dim dataAdapter As SqlDataAdapter 'data adapter </p> <p> 'sets the command instance <br/> _ command. commandText = strSqlBun <br/> _ command. transaction = _ transaction </p> <p> 'initialize the data adapter <br/> dataAdapter = New SqlDataAdapter (_ command) </p> <p> 'PUT THE retrieved data into the result set. <br/> dataAdapter. fill (dtResult) </p> <p> End Sub </p> <p> ''' <summary> <br/> ''' run the SQL statement. <br/> ''' </summary> <br/> ''' <param name = "strSqlBun"> SQL statement </param> <br/> ''' <remarks> 2010-02-06 </remarks> <br/> Protected Sub ExecuteNonQuery (ByVal strSqlBun As String) </p> <p> 'SET transaction <br/> _ command. transaction = _ transaction </p> <p> 'sets the SQL statement and runs <br/> _ command. commandText = strSqlBun <br/> _ command. executeNonQuery () </p> <p> End Sub </p> <p> ''' <summary> <br/> ''' start transaction <br/> ''' </ summary> <br/> ''' <remarks> 2010-02-06 </remarks> <br/> Protected Sub BeginTransaction () </p> <p> _ transaction = _ connection. beginTransaction () </p> <p> End Sub </p> <p> ''' <summary> <br/> ''' commit transactions <br/> ''' </ summary> <br/> ''' <remarks> 2010-02-06 </remarks> <br/> Protected Sub Commit () </p> <p> _ transaction. commit () </p> <p> End Sub </p> <p> ''' <summary> <br/> ''' roll back the transaction <br/> ''' </summary> <br/> ''' <remarks> 2010-02-06 </remarks> <br/> Protected Sub Rollback () </p> <p> _ transaction. rollback () </p> <p> End Sub </p> <p> ''' <summary> <br/> ''' shut down the database. <br/> ''' </ summary> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Protected Sub Close () </p> <p> 'determines whether the connection object Is null <br/>' If it Is null <br/> If _ connection Is Nothing Then <br/> Return <br/> End If </p> <p> 'judge the status of the connection object <br/>' If the status is Not closed <br/> If Not _ connection. state = ConnectionState. closed Then <br/> _ connection. close () <br/> End If </p> <p> 'release connection resource <br/> _ connection. dispose () <br/> _ connection = Nothing </p> <p> End Sub </p> <p> ''' <summary> <br/> ''' release resources <br/> ''' </summary> <br/> ''' <remarks> 2010-02-05 </remarks> <br/> Protected Sub Dispose () implements System. IDisposable. dispose </p> <p> Close () <br/> GC. suppressFinalize (Me) </p> <p> End Sub </p> <p> ''' <summary> <br/> ''' open the SQL database. <br/> ''' </ summary> <br/> ''' <param name = "ConnectionString"> connection string </param> <br/> ''' <remarks> 2010-02-05 </remarks> <br /> Private Sub Open (Optional ByVal ConnectionString As String = "") </p> <p> 'connect to and open the sqldatabase <br/> _ connection = New SqlConnection (ConnectionString) <br/> _ connection. open () </p> <p> 'create command object <br/> _ command = _ connection. createCommand </p> <p> End Sub </p> <p> # End Region </p> <p> End Class