DBClass~
来源:互联网 发布:dbaccess 执行sql 编辑:程序博客网 时间:2024/05/16 04:48
<Serializable()> _
Public Class clsDBConn
//REPETITION_KEY ="2627"
//REFERENCE_KEY ="547"
Private m_conn As System.Data.SqlClient.SqlConnection
Private m_da As System.Data.SqlClient.SqlDataAdapter
Private m_tran As System.Data.SqlClient.SqlTransaction
Private strConn As System.Data.SqlClient.SqlConnection
Public Sub New()
Dim connectionString As String = ""
connectionString = GblConnectString()
m_conn = New System.Data.SqlClient.SqlConnection(connectionString)
End Sub
Public Sub New(ByVal strDBName As String)
Dim connectionString As String = ""
connectionString = GblConnectString(strDBName)
m_conn = New System.Data.SqlClient.SqlConnection(connectionString)
End Sub
Public Function GblConnectString() As String
GblConnectString = "User ID=" & ConfigurationManager.AppSettings.Get("gUserID") & _
";Password=" & ConfigurationManager.AppSettings.Get("gPass") & _
";Initial Catalog=" & ConfigurationManager.AppSettings.Get("sDataBase") & _
";Data Source=" & ConfigurationManager.AppSettings.Get("sServerName")
End Function
Public Function GblConnectString(ByVal strDBName As String) As String
GblConnectString = "User ID=" & ConfigurationManager.AppSettings.Get("gUserID") & _
";Password=" & ConfigurationManager.AppSettings.Get("gPass") & _
";Initial Catalog=" & strDBName & _
";Data Source=" & ConfigurationManager.AppSettings.Get("sServerName")
End Function
Public ReadOnly Property Connection() As System.Data.SqlClient.SqlConnection
Get
Return m_conn
End Get
End Property
Public Sub Open()
Try
m_conn.Open()
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
End Sub
Public Sub Close()
Try
If Me.IsConnected Then
If Me.InTransaction Then
Me.Rollback()
End If
m_conn.Close()
End If
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
End Sub
Public Sub BeginTransaction()
Try
m_tran = m_conn.BeginTransaction()
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
End Sub
Public Sub Commit()
Try
m_tran.Commit()
m_tran.Dispose()
m_tran = Nothing
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
End Sub
Public Sub Rollback()
Try
m_tran.Rollback()
m_tran.Dispose()
m_tran = Nothing
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
End Sub
Public ReadOnly Property IsConnected() As Boolean
Get
Return (m_conn.State = ConnectionState.Open)
End Get
End Property
Public ReadOnly Property InTransaction() As Boolean
Get
Return (Not m_tran Is Nothing)
End Get
End Property
Public Function ExecuteSql(ByVal pv_strSql As String, ByVal flag As Integer) As Integer
Try
Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
objCmd.Connection = m_conn
objCmd.Transaction = m_tran
objCmd.CommandType = CommandType.Text
Return objCmd.ExecuteNonQuery()
Catch ex As System.Data.SqlClient.SqlException
Throw ex
Catch ex As Exception
Throw ex
End Try
End Function
Public Function ExecuteSql(ByVal pv_strSql As String) As Integer
Dim r As Integer = -1
Try
Me.Open()
Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
objCmd.Connection = m_conn
objCmd.CommandType = CommandType.Text
r = objCmd.ExecuteNonQuery()
Me.Close()
Return r
Catch ex As System.Data.SqlClient.SqlException
If Me.IsConnected Then
Me.Close()
End If
Throw ex
Catch ex As Exception
If Me.IsConnected Then
Me.Close()
End If
Throw ex
End Try
End Function
Public Function ExecuteSql(ByVal pv_strSql As String, ByRef strFlag As String) As Integer
Try
Me.Open()
Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
objCmd.Connection = m_conn
objCmd.CommandType = CommandType.Text
ExecuteSql = objCmd.ExecuteNonQuery()
Me.Close()
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
End Function
Public Function ExecuteSql(ByVal pv_strSql As String, ByRef strFlag As String, ByVal intFlag As Integer) As Integer
Try
Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
objCmd.Connection = m_conn
objCmd.Transaction = m_tran
objCmd.CommandType = CommandType.Text
ExecuteSql = objCmd.ExecuteNonQuery()
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
End Function
Public Function QuerySql(ByVal strSql As String, ByRef m_dt As DataTable) As Boolean
Try
m_da = New System.Data.SqlClient.SqlDataAdapter()
Dim objCmd As New System.Data.SqlClient.SqlCommand(strSql)
objCmd.Connection = m_conn
objCmd.Transaction = m_tran
objCmd.CommandType = CommandType.Text
m_da.SelectCommand = objCmd
m_da.Fill(m_dt)
Return True
Catch ex As System.Data.SqlClient.SqlException
Throw ex
Catch ex As Exception
Throw ex
End Try
End Function
Public Function QuerySql(ByVal strSql As String, ByRef m_dt As DataTable, ByVal flag As Integer) As Boolean
Try
m_da = New System.Data.SqlClient.SqlDataAdapter()
Dim objCmd As New System.Data.SqlClient.SqlCommand(strSql)
objCmd.Connection = m_conn
objCmd.Transaction = m_tran
objCmd.CommandType = CommandType.Text
m_da.SelectCommand = objCmd
m_da.Fill(m_dt)
Return True
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
End Function
Public Function QuerySql(ByVal strSql As String, ByRef m_dt As DataTable, ByRef err As String) As Boolean
QuerySql = True
Try
Me.Open()
m_da = New System.Data.SqlClient.SqlDataAdapter(strSql, m_conn)
m_da.Fill(m_dt)
Me.Close()
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
End Function
Public Function QuerySqlPage(ByVal strSql As String, ByRef m_dt As DataTable, ByVal flag As Integer) As Boolean
Try
m_da = New System.Data.SqlClient.SqlDataAdapter()
Dim objCmd As New System.Data.SqlClient.SqlCommand(strSql)
objCmd.Connection = m_conn
objCmd.Transaction = m_tran
objCmd.CommandType = CommandType.Text
m_da.SelectCommand = objCmd
m_da.Fill(flag, 100, m_dt)
Return True
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
End Function
Public Function getConnectString() As String
Dim strCon As String = "User ID=" & ConfigurationManager.AppSettings.Get("gUserID") & _
";Password=" & ConfigurationManager.AppSettings.Get("gPass") & _
";Initial Catalog=" & "master" & _
";Data Source=" & ConfigurationManager.AppSettings.Get("sServerName")
Return strCon
End Function
Public Function EtSql(ByVal pv_strSql As String) As String
Dim r As String = "-1"
Try
Me.Open()
Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
objCmd.Connection = m_conn
objCmd.CommandType = CommandType.Text
r = objCmd.ExecuteNonQuery()
Me.Close()
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
Return r
End Function
Public Function ExSql(ByVal pv_strSql As String) As String
Dim dt As New Data.DataTable
Dim Sql As String
Sql = "select spid from master..sysprocesses where dbid=db_id('" & ConfigurationManager.AppSettings.Get("sDataBase") & "')"
Dim r As String = "-1"
strConn = New System.Data.SqlClient.SqlConnection(getConnectString())
Dim dsr As New System.Data.SqlClient.SqlDataAdapter(Sql, strConn)
Dim ds As New DataSet
dsr.Fill(ds)
If strConn.State = ConnectionState.Closed Then
strConn.Open()
End If
Try
Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
objCmd.Connection = strConn
objCmd.CommandType = CommandType.Text
For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
objCmd.CommandText = "KILL " & ds.Tables(0).Rows(i)(0)
r = objCmd.ExecuteNonQuery()
Next
objCmd.CommandText = pv_strSql
r = objCmd.ExecuteNonQuery()
Me.Close()
Catch ex As System.Data.SqlClient.SqlException
If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
Throw ex
Else
If Not IsNothing(m_conn) Then
Me.Close()
End If
System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
End If
End Try
Return r
End Function
End Class
- DBClass~
- 自用DBClass SQLServer数据库操作类
- Preparing Hardware and Software for Use with AutoPlay
- 郭敬明的最伤感经典125句
- 2009ACM多校联合暑期集训(2)——TJU专场
- MS-SQLServer 2000 T-SQL 交叉报表(行列互换) 交叉查询 旋转查询
- Flex与JOSN和XML
- DBClass~
- 取得运行目录与当前脚本目录
- javascript 不停(setInterval)/延时(setTimeout)函数
- BGP的NEXT-HOP-SELF试验
- 遭“画皮门”试图移花接木 用友U9明战SAP
- 世界上主流PLC
- CNBC:aiCache助我轻松应对每日2亿5千万条实时股票行情信息
- 运行出错,
- 按钮的重绘代码