CODEDIGEST
Home Articles CodeDigest Tutorials FAQs
Skip Navigation LinksHome » Article » ASP.Net Article » Import/Upload Excel Sheet data to Sql Server in C# and Asp.Net   You are not logged in.
Search
 

Technologies
 

Sponsors
 

CodeDigest Navigation
 

Technology News
 

Community News
No News Feeds available at this time.
 
Import/Upload Excel Sheet data to Sql Server in C# and Asp.Net

By Bala Murugan
Posted On Mar 25, 2011
Article Rating: (Login)
Be first to rate
this article.
No of Comments: 7
Print this article.
Category: ASP.Net/ADO.Net

Subscribe to our feed!

Import/Upload Excel Sheet data to Sql Server in C# and Asp.Net

 

One of my previous article, Doing Bulk Upload/Insert of DataTable to a Table in SQL server in C# demonstrated how to upload a DataTable to a SQL server table using SqlBulkCopy class. At times, we may have requirement where we need to upload the data in a excel sheet to a SQL server table.

Moving forward, let’s understand how to perform bulk upload of the data in excel sheet to SQL server table in ASP.Net.

 

Steps

1.      Open your Visual Studio 2008 from Start Menu and create a new Asp.Net project. I have used C# as the language in this sample.

2.      To understand this article, we will use a Sql Express database created in the App_Data folder in our solution. Add a new database using "Add New Item.." dialog box. Then, create a table called Employees with the necessary columns using the “Server Explorer”.

      Refer the below figure,

How to import Excel sheet to Sql Server Using C#?

 

3.      Create an excel sheet with some sample Employee data to upload. Below is the excel sheet I have used in this example.

 

How to import Excel sheet to Sql Server Using C#?

You can copy this excel sheet into the App_Data folder in visual studio solution.

 

With the above things in place, we will move forward and import the excel sheet data to Employees table using SqlBulkUpload class. To do this, drag a button control into your Default.aspx and name it as btnImport. On its Button click event,

Import Excel sheet into SqlServer Using C#

protected void btnImport_Click(object sender, EventArgs e)

    {

        using (OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings["ExcelCon"].ConnectionString))

        {

            con.Open();

            OleDbCommand com = new OleDbCommand("Select * from [EmployeeInfo$]", con);

            OleDbDataReader dr = com.ExecuteReader();

            using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString))

            {

                sqlcon.Open();

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))

                {

                    bulkCopy.ColumnMappings.Add("[Employee Name]", "EmpName");

                    bulkCopy.ColumnMappings.Add("Department", "Department");

                    bulkCopy.ColumnMappings.Add("Address", "Address");

                    bulkCopy.ColumnMappings.Add("Age", "Age");

                    bulkCopy.ColumnMappings.Add("Sex", "Sex");

                    bulkCopy.DestinationTableName = "Employees";

                    bulkCopy.WriteToServer(dr);

                }

            }

            dr.Close();

            dr.Dispose();

        }

        Response.Write("Upload Successfull!");

    }

 

Connection String

<add name="ExcelCon" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|NewEmployees.xlsx;Extended Properties=Excel 12.0"/>

    <add name="Sql" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True;"/>



ASP.Net Hosting

Recent Articles

The above code will read the excel content using OleDbDataReader and will do a bulk upload to Employees table using SqlBulkCopy.WriteToServer() method. You need to include System.Data.OleDb and using System.Data.SqlClient namespace for the above code to work. We can also set batch size and upload timeout in case of large volume of data using SqlBulkCopy class. You can read my previous article to know more.

Doing Bulk Upload/Insert of DataTable to a Table in SQL server in C#

 

Downloads

Download Source 

Conclusion

Importing excel sheet data to Sql server is one of the most common task we will get in most of the projects. There are numerous ways to do this, like reading the excel sheet and inserting it to Sql table by constructing insert queries. But, this approach is not that efficient where an insert query is fired for every row. Thus, in this article we have understood using the SqlBulkCopy class for efficiently uploading the excel data which does the task at a shot. Check out the Excel dashboards at iDashboards.com if you're looking for new ways to further display or manipulate that data.

Download the source attached with this article and see it in action.

 

Similar Articles
  • You can contribute to CodeDigest.Com:
    Article Feedback
    Title  
    Submitted By  
    Comment  
    Enter the verification number
     
    Comments
    Thanks you but some one not working
    That working: ExcelConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\xlsx\\Test.xlsx ;Extended Properties=Excel 12.0";
    what is employeesinfo
    I can't understand what this line is doing.

    Select * from [EmployeeInfo$]
    Importing Excel
    This is really good..and i hope this help anyone to import Excel in Sql....
    nice
    nice
    Importing Excel
    This is great and works very well. Can you tell me how to connect a browse button to this code. I have been working on doing so, but is not working out. Any help would be appreciated...just like to browse for the excel file. Im not a developer, just got stuck on a new project.
    Importing Excel
    This is great and works very well. Can you tell me how to connect a browse button to this code. I have been working on doing so, but is not working out. Any help would be appreciated...just like to browse for the excel file. Im not a developer, just got stuck on a new project.
    Importing Excel
    looks easy enough. I may try it.