Read data from SqlDataReader

问题:

I have a SQL Server 2008 database and I am working on it in the backend. I am working on asp.net/C#

SqlDataReader rdr = cmd.ExecuteReader();  
while (rdr.Read())  
{              
   //how do I read strings here????  
}  

I know that the reader has values. My SQL command is to select just 1 column from a table. The column contains strings ONLY. I want to read the strings (rows) in the reader one by one. How do I do this?

回答1:

using(SqlDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        var myString = rdr.GetString(0); //The 0 stands for \"the 0\'th column\", so the first column of the result.
        // Do somthing with this rows string, for example to put them in to a list
        listDeclaredElsewhere.Add(myString);
    }
}

回答2:

string col1Value = rdr[\"ColumnOneName\"].ToString();

or

string col1Value = rdr[0].ToString();

These are objects, so you need to either cast them or .ToString().

回答3:

Put the name of the column begin returned from the database where \"ColumnName\" is. If it is a string, you can use .ToString(). If it is another type, you need to convert it using System.Convert.

SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
    string column = rdr[\"ColumnName\"].ToString();
    int columnValue = Convert.ToInt32(rdr[\"ColumnName\"]);
}

回答4:

while(rdr.Read())
{
   string col=rdr[\"colName\"].ToString();
}

it wil work

回答5:

For a single result:

if (reader.Read())
{
    Response.Write(reader[0].ToString());
    Response.Write(reader[1].ToString());
}

For multiple results:

while (reader.Read())
{
    Response.Write(reader[0].ToString());
    Response.Write(reader[1].ToString());
}

回答6:

Thought to share my helper method for those who can use it:

public static class Sql
{
    public static T Read<T>(DbDataReader DataReader, string FieldName)
    {
        int FieldIndex;
        try { FieldIndex = DataReader.GetOrdinal(FieldName); }
        catch { return default(T); }

        if (DataReader.IsDBNull(FieldIndex))
        {
            return default(T);
        }
        else
        {
            object readData = DataReader.GetValue(FieldIndex);
            if (readData is T)
            {
                return (T)readData;
            }
            else
            {
                try
                {
                    return (T)Convert.ChangeType(readData, typeof(T));
                }
                catch (InvalidCastException)
                {
                    return default(T);
                }
            }
        }
    }
}

Usage:

cmd.CommandText = @\"SELECT DISTINCT [SoftwareCode00], [MachineID] 
                    FROM [CM_S01].[dbo].[INSTALLED_SOFTWARE_DATA]\";
using (SqlDataReader data = cmd.ExecuteReader())
{
    while (data.Read())
    {
        usedBy.Add(
            Sql.Read<String>(data, \"SoftwareCode00\"), 
            Sql.Read<Int32>(data, \"MachineID\"));
    }
}

The helper method casts to any value you like, if it can\'t cast or the database value is NULL, the result will be null.

回答7:

Actually, I figured it out myself that I could do this:

while (rdr.read())
{  
  string str = rdr.GetValue().ToString().Trim();  
}

回答8:

In the simplest terms, if your query returns column_name and it holds a string:

while (rdr.Read())
{
    string yourString = rdr.getString(\"column_name\")
}

回答9:

I know this is kind of old but if you are reading the contents of a SqlDataReader into a class, then this will be very handy. the column names of reader and class should be same

public static List<T> Fill<T>(this SqlDataReader reader) where T : new()
        {
            List<T> res = new List<T>();
            while (reader.Read())
            {
                T t = new T();
                for (int inc = 0; inc < reader.FieldCount; inc++)
                {
                    Type type = t.GetType();
                    string name = reader.GetName(inc);
                    PropertyInfo prop = type.GetProperty(name);
                    if (prop != null)
                    {
                        if (name == prop.Name)
                        {
                            var value = reader.GetValue(inc);
                            if (value != DBNull.Value)
                            { 
                                prop.SetValue(t, Convert.ChangeType(value, prop.PropertyType), null);
                            }
                            //prop.SetValue(t, value, null);

                        }
                    }
                }
                res.Add(t);
            }
            reader.Close();

            return res;
        }

回答10:

I have a helper function like:

  public static string GetString(object o)
    {
        if (o == DBNull.Value)
            return \"\";

        return o.ToString();
    }

then I use it to extract the string:

 tbUserName.Text = GetString(reader[\"UserName\"]);

回答11:

I would argue against using SqlDataReader here; ADO.NET has lots of edge cases and complications, and in my experience most manually written ADO.NET code is broken in at least one way (usually subtle and contextual).

Tools exist to avoid this. For example, in the case here you want to read a column of strings. Dapper makes that completely painless:

var region = ... // some filter
var vals = connection.Query<string>(
    \"select Name from Table where Region=@region\", // query
    new { region } // parameters
).AsList();

Dapper here is dealing with all the parameterization, execution, and row processing - and a lot of other grungy details of ADO.NET. The <string> can be replaced with <SomeType> to materialize entire rows into objects.

回答12:

I usually read data by data reader this way. just added a small example.

string connectionString = \"Data Source=DESKTOP-2EV7CF4;Initial Catalog=TestDB;User ID=sa;Password=tintin11#\";
string queryString = \"Select * from EMP\";

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(queryString, connection))
            {
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(String.Format(\"{0}, {1}\", reader[0], reader[1]));
                        }
                    }
                    reader.Close();
                }
            }