This tutorial will teach you how to store C# or .NET a big data table into a database table using table-valued parameters in the SQL server. The thing that allows you to send the whole data table from C# or .NET as an SQL parameter into a procedure or function.
Table Of Contents
Before getting started, if you’re looking for how to store a million records, you have to read the SQL Server Bulk Insert tutorial.
Anyway, the table-valued parameters, allow you to define a new tabular as a user table data type to give you the ability to send a data table from C# or .Net into SQL server procedures or functions.
Before 2008 we were not able to pass a table variable into SQL procedures or SQL functions, the thing that compelled us to send the data of table rows one by one into the database. And that was affecting the application performance.
In the next section, I am going to cover how to create a new “user-defined table type”.
Passing C# Datatable into SQL Procedure Using Table-Valued Parameters
Before introducing the SQL server, In 2008 they made a new way to help us to store the table-valued parameters into the database tables using “UDTTs” User-Defined Table Types at one time.
The “User-Defined Table Types” list is located on the Microsoft SQL management studio. You can follow these steps to see the user-defined table types.
- Open Microsoft SQL management studio and connect with SQL server
- If you have no database, create a new one and then expand it.
- By opening the programmability tab, you find the types tab you have to expand.
- Under the types, you will find User-Defined Table Types. All table-valued parameters are stored behind this folder.
User-Defined Table Types
The User-Defined Table Types are predefined tables created by users to store temporary data with the defined data types. We can send a giant data table from .NET to an SQL server using the User-Defined Table Types and save it at once without any problem.
To do that, firstly, you must create a database table using the below command and make sure that you will store the same data type 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
);Create Table-Valued Parameters
In the next step, we need to define a new table type in the SQL server, and inside it, you should define all column types as you did in the SQL database table by running the below 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 above code, you will see a new table type defined in the list of The User Defined Table Types like the below image.
Note: The data table columns coming from .NET should be the same in table data type and the exact columns count.
Using the Defined Table Type in Stored Procedures
When we create a procedure, some defined parameters start with “@” each one contains two parts: the name of the parameter and the type of data. We will do the same to define table-valued parameters. It’s like a declared variable.
@table_params varchar(50)Our new defined table is called “[dbo].[Accounting_Tree],” so we need to put it instead of the varchar(50) data type and then add a “READONLY” word beside it. It should be like the below code.
@table_params AS [dbo].[Accounting_Tree] READONLYWhile, the body of the procedure should select each column as alone to insert it into the database table but before that, we need to detect if we already have at least one row in table-valued parameters.
IF EXISTS( SELECT 1 FROM @table_params )
BEGIN
INSERT INTO accounts ( 1.., 2.., 3... ) SELECT 1.., 2.., 3.. FROM @table_params
ENDSo 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
ENDSending DataTable from C# to SQL Procedure
In this section, we will focus a little bit on the C# data table. So 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 of the SQL ” System.Data.SqlClient “
Then add a new button to store this data table in this form.
After that, write the database connection code like the below snippet
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 6 rows. But the data inside this table should have the same data type 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, so it’s an event to store all data table rows.
Now, we need to send this data table into the SQL procedure. To do that, we have to define a new SQL data type with SQL parameter. It called SqlDbType.Structured.
Then send the C# data table into 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 be like in the below image.
But!, What will happen if we click on the same button again?
It will store the duplicate data table rows. And that means we will have duplicated records in the database table.
To solve this problem, we have to detect the rows that already exist in our database table and then update those rows or do something else.
So we would update the SQL commands of the procedure to do some potential actions for the incoming data table.
Updating DataTable using Table-Valued Parameters
Updating the data table using table-valued parameters has three scenarios. But before we talk about these scenarios, we have to remember that. The rows in the data table should have unique ids or numbers.
- Updating rows have the 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 already do not exist in the table-valued parameter.
Step 1: Updating DataTable Rows
In the below image you will see two tables. The right one is for the database table and the left one is for the incoming C# datatable. If you look at the left table you will see a lot of fonts with green colors. These fonts mean the data table has some changes in values that are needed to be changed in the database table. All rows have unique numbers.
Actually, we will use “account_number” column as unique ids. If you made a comparison between the two tables you will see the same account_number values. So we have to use this column to detect and search in the database table then we can update the new changes. The below code shows you that.
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 have to set a condition to filter only the records in the C# data table that don’t exist in the database table. So, the thing like exactly you set an array with not needed account numbers. Then store the other account numbers.
In the previous image, you will see yellow-green fonts that are new records and we need to insert them into the database table. The question is, how can we filter only these new records to store them in the database table?
As we mentioned before, we can set an array with unwanted rows so the code will be like the below snippet.
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 back to the previous scenario you will see there are some rows in the database table that are not found in the C# data table.
In the below image there is a red rectangle specifying some records in the database table that are not found in the C# data table, only we need to collect these records and delete them. Because they don’t exist in the incoming C# data table.
The below code shows you 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 be 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 );
ENDThe Difference between Sending Datatable Rows One by One and Table-Valued Parameters
As we mentioned before when we send data table to SQL server by using table-valued parameters. it is 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.
Conclusion
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.