Database Query – Best Way to Query Data and Modify It

cdatabasenetsqlvb.net

I'm working on a software using VB.Net which retrieves string packets through a TCP socket. The problem is it receives hundreds of packets per second. For each incoming packet the software should connect to the database (right now it's Access DB) and do one of the following:

  • Query one row of the database and use some item's of it (no problem with this).
  • Query one row, read some items, and then modify items in the same row.
  • Query one row, read some items, and then modify items in another row.

Now obviously this need to be done as fast as possible, so I'm looking for a good way (using less resources as well) to handle this.

The only way I know to query & modify data in the same connection is to use disconnected mode (method #3) but I'm thinking that if I use disconnected mode to handle updating the data, that might cause some conflicts.

Here's what I thought of anyway:

Method #1 (Mix between connected & disconnected mode):

Create a function that uses DataAdapter and returns a variable of type DataRow contains the data of the found row. And then modify the row directly in the DB by using UPDATE.. SET statement and ExecuteNonQuery method.

Method #2 (All connected):

Create a function that uses DataReader and returns required items from the found row. And then modify the row directly in the DB by using UPDATE.. SET statement and ExecuteNonQuery method.

Method #3 (All disconnected):

Query and fill the found row into a DataTable using DataAdapter, and then update & save the row using CommandBuilder and DataAdapter.Update

So, I'm looking the best approach to handle this. Please offer other approaches if you think it's better than the 3 methods I mentioned.

Note: I'm using an Access database right now for comparability issues, and I'm planning to use MySQL in the future.

Best Answer

If you going to query and update then I would go with Reader. It is a little more efficient and it sounds like you do not need any DataTable features.

On Reader use ordinal (not column name) for a little faster reference.

Connections are pooled so open and close a connection is fast. 

MySqlConnection con = new MySqlConnection(.... 
try 
{
     con.Open();
     using(MySqlCommand cmd = con.CreateCommand())
     {
         Int    data1;
         String data2;
         cmd.CommandText = .....;  //or better parameterized query
         using(MySqlReader rdr = cmd.ExecuteReader())
         {
            data2 = rdr.GetInt(0); 
            data2 = rdr.GetString(1);
         }
         // rdr should be closed so you can reuse the cmd  
         // do any cacls you need 
         cmd.CommandText = "update table ....." + data;  //or better parameterized query
         cmd.ExecuteNonQuery();
     }
}
catch(SQLexception Ex) 
{
}
finally
{
    if(con.IsOpen)   // not the actual syntax - look it up
       con.Close();
}

If you need to worry about data changed between the read and the write then you could need a transaction

And you should use parameterized query / update unless it is data under your control that can clean of injections

Also StoredProduces could be a little faster. But if you are accessing a row by a PK direct to the table is going to be pretty fast.

If the query is not by the PK then retrieve the PK and use that in the update

Related Topic