Reading values from SQL database in C#

Question

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

When using databases in c# you should really have a look at Linq2SQL or Entity Framework. It simplifies a lot. — Albin Sunnanbo
– Albin Sunnanbo, Commented May 14, 2011 at 17:18
@abatishchev Linq2Sql has helped me more often than not. The readability, compile time type checking and the intellisense really helps productivity in my experience. — Albin Sunnanbo
– Albin Sunnanbo, Commented May 14, 2011 at 17:40
@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 — abatishchev
– abatishchev, Commented May 14, 2011 at 18:10
On 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. — TomTom
– TomTom, Commented Sep 2, 2011 at 7:31

Albin Sunnanbo · Accepted Answer · 2011-05-14 17:16:28Z

31

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.

answered May 14, 2011 at 17:16

Albin Sunnanbo

47.2k8 gold badges72 silver badges110 bronze badges

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

1 Comment

Matthew Cox Over a year ago

@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.

Stefan Laity · Accepted Answer · 2014-06-25 12:14:19Z

27

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

edited Jun 25, 2014 at 12:14

Stefan Laity

33 bronze badges

answered May 14, 2011 at 17:17

abatishchev

101k88 gold badges303 silver badges443 bronze badges

Comments

Marc Gravell · Accepted Answer · 2011-05-14 17:20:15Z

7

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.

answered May 14, 2011 at 17:20

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

Dietpixel · Accepted Answer · 2011-05-14 17:25:28Z

0

I would create an object with properties that holds those values and then pass that object around as needed.

public class YourObjectName
{
   public string Username { get; set; }
   public string Item { get; set; }
   public string Amount { get; set; }
   public string Complete { get; set; }
}

YourObjectName a = new YourObjectName();
a.Username = Reader['Username'].ToString();

edited May 14, 2011 at 17:25

answered May 14, 2011 at 17:19

Dietpixel

10.2k11 gold badges31 silver badges33 bronze badges

1 Comment

Marc Gravell Over a year ago

Never, ever, ever call a custom type Object :)

Albert Laure · Accepted Answer · 2013-09-12 08:31:58Z

0

i know its a bit late but you can use local string variables,or string array or list insert the data in the database there and then call it in your console write line

answered Sep 12, 2013 at 8:31

Albert Laure

1,7525 gold badges22 silver badges51 bronze badges

Collectives™ on Stack Overflow

Reading values from SQL database in C#

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related