Link to home
Start Free TrialLog in
Avatar of RIP_Leroi
RIP_Leroi

asked on

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.
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Can you please show the code you are using now to get the data from the stored procedure.
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/
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".
Avatar of RIP_Leroi
RIP_Leroi

ASKER

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

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

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

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.
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?
ASKER CERTIFIED SOLUTION
Avatar of Daniel Van Der Werken
Daniel Van Der Werken
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.  I got it working.