Fill C# object with SQL Server Stored Procedure return data

9/4/2015

Fernando Soto

9/4/2015

Can you please show the code you are using now to get the data from the stored procedure.

Ryan Chong

9/4/2015

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/

9/4/2015

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
}

where the column name in the datatable is "EmployeeID".

9/4/2015

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

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

9/4/2015

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

    }

9/4/2015

ASKER

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

9/4/2015

You did it in your code here:

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

Since you do a SELECT * FROM Table, I don't know your column names, but they would be what the select returns.

9/4/2015

ASKER

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

9/4/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial