code评论：ADO.NET数据访问工具类（VB） [英] Code Review: ADO.NET Data Access Utility Class (VB)

问题描述

当我开始在我现在的老板我继承了一个项目从previous开发商和在该项目中，试图简化了许多涉及拨打电话和检索数据的code进行数据访问工具类背面从数据库。随着时间的推移它已被修改，添加更多的功能重载形式，现在我期待在从计算器社会可行的建议。

When I started at my current employer I inherited a project from a previous developer and in that project was a data access utility class that tries to simplify a lot of the code involved in making calls and retrieving data back from the database. Over time it has been modified to add more overloaded forms of functions, and now I am looking at possible suggestions from the StackOverflow community.

你认为应该加入？去掉？修改？

What do you think should be added? Removed? Modified?

这将是很好，如果这个类可以保持兼容VB.NET在.NET 2.0框架。我们也正在发展3.5，但我想有一些，一般要在大多数的框架（所以没有LINQ等）另外，请避免不必要的答案，包括没有避免，但使用NHibernate的或其他工具工作

It would be nice if this class could remain compatible to VB.NET in the .NET 2.0 framework. We are also developing in 3.5, but I would like to have something that is generally going to work across most frameworks (so no LINQ, etc.) Also, please refrain from unnecessary answers that consist of nothing but "Use nHibernate" or other tools.

Public Class DataAccess
    Public Shared Function ReturnScalar(ByVal CmdStr As String) As String
        Dim Result As String
        Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As SqlCommand = New SqlCommand(CmdStr, con)
        cmd.CommandType = CommandType.StoredProcedure
        Result = cmd.ExecuteScalar
        con.Close()
        Return Result
    End Function

    Public Shared Function ReturnScalar(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As Integer) As String
        Dim Result As String
        Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As SqlCommand = New SqlCommand(CmdStr, con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter(ParamName, Param))
        Result = cmd.ExecuteScalar
        con.Close()
        Return Result
    End Function

    Public Shared Function ReturnScalar(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As String) As String
        Dim Result As String
        Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As SqlCommand = New SqlCommand(CmdStr, con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter(ParamName, Param))
        Result = cmd.ExecuteScalar
        con.Close()
        Return Result
    End Function

    Public Shared Function ReturnScalar(ByVal CmdStr As String, ByVal Params As Hashtable) As String
        Dim Result As String
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        cmd.CommandType = CommandType.StoredProcedure
        For Each entry As DictionaryEntry In Params
            cmd.Parameters.Add(New SqlParameter(entry.Key.ToString, entry.Value))
        Next
        Result = cmd.ExecuteScalar
        con.Close()
        Return Result
    End Function

    Public Shared Function ReturnDataSet(ByVal CmdStr As String) As DataSet
        Dim DS As New DataSet
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        Dim adp As New SqlDataAdapter(cmd)
        cmd.CommandType = CommandType.StoredProcedure
        adp.Fill(DS)
        con.Close()
        Return DS
    End Function

    Public Shared Function ReturnDataSet(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As Integer) As DataSet
        Dim DS As New DataSet
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        Dim adp As New SqlDataAdapter(cmd)

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter(ParamName, Param))
        adp.Fill(DS)
        con.Close()
        Return DS
    End Function

    Public Shared Function ReturnDataSet(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As String) As DataSet
        Dim DS As New DataSet
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        Dim adp As New SqlDataAdapter(cmd)    
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter(ParamName, Param))
        adp.Fill(DS)
        con.Close()
        Return DS
    End Function

    Public Shared Function ReturnDataSet(ByVal CmdStr As String, ByVal Params As Hashtable) As DataSet
        Dim DS As New DataSet
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        Dim adp As New SqlDataAdapter(cmd)
        cmd.CommandType = CommandType.StoredProcedure
        For Each entry As DictionaryEntry In Params
            cmd.Parameters.Add(New SqlParameter(entry.Key.ToString, entry.Value))
        Next
        adp.Fill(DS)
        con.Close()
        Return DS
    End Function

    Public Shared Function ReturnDataTable(ByVal CmdStr As String) As DataTable
        Dim DT As New DataTable
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        Dim adp As New SqlDataAdapter(cmd)
        cmd.CommandType = CommandType.StoredProcedure
        adp.Fill(DT)
        con.Close()
        Return DT
    End Function

    Public Shared Function ReturnDataTable(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As Integer) As DataTable
        Dim DT As New DataTable
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        Dim adp As New SqlDataAdapter(cmd)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter(ParamName, Param))
        adp.Fill(DT)
        con.Close()
        Return DT
    End Function

    Public Shared Function ReturnDataTable(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As Date) As DataTable
        Dim DT As New DataTable
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        Dim adp As New SqlDataAdapter(cmd)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter(ParamName, Param))
        adp.Fill(DT)
        con.Close()
        Return DT
    End Function

    Public Shared Function ReturnDataTable(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As String) As DataTable
        Dim DT As New DataTable
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        Dim adp As New SqlDataAdapter(cmd)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter(ParamName, Param))
        adp.Fill(DT)
        con.Close()
        Return DT
    End Function

    Public Shared Function ReturnDataTable(ByVal CmdStr As String, ByVal Params As Hashtable) As DataTable
        Dim DT As New DataTable
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        Dim adp As New SqlDataAdapter(cmd)
        cmd.CommandType = CommandType.StoredProcedure
        For Each entry As DictionaryEntry In Params
            cmd.Parameters.Add(New SqlParameter(entry.Key.ToString, entry.Value))
        Next
        adp.Fill(DT)
        con.Close()
        Return DT
    End Function

    Public Shared Function DBExecute(ByVal CmdStr As String) As Integer
        Dim RowsAffected As Integer
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        cmd.CommandType = CommandType.StoredProcedure
        RowsAffected = cmd.ExecuteNonQuery
        con.Close()
        Return RowsAffected
    End Function

    Public Shared Function DBExecute(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As Integer) As Integer
        Dim RowsAffected As Integer
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter(ParamName, Param))
        RowsAffected = cmd.ExecuteNonQuery
        con.Close()
        Return RowsAffected
    End Function

    Public Shared Function DBExecute(ByVal CmdStr As String, ByVal Params As Hashtable) As Integer
        Dim RowsAffected As Integer
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString)
        con.Open()
        Dim cmd As New SqlCommand(CmdStr, con)
        cmd.CommandType = CommandType.StoredProcedure
        For Each entry As DictionaryEntry In Params
            cmd.Parameters.Add(New SqlParameter(entry.Key.ToString, entry.Value))
        Next
        RowsAffected = cmd.ExecuteNonQuery
        con.Close()
        Return RowsAffected
    End Function
End Class

注2：

RoutingConnectionString恰好是在这个类的特定版本的ConnectionString的名称，是不是这将贯穿我们所有的项目保持不变。

Note #2:

"RoutingConnectionString" just happens to be the name of the ConnectionString in this particular version of the class and is not something which would remain the same throughout all of our projects.

我们所有的项目都利用SQLServer2005的，但我看不出什么毛病的修改，可以帮助使它更加灵活与其他数据库系统，如MySQL或旧/较新版本的SQL Server。

All of our projects utilize SQLServer2005, but I do not see anything wrong with modifications which could help make it more flexible with other database systems such as MySQL or older/newer versions of SQL Server.

推荐答案

这将需要一些时间来研究上述的类。然而，很<一href="http://groups.google.com/group/DotNetDevelopment/browse%5Fthread/thread/d292b583f4c430d7">similar问题被问及一个月前在我的论坛，你可能会发现值得读的答案。

It's going to take a little while to study the class above. However, a very similar question was asked about a month ago on my discussion group and you may find it worthwhile to read the answers.

在此期间，我会加载您的类，并对其进行分析。完成后，我将编辑我的帖子，包括我的意见。

In the meantime, I'll load up your class and analyse it. When done, I'll edit my post to include my comments.

---

我希望你已经采取了看看我联系，所以我不会列出有提及这点相关这里也该线程。没有一点重复自己时，这是怎么回事反正是一个长期的职位！ ; - ）

I'm hoping you've taken a look at the thread I linked to so I will not be listing points mentioned there that are relevant here too. No point repeating myself when this is going to be a long post anyway! ;-)

问题1：建设/破坏： 类似乎没有公共构造，因此初始化逻辑重复每个调用的方法中。这使得这个类更多的是静态类。我不相信这是最好的设计决定了DataLayer类，它通常是打算同时从数百类/模块/库/页调用。

Problem 1: Construction/Destruction : The class appears to have no public constructor and therefore the initialization logic is repeated within each called method. This makes this class more of a Static Class. I do not believe that this is the best design decision for a DataLayer class, which is usually intended to be called simultaneously from hundreds of classes/modules/libraries/pages.

其次，它没有配置逻辑和没有终结。该Dispose模式是这么好商定的VS实际插入模板放到c你实现的那一刻的IDisposable 您$ C $。数据库相关类应为处理。

Secondly, it has no disposition logic and no finalizer. The dispose pattern is so well agreed upon that VS actually inserts the template into your code the moment you implement IDisposable. Database related classes should provide for dispose.

问题2：无异常处理：

图2a）尝试捕获：的 没有一个单一的尝试捕获，最后块类。您的类将不能正常处理异常和传递有用的信息给调用code。如果在数据库访问发生异常，您的连接的将会的保持开放。 使用 - 最终使用构造都非常推荐的，他们基本上是尝试，最后模式的抽象

2a) Try-Catch: There is not a single Try-Catch-Finally block in the class. Your class will not be able to gracefully handle exceptions and pass on useful information to the calling code. If an exception occurs during database access, your connection will remain open. Using - End Using constructs are very much recommended and they are basically an abstraction of the Try-Finally pattern.

2b）的错误日志记录：的 您的code应该有某种记录机制，虽然这也可能会被调用code。如果异常被从​​这个类抛出来实现。

2b) Error logging: Your code should have some sort of logging mechanism though this might also be implemented by the calling code if exceptions are thrown from this class.

问题3：过度/不需要的过载： 我实在不明白，需要有presented过载。

Problem 3: Excessive/Unneeded Overloads: I don't really see the need to have the presented overloads.

3a）的重复：的 大部分的code在重载似乎是重复的。我认为这可能与检查空值的单个方法处理。或者，它可以提取到私有方法。

3a) Repetition: Much of the code in the overloads seems to be repetitive. I think it could be handled in a single method with checks for nulls. Or it could be extracted into private methods.

例如：

Public Shared Function DBExecute(ByVal CmdStr As String, ByVal Params As Hashtable) As Integer
  ...basic initialization
  If Params isnot Nothing then
    For Each entry As DictionaryEntry In Params
      cmd.Parameters.Add(New SqlParameter(entry.Key.ToString, entry.Value))
    Next
  End If
  '...rest of processing.
End Function

3B）（dispensability）的：的 其次，很多的重载单纯从参数的更改数据类型的结果。要解决这个问题，你可能会发现更容易反省在哈希表项的值的基类型。我已经在过去使用的另一种方法是有一个单一的方法签名，并允许用户通过在SqlParameters本身的阵列。这允许用户在完全自由地设定如数据类型，大小，方向，空性，和precision性质。我目前使用的是第三种方法是有类型的可设置属性 SqlParameterCollection ，将最终用户进行设置和执行各种数据库的方法之前评估。

3b) Dispensability: Secondly, many of your overloads simply result from the changed datatype of the Parameter. To resolve this problem, you may find it easier to reflect upon the base type of the value of the item in the HashTable. Another way that I have used in the past is to have a single method signature and allow the user to pass in an array of SqlParameters themselves. This allows the user the complete freedom to set properties such as datatypes, size, direction, nullability, and precision. A third method that I use currently is to have a settable property of type SqlParameterCollection that would be set by the end user and evaluated before executing each kind of database method.

最后，我看不出一点有单独的方法（和重载）为 ReturnDataSet 和 ReturnDataTable ，由于前者将返回数据表的集合，在任何情况下。

Finally, I see no point in having separate methods (and overloads) for ReturnDataSet and ReturnDataTable, since the former will return a collection of DataTables in any case.

问题4：连接重用： 没有一个构造函数的使用共享类模式意味着用户不能，而不必通过相同的初始化运行使用相同的对象，随之而来的问题。

Problem 4: Connection reuse: The use of a Shared class pattern without a constructor means that a user cannot run consequent queries using the same object without having to go through the same initialization.

问题5：存储过程的支持： 该的CommandType 的的SqlCommand 已经没有办法设置为文本。所有的命令都被假定为类型 StoredProcedure的。

Problem 5: Stored Procedure support: The CommandType of an SqlCommand has no way to be set to Text. All Commands are assumed to be of type StoredProcedure.

第6题：的ConnectionStrings：

6A）加密：的 这个类提供了配置文件中的加密连接字符串的支持。

6a) Encryption: The class provides no support for encrypted connection strings in the configuration file.

6b）的可配置性：的 你提到的ConnectionString键的名称可以不同。这将如何设置，而无需重新编译类？

6b) Configurability: You mentioned that the name of the Connectionstring key can differ. How will that be set without having to recompile the class?

问题7：DataAdapter的连接方式： 当使用DataAdapters（如在 ReturnDataSet 方法），它总是最好让DataAdapter的管理连接的开幕式和闭幕式。

Problem 7: DataAdapter connections: When using DataAdapters (such as in the ReturnDataSet methods), it's always better to let the DataAdapter manage the opening and closing of the Connection.

问题8：ReturnScalar数据类型： 你的 ReturnScalar 方法假定字符串的返回类型。首先，明确投缺失（我选项显式上的全局设置立即标记此错误）。什么其他数据类型其次，？我想你应该返回一个对象，让调用code施放的要求。

Problem 8: ReturnScalar datatypes: Your ReturnScalar methods assume a return type of String. Firstly, the explicit cast is missing (My "Option Explicit On" global setting immediately flagged this error). Secondly, what of other datatypes ? I think you should return an Object and let the calling code cast as required.

问题9：XML的支持： 没有一个方法允许的ExecuteXmlReader方法，该方法是特定于SqlClient提供的调用。您可能不需要这在您的方案，但我认为这是值得一提。

Problem 9: Xml support: None of the methods allow invocation of the ExecuteXmlReader method which is specific to the SqlClient provider. You may not need this in your scenario, but I thought it's worth a mention.

我想这大概包裹起来。请不要介意这一点，但如果你要问我评论这课，我想给它一个3 10这3个是良好的愿望和值得称道的举措来封装数据库访问逻辑放到一个单独的库。

I guess that about wraps it up. Please don't mind this, but if you were to ask me to rate this class, I would give it a 3 on 10. Those 3 are for good intentions and for the commendable initiative to encapsulate database access logic into a separate library.

这篇关于code评论：ADO.NET数据访问工具类（VB）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！