VB.NET 和 ASP.NET 数据访问器类

引言

以下是一些将许多常见的数据访问代码封装到一个类中的示例，以便在您的 .NET 项目中使用。这个特定的类允许在创建时使用可选的构造函数来指定数据库连接，而不是我们在 web.config 文件中使用的默认连接。

1. 首先，在您的项目中添加一个新类。添加…添加新项目…选择类并将其命名为 DAOObject（或者您喜欢的任何名称）
2. 使用下面的代码示例导入 System 命名空间并创建您的构造函数

代码

Imports System.Data
Imports 
System.Data.SqlClient

Namespace DBAccess

 Public Class 
DAOObject

 Public Sub New()
 End Sub

 Private 
mCN As String
 Public Sub New(ByVal ConnectionString As 
String) 
   'Allows us to use a CN String Other then the 
Default
    mCN = ConnectionString
 End 
Sub

 Protected Function GetConnection() As 
SqlConnection

   Dim ret_conn As 
SqlConnection

   If mCN = String.Empty 
Then
     ret_conn = New 
SqlConnection(ConfigurationSettings.AppSettings("cn")) 'Use 
Default
   Else
     ret_conn = New 
SqlConnection(mCN)
   End If

   
ret_conn.Open()
   GetConnection = ret_conn

  End 
Function

  Protected Sub CloseConnection(ByVal conn As 
SqlConnection)
     
conn.Close()
     conn = Nothing
  End 
Sub

下面这两个重载函数允许您传递一个存储过程名称，带或不带参数，并返回一个 DataReader。将这些添加到您的类中。

Public Overloads 
Function RunSPReturnRS(ByVal strSP As String, ByVal ParamArray 
commandParameters() As SqlParameter) As SqlDataReader

   Dim cn 
As SqlConnection = GetConnection()
   Dim rdr As 
SqlDataReader

   Dim cmd As New SqlCommand(strSP, 
cn)
   cmd.CommandType = 
CommandType.StoredProcedure

   Dim p As 
SqlParameter
   For Each p In 
commandParameters
     p = 
cmd.Parameters.Add(p)
     p.Direction = 
ParameterDirection.Input
   Next

   rdr = 
cmd.ExecuteReader(CommandBehavior.CloseConnection)
   
cmd.Dispose()

   Return rdr

End 
Function



Public Overloads Function RunSPReturnRS(ByVal strSP As 
String) As SqlDataReader

   Dim cn As SqlConnection = 
GetConnection()
   Dim rdr As SqlDataReader

   Dim 
cmd As New SqlCommand(strSP, cn)
   cmd.CommandType = 
CommandType.StoredProcedure

   rdr = 
cmd.ExecuteReader(CommandBehavior.CloseConnection)

   
cmd.Dispose()

   Return rdr

End 
Function

使用示例。（不要忘记导入 system.data 和 system.sqlclient 命名空间）

Dim objDB as New 
DBAccess.DAOObject()

Dim rdrDBAccess as SqlDataReader = 
objDB.RunSPReturnRS("spRptsDBSelect", _
   New 
SqlParameter("@roleID", Session("Role")), _
   New 
SqlParameter("@db", intDbId), _
   New SqlParameter("@userID", 
Session("UserID")))

While rdrDBAccess.Read()
...Code Here
End 
While
rdrDBAccess.close

下面的两个函数只是让您可以运行“直通”SQL 查询和操作查询。将这些添加到您的类中

Public Function RunPassSQL(ByVal strSQL As String) As 
SqlDataReader

   Dim cn As SqlConnection = 
GetConnection()
   Dim rdr As 
SqlDataReader

   Dim cmd As New SqlCommand(strSQL, 
cn)
   rdr = 
cmd.ExecuteReader(CommandBehavior.CloseConnection)
   
cmd.Dispose()

   Return rdr

End Function

Public 
Sub RunActionQuery(ByVal strSQL As String)

  Dim cn As SqlConnection 
= GetConnection()
  Dim cmd As New SqlCommand(strSQL, cn)

  
Try
    cmd.ExecuteNonQuery()
    
cmd.Dispose()
  Finally
    
CloseConnection(cn)
  End Try

End Sub

使用示例

Dim objDB as 
New DBAccess.DAOObject()

Dim dr As SqlDataReader = 
objDB.RunPassSQL("select * from tbl_User WHERE usrEmail = '" & strUser & 
"'")

If dr.Read then
…Code Here
End 
If
dr.close


objDB.RunActionQuery("Delete from tbl_Users where 
Fac_ID = " & intFacID)

对于我们的应用程序，我们通常在存储过程中有一个返回值，该返回值对于成功返回 0，对于失败返回 0 以外的整数。我们始终使用名为 @RetVal 的输出变量来实现此目的。将下面的函数添加到您的类中。

Public Function RunSPReturnInteger(ByVal strSP 
As String, ByVal ParamArray commandParameters() As SqlParameter) As 
Integer

  Dim cn As SqlConnection = GetConnection()
  Dim 
retVal As Integer

  Try

  Dim cmd As New 
SqlCommand(strSP, cn)
  cmd.CommandType = 
CommandType.StoredProcedure

  Dim p As SqlParameter
  For 
Each p In commandParameters
   p = 
cmd.Parameters.Add(p)
   p.Direction = 
ParameterDirection.Input
  Next

  p = cmd.Parameters.Add(New 
SqlParameter("@RetVal", SqlDbType.Int))
  p.Direction = 
ParameterDirection.Output

  cmd.ExecuteNonQuery()
  retVal = 
cmd.Parameters("@RetVal").Value
  cmd.Dispose()

  
Finally
  CloseConnection(cn)
  End Try

  Return 
retVal

End Function

使用示例

Dim objDB as New DBAccess.DAOObject()

If 
(objDB.RunSPReturnInteger("sptbl_CertUpdate", _
 New SqlParameter("@id", 
intAccID), _
 New SqlParameter("@fac", intFacID), _
 New 
SqlParameter("@Aff", intSponFac), _
 New SqlParameter("@spec", 
intSpecID), _
 New SqlParameter("@AccrdID", intAccredID), _
 New 
SqlParameter("@faRotate", intVARotate), _
 New SqlParameter("@dteMRAA", 
dteMRA), _
 New SqlParameter("@dteNST", dteNS), _
 New 
SqlParameter("@User", Session("UserID"))) = 0) 
Then

 Response.Redirect("NextPage.aspx")

Else

 strError = "Record Update 
FAILED, try again"

End If

下面是一个函数，它接受带有参数的存储过程，以及您选择的 Datatable 名称，并返回一个 Dataset。将此代码添加到您的类中

Public 
Function RunSPReturnDataSet(ByVal strSP As String, ByVal DataTableName As 
String, ByVal ParamArray commandParameters() As SqlParameter) As 
DataSet

  Dim cn As SqlConnection = GetConnection()

  
Dim ds As New DataSet()

  Dim da As New SqlDataAdapter(strSP, 
cn)
  da.SelectCommand.CommandType 
= CommandType.StoredProcedure

  Dim p As SqlParameter
  
For Each p In commandParameters
   
da.SelectCommand.Parameters.Add(p)
   p.Direction = 
ParameterDirection.Input
  Next

  da.Fill(ds, 
DataTableName)

  CloseConnection(cn)
  
da.Dispose()

  Return ds

End 
Function

使用示例

Dim DS As DataSet
Dim 
objDB As New DBAccess.DAOObject()
DS = New DataSet()
DS = 
objDB.RunSPReturnDataSet("spWRpt01", "Report", New SqlParameter("@SrvID", 1), 
New SqlParameter("@FacID", FacID))

If DS.Tables("Report").Rows.Count = 0 
Then

 strHTML = "<tr><td>There were no 
records</tr></td>"

else

  Dim drFullRpt As 
DataRow

  For Each drFullRpt In 
DS.Tables("Report").Rows
     ..Code Here
  
Next

End If

这些只是我使用的一些示例。我还有许多其他例程，包括将 datatable 添加到 dataset 等。发挥您的创造力，您可以编写自己的或更好的代码。