Table as parameter SQL Server

SQL Server provides a special parameter named table parameter. Using table parameter user can pass entire table as other parameter pass to store procedure. Table parameter is better alternate of temporary table. It reduces unnecessary work and time. Which need to pass more than one row to database for save.

Table Structure

Field Name	Data Type
Roll	Int
Name	Varchar(30)
City	Varchar(40)

Store Procedure

Table Type

CREATE TYPE dbo.myDataType AS TABLE  

( roll int, 

  name varchar(30),

  city varchar(30)

 );  

Store Procedure

USE [Girfa_Help]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[AddStudentRecord]

   @TVP myDataType READONLY

as

insert into student

select * from @TVP

C# Code

Model

public class StudentModel

{

    public int roll { get; set; }

    public string name { get; set; }

    public string city { get; set; }

}

Repository Code 

public bool StudentRecord(List<StudentModel> stuid)

{

            bool result = false;

            try

            {

                DataTable dt = new DataTable();

                DataColumn dc = new DataColumn();

                dc = new DataColumn("roll");

                dc.DataType = typeof(int);

                dt.Columns.Add(dc);

                dc = new DataColumn("name");

                dc.DataType = typeof(string);

                dt.Columns.Add(dc);

                dc = new DataColumn("city");

                dc.DataType = typeof(string);

                dt.Columns.Add(dc);

             

                foreach (AdmitCardSearchModel item in stuid)

                {

                    DataRow row = dt.NewRow();

                    row[0] = item.Roll;

                    row[1] = item.Name;

                    row[2] = item.City;                  

                    dt.Rows.Add(row);

                }

                    using (SqlConnection dbcon = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ToString()))

                    {

                        using (SqlCommand cmd = new SqlCommand("[dbo].[AddTemporaryIDForAdmitCard]", dbcon))

                        {

                            cmd.CommandType = CommandType.StoredProcedure;

                            cmd.Parameters.AddWithValue("@TVP", dt);

                           

                            if (dbcon.State == ConnectionState.Closed)

                                dbcon.Open();

                            cmd.ExecuteNonQuery();

                            result = true;

                        }

                    }

               

               

            }

            catch(Exception ex)

            {

               

            }

            return true;

           

        }

 Next Topic