I have come across many mails in dotnetspider and other sites where in developers are stuck with this problem and there aren’t any good resources available in C# to overcome this problem and if present they are scattered.So basically I have tried to combine my knowledge along with the material I found on web to create this sample.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);
}
And here you go.
This is a complete sample and I hope you will find it useful. If yes please provide feedback. I will love to hear from you all.
Shakti Singh Tanwar
“There is no code better then “no code”.”
|
| Author: Mahesh Raj 07 Jun 2008 | Member Level: Gold Points : 1 |
This is very good information,Continue posting such useful articles.
|
| Author: John Fernandez 08 Jun 2008 | Member Level: Gold Points : 1 |
Very well written Article.Thanks for sharing this information.
|
| Author: Nanak Deep 18 Jun 2008 | Member Level: Bronze Points : 2 |
sir its great, but can we do multiple writing to excel
means
excel file is posted on web, now different users are accessing it and updating it..
so changes made by one will reflect in others...
is it also possible..
like many users accessing it at same time..m very eager to know this..
|
| Author: Gaurav Agrawal 18 Jun 2008 | Member Level: Silver Points : 2 |
Hi sakti,
Very good and detailed article.
It is very helpful for me.
I have used your code in my application and really it works fine.
Again thanks to share your knowledge with us.
Keep Posting and help your fellow developers.
Regards
Gaurav Agrawal
|
| Author: Leon 20 Aug 2008 | Member Level: Bronze Points : 1 |
Hi
Very good article!
One question, how can I read multiple worksheets in a excel file by this way?
Thanks
Leon
|