Storing Contents from database in c# control

Question

hi,

i have a function called ViewComments() which retrieve contents from database using SQL commands

     private void ViewComments()
     {           
        hookUp = new SqlConnection("Server=XXXX\\SQLEXPRESS;Database=WebExcerciseDB;" + "Integrated Security=True");
        SqlCmd = new SqlCommand("SELECT PersonName,PersonMail,Comment FROM CommentsTable", hookUp);
        try
        {
            hookUp.Open();

            reader = SqlCmd.ExecuteReader();
            while (reader.Read())
            {
                SQL_Result.Text += reader["PersonName"] + "  " + reader["PersonMail"] + "  " + reader["Comment"] + Convert.ToString(reader["PostDate"]) + "<br/>";
            }
        }
        catch (Exception)
        {
            MessageBox.Show("Error in database code\n");

        }
        finally
        {
            reader.Close();
            hookUp.Close();
        }
    }

i want to store the contents in IList<> control, how to do that?

thanks any way..

Answer 1

Create a class that has properties for the values you want:

public class Comment
{
    public string PersonName {get;set;}
    public string PersonMail {get;set;}
    public string Comment {get;set;}
    public string PostDate {get;set;}
}

and then Create a List and fill it up with the values:

List<Comment> comments = new List<Comment>();

...

while (reader.Read())
{
    comments.Add(new Comment()
    { 
        PersonName = reader["PersonName"], 
        PersonMail = reader["PersonMail"], 
        Comment = reader["Comment"], 
        PostDate = Convert.ToString(reader["PostDate"]) 
    });
}

Answer 2

You could also use LINQ to SQL to create the classes for you. You do this by:

1. In the Visual Studio Solution Explorer, Right click your project and select Add -> New Item
2. Under Categories, select Data, and then LINQ to SQL Classes.
3. Next, in Server Explorer, add a new connection to your SQL database.
4. Drag the desired tables, views, etc from server explorer onto the Object Relational Designer (the edit page) of the DBML file. Viola! Visual Studio has created the classes for you.

Then, to query your database within code, you would do somthing such as:

    private void QueryMyData()
    {
        MyDbmlFileNameDataContext context = new MyDbmlFileNameDataContext("Server=XXXX\\SQLEXPRESS;Database=WebExcerciseDB;" + "Integrated Security=True");

        var query = from c in context.CommentsTable
                    where c.PersonName == "John Doe"        // This is optional if you want to sub-select.
                    select c;

        foreach (var comment in query)
        {
            Console.WriteLine(comment.PersonName + " " + comment.PersonMail + " " comment.Comment);
        }
    }

You can see more examples here or here.

WARNING: From my own personal experience, LINQ does not seem to be very efficient with extremely large data sets.