How can i Read data using SqlDataReader

Question

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

using (SqlCommand StrQuer = new SqlCommand("SELECT *  FROM [shopcart].[dbo].[user] WHERE username=@userid AND password=@password", myconn))
{
   StrQuer.Parameters.AddWithValue("@userid", str_usr);
   StrQuer.Parameters.AddWithValue("@password", str_psd);

   SqlDataReader dr = StrQuer.ExecuteReader();

   if (dr.HasRows)
   {
      // MessageBox.Show("loginSuccess");    
   }
}

I know that the reader has values. My SQL command is to select just 1 row from a table. I want to read the items in the row in the reader one by one. How do I do this?

Thanks

such type of questions i dont think should be answered here, very basic thing one can get answer at first search — Md. Parvez Alam, Aug 01 '13 at 10:59

Alex Filipovici · Answer 1 · 2013-08-01T11:02:54.210

2

You may use the FieldCount property:

if (dr.Read())
{
    for (int i = 0; i < dr.FieldCount; i++)
    {
        var value = dr[i];
        // do something
    }
}

edited Aug 01 '13 at 11:02

answered Aug 01 '13 at 10:57

Alex Filipovici

31,789
6
54
78

http://stackoverflow.com/questions/4018114/read-data-from-sqldatareader – Md. Parvez Alam Aug 01 '13 at 11:08

Farhad Jabiyev · Answer 2 · 2013-08-01T11:10:59.080

1

You can use dr.Read() inside while loop.

while(dr.Read())
{
    string firstCell = dr[0].ToString();
    string secondCell = dr[1].ToString();

    // and so on...
}

// It will be better if you close DataReader
dr.Close();

edited Aug 01 '13 at 11:10

answered Aug 01 '13 at 10:57

Farhad Jabiyev

26,014
8
72
98

http://stackoverflow.com/questions/4018114/read-data-from-sqldatareader – Md. Parvez Alam Aug 01 '13 at 11:05

score 1 · Answer 3 · edited May 23 '17 at 10:26

1

if (dr.HasRows)
{
    dr["NameOfColumn"].ToString();    
}

You obviously assign the variable and cast to appropriate type above.

Take a look at my question here too, for another solution Casting Ado.net DataReader to IDataRecord giving strange result

edited May 23 '17 at 10:26

Community

1
1

answered Aug 01 '13 at 10:59

Christian Phillips

18,399
8
53
82

score 1 · Answer 4 · answered Aug 01 '13 at 11:03

1

You can use dr.Read() inside while loop. Like This :

while(dr.read())
{
    // MessageBox.Show("loginSuccess");    
}

answered Aug 01 '13 at 11:03

Jignesh.Raj

5,776
4
27
56

score 0 · Answer 5 · answered Aug 01 '13 at 10:59

Here is full example of sqldatareader.

 SqlConnection myConnection = new SqlConnection();  
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;  
        SqlCommand myCommand = new SqlCommand();  
        myCommand.CommandType = CommandType.Text;  
        myCommand.CommandText = "SELECT CategoryID, CategoryName FROM Categories";  
        myCommand.Connection = myConnection;  
        SqlDataReader myReader;  

        try  
        {  
            myConnection.Open();  
            myReader = myCommand.ExecuteReader();  
            while (myReader.Read())  
            {  
                ListItem li = new ListItem();  
                li.Value = myReader["CategoryID"].ToString();  
                li.Text = myReader["CategoryName"].ToString();  
                ListBox1.Items.Add(li);  
            }  
            myReader.Close();  
        }  
        catch (Exception err)  
        {  
            Response.Write("Error: ");  
            Response.Write(err.ToString());  
        }  
        finally  
        {  
            myConnection.Close();  
        }

This is a terrible example. There are no less than 3 `IDisposable` resources here and you `Dispose` none of them. Please read the fine manual for the `using` statement. — ta.speot.is, Aug 01 '13 at 11:01

score 0 · Answer 6 · answered Aug 01 '13 at 11:02

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

How can i Read data using SqlDataReader

6 Answers6