数据操作的封装--sqlhelper

Imports System.Data.SqlClient Imports System.Configuration Imports System.Data '需要在管理器中添加引用 Public Class sqlHelper '定义变量 Dim ConnString As String = "Server=.;Database=Charge;User=sa;PassWord=123456" '获得数据库连接字符串 Private ReadOnly strConnection As String = ConfigurationSettings.AppSettings("ConnString") Dim conn As SqlConnection = New SqlConnection(strConnection) '定义CMD命令 Dim cmd As New SqlCommand ''' <summary> ''' 执行查询操作（有参数），参数没有限制 ''' </summary> ''' <param name="cmdText">需要执行的语句，一般是SQL语句，也可能是存储过程</param> ''' <param name="cmdType">判断SQL语句的类型，一般不是存储过程</param> ''' <param name="sqlparameters">传入参数</param> ''' <returns></returns> ''' <remarks></remarks> Public Function ParaSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal sqlparameters As SqlParameter()) As DataTable ' Using conn As New SqlConnection(ConnString) ' Dim sqlAdapter As SqlDataAdapter ' Dim dt As New DataTable ' Dim ds As New DataSet ' '给CMD赋值 cmd.CommandText = cmdText cmd.CommandType = cmdType cmd.Connection = conn cmd.Parameters.AddRange(sqlparameters) '添加参数 sqlAdapter = New SqlDataAdapter(cmd) '实例化adapter Try sqlAdapter.Fill(ds) '用adapter将dataSet填充 dt = ds.Tables(0) 'datatable为dataSet的第一个表 cmd.Parameters.Clear() '清除参数 Catch ex As Exception '抛出异常 MsgBox("查询失败", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "警告") Finally Call CloseCmd(cmd) '销毁cmd命令 End Try Return dt End Using End Function ''' <summary> ''' 执行查询操作（无参数） ''' </summary> ''' <param name="cmdText">同上</param> ''' <param name="cmdType">同上</param> ''' <returns>dataTable查询到表格</returns> ''' <remarks></remarks> Public Function NonParaSelect(cmdText As String, cmdType As CommandType) As DataTable Using conn As New SqlConnection(ConnString) Dim sqlAdapter As SqlDataAdapter Dim ds As New DataSet cmd.CommandText = cmdText cmd.CommandType = cmdType cmd.Connection = conn sqlAdapter = New SqlDataAdapter(cmd) Try sqlAdapter.Fill(ds) Return ds.Tables(0) '抛出异常，无返回值 Catch ex As Exception Return Nothing Finally Call CloseCmd(cmd) '关闭CMD命令 End Try End Using End Function ''' <summary> ''' 执行增、删、改操作（有参数），使用Integer作为返回值类型，0操作失败，1操作成功 ''' </summary> ''' <param name="cmdText">需要执行的语句</param> ''' <param name="cmdType">判断SQL语句类型</param> ''' <param name="sqlParameter">参数数组，参数没有限制</param> ''' <returns></returns> ''' <remarks></remarks> Public Function ParaDataManager(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal sqlParameter As SqlParameter()) As Integer '使用Using关键字实例化连接字符串，给cmd赋值 Using conn As New SqlConnection(ConnString) cmd.Parameters.AddRange(sqlParameter) cmd.CommandType = cmdType '设置一个值，解释cmdText cmd.Connection = conn '设置连接，全局变量 cmd.CommandText = cmdText '设置查询语句 Try conn.Open() '打开连接 Return cmd.ExecuteNonQuery '执行操作 cmd.Parameters.Clear() '清除参数 Catch ex As Exception '抛出异常 Return 0 Finally Call CloseConn(conn) Call CloseCmd(cmd) End Try End Using End Function ''' <summary> ''' 执行增、删、改操作（无参数） ''' </summary> ''' <param name="cmdType">同上</param> ''' <param name="cmdText">同上</param> ''' <returns></returns> ''' <remarks></remarks> Public Function NonParaDataManager(ByVal cmdType As CommandType, ByVal cmdText As String) As Integer '使用Using 关键字实例化连接字符串 Using conn As New SqlConnection(ConnString) cmd.CommandText = cmdText '设置查询语句 cmd.CommandType = cmdType '设置SQL语句类型 cmd.Connection = conn '设置连接 Try '执行操作 conn.Open() Return cmd.ExecuteNonQuery '返回值 Catch ex As Exception Return 0 '抛出异常，返回0表示操作失败 Finally Call CloseCmd(cmd) Call CloseConn(conn) End Try End Using End Function ''' <summary> ''' 关闭连接 ''' </summary> ''' <param name="conn">需要关闭的连接 </param> ''' <remarks></remarks> Public Sub CloseConn(ByVal conn As SqlConnection) If (conn.State <> ConnectionState.Closed) Then '判断源对象是否关闭 conn.Close() '关闭连接 conn = Nothing '不指向原对象 End If End Sub ''' <summary> ''' 关闭命令 ''' </summary> ''' <param name="cmd">需要关闭的命令</param> ''' <remarks></remarks> Public Sub CloseCmd(ByVal cmd As SqlCommand) If Not IsNothing(cmd) Then '如果CMD命令存在 cmd.Dispose() '销毁命令 cmd = Nothing End If End Sub End Class

Imports System.Data.SqlClient Imports System.Data Imports Charge.DAL.sqlHelper Public Class Login Public user1 As Charge.Model.User Dim strSQL As String Dim help As New sqlHelper Public Function SelectUsers(user1 As Charge.Model.User) As DataTable strSQL = "select * from User_Info where UserName=@UserName and PassWord=@PassWord" Dim sqlPara As SqlParameter() = { New SqlParameter("@UserName", user1.UserName), New SqlParameter("@PassWord", user1.PassWord) } Return help.ParaSelect(strSQL, CommandType.Text, sqlPara) End Function End Class

Imports System.Data.SqlClient Imports Charge.DAL.sqlHelper Imports System.Data Public Class AddUsers Dim strSQL As String Dim help As New sqlHelper Public Function AddUser(user3 As Charge.Model.User) As Integer strSQL = "insert into [User_Info] (UserName,PassWord,Level,RealName)values(@UserName,@PassWord,@Level,@RealName) " Dim sqlPara As SqlParameter() = { New SqlParameter("@UserName", user3.UserName), New SqlParameter("@PassWord", user3.PassWord), New SqlParameter("@Level", user3.Level), New SqlParameter("@RealName", user3.RealName) } Return help.ParaDataManager(strSQL, CommandType.Text, sqlPara) End Function End Class   这个模块时插入模块，使用了四个参数，返回值类型为Integer。
   这里没有用到设计模式，只是进行了封装，没有用到泛化和继承，随着进一步的学习，会有更好的办法来解决重复的问题。

数据操作的封装--sqlhelper,布布扣,bubuko.com