Friday 23 December 2011

C# OLEDB CONNECTION STRING FOR EXCEL ( USE EXCEL AS DATABASE)


 System.Data.OleDb.OleDbConnection MyConnection;
                System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
                System.Data.OleDb.OleDbCommand create = new System.Data.OleDb.OleDbCommand();
                string sql = null, cretable = null; ;
                MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties=\"Excel 8.0;HDR=Yes;\"");
                //MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;");
                //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
                MyConnection.Open();
                myCommand.Connection = MyConnection;
                create.Connection = MyConnection;
                cretable = "create Table [Sheet1$] (InvoiceNo int,ProductId int,ProductName char(20),Quantity int,Price int,Amount int,TransactionNo Text)";
                create.CommandText = cretable;
                create.ExecuteNonQuery();

No comments:

Post a Comment