Friday, January 11, 2013

Passing Table-valued Parameter to Stored procedure

Table-Valued Parameters:
                Table valued parameter provide the developer an easy way to work with Multiple rows and data. Table-Valued parameters were introduced in Sql Server 2008
Syntax:
create type TypeName AS TABLE(

column1 type,
column2 type...

)

Eg: create type EmployeeDetails AS TABLE(

RowID int,
EmployeeName varchar(25),
EmployeeTitle varchar(30),
EmployeeAge int
)
In the above eg,
We are creating a table valued type for EmployeeDetails,this table valued type replicate the same of a Sql Table.

Manipulation on Table Valued Type:

     Table valued parameters are same as Sql Table.

You can insert into table valued Type as same like how you insert value to a table.


Declare @EmployeeDt as EmployeeDetails
insert into @EmployeeDt values(1,'George','Manager',24)
insert into @EmployeeDt values(2,'Rahul','Tester',38)
You can select the entire data from that table-valued type by
a single select statement.

select * from @EmployeeDt



Passing Table-Valued type to StoredProcedure:
create procedure SaveEmployeeDetails
@EmployeeDt as EmployeeDetails Readonly
AS
Begin
--selecting from table valued parameter and inserting into table --in a single strech
insert into Tbl_Employee(ID,EmployeeName ,EmployeeTitle,EmployeeAge)
select  RowID,EmployeeName ,EmployeeTitle,EmployeeAge
from @EmployeeDt
End



C# Code:
SqlDatabase sqlConn;
DbCommand dbcmd;
SqlConn = new SqlDatabase ("your Connection string here");
dbcmd = sqlConn.GetStoredProcCommand(SaveEmployeeDetails);
dbcmd.CommandType = CommandType.StoredProcedure;
sqlConn.AddInParameter(dbcmd, "@EmployeeDt", SqlDbType.Structured, dt);
              
 sqlConn.ExecuteNonQuery (dbcmd);