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]
  13. GO
  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;
  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. }
  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