Uploading XL Sheet data into SQL Server 2005 using C#

Uploading XL Sheet data into SQL Server 2005 using C#



Uploading XL Sheet data into SQL Server 2005 using C#:

This example will explain you how to upload the XL Sheet data into SQL Server 2005 using C#.

Add on upload control and button control to the ASP.NET 2.0 webform.

In button click event paste the following code:

a3.bmp

protected void btn_upload1_Click(object sender, EventArgs e)

    {

        try

        {

            string FilePath = System.IO.Path.GetFullPath(fileload1.PostedFile.FileName);

            string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";" + "Extended Properties=Excel 8.0;";

            using (OleDbConnection connection = new OleDbConnection(xConnStr))

            {

                OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);

                connection.Open();

                using (DbDataReader dr = command.ExecuteReader())

                {

                    string conStr = ConfigurationManager.AppSettings["conStr"];

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conStr))

                    {

                        bulkCopy.DestinationTableName = "Your_Table_Name";

                        bulkCopy.WriteToServer(dr);

                        lbl_Hardwaremsg.Visible = true;

                        lbl_Hardwaremsg.Text = "Hardware Details uploaded successfully";

                    }

                }

            }

      }


"Your_Table_Name" is your table name.

This is working in my application.