CODEWITHSUNDEEP
c#asp.netsql-server-2008sqldatareader
zack
zack

Reputation: 7385

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?

Upvotes: 186

Views: 649973

Answers (13)

Md Farid Uddin Kiron
Md Farid Uddin Kiron

Reputation: 22515

You have to read database columnhere. You could have a look on following code snippet for implementation:

string connectionString = ConfigurationManager.ConnectionStrings["NameOfYourSqlConnectionString"].ConnectionString;
using (var _connection = new SqlConnection(connectionString))
{
    _connection.Open();

    using (SqlCommand command = new SqlCommand("SELECT SomeColumnName FROM TableName", _connection))
    {

        SqlDataReader sqlDataReader = command.ExecuteReader();
        if (sqlDataReader.HasRows)
        {
            while (sqlDataReader.Read())
            {
                string YourFirstDataBaseTableColumn = sqlDataReader["SomeColumn"].ToString(); // Remember Type Casting is required here it has to be according to database column data type
                string YourSecondDataBaseTableColumn = sqlDataReader["SomeColumn"].ToString();
                string YourThridDataBaseTableColumn = sqlDataReader["SomeColumn"].ToString();

            }
        }
        sqlDataReader.Close();
    }
    _connection.Close();
}

Upvotes: 9

Mark Avenius
Mark Avenius

Reputation: 13947

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

or

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

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

Upvotes: 133

Mist
Mist

Reputation: 684

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

Upvotes: 4

Marc Gravell
Marc Gravell

Reputation: 1063944

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.

Upvotes: 9

Piseth Sok
Piseth Sok

Reputation: 1819

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

Upvotes: 20

Mohini Mhetre
Mohini Mhetre

Reputation: 919

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

it wil work

Upvotes: 25

Ali Umair
Ali Umair

Reputation: 1424

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

Upvotes: 12

Tiele Declercq
Tiele Declercq

Reputation: 2160

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.

Upvotes: 22

JBrooks
JBrooks

Reputation: 10013

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"]);

Upvotes: 3

Scott Chamberlain
Scott Chamberlain

Reputation: 127603

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

Upvotes: 177

zack
zack

Reputation: 7385

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

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

Upvotes: 8

Dekker500
Dekker500

Reputation: 821

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

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

Upvotes: 7

Martin
Martin

Reputation: 11041

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"]);
}

Upvotes: 37

Related Questions