CODEWITHSUNDEEP
c#sql-serversqldatareader
user10216769
user10216769

Reputation: 33

How to get data in INT from SQLDataReader in .NET

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

Upvotes: 0

Views: 1324

Answers (1)

Flydog57
Flydog57

Reputation: 7111

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.

Upvotes: 2

Related Questions