SQL Server 的串連、查詢與更新

#Region " 命名空間 "</p><p>Imports System.Data<br />Imports System.Data.SqlClient</p><p>#End Region</p><p>''' <summary><br />''' `串連基類`<br />''' </summary><br />''' <remarks>2010-02-05</remarks><br />Public MustInherit Class BaseDBConnection<br /> Implements IDisposable</p><p>#Region " 建構函式 "</p><p> ''' <summary><br /> ''' 建構函式<br /> ''' </summary><br /> ''' <param name="ConnectionString">連接字串</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 " 成員變數 "</p><p> ''' <summary><br /> ''' 連線物件<br /> ''' </summary><br /> ''' <remarks>2010-02-05</remarks><br /> Private _connection As SqlConnection</p><p> ''' <summary><br /> ''' 命令對象<br /> ''' </summary><br /> ''' <remarks>2010-02-05</remarks><br /> Private _command As SqlCommand</p><p> ''' <summary><br /> ''' 事務對象<br /> ''' </summary><br /> ''' <remarks>2010-02-05</remarks><br /> Private _transaction As SqlTransaction</p><p> ''' <summary><br /> ''' 連接字串<br /> ''' </summary><br /> ''' <remarks>2010-02-05</remarks><br /> Private _connectionString As String</p><p>#End Region</p><p>#Region " 成員屬性 "</p><p> ''' <summary><br /> ''' 連接字串<br /> ''' </summary><br /> ''' <value>字串</value><br /> ''' <returns>連接字串</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 /> ''' 執行SQL語句的逾時時間<br /> ''' </summary><br /> ''' <value>整形</value><br /> ''' <returns>執行SQL語句的逾時時間</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 /> ''' 串連狀態<br /> ''' </summary><br /> ''' <value>枚舉</value><br /> ''' <returns>串連狀態</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 " 過程函數 "</p><p> ''' <summary><br /> ''' 清空參數<br /> ''' </summary><br /> ''' <remarks>2010-02-05</remarks><br /> Protected Sub ClearParameter()<br /> _command.Parameters.Clear()<br /> End Sub</p><p> ''' <summary><br /> ''' 添加參數<br /> ''' </summary><br /> ''' <param name="ParameterName">參數名</param><br /> ''' <param name="SqlDbType">參數類型</param><br /> ''' <param name="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 /> ''' 添加參數<br /> ''' </summary><br /> ''' <param name="ParameterName">參數名</param><br /> ''' <param name="SqlDbType">參數類型</param><br /> ''' <param name="Size">參數大小</param><br /> ''' <param name="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 /> ''' 取得資料<br /> ''' </summary><br /> ''' <param name="dtResult">用於存放資料的結果集</param><br /> ''' <param name="strSqlBun">SQL語句</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 '資料配接器</p><p> '設定命令執行個體<br /> _command.CommandText = strSqlBun<br /> _command.Transaction = _transaction</p><p> '初始化資料配接器<br /> dataAdapter = New SqlDataAdapter(_command)</p><p> '取出資料放到結果集中<br /> dataAdapter.Fill(dtResult)</p><p> End Sub</p><p> ''' <summary><br /> ''' 執行SQL語句<br /> ''' </summary><br /> ''' <param name="strSqlBun">SQL語句</param><br /> ''' <remarks>2010-02-06</remarks><br /> Protected Sub ExecuteNonQuery(ByVal strSqlBun As String)</p><p> '設定事務<br /> _command.Transaction = _transaction</p><p> '設定SQL語句並執行<br /> _command.CommandText = strSqlBun<br /> _command.ExecuteNonQuery()</p><p> End Sub</p><p> ''' <summary><br /> ''' 開始事務<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 /> ''' 提交事務<br /> ''' </summary><br /> ''' <remarks>2010-02-06</remarks><br /> Protected Sub Commit()</p><p> _transaction.Commit()</p><p> End Sub</p><p> ''' <summary><br /> ''' 復原事務<br /> ''' </summary><br /> ''' <remarks>2010-02-06</remarks><br /> Protected Sub Rollback()</p><p> _transaction.Rollback()</p><p> End Sub</p><p> ''' <summary><br /> ''' 關閉資料庫<br /> ''' </summary><br /> ''' <remarks>2010-02-05</remarks><br /> Protected Sub Close()</p><p> '判斷連線物件是否為空白<br /> '如果為空白的場合<br /> If _connection Is Nothing Then<br /> Return<br /> End If</p><p> '判斷連線物件的狀態<br /> '如果狀態不為關閉的場合<br /> If Not _connection.State = ConnectionState.Closed Then<br /> _connection.Close()<br /> End If</p><p> '釋放串連資源<br /> _connection.Dispose()<br /> _connection = Nothing</p><p> End Sub</p><p> ''' <summary><br /> ''' 釋放資源<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 /> ''' 開啟SQL資料庫<br /> ''' </summary><br /> ''' <param name="ConnectionString">連接字串</param><br /> ''' <remarks>2010-02-05</remarks><br /> Private Sub Open(Optional ByVal ConnectionString As String = "")</p><p> '串連並開啟SQL資料庫<br /> _connection = New SqlConnection(ConnectionString)<br /> _connection.Open()</p><p> '建立命令對象<br /> _command = _connection.CreateCommand</p><p> End Sub</p><p>#End Region</p><p>End Class