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

C#
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.