我也是初学者,我也有些过类似的东东,拿出来请高手帮我看看吧
Public Class BDatabaseAccess
Protected objConnection As SqlClient.SqlConnection 'Object to connect SQL Server
Protected objCommand As SqlClient.SqlCommand 'Object to run SQL statment
Protected objTransaction As SqlClient.SqlTransaction 'Object to commit transaction
Public objReader As SqlClient.SqlDataReader 'Object to send data out
Protected strErrText As String 'String to record error
Protected bIsClosed As Boolean 'Boolean to denote whether connection is closed
Protected bIsCommited As Boolean 'Boolean to denote whether transaction is commited 'Constructors 'Construct the Database Access with default Connection text
Public Sub New()
Dim strConnection As String = "user id=PIV;password=PIV;"
strConnection += "database=PIZZA.NET;server=chocobo;"
strConnection += "Connect Timeout=30"
Try
objConnection = New SqlClient.SqlConnection(strConnection)
objCommand = New SqlClient.SqlCommand()
Catch e As Exception
Finally
End Try bIsClosed = True
bIsCommited = True End Sub 'Call the constructor by a User defined connection text
Public Sub New(ByVal strIn As String)
objConnection = New SqlClient.SqlConnection(strIn)
objCommand = New SqlClient.SqlCommand()
bIsClosed = True
End Sub 'Run the function before run INSERT statement
'It is not needed when connecting to Pizza.Net
Public Function SetInsert(ByVal strTableName As String) As Boolean
Return RunSQL("SET IDENTITY_INSERT " + strTableName + " ON", False)
End Function 'Run the function to Run SQL statment as string
'If your SQL statment is a SELECT statment then
' Denote bSelect True and get your result from the public menber objReader as SqlClient.SqlDataReader
' After you get the data from objReader run the memberfunction CloseConnection()
'end if
'You can also call RunSQL(strSQL as string) to run NonSelect statements
Public Function RunSQL(ByVal strSQL As String) As Boolean
Return RunSQL(strSQL, False)
End Function
'The main function of Database Access
'Function to run SQL statement
Public Function RunSQL(ByVal strSQL As String, ByVal bSelect As Boolean) As Boolean
If bIsCommited = False Or bIsClosed = False Then
Try
CloseConnection()
Catch e As Exception
strErrText = e.Message
objConnection.Close() 'Haven't been tested
' Return False End Try
End If
If objConnection.State <> ConnectionState.Closed Then objConnection.Close()
Try
objConnection.Open()
objTransaction = objConnection.BeginTransaction()
objCommand.Connection = objConnection
objCommand.Transaction = objTransaction
objCommand.CommandText = strSQL
bIsCommited = False
If bSelect = True Then
objReader = objCommand.ExecuteReader()
bIsClosed = False
Else
objCommand.ExecuteNonQuery()
objTransaction.Commit()
bIsCommited = True
End If
Catch e As Exception If bIsClosed = False Then
objReader.Close()
bIsClosed = True
End If
If objConnection.State <> ConnectionState.Closed Then objTransaction.Rollback()
strErrText = e.Message
objConnection.Close()
Return False
Finally
End Try
If bSelect = False Then
objConnection.Close()
End If
Return True
End Function 'Function to close connection
'Run it befor you begin a new transaction when you just ran a SELECT statment Function CloseConnection()
If bIsClosed = False Then
objReader.Close()
bIsClosed = True
End If
If bIsCommited = False Then
objTransaction.Commit()
bIsCommited = True
End If End Function 'Function to get error text Function GetLastErr() As String
Return strErrText
End Function 'Function to run Select Function just return whether there is some result
'Return: -1 Not Found -2 Connection Error >=1 Number of Found Function Find(ByVal strSQL As String) As Integer
If RunSQL(strSQL, True) = False Then Return -2
If Me.objReader.Read() = False Then
Me.CloseConnection()
Return -1
End If
Dim i As Integer = 0
While (Me.objReader.Read() = True)
i += 1
End While
Me.CloseConnection()
Return i + 1
End Function 'Distructor
Protected Overrides Sub Finalize()
If bIsCommited = False Or bIsClosed = False Then CloseConnection()
MyBase.Finalize()
End SubEnd Class
Public Class BDatabaseAccess
Protected objConnection As SqlClient.SqlConnection 'Object to connect SQL Server
Protected objCommand As SqlClient.SqlCommand 'Object to run SQL statment
Protected objTransaction As SqlClient.SqlTransaction 'Object to commit transaction
Public objReader As SqlClient.SqlDataReader 'Object to send data out
Protected strErrText As String 'String to record error
Protected bIsClosed As Boolean 'Boolean to denote whether connection is closed
Protected bIsCommited As Boolean 'Boolean to denote whether transaction is commited 'Constructors 'Construct the Database Access with default Connection text
Public Sub New()
Dim strConnection As String = "user id=PIV;password=PIV;"
strConnection += "database=PIZZA.NET;server=chocobo;"
strConnection += "Connect Timeout=30"
Try
objConnection = New SqlClient.SqlConnection(strConnection)
objCommand = New SqlClient.SqlCommand()
Catch e As Exception
Finally
End Try bIsClosed = True
bIsCommited = True End Sub 'Call the constructor by a User defined connection text
Public Sub New(ByVal strIn As String)
objConnection = New SqlClient.SqlConnection(strIn)
objCommand = New SqlClient.SqlCommand()
bIsClosed = True
End Sub 'Run the function before run INSERT statement
'It is not needed when connecting to Pizza.Net
Public Function SetInsert(ByVal strTableName As String) As Boolean
Return RunSQL("SET IDENTITY_INSERT " + strTableName + " ON", False)
End Function 'Run the function to Run SQL statment as string
'If your SQL statment is a SELECT statment then
' Denote bSelect True and get your result from the public menber objReader as SqlClient.SqlDataReader
' After you get the data from objReader run the memberfunction CloseConnection()
'end if
'You can also call RunSQL(strSQL as string) to run NonSelect statements
Public Function RunSQL(ByVal strSQL As String) As Boolean
Return RunSQL(strSQL, False)
End Function
'The main function of Database Access
'Function to run SQL statement
Public Function RunSQL(ByVal strSQL As String, ByVal bSelect As Boolean) As Boolean
If bIsCommited = False Or bIsClosed = False Then
Try
CloseConnection()
Catch e As Exception
strErrText = e.Message
objConnection.Close() 'Haven't been tested
' Return False End Try
End If
If objConnection.State <> ConnectionState.Closed Then objConnection.Close()
Try
objConnection.Open()
objTransaction = objConnection.BeginTransaction()
objCommand.Connection = objConnection
objCommand.Transaction = objTransaction
objCommand.CommandText = strSQL
bIsCommited = False
If bSelect = True Then
objReader = objCommand.ExecuteReader()
bIsClosed = False
Else
objCommand.ExecuteNonQuery()
objTransaction.Commit()
bIsCommited = True
End If
Catch e As Exception If bIsClosed = False Then
objReader.Close()
bIsClosed = True
End If
If objConnection.State <> ConnectionState.Closed Then objTransaction.Rollback()
strErrText = e.Message
objConnection.Close()
Return False
Finally
End Try
If bSelect = False Then
objConnection.Close()
End If
Return True
End Function 'Function to close connection
'Run it befor you begin a new transaction when you just ran a SELECT statment Function CloseConnection()
If bIsClosed = False Then
objReader.Close()
bIsClosed = True
End If
If bIsCommited = False Then
objTransaction.Commit()
bIsCommited = True
End If End Function 'Function to get error text Function GetLastErr() As String
Return strErrText
End Function 'Function to run Select Function just return whether there is some result
'Return: -1 Not Found -2 Connection Error >=1 Number of Found Function Find(ByVal strSQL As String) As Integer
If RunSQL(strSQL, True) = False Then Return -2
If Me.objReader.Read() = False Then
Me.CloseConnection()
Return -1
End If
Dim i As Integer = 0
While (Me.objReader.Read() = True)
i += 1
End While
Me.CloseConnection()
Return i + 1
End Function 'Distructor
Protected Overrides Sub Finalize()
If bIsCommited = False Or bIsClosed = False Then CloseConnection()
MyBase.Finalize()
End SubEnd Class
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货