Today we will discuss importance of Table Types provided in MS SQL Server. This is probably the most under rated feature although extremely powerful and useful while developing an Enterprise application.
Real time scenario:
It is a very common scenario that as a developer we have to pass large amount of data to the database at once. For instance A Data Table having 1000 rows, more or less. So if we have such situation then most of the times as a developer we call our Insert Stored Procedure 100 times in a loop. Classic way !
With UDT very easily we can pass entire object of Data Table to a Stored Procedure as a single parameter. Looping then can be done in Database itself which will ultimately boosts overall performance.
In the MS SQL Server Query :
CREATE TYPE [dbo].[MyCustomType] AS TABLE
[FieldId] [int] NOT NULL,
[FieldName] [varchar(10)] NOT NULL,
It can be viewed here:
Then I will create a Stored Procedure which will accept
MyCustomType as a parameter.
CREATE PROCEDURE [dbo].[SP_InsertDataDemo] (
@in_multipleRecords MyCustomType READONLY
//Logic goes here
SELECT * FROM @in_multipleRecords WHERE (your condition)
Creating Sample DataTable to pass:
Creating DataTable – Language C#
DataTable dt = new DataTable();
for (int i = 0; i < 100; i++)
DataRow dr = dt.NewRow();
dr[“FieldId”] = “Field_”+i.ToString();
dr[“FieldName”] = “FieldName_”+i.ToString();
dr[“FieldCreatedDate”] = DateTime.Now;
dr = null;
Note: Column names and Data Type of the columns in said Data Table must be exact the same as per the
[MyCustomType] Type which we had defined in the MS SQL Server.
Passing the DataTable to Stored Procedure:
Now that we have everything set up. Let’s get into action asap.
SqlConnection con = new SqlConnection("Data Source= ; initial
catalog= Northwind ; User Id= ; Password= '");
SqlCommand command = new SqlCommand("SP_InsertDataDemo",con);
That’s it ! Entire Data Table will be passed in one go.
When you have multiple rows to be passed, UDT is an option available for quickly performing the task from front-end or from your DAL layer.
Ensure that Data validation is performed at Data Table level itself so that invalid data should not reach till your Database.