User Defined Table Types (UDT)

Hi Folks,

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.

Implementation:

Creating UDT:

In the MS SQL Server Query :


CREATE TYPE [dbo].[MyCustomType] AS TABLE
(
[FieldId] [int] NOT NULL,
[FieldName] [varchar(10)] NOT NULL,
[FieldCreatedDate] [DateTime]
)

It can be viewed here:

SQlTypes1

Then I will create a Stored Procedure which will accept MyCustomType as a parameter.

Creating Procedure:


CREATE PROCEDURE [dbo].[SP_InsertDataDemo] (
@in_UserId INT,
@in_UserName VARCHAR(30),
@in_multipleRecords MyCustomType READONLY
)
AS
BEGIN
//Logic goes here
SELECT * FROM @in_multipleRecords WHERE (your condition)
END

Creating Sample DataTable to pass:

Creating DataTable – Language C#


DataTable dt = new DataTable();
dt.Columns.Add("FieldId", typeof(int));
dt.Columns.Add("FieldName", typeof(string));
dt.Columns.Add("FieldCreatedDate", typeof(DateTime));

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;
dt.Rows.Add(dr);
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);
command.Parameters.AddWithValue("@in_UserId",123);
command.Parameters.AddWithValue("@in_UserName","user");
command.Parameters.AddWithValue("@in_multipleRecords",dt);
con.Open();
int i=command.ExecuteNonQuery();
con.Close();

That’s it ! Entire Data Table will be passed in one go.

Conclusion:

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.

P.S. –

Ensure that Data validation is performed at Data Table level itself so that invalid data should not reach till your Database.

Thank you.

GauranG

One thought on “User Defined Table Types (UDT)

Leave a Reply to Kiran Sarode Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s