本文主要是介绍SQLHelper的妙用,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
在做三层的时候,我们会发现一个问题,在D层有两个功能,一个是用户登录,一个是增加分数,通过分析发现,这两个方法都会有一个相同的地方,那就是对数据库的操作,其代码基本上是相同的。可能现在不会有什么问题,因为就只有两个功能而已。假如说要敲一个完整的系统的话,其中涉及到的功能不知道有多少,而每一个功能都会产生对数据库的操作问题,比如说连接,打开,关闭,大致都是相同的。而这个时候相同的代码会是一个非常庞大的数量,如果说其中一个错了,就得修改所有的功能,这无疑会是一个非常庞大的工作量。
由上面的问题可知,既然D层操作数据库的代码大部分都是相同的,何不把它抽象出来,作为公共法方法。因此SQLHelper由此诞生,其功能就是专门负责对数据库进行操作:无非就是两种情况,一是对表的查询,一个对表的更改(增、删、改)。如下:
<span style="color:#006600;">''' <summary>''' 执行不带参数的增删改操作''' </summary>''' <param name="cmdTxt">参数cmdTxt为所要执行的sql语句</param>''' <param name="cmdType">SqlCommand的执行类型</param>''' <returns>整形返回值res,执行成功则res!=0,不成功则res=0</returns>''' <remarks></remarks></span>Public Shared Function ExecuteNoQuery(ByVal cmdTxt As String, ByVal cmdType As CommandType) As IntegerDim conn As SqlConnection = New SqlConnection(ConnectionString) <span style="color:#006600;">'定义连接变量conn,进行数据库连接</span>Dim cmd As SqlCommand <span style="color:#006600;">'定义执行命令变量</span>Dim res As Integer <span style="white-space:pre"> </span> <span style="color:#006600;">'定义返回值变量res</span>cmd = New SqlCommand(cmdTxt, conn) <span style="color:#009900;">'实例化conn上的SqlCommand命令,执行语句为cmdTxt</span>cmd.CommandType = cmdType <span style="color:#009900;">'命令执行的类型</span>Try<span style="color:#009900;"> '如果数据库连接状态为关闭则将其打开</span>If conn.State = ConnectionState.Closed Thenconn.Open()End If<span style="color:#009900;">'执行cmdTxt语句,并将执行结果返回给res(执行成功返回的记录总数)</span>res = cmd.ExecuteNonQuery()Catch ex As Exception<span style="color:#009900;">'错误处理程序,出错则提示</span>MsgBox(ex.Message, , "数据库操作")Finally<span style="color:#009900;"> '如果连接状态为打开则将其关闭,释放内存</span>If conn.State = ConnectionState.Open Thenconn.Close()End IfEnd TryReturn res End Function<span style="color:#009900;"> ''' <summary>''' 执行带参数的增删改操作''' </summary>''' <param name="cmdTxt">参数cmdTxt为所要执行的sql语句</param>''' <param name="cmdType">SqlCommand的执行类型</param>''' <param name="paras">sql语句中引用的参数</param>''' <returns>整形返回值res,执行成功则res!=0,不成功则res=0</returns>''' <remarks></remarks></span>Public Shared Function ExecuteNoQuery(ByVal cmdTxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As IntegerDim conn As SqlConnection = New SqlConnection(ConnectionString) Dim cmd As SqlCommand Dim res As Integer cmd = New SqlCommand(cmdTxt, conn) cmd.CommandType = cmdType <span style="color:#009900;">'命令执行的类型</span>cmd.Parameters.AddRange(paras) <span style="color:#009900;">'执行cmdTxt语句时所带的参数</span>TryIf conn.State = ConnectionState.Closed Thenconn.Open()End Ifres = cmd.ExecuteNonQuery()Catch ex As ExceptionMsgBox(ex.Message, , "数据库操作")FinallyIf conn.State = ConnectionState.Open Thenconn.Close()End IfEnd TryReturn res End Function<span style="color:#009900;"> ''' <summary>''' 执行不带参数的查询操作''' </summary>''' <param name="cmdTxt">cmdTxt作为查询时的sql语句</param>''' <param name="cmdType">查询时的查询方式</param>''' <returns>查询后以表的方式返回,如下面的adataset.Tables(0)</returns>''' <remarks></remarks></span>Public Shared Function GetDataTable(ByVal cmdTxt As String, ByVal cmdType As CommandType) As DataTableDim conn As SqlConnection = New SqlConnection(ConnectionString) Dim cmd As New SqlCommand Dim adataset As DataSet <span style="color:#009900;"> '定义数据缓存变量</span>Dim adaptor As SqlDataAdapter <span style="color:#009900;">'定义数据适配器变量</span>cmd = New SqlCommand(cmdTxt, conn) adaptor = New SqlDataAdapter(cmd) <span style="color:#009900;">'将结果绑定到数据适配器变量adaptor上面</span>adataset = New DataSet <span style="white-space:pre"> </span> <span style="color:#009900;">'实例化数据缓存变量adataset</span>cmd.CommandType = cmdType <span style="white-space:pre"> </span> <span style="color:#009900;">'选择命令执行的类型</span>TryIf conn.State = ConnectionState.Closed Thenconn.Open()End Ifadaptor.Fill(adataset)Catch ex As ExceptionMsgBox(ex.Message, , "数据库操作")FinallyIf conn.State = ConnectionState.Open Thenconn.Close()End IfEnd Try<span style="color:#009900;"> '以表格形式返回结果</span>Return adataset.Tables(0)End Function<span style="color:#009900;"> ''' <summary>''' 执行带参数的查询操作''' </summary>''' <param name="cmdTxt">参数cmdTxt为所要执行的sql语句</param>''' <param name="cmdType">查询时的查询方式</param>''' <param name="paras">查询时的命令参数paras</param>''' <returns>查询后以表的方式返回,如下面的adataset.Tables(0)</returns>''' <remarks></remarks></span>Public Shared Function GetDataTable(ByVal cmdTxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTableDim conn As SqlConnection = New SqlConnection(ConnectionString) Dim cmd As SqlCommand Dim adaptor As SqlDataAdapter Dim adataset As DataSet cmd = New SqlCommand(cmdTxt, conn) cmd.CommandType = cmdType <span style="color:#009900;"> '命令执行的类型</span>cmd.Parameters.AddRange(paras) <span style="color:#009900;"> '命令执行时的参数</span>adaptor = New SqlDataAdapter(cmd) adataset = New DataSetTryIf conn.State = ConnectionState.Closed Thenconn.Open()End Ifadaptor.Fill(adataset) <span style="color:#009900;">'向adaptor对象中填充查询的数据</span>Catch ex As ExceptionMsgBox(ex.Message, , "数据库操作")FinallyIf conn.State = ConnectionState.Open Thenconn.Close()End IfEnd TryReturn adataset.Tables(0)End Function
缺点:当然指的是我这篇文章中的代码,通过比较就会发现,其中相同的东西还是很多的,其还可以进一步进行抽象概括,具体如何进行,就交给阅读此文章的大牛了。
这篇关于SQLHelper的妙用的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!