ASP.NET

Inserting Excel File Records Into SQL Server Database Using ASP.Net C#

 Background

I have often read the common question in forum posts, how to upload Excel 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 Excel 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 same Excel 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 "InsertExcelFileIntoDataBase" 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. <html xmlns="http://www.w3.org/1999/xhtml">  
  5. <head id="Head1" runat="server">  
  6.     <title>Article by Vithal Wadje</title>  
  7. </head>  
  8. <body bgcolor="blue">  
  9.     <form id="form1" runat="server">  
  10.     <div style="color: White;">  
  11.         <h4>  
  12.             Article for C#Corner  
  13.         </h4>  
  14.         <table>  
  15.             <tr>  
  16.                 <td>  
  17.                     Select File  
  18.                 </td>  
  19.                 <td>  
  20.                     <asp:FileUpload ID="FileUpload1" runat="server" />  
  21.                 </td>  
  22.                 <td>  
  23.                 </td>  
  24.                 <td>  
  25.                     <asp:Button ID="Button1" runat="server" Text="Upload" OnClick="Button1_Click" />  
  26.                 </td>  
  27.             </tr>  
  28.         </table>  
  29.     </div>  
  30.     </form>  
  31. </body>  
  32. </html> 
Now open the Default.aspx.cs page and write the following code to create an oledbconnection for the Excel file as in the following:
  1. private void ExcelConn(string FilePath)  
  2.     {  
  3.   
  4.         constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);  
  5.         Econ = new OleDbConnection(constr);  
  6.        
  7.     } 
Create a function for Sqlconnection as:
  1. private void connection()  
  2.     {  
  3.         sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString;  
  4.         con = new SqlConnection(sqlconn);  
  5.       
  6.     } 
Create a function to read and insert an Excel File into the database as:
  1. private void InsertExcelRecords(string FilePath)  
  2.    {  
  3.        ExcelConn(FilePath);  
  4.   
  5.        Query = string.Format("Select [Name],[City],[Address],[Designation] FROM [{0}]", "Sheet1$");  
  6.        OleDbCommand Ecom = new OleDbCommand(Query, Econ);  
  7.        Econ.Open();  
  8.   
  9.        DataSet ds=new DataSet();  
  10.        OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);  
  11.        Econ.Close();  
  12.        oda.Fill(ds);  
  13.       DataTable Exceldt = ds.Tables[0];  
  14.       connection();  
  15.       //creating object of SqlBulkCopy    
  16.       SqlBulkCopy objbulk = new SqlBulkCopy(con);  
  17.       //assigning Destination table name    
  18.       objbulk.DestinationTableName = "Employee";  
  19.       //Mapping Table column    
  20.       objbulk.ColumnMappings.Add("Name", "Name");  
  21.       objbulk.ColumnMappings.Add("City", "City");  
  22.       objbulk.ColumnMappings.Add("Address", "Address");  
  23.       objbulk.ColumnMappings.Add("Designation", "Designation");  
  24.       //inserting Datatable Records to DataBase    
  25.       con.Open();  
  26.       objbulk.WriteToServer(Exceldt);  
  27.       con.Close();  
  28.    } 
Now call the preceding function on the button click as:
  1. protected void Button1_Click(object sender, EventArgs e)  
  2.     {  
  3.         string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);  
  4.         InsertExcelRecords(CurrentFilePath);  
  5.     } 
The entire code of the default.aspx.cs page will look as follows:
  1. using System;  
  2. using System.Data;  
  3. using System.IO;  
  4. using System.Data.OleDb;  
  5. using System.Configuration;  
  6. using System.Data.SqlClient;  
  7.   
  8. public partial class _Default : System.Web.UI.Page  
  9. {  
  10.     OleDbConnection Econ;  
  11.     SqlConnection con;  
  12.   
  13.     string constr,Query,sqlconn;  
  14.     protected void Page_Load(object sender, EventArgs e)  
  15.     {  
  16.   
  17.   
  18.     }  
  19.   
  20.     private void ExcelConn(string FilePath)  
  21.     {  
  22.   
  23.         constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);  
  24.         Econ = new OleDbConnection(constr);  
  25.        
  26.     }  
  27.     private void connection()  
  28.     {  
  29.         sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString;  
  30.         con = new SqlConnection(sqlconn);  
  31.       
  32.     }  
  33.     
  34.   
  35.     private void InsertExcelRecords(string FilePath)  
  36.     {  
  37.         ExcelConn(FilePath);  
  38.   
  39.         Query = string.Format("Select [Name],[City],[Address],[Designation] FROM [{0}]", "Sheet1$");  
  40.         OleDbCommand Ecom = new OleDbCommand(Query, Econ);  
  41.         Econ.Open();  
  42.   
  43.             DataSet ds=new DataSet();  
  44.             OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);  
  45.             Econ.Close();  
  46.             oda.Fill(ds);  
  47.             DataTable Exceldt = ds.Tables[0];  
  48.        connection();  
  49.        //creating object of SqlBulkCopy    
  50.        SqlBulkCopy objbulk = new SqlBulkCopy(con);  
  51.        //assigning Destination table name    
  52.        objbulk.DestinationTableName = "Employee";  
  53.        //Mapping Table column    
  54.        objbulk.ColumnMappings.Add("Name", "Name");  
  55.        objbulk.ColumnMappings.Add("City", "City");  
  56.        objbulk.ColumnMappings.Add("Address", "Address");  
  57.        objbulk.ColumnMappings.Add("Designation", "Designation");  
  58.        //inserting Datatable Records to DataBase    
  59.        con.Open();  
  60.        objbulk.WriteToServer(Exceldt);  
  61.        con.Close();  
  62.  
  63.     }  
  64.     protected void Button1_Click(object sender, EventArgs e)  
  65.     {   
  66.         string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);  
  67.         InsertExcelRecords(CurrentFilePath);  
  68.     }  
  69. } 
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 Excel records into the database. I hope this article is useful for all readers, if you have a suggestion then please contact me.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs