menu search more_vert

Pass DataTable to Stored Procedure as Parameter in C#

watch_later 23 January, 2022

In this article, I am going to explain how to pass DataTable to the stored procedure as a parameter. Here I will also explain how to create user-defined table types, stored procedures as well as reading data from notepads.

How to Pass DataTable to Stored Procedure as Parameter in C#

I have seen, many developers get requirements for getting records from files and bulk insert into database. Sometimes they get confused about the technical implementation of such a requirement. Today I got the same requirement for reading data from a notepad file and inserting all those records are available in the notepad into the SQL server database. So, In this article, I will show the easiest way to archive this kind of requirement with suitable examples.

In my previous article, I explained how to write a dynamic PIVOT query as well as an article about how to pass output parameter in stored procedures and how to create and execute parameterized stored procedures from another stored procedure that you might like to read.

So Let's start our implementation of this requirement.

Implementation

I already explained in my previous article, regarding the reading data from the notepad file and preparing a DataTable for all those records available in the notepad file, So reading a file in C# you can follow the following article:

C# | Read Delimited Text File

As per the above article, we created a sample console application in .net core and we have prepared a DataTable by reading a notepad file, So now we have data and we need to perform bulk insertion operation on all these data. 

For the demonstration purpose, First, we need to create a database, table, user-defined table types as well as stored procedure.

In this example, I created a database with the name "DB_Codingvila" and then created a database table with the name "tbl_Articles" where we perform bulk insertion and insert all the records of the notepad file into this particular table.

You can use the following script for creating the able table "tbl_Articles".

Create Table

CREATE TABLE [dbo].[tbl_Articles]
(
    [ArticleId] INT NULL, 
    [Type] VARCHAR(10) NULL, 
    [Title] VARCHAR(100) NULL, 
    [Author] VARCHAR(15) NULL, 
    [Date] DATETIME NULL
)

Now, We have to create the user-defined table type in the SQL server. Here I have created the table type with the name "tblCodingvila".

You can use the following script for creating the user-defined table types "tblCodingvila".

Create Table Types

CREATE TYPE [dbo].[tblCodingvila] AS TABLE(
    [Id] INT NULL, 
    [Type] VARCHAR(10) NULL, 
    [Title] VARCHAR(250) NULL, 
    [Author] VARCHAR(15) NULL, 
    [Date] VARCHAR(15) NULL
)
GO

Note: Make sure that, you need to create the table types as per the exact columns and column names are available in your DataTable, Ultimately table types must have the same schema as DataTable that we are going to pass to the Stored Procedure as a parameter.

Now, we will create a parameterized stored procedure for performing bulk insertion operations. Here I have created the stored procedure with the name "'SP_Insert_Article".

You can use the following script for creating the parameterized stored procedure "SP_Insert_Article".

Stored Procedure

CREATE PROCEDURE [dbo].[SP_Insert_Article]
    @tblCodingvila tblCodingvila READONLY
AS
BEGIN
    SET NOCOUNT ON;
   
    INSERT INTO tbl_Articles(ArticleId, Type, Title,Author,Date)
    SELECT Id, Type, Title,Author,CAST(Date AS datetime) As Date FROM @tblCodingvila
END

Look in the created stored procedure, I have created one parameter @tblCodingvila and selected the data type of this parameter as created user-defined table type "tblCodingvila" and marked it read-only.

Then simply I just inserted all the records into table "tbl_Articles" using a select statement on "@tblCodingvila". whare "tblCodingvila" will have all the records of DataTable which we will pass as parameters into a stored procedure.

Now, Let's write and c# code for passing DataTable with all records to the stored procedure, execute the stored procedure, and perform bulk insertion operation.

To deal with the SQL server database, we need to import the following library or NuGet packages into our application.

Go to Solution explorer >> Right-click on dependencies >> Manage NuGet packages

Manage NuGet packages


In the next window, search for SQL Client >> Select System.Data.SqlClient >> Install

System.Data.SqlClient


Now, we need to connect our application with the SQL server. So, we need to write a connection string. For the connection string, you have to follow the below steps.

Go to Solution Explorer >> Select Project >> Right-click and select Add New Item >> Search for Config >> Select Application Configuration File >> Add.

Application Configuration File


Now, we need to write the following connection string into our app.config file.

App.config file

<configuration>
  <connectionStrings>
    <add name="strConnection" connectionString="Data Source=localhost;Initial Catalog=DB_Codingvila;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

To fetch the connection string from aap.config file we need to install one another NuGet Package "ConfigurationManager" Install the same as the SQL Client NuGet Package.

Now, we need to import the following namespace into our c# code file.

Namespace

using System.Data.SqlClient;
using System.Configuration;

Let's start actual implementation for bulk insert and pass DataTable to a stored procedure as a parameter.

C#

static void Main(string[] args)
      {
          //Fatch File Path
          string path = @"F:\Codingvila\Codingvila_ReadDelimitedFile\Files\Data.txt";
 
          //Call Readfile method and pass required parameters
          DataTable dtTable = ReadFile(FilePath: path, delimiter: '|', isFirstRowHeader: true);
 
          //Connect Database Server and Insert Records
          #region Insert Records into Database
          if (dtTable!= null && dtTable.Rows.Count > 0)
          {
              try
              {
                  //fetch connection string from app.config file
                  string consString = ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString;
                  //creating object for SqlConnection class
                  using (SqlConnection con = new SqlConnection(consString))
                  {
                      //creating object for SqlCommand class
                      using (SqlCommand cmd = new SqlCommand("SP_Insert_Article"))
                      {
                          //set commandType as stored procedure
                          cmd.CommandType = CommandType.StoredProcedure;
                          cmd.Connection = con;
                          //pass datatable to stored procedure as parameter
                          cmd.Parameters.AddWithValue("@tblCodingvila", dtTable);
                          //open connection
                          con.Open();
                          //execute stored procedure
                          cmd.ExecuteNonQuery();
                          //close connection
                          con.Close();
                          Console.WriteLine("Articles are inserted successfully.");
                      }
                  }
              }
              catch (Exception ex)
              {
                  Console.WriteLine("Oho, Something went wrong, " + ex.Message.ToString());
              }
          }
          #endregion
          Console.ReadKey();
      }

Explanation:

As you can see in the code, Here I have checked the condition for the DataTable and checked for whether DataTable has valid records or not. 

Then I have fetched the connection string from the app.config file and stored it into a local string variable called as consString.

Now, by creating an object of SQL connection, prepared a connection based on the connection string fetched from the config file. 

Then I have created another object for the SQL command, I have written the command type as stored procedure and pass the name of a created stored procedure into it and along with that I also add the required parameter as a DataTable. 

Finally, I opened the connection object, execute the stored procedure, and then closed the open connection of the SQL server, and printed the appropriate success and validation messages.

Output:


Result


Summary

In this article, we learned how to implement bulk import functionality using c# using the stored procedure by passing the DataTable as a parameter into Stored procedure.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon

expand_less