Reading SQL query result with multiple columns in c#-CodePudding

This code is working. It gives me the first and second columns in the first row. But the SQL query result has 3 rows and I want to read them too. How can I do that? Maybe my method is wrong for this but I couldn't figure it out. Thanks.

    private void GetDataAN()
    {

        string sqlAN = "SELECT SUM(Original_Kap_Adeti  Bag_Kap_Adeti), SUM(Original_Kg   Bag_Kg)  FROM Antrepo_Form WHERE Antrepo_No LIKE '"   Antrepo_No.Text   "' GROUP BY Form_Tipi";
        SqlCommand cmdAN = new(sqlAN, Antrepo_DB.con);
        SqlDataReader drAN = cmdAN.ExecuteReader();
                    
        while (drAN.Read())
        {
            Antrepo_Kap.Text = drAN[0].ToString();   //1. row, 1. column
            Antrepo_Kg.Text = drAN[1].ToString();   //1. row, 2. column

           // Ellecleme_Kap.Text = drAN[0].ToString();   //2. row, 1. column
           // Ellecleme_Kg.Text = drAN[1].ToString();   //2. row, 2. column
           // Ihracat_Kap.Text = drAN[0].ToString();   //3. row, 1. column
           // Ihracat_Kg.Text = drAN[1].ToString();   //3. row, 2. column
        }

        AN_Kap.Text = (Antrepo_Kap.Text   Ellecleme_Kap.Text   Ihracat_Kap.Text);
        AN_Kg.Text = (Antrepo_Kg.Text   Ellecleme_Kg.Text   Ihracat_Kg.Text);

    }

CodePudding user response：

I assume that Form_Tipi is a column that tells you what type of data you have (Anthrepo, Ellecleme or Ihracat).

You need to include this column in the select result so that you can access it as part of the result:

string sqlAN = @"SELECT SUM(Original_Kap_Adeti  Bag_Kap_Adeti),
   SUM(Original_Kg   Bag_Kg), 
   Form_Tipi 
   FROM Antrepo_Form 
   WHERE Antrepo_No LIKE '"   Antrepo_No.Text   @"' 
   GROUP BY Form_Tipi";

You can then read the value of the Form_Tipi column while looping over the result set to decide where to put the data from that row:

 while (drAN.Read()) 
 {
    // Get the values of the row into local variables
    var kap = drAN[0].ToString();
    var kg = drAN[1].ToString();
    var formTipi = drAN[2].ToString();

    // Now depending on formTipi fill the correct text box:
    if (formTipi == 'anthrepo') 
    {
        Antrepo_Kap.Text = kap;
        Antrepo_Kg.Text = kg;
    } else if (formTipi == 'ellecleme') 
    {
        Ellecleme_Kap.Text = kap;
        Ellecleme_Kg.Text = kg;
    } else if (formTipi == 'ihracat') 
    {
        Ihracat_Kap.Text = kap;
        Ihracat_Kg.Text = kg;
    }
 }

Note, there are additional issues with your code that are not part of the question:

User input should be sanitized before used as part of the sql statement
The Text boxes should all be cleared before executing the statement so that they are empty when no fitting data for them is found.
The data is in the database is probably not really strings but numerical data. Work with numerical data types instead of strings

CodePudding user response：

You can do it like below :

private void GetDataAN()
{

    string sqlAN = "SELECT SUM(Original_Kap_Adeti  Bag_Kap_Adeti), SUM(Original_Kg   Bag_Kg)  FROM Antrepo_Form WHERE Antrepo_No LIKE @Antrepo_No GROUP BY Form_Tipi";
    SqlCommand cmdAN = new(sqlAN, Antrepo_DB.con);
    cmdAN.Parameters.AddWithValue("@Antrepo_No", Antrepo_No.Text);
    SqlDataReader drAN = cmdAN.ExecuteReader();
    
    If(drAN.HasRows)
    {
        drAN.Read(); // read first row
        Antrepo_Kap.Text = drAN[0].ToString();   //1. row, 1. column
        Antrepo_Kg.Text = drAN[1].ToString();   //1. row, 2. column
        
        drAN.Read(); // read second row
        Ellecleme_Kap.Text = drAN[0].ToString();   //2. row, 1. column
        Ellecleme_Kg.Text = drAN[1].ToString();   //2. row, 2. column
        
        drAN.Read();  // read third row
        Ihracat_Kap.Text = drAN[0].ToString();   //3. row, 1. column
        Ihracat_Kg.Text = drAN[1].ToString();   //3. row, 2. column
    }

    AN_Kap.Text = (Antrepo_Kap.Text   Ellecleme_Kap.Text   Ihracat_Kap.Text);
    AN_Kg.Text = (Antrepo_Kg.Text   Ellecleme_Kg.Text   Ihracat_Kg.Text);

}

CodePudding user response：

This should get you started, but I would advise you to put a breakpoint on the first line, step through all lines of code, and understand what is happening. Also, take note of the comments about SQL injection. Also, look into how and why to dispose of the the objects you are creating (SqlDataReader and SQLCommand).

Change:

Antrepo_Kap.Text = drAN[0].ToString();   //1. row, 1. column
Antrepo_Kg.Text = drAN[1].ToString();

To:

Antrepo_Kap.Text  = drAN[0].ToString();   //1. row, 1. column
Antrepo_Kg.Text  = drAN[1].ToString();

CodePudding user response：

The method drAN.Read() will try to move to the next row. It will return false if there is no next row.

Your code will loop trough all the rows but will constantly overwrite the Antrepo_Kap and Antrepo_Kg with the values for the new row, leaving your with only the results for the last row visible.

In this case you want to fill 6 separate textboxes with your data. You can use something as follow:

// Go to the first row
drAN.Read();
Antrepo_Kap.Text = drAN[0].ToString();   //1. row, 1. column
Antrepo_Kg.Text = drAN[1].ToString();   //1. row, 2. column

// Go to the second row
drAN.Read();
Ellecleme_Kap.Text = drAN[0].ToString();   //2. row, 1. column
Ellecleme_Kg.Text = drAN[1].ToString();   //2. row, 2. column

// Go to the third row
drAN.Read();
Ihracat_Kap.Text = drAN[0].ToString();   //3. row, 1. column
Ihracat_Kg.Text = drAN[1].ToString();   //3. row, 2. column

In this case I assume that there are always 3 (or more) rows. It is better that you check the result of drAN.Read() on each call and stop your code (or do something else) when it's false, otherwise you will get an exception in the rest of your code. This also assumes that the order of results from the database is always the same. Inspect your query to be sure that the same order is always preserved or add extra information so you can identify the row.

Also, like others mentioned, your query is vulnerable for sql injections. You will get errors if someone enters text like O'Brian. It's best that you use parameters, this can look as follows:

string sqlAN = "SELECT SUM(Original_Kap_Adeti  Bag_Kap_Adeti), SUM(Original_Kg   Bag_Kg)  FROM Antrepo_Form WHERE Antrepo_No LIKE @no GROUP BY Form_Tipi";
SqlCommand cmdAN = new(sqlAN, Antrepo_DB.con);
cmdAN.Parameters.AddWithValue("@no", Antrepo_No.Text);