Read Data from SQLdatareader

How to get data in INT from SQLDataReader in .NET

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.

How to read data using SqlDataReader when two columns in different tables have same name?

You could use alias to give a different name to the duplicate column.

SqlCommand query = new SqlCommand("SELECT Store.StoreID, 
"Store.Name as StoreName," + 
"Product.Name as ProductName," +
" StockRequest.Quantity, StoreInventory.StockLevel from Store" +
" LEFT JOIN StoreInventory ON StoreInventory.StoreID = Store.StoreID" 
+ " LEFT JOIN Product ON StoreInventory.ProductID = 
Product.ProductID" + " LEFT JOIN StockRequest ON StockRequest.StoreID 
= Store.StoreID", conn);

Then just use the right name when reading it, like:

read["StoreID"],
read["StoreName"], read["ProductName"],
read["Quantity"], read["StockLevel"]

Common method to read data from SQL Data Reader

Well, you could do that, you might even want to do that, but you really should not do that.

Data readers can only work in a connected mode, meaning that to work with a data reader it's required to keep an open connection to the database - so if your method returns a data reader it must leave the connection open, and it's the responsibility of the calling method to close and dispose it once it's done using the data reader.

What you should do is use an ORM such as Entity Framework or Dapper (my personal favorite) to get the results from the database directly into classes so that the connection to the database can be closed as soon as the SQL statement have executed.

If you can't, or don't want to use an ORM, here is one way you can reduce code repetition:

Use a generic method that takes in the sql to execute, a Func<IDbDataReader, T>, the parameters needed for the sql command, and returns an instance of T:

public T Execute<T>(
    // sql statement to execute
    string sql, 
    // E.g CommandType.StoredProcedure
    CommandType commandType, 
    // this holds the logic to create an instance of T from the data reader
    Func<IDbDataReader, T> populator, 
    // parameters required to the sql
    params IDbDataParameter[] parameters)
{
    using(var con = new SqlConnection(_connectionString))
    {
        using(var cmd = new SqlCommand(sql, con))
        {
            cmd.CommandType = commandType;
            cmd.Parameters.AddRange(parameters);
            con.Open();
            using(var reader = cmd.ExecuteReader())
            {
                return populator(reader); 
            }
        }
    }
} 

Now, the populator will execute inside the using statements so everything that needs to be disposed gets disposed exactly when it should.

How do you get the values out of SqlDataReader?

Don't call cmd.ExecuteNonQuery();
Try this

    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        object obj1 = reader["ColumnName1"];
        object obj2 = reader["ColumnName2"];  
        object obj3 = reader["ColumnName3"];
    }

Can't read data from SqlDataReader

You just don't need the quotes on the parameter names in a parameterised query:

  cmd.CommandText = "SELECT username, pass, st, lastonline FROM admins WHERE username=@u AND pass=@p";

Your version was trying to actually match against a username of @u and a password of @p.

How to properly read data from SqlDataReader in C#?

using reflector you can see the indexer [] internally is the same as calling the methods

public override object this[int i]
{
    get
    {
        return this.GetValue(i);
    }
}
public override object this[string name]
{
    get
    {
        return this.GetValue(this.GetOrdinal(name));
    }
}

so the actual difference is. if you know the position and care about performance use the int version of the methods.

 int j = (int)reader[0];//direct array access for some column
 int j = reader.GetInt32(0);

if you don't know the position or prefer readability use the string version

 //must first goto hash table and waist time looking for column index
 int j = (int)reader["price"]; //but at least we know the meaning of the column
 int j = reader.GetInt32(reader.GetOrdinal("price"));
 int j = (int)reader.GetValue(reader.GetOrdinal("price"));

and to finalize the difference bettween GetInt32 and GetValue is just that GetInt32 does the type validation and cast for you, so if you know the type of data makes you life easier..

PS. the performance hit of looking up the index of the column by name is usually ignorable.. but.. is not to be dismissed, I have a project where GetOrdinal that is one of the most called functions hundreds of thousand of times, summing up to seconds of time, that I could have avoided by using ints, and now that I'm hitting bottlenecks, I can't rewrite the application.

---

Related Topics