SQLHelper蓦然回首
来源：程序员人生 发布时间：2015-01-12 08:52:45 阅读次数：3957次
自己前面写过1篇博客是关于Ado.net和数据库的但是自己并没有怎样注意直到机房重构的时候发现其实好多东西封装起来可以减少DAL层的代码量没错啦这就是我在读了很多前人的博客和峰哥借的书以后明白的sqlhelper之前看大家很多人1直在写关于这方面的博客然后自己没有太注意，1直以为他是1个配置文件直接拿来就能够用的觉得他很高大上但是自己开始动手做以后才发现原来他其实就是我们在对数据库进行操作的时候会用到的，比如我们在进行上1次的数据库的操作时我们会做下面几步：
1.加载驱动
2.对数据库进行连接
3.对数据库进行操作（包括query和update，其中update又可以分为insert、delete、update）
4.关闭数据库
5.返回结果集
但是如果我们1直都在每个界面都这么进行操作的话那末代码量就是很大的所以这就有了我之条件过的sqlhelper，他可以看作是1个封装的类，可以调用里面的方法，下面是我做的1个注释和理解
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data
Public Class sqlHelper
Public Shared Function ExecuteNoQuery(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("Connstr") '加载驱动并且定义1个连接对象
Dim conn As SqlConnection = New SqlConnection(strConnStr) '“ConnStr”是你web.config 这个配置文件里面连接
数据库的的关键字,'
Dim cmd As New SqlCommand '也就是你在每一个.vb页面援用这1句就能够连接
数据库了
Dim res As Integer '使用SQLCommand的作用是用来调用sql语句的
cmd = New SqlCommand(cmdText, conn)
cmd.CommandType = cmdType
cmd.Parameters.AddRange(paras) '用来添加参数
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
res = cmd.ExecuteNonQuery() '如果正确的设置了cmd的属性就能够通过executenonquery来进行履行SQL语句
Catch ex As Exception
MsgBox(ex.Message, "
数据库操作")
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
Return res
End Function
Public Shared Function ExecuteNoQuery(ByVal cmdTxt As String, ByVal cmdType As CommandType) As Integer
Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
Dim conn As SqlConnection = New SqlConnection(strConnStr)
Dim cmd As New SqlCommand
Dim res As Integer
cmd = New SqlCommand(cmdTxt, conn)
cmd.CommandType = cmdType
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
res = cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, , "
数据库操作")
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
Return res
End Function
Public Shared Function GetDataTable(ByVal cmdtxt As String, ByVal cmdType As CommandType) As DataTable
Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
Dim conn As SqlConnection = New SqlConnection(strConnStr)
Dim cmd As New SqlCommand
Dim adataset As DataSet
Dim adaptor As SqlDataAdapter
cmd = New SqlCommand(cmdtxt, conn)
adaptor = New SqlDataAdapter(cmd) '增加1个适配器对象
adataset = New DataSet '创建DataSet实例
cmd.CommandType = cmdType
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
adaptor.Fill(adataset) '填充数据集
End If
Catch ex As Exception
MsgBox(ex.Message, , "
数据库操作")
Finally
If conn.State = ConnectionState.Open Then
conn.Close() '关闭
数据库
End If
End Try
Return adataset.Tables(0)
End Function
Public Shared Function GetDataTable(ByVal cmdtxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
Dim conn As SqlConnection = New SqlConnection(strConnStr)
Dim cmd As New SqlCommand
Dim adataset As DataSet
Dim adaptor As SqlDataAdapter
cmd = New SqlCommand(cmdtxt, conn)
adaptor = New SqlDataAdapter
adataset = New DataSet
cmd.CommandType = cmdType
cmd.Parameters.AddRange(paras)
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
adaptor.Fill(adataset)
Catch ex As Exception
MsgBox(ex.Message, , "
数据库操作")
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
Return adataset.Tables(0)
End Function
Public Shared Function GetReader(ByVal cmdtxt As String, ByVal cmdType As CommandType) As SqlDataReader
Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
Dim conn As SqlConnection = New SqlConnection(strConnStr)
Dim cmd As SqlCommand
cmd = New SqlCommand(cmdtxt, conn)
cmd.CommandType = cmdType
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Catch ex As Exception
MsgBox(ex.Message, , "
数据库操作")
Finally
End Try
Return cmd.ExecuteReader(CommandBehavior.CloseConnection) '
End Function
Public Shared Function GetReader(ByVal cmdtxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As SqlDataReader
Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
Dim conn As SqlConnection = New SqlConnection(strConnStr)
Dim cmd As New SqlCommand
cmd = New SqlCommand(cmdtxt, conn)
cmd.CommandType = cmdType
cmd.Parameters.AddRange(paras)
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Catch ex As Exception
MsgBox(ex.Message, , "
数据库操作")
Finally
End Try
Return cmd.ExecuteReader() '尽量快地对
数据库进行查询并得到结果1定要关闭！
End Function
End Class
进行到这里我觉得对面向对象的理解还是不太好，所以打算要进1步的动手和查阅资料，还有设计模式的应用，也要好好的总结！
生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
