SQLHelper驀然回首
來源：程序員人生 發(fā)布時間：2015-01-12 08:52:45 閱讀次數(shù)：4115次
自己前面寫過1篇博客是關(guān)于Ado.net和數(shù)據(jù)庫的但是自己并沒有怎樣注意直到機(jī)房重構(gòu)的時候發(fā)現(xiàn)其實好多東西封裝起來可以減少DAL層的代碼量沒錯啦這就是我在讀了很多前人的博客和峰哥借的書以后明白的sqlhelper之前看大家很多人1直在寫關(guān)于這方面的博客然后自己沒有太注意，1直以為他是1個配置文件直接拿來就能夠用的覺得他很高大上但是自己開始動手做以后才發(fā)現(xiàn)原來他其實就是我們在對數(shù)據(jù)庫進(jìn)行操作的時候會用到的，比如我們在進(jìn)行上1次的數(shù)據(jù)庫的操作時我們會做下面幾步：
1.加載驅(qū)動
2.對數(shù)據(jù)庫進(jìn)行連接
3.對數(shù)據(jù)庫進(jìn)行操作（包括query和update，其中update又可以分為insert、delete、update）
4.關(guān)閉數(shù)據(jù)庫
5.返回結(jié)果集
但是如果我們1直都在每個界面都這么進(jìn)行操作的話那末代碼量就是很大的所以這就有了我之條件過的sqlhelper，他可以看作是1個封裝的類，可以調(diào)用里面的方法，下面是我做的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") '加載驅(qū)動并且定義1個連接對象
Dim conn As SqlConnection = New SqlConnection(strConnStr) '“ConnStr”是你web.config 這個配置文件里面連接
數(shù)據(jù)庫的的關(guān)鍵字,'
Dim cmd As New SqlCommand '也就是你在每一個.vb頁面援用這1句就能夠連接
數(shù)據(jù)庫了
Dim res As Integer '使用SQLCommand的作用是用來調(diào)用sql語句的
cmd = New SqlCommand(cmdText, conn)
cmd.CommandType = cmdType
cmd.Parameters.AddRange(paras) '用來添加參數(shù)
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
res = cmd.ExecuteNonQuery() '如果正確的設(shè)置了cmd的屬性就能夠通過executenonquery來進(jìn)行履行SQL語句
Catch ex As Exception
MsgBox(ex.Message, "
數(shù)據(jù)庫操作")
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, , "
數(shù)據(jù)庫操作")
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 '創(chuàng)建DataSet實例
cmd.CommandType = cmdType
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
adaptor.Fill(adataset) '填充數(shù)據(jù)集
End If
Catch ex As Exception
MsgBox(ex.Message, , "
數(shù)據(jù)庫操作")
Finally
If conn.State = ConnectionState.Open Then
conn.Close() '關(guān)閉
數(shù)據(jù)庫
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, , "
數(shù)據(jù)庫操作")
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, , "
數(shù)據(jù)庫操作")
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, , "
數(shù)據(jù)庫操作")
Finally
End Try
Return cmd.ExecuteReader() '盡量快地對
數(shù)據(jù)庫進(jìn)行查詢并得到結(jié)果1定要關(guān)閉！
End Function
End Class
進(jìn)行到這里我覺得對面向?qū)ο蟮睦斫膺€是不太好，所以打算要進(jìn)1步的動手和查閱資料，還有設(shè)計模式的應(yīng)用，也要好好的總結(jié)！
生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈
