C# Class Library for Data Layer - DataTable conversion

Question

I am using a class library for my "data layer" and I am trying to create a method to load results from a SP into a datagrid via the .net DataTable.

I am having trouble returning the datatable (dt) object because of error "cannot implicitly convert type datatable to my custom type.

Here are some code snippets:

public DesignationTable GetDesignationTable(string designationName)
{
    using (SqlConnection conn = DB.GetSqlConnection())
    {
        DesignationTable dt = new DataTable();

        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = @"GET_DESG_DETAILS_BY_ATTRIBUTE";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            SqlParameter p1 = new SqlParameter("DESG_ATTRIBUTE", System.Data.SqlDbType.Text);
            p1.Value = designationName;

            cmd.Parameters.Add(p1);

            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            dt.Load(dr);
        }
        return dt;
    }
}

public class DesignationTable
{
    public string DESG_ATTRIBUTE { get; set; }
    public string DESG_ATTRIBUTE_DESC { get; set; }
    public string DESG { get; set; }
    public string DESG_NAME { get; set; }
    public string TYPE { get; set; }
    public string TYPE_DESC { get; set; }
    public string DESG_PROJECT { get; set; }
    public string DESG_PROJECT_DESC { get; set; }
    public DateTime START_DATE { get; set; }
    public DateTime END_DATE { get; set; }
    public string STATUS { get; set; }

    public void Load(SqlDataReader dr)
    {
        DESG_ATTRIBUTE = dr["DESG_ATTRIBUTE"].ToString();
        DESG_ATTRIBUTE_DESC = dr["DESG_ATTRIBUTE_DESC"].ToString();
        DESG = dr["DESG"].ToString();
        DESG_NAME = dr["DESG_NAME"].ToString();
        TYPE = dr["TYPE"].ToString();
        TYPE_DESC = dr["TYPE_DESC"].ToString();
        DESG_PROJECT = dr["DESG_PROJECT"].ToString();
        DESG_PROJECT_DESC = dr["DESG_PROJECT__DESC"].ToString();
        //START_DATE = DateTime.Parse(reader["START_DATE"].ToString());
        //START_DATE = DateTime.Parse(reader["END_DATE"].ToString());
        STATUS = dr["STATUS"].ToString();
    }
}

you don't show the code that gets the error. also your "table" looks like a row to me. — Hogan, Mar 25 '15 at 15:03
Looks like this would cause that kind of error: `DesignationTable dt = new DataTable();`. You can't cast a `DataTable` to your own object. Looking at your code that line should be: `DesignationTable dt = new DesignationTable();` — amura.cxg, Mar 25 '15 at 15:09
also you do not need the literal sign in front of the stored procedure name here `cmd.CommandText = @"GET_DESG_DETAILS_BY_ATTRIBUTE"` you probably would want to prepend the literal sign here in the stored proc param name `SqlParameter p1 = new SqlParameter("DESG_ATTRIBUTE", System.Data.SqlDbType.Text);` Like this `SqlParameter p1 = new SqlParameter("@DESG_ATTRIBUTE", System.Data.SqlDbType.Text);` use google to do a search on how to use the `Fill()` method in regards to your DataReader / DataTable as well — MethodMan, Mar 25 '15 at 15:22
Is it a compilation error ? I also think it is related to this declaration DesignationTable dt = new DataTable() — Pablo Rodríguez, Mar 25 '15 at 15:27
reader is IDisposable ...http://stackoverflow.com/questions/744051/sqldatareader-dispose `DesignationTable dt = new DataTable();` doesnt sound right either... You will need to `DesignationTable dt = new DesignationTable()`; You are also missing your `while reader.Read()` — Andy Danger Gagne, Mar 25 '15 at 15:31
Thanks All... as you can see I am new to this... Let me regroup a bit... my goal is to return multiple rows from a proc into an object in my data access layer. I then want to display the results on the asp webform page. The proc has one param which will be driven by a dropdown list control on the page already. Is there a better way to create the object without using DataTable? — Nick, Mar 26 '15 at 16:56

score 0 · Answer 1 · answered Mar 25 '15 at 15:26

0

Is this line correct?

DesignationTable dt = new DataTable();

Should it not be

DataTable dt=new DataTable();

then .Load() method can be used.

answered Mar 25 '15 at 15:26

C# Class Library for Data Layer - DataTable conversion

1 Answers1