Post
Ask Question

How To Import Excel Data In SQL Server Using ASP.NET

Introduction

In this blog, we are going to learn how to import Excel data into an SQL database table using ASP.NET and display the data in a GridView jQuery data table.

Step 1

Create database table in the SQL Server of your choice.

  1. CREATE TABLE [dbo].[Employee](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [nvarchar](50) NULL,  
  4.     [Position] [nvarchar](50) NULL,  
  5.     [Office] [nvarchar](50) NULL,  
  6.     [Salary] [nvarchar](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]  
  12.   
  13. GO  
  14.   
  15. CREATE procedure [dbo].[spGetAllEmployee]  
  16. as  
  17. begin  
  18. select ID,Name,Position,Office,Salary from Employee  
  19. end  
Step 2

Double click on webconfig file and add database connection.
  1. <connectionStrings>  
  2.   <add name="DBCS" connectionString="data source=FARHAN\SQLEXPRESS; database=simpleDB; integrated security=true;"/>  
  3. </connectionStrings>  

Step 3

Create an empty project in Visual Studio. Right-click the project and aa dd new item, choose web form, give it a  meaningful name, and click on Add.

Add script and styles in the head section of web form:

  1. <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">  
  2. <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">  
  3. <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>  
  4. <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>  
  5. <link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/dataTables.bootstrap4.min.css" />  
  6. <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js" type="text/javascript"></script>  
  7. <script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap4.min.js" type="text/javascript"></script>  

 Write script to apply jQuery data table with GridView:

  1. <script type="text/javascript">  
  2.         $(document).ready(function () {  
  3.             $("#GridView1").prepend($("<thead></thead>").append($(this).find("tr:first"))).dataTable();  
  4.         });  
  5. </script>  

Step 4

Design HTML web form by dragging and dropping the "File Upload" button and GridView control and some Bootstrap 4 classes.

  1. <body>  
  2.     <form id="form1" runat="server">  
  3.         <div class="container py-3">  
  4.             <h2 class="text-center text-uppercase">How to upload excel file in sql server database in asp.net</h2>  
  5.             <div class="card">  
  6.                 <div class="card-header bg-primary text-uppercase text-white">  
  7.                     <h5>Import Excel File</h5>  
  8.                 </div>  
  9.                 <div class="card-body">  
  10.                     <button style="margin-bottom:10px;" type="button" class="btn btn-primary" data-toggle="modal" data-target="#myModal">  
  11.                         <i class="fa fa-plus-circle"></i> Import Excel  
  12.                     </button>  
  13.                     <div class="modal fade" id="myModal">  
  14.                         <div class="modal-dialog">  
  15.                             <div class="modal-content">  
  16.                                 <div class="modal-header">  
  17.                                     <h4 class="modal-title">Import Excel File</h4>  
  18.                                     <button type="button" class="close" data-dismiss="modal">×</button>  
  19.                                 </div>  
  20.                                 <div class="modal-body">  
  21.                                     <div class="row">  
  22.                                         <div class="col-md-12">  
  23.                                             <div class="form-group">  
  24.                                                 <label>Choose excel file</label>  
  25.                                                 <div class="input-group">  
  26.                                                     <div class="custom-file">  
  27.                                                         <asp:FileUpload ID="FileUpload1" CssClass="custom-file-input" runat="server" />  
  28.                                                         <label class="custom-file-label"></label>  
  29.                                                     </div>  
  30.                                                     <label id="filename"></label>  
  31.                                                     <div class="input-group-append">  
  32.                                                         <asp:Button ID="btnUpload" runat="server" CssClass="btn btn-outline-primary" Text="Upload" OnClick="btnUpload_Click" />  
  33.                                                     </div>  
  34.                                                 </div>  
  35.                                                 <asp:Label ID="lblMessage" runat="server"></asp:Label>  
  36.                                             </div>  
  37.                                         </div>  
  38.                                     </div>  
  39.                                 </div>  
  40.                                 <div class="modal-footer">  
  41.                                     <button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>  
  42.                                 </div>  
  43.                             </div>  
  44.                         </div>  
  45.                     </div>  
  46.                     <asp:GridView ID="GridView1" HeaderStyle-CssClass="bg-primary text-white" ShowHeaderWhenEmpty="true" runat="server" AutoGenerateColumns="false" CssClass="table table-bordered``">  
  47.                         <EmptyDataTemplate>  
  48.                             <div class="text-center">No record found</div>  
  49.                         </EmptyDataTemplate>  
  50.                         <Columns>  
  51.                             <asp:BoundField HeaderText="ID" DataField="ID" />  
  52.                             <asp:BoundField HeaderText="Name" DataField="Name" />  
  53.                             <asp:BoundField HeaderText="Position" DataField="Position" />  
  54.                             <asp:BoundField HeaderText="Office" DataField="Office" />  
  55.                             <asp:BoundField HeaderText="Salary" DataField="Salary" />  
  56.                         </Columns>  
  57.                     </asp:GridView>  
  58.                 </div>  
  59.             </div>  
  60.         </div>  
  61.     </form>  
  62. </body>  

Step 5

Double click on Upload button and write the following C# code.

Add namespace

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
using System.Data.Common;

Complete C# code

  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.Configuration;  
  5. using System.Data.OleDb;  
  6. using System.Data.Common;  
  7.   
  8. namespace Upload_ExcelFile_Demo  
  9. {  
  10.     public partial class FileUpload : System.Web.UI.Page  
  11.     {  
  12.         protected void Page_Load(object sender, EventArgs e)  
  13.         {  
  14.             if (!IsPostBack)  
  15.             {  
  16.                 BindGridview();  
  17.             }  
  18.         }  
  19.   
  20.         private void BindGridview()  
  21.         {  
  22.             string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;  
  23.             using (SqlConnection con = new SqlConnection(CS))  
  24.             {  
  25.                 SqlCommand cmd = new SqlCommand("spGetAllEmployee", con);  
  26.                 cmd.CommandType = CommandType.StoredProcedure;  
  27.                 con.Open();  
  28.                 GridView1.DataSource = cmd.ExecuteReader();  
  29.                 GridView1.DataBind();  
  30.             }  
  31.         }  
  32.         protected void btnUpload_Click(object sender, EventArgs e)  
  33.         {  
  34.             if (FileUpload1.PostedFile!=null)  
  35.             {  
  36.                 try  
  37.                 {  
  38.                     string path = string.Concat(Server.MapPath("~/UploadFile/" + FileUpload1.FileName));  
  39.                     FileUpload1.SaveAs(path);  
  40.                     // Connection String to Excel Workbook  
  41.                     string excelCS = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);  
  42.                     using (OleDbConnection con = new OleDbConnection(excelCS))  
  43.                     {  
  44.                         OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con);  
  45.                         con.Open();  
  46.                         // Create DbDataReader to Data Worksheet  
  47.                         DbDataReader dr = cmd.ExecuteReader();  
  48.                         // SQL Server Connection String  
  49.                         string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;  
  50.                         // Bulk Copy to SQL Server   
  51.                         SqlBulkCopy bulkInsert = new SqlBulkCopy(CS);  
  52.                         bulkInsert.DestinationTableName = "Employee";  
  53.                         bulkInsert.WriteToServer(dr);  
  54.                         BindGridview();  
  55.                         lblMessage.Text = "Your file uploaded successfully";  
  56.                         lblMessage.ForeColor = System.Drawing.Color.Green;  
  57.                     }  
  58.                 }  
  59.                 catch (Exception)  
  60.                 {  
  61.                     lblMessage.Text = "Your file not uploaded";  
  62.                     lblMessage.ForeColor = System.Drawing.Color.Red;  
  63.                 }  
  64.             }  
  65.         }  
  66.     }  
  67. }  

Step 6

Run project using ctr+F5.

Here is the final output.

Screenshot 1

output

Screenshot 2


output

Screenshot 3

output
Next Recommended Reading Export Gridview Data to Excel File Inside Ajax Update Panel Using ASP.NET and C#