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

CodePudding user response：

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