C# – Using Table Valued Parameter – Csharp Star

On this article, we’ll be taught:

• What’s Desk Valued Parameter?
• The way to cross Desk Valued Parameter from C#?
• Benefits of utilizing Desk Valued Parameter?

 

What’s Desk Valued Parameter?

Desk Valued Parameters are used to cross a number of rows of knowledge from a .internet/consumer utility to SQL Server with out a number of spherical journeys. we will cross a number of rows of a desk to a saved process.
 

The way to create a Desk Valued Parameter and cross it from C#?

 

Step 1: Create a Desk in SQL server

On this instance, we’ll create a SALESPERSON desk.

CREATE TABLE SALESPERSON
(
      ID INT NOT NULL, 
      Title VARCHAR(MAX), 
      IsActive VARCHAR(10),
      PRIMARY KEY (ID)
)

 

Step 2: Create a Desk Valued Parameter in SQL server

CREATE TYPE dbo.SALESPERSONTYPE AS TABLE 
(
      ID INT NOT NULL, 
      Title VARCHAR(MAX), 
      IsActive VARCHAR(10),
      PRIMARY KEY (ID)
)

 

Step 3: Create a saved process to Insert data to SALESPERSON desk in DB

CREATE PROCEDURE InsertData
(@SALESPERSONTYPE  AS dbo.SALESPERSONTYPE READONLY)
AS
BEGIN
      INSERT INTO SALESPERSON(ID ,Title ,IsActive)
      SELECT ID, Title, IsActive FROM @SALESPERSONTYPE  
END

 

Step 4:Create a DataTable from ADO.internet identical as your Desk Valued Parameter

 

static DataTable CreateTable()

    DataTable dt = new DataTable();
    dt.Columns.Add("ID", typeof(Int32));
    dt.Columns.Add("Title", typeof(string));
    dt.Columns.Add("IsActive", typeof(string));
    return dt;

 

Step 5: Move DataTable to Saved Process as a parameter

//Create Desk
DataTable salepersonTable = CreateTable(); 

// Add New Rowto desk
salepersonTable.Rows.Add(1, "Lisa","Sure");
salepersonTable.Rows.Add(2, "Jennifer", "No");
salepersonTable.Rows.Add(3, "Tina", "Sure");
salepersonTable.Rows.Add(4, "John", "Sure");
salepersonTable.Rows.Add(5, "Ryan", "Sure");
 
SqlConnection connection = new SqlConnection("Knowledge Supply= DatabaseName;Preliminary Catalog=csharpstar;Person
Id=cs;Password=cs123;");
connection.Open();
SqlCommand cmd = new SqlCommand("InsertData", connection);
cmd.CommandType = CommandType.StoredProcedure;

//Move desk Valued parameter to Retailer Process
SqlParameter sqlParam = cmd.Parameters.AddWithValue("SALESPERSONTYPE", salepersonTable);
sqlParam.SqlDbType = SqlDbType.Structured; 
cmd.ExecuteNonQuery();
connection.Shut();
Console.Write("Knowledge Inserted Efficiently.");

 

Benefits of utilizing Desk Valued Parameter?

• You’ll be able to keep away from a number of spherical journeys between consumer utility and Database
• Efficiency will probably be a lot better since you may insert a number of data as soon as into the desk

 
Thanks for visiting !

© 2017, Csharp Star. All rights reserved.

Associated