DataAdapter and MS Access

[Guest]

Hi, All
Please, help
I have .NET 1.1 and MS Access, i am reading text file into MS Access table,
1. I did it with following statments;
OleDbConnection conn = null;
conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data
Source=C:\\Development\\WhitePlainsDB\\Data\\WhitePlains.mdb;");
conn.Open();

OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.Parameters.Add("@Code", OleDbType.VarChar, 10).Value = @sVill;
cmd.Parameters.Add("@TickNo", OleDbType.VarChar, 20).Value = @sTick;
Where sql is "INSERT INTO..." string
I have up to 2 million records and it takes 3 hours to accomplish this task.

I want to try with DataSet and DataAdapter,
DataSet ds = new DataSet();
DataTable tb = new DataTable("CustTable");
tb.Columns.Add("Code", typeof(String));
tb.Columns.Add("TickNo", typeof(String));
ds.Tables.Add("CustTable");
DataRow rw;
// read file in the loop
i = tb.Rows.Count + 1;
rw = tb.NewRow();
rw["Code"] = line.Substring(0, 1);
rw["TickNo"] = line.Substring(1, 10);
// populate 39 fields...
tb.Rows.Add(rw);
// End of file

OleDbDataAdapter da = new OleDbDataAdapter();
How to insert DataSet to MS Access table?
Or any other ideas, any links to this topic or code snipet
(I have to use Access db ) or should i update .NET 2.0 will be better
solution?
Please, advise if it possible at all.

 

[Cor Ligthert [MVP]]

Elena,

SQLExpress with Net 2.0 and SQL bulkcopy will probably do a better job for
you. The dataset is no alternative, it will only slow your proces down and
in your present situations is in my idea the Insert the best..

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Cor