一个简单的数据库操作类,主要有以下几个公有函数:
ExecuteSQL
SQLBeginTransaction
getDataSetFromSQL
getDataTableFromSQL
getDataReaderFromSQL
CloseConnection
具体代码如下:
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Namespace BOYLEE
Public Class clsAdoNet
Private SqlCon As SqlConnection
Private SqlCmd As SqlCommand
Private SqlDta As SqlDataAdapter
Private SqlTrs As SqlTransaction

Public Sub New()
Try
Me.SqlCon = New SqlConnection
Me.SqlCon.ConnectionString = getConSql()
Catch
HttpContext.Current.Response.Write("数据库连接失败")
HttpContext.Current.Response.End()
End Try
End Sub

Private Function getConSql() As String
Dim strCon As String
Dim strDataSource As String
Dim strDataBase As String
Dim strUser As String
Dim strPassword As String
Dim strConnection As New System.Text.StringBuilder
strDataSource = ConfigurationSettings.AppSettings("DB_SERVER")
strDataBase = ConfigurationSettings.AppSettings("DB_DATABASE")
strUser = ConfigurationSettings.AppSettings("DB_USER")
strPassword = ConfigurationSettings.AppSettings("DB_PASSWORD")
With strConnection
.Append("server=")
.Append(strDataSource)
.Append(";uid=")
.Append(strUser)
.Append(";pwd=")
.Append(strPassword)
.Append(";database=")
.Append(strDataBase)
End With
strCon = strConnection.ToString
Return strCon
End Function

Public Function ExecuteSQL(ByVal strSQL As String) As Integer
Dim intRows As Integer
Me.SqlCmd = New SqlCommand
Me.SqlCmd.CommandText = strSQL
Try
Me.SqlCon.Open()
Me.SqlCmd.Connection = Me.SqlCon
intRows = Me.SqlCmd.ExecuteNonQuery()
Catch
HttpContext.Current.Response.Write("SQL语句执行不成功!")
HttpContext.Current.Response.End()
intRows = -1
Finally
If (Me.SqlCon.State.ToString.Equals("Open")) Then
Me.SqlCon.Close()
End If
End Try
Return intRows
End Function

Public Function SQLBeginTransaction(ByVal strSQL As String()) As Boolean
Dim tmpFlag As Boolean
Dim intI As Integer
Dim strLen As Integer

strLen = strSQL.Length
Me.SqlCmd = New SqlCommand
Try
Me.SqlCon.Open()
Me.SqlTrs = Me.SqlCon.BeginTransaction()
Me.SqlCmd.Connection = Me.SqlCon
Me.SqlCmd.Transaction = Me.SqlTrs
For intI = 0 To strLen - 1
Me.SqlCmd.CommandText = strSQL(intI)
Me.SqlCmd.ExecuteNonQuery()
Next
Me.SqlTrs.Commit()
tmpFlag = True
Catch
If Not Me.SqlTrs Is Nothing Then
Me.SqlTrs.Rollback()
End If
HttpContext.Current.Response.Write("SQL语句执行不成功!")
HttpContext.Current.Response.End()
tmpFlag = False
Finally
If (Me.SqlCon.State.ToString.Equals("Open")) Then
Me.SqlCon.Close()
End If
End Try
Return tmpFlag
End Function

Public Function getDataSetFromSQL(ByVal strSQL As String) As DataSet
Dim dSet As New DataSet
Try
Me.SqlCon.Open()
Me.SqlDta = New SqlDataAdapter(strSQL, SqlCon)
Me.SqlDta.Fill(dSet, 0)
Catch
dSet = Nothing
HttpContext.Current.Response.Write("数据查询失败!")
HttpContext.Current.Response.End()
Finally
If (Me.SqlCon.State.ToString.Equals("Open")) Then
Me.SqlCon.Close()
End If
End Try
Return dSet
End Function

Public Function getDataTableFromSQL(ByVal strSQL As String) As DataTable
Dim dSet As New DataSet
Dim dTbl As New DataTable
Try
Me.SqlCon.Open()
Me.SqlDta = New SqlDataAdapter(strSQL, SqlCon)
Me.SqlDta.Fill(dSet, "TempTable")
dTbl = dSet.Tables("TempTable")
Catch
dSet = Nothing
dTbl = Nothing
HttpContext.Current.Response.Write("数据查询失败!")
HttpContext.Current.Response.End()
Finally
If (Me.SqlCon.State.ToString.Equals("Open")) Then
Me.SqlCon.Close()
End If
End Try
Return dTbl
End Function

Public Function getDataReaderFromSQL(ByVal strSQL As String) As SqlDataReader
Dim DataRead As SqlDataReader
Me.SqlCmd = New SqlCommand
Me.SqlCmd.CommandText = strSQL
Try
Me.SqlCon.Open()
Me.SqlCmd.Connection = Me.SqlCon
DataRead = Me.SqlCmd.ExecuteReader
Catch
HttpContext.Current.Response.Write("数据查询失败!")
HttpContext.Current.Response.End()
Finally
If (Me.SqlCon.State.ToString.Equals("Open")) Then
Me.SqlCon.Close()
End If
End Try
Return DataRead
End Function

Public Function CloseConnection() As Boolean
Dim SqlConStatus As Boolean = False
If (Me.SqlCon.State.ToString.Equals("Open")) Then
Me.SqlCon.Close()
SqlConStatus = True
End If
Return SqlConStatus
End Function
End Class
End Namespace

Back