Reading values from SQL database in C#
Solution 1
One problem is missing braces after the while
while (myReader.Read())
{ // <<- here
Console.WriteLine(myReader["Username"].ToString());
Console.WriteLine(myReader["Item"].ToString());
Console.WriteLine(myReader["Amount"].ToString());
Console.WriteLine(myReader["Complete"].ToString());
} // <<- here
if you skip the braces only the first line will be processed in each loop, the rest will be processed after the loop, then myReader
is past the last row.
Solution 2
Don't forget to use the using(){}
block :
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("select * from Requests where Complete = 0", connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["Username"].ToString());
Console.WriteLine(reader["Item"].ToString());
Console.WriteLine(reader["Amount"].ToString());
Console.WriteLine(reader["Complete"].ToString());
}
}
}
Solution 3
Personally I'd write a class with 4 properties (with matching names and types), then use "dapper" (http://code.google.com/p/dapper-dot-net/):
var data = connection.Query<Request>(
"select * from Requests where Complete = 0").ToList();
With something like:
public class Request {
public string Username{get;set;}
...
public bool Complete {get;set;}
}
Dapper is free, simple, has parameterisation to avoid SQL-injection, and is very very fast.
Related videos on Youtube
Paul
Updated on July 09, 2022Comments
-
Paul almost 2 years
i have just started learning C# and i can write data to the database without a problem. But i'm having problems with reading, the SQL executes fine but i'm having issues with storing it. How would i store the four columns that should be returned and then show them as a message box? Thanks.
SqlCommand myCommand = new SqlCommand("select * from Requests where Complete = 0", myConnection); SqlDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) Console.WriteLine(myReader["Username"].ToString()); Console.WriteLine(myReader["Item"].ToString()); Console.WriteLine(myReader["Amount"].ToString()); Console.WriteLine(myReader["Complete"].ToString());
-
Albin Sunnanbo about 13 yearsWhen using databases in c# you should really have a look at Linq2SQL or Entity Framework. It simplifies a lot.
-
Albin Sunnanbo about 13 years@abatishchev Linq2Sql has helped me more often than not. The readability, compile time type checking and the intellisense really helps productivity in my experience.
-
abatishchev about 13 years@Albin: I agree that ORM is great technology but it isn't necessary to use it everywhere. On newbie level it will confuse more then teach
-
TomTom over 12 yearsOn top the question has NOTHING to do with teh database but more with how to deal with the data after reading it. Poster is bviously challenged with beginner questions regarding programming.
-
-
Matthew Cox about 13 years@Paul if this answered your question, which appears to be the case, please be sure to mark the response as the answer. This is the check mark symbol by answer posts.
-
Marc Gravell about 13 yearsNever, ever, ever call a custom type
Object
:)