一 数据连接
Public sqlconn As SqlClient.SqlConnection ' 全局变量，供整个应用程序使用
Sub open_conn()
Dim sqlconnStr As String
Try
sqlconnStr = " workstation id= " + DbHost + " ;packet size=4096;data source = " sqlserver主机 " ;persist security info=true;initial catalog = dbName; user id = userName; password =userPwd sqlconn = New SqlClient.SqlConnection(sqlconnStr)
Catch ex As Exception
MsgBox (ex.ToString)
End Try
' MsgBox("数据库连接准备完成")
End Sub
二 日志记录类
' 系统日志类
' 该类供应用各个模块调用，记录用户的登陆，退出，对数据库的操作，以及运行过程中系统的异常的记录。
' 该类的成员变量为结构体loginfo的一个实例
Public Class cLogWrite
Private myLogInfo As New logInfo ’定义的一个结构体类型的公共变量，包含用户，日志类型，主机，和日‘志内容
Public Sub setUserId( ByVal myId As String )
myLogInfo.user_id = myId
End Sub
Public Sub setLogType( ByVal myType As String )
myLogInfo.log_type = myType
End Sub
Public Sub setLogContent( ByVal myContent As String )
myLogInfo.log_content = myContent
End Sub
Public Sub setLogMachine()
myLogInfo.log_machine = System.Net.Dns.GetHostName
End Sub
Public Sub insertLog()
Dim mysqlcomm As SqlClient.SqlCommand
Dim mysql As String
mysql = " insert into wjb_log(log_datetime,log_type,user_id,log_content,log_machine) values (current_timestamp,' " + myLogInfo.log_type + " ',' " + myLogInfo.user_id + " ',' " + myLogInfo.log_content + " ',' " + myLogInfo.log_machine + " ') "
Try
sqlconn.Open()
mysqlcomm = New SqlClient.SqlCommand(mysql, sqlconn)
mysqlcomm.ExecuteNonQuery()
sqlconn.Close()
Catch ex As Exception
If sqlconn.State.Open = 1 Then
sqlconn.Close()
End If
Throw ex
Exit Sub
End Try
End Sub
End Class
三 数据库操作
' 该类为数据处理类，定义了所有对数据库的操作
' 该类为所有功能模块提供对数据库数据的操作接口，select、insert、update、delete
Public Class cDataProcess
' 该方法为各功能模块 查询数据库中的数据提供结果集，输入select sql语句，和操作的数据库表，返回相应表的结果集
Dim myLogWrite As New cLogWrite
Public Function dataSelect( ByVal strSql As String , ByVal strTableName As String ) As DataSet
Dim mydataset As New DataSet
Dim mySqlAdapter As SqlClient.SqlDataAdapter
Try
sqlconn.Open()
mySqlAdapter = New SqlClient.SqlDataAdapter(strSql, sqlconn)
mySqlAdapter.Fill(mydataset, strTableName)
sqlconn.Close()
mySqlAdapter = Nothing
' 该处记录查询的日志信息，日志类型为：用户操作，用户内容包括查询语句，查询的表
' myLogWrite.setLogType("用户操作")
' myLogWrite.setUserId(userStruc.userid)
' myLogWrite.setLogMachine()
' myLogWrite.setLogContent("用户通过 " + strSql + " 查询表 " + strTableName)
' myLogWrite.insertLog()
Catch ex As Exception
sqlconn.Close()
' 该处记录日志信息，日志类型为操作异常
myLogWrite.setLogType( " 系统异常 " )
myLogWrite.setUserId(userStruc.userid)
myLogWrite.setLogMachine()
myLogWrite.setLogContent( "" + ex.Message) ' "用户查询表 " + strTableName + " 时发生异常：
myLogWrite.insertLog()
Throw ex
End Try
Return mydataset
End Function
' 该方法为各功能模块提供插入数据操作，参数为：insert语句和表名
Public Sub dataInsert( ByVal strSql As String , ByVal strTableName As String )
Dim mysqlcomm As SqlClient.SqlCommand
Try
sqlconn.Open()
mysqlcomm = New SqlClient.SqlCommand(strSql, sqlconn)
mysqlcomm.ExecuteNonQuery()
sqlconn.Close()
mysqlcomm = Nothing
' 该处记录日志信息，日志类型为：用户操作，用户内容包括insert语句，操作的表
myLogWrite.setLogType( " 用户操作 " )
myLogWrite.setUserId(userStruc.userid)
myLogWrite.setLogMachine()
myLogWrite.setLogContent( " 用户向表 " + strTableName + " 中插入数据 " )
myLogWrite.insertLog()
Catch ex As Exception
If sqlconn.State.Open = 1 Then
sqlconn.Close()
End If
' 该处记录日志信息，日志类型为操作异常
myLogWrite.setLogType( " 系统异常 " )
myLogWrite.setUserId(userStruc.userid)
myLogWrite.setLogMachine()
myLogWrite.setLogContent( "" + escapeZifu(ex.Message, " ' " )) ' 用户向表 " + strTableName + " 中插入数据时发生异常：
myLogWrite.insertLog()
Throw ex
End Try
End Sub
' 该方法为各功能模块提供修改数据操作，参数为：update语句和表名
Public Sub dataUpdate( ByVal strSql As String , ByVal strTableName As String )
Dim mysqlcomm As SqlClient.SqlCommand
Try
sqlconn.Open()
mysqlcomm = New SqlClient.SqlCommand(strSql, sqlconn)
mysqlcomm.ExecuteNonQuery()
sqlconn.Close()
mysqlcomm = Nothing
' 该处记录日志信息，日志类型为：用户操作，用户内容包括insert语句，操作的表
myLogWrite.setLogType( " 用户操作 " )
myLogWrite.setUserId(userStruc.userid)
myLogWrite.setLogMachine()
myLogWrite.setLogContent( " 用户更改表 " + strTableName + " 的记录 " )
myLogWrite.insertLog()
Catch ex As Exception
If sqlconn.State.Open = 1 Then
sqlconn.Close()
End If
' 该处记录日志信息，日志类型为操作异常
myLogWrite.setLogType( " 系统异常 " )
myLogWrite.setUserId(userStruc.userid)
myLogWrite.setLogMachine()
myLogWrite.setLogContent( "" + escapeZifu(ex.Message, " ' " )) ' 用户更改表 " + strTableName + " 中记录时发生异常：
myLogWrite.insertLog()
Throw ex
End Try
End Sub
' 该类为各功能模块提供删除数据的操作，参数为delete语句，和要删除的表
Public Sub dataDelete( ByVal strSql As String , ByVal strTableName As String )
Dim mysqlcomm As SqlClient.SqlCommand
Try
sqlconn.Open()
mysqlcomm = New SqlClient.SqlCommand(strSql, sqlconn)
mysqlcomm.ExecuteNonQuery()
sqlconn.Close()
mysqlcomm = Nothing
' 该处记录日志信息，日志类型为：用户操作，用户内容包括insert语句，操作的表
myLogWrite.setLogType( " 用户操作 " )
myLogWrite.setUserId(userStruc.userid)
myLogWrite.setLogMachine()
myLogWrite.setLogContent( " 用户删除表 " + strTableName + " 中的记录 " )
myLogWrite.insertLog()
Catch ex As Exception
If sqlconn.State.Open = 1 Then
sqlconn.Close()
End If
' 该处记录日志信息，日志类型为操作异常
myLogWrite.setLogType( " 系统异常 " )
myLogWrite.setUserId(userStruc.userid)
myLogWrite.setLogMachine()
myLogWrite.setLogContent( "" + escapeZifu(ex.Message, " ' " )) ' 用户删除表 " + strTableName + " 中记录时发生异常：
myLogWrite.insertLog()
Throw ex
End Try
End Sub
' 该函数目的去掉字符串中的符号
Public Function escapeZifu( ByVal str As String , ByVal zifu As String ) As String
Dim istart As Int32
istart = str .Trim.IndexOf(zifu)
While istart <> - 1
str = str .Substring( 0 , istart) + str .Substring(istart + 1 , str .Length - istart - 1 )
istart = str .IndexOf(zifu)
End While
Return str
End Function
End Class
四 调用方法
自己构造sql语句，和相关的表名
通过参数传递进去即可，这样将大大减少数据库操作方面的工作量。