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.
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:
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
In the next window, search for SQL Client >> Select System.Data.SqlClient >> Install
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.
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:
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.
