需要更正或更好的方法来通过 SQL ADO.NET 读取数据

Question

目前，我使用 DataSet 从 ADO.NET SQL 提供程序读取数据，但我了解到 DataReader 在某些情况下效率更高。很多时候我只读取单个/几条记录，不需要内存数据或多个表关系操作。

1. 是否有更干净、高效的阅读方式？

2. 我可以以简单的方式将完整记录直接映射到 Agent 类，而不必像现在一样解析每一列吗？

编辑： - 我对 ORM 进行了一些调查，并使用了一点 Linq2SQL，但熟悉度曲线似乎有点陡峭，所以就这样吧。请建议 .NET 中可用的其他方法，而不是任何外部工具。

public override Agent GetAgentByAgentId(string agentId)
{
   Agent agent;

   try
   {
      ArrayList paramList = new ArrayList();
      paramList.Add(_dbUtilities.CreateSqlParamater("@agent_id", SqlDbType.VarChar, 10, ParameterDirection.Input, agentId));

      // Query the database for an agent with given agentId
      DataSet ds = _dbLib.ExecuteProcedureDS("sp_dbc_agentsSelect", paramList);

      if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
      {
         DataTable dt = ds.Tables[0];

         if (dt.Rows.Count > 0)
         {
            agent = new Agent();
            DataRow dr = dt.Rows[0];

            // Get the agent data
            agent.IsActive = bool.Parse(dr["is_active"].ToString());
            agent.UserId = dr["user_id"].ToString();
            ....
          }
        }

Answer 1

首先，我想推荐 SLAk 的答案。这确实是你问题的答案。我理解您对使用 EF 等大型工具集的担忧，但这确实是正确的解决方案，并且没有您想象的那么多学习曲线。几乎没有理由再直接使用 DataReader 之类的东西了。 EF 是 .NET 的一部分，我极力鼓励您使用它。

同样，不要走上创建自己的带有装饰和自动代码生成之类的 ORM 的道路。做。不是。做。它。最后，您将花费与维护业务逻辑一样多的时间，并且您会因为没有使用许多比我聪明得多的人的工作而自责，可能也比您聪明;) （当然，无意冒犯）。如果你正在寻找快速而肮脏的方法，那么你就需要快速而肮脏的方法。

话虽这么说，使用 DataReader 是从数据库读取数据的最轻量级方法。

除了给您提供一个在 C# 中使用 DataReader 的示例之外，我不确定如何准确回答您的第一个问题。

using(DbConnection conn = new SqlConnection("connection string"0)
{
    conn.Open();

    using(DbCommand cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "sql statement";

        using(IDataReader reader = cmd.ExecuteReader())
        {
            while(reader.Read())
            {
                var column1 = reader["column1"];
            }
        }
    }
}

while 语句将对从读取器检索到的每一行执行，尽管您当然可以根据需要短路该循环而不解析整个结果集。

Answer 2

您可以使用 ORM，例如 Microsoft Entity Framework。
它们将自动生成 SQL 并将属性复制到业务对象。

Answer 3

从 ORM 回到直接的 SQL 和 ADO 激励我创建一种简单的方法来进行数据提取，而不是像您现在那样（以及我以前的方式）。

这里有很多代码要显示，但我将简要概述如何处理这个问题。

创建一个继承自 Attribute 的 Column 类

在您的 Agent 类上像这样装饰您的 Properties：

[Column("is_active")]
public bool IsActive { get; set; }

[Column("user_id")]
public int UserId { get; set; }

从现在起您应该能够获得这些自定义属性类 Properties 的属性并获取一列并设置属性值：

property.SetValue(t, value, null);

如果你做对了，你最终应该得到类似的结果：

agent = dt.Rows[0].ExtractAs<Agent>();

该方法的签名可能是：

public static T ExtractAs<T>(this DataRow row) where T : class, new() 
{ 
    /* map columns to properties */ 
}

Answer 4

我目前使用 n 层环境进行开发。我有一个核心数据类，我的所有数据层类都继承自该核心数据类，它是单个数据库连接。它有执行我的存储过程或 t-sql 的方法。这些方法可以返回我的业务对象用来加载变量的 sqlreader 对象。我将发布一些示例。我希望这有帮助。

这是我的核心数据类：

Imports System.Data
Imports System.Data.SqlClient

Namespace Datalayer
''' <summary>
''' Base class to connect to a database using a single connection.
''' </summary>
''' <remarks>
''' jwames - 12/20/2010 - original code
''' </remarks>
Public Class CoreDataClass
    Implements IDisposable

#Region "attributes"
    Private _connection As SqlConnection
    Private _connectionString As String = My.Settings.PowerNetPricingDB()
    Private _disposedValue As Boolean
    Private _pool As Boolean
#End Region

#Region "constructor"
    ''' <summary>
    ''' Initialize the connection string
    ''' </summary>
    ''' <remarks>
    ''' jwames - 12/20/2010 - original code
    ''' </remarks>
    Public Sub New()
        _connection = New SqlConnection(_connectionString)
        _disposedValue = False
        _pool = False
    End Sub
#End Region

#Region "functions"
    ''' <summary>
    ''' IDisposable implementation
    ''' </summary>
    ''' <param name="disposing"></param>
    ''' <remarks>
    ''' jwames - 12/10/2010 - original code
    ''' </remarks>
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not _disposedValue Then
            If disposing Then
                If _connection Is Nothing = False Then _connection.Dispose()
            End If

            _connectionString = Nothing
        End If

        _disposedValue = True
    End Sub

    ''' <summary>
    ''' Dispose of the database connection objects
    ''' </summary>
    ''' <remarks>
    ''' jwames - 12/20/2010 - original code
    ''' </remarks>
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub

    ''' <summary>
    ''' Base function used to execute queries that return results
    ''' </summary>
    ''' <remarks>
    ''' jwames - 12/20/2010 - generated by original code
    ''' </remarks>
    Protected Function GetDataReader(ByRef cmd As SqlCommand) As SqlDataReader
        Try
            cmd.CommandType = CommandType.StoredProcedure

            If Me.Connection.State = ConnectionState.Closed Then Me.Connection.Open()

            If _pool Then
                Return cmd.ExecuteReader()
            Else
                Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
            End If
        Catch ex As SqlException
            Throw ex
        End Try
    End Function

    ''' <summary>
    ''' Base function used to execute dynamic queries that return results
    ''' </summary>
    ''' <remarks>
    ''' jwames - 12/20/2010 - original code
    ''' </remarks>
    Protected Function GetDataReader(ByVal sql As String) As SqlDataReader
        Try
            Dim cmd As New SqlCommand(sql, Me.Connection)
            cmd.CommandType = CommandType.StoredProcedure

            If Me.Connection.State = ConnectionState.Closed Then Me.Connection.Open()

            If _pool Then
                Return cmd.ExecuteReader()
            Else
                Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
            End If
        Catch ex As SqlException
            Throw ex
        End Try
    End Function

    ''' <summary>
    ''' Base function that will execute a procedure
    ''' </summary>
    ''' <remarks>
    ''' jwames - 12/20/2010 - original code
    ''' </remarks>
    Protected Sub ExecuteCommand(ByVal cmd As SqlCommand)
        Try
            cmd.CommandType = CommandType.StoredProcedure

            If Me.Connection.State = ConnectionState.Closed Then Me.Connection.Open()

            cmd.ExecuteNonQuery()

            If _pool = False Then
                Me.Connection.Close()
                Me.Connection.Dispose()
            End If
        Catch ex As SqlException
            Throw ex
        End Try
    End Sub

    ''' <summary>
    ''' Base function that will execute a procedure
    ''' </summary>
    ''' <remarks>
    ''' jwames - 12/20/2010 - original code
    ''' </remarks>
    Protected Sub ExecuteCommand(ByVal sql As String)
        Try
            Dim cmd As New SqlCommand(sql, Me.Connection)
            cmd.CommandType = CommandType.StoredProcedure

            If Me.Connection.State = ConnectionState.Closed Then Me.Connection.Open()

            cmd.ExecuteNonQuery()

            If _pool = False Then
                Me.Connection.Close()
                Me.Connection.Dispose()
            End If
        Catch ex As SqlException
            Throw ex
        End Try
    End Sub
#End Region

#Region "properties"
    Public Property Connection() As SqlConnection
        Get
            Return _connection
        End Get
        Set(ByVal value As SqlConnection)
            _connection = value
        End Set
    End Property

    ''' <summary>
    ''' Use the same connection to run multiple queries
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>
    ''' jwames - 12/20/2010 - original code
    ''' </remarks>
    Public Property PoolConnections() As Boolean
        Get
            Return _pool
        End Get
        Set(ByVal value As Boolean)
            _pool = value
        End Set
    End Property
#End Region


End Class
End Namespace

这是一个简单的数据层类：

Imports System.Data.SqlClient

Namespace Datalayer
''' <summary>
''' Class that connects to the database and relays information to the business layer
''' </summary>
''' <remarks>
''' jwames - 1/3/2011 - original code
''' </remarks>
Public Class ItemCost
    Inherits CoreDataClass

#Region "functions"
    ''' <summary>
    ''' Function that returns all rows within the database
    ''' </summary>
    ''' <remarks>
    ''' jwames - 1/4/2011 - original code
    ''' </remarks>
    Public Function GetAllItemCost(ByVal BranchId As String, ByVal ItemNumber As String, ByVal ShipDate As DateTime) As SqlDataReader
        Dim cmd As New SqlCommand("spGetAllItemCostByItem", Connection)
        cmd.Parameters.AddWithValue("@BranchId", BranchId)
        cmd.Parameters.AddWithValue("@ItemNumber", ItemNumber)
        cmd.Parameters.AddWithValue("@ShipDate", ShipDate)

        Return GetDataReader(cmd)
    End Function

    ''' <summary>
    ''' Function that returns a single row from the database
    ''' </summary>
    ''' <remarks>
    ''' jwames - 1/3/2011 - generated by TierCreator
    ''' </remarks>
    Public Function GetItemCost(ByVal BranchId As String, ByVal ItemNumber As String, ByVal CostTypeId As String, ByVal ShipDate As DateTime) As SqlDataReader
        Dim cmd As New SqlCommand("spGetItemCost", MyBase.Connection)
        cmd.Parameters.AddWithValue("@BranchId", BranchId)
        cmd.Parameters.AddWithValue("@ItemNumber", ItemNumber)
        cmd.Parameters.AddWithValue("@CostTypeId", CostTypeId)
        cmd.Parameters.AddWithValue("@ShipDate", ShipDate)

        Return GetDataReader(cmd)
    End Function

    ''' <summary>
    ''' Move all of the records from the staging tables to the working tables
    ''' </summary>
    ''' <remarks>
    ''' jwames - 1/4/2011 - original code
    ''' </remarks>
    Public Sub PublishItemCost()
        ExecuteCommand("spPublishItemCost")
    End Sub


    ''' <summary>
    ''' Create/Update a row in the database
    ''' </summary>
    ''' <remarks>
    ''' jwames - 1/3/2011 - generated by TierCreator
    ''' </remarks>
    Public Sub SaveItemCost(BranchId As String, ItemNumber As String, CostTypeId As String, EffectiveDate As DateTime, Cost As Double)
        Dim cmd As New SqlCommand("spSaveItemCost", MyBase.Connection)
        cmd.Parameters.AddWithValue("@BranchId", BranchId)
        cmd.Parameters.AddWithValue("@ItemNumber", ItemNumber)
        cmd.Parameters.AddWithValue("@CostTypeId", CostTypeId)
        cmd.Parameters.AddWithValue("@EffectiveDate", EffectiveDate)
        cmd.Parameters.AddWithValue("@Cost", Cost)

        ExecuteCommand(cmd)
    End Sub

#End Region

End Class
End Namespace

这是一个使用此数据层类的业务层类。需要注意的部分是加载和保存方法。主要是负载。

Imports System.Text

Namespace BusinessLayer
''' <summary>
''' Class representation of a row in the table in the database
''' </summary>
''' <remarks>
''' jwames - 1/3/2011 - original code
''' </remarks>
Public Class ItemCost
#Region "attributes"
    Private _CostType As CostType
    Private _EffectiveDate As DateTime
    Private _Cost As Double
    Private _exits As Boolean
#End Region

#Region "constructor"
    ''' <summary>
    ''' Initialize all of the module level variables
    ''' </summary>
    ''' <remarks>
    ''' jwames - 1/5/2011 - original code
    ''' </remarks>
    Public Sub New()
        _exits = False
    End Sub

    ''' <summary>
    ''' Initialize all of the module level variables and execute the load method
    ''' </summary>
    ''' <remarks>
    ''' jwames - 1/3/2011 - original code
    ''' </remarks>
    Public Sub New(ByVal BranchId As String, ByVal ItemNumber As String, ByVal CostTypeId As String, ByVal ShipDate As DateTime)
        Me.New()
        Load(BranchId, ItemNumber, CostTypeId, ShipDate)
    End Sub
#End Region

#Region "functions"
    ''' <summary>
    ''' Get a single row based on the arguments passed and set the class variables with the results
    ''' </summary>
    ''' <remarks>
    ''' jwames - 1/5/2011 - original code
    ''' </remarks>
    Public Sub Load(ByVal MyBranchId As String, ByVal MyItemNumber As String, ByVal MyCostTypeId As String, ByVal ShipDate As DateTime)
        ' set these vars to perform validation
        Dim branchId As New Branch(MyBranchId)
        Dim itemNum As New ItemNumber(MyItemNumber)
        CostType.SetCostType(MyCostTypeId)

        Using ic As New Datalayer.ItemCost
            Using rdr As Data.SqlClient.SqlDataReader = ic.GetItemCost(branchId.Abbreviation, itemNum.Number, CostType.GetCostTypeAsString(), ShipDate)
                If rdr.Read() Then
                    If rdr.IsDBNull(0) = False Then CostType.SetCostType(rdr.GetString(0))
                    If rdr.IsDBNull(1) = False Then EffectiveDate = rdr.GetDateTime(1)
                    If rdr.IsDBNull(2) = False Then Cost = rdr.GetDecimal(2)

                    _exits = True
                Else
                    _exits = False
                End If

                rdr.Close()
            End Using
        End Using
    End Sub

    ''' <summary>
    ''' Save the class information in the database
    ''' </summary>
    ''' <remarks>
    ''' jwames - 1/5/2011 - original code
    ''' </remarks>
    Public Shared Sub Save(ByVal BranchId As String, ByVal ItemNumber As String, ByVal CostTypeId As String, ByVal EffectiveDate As DateTime, _
                           ByVal Cost As Double)
        ' set these vars to perform validation
        Dim branch As New Branch(BranchId)
        Dim itemNum As New ItemNumber(ItemNumber)
        Dim ct As New CostType(CostTypeId)

        Using ic As New Datalayer.ItemCost
            ic.SaveItemCost(branch.Abbreviation, itemNum.Number, ct.GetCostTypeAsString(), EffectiveDate, Cost)
        End Using
    End Sub
#End Region

#Region "properties"
    Public Property CostType() As CostType
        Get
            Return _CostType
        End Get
        Set(ByVal value As CostType)
            _CostType = value
        End Set
    End Property

    Public Property EffectiveDate As DateTime
        Get
            Return _EffectiveDate
        End Get
        Set(ByVal value As DateTime)
            _EffectiveDate = value
        End Set
    End Property 

    Public Property Cost() As Double
        Get
            Return _Cost
        End Get
        Set(ByVal value As Double)
            _Cost = value
        End Set
    End Property

    ''' <summary>
    ''' signifies if the item cost was found and loaded in the load method
    ''' </summary>
    ''' <returns>true if found</returns>
    ''' <remarks>
    ''' jwames - 1/5/2011 - original code
    ''' </remarks>
    Public ReadOnly Property Exists() As Boolean
        Get
            Return _exits
        End Get
    End Property
#End Region
End Class
End Namespace