Upload excel file content to SQl server using SQL bulk copy method

This code shows how to upload excel file content to SQL Server


protected void btnImport_Click(object sender, EventArgs e)
{
lblError.Visible = false;

string xlsFile = fileImport.FileName; //fileImport is the file upload control

if (xlsFile == "" || xlsFile.EndsWith(".xls") != true)
{
lblError.Text = "Please select an Excel File";

lblError.Visible = true;

return;
}

string targetFileName = MapPath("~/_OrgData/Excel/" + xlsFile);

fileImport.PostedFile.SaveAs(targetFileName);

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + targetFileName + ";Extended Properties='Excel 4.0;HDR=YES;'";

OleDbConnection xlsConn = new OleDbConnection(excelConnectionString);

xlsConn.Open();

try
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$] WHERE Username <> NULL", xlsConn);

DbDataReader dr = command.ExecuteReader();

string sqlConnectionString = TAData.Common.GetConnectionString();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "tblPeopleTemp";

bulkCopy.ColumnMappings.Add("Username", "UserName");
bulkCopy.ColumnMappings.Add("Password", "Password");
bulkCopy.ColumnMappings.Add("Surname", "LName");
bulkCopy.ColumnMappings.Add("First Name", "FName");

bulkCopy.WriteToServer(dr);
}
}

catch (Exception ex)
{
lblError.Text = "The Excel content is not valid";

lblError.Visible = true;
}

xlsConn.Close();

File.Delete(targetFileName);
}


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: