SqlDataReader and method scope

Question

Why is data read from SqlDataReader not available to a method call? I have a table, with 'id' as column in it. When I make a query to database, it returns rows.

This code doesnt work (Says 'id' column doesnt exist):

con.Open();
SqlDataReader requestReader = cmd.ExecuteReader();
if (requestReader.HasRows)
{
   DataTable requestTable = requestReader.GetSchemaTable();
   request = ReadRequest(requestTable.Rows[0]);        
}

con.Close();

while this one works:

con.Open();
SqlDataReader requestReader = cmd.ExecuteReader();
if (requestReader.HasRows)
{
   DataTable requestTable = requestReader.GetSchemaTable();
   var requestRow = requestTable.Rows[0];
   request = new Request();
   request.UniqueId = (string)requestRow["id"];
}

con.Close();

stupid question. Like Tim said, even the second approach didnt work, but due to quick testing and code-structuring, I didnt catch that. Thanks all. — PushpRaj
– PushpRaj, Commented Apr 7, 2014 at 11:00

Tim Schmelter · Accepted Answer · 2014-04-07 10:16:45Z

1

You are using DataReader.GetSchemaTable which returns a DataTable with all schema informations for a given table.

It has following columns:

ColumnName
ColumnOrdinal
ColumnSize
NumericPrecision
// .. 26 others

So you don't find your id-column which belongs to your table. That's why you get the error "'id' column doesnt exist". I doubt that your second approach works. I don't see why you need GetSchemaTable at all. You just have to advance the reader to the next record:

if (requestReader.HasRows && requestReader.Read())
{
   int id = requestReader.GetInt32(requestReader.GetOrdinal("id"));
   // ...
}

answered Apr 7, 2014 at 10:16

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlDataReader and method scope

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related