How to call a stored procedure with table valued parameters in c#?

home / software / tips and tricks / How to call a stored procedure with table valued parameters in c#?

How to call a stored procedure with table valued parameters in c#?

Category: C#
view more software Tips and Tricks

Updated: 04/30/2009 12:04 PM
Author: Shiju Mathews

Status: Resolved.

For creating stored procedure with table valued parameters in SQL Server 2008 refer sing table valued parameters in SQL Server 2008

In order to execute the stored procedure from c#. First we need to create a data table that is matching with the “EMPLOYEETABLETYPE” and Fill with values.

//Create Data table and add Colums
DataTable tblNewEmployee = new DataTable();
tblNewEmployee.Columns.Add("EmployeeName");
tblNewEmployee.Columns.Add("Birthdate", Type.GetType("System.DateTime"));
tblNewEmployee.Columns.Add("Age", Type.GetType("System.Int32"));
//Add Rows
DataRow newRow = tblNewEmployee.NewRow();
newRow["EmployeeName"] = "Shiju";
newRow["Birthdate"] = DateTime.Now.AddYears(-40);
newRow["Age"] = 40;
tblNewEmployee.Rows.Add(newRow);
newRow = tblNewEmployee.NewRow();
newRow["EmployeeName"] = "Nima";
newRow["Birthdate"] = DateTime.Now.AddYears(-35);
newRow["Age"] = 35;
tblNewEmployee.Rows.Add(newRow);
newRow = tblNewEmployee.NewRow();
newRow["EmployeeName"] = "Akhil";
newRow["Birthdate"] = DateTime.Now.AddYears(-8);
newRow["Age"] = 8;
tblNewEmployee.Rows.Add(newRow);
newRow = tblNewEmployee.NewRow();
newRow["EmployeeName"] = "Shilpa";
newRow["Birthdate"] = DateTime.Now.AddYears(-6);
newRow["Age"] = 6;
tblNewEmployee.Rows.Add(newRow);
//Create Parameter List
int ReturnValue = 0;
SqlParameter[] sqlParameter = new SqlParameter[2];
sqlParameter[0] = new SqlParameter("@ReturnValue", SqlDbType.Int, 0);
sqlParameter[1] = new SqlParameter("@TABLEVARIABLE", SqlDbType.Structured, 0);
sqlParameter[0].Direction = ParameterDirection.ReturnValue;
sqlParameter[0].Value = 0;
sqlParameter[1].Value = tblNewEmployee;
using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand("USP_EMPLOYEEGROUP", sqlConnection))
{
try
{
sqlCommand.Parameters.AddRange(sqlParameter);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
catch (SqlException sqlException)
{ ReturnValue = 1;
}
catch (Exception exception)
{
ReturnValue = 1;
}
finally
{
}
}
}

Indian Resources by Category

Indian Grocery Stores

Indian Jewelry Stores

Indian Hindu Temple

alt="Add your business to our listing Free"

Software Resources

Tips and Tricks

Interview Questions for C#

Interview Questions for ASP.Net

Interview Questions for SQL Server