Database Commands From C# Form. It Works But ...
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!