Hello. I am working on a simple C# application to deal with an Access database. This is what I am using for my INSERT/DELETE/UPDATE/SELECT operations:

==============
OleDbConnection myConn = new OleDbConnection(myConnString);
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();

string mySQLString = "SQL for INSERT/DELETE/UPDATE/SELECT";

try
{
  da.SelectCommand = new OleDbCommand(mySQLString, myConn);
  da.Fill(ds);

  // Use ds.Tables[...] if SELECT operation

  ds.Clear();
  ds.Dispose();
  da.Dispose();
}

catch (System.Exception x)
{
  MessageBox.Show(x.ToString());
}
==============

It works, but is it right? Not many, if at all any, of the coding examples I've come across seem to go this way. Here's what's bothering me ...

(1) Is it a good idea to not open/close one's connections explicitly?

(2) Is it a good idea to have not utilized the ExecuteNonQuery() method?

(3) Is there anything that's redundant above? Perhaps the Clear() and Dispose() commands.

(4) Would I be paying a heavy price in resources/speed if I were to use Transactions?

(5) My updated values (if at all updated values, else old values) for the database are in a textfile. At present I am taking them line by line for INSERT? Is there a faster way? The textfile could have new rows as well, so I:

try
{INSERT}

catch (System.Exception)
{
  try
  {
    DELETE;
    INSERT;
  }

  catch (System.Exception x)
  {MessageBox.Show(x.ToString());}
}


(6) Is OleDb passe? Should I go SqlClient?


Again, it's a simple (4-Table) Access Database.

I agree, that's a lots of questions. All and any comments/answers will be helpful. Anything I am missing, anything that could be catastrophic, anything I should look-up. Thanks in advance!

(1) the Fill() method encapsulates the opening and closing of the database. Explicitly Opening and Closing of the connection is NOT needed.

(2) the ExecuteNonQuery() method should probably be avoided; it doesn't facilitate n-tiered application development, and is often a security concern (SQL injection). Through the DataSet and ADO.NET we have a new model for data access.

(3) the Dispose() methods are unneeded and should generally NOT be called explicitly as it invokes garbage collection, thus creating additional overhead. Instead you should set your DataSet and DataAdapter to null. Calling the .Clear() on a DataSet is a good idea as it frees up unneeded resources, however the Garbage Collection would have cleaned up those resources when the DataSet went out of scope.

(4) Transactions coupled with stored procedures are good.
Will you pay a heavy price in resources/speed? That depends on what are you using now? I can't give a quantitative answer.

(5) Not too clear on the question, but Stored Procedures are very quick.

(6) SqlClient over OleDB, OleDB over ODBC. SqlClient has been optimized for SQL Server and ASP.NET, OleDB adds another layer on DB communication, and ODBC adds even more layers.

----
You should read up on ADO.NET, specifically DataSets, and DataAdpaters..
Through DataAdapters you can offer an alternative too: [string mySQLString = "SQL for INSERT/DELETE/UPDATE/SELECT";]

You may want to consider buying an ADO.NET reference book..

Links of interest:
http://msdn.microsoft.com/library/de...sfilltopic.asp
http://msdn.microsoft.com/library/de...html/psent.asp

- A.Kahtava

Thanks Adam, for your response. It is very helpful.

About (5) in my original posting, let me explain it in more detail: When I acquire data for my application, it is in the form of a tab-delimited textfile (with no row of variable names; but this can be changed). The columns are in the same order as the columns in the Access Table. For example:

[ID Name Income Age Gender Raise Car...]
'1' 'AB' '30000' '35' 'M' '5' 'Saturn'
'2' 'CD' '45000' '30' 'M' '7' 'Jetta'


At the very outset, I INSERT each row and populate the Access Database and can query the data. Now, 2 days later, I get a new textfile as follows:

'1' 'AB' '32000' '35' 'M' '5' 'Civic'
'2' 'CD' '45000' '30' 'M' '7' 'Jetta'
'3' 'EF' '35000' '25' 'F' '4' 'Pontiac'

Some values, if at all any, have changed (e.g. in the first Row). Additionally, there's a new Row. If I INSERT again, it won't work for Rows 1 and 2. But INSERT will work for Row 3. So what I am doing is trying an INSERT first, and if it throws any Exception, then I DELETE the Row and (re) INSERT the ("new") Row. Any updated values in the texfile (I don't know which ones) are, therefore, taken care of. Here's how it goes:

(1) Try INSERT for "AB." It won't work. Delete "AB" and then INSERT "AB."
(2) Now try INSERT for "CD." It won't work. Delete "CD" and then INSERT "CD."
(3) Now try INSERT for "EF." It works.

I expect a multitude of such new updated textfiles over time. I am just trying to automate the transfer of data to the database, so that I can query it. And this has left me wondering: Must I go line-by-line through the textfile and each time? Is the INSERT-(DELETE-INSERT) approach the only way to go here? Can a textfile be linked to an Access Database, so that the Access Tables reflect the state of the textfile?

I was advised on a different forum to not go line-by-line from the textfile, but load the textfile into a dataset then (presumably) go row-by-row from the dataset. Is row-by-row the only way? Can a dataset be linked (bound?) to an Access Database, so that the Access Tables reflect the state of the dataset?

Again, a lot of questions. These are what I am searching on and reading up on over the next few days. Any tips/pointers/leads/links will be greatly appreciated. Thanks!

Hi, box! I beleive going line by line and loading into a DataSet could help if you first have a Dataset with data from your access.mdb and then merge the new rows of your other dataset and persist changes against the DB. (Just an idea! not quite tried it yet!)

:-Q Marvin B-)