每行上的datatable请求数据库

Question

我有一个连接类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将再次转到连接类并进行新的查询。怎么了？

Answer 1

因为每次调用"Dt"都会创建一个新的数据表和适配器，并用适配器填充数据表。这将导致在每次调用中执行查询。

您应该将返回的DataTable存储在一个本地变量中，并对其执行类似"Rows"的操作，而不是每次都调用DB类。

因此：

DataTable dt = cc.Dt(sorgu);

然后使用

dt.Rows[0]["siparisMetresi"]

而不是

cc.Dt(sorgu).Rows[0]["siparisMetresi"]

Answer 2

我发现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;
    }