Need corrections or better ways to read data via SQL ADO.NET

currently I use DataSet to read data from ADO.NET SQL provider, but I read that DataReader is more efficient for some cases. Many a times I only read single/few records and don't need in-memory data or multiple table relation manipulations.

1. Are there more clean and efficient ways to read ?

2. Can I in simple way map the full record directly to the Agent class, without having to parse out each column like I do now?

EDIT: - I have investigated somewhat on ORMs and used Linq2SQL a little, but the familiarity curve seems little steep, so left it at that. Please suggest other ways available within .NET and not any external tools.

public override A开发者_Python百科gent 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();
            ....
          }
        }

First, I'd like to recommand SLak's answer. This really is the answer to your question. I understand your trepidation about using large toolsets like EF, but it's really the right solution and doesn't have as much of a learning curve as you might think. There's very little reason to go straight to things like a DataReader anymore. EF is part of .NET and I can't encourage you enough to use it.

Likewise, don't go down the road of creating your own ORM of sorts with decorations and automatic code generation and the like. Do. Not. Do. It. In the end, you'll spend as much time maintaining that as you do your business logic, and you'll kick yourself for not using the work of many people who are a lot smarter than I am and probably you, as well ;) (No offense, of course). If you're looking for quick-and-dirty, then you need to go quick-and-dirty.

That being said, using a DataReader is the most lightweight method for reading data from the database.

I'm not sure exactly how to answer your first question, other than to give you an example of using a DataReader in C#.

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"];
            }
        }
    }
}

The while statement will execute for every row retrieved from the reader, though you can certainly short-circuit that loop if need be and not parse the entire result set.

You can use an ORM, such as Microsoft Entity Framework.
They will automatically generate SQL and copy properties to business objects.

Going back to straight SQL and ADO from an ORM inspired me to create a simple way to do data extraction rather than the way you're doing it (and the way I used to do it).

There is a lot of code to show here but I will give you a brief overview of how you can take care of this.

Create a Column class that inherits from Attribute

On your Agent class decorate your Properties like so:

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

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

From this point you should be able to get those custom attributes off of the class Properties and get a column and set a property value:

property.SetValue(t, value, null);

If you get this right, you should end up with something like:

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

where the signature of that method could be:

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

I currently use an n-tier environment for my development. I have a core data class that all of my data layer classes inherit from that is a single database connection. It has methods to execute my stored procs or t-sql. These methods can return sqlreader objects that my business objects use to load the variables. I'll post some examples. I hope this helps.

Here is my core data class:

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

Here is a simple data layer class:

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

Here is a business layer class that consumes this data layer class. The parts to pay attention to are the load and save methods. Mostly the load.

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