每行上的datatable请求数据库
本文关键字：请求 数据库 datatable | 更新日期: 2023-09-27 18:00:30
我有一个连接类laki this：
public static OleDbConnection connection = new OleDbConnection();
public string sorgu;
public static string server;
public static string userId;
public static string catalog;
public static string password;
public DataSet ds = new DataSet();
public bool Open()
{
try
{
if (connection.State != ConnectionState.Open)
{
connection.ConnectionString = "Provider=SQLOLEDB.1" +
";Password=" + password +
";Persist Security Info=True" +
";User ID=" + userId +
";Initial Catalog=" + catalog +
";Data Source=" + server;
connection.Open();
return true;
}
else
{
return true;
}
}
catch (Exception ex)
{
MessageBox.Show("Sistem Mesajı:" + ex.Message, "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
public void CloseConnection()
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
public DataTable Dt(string query)
{
DataTable dt = new DataTable();
if (Open())
{
OleDbDataAdapter da = new OleDbDataAdapter(query, connection);
try
{
da.Fill(dt);
}
catch (Exception ex)
{
MessageBox.Show("Sistem Mesajı:" + ex.Message, "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//baglanti.Close();
return dt;
}
这是我的代码，我在窗口窗体中获取值
ConnectionClassOleDb cc = new ConnectionClassOleDb();
sorgu = " SELECT * FROM tblFiyatlandirma WHERE " +
" musteriNo=" + musteriNo +
" AND musteriTipId=" + musteriTipId;
nudSiparisMetresi.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["siparisMetresi"]);
nudKar.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["kar"]);
nudKumasEni.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["kumasEni"]);
nudMamulFire.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["mamulFire"]);
nudMamulKumasKari.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["mamulKumasKar"]);
nudNakliyeUcreti.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["nakliye"]);
nudKomisyon.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["komisyon"]);
nudTarakEni.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["tarakEni"]);
nudTarakNo.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["tarakNo"]);
nudTarakGecisi.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["tarakGecisi"]);
nudAtkiFiyat.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["atkiFiyati"]);
nudAtkiSikligi.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["atkiSikligi"]);
nudAnalizNo.Value = Convert.ToDecimal(cc.Dt(sorgu).Rows[0]["analizNo"]);
代码是有效的，但在每个cc.Dt（sorgu）.Rows[0]行cc-class将再次转到连接类并进行新的查询。怎么了？
因为每次调用"Dt"都会创建一个新的数据表和适配器，并用适配器填充数据表。这将导致在每次调用中执行查询。
您应该将返回的DataTable存储在一个本地变量中，并对其执行类似"Rows"的操作，而不是每次都调用DB类。
因此：
DataTable dt = cc.Dt(sorgu);
然后使用
dt.Rows[0]["siparisMetresi"]
而不是
cc.Dt(sorgu).Rows[0]["siparisMetresi"]
我发现DataAdapters非常不可预测，我敢打赌这就是你的问题。
相反，我会通过获取一个读取器并转换为一个表来从连接中封装您的DataTable。大约5年前，我在网上发现了这段代码，现在每次都在使用它（ConvertSqlDataReaderToDataTable）。下面的代码显示了如何调用它。您应该能够很容易地根据需要进行修改。但是，如果只通过一次，则不要创建DataTable，而只使用DataReader。
public static DataTable ConvertSqlDataReaderToDataTable(SqlDataReader reader)
{
ArrayList alColumns;
DataColumn dcColumn;
DataRow drRow;
DataTable dtTemp;
DataTable dtReturn;
Int32 i;
// create dataset to match the reader using reader's schema
alColumns = new ArrayList();
dtReturn = new DataTable();
dtTemp = reader.GetSchemaTable();
for (i = 0; i < dtTemp.Rows.Count; i++)
{
dcColumn = new DataColumn();
if (!dtReturn.Columns.Contains(dtTemp.Rows[i]["ColumnName"].ToString()))
{
dcColumn.ColumnName = dtTemp.Rows[i]["ColumnName"].ToString();
dcColumn.Unique = Convert.ToBoolean(dtTemp.Rows[i]["IsUnique"]);
dcColumn.AllowDBNull = Convert.ToBoolean(dtTemp.Rows[i]["AllowDBNull"]);
dcColumn.ReadOnly = Convert.ToBoolean(dtTemp.Rows[i]["IsReadOnly"]);
alColumns.Add(dcColumn.ColumnName);
dtReturn.Columns.Add(dcColumn);
}
}
// read data into the dataset
while (reader.Read())
{
drRow = dtReturn.NewRow();
for (i = 0; i < alColumns.Count; i++)
{
drRow[alColumns[i].ToString()] = reader[alColumns[i].ToString()];
}
dtReturn.Rows.Add(drRow);
}
return dtReturn;
}
public static DataTable GetTable()
{
DataTable dtReturn;
SqlConnection connection = new SqlConnection(global::Project.Properties.Settings.Default.DBConnection);
SqlCommand command;
SqlDataReader reader;
try
{
connection.Open();
command = new SqlCommand("SELECT * FROM Table", connection);
command.CommandType = CommandType.Text;
reader = command.ExecuteReader(CommandBehavior.SingleResult);
dtReturn = ConvertSqlDataReaderToDataTable(reader);
dtReturn.TableName = command.CommandText;
reader.Close();
}
catch (Exception e)
{
// ADD ERROR HANDLING HERE
dtReturn = new DataTable();
}
finally
{
connection.Close();
}
return dtReturn;
}