Creating a Dataset in Code

The ch10_01 example creates a connection to a data source, SQL Server in this case, and then binds the data from that data source to a data grid. This example uses the authors table in the pubs database, and displays that table, as you can see in Figure 10.2.

Figure 10.2. The ch10_01 example.

We'll take this example apart line by line to see what makes it tick. When the user clicks the Connect to Database button you see in Figure 10.2, this application starts by creating a new dataset object named dataset1 , passing that name to the DataSet constructor (note that we're using the System.Data.SqlClient namespace here to access the SQL Server data objects):

 using System.Data.SqlClient; private void button1_Click(object sender, System.EventArgs e) {  DataSet dataset1 = new DataSet("dataset1");  .     .     . 

We'll need a connection object to connect to the authors table. Here's the connection string and connection object in this example (you have to modify this connection string if you want to run this example yourself):

 private void button1_Click(object sender, System.EventArgs e) {   DataSet dataset1 = new DataSet("dataset1");  string connectionString = "workstation id=STEVE;packet size=4096;" +   "integrated security=SSPI;initial catalog=pubs;" +   "persist security info=False";   SqlConnection connection1 = new SqlConnection(connectionString);  .     .     . 

At this point, we have a Connection object, and we're working towards creating a data adapter that we'll use to get data from the database. Our next step is to use the connection object to create a command object, and then we can assign that command object to a data adapter's SelectCommand property in order to set the SQL the data adapter will use to fetch data from the database.

In this example, we'll create a SqlCommand object, giving it the SQL SELECT * FROM authors and set the command's type to CommandType.Text . After opening this new connection object, we can assign that connection object to the command object's Connection property like this:

 private void button1_Click(object sender, System.EventArgs e) {   DataSet dataset1 = new DataSet("dataset1");   string connectionString = "workstation id=STEVE;packet size=4096;" +     "integrated security=SSPI;initial catalog=pubs;" +     "persist security info=False";   SqlConnection connection1 = new SqlConnection(connectionString);  SqlCommand command1 = new SqlCommand("SELECT * FROM authors");   command1.CommandType = CommandType.Text;   connection1.Open();   command1.Connection = connection1;  .     .     . 

Now we're ready to create the data adapter we'll need. To get the authors table from the database, we'll create a SqlDataAdapter object, and assign our command object to that adapter's SelectCommand property. After that, we create a DataSet object, dataSet1 , and use the data adapter to fill that dataset with the Fill method.

There's one more thing to be aware of. When you generate a dataset from a data adapter using the IDE's Data, Generate Dataset menu item, that dataset is configured for the tables the data adapter provides. But in this example, our dataset object is new, which means that we must specify which table in the dataset we want to store data in, and we do that by passing the name " authors " to the Fill method. All we need to do after that is to bind the filled dataset to a data grid as you see in Figure 10.2:

 private void button1_Click(object sender, System.EventArgs e) {   DataSet dataset1 = new DataSet("dataset1");   string connectionString = "workstation id=STEVE;packet size=4096;" +     "integrated security=SSPI;initial catalog=pubs;" +     "persist security info=False";   SqlConnection connection1 = new SqlConnection(connectionString);   SqlCommand command1 = new SqlCommand("SELECT * FROM authors");   command1.CommandType = CommandType.Text;   connection1.Open();   command1.Connection = connection1;  SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter();   sqlDataAdapter1.SelectCommand = command1;   sqlDataAdapter1.Fill(dataset1, "authors");   dataGrid1.SetDataBinding(dataset1, "authors");  } 

And that's itnow we've connected to a database. You can see the results in Figure 10.2, where the authors table appears in the data grid when the user clicks the Connect to Database button.