在Access中基于数据表行运行查询的快速/高效方式
本文关键字:方式 高效 查询 运行 Access 数据表 | 更新日期: 2023-09-27 18:21:06
我有一个数据表,其中可能有1000行左右。我需要逐行查看数据表,获取列的值,运行查询(Access 2007 DB)并用结果更新数据表。以下是我目前所拥有的,有效的:
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();
然而,这似乎效率很低,因为查询需要为数据表中的每一行运行一次。有更好的方法吗?
您想到的是一个更新查询。实际上,你不必一行一行地复习。SQL是一种基于集合的语言,所以您只需要编写一条语句,它应该为所有行执行该语句。
这样做:
1) 创建>查询设计
2) 关闭选择表格的对话框
3) 确保您处于sql模式(左上角)
4) 粘贴此:
UPDATE INPUTTABLE
INNER JOIN MDDB_MASTER ON INPUTTABLE.NDC = MDDB_MASTER.NDC
SET INPUTTABLE.NDCDESC = [MDDB_MASTER].[NDC_DESC];
5) 切换到设计模式,看看会发生什么。您可能需要更正输入表,我找不到它的名称。我假设他们;我们都在同一个数据库中。
您将看到查询类型现在是一个更新查询。
您可以通过cmd.ExecuteNonQuery(sql)
运行此文本,整个过程应该运行得非常快。如果没有,您将需要其中一个表上的索引;
这是通过连接NDC上的两个表,然后将NDC_DESC从MDDB_MASTER复制到输入表来实现的。
我错过了Excel中关于InputTable的部分。
为了获得更好的速度,您可以在一个select语句中将MDDB_MASTER中的所有行获取到数据表中,而不是在Access中反复执行查询:
SELECT MDDB.NDC, MDDB.NDC_DESC FROM MDDB_MASTER
然后使用DataTable.Select方法过滤右侧的行。
mddb_master.Select("NDC = '" + NDC +'")
这将在记忆中完成,并且应该比你现在的所有往返旅行快得多。尤其是在网络上,这些往返行程非常昂贵。225k行应该只有几个MB(大致是JPEG图像),所以这应该不是问题。
您可以使用"IN"子句来构建更大的查询,例如:
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 += ")";
您可以使用StringBuilders进行优化,因为这可能有很多字符串,但这是您的工作。:)
然后在一个查询中,您将获得所需的所有NDC描述,并避免执行1000个查询。然后,您将滚动浏览阅读器,在InputTable中查找值,并更新它们。当然,在这种情况下,您要多次循环InputTable,但这可能是一个更好的选项。特别是如果您的InputTable可能包含重复的NDC值。
此外,请注意,您的代码中存在OleDbDataReader泄漏。在处理旧读卡器之前,您一直在将读卡器引用重新分配给读卡器的新实例。命令也是如此。您一直在实例化一个新命令,但没有正确处理它。