Excel to Sql Server using Oledb connection

Hi,

If we want upload the excel file date to sql server database we have to use the following code.

1. Add the "System.Data.OleDb" namespace

eg : using System.Data.OleDb;

2. Use the following connection string for Oledb Connection. It will support both 2003 and 2007 excel formats. I am adding this, in web.config.



3. wrie query for read data from excel
string strQuery = "SELECT FirstName,LastName,EmailID,Organisation,MotherTongue,PreferredLanguage,PhoneNumber,Role FROM [Sheet1$]";

4. following is the code for insert the data from excel using Oledb connection
public void UploadExcelFile(string query, string excelConnectionString, int intBulkUploadID)
{
using(var oleDbConnection = new OleDbConnection(excelConnectionString))
using (var destinationConnection = new SqlConnection(sConn))
using (var bulkCopy = new SqlBulkCopy(destinationConnection))
{

bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
bulkCopy.ColumnMappings.Add("LastName", "LastName");
bulkCopy.ColumnMappings.Add("EmailID", "EmailID");
bulkCopy.ColumnMappings.Add("Organisation", "Organisation");
bulkCopy.ColumnMappings.Add("MotherTongue", "MotherTongue");
bulkCopy.ColumnMappings.Add("PreferredLanguage", "PreferredLanguage");
bulkCopy.ColumnMappings.Add("PhoneNumber", "PhoneNumber");
bulkCopy.ColumnMappings.Add("Role", "Role");
bulkCopy.ColumnMappings.Add("BulkUploadID", "BulkUploadID");
bulkCopy.DestinationTableName = "dbo.Bulk_EntryUserMaster";

using (var myCommand = new OleDbCommand(query, oleDbConnection))
{
oleDbConnection.Open();
destinationConnection.Open();
int count = bulkCopy.NotifyAfter;

OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = new OleDbCommand(query, oleDbConnection);
DataTable dt = new DataTable("Bulk_EntryUserMaster");

dt.Columns.Add("BulkUploadID", Type.GetType("System.Int32")).DefaultValue = intBulkUploadID;

oleAdapter.FillSchema(dt, SchemaType.Source);
oleAdapter.Fill(dt);

// Removing the empty rows
dt.DefaultView.RowFilter = "FirstName Is Not Null or LastName Is Not Null or EmailID Is Not Null or MotherTongue Is Not Null or PreferredLanguage Is Not Null or Organisation Is Not Null or PhoneNumber Is Not Null or Role Is Not Null";
DataTable dtNew = dt.DefaultView.ToTable();

//IEnumerable results = dtNew.AsEnumerable().Distinct();

//DataTable results = dtNew.AsEnumerable().Distinct().CopyToDataTable();

bulkCopy.WriteToServer(dtNew);

}
}
}

If you want validate excel columns see this.

private string ValidateExcelColumns(string excelConnectionString)
{
using (var oleDbConnection = new OleDbConnection(excelConnectionString))
{
oleDbConnection.Open();
DataTable dt = new DataTable();
String[] restrection = { null, null, "Sheet1$", null };

dt = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrection);

if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;

DataRow[] rows = dt.Select("", "ORDINAL_POSITION ASC");

foreach (DataRow row in rows)
{
excelSheets[i] = row["COLUMN_NAME"].ToString();
i++;
}

String[] databaseColumns = { "FirstName", "LastName", "EmailID", "Organisation","Role", "MotherTongue", "PreferredLanguage", "PhoneNumber" };

for (int j = 0; j < excelSheets.Length; j++)
{
if (excelSheets[j].ToString().Trim() != databaseColumns[j].ToString().Trim())
{
return "Columns Names not Proper";
}

}
}
return string.Empty;


}