SQLHelper驀然回首
來源：程序員人生 發布時間：2015-01-12 08:52:45 閱讀次數：4123次
自己前面寫過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步的動手和查閱資料，還有設計模式的應用，也要好好的總結！
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
