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.
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.
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/
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,
where the column name in the datatable is "EmployeeID".
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".
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();
}
}
The mechanism you're using is fine. Looks like you already understand what you need to do.
Does this example help?
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;
}
}
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);
You did it in your code here:
Since you do a SELECT * FROM Table, I don't know your column names, but they would be what the select returns.
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.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. I got it working.