Table
Valued ParameterWhen you want bulk insert in SQL server
database then this is best way to do this stuff. Here is example of SQL table
valued parameter.Create parameter:
CREATE
TYPE [dbo].[PersonType] AS TABLE(
[Name] [varchar](50) NULL,
[EmailID] [varchar](50) NULL
)
GO
Store Procedure for insert value to table :
CREATE PROC [dbo].[sp_Insert]
(
@PersonType PersonType
READONLY
)
AS
BEGIN
INSERT INTO tblInfo (Name,EmailID) -- Table tblInfo has two
columns
SELECT Name,EmailID FROM @PersonType
END
Store
Procedure for Update table :
CREATE PROC [dbo].[SP_UPDATE]
(
@PersonType PersonType
READONLY
)
AS
BEGIN
UPDATE tbl SET Name = tp.Name
,EMailID = tp.EmailID
FROM tblInfo tbl INNER JOIN @PersonType as tp ON tbl.EmailID = tp.EmailID
END
Now from Coding side, How to pass parameter
from C#.
public void INSERT()
{
SqlConnection con = new SqlConnection(@"Connection String");
SqlCommand cmd;
if (test.CommandArgument == "1")
{
cmd = new SqlCommand("SP_Insert", con);
}
else
{
cmd = new SqlCommand("SP_UPDATE", con);
}
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter para = new SqlParameter("@PersonType",
System.Data.SqlDbType.Structured);
// para.TypeName =
"dbo.PersonType"; /// For
inline Query
para.Value
= GetName(txtName.Text, txtEmail.Text);
cmd.Parameters.Add(para);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
}
public DataTable GetName(string
Name, string Email)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Email");
DataRow dr = dt.NewRow();
dr["Name"] = Name;
dr["Email"] = Email;
dt.Rows.Add(dr);
return dt;
}
