connectseekers.com

Thursday, 2 March 2017

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

3/02/2017 - By Pranav Singh 0

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.




About the Author

We are the group of people who are expertise in different Microsoft technology like Asp.Net,MVC,C#.Net,VB.Net,Windows Application,WPF,jQuery,Javascript,HTML. This blog is designed to share the knowledge.

Get Updates

Subscribe to our e-mail newsletter to receive updates.

Share This Post

0 comments:

Please let me know your view

Free Ebooks


About Us

We are the group of people who are expertise in different Microsoft technology like Asp.Net,MVC,C#.Net,VB.Net,Windows Application,WPF,jQuery,Javascript,HTML. This blog is designed to share the knowledge.

Contact Us

For writing article in this website please send request by your

GMAIL ID: dotnetpools@gmail.com
Email Id : contact@aspdotnet-pools.com

Bugs and Suggestions

As we all know that this website is for sharing knowledge and providing proper solution. So while reading the article is you find any bug or if you have any suggestion please mail us at contact@aspdotnet-pools.com.

Partners


Top jQuery Plugins

Global Classified : Connectseekers.com
© 2014 aspdotnet-pools.com Designed by Bloggertheme9.
back to top