SQL, EXECUTEREADER

[Givisiez]

Hi

I'm programming a windows application. I store values on a SQL-
database.
Now I've a problem reading the values stored.
I want to read a whole column from a table. I tried my SQL Command in
the "Query" from the Microsoft Visual Studio. There I get exactly the
values, which I want.
When I use the same command with a ExecuteReader, the only value which
I get for "reader" is:
System.Data.SqlClient.SqlDataReader
But I expect a table of values with float values. Does anybody has an
Idea where I made a mistake?

SqlDataReader reader;

try
{
SQLComm.CommandText = "SELECT r.valeur FROM
Resultats_mesure_testeur ...

SQLComm.Connection = conn;
conn.Open();
reader = SQLComm.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
test = reader.GetFloat(1);
}


In test I expect a value like "10" or something like that. But when I
debug I got an Error, Indexoutofboundexception.

Thanks for any help in advance!
Cheers,
Marco

 

[sloan]

The array is 0 based.

test = reader.GetFloat(0);


When in doubt , write DEVELOPMENT/DEBUG code like thisl


object o = reader.GetValue( N ) ;

Where N is an ordinal number.

and put a watch on "o".

Do not do this for production code.

 

[Givisiez]

Thanks for your quick answer!!

I tried as you described:
object o = reader.GetValue( N ) ;

for N=0, I get a value, in my case 36, that's great!!! I recive the
value which I should.
But for N=1, I've an exception "IndexOutOfrangeException". But it is a
table with more than 20 values..
So why haven't I access to the other Values?

 

[sloan]

You're only on the first row.

You can find a good datareader example here:

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry

Look in the *Controller.cs file , where * is any noun.





Here is the base snipplet:

Customer item = new Customer();

while (dataReader.Read())
{
if
(!(dataReader.IsDBNull(CustomersAllLayout.CustomerID))) //
CustomersAllLayout.CustomerID is an enum of value 0
{
item = new
BusinessObjects.Customer(dataReader.GetString(CustomersAllLayout.CustomerID)
);

if
(!(dataReader.IsDBNull(CustomersAllLayout.ContactName))) //
CustomersAllLayout.ContactName is an enum of value 1
{
item.ContactName =
dataReader.GetString(CustomersAllLayout.ContactName);
}

if (!(dataReader.IsDBNull(CustomersAllLayout.City)))
//CustomersAllLayout.City is an enum of value 2
{
item.City =
dataReader.GetString(CustomersAllLayout.City);
}


}
}















private List<BusinessObjects.Customer>
SerializeCustomers(IDataReader dataReader, bool deep)
{
BusinessObjects.Customer item = new BusinessObjects.Customer();
List<BusinessObjects.Customer> coll = new
List<BusinessObjects.Customer>();
try
{
while (dataReader.Read())
{
if
(!(dataReader.IsDBNull(CustomersAllLayout.CustomerID)))
{
item = new
BusinessObjects.Customer(dataReader.GetString(CustomersAllLayout.CustomerID)
);

if
(!(dataReader.IsDBNull(CustomersAllLayout.ContactName)))
{
item.ContactName =
dataReader.GetString(CustomersAllLayout.ContactName);
}

if (!(dataReader.IsDBNull(CustomersAllLayout.City)))
{
item.City =
dataReader.GetString(CustomersAllLayout.City);
}

coll.Add(item);
}
}

/////////////////// Here we go, lets check the 2nd
ResultSet
if ((deep))
{
dataReader.NextResult();


while (dataReader.Read())
{
if (!(dataReader.IsDBNull(OrdersAllLayout.OrderID)))
{
BusinessObjects.Order currentOrder = new
BusinessObjects.Order(dataReader.GetInt32(OrdersAllLayout.OrderID));


//customerid
if
(!(dataReader.IsDBNull(OrdersAllLayout.CustomerID)))
{
currentOrder.CustomerID =
dataReader.GetString(OrdersAllLayout.CustomerID);
}


if
(!(dataReader.IsDBNull(OrdersAllLayout.OrderDate)))
{
currentOrder.OrderDate =
dataReader.GetDateTime(OrdersAllLayout.OrderDate);
}
if
(!(dataReader.IsDBNull(OrdersAllLayout.ShippedDate)))
{
currentOrder.ShippedDate =
dataReader.GetDateTime(OrdersAllLayout.ShippedDate);
}

if
(!(dataReader.IsDBNull(OrdersAllLayout.Freight)))
{
//object o = dataReader.GetValue (
OrdersAllLayout.Freight ); // this is how you figure out the datatype, if
you ever get stuck. o = .GetValue and then look at "o" in the debugger
currentOrder.Freight =
dataReader.GetDecimal(OrdersAllLayout.Freight);
}




// Create a new person location filter, that
will look for the specified CustomerID
Filters.CustomerFilter custFilt = new
Filters.CustomerFilter(currentOrder.CustomerID);
// Create a new predicate, that uses the
FilterByLocation method to determine whether the Customer has been found
Predicate<BusinessObjects.Customer>
filterByLocation = new
Predicate<BusinessObjects.Customer>(custFilt.FilterByCustomerId );
// Find the Customer in the collection
BusinessObjects.Customer foundCustomer =
coll.Find(filterByLocation);



//1.1 Way//BusinessObjects.Customer
foundCustomer = coll.Contains(currentOrder.CustomerID);
if (null != foundCustomer)
{
foundCustomer.AllOrders.Add(currentOrder);
}
else
{
throw new ApplicationException("There is an
orphaned Order with Order.OrderID = " +
Convert.ToString(currentOrder.OrderID) + " and Order.CustomerID = " +
currentOrder.CustomerID);
}



}
}



}
return coll;
}
//no catch here... see
http://blogs.msdn.com/brada/archive/2004/12/03/274718.aspx
finally
{
if (!((dataReader == null)))
{
try
{
dataReader.Close();
}
catch
{
}
}
}
}

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Thanks for your quick answer!!

I tried as you described:
object o = reader.GetValue( N ) ;

for N=0, I get a value, in my case 36, that's great!!! I recive the
value which I should.
But for N=1, I've an exception "IndexOutOfrangeException". But it is a
table with more than 20 values..
So why haven't I access to the other Values?

The table maybe has 20 rows but you are returning one column only "valeur "

Modify your query from:
SQLComm.CommandText = "SELECT r.valeur FROM Resultats_mesure_testeur ...

to

SQLComm.CommandText = "SELECT * FROM Resultats_mesure_testeur ...

 

[sloan]

You need to ask yourself?

Am I on a ROW, or am I accessing a COLUMN.

GetString(N) GetDecimal(N) , etc ,etc are COLUMN accessors, now row
accessors.


the while loop will loop over the ROWS.

int rowCounter = 0;
while (dataReader.Read())
{

rowCounter ++;
}

Console.WriteLine( rowCounter.ToString() ) ;

 

[Givisiez]

I tried out earlier SELECT * FROM, but then I get the whole table. The
Select command is right.
The solution was the while(datareader.Read()) -Loop!!!
Thanks a lot for your help!!