Post
Ask Question

Inserting CSV File Records Into Database Using ASP.NET C#

Background
I have often read the common question in forum posts, how to upload CSV file records into a database but no one has provided the proper solution and many solutions contain a lot of code that is not required so by considering the preceding requirements I have decided to write this article to provide the solution to insert CSV file records into the database with a minimum amount of code. So let us start creating an application so beginners can also understand.
First create the table named Employee using the following script:
  1. CREATE TABLE [dbo].[Employee](    
  2.     [id] [int] IDENTITY(1,1) NOT NULL,    
  3.     [Name] [varchar](50) NULL,    
  4.     [City] [varchar](50) NULL,    
  5.     [Address] [varchar](50) NULL,    
  6.     [Designation] [varchar](50) NULL,    
  7.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED     
  8. (    
  9.     [id] ASC    
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]    
  11. ) ON [PRIMARY] 
Then the design view of the table will look such as follows
 
 
Create the some CSV file with the following records 
 
 
Now Let us create the sample web application as follows:
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
  2. "File" - "New WebSite" - "C#" - "Empty WebSite" (to avoid adding a master page).
  3. Provide the web site a name such as "InsertCSVFileIntoDataBase" or another as you wish and specify the location.
  4. Then right-click on Solution Explorer - "Add New Item" - Add Web Form.
  5. Drag and drop one Button and FileUploader controler onto the <form> section of the Default.aspx page.

Now the default.aspx Page source code will look such as follows.

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head id="Head1" runat="server">    
  7.     <title>Article by Vithal Wadje</title>    
  8. </head>    
  9. <body bgcolor="gray">    
  10.     <form id="form1" runat="server">    
  11.     <div style="color: White;">    
  12.         <h4>    
  13.             Article for C#Corner    
  14.         </h4>    
  15.         <table>    
  16.             <tr>    
  17.                 <td>    
  18.                     Select File    
  19.                 </td>    
  20.                 <td>    
  21.                     <asp:FileUpload ID="FileUpload1" runat="server" />    
  22.                 </td>    
  23.                 <td>    
  24.                 </td>    
  25.                 <td>    
  26.                     <asp:Button ID="Button1" runat="server" Text="Upload" OnClick="Button1_Click" />    
  27.                 </td>    
  28.             </tr>    
  29.         </table>  <br /><br />  
  30.         
  31.     </div>    
  32.       
  33.     </form>    
  34. </body>    
  35. </html>   
Create a function for Sqlconnection as:
  1. private void connection()    
  2. {    
  3.     sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString;    
  4.     con = new SqlConnection(sqlconn);    
  5. }    
 Create function to insert CSV file records into database using SqlBulkCopy as:
  1. //Function to Insert Records  
  2. private void InsertCSVRecords(DataTable csvdt)  
  3. {
  4.     connection();  
  5.     //creating object of SqlBulkCopy    
  6.     SqlBulkCopy objbulk = new SqlBulkCopy(con);  
  7.     //assigning Destination table name    
  8.     objbulk.DestinationTableName = "Employee";  
  9.     //Mapping Table column    
  10.     objbulk.ColumnMappings.Add("Name", "Name");  
  11.     objbulk.ColumnMappings.Add("City", "City");  
  12.     objbulk.ColumnMappings.Add("Address", "Address");  
  13.     objbulk.ColumnMappings.Add("Designation", "Designation");  
  14.     //inserting Datatable Records to DataBase    
  15.     con.Open();  
  16.     objbulk.WriteToServer(csvdt);  
  17.     con.Close();  
  18. } 
Write the following code  to read  CSV File Records and call InsertCSVRecords function on button click as
  1. protected void Button1_Click(object sender, EventArgs e)  
  2. {  
  3.     //Creating object of datatable  
  4.     DataTable tblcsv = new DataTable();  
  5.     //creating columns  
  6.     tblcsv.Columns.Add("Name");  
  7.     tblcsv.Columns.Add("City");  
  8.     tblcsv.Columns.Add("Address");  
  9.     tblcsv.Columns.Add("Designation");  
  10.     //getting full file path of Uploaded file  
  11.     string CSVFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);  
  12.     //Reading All text  
  13.     string ReadCSV = File.ReadAllText(CSVFilePath);  
  14.     //spliting row after new line  
  15.     foreach (string csvRow in ReadCSV.Split('\n'))  
  16.     {  
  17.         if (!string.IsNullOrEmpty(csvRow))  
  18.         {  
  19.             //Adding each row into datatable  
  20.             tblcsv.Rows.Add();  
  21.             int count = 0;  
  22.             foreach (string FileRec in csvRow.Split(','))  
  23.             {  
  24.                 tblcsv.Rows[tblcsv.Rows.Count - 1][count] = FileRec;  
  25.                 count++;  
  26.             }  
  27.         }    
  28.     }  
  29.     //Calling insert Functions  
  30.     InsertCSVRecords(tblcsv);  
  31. } 
The entire code of the default.aspx.cs page will look as follows:
  1. using System;  
  2. using System.IO;  
  3. using System.Data;  
  4. using System.Data.SqlClient;  
  5. using System.Configuration;  
  6. public partial class _Default : System.Web.UI.Page  
  7. {  
  8.      
  9.     SqlConnection con;  
  10.   
  11.     string sqlconn;  
  12.     protected void Page_Load(object sender, EventArgs e)  
  13.     {  
  14.     }
  15.     private void connection()  
  16.     {  
  17.         sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString;  
  18.         con = new SqlConnection(sqlconn);
  19.     }
  20.     protected void Button1_Click(object sender, EventArgs e)  
  21.     {  
  22.         //Creating object of datatable  
  23.         DataTable tblcsv = new DataTable();  
  24.         //creating columns  
  25.         tblcsv.Columns.Add("Name");  
  26.         tblcsv.Columns.Add("City");  
  27.         tblcsv.Columns.Add("Address");  
  28.         tblcsv.Columns.Add("Designation");  
  29.          //getting full file path of Uploaded file  
  30.         string CSVFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);  
  31.         //Reading All text  
  32.         string ReadCSV = File.ReadAllText(CSVFilePath);  
  33.         //spliting row after new line  
  34.         foreach (string csvRow in ReadCSV.Split('\n'))  
  35.         {  
  36.             if (!string.IsNullOrEmpty(csvRow))  
  37.             {  
  38.                 //Adding each row into datatable  
  39.                 tblcsv.Rows.Add();  
  40.                 int count = 0;  
  41.                 foreach (string FileRec in csvRow.Split(','))  
  42.                 {  
  43.                     tblcsv.Rows[tblcsv.Rows.Count - 1][count] = FileRec;  
  44.                     count++;  
  45.                 }  
  46.             }       
  47.         }  
  48.         //Calling insert Functions  
  49.         InsertCSVRecords(tblcsv);  
  50.     }  
  51.     //Function to Insert Records  
  52.     private void InsertCSVRecords(DataTable csvdt)  
  53.     {  
  54.   
  55.         connection();  
  56.         //creating object of SqlBulkCopy    
  57.         SqlBulkCopy objbulk = new SqlBulkCopy(con);  
  58.         //assigning Destination table name    
  59.         objbulk.DestinationTableName = "Employee";  
  60.         //Mapping Table column    
  61.         objbulk.ColumnMappings.Add("Name", "Name");  
  62.         objbulk.ColumnMappings.Add("City", "City");  
  63.         objbulk.ColumnMappings.Add("Address", "Address");  
  64.         objbulk.ColumnMappings.Add("Designation", "Designation");  
  65.         //inserting Datatable Records to DataBase    
  66.         con.Open();  
  67.         objbulk.WriteToServer(csvdt);  
  68.         con.Close();
  69.     }  
  70. }
Now run the application and upload the file as follows
 
 
Now click on the Upload button and see the records in the database table as
 
 
Now you have seen how the records are inserted into the database with minimal code and effort.
 
Notes
  • For detailed code please download the sample Zip file.
  • Do a proper validation such as date input values when implementing.
  • Make the changes in the web.config file depending on your server details for the connection string.
Summary

From all the preceding examples you have learned how to insert CSV File records into the database. I hope this article is useful for all readers, if you have a suggestion then please contact me.


Similar Articles