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.