Import & Save Excel Sheet Data Into SQL Server Database Table In Asp.net

This article will show you how you can import excel sheet into dataset and save all the excel data into sql server database table. In this first I have read the read the data from excel and applied bulk save into database by using single connection.

So first we will create a new apsp.net application and add the below code into the page.

<div>             <asp:Button ID="Button1" runat="server" Text="Import Excel Into Databse" OnClick="Button1_Click" />             <br />             <asp:Label ID="lblMessage" runat="server" Style="color: #FF0000; font-weight: 700" Text=""></asp:Label>         </div>

Now we will create the table where we will save the data.

CREATE TABLE [dbo].[StudentDetail](        [Id] [int] IDENTITY(1,1) NOT NULL,        [Name] [varchar](50) NULL,        [Address] [varchar](50) NULL,        [Section] [varchar](50) NULL,        [Marks] [int] NULL ) ON [PRIMARY] GO

Here is the table structure.

After this we will create a new excel sheet and save into the application as shown below.

Please check the saved excel in application for this article.

Here is the excel data in dataset.

Here we have performed the operation on button click. Now check the code.

protected void Button1_Click(object sender, EventArgs e)         {             string filepath = @"\Excel\Studdent.xlsx";             DataSet ds = new DataSet();             ds = ReadExcel(filepath);             lblMessage.Text = SaveExcelSheet(ds);         }         ///         /// Read excel file         ///         ///         private DataSet ReadExcel(string filePath)         {             DataSet ds = new DataSet();             OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Server.MapPath(filePath) + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");             OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);             da.Fill(ds);             return ds;         }         ///         /// Save excel data into sql database         ///         ///         private string SaveExcelSheet(DataSet ds)         {             try             {                 SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=True");                 string query = "";                 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)                 {                     query = query + "Insert into StudentDetail (Name,Address,Section,Marks) values('" + ds.Tables[0].Rows[i]["Name"] + "','" + ds.Tables[0].Rows[i]["Address"] + "','" + ds.Tables[0].Rows[i]["Section"] + "'," + ds.Tables[0].Rows[i]["Marks"] + ");";                 }                 con.Open();                 SqlDataAdapter da = new SqlDataAdapter(query, con);                 da.SelectCommand.ExecuteNonQuery();                 con.Close();                 return "Data saved successfully";             }             catch             {                 return "Error while saving data.";             }         }

        

In above code I have created two function first will read the excel sheet.

private DataSet ReadExcel(string filePath)         {             DataSet ds = new DataSet();             OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Server.MapPath(filePath) + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");             OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);             da.Fill(ds);             return ds;         }

After this we will save the data into sql database.

private string SaveExcelSheet(DataSet ds)         {             try             {                 SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=True");                 string query = "";                 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)                 {                     query = query + "Insert into StudentDetail (Name,Address,Section,Marks) values('" + ds.Tables[0].Rows[i]["Name"] + "','" + ds.Tables[0].Rows[i]["Address"] + "','" + ds.Tables[0].Rows[i]["Section"] + "'," + ds.Tables[0].Rows[i]["Marks"] + ");";                 }                 con.Open();                 SqlDataAdapter da = new SqlDataAdapter(query, con);                 da.SelectCommand.ExecuteNonQuery();                 con.Close();                 return "Data saved successfully";             }             catch             {                 return "Error while saving data.";             }         }

In above code bulk insert operation I have performed. In this first I have prepare the multiple no of insert query and then save the record into the database in a single connection.

Now execute the code and check the output.

Now click on button and check the sql table.