Convert SqlDataReader to object for .NET Framework 4

Question

I am working on a class Library with .NET Framework 4.0. I have managed to pull a row using ADO.NET, but I'm unable to read individual values. I want the end result in class object. I have tried reading individual value dbReader.GetValue(dbReader.GetOrdinal("BranchCode")) but getting empty result.

Branch class:

public class Branch
{
    public Branch() { }

    public int BranchId { get; set; }
    public string BranchCode { get; set; }
    public string BranchName { get; set; }
}

DataReader class:

public void Initialize()
{
        try
        {
            string connectionString = "xyz";

            SqlConnection dbConnection = new SqlConnection(connectionString);

            SqlCommand cmd = new SqlCommand("Select * from dbo.Branch", dbConnection);

            dbConnection.Open();

            SqlDataReader dbReader = cmd.ExecuteReader();

            while (dbReader.Read())
            {
                var x1 = dbReader.GetValue(dbReader.GetOrdinal("BranchId"));
                var x2 = dbReader.GetValue(dbReader.GetOrdinal("BranchCode"));
                var x3 = dbReader.GetValue(dbReader.GetOrdinal("BranchName"));
            }

            var dd = "Dd";
        }
        catch(Exception ex)
        {
            throw ex;
        }
}

Charlieface · Accepted Answer · 2022-07-22 08:54:54Z

You have a number of issues with your code.

You need to actually create the Branch objects and do something with them. For example, return List.
To read the values, the easiest way is to do (typeNameHere) dbReader["ColumnName"]
You should SELECT exactly the right columns not SELECT *
Don't catch then re-throw exceptions with throw ex; as it wipes the stakc trace.

public List<Branch> Initialize()
{
    string connectionString = "xyz";
    const string query = @"
Select
  b.BranchId,
  b.BranchCode,
  b.BranchName
from dbo.Branch b;
";
    using (SqlConnection dbConnection = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(query, dbConnection))
    {
        dbConnection.Open();
        using (SqlDataReader dbReader = cmd.ExecuteReader())
        {
            var list = new List<Branch>();
            while (dbReader.Read())
            {
                var b = new Branch();
                b.BranchId = (int)dbReader["BranchId"];
                b.BranchCode = (string)dbReader["BranchCode"];
                b.BranchName = (string)dbReader["BranchName"];
                list.Add(b);
            }
            return list;
        }
    }
}

Collectives™ on Stack Overflow

Convert SqlDataReader to object for .NET Framework 4

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related