c# windows forms / basic data grid

Question

One of the problems I am having with c# is that there seems to be so much information online that I am having trouble finding the right answer to the most basic of questions.

I am trying to do something simple: I have a button, I click it, it queries the database and populates a datagrid on my windows form.

    private void button1_Click(object sender, EventArgs e)
    { 
        SqlConnection c = new SqlConnection("Data Source = (local); Integrated Security = true; Initial Catalog = pubs;  "); 

        c.Open();
        // 2
        // Create new DataAdapter
        SqlCommand cmd = c.CreateCommand();
        cmd.CommandText = @" SELECT * FROM Authors ";
        SqlDataReader reader = cmd.ExecuteReader();

        dataGridView1.DataSource = reader;
        dataGridView1.DataBind();
    }

Error 1 'System.Windows.Forms.DataGridView' does not contain a definition for 'DataBind' and no extension method 'DataBind' accepting a first argument of type 'System.Windows.Forms.DataGridView' could be found.....

I am probably missing a "using directive" but which one? Multiple Google searches tell me how to bind a Yahoo RSS Feed to a gridview or provide various obscure details on "using directives".

Maybe I am using the SqlDataReader incorrectly. Should I be using SqlAdapter instead? What happened to all the good basic tutorials online for windows c# forms? A few months ago I found a couple great tutorials, but they seem to have lost their pageranking and I cannot find them anymore using basic google searches.

Answer 1

You're not missing a using directive; it's just that the WinForms DataGridView doesn't have a DataBind method. Just assigning DataSource is enough to get the binding to happen; you don't need to call a method as well.

However, I don't think you can assign a SqlDataReader as the DataSource. According to the DataSource property documentation in MSDN, the DataSource must be an IList, an IListSource, an IBindingList or an IBindingListView. You will probably instead need to load the data into a DataTable or DataSet (or an object data source populated using an object-relational mapper), and use that as the DataSource.

Answer 2

Try this instead:

using (SqlConnection conn = new SqlConnection("your connection string"))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(@"SELECT * FROM Authors", conn))
    {
        using (SqlDataAdapter adap = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            adap.Fill(dt);
            dataGridView1.DataSource = dt;
        }
    }
}

The DataGridView does not have a DataBind() method because it doesn't need one. Setting the DataSource property handles the binding for you. The using() blocks will automatically close and dispose of everything for you as well.

Note: you should replace "your connection string" with a valid connection string. I left yours out of my sample to avoid the horizontal scrollbars, and I'm not sure yours is valid anyway. You may get a runtime error when you run the code using your connection string. www.connectionstrings.com is a great resource for figuring out a valid connection string.

Update: instead of the nested using() blocks, you can also do it like this:

using (SqlConnection conn = new SqlConnection("..."))
using (SqlCommand cmd = new SqlCommand(@" SELECT * FROM Authors", conn))
using (SqlDataAdapter adap = new SqlDataAdapter(cmd))
{
    conn.Open();
    DataTable dt = new DataTable();
    adap.Fill(dt);
    dataGridView1.DataSource = dt;
}

I prefer the nested style, but it's "half of one, six dozen of the other" to me. Typically, I would encapsulate code like this into a class (called "DataGetter" or whatever) with a static method like:

public static DataTable GetData(string query)
{
    // do all the connecting and adapting and filling and so forth
}

so that the code in your button click would be as simple as:

dataGridView1.DataSource = DataGetter.GetData("SELECT * FROM AUTHORS");

However, I would not do this in any performance-critical section of my code, since you sometimes want to keep a SqlCommand object (and its SqlParameter collection) around between calls. You do not need to keep SqlConnection objects around between calls, thanks to connection pooling (in fact, you don't want to keep them around under any circumstances).