How can Reading Excel into Access Database Using ADO.Net & C#

This articles basically describes following things:-
1. Creating Access database from C# code
2. Reading Excel file into database
3. Drop table in Access database using code
4. Create table in Access database using code
5. Copy values from dataset to access table

To start with we will first create a new access database on the system.Here we are creating it in @"C:\AccessDB\NewMDB.mdb".
First of all in your project, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.

As a first step we will create an access database

DataSet ds = new DataSet();
System.Collections.ArrayList tableNames = new System.Collections.ArrayList();
try
{
//Create Access database Start
if (!System.IO.File.Exists(@"C:\AccessDB\NewMDB.mdb"))
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\AccessDB\\NewMDB.mdb;" +
"Jet OLEDB:Engine Type=5");
Console.WriteLine("Database Created Successfully");
cat = null;
}
//Create Access database End
}
catch (Exception ex)
{
Console.WriteLine("Database creation failed due to following reason.");
Console.WriteLine(ex.Message);
}

Now we will read the excel file into dataset.For this you just need to make a change in connection string of OledbProvider i.e. to include excel file name instead of database in datasource propoerty and then append Extended Properties=""Excel 8.0;HDR=YES;"".For this particular sample the excel file that I have has three columns viz ID,City and State, you can also do a Select * on excel file.In the select query you can give the name of the excel worksheet to retrieve data from it.

//Read Excel into dataset start
try
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbDataAdapter adapter = factory.CreateDataAdapter();
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = "SELECT ID,City,State FROM [Sheet1$]";//Here Sheet1 is worksheet name
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
selectCommand.Connection = connection;
adapter.SelectCommand = selectCommand;
adapter.Fill(ds, "Test");
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("Excel file can't be read.");
Console.WriteLine(ex.Message);
}
finally
{

}
//Read Excel into dataset End

The requirement is that I need to change data everytime so I am dropping the table here using code below.

//Check whether table existe in database if yes Drop it
try
{
if (ds.Tables.Count > 0)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
ADODB.Connection con1 = new ADODB.Connection();
try
{
object obj1 = new object();
con1.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\AccessDB\\NewMDB.mdb;", "", "", 0);
cat.ActiveConnection = con1;
for (int i = 0; i < cat.Tables.Count; i++)
{
tableNames.Add(cat.Tables[i].Name);
}
if (tableNames.Contains("Test"))
{
//Drop table
con1.Execute("Drop table Test", out obj1, 0);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con1.State != 0)
con1.Close();
cat = null;
con1 = null;
}

}
}
catch (Exception ex)
{
Console.WriteLine("Not able to drop table from database.");
Console.WriteLine(ex.Message);
}
//Check whether table existe in database if yes Drop it




Now that we have dropped the table we can create a new table in database by reading the information from the excel file.As a first baby step lets first create a table with schema only.We will fill the data later on using dataset.
We are iterating the columns from dataset to create a create table query which we will fire in the database to create the table.This is just one off way you get try many other ways also for this.

//Create Access database Table from excel Start
ADODB.Connection con = new ADODB.Connection();

try
{
object obj = new object();
con.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\AccessDB\\NewMDB.mdb;", "", "", 0);
string sql = "CREATE TABLE Test(";
int cnt = 0;
foreach (DataColumn dc in ds.Tables[0].Columns)
{
sql += dc.ColumnName;
if (dc.DataType == typeof(double))
{
sql += " NUMBER NULL";
}
else
{
sql += " STRING(120) NULL";
}
cnt++;
if (cnt != ds.Tables[0].Columns.Count)
{
sql += ",";
}
}

sql += ")";

con.Execute(sql, out obj, 0);

Console.WriteLine("A new table named Test has been created");

con.Close();
}
catch (Exception ex)
{
Console.WriteLine("Not able to create a new table in database.");
}
finally
{
if (con.State != 0)
{
con.Close();
}
}
//Create Access database Table from excel End

Now as a last part we are going to insert the data into the table that we have just created.For this particular sample I will create Insert queries for each row as there might be some rows in excel that contain invalid data.We need to ignore that.

//Insert into access database start
OleDbConnection oledbCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\AccessDB\\NewMDB.mdb;");
try
{
string insertSql = "INSERT INTO Test (";
string valueClause = "VALUES (";
int cnt = 0;
foreach (DataColumn dc in ds.Tables[0].Columns)
{

insertSql += dc.ColumnName;
valueClause += "@" + dc.ColumnName;

if (cnt != ds.Tables[0].Columns.Count - 1)
{
insertSql += ",";
valueClause += ",";
}
cnt++;

}
insertSql += ")" + valueClause + ")";

try
{
cnt = 0;
foreach (DataRow dr in ds.Tables[0].Rows)
{
OleDbCommand cmd = new OleDbCommand();//"Select * from Test",oledbCon);
cmd.Connection = oledbCon;
cmd.CommandText = insertSql;
foreach (DataColumn dc in ds.Tables[0].Columns)
{
string columnName = dc.ColumnName;
string paramName = "@" + dc.ColumnName;
string paramValue = Convert.ToString(dr[columnName]);
cmd.Parameters.Add(paramName, OleDbType.VarChar);
cmd.Parameters[paramName].Value = paramValue;
}
oledbCon.Open();
cmd.ExecuteNonQuery();
oledbCon.Close();
}
}
catch (Exception ex)
{
if (oledbCon.State == ConnectionState.Open)
oledbCon.Close();
MessageBox.Show(ex.Message);
}

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}