C# How to implement method that return list of SQL result?

Question

using (var connection = new SqlConnection("user id=xxx;" +
                   "password=xxx;server=xxx;" +
                   "Trusted_Connection=yes;" +
                   "database=xxx; " +
                   "connection timeout=30"))
{
    connection.Open();
    string sql = "SELECT * FROM testTable";
    using (var command = new SqlCommand(sql, connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                .
                .
                .
                .
                .
            } 
        }
    }
}

The code above can be used to retrive the list of rows of records and display them in my C# console application but it will be very messy to be in Main method. I'm wondering how to handle this chunk of records and store them into a list or something by calling a method. Will it be possible in C# console application?

Karl Anderson · Accepted Answer · 2013-09-12 03:23:51Z

Not knowing what is in your database table, I will make up an example:

Account class:

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
}

Now you can have your reader loop populate each Person, like this:

while (reader.Read())
{
    var person = new Person();
    person.FirstName = reader["FirstName"].ToString();
    person.LastName = reader["LastName"].ToString();
    person.Age = Convert.ToInt32(reader["Age"]);
}

Finally, we want to build a list of Person objects to return, like this:

var listOfPerson = new List<Person>();

while (reader.Read())
{
    var person = new Person();
    person.FirstName = reader["FirstName"].ToString();
    person.LastName = reader["LastName"].ToString();
    person.Age = Convert.ToInt32(reader["Age"]);

    listOfPerson.Add(person);
}

return listOfPerson;

Complete code:

public List<Person> LoadPeople()
{
    var listOfPerson = new List<Person>();

    using (var connection = new SqlConnection("user id=xxx;" +
               "password=xxx;server=xxx;" +
               "Trusted_Connection=yes;" +
               "database=xxx; " +
               "connection timeout=30"))
    {
        connection.Open();
        string sql = "SELECT * FROM testTable";
        using (var command = new SqlCommand(sql, connection))
        {
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var person = new Person();
                    person.FirstName = reader["FirstName"].ToString();
                    person.LastName = reader["LastName"].ToString();
                    person.Age = Convert.ToInt32(reader["Age"]);

                    listOfPerson.Add(person);
                }
            }
        }
    }

    return listOfPerson;
}

Damith · Accepted Answer · 2013-09-12 03:30:50Z

1

You can create custom class with the properties what you want and then in each record you can create object of it and add it to a list. if you want to print any results given in sql statement then you can do as below.

using (var connection = new SqlConnection(connectionString))
using (var adapter = new SqlDataAdapter(sql, connection))
{
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    foreach (DataRow row in dt.Rows)
    {
        Console.WriteLine("--- Row ---");
        foreach (var item in row.ItemArray) 
        {
            Console.Write("Item: "); 
            Console.WriteLine(item); 
        }
    }
}

edited Sep 12, 2013 at 3:30

answered Sep 12, 2013 at 3:23

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Comments

Pyromancer · Accepted Answer · 2013-09-12 03:30:55Z

1

The Read() Method reads the data (table) you are accessing so, you can save it to a list.

here I suppose that the data in the rows are strings

list<string> firstRow = new list<string>;
list<string> secondRow = new list<string>;

//in your while(reader.Read()) code . . . 

while (reader.Read())
            {
                firstRow.Add(reader[0].ToString());
                secondRow.Add(reader[1].ToString());
            }

Hope this helps.

answered Sep 12, 2013 at 3:30

Pyromancer

2,5395 gold badges22 silver badges28 bronze badges

Collectives™ on Stack Overflow

C# How to implement method that return list of SQL result?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related