Table Valued Parameters

Last updated on

This tutorial will teach you how to store a big data table in C# or .NET into a database table using table-valued parameters in the SQL server. This feature enables you to send the entire data table from C# or .NET as an SQL parameter to a procedure or function.

Before getting started, if you’re looking for how to store a million records, you must read the SQL Server Bulk Insert tutorial..

Anyway, table-valued parameters allow you to define a new table as a user-defined data type, providing you with the capability to send a data table from C# or .NET into SQL Server procedures or functions.

Before 2008, we were unable to pass a table variable into SQL procedures or SQL functions. This limitation compelled us to send the data of table rows one by one into the database, which significantly impacted the application’s performance.

But currently, the table-valued parameters like sending a single value. very fast to store big data table in database table rather than sending the C# data table into SQL row by row, it may crash the program and take so time and high memory usage.

In the following section, I will cover how to create a new “user-defined table type.”

Passing C# DataTable into SQL Procedure Using Table-Valued Parameters

Before introducing SQL Server, in 2008, a new method was devised to facilitate the storage of table-valued parameters into database tables using “UDTTs” (User-Defined Table Types) all at once.

The “User-Defined Table Types” list is located in Microsoft SQL Management Studio. You can follow these steps to view the user-defined table types.

  1. Open Microsoft SQL Management Studio and connect to SQL Server.
  2. If you don’t have a database, create a new one and then expand it.
  3. By opening the “Programmability” tab, you will find the “Types” tab that you need to expand.
  4. Under the “Types” section, you will find “User-Defined Table Types,” where all table-valued parameters are stored.

Let’s get started with the UDTTs definition.

User-Defined Table Types

The User-Defined Table Types are predefined tables created by users to store temporary data with specified data types. Using User-Defined Table Types, we can seamlessly transmit a large data table from .NET to an SQL Server and save it in one go without any issues.

To achieve this, the initial step is to create a database table using the following command, ensuring that you store the same data types in the User-Defined Table Types.

-- DATABASE TABLE
CREATE TABLE [dbo].[accounts](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [account_name] varchar (50) NULL,
  [account_number] varchar(50) NULL, 
  [parent_account] varchar (50) NULL
);

Let’s move onto the below section to understand how to create the table-valued parameters.

Create Table-Valued Parameters

In the next step, you must define a new table type in SQL Server, and inside it, define all column types exactly as you did in the SQL database table by executing the following command.

-- USER DEFINED TABLE TYPE
CREATE TYPE [dbo].[Accounting_Tree] AS TABLE(
  [account_name] varchar (50) NULL,
  [account_number] varchar(50) NULL, 
  [parent_account] varchar (50) NULL 
);

Once you execute the provided code, a new table type will appear in the list of User-Defined Table Types, as illustrated in the image below.

user defined table types

Note: The columns of the data table originating from .NET must precisely match those in the table data type, including the exact number of columns.

But how to use this inside the stored procedures? The answer will be in the following section.

Using the Defined Table Type in Stored Procedures

When we create a procedure, some defined parameters start with ‘@,’ each one consisting of two parts: the parameter’s name and the data type. We will apply the same approach to define table-valued parameters, akin to declaring a variable.

@table_params varchar(50)

Our newly defined table is named “[dbo].[Accounting_Tree]” so we need to replace the ‘varchar(50)’ data type with it and add “READONLY” next to it. The code should resemble the following.

@table_params AS [dbo].[Accounting_Tree] READONLY

While the body of the procedure should select each column individually for insertion into the database table, before that, we must determine if we already have at least one row in the table-valued parameters.

IF EXISTS( SELECT 1 FROM @table_params )
BEGIN
INSERT INTO accounts ( 1.., 2.., 3... ) SELECT 1.., 2.., 3.. FROM @table_params
END

So our final procedure should be like the below code.

CREATE PROC table_valued_parameter 
  @table_params AS [dbo].[Accounting_Tree] READONLY
AS
IF EXISTS( SELECT 1 FROM @table_params )
BEGIN
  INSERT INTO accounts (account_name, account_number, parent_account ) 
    SELECT account_name, account_number, parent_account FROM @table_params
END

Let’s understand how to send this table from C# to the SQL Server procedure.

Sending DataTable from C# to SQL Procedure

In this section, we will focus a bit on the C# data table. Firstly, we need to add a new form with a data table, and don’t forget the namespaces. You need to create only a new C# desktop project in Visual Studio. Then, add the namespace for SQL “System.Data.SqlClient”.

Then, add a new button to store this data table in this form.

After that, write the database connection code as in the snippet below:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;

namespace WindowsFormsApp1
{
    

    public partial class Form1 : Form
    {
        string con_string;
        SqlConnection cnn; 
        DataTable table;

        public Form1()
        {
            this.con_string = @"Data Source=.\SQLEXPRESS;Initial Catalog=dbtest; Integrated Security =true";
            this.cnn = new SqlConnection(this.con_string);

            InitializeComponent();
        }

 
    }

}

Next, we have to create a data table and fill it with six rows. However, the data inside this table should have the same data type as in the table-valued parameters.

private void Form1_Load(object sender, EventArgs e) {
     
   this.table = new DataTable();

   // Add Columns 
   this.table.Columns.Add("account_name", typeof(String));
   table.Columns.Add("account_number", typeof(String));
   table.Columns.Add("parent_account", typeof(String));

   // Add 6 Accounts To Table 
   DataRow row;
   for (int i = 1; i <= 6; i++) {

      string account_name = "Account Number " + i; 
      string account_number = "00" + i;
      string parent_account = "parent_account_" + i;

      // Add the new row to data table 
      row = table.NewRow();
      row["account_name"] = account_name;
      row["account_number"] = account_number;
      row["parent_account"] = parent_account;
      table.Rows.Add(row);
   }

}

The previous code will execute the data table when we load the application form, serving as an event to store all the data table rows.

Now, we need to send this data table to the SQL procedure. To do this, we have to define a new SQL data type with an SQL parameter called SqlDbType.Structured.

sql db type

Then, send the C# data table to the SQL procedure using the following function.

public void Store_DataTable_Into_Database() {

  this.cnn.Open();
  SqlCommand sqlcmd = new SqlCommand();
  sqlcmd.CommandType = CommandType.StoredProcedure;

  //Path The name of Procedure
  sqlcmd.CommandText = "table_valued_parameter";
  sqlcmd.Connection = cnn;

  // Procedure Parameters 
  SqlParameter[] param = new SqlParameter[1];

  param[0] = new SqlParameter("@table_params", SqlDbType.Structured);
  param[0].Value = this.table;

  sqlcmd.Parameters.AddRange(param);
  sqlcmd.ExecuteNonQuery();

  this.cnn.Close(); 

}

// Button to store the datatable in database 
private void button1_Click(object sender, EventArgs e)
 {
   this.Store_DataTable_Into_Database();    
 }

The results should appear as in the image below.

Showing Table OF SQL Server

But, what will happen if we click on the same button again?

It will store the duplicate data table rows, which means we will have duplicated records in the database table.

To solve this problem, we must detect the rows that already exist in our database table and then update those rows or take another action.

Therefore, we should update the SQL commands of the procedure to perform certain actions for the incoming data table.

Anyway, let’s move on to the section below to understand how to update the datatable that is already stored in the SQL Server database.

Updating DataTable Using Table-Valued Parameters

Updating the datatable using table-valued parameters involves three scenarios. However, before we delve into these scenarios, it’s crucial to remember that the rows in the datatable should have unique IDs or numbers.

  • Updating rows that have duplicate keys or IDs in table-valued parameters and database tables..
  • Storing the new data in table-valued parameters that are not found in a database table.
  • Finally, delete all rows from the database table that do not already exist in the table-valued parameter.

Step 1: Updating DataTable Rows

In the image below, you will see two tables. The right one represents the database table, and the left one represents the incoming C# datatable. If you observe the left table, you will notice many fonts in green color. These green fonts indicate that the datatable has some changes in values that need to be updated in the database table. All rows have unique numbers.

Update DataTable SQL Server and CSharp

Actually, we will use the “account_number” column as unique IDs. If you make a comparison between the two tables, you will see the same account_number values. Therefore, we have to use this column to detect and search in the database table, and then we can update with the new changes. The code below demonstrates this.

UPDATE [dbo].accounts SET
      account_name = udtts.account_name,
      parent_account = udtts.parent_account
   FROM [dbo].accounts
      INNER JOIN @table_params AS udtts
      ON [dbo].accounts.account_number = udtts.account_number;

Step 2: Insert the New Rows in the Data Table Rows

In this scenario, we must set a condition to filter only the records in the C# datatable that do not exist in the database table. Essentially, it’s like setting an array with account numbers that are not needed, and then storing the other account numbers.

Update data using table values parameters

In the previous image, the yellow-green fonts indicate new records that need to be inserted into the database table. The question then arises: how can we filter only these new records to store them in the database table?

As mentioned before, we can set an array with unwanted rows, so the code will look like the snippet below.

INSERT INTO [dbo].accounts(account_name,parent_account,account_number)
    SELECT account_name,parent_account,account_number FROM @table_params
    WHERE account_number NOT IN (SELECT account_number FROM [dbo].accounts );

Step 3: Delete Unwanted Rows from Database Table

If you refer back to the previous scenario, you will notice there are some rows in the database table that are not found in the C# datatable.

In the image below, a red rectangle highlights some records in the database table that are not found in the C# datatable. We only need to collect these records and delete them because they do not exist in the incoming C# datatable.

Deleting unneed rows by sql valued parameters

The code below demonstrates the delete query.

DELETE FROM [dbo].accounts 
    WHERE account_number NOT IN (SELECT account_number FROM @table_params );

That’s all :), The final code in the SQL procedure should look like the below.

ALTER PROC [dbo].[table_valued_parameter]
   @table_params AS [dbo].[Accounting_Tree] READONLY
AS
   IF EXISTS( SELECT 1 FROM @table_params )
   BEGIN
      
      -- Update
      UPDATE [dbo].accounts SET
         account_name = udtts.account_name,
         parent_account = udtts.parent_account
      FROM [dbo].accounts
        INNER JOIN @table_params AS udtts
        ON [dbo].accounts.account_number = udtts.account_number

      -- STORE
      INSERT INTO [dbo].accounts(account_name,parent_account,account_number)
        SELECT account_name,parent_account,account_number FROM @table_params
        WHERE account_number NOT IN (SELECT account_number FROM [dbo].accounts )

      -- DELETE
      DELETE FROM [dbo].accounts
      WHERE account_number NOT IN (SELECT account_number FROM @table_params );

   END

Anyway, Let’s summarize it

Wrapping Up

In this tutorial, we learned how to send a big data table from C# to SQL procedure using table-valued parameters—and updating the same data in the database table. The User-Defined Table Types tab is already containing all table-valued parameters.

The data table columns that are coming from .NET should be the same columns in table data type and the exact columns count.

Thank you for reading. Happy Coding!

Share on: