datatable request database on every row

I have a connection class 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, MessageBoxIc开发者_运维知识库on.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;


    }

and this is my code which I get the values in a windows form

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"]);

the codes are work but in every cc.Dt(sorgu).Rows[0] row cc clas is going to connection class again and doing a new query. what is wrong?

---

Because every time you call 'Dt' you create a new datatable and adapter and fill the datatable with the adapter. This will cause the query to be executed on each call.

You should store the returned DataTable in a local variable and do your operations like 'Rows' on that instead of calling your DB class every time.

So:

DataTable dt = cc.Dt(sorgu);

and then use

dt.Rows[0]["siparisMetresi"]

instead of

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

---

I have found DataAdapters horribly unpredictable, and I bet that is your problem.

I would instead wrap up your DataTable from the connection by getting a reader and converting to a table. I found this code on the net about 5 years ago, and still use it every time (ConvertSqlDataReaderToDataTable). The code below shows how to call it. You should be able to modify to your needs very easily. However, if you only pass through once, don't create the DataTable, but just use the DataReader instead.

        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;
    }