CODEWITHSUNDEEP
c#sqlms-accessdatatable
Jeff Brady
Jeff Brady

Reputation: 1498

fast/efficient way to run a query in Access based on datatable rows?

I have a datatable that may have 1000 or so rows in it. I need to go thru the datatable row by row, get the value of a column, run a query (Access 2007 DB) and update the datatable with the result. Here's what I have so far, which works:

String FilePath = "c:\\MyDB.accdb";

string QueryString = "SELECT MDDB.NDC, MDDB.NDC_DESC "
    + "FROM MDDB_MASTER AS MDDB WHERE MDDB.NDC = @NDC";

OleDbConnection strAccessConn = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath));
strAccessConn.Open();
OleDbDataReader reader = null;

int rowcount = InputTable.Rows.Count; //InputTable is the datatable
int count = 0;

while (count < rowcount)
{
    string NDC = InputTable.Rows[count]["NDC"].ToString(); 
    //NDC is a column in InputTable                      

    OleDbCommand cmd = new OleDbCommand(QueryString, strAccessConn);
    cmd.Parameters.Add("@NDC", OleDbType.VarChar).Value = NDC;

    reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        //update the NDCDESC column with the query result
        //the query should only return 1 line
        dataSet1.Tables["InputTable"].Rows[count]["NDCDESC"] = reader.GetValue(1).ToString();
    }

    dataGridView1.Refresh();
    count++;
}
strAccessConn.Close();

However this seems very inefficient since the query needs to run one time for each row in the datatable. Is there a better way?

Upvotes: 2

Views: 1236

Answers (3)

gjvdkamp
gjvdkamp

Reputation: 10516

I missed the part about InputTable coming from Excel.

For better speed, instead of executing the query in Access over and over, you can get all rows from MDDB_MASTER into a datatable in one select statement: 

SELECT MDDB.NDC, MDDB.NDC_DESC FROM MDDB_MASTER

And then use the DataTable.Select method to filter the right row. 

mddb_master.Select("NDC = '" + NDC +'")

This will be done in memory and should be much faster than all the round trips you have now. Especially over the network these round trips are expensive. 225k rows should be only a few MB (roughly a JPEG image) so that shouldn't be an issue.

Upvotes: 1

Tombala
Tombala

Reputation: 1690

You could use the "IN" clause to build a bigger query such as:

string QueryString = "SELECT MDDB.NDC, MDDB.NDC_DESC "
    + "FROM MDDB_MASTER AS MDDB WHERE MDDB.NDC IN (";

int rowcount = InputTable.Rows.Count; //InputTable is the datatable
int count = 0;

while (count < rowcount)
{
    string NDC = InputTable.Rows[count]["NDC"].ToString(); 
    QueryString += (count == 0 ? "" : ",") + "'" + NDC + "'";
}
QueryString += ")";

You can optimize that with StringBuilders since that could be a lot of strings but that's a job for you. :)

Then in a single query, you would get all the NDC descriptions you need and avoid performing 1000 queries. You would then roll through the reader, find values in the InputTable, and update them. Of course, in this case, you're looping through the InputTable multiple times but it might be a better option. Especially if yor InputTable could hold duplicate NDC values.

Also, note that you have a OleDbDataReader leak in your code. You keep reassigning the reader reference to a new instance of a reader before disposing of the old reader. Same with commands. You keep instantiating a new command but are not disposing of it properly.

Upvotes: 1

gjvdkamp
gjvdkamp

Reputation: 10516

You're thinking of an update query. You don't actually have to go over every row one by one. SQL is a set based language, so you only have to write a single statement that it should do for all rows.

Do this:

1) Create > Query Design

2) Close the dialog that selects tables

3) Make sure you're in sql mode (top left corner)

4) Paste this: 

UPDATE INPUTTABLE 
INNER JOIN MDDB_MASTER ON INPUTTABLE.NDC = MDDB_MASTER.NDC 
SET INPUTTABLE.NDCDESC = [MDDB_MASTER].[NDC_DESC];

5) Switch to design mode to see what happens. You may have to correct Input table, I couldn't find it's name. I'm assuming they;re both in the same database.

You'll see the query type is now an update query.

You can run this text through cmd.ExecuteNonQuery(sql) and the whole thing should run very quickly. If it doesn't you'll need an index on one of the tables;

THis works by joining the two table on NDC and then copying the NDC_DESC over from MDDB_MASTER to the inputtable.

Upvotes: 3

Related Questions