C# .NET - How to upload excel sheet in sql database using c#

Asked By vish on 16-Jul-12 01:45 AM

Choose File  


When i choose the excel file in the file upload control the sheets come into the listbox.
The problem is when i choose the particular sheet from the listbox and then click on the upload button the data is not going to database.


protected


void btnChooseSheet_Click(object sender, EventArgs e)


{



// System.Data.DataTable dt = null;



try


{


string path1 = FileUpload1.PostedFile.FileName;



string oledbconnection1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";



//Label2.Text = path1.ToString();


abc = path1.ToString();

Label2.Text = abc.ToString();


objConn =

new OleDbConnection(oledbconnection1);


objConn.Open();



DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);



if (dt == null)


{

Response.Write(

"hello");


}


String[] sheetsname = new String[dt.Rows.Count];



int i = 0;



foreach (DataRow dr in dt.Rows)


{

sheetsname[i] = dr[

"TABLE_NAME"].ToString();



ListBox1.Items.Add(sheetsname[i].ToString());

i++;

}

}


catch (Exception ex)


{

Response.Write(ex.Message);

}

}

This code is inserts the sheets into listbox. Further i want to upload the particular sheet from the listbox into database.

So guys any suggestion.....pls help..

Thanks in Advance...

Jitendra Faye replied to vish on 16-Jul-12 09:47 AM
Try to implement this code-

Using sqlBulk Class you can export excel file to DataBase.

Use this code-


protected void btnSend_Click(object sender, EventArgs e)
{
String strConnection = "Data Source=MySystem;Initial Catalog=MySamplesDB;Integrated Security=True";

//file upload path
string path = fileuploadExcel.PostedFile.FileName;

//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}

Try this code and let me know.