While the SqlDataSource control is a nifty little thing, oftentimes a developer has to have more control over the page lifecycle (asp.net) or doesn’t want to use a BindingSource (winforms). Instead, you just want to query your database and get a DataSet back. Maybe you’re building a data access layer, or you’re using someone else’s framework, or it’s just biting you in the butt that binding with SqlDataSource fires off events in a completely unintuitive order.

We’ve seen how to execute a query that just returns one thing with ExecuteScalar. Now, though, we need entire results. And for whatever reason, we want a DataSet, not a SqlDataReader.

Luckily for us, querying and getting results into a DataSet using SqlDataAdapter is extremely easy, and works the same both in web forms and winforms. The following code is a full working example for querying Northwind (I just can’t get used to AdventureWorks yet, sorry) and getting a DataSet back. You can Bind a DataGridView to it, or a GridView, or loop the tables, or whatever.

Notes:
1. You have to have a using System.Data and using System.Data.SqlClient.
2. Your connection string should normally be stored in Settings (winforms) or web.config (web forms). In this example, I was playing with winforms, so it’s a Setting. For web forms, you’d use ConfigurationManager.ConnectionStrings.

private void button1_Click(object sender, EventArgs e)
{
    DataSet myDataSet = new DataSet();
    using (SqlConnection sqlconnection = new SqlConnection(Properties.Settings.Default.Northwind))
    {
        SqlCommand command = new SqlCommand("select * from products", sqlconnection);
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        adapter.Fill(myDataSet);
    }
}

It’s really that easy. Happy coding!

If you found this post helpful, please spread the love and share using one of the bookmark links below. Thanks!