Saturday, 13 January 2018

Table as parameter SQL Server

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