Database Commands From C# Form. It Works But ...

Question

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!

AB · Answer

Thanks again Binu and Subi for the useful tips!

Binu Subi · Answer

Easiest way is to insert one row at a time to database other method is to query the database first for the data and update insert the rows and call .Update method on the dataset. Here is some code sample i copied from VS Help public DataSet CreateCmdsAndUpdate(DataSet myDataSetstring myConnectionstring mySelectQuerystring myTableName) { OleDbConnection myConn = new OleDbConnection(myConnection); OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(); myDataAdapter.SelectCommand = new OleDbCommand(mySelectQuery myConn); OleDbCommandBuilder custCB = new OleDbCommandBuilder(myDataAdapter); myConn.Open(); DataSet custDS = new DataSet(); myDataAdapter.Fill(custDS); //code to modify data in dataset here //Without the OleDbCommandBuilder this line would fail myDataAdapter.Update(custDS); myConn.Close(); return custDS; } Refer to help on these topics you will get some ideas...And one thing to note In Ver 1.1 even if you do use this method it internally saves it one row at a time Cheers

AB · Answer

Thanks Subi and Binu for your response. You write: ... read the text data into a data structure of your choice (ex: dataset) then do the db operations. Okay so I have a tab-limited textfile (with no variable names) in the following format: '1' 'AA' 'CX' '1000' '1' 'M' 'Pontiac' i.e. in a table-format. The order of columns is the same as the order of columns in the table Customers in my Access Database. As you suggest I should read this file into a Dataset. Any pointers on how I can transfer from the Dataset to the table Customers in my Access Database? Is there a way to transfer besides doing an INSERT row by row? Can a dataset be linked (bound?) to an Access Table so that the Access table reflects the state of the textfile? I'll need to do such transfers several times as I get updated textfiles. (On the same note can a textfile be linked to an Access Table so that the Table reflects the state of the textfile.) Thanks!

Binu Subi · Answer

1) Is it a good idea to not open/close one's connections explicitly? try to use using statement when you create your connection using (OleDbConnection myConn = new OleDbConnection(myConnString)) {} (2) Is it a good idea to have not utilized the ExecuteNonQuery() method? Depends on what you want to do when getting data use a dataadapter to fill a dataset or get data into a datareader. ExecuteNonQuery is used to do action sql statments like insertdelete etc (3) Is there anything that's redundant above? Perhaps the Clear() and Dispose() commands. see (1) (4) Would I be paying a heavy price in resources/speed if I were to use Transactions? not really (5) My updated values (if at all updated values else old values) for the database are in a textfile. Again personal design choice if you are reading one line from text and inserting to db then read next line you should think about a different approach. read the text data into a data structure of your choice (ex: dataset) then do the db operations (6) Is OleDb passe? Should I go SqlClient? SqlClient works only with SqlServer Cheers!