ADO.NET: Delete from Database and perfomance issues

Question

Hello, I.m a c# and ado.net beginner and Ihave some questions. I have simple database application, using access db. It only need to read all data from table upon app load, insert rows and sometimes delete all rows from this table. So here is code to enable this three purposes:

string strConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\db\RTSdb.mdb", Application.StartupPath);

string strViewSessionContents = "SELECT * FROM OccuredNumbers";

OleDbDataAdapter dAdapter;

DataSet ds;

myconnection = new OleDbConnection(strConnectionString);

myconnection.Open();

dAdapter = new OleDbDataAdapter(strViewSessionContents, strConnectionString);

dAdapter.InsertCommand = myconnection.CreateCommand();

dAdapter.InsertCommand.CommandText = "INSERT INTO OccuredNumbers (NumberOrder, NumberValue) VALUES (?,?)";

dAdapter.InsertCommand.Parameters.Add("NumberOrder", OleDbType.Integer, 0, "NumberOrder");

dAdapter.InsertCommand.Parameters.Add("NumberValue", OleDbType.Integer, 0, "NumberValue");

dAdapter.DeleteCommand = myconnection.CreateCommand();

dAdapter.DeleteCommand.CommandText = "DELETE FROM OccuredNumbers WHERE NumberOrder = ?";

dAdapter.DeleteCommand.Parameters.Add("NumberOrder", OleDbType.Integer, 0, "NumberOrder");

ds = new DataSet();

dAdapter.Fill(ds, "OccuredNumbers");

 

I am interested in delete all rows functionality. So, concerning used deletecommand, I need to delete all rows one by one:

foreach (DataRow dr in ds.Tables["OccuredNumbers"].Rows)

{

dr.Delete();

}

dAdapter.Update(ds, "OccuredNumbers");

ds.Tables["OccuredNumbers"].AcceptChanges();

 

BUT, having some DB experience, I'm shure that it is not the best way from the perfomance point of view. It is better to delete all rows in bulk. Is it possible to write code that will be equal to "DELETE FROM OccuredNumbers" SQL statement? I've tried to implement this SQL statement in DeleteCommand, but I don't know how to make it work. So this is the FIRST QUESTION.

SECOND QUESTION - is it possible to use several sql commands for one kind of operation, i.e as I said above Delete one and Delete all rows?

THIRD QUESTION. While I'm having no idea how to solve my first question, I've tried to go from other side - update my database by ad-hoc SQL statement that wil ldelete all rows in bulk and than refresh my dataset:

The code might be not perfect (as I am beginner), but nevertheless:

//first, execute sql query:

OleDbCommand cmd = new OleDbCommand("DELETE FROM OccuredNumbers", myconnection);

cmd.ExecuteNonQuery();

//then i'm trying to refresh my dataset

ds.Clear();

dAdapter = new OleDbDataAdapter(strViewSessionContents, strConnectionString);

dAdapter.Fill(ds, "OccuredNumbers");

The code seems to be working BUT, there is some BUG: If I'm runnig this code without interruption the dataadaper don't see that rows were deleted, i.e. I suppose that there is some LAG in delete operation, when database engine do this transaction too slow (relatively slow) and following SELECT statement see the rows that are being deleted, because commit operation is not performed. I say so because if i put breakepoint after cmd.ExecuteNonQuery(); and wait a while, the following select query will return nothing as it must do!!!

Could someone comment this situation?

And the last question: Is the only way to re-read changed table in database (that was changed bypass dataadapter, as for instance in my example, by ad-hoc SQL command) is to re-create dataadaper object:

dAdapter = new OleDbDataAdapter(strViewSessionContents, strConnectionString);

Could it be just refreshed? Is it possible re-run SELECT sql statement?

THANKS ALOT IN ADVANCE!