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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

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

  1. #1
    Join Date
    Feb 2006
    Posts
    18

    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!

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    Quote Originally Posted by boxwalah
    (1) Is it a good idea to not open/close one's connections explicitly?
    Since you're using a Connection object then you should probably explicitly open it. It's easier to identify connection issues.

    Quote Originally Posted by boxwalah
    (2) Is it a good idea to have not utilized the ExecuteNonQuery() method?
    As opposed to what?

    Quote Originally Posted by boxwalah
    (3) Is there anything that's redundant above? Perhaps the Clear() and Dispose() commands.
    You don't need the Dispose commands. Using Close would be much clearer.

    Quote Originally Posted by boxwalah
    (4) Would I be paying a heavy price in resources/speed if I were to use Transactions?
    I wouldn't use them unless there is some reason for maintaining database consistency.

    Quote Originally Posted by boxwalah
    (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
    For an INSERT you can probably import directly using SQL. I'm not really clear on what sort of updates you have.

    Quote Originally Posted by boxwalah
    (6) Is OleDb passe? Should I go SqlClient?
    Not for a Jet database. You should be using OleDb and the Jet OLEDB Provider.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Feb 2006
    Posts
    18
    Thanks Paul, for your response.

    About the INSERT/UPDATE problem, 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*). 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 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 I somehow link the database to the textfile?

    Thanks again for your useful reponse earlier.

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    If your text file is comprised of both new and updated information there probably wouldn't be an easy way to perform an import via SQL. At least not unless there is a flag of some type that identifies each row as new or updated. The alternative is to process each row individually.

    You can link a text file to an Access database (as a Table object) if that makes it easier for you to query. You can also use ADO directly. Below is an example:

    Code:
    Dim rs As New ADODB.Recordset
    Dim cnn As New ADODB.Connection
    Dim strSQL As String
    
    cnn.Open _
       "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\My Documents\db1.mdb;" & _
       "Jet OLEDB:Engine Type=4"
    
    strSQL = "SELECT * FROM [Text;DATABASE=E:\My Documents\TextFiles;].[Table1.txt]"
    rs.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
    You also need a simple schema.ini file for tab delimited files:

    http://msdn.microsoft.com/library/de...a_ini_file.asp
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Feb 2006
    Posts
    18
    Thanks again, Paul. It is all very helpful.

Similar Threads

  1. Get all database form the server
    By John in forum Database
    Replies: 0
    Last Post: 07-03-2003, 02:03 AM
  2. Replies: 2
    Last Post: 09-12-2002, 07:12 PM
  3. Replies: 0
    Last Post: 10-14-2001, 12:29 AM
  4. Replies: 1
    Last Post: 01-24-2001, 07:59 AM
  5. Update Database using Data Form Wizard
    By Martin in forum VB Classic
    Replies: 0
    Last Post: 11-10-2000, 11:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center