Using SQL Server User Defined Table Types with Stored Procedures

Take this scenario. You have a requirement where you need to insert 1000s of rows of data in a SQL Server database table. The insert data comes from an external source in the form of an array and to ensure high performance you want to run 1 stored procedure to insert all the data in one hit rather than loop through each interation and insert into the database one by one.

The problem is, how can you pass an array to a SQL Server Stored Procedure as a parameter?

You'll be glad to know that it is possible, but will require a user defined table type on the SQL Server side and some C# to convert the array into a DataTable object.

Let’s look at a coded example...

For this tutorial, I will show you how to do this by creating a user defined table type, a stored procedure that takes this data type as a parameter and within C# we will use Entity Framework to execute the stored procedure and insert the data.

I will be using the Tags table in IntermittentBug as the example. This is a simple table with 4 columns, the first (ID) column being an auto increment Primary Key. As the Primary Key is set to auto increment we only need to insert the Tag, TagURL and Description as SQL Server will automatically assign an ID for us.

Below is the structure of the tags table. It’s very simple and straightforward.

Tags table

CREATE TABLE [dbo].[tbl_Tags](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Tag] [varchar](100) NULL,
	[TagURL] [varchar](100) NULL,
	[Description] [varchar](5000) NULL,
 CONSTRAINT [PK_tbl_Tags] PRIMARY KEY CLUSTERED 

Ok so let’s have a look at the stored procedure we will use that will insert all the data. You can see that we are inserting from a SELECT statement, but rather than selecting from a table we are selecting from the parameter @InsertTable. This parameter is of type “TagsTableType” which is a user defined table type object. To get this working we will need to create the table type.

User Defined Table type

CREATE TYPE [dbo].[TagsTableType] AS TABLE(
	[Tag] [varchar](100) NULL,
	[TagURL] [varchar](100) NULL,
	[Description] [varchar](5000) NULL
)
GO

Insert Tags Stored Procedure

CREATE PROCEDURE [dbo].[Usp_InsertTags]

	-- Add the parameters for the stored procedure here
	@InsertTable TagsTableType READONLY

AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO tbl_Tags (Tag, TagURL, [Description])
		SELECT Tag, TagURL, [Description]
		FROM @InsertTable

END

Ok great so were setup in SQL server, next it’s time to write some C# to populate our table type and execute our stored procedure. To begin let’s create some dummy data with C#. The dummy data will be implemented using a while loop to create a DataTable of 1000 rows of test data. We will use this object to pass to the table type. As long as the columns of the DataTable match exactly our user defined table type we will be able to successfully pass it as a parameter.

private DataTable GenerateDataTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Tag");
    dt.Columns.Add("TagURL");
    dt.Columns.Add("Description");

    int counter = 0;
    while(counter < 1000)
    {
        DataRow row = dt.NewRow();
        row["Tag"] = ("Tag Name " + counter);
        row["TagURL"] = ("Tag URL " + counter);
        row["Description"] = ("Tag Description " + counter);

        dt.Rows.Add(row);
        counter++;
    }

    return dt;
}

Ok cool, so we now have the data table setup and populated with data, we need to import our stored procured into Entity Framework. we will use Entity Framework database first and import our stored procedure into out context.

To finish, let’s look at how we can use Entity Framework to execute our stored procedure and pass our DataTable. The key area is to define the type of the parameter. You can see we do this by assigning the SqlParameter.TypeName as a string of the user defined table type. The we can use the Entity Framework ExecuteSqlCommand to call raw SQL to execute the stored proc passing in the DataTable Parameter.

[TestClass]
public class InseringUsingTableTypes
{
    [TestMethod]
    public void InseringUsingTableTypes_Test()
    {
        using (var DB = new IntermittentBug_EFOverride())
        {
            DataTable _DataTable = GenerateDataTable();

            SqlParameter Parameter = new SqlParameter("@InsertTable", _DataTable);
            Parameter.TypeName = "dbo.TagsTableType";

            DB.Database.ExecuteSqlCommand("exec Usp_InsertTags @InsertTable", Parameter);
        }
    }

    private DataTable GenerateDataTable()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Tag");
        dt.Columns.Add("TagURL");
        dt.Columns.Add("Description");

        int counter = 0;
        while(counter < 1000)
        {
            DataRow row = dt.NewRow();
            row["Tag"] = ("Tag Name " + counter);
            row["TagURL"] = ("Tag URL " + counter);
            row["Description"] = ("Tag Description " + counter);

            dt.Rows.Add(row);
            counter++;
        }

        return dt;
    }
}

Before

After

So, there we have it, a simple example that you can use and understand for similar scenarios in your own projects. Please leave your comments below if you have any feedback or queries regarding this tutorial.


JGilmartin Profile Image

JGilmartin

Technical Architect at Pinewood Technologies

Rating: 2890

C# Expert

Offline


Tutorial Statistics
  • Views: 4918
  • Comments: 0
  • Author: JGilmartin (2890)
  • Date: 31/5/2017 22:14
Tags
C# SQL Server SQL Entity Framework

© 2016 - 2018 - IntermittentBug