C＃ACCES表用于参数的数据传输 [英] C# ACCES TABLE TO DATATABLE DATA TRANSFER WITH PARAMETERS
问题描述
Hello there，
以下代码是来自"acces"的数据传输。到"访问"。然后删除重复的行。
如何从"access"传输数据到"数据表"使用以下代码中的参数。
Hello there,
The following code is a data transfer from "acces" to "acces". Then duplicate lines are deleted.
How to transfer data from "acces" to "datatable" with the parameter as in the following code.
因为超过20-30行数据处理需要太长时间。
Because more than 20-30 lines of data processing takes too long.
我的代码;
public string tempyol = " C：\\test.mdb";
public string tempyol = "C:\\test.mdb";
void start（）
{
emptyperiodadd（）;
dublicatedrowclean（）;
}
$
void emptyperiodadd（）
{
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; for（int i = 0; i< dataGridView1.RowCount; i ++）
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; if（dataGridView1.Rows [i]！= null&& dataGridView1.Rows [i] .Cells [1] .Value！= null）
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; DateTime d = DateTime.Parse（dataGridView1.Rows [i] .Cells [1] .Value.ToString（））;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;的InsertRow（d.ToString（QUOT; DD.MM.YYYY"））;&NBSP; &NBSP; }¥b $ b &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; }¥b $ b &NBSP; &NBSP; &NBSP; }
&NBSP; &NBSP; &NBSP; void InsertRow（字符串单元格）
&NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; DateTime月;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; string ncell = cell.Replace（'。'，' - '）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; if（DateTime.TryParseExact（ncell，" dd-MM-yyyy"，null，DateTimeStyles.None，out month））
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; InsertMonth（month.AddMonths（-1））;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; InsertMonth（月）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; }¥b $ b &NBSP; &NBSP; &NBSP; }
&NBSP; &NBSP; &NBSP; void InsertMonth（DateTime month）
&NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; using（var conn = new OleDbConnection（" Provider =" +" Microsoft.Jet.OLEDB.4.0" +" ;; Data Source =" + tempyol））
; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;使用（var cmd1 = conn.CreateCommand（））
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; cmd1.Connection.Open（）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; cmd1.CommandText ="插入kdv（a）值（？）" ;;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; cmd1.Parameters.AddWithValue（"？"，month.ToString（" MM / yyyy"）。Replace（'。'，'/'））;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; cmd1.ExecuteNonQuery（）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; }
&NBSP; &NBSP; int kdvsil1，kdvsil2;
&NBSP; &NBSP; &NBSP; private void dublicatedrowclean（）
&NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; OleDbConnection con = new OleDbConnection（" Provider =" +" Microsoft.Jet.OLEDB.4.0" +" ;; Data Source =" + tempyol）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; DataSet ds = new DataSet（）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; OleDbDataAdapter da = new OleDbDataAdapter（" select * from kdv"，con）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; con.Open（）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; da.Fill（ds，" kdv"）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; for（int i = 0; i< ds.Tables [" kdv"]。Rows.Count; i ++）
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; DataRow row = ds.Tables [" kdv"]。行[i];
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; kdvsil2 ++;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; for（int j = kdvsil2; j< ds.Tables [" kdv"]。Rows.Count; j ++）
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; DataRow row2 = ds.Tables [" kdv"]。行[j];
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; if（row.ItemArray.GetValue（1）.ToString（）== row2.ItemArray.GetValue（1）.ToString（））
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; if（row.ItemArray.GetValue（3）.ToString（）== row2.ItemArray.GetValue（3）.ToString（））
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; kdvsil1 = int.Parse（row2.ItemArray.GetValue（0）.ToString（））;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; kdvsila（kdvsil1）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; }¥b $ b &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; }¥b $ b &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; }¥b $ b &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; }¥b $ b &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; con.Close（）;
&NBSP; &NBSP; &NBSP; }
&NBSP; &NBSP; &NBSP; private void kdvsila（int num）
&NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; OleDbConnection con = new OleDbConnection（" Provider =" +" Microsoft.Jet.OLEDB.4.0" +" ;; Data Source =" + tempyol）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; con.Open（）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; OleDbCommand c = new OleDbCommand（"从kdv删除，其中ID =？"，con）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; c.Parameters.AddWithValue（" ID"，num）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; c.ExecuteNonQuery（）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; con.Close（）;
&NBSP; &NBSP; &NBSP; }
void start()
{
emptyperiodadd();
dublicatedrowclean();
}
void emptyperiodadd()
{
for (int i = 0; i < dataGridView1.RowCount; i++)
{
if (dataGridView1.Rows[i] != null && dataGridView1.Rows[i].Cells[1].Value != null)
{
DateTime d = DateTime.Parse(dataGridView1.Rows[i].Cells[1].Value.ToString());
InsertRow(d.ToString("dd.MM.yyyy")); }
}
}
void InsertRow(string cell)
{
DateTime month;
string ncell = cell.Replace('.', '-');
if (DateTime.TryParseExact(ncell, "dd-MM-yyyy", null, DateTimeStyles.None, out month))
{
InsertMonth(month.AddMonths(-1));
InsertMonth(month);
}
}
void InsertMonth(DateTime month)
{
using (var conn = new OleDbConnection("Provider=" + "Microsoft.Jet.OLEDB.4.0" + "; Data Source=" + tempyol))
using (var cmd1 = conn.CreateCommand())
{
cmd1.Connection.Open();
cmd1.CommandText = "insert into kdv (a) values (?)";
cmd1.Parameters.AddWithValue("?", month.ToString("MM/yyyy").Replace('.', '/'));
cmd1.ExecuteNonQuery();
}
int kdvsil1, kdvsil2;
private void dublicatedrowclean()
{
OleDbConnection con = new OleDbConnection("Provider=" + "Microsoft.Jet.OLEDB.4.0" + "; Data Source=" + tempyol);
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter("select * from kdv", con);
con.Open();
da.Fill(ds, "kdv");
for (int i = 0; i < ds.Tables["kdv"].Rows.Count; i++)
{
DataRow row = ds.Tables["kdv"].Rows[i];
kdvsil2++;
for (int j = kdvsil2; j < ds.Tables["kdv"].Rows.Count; j++)
{
DataRow row2 = ds.Tables["kdv"].Rows[j];
if (row.ItemArray.GetValue(1).ToString() == row2.ItemArray.GetValue(1).ToString())
{
if (row.ItemArray.GetValue(3).ToString() == row2.ItemArray.GetValue(3).ToString())
{
kdvsil1 = int.Parse(row2.ItemArray.GetValue(0).ToString());
kdvsila(kdvsil1);
}
}
}
}
con.Close();
}
private void kdvsila(int num)
{
OleDbConnection con = new OleDbConnection("Provider=" + "Microsoft.Jet.OLEDB.4.0" + "; Data Source=" + tempyol);
con.Open();
OleDbCommand c = new OleDbCommand("Delete from kdv where ID =?", con);
c.Parameters.AddWithValue("ID", num);
c.ExecuteNonQuery();
con.Close();
}
推荐答案
你好，
Hello,
你必须弄清楚为什么它很慢。
You have to figure out why it is slow.
您的要求没有说明在屏幕上显示数据。但是出于某种原因，您使用DataGridView并将所有将数据转换为适用格式的费用显示在数据网格中并返回以进行计算。
Your requirement didn't say anything about showing data on the screen. However for some reason you use DataGridView and have all expenses on converting data to format applicable to be shown in the data grid and back for calculation you need.
使用数据库时，您必须执行尽可能多的操作。你可以"一气呵成"。在您的情况下 - 您可以创建ID列表并使用一个语句删除整个列表。阅读关于IN声明的部分内容。
Using database you must do as many as you can in "one go". In your case - you can create a list of IDs and remove whole list with one statement. Read about IN part of statement.
您在同一个数据库上执行的操作比两个不同的（在您的定义中描述的）数据库中的相同操作执行得更快。对于许多类型的操作，无需在客户端系统或不同数据库
上传输数据 - 这将显着加快执行速度。在您的情况下 - 您应该构建/查找SQL语句，该语句将删除服务器上的所有必需语句，而不将其加载到应用程序中。
Operation you make on the same database are executed faster then the same operation on two different (as this described in your definition) databases. For many types of operations no needs to transfer data on the client system or on different database - this will accelerate execution significantly. In your case - you should build/find an SQL statement which will remove all required statement on the server without loading them into app.
这篇关于C＃ACCES表用于参数的数据传输的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
