CODEWITHSUNDEEP
c#asp.netsqldatasourcesqldatareader
TheTechGuy
TheTechGuy

Reputation: 17354

Reference data in SQLDataReader by column and rows or any alternative

I am running a stored procedure and the result is this format

+------+--------+-----+-------+
|  ID  | Resign | Sum | Count |
+------+--------+-----+-------+
| 1234 |      0 | 400 |     3 |
| 1234 |      1 | 800 |     4 |
+------+--------+-----+-------+

I tried this code to reference the values returned by the query but, it seem not working the way I want it

if (conn.State != ConnectionState.Open)
    conn.Open();
    SqlCommand sc = new SqlCommand();
    sc.CommandText = "usp_GetResignPool";
    sc.CommandType = CommandType.StoredProcedure;
    sc.Connection = conn;
    sc.Parameters.Add(AddParam(EndDate, "@EndDate"));
    sc.Parameters.Add(AddParam(am_id, "@id"));

    SqlDataReader reader;
    reader = sc.ExecuteReader();

 while (reader.Read())
            {
                if reader. // lost here
            }

How can I do something like this. ↓

int resigned = 0, resign_count = 0, not_resigned = 0, notresign_count =0;

if (read["Resigned"] == 1)
{
    resigned = read["sum"];
    resign_count = read["count"];
}
else
{
    not_resigned = read["sum"];
    notresign_count = read["count"]; 
}

It is not important that I used SQLDataReader.

Edit: Real column names

ID        Resigned    sum                    count
--------- ----------- ---------------------- -----------

Upvotes: 2

Views: 625

Answers (3)

user596075
user596075

Reputation:

It didn't work because you don't have a column in your table named "Resigned", like you have when you are working with your SqlDataReader.

EDIT: I think the root of the problem is the way you are adding parameters. AddParam() is not the method you want to be using. Therefore, your result set is probably empty.

....

SqlCommand sc = new SqlCommand(); 
sc.CommandText = "usp_GetResignPool"; 
sc.CommandType = CommandType.StoredProcedure; 
sc.Connection = conn; 
sc.Parameters.AddWithValue("@EndDate", EndDate);
sc.Parameters.AddWithValue("id", am_id);

SqlDataReader reader; 
reader = sc.ExecuteReader(); 

using (reader = sc.ExecuteReader())
{
    while (reader.Read())
    {
        if (Convert.ToInt32(read["Resign"]) == 1)   
        {   
            resigned = Convert.ToInt32(read["Sum"]);   
            resign_count = Convert.ToInt32(read["Count"]);   
        }   
        else   
        {   
            not_resigned = Convert.ToInt32(read["Sum"]);   
            notresign_count = Convert.ToInt32(read["Count"]);    
        } 
    }
}

Notice how I changed your element indicator to "Resign". This needs to match the column that is returned in your dataset. Or, you could use a column number to get this, like so:

        if (Convert.ToInt32(read[1]) == 1)   
        {   
            resigned = Convert.ToInt32(read[2]);   
            resign_count = read[3];   
        }   
        else   
        {   
            not_resigned = Convert.ToInt32(read[2]);   
            notresign_count = Convert.ToInt32(read[3]);    
        }

Also, keep in my that in every iteration or your while loop, you'll be overwriting the variables resigned, resign_count or not_resigned and notresign_count.

Upvotes: 3

dillenmeister
dillenmeister

Reputation: 1647

Would this work?

int resign = 0;
int not_resign = 0;
int resign_count = 0;
int not_resign_count = 0;

while (reader.Read())
{   
    if (Convert.ToInt32(reader["Resigned"]) == 1)
    {
        resign = Convert.ToInt32(reader["Sum"]);        
        resign_count = Convert.ToInt32(reader["Count"]);        
    }
    else
    {
        not_resign = Convert.ToInt32(reader["Sum"]);        
        not_resign_count = Convert.ToInt32(reader["Count"]);        
    } 
}

Upvotes: 1

emirc
emirc

Reputation: 2018

Can you post your query from the procedure? Are the column names really "Sum" and "Count"? There are reserved words, maybe you should try using "AS" and give other names to these to columns in the projection.

Upvotes: 0

Related Questions