Fill C# object with SQL Server Stored Procedure return data

I have a SQL Server stored proc that returns a single row of data with 28 columns.  I would like to use that data to fill up an Employee object I created.  I pass an EmployeeID to the stored proc and it returns all values for that employee.

I attempted to fill a DataTable but I couldn't figure out how to access the row values.

Is there a best practice that is recommended for this?  Seems like it's very common but I've never done it before, so any help would be greatly appreciated.
RIP_LeroiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Can you please show the code you are using now to get the data from the stored procedure.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can have a read in this article and customize accordingly:

DAL: Retrieve a DataTable using a Stored Procedure
http://blogs.msmvps.com/deborahk/dal-retrieve-a-datatable-using-a-stored-procedure/
Daniel Van Der WerkenIndependent ConsultantCommented:
Since we don't know exactly what you're doing, this is hard to tell. However, the columns of a row of a datatable are typically accessed via the column string name values. For example,

DataRow row = dataTable.Rows[0];
int EmployeeID = int.MinValue;
if (int.TryParse(row["EmployeeID"].ToString(), out EmployeeID))
{
   // we got employee id
}

Open in new window


where the column name in the datatable is "EmployeeID".
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

RIP_LeroiAuthor Commented:
Here is my current code.  I don't have to use a DataTable.  I would prefer to use whatever the recommended best practice is for filling an object from a database.

GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spGetEmployee]
@EmployeeID nvarchar(30)
AS
SELECT * 
FROM dbo.Employee
INNER JOIN [dbo].[ParentChildDepartment]
	ON dbo.Employee.ParentChildDepartmentID=[dbo].[ParentChildDepartment].ParentChildDepartmentID
INNER JOIN [dbo].[ChildDepartment]
	ON [dbo].[ChildDepartment].ChildDepartmentID=[dbo].[ParentChildDepartment].ChildDepartmentID
INNER JOIN [dbo].[JobClassification]
	ON dbo.Employee.JobClassificationID=[dbo].[JobClassification].JobClassificationID
INNER JOIN [dbo].[ParentDepartment]
	ON [dbo].[ParentDepartment].ParentDepartmentID=[dbo].[ParentChildDepartment].ParentDepartmentID
INNER JOIN [dbo].[WorkingTitle]
	ON dbo.Employee.WorkingTitleID=[dbo].[WorkingTitle].WorkingTitleID
INNER JOIN [dbo].[WorkLocation]
	ON dbo.Employee.WorkLocationID=[dbo].[WorkLocation].WorkLocationID
WHERE dbo.Employee.EmployeeID = @EmployeeID

Open in new window


public DataTable GetEmployee(string empID)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString);
        SqlCommand cmd = new SqlCommand("spGetEmployee", conn);
        SqlParameter parEmployeeID = new SqlParameter("@EmployeeID", SqlDbType.NVarChar);
        string userName = HttpContext.Current.User.Identity.Name;

        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        
        try
        {
            conn.Open();
            parEmployeeID.Value = GetEmployeeID(userName);
            cmd.Parameters.Add(parEmployeeID);

            cmd.ExecuteNonQuery();

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

            da.Fill(dt); // Not sure what to do here or if DataTable is even correct to use
            string test = dt.Rows[0].Field<string>(0);
            return dt;
        }
        finally
        {
            conn.Close();
        }
    }

Open in new window

Daniel Van Der WerkenIndependent ConsultantCommented:
The mechanism you're using is fine. Looks like you already understand what you need to do.

Does this example help?

   class DoDataTableStuff
    {
        public void DoStuff()
        {
            DataTable dt = MakeDataTable();
            int empID = dt.Rows[0].Field<int>("EmployeeID");
            string empFirstName = dt.Rows[0]["FirstName"].ToString();
            string empLastName = dt.Rows[0][2].ToString();
        }

        public DataTable MakeDataTable()
        {
            DataTable dt = new DataTable();
            DataColumn column1 = new DataColumn()
            {
                ColumnName = "EmployeeID",
                DataType = typeof(int),
                DefaultValue = 0
            };

            DataColumn column2 = new DataColumn()
            {
                ColumnName = "FirstName",
                DataType = typeof(string),
                DefaultValue = "Leeroy"
            };

            DataColumn column3 = new DataColumn()
            {
                ColumnName = "LastName",
                DataType = typeof(string),
                DefaultValue = "Jenkins"
            };

            dt.Columns.Add(column1);
            dt.Columns.Add(column2);
            dt.Columns.Add(column3);

            DataRow dr = dt.NewRow();
            dr["EmployeeID"] = 1;
            dr["FirstName"] = "Hello";
            dr["LastName"] = "Kitty";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["EmployeeID"] = 2;
            dr["FirstName"] = "Harrison";
            dr["LastName"] = "Jones";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["EmployeeID"] = 3;
            dr["FirstName"] = "Mary";
            dr["LastName"] = "HadALittleLamb";
            dt.Rows.Add(dr);

            return dt;
        }

    }

Open in new window

RIP_LeroiAuthor Commented:
That makes sense except I have a question.  How can I populate the DataTable from my stored procedure instead of manually filling it in with fake info?

...
DataRow dr = dt.NewRow();
            dr["EmployeeID"] = 1;  //How do I get my EmployeeID from SQL to go here?
            dr["FirstName"] = "Hello";
            dr["LastName"] = "Kitty";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["EmployeeID"] = 2;
            dr["FirstName"] = "Harrison";
            dr["LastName"] = "Jones";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["EmployeeID"] = 3;
            dr["FirstName"] = "Mary";
            dr["LastName"] = "HadALittleLamb";
            dt.Rows.Add(dr);

Open in new window

Daniel Van Der WerkenIndependent ConsultantCommented:
You did it in your code here:

SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

Open in new window


Since you do a SELECT * FROM Table, I don't know your column names, but they would be what the select returns.
RIP_LeroiAuthor Commented:
I think I'm probably not explaining it correctly so my apologies.

I have 28 values I need to get from the DataTable to an Employee object.  How do I get data out of the DataTable and into my Employee object?
Daniel Van Der WerkenIndependent ConsultantCommented:
You read them, one at a time, from the data table row.

Using the EntityFramework would make this easier. However, you read a row, then grab each column, one at a time, and fill your object.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIP_LeroiAuthor Commented:
Thanks.  I got it working.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.