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.