Monday, 27 January 2014

Table valued parameter in SQL Server

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




No comments:

Post a comment