SQL Server 的连接、查询与更新

#Region " 命名空间 " Imports System.Data Imports System.Data.SqlClient #End Region ''' <summary> ''' `连接基类` ''' </summary> ''' <remarks>2010-02-05</remarks> Public MustInherit Class BaseDBConnection Implements IDisposable #Region " 构造函数 " ''' <summary> ''' 构造函数 ''' </summary> ''' <param name="ConnectionString">连接字符串</param> ''' <remarks>2010-02-05</remarks> Public Sub New(ByVal ConnectionString As String) Open(ConnectionString) End Sub #End Region #Region " 成员变量 " ''' <summary> ''' 连接对象 ''' </summary> ''' <remarks>2010-02-05</remarks> Private _connection As SqlConnection ''' <summary> ''' 命令对象 ''' </summary> ''' <remarks>2010-02-05</remarks> Private _command As SqlCommand ''' <summary> ''' 事务对象 ''' </summary> ''' <remarks>2010-02-05</remarks> Private _transaction As SqlTransaction ''' <summary> ''' 连接字符串 ''' </summary> ''' <remarks>2010-02-05</remarks> Private _connectionString As String #End Region #Region " 成员属性 " ''' <summary> ''' 连接字符串 ''' </summary> ''' <value>字符串</value> ''' <returns>连接字符串</returns> ''' <remarks>2010-02-05</remarks> Protected ReadOnly Property ConnectionString() As String Get Return _connectionString End Get End Property ''' <summary> ''' 执行SQL语句的超时时间 ''' </summary> ''' <value>整形</value> ''' <returns>执行SQL语句的超时时间</returns> ''' <remarks>2010-02-06</remarks> Protected Property Timeout() As Integer Get Return _command.CommandTimeout End Get Set(ByVal value As Integer) _command.CommandTimeout = value End Set End Property ''' <summary> ''' 连接状态 ''' </summary> ''' <value>枚举</value> ''' <returns>连接状态</returns> ''' <remarks>2010-02-06</remarks> Protected ReadOnly Property ConnectionState() As ConnectionState Get Return _connection.State End Get End Property #End Region #Region " 过程函数 " ''' <summary> ''' 清空参数 ''' </summary> ''' <remarks>2010-02-05</remarks> Protected Sub ClearParameter() _command.Parameters.Clear() End Sub ''' <summary> ''' 添加参数 ''' </summary> ''' <param name="ParameterName">参数名</param> ''' <param name="SqlDbType">参数类型</param> ''' <param name="Value">值</param> ''' <remarks>2010-02-05</remarks> Protected Sub AddParameter( _ ByVal ParameterName As String, _ ByVal SqlDbType As SqlDbType, _ ByVal Value As Object) _command.Parameters.Add(ParameterName, SqlDbType).Value = Value End Sub ''' <summary> ''' 添加参数 ''' </summary> ''' <param name="ParameterName">参数名</param> ''' <param name="SqlDbType">参数类型</param> ''' <param name="Size">参数大小</param> ''' <param name="Value">值</param> ''' <remarks>2010-02-05</remarks> Protected Sub AddParameter( _ ByVal ParameterName As String, _ ByVal SqlDbType As SqlDbType, _ ByVal Size As Integer, _ ByVal Value As Object) _command.Parameters.Add(ParameterName, SqlDbType, Size).Value = Value End Sub ''' <summary> ''' 取得数据 ''' </summary> ''' <param name="dtResult">用于存放数据的结果集</param> ''' <param name="strSqlBun">SQL语句</param> ''' <remarks>2010-02-05</remarks> Protected Sub Fill(ByVal dtResult As DataTable, ByVal strSqlBun As String) Dim dataAdapter As SqlDataAdapter '数据适配器 '设置命令实例 _command.CommandText = strSqlBun _command.Transaction = _transaction '初始化数据适配器 dataAdapter = New SqlDataAdapter(_command) '取出数据放到结果集中 dataAdapter.Fill(dtResult) End Sub ''' <summary> ''' 执行SQL语句 ''' </summary> ''' <param name="strSqlBun">SQL语句</param> ''' <remarks>2010-02-06</remarks> Protected Sub ExecuteNonQuery(ByVal strSqlBun As String) '设置事务 _command.Transaction = _transaction '设置SQL语句并执行 _command.CommandText = strSqlBun _command.ExecuteNonQuery() End Sub ''' <summary> ''' 开始事务 ''' </summary> ''' <remarks>2010-02-06</remarks> Protected Sub BeginTransaction() _transaction = _connection.BeginTransaction() End Sub ''' <summary> ''' 提交事务 ''' </summary> ''' <remarks>2010-02-06</remarks> Protected Sub Commit() _transaction.Commit() End Sub ''' <summary> ''' 回滚事务 ''' </summary> ''' <remarks>2010-02-06</remarks> Protected Sub Rollback() _transaction.Rollback() End Sub ''' <summary> ''' 关闭数据库 ''' </summary> ''' <remarks>2010-02-05</remarks> Protected Sub Close() '判断连接对象是否为空 '如果为空的场合 If _connection Is Nothing Then Return End If '判断连接对象的状态 '如果状态不为关闭的场合 If Not _connection.State = ConnectionState.Closed Then _connection.Close() End If '释放连接资源 _connection.Dispose() _connection = Nothing End Sub ''' <summary> ''' 释放资源 ''' </summary> ''' <remarks>2010-02-05</remarks> Protected Sub Dispose() Implements System.IDisposable.Dispose Close() GC.SuppressFinalize(Me) End Sub ''' <summary> ''' 打开SQL数据库 ''' </summary> ''' <param name="ConnectionString">连接字符串</param> ''' <remarks>2010-02-05</remarks> Private Sub Open(Optional ByVal ConnectionString As String = "") '连接并打开SQL数据库 _connection = New SqlConnection(ConnectionString) _connection.Open() '创建命令对象 _command = _connection.CreateCommand End Sub #End Region End Class