How to get data in INT from SQLDataReader in .NET

Question

I am trying to get int value from the database but It is throwing an error

Unable to cast object of type 'System.Byte' to type 'System.Int32'.

In the database, Active field is tinyint.

Also, how to return both values from this method.

private string CheckData(string firstValue, string SecondValue, int Active)
{
    string Data = "";

    StringBuilder sb = new StringBuilder();
    string query = @"select M.ident Mi, mmp.active Active 
                     from Iden.Iden M
                     inner join PtM.MPt MMP on MMP.mPat = M.id
                     where M.ident = 'firstValue' 
                       and Mi.ident = 'SecondValue'";
    sb.Append(query);

    sb.Replace("firstValue", firstValue);
    sb.Replace("SecondValue", SecondValue);
            
    SqlConnection connection = new SqlConnection(connString);

    SqlCommand cmd = new SqlCommand(sb.ToString());
    cmd.CommandTimeout = 0;
    cmd.CommandType = CommandType.Text;
    cmd.Connection = connection;

    try
    {
        connection.Open();

        SqlDataReader reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Data = reader.GetString(reader.GetOrdinal("Mi"));
                Active = reader.GetInt32(reader.GetOrdinal("Active"));
            }
        }
    }
    catch (Exception ex)
    {
        _log.Error($"Exception:{ex.Message}");
    }
    finally
    {
        connection.Close();
        connection.Dispose();
    }

    return Data;
}

It's a TINY_INT not an INT. A tiny int maps to a byte in .NET code. The reader reads it as an object, which means that the actually representation will be as a boxed byte. You can only unbox boxed values to their actual type. If you want to represent it as an integer in your code, you will need to unbox it to a byte first and then cast the byte to an int: Active = (int) reader.GetByte(reader.GetOrdinal("Active")); — Flydog57
– Flydog57, Commented Apr 12, 2021 at 23:27
Two more comments. 1) Your code has SQL Injection issues, use parameters rather than StringBuilder.Replace, and 2) since you are fishing out the ordinal values, do it once (before going into the while loop) rather than doing on each pass through the loop. Your code may end up noticeably faster (if you have enough rows): var dataOrdinal = reader.GetOrdinal("Mi"); and similar for activeOrdinal. Then using them in the GetXxx methods. — Flydog57
– Flydog57, Commented Apr 12, 2021 at 23:32
How is the Active column declared in your SQL Schema. Is it a Bit? If so, consider making Active a bool in your C# code and using reader.GetBool(). Oh, and you should dispose your data reader. Read up on using the using keyword for disposing IDisposable objects — Flydog57
– Flydog57, Commented Apr 12, 2021 at 23:35
@Flydog57, Thanks. How to return both Data and Active. Also, how to get the boolean if I do not want to use int. — user10216769
– user10216769, Commented Apr 12, 2021 at 23:37
Hi @Flydog57, it is tinyint in the SQL schema. Could you please provide me proper solution for this. Thanks — user10216769
– user10216769, Commented Apr 12, 2021 at 23:40

Flydog57 · Accepted Answer · 2021-04-13 00:02:35Z

Here's a stab at it. I can't debug it (since I don't feel like creating a database).

First I create a type to hold the results. You could just use a Tuple, but this seems clearer:

public class DataActive
{
    public string Data { get; set; }
    public byte Active { get; set; }
}

I make your function return a collection of these - it's not obvious from your code that there is only one.

You'll also notice that I use SqlParameters to add firstValue and secondValue to your query. Look up SQL Injection (and Little Bobby Tables).

If you are using a recent version of C# (which I don't), there's a new syntax for using that requires less indenting. The using statements stick a call to Dispose in a finally statement at the end of the block. Also note that I'm disposing the SqlCommand and the DataReader

public static IEnumerable<DataActive> CheckData(string firstValue, string secondValue)
{
    var results = new List<DataActive>();

    const string query = @"select M.ident Mi,mmp.active Active from Iden.Iden M
                Inner join PtM.MPt MMP on MMP.mPat =M.id                    
                where M.ident = @firstValue and Mi.ident = @secondValue";

    using (var connection = new SqlConnection(connString))
    {
        using (var cmd = new SqlCommand(query))
        {
            cmd.CommandTimeout = 0;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connection;

            cmd.Parameters.Add("@firstValue", SqlDbType.NVarChar, 50).Value = firstValue;
            cmd.Parameters.Add("@secondValue", SqlDbType.NVarChar, 50).Value = secondValue;

            try
            {
                connection.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    var dataOrdinal = reader.GetOrdinal("Mi");
                    var activeOrdinal = reader.GetOrdinal("Active");
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            results.Add(new DataActive
                            {
                                Data = reader.GetString(dataOrdinal),
                                Active = reader.GetByte(activeOrdinal),
                            });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _log.Error($"Exception:{ex.Message}");
            }
        }
    }
    return results;
}

If your TINY_INT Active represents a boolean value, figure out what the rule is, and do a conversion after you get the value using reader.GetByte.

One final note, it's often better to log ex.ToString() rather than ex.Message. You get the message and the stack that way.

Collectives™ on Stack Overflow

How to get data in INT from SQLDataReader in .NET

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related