Section 14.5. Working with Data-Bound Controls

14.5. Working with Data-Bound Controls

Let's try a different, somewhat more declarative approach. Create a new Windows Forms Solution (call it DeclarativeDataDisplay). Begin by enlarging the form, and renaming it to DeclarativeDB.cs and retitling it to Declarative Data Base. Drag a DataGridView onto the form.

Once it is in place, an Action menu will appear. Click the drop down to choose a data source. Click Add Project Data Source to open the Data Source Configuration Wizard. Click Next to choose your datatype, as shown in Figure 14-3.

Figure 14-3. The Data Source Configuration Wizard

Click Database and click Next. This brings you to the Data Source Configuration Wizard, where you can click New Connection, as shown in Figure 14-4.

Figure 14-4. Choosing a data connection

Fill in the appropriate information for the Connection Properties, as shown in Figure 14-5.

Figure 14-5. Setting the connection properties

Be sure to click the Test Connection button before clicking OK. On the next tab, click both "Yes, save the connection as" and "Include sensitive data," as shown in Figure 14-6.

Figure 14-6. Saving sensitive data

You will be presented with the contents of your chosen DataSet. In this case, expand the tables, and expand the Customers table. Select every column except Region (to demonstrate that your query need not return every column) and click Finish.

Click the smart tab for the data grid, and choose Edit Columns. Edit the column headers to meaningful header text, as shown in Figure 14-7.

Figure 14-7. Editing columns

Run the program. You have bound data, as shown in Figure 14-8.

Figure 14-8. The data grid in action

Examine the tray below the grid, where you will find three objects: northwindDataSet, CustomerDataConnector, and customersTableAdapter, as shown in Figure 14-9.

Figure 14-9. Examining the objects in the tray

Each represents an object whose properties you can set by clicking the object and then setting properties in the Properties window.

14.5.1. Populating a DataGrid Programmatically

If you absolutely, positively insist on creating these connection objects by hand, you can certainly do so, and you can manually wire them up to your data grid as well.

Create a new project called ProgrammaticDataDisplay. Rename the .cs file and the form and set the title appropriately. Drag a DataGridView in place, but ignore the action menu.

Right-click the form and choose View Code. Add these lines to the constructor:

string connectionString = "server=localhost;" + "Trusted_Connection=yes; database=northwind"; string commandString =     "Select CompanyName, ContactName, ContactTitle, "     + "Phone, Fax from Customers"; // create a data set and fill it SqlDataAdapter DataAdapter =     new SqlDataAdapter( commandString, connectionString ); DataSet DataSet = new DataSet( ); DataAdapter.Fill( DataSet, "Customers" ); // bind the DataSet to the grid dataGridView1.DataSource =     DataSet.Tables["Customers"].DefaultView;

In this case, you are setting the connection string and command string by hand, and then creating a SqlDataAdapter and DataSet to retrieve the data. You then bind the dataGridView's DataSource property to the Default view of the Customers table you retrieved.

You are free to set the DataGridView's properties programmatically or declaratively, and you can mix and match approaches. But clearly, Microsoft has gone a long way to allowing you to just drag and drop database connection controls onto your form to make interacting with ADO.NET much simpler.

14.5.2. Customizing the DataSet

It is possible to control precisely every aspect of creating the DataSet, instead of using the default settings. You may do so declaratively or programmatically.

In the previous example, when you created the DataSet, you passed in a commandString and a connectionString:

SqlDataAdapter DataAdapter =     new SqlDataAdapter(commandString, connectionString);

These were assigned internally to a SqlCommand object and a SqlConnection object, respectively. You can instead explicitly create these objects.

In this next example, you'll give the class four new members:

private System.Data.SqlClient.SqlConnection myConnection; private System.Data.DataSet myDataSet; private System.Data.SqlClient.SqlCommand myCommand; private System.Data.SqlClient.SqlDataAdapter DataAdapter;

The connection is created by instantiating a SqlConnection object with the connection string:

string connectionString = "server=(local)\\NetSDK;" +      "Trusted_Connection=yes; database=northwind";          myConnection = new System.Data.SqlClient.SqlConnection(connectionString);

and then it is opened explicitly:

myConnection.Open();

By hanging on to this connection, you can reuse it (as you'll see in a subsequent example) and you can also use its transaction support if needed.

You can also allow the DataAdapter to create your connection, and then explicitly open it or even maintain a reference to it by using code such as:

SqlConnection myConnection = myAdapter.Connection

Next, explicitly create the DataSet object and set one of its properties:

myDataSet = new System.Data.DataSet(); myDataSet.CaseSensitive=true;

Setting CaseSensitive to true indicates that string comparisons within DataTable objects are case-sensitive.

Next, explicitly create the SqlCommand object and give that new command object the connection object and the text for the command:

myCommand = new System.Data.SqlClient.SqlCommand() myCommand.Connection=myConnection; myCommand.CommandText = "Select * from Customers";

Finally, create the SqlDataAdapter object and assign to it the SqlCommand object you just established. Then tell the DataAdapter how to map the table columns, using the table you're searching, and instruct the SqlDataAdapter to fill the DataSet object:

DataAdapter = new System.Data.SqlClient.SqlDataAdapter(); DataAdapter.SelectCommand= myCommand; DataAdapter.TableMappings.Add("Table","Customers"); DataAdapter.Fill(myDataSet);

With that done, you're ready to fill the DataGridView (note that this time I've used the default name for the DataGrid):

dataGrid1.DataSource=     myDataSet.Tables["Customers"].DefaultView;

Example 14-3 provides the complete source code.

Example 14-3. Customizing a DataSet

#region Using directives using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Windows.Forms; #endregion namespace CustomizedDataSet {    partial class CustomizedDataSet : Form    {       private System.Data.SqlClient.SqlConnection myConnection;       private System.Data.DataSet myDataSet;       private System.Data.SqlClient.SqlCommand myCommand;       private System.Data.SqlClient.SqlDataAdapter DataAdapter;       public CustomizedDataSet( )       {          InitializeComponent( );          string connectionString = "server=localhost;" +             "Trusted_Connection=yes; database=northwind";          myConnection = new             System.Data.SqlClient.SqlConnection( connectionString );          myConnection.Open( );          // create the DataSet and set a property          myDataSet = new System.Data.DataSet( );          myDataSet.CaseSensitive = true;          // create the SqlCommand  object and assign the          // connection and the select statement          myCommand = new System.Data.SqlClient.SqlCommand( );          myCommand.Connection = myConnection;          myCommand.CommandText = "Select * from Customers";          // create the DataAdapter object and pass in the          // SQL Command object and establish the table mappings          DataAdapter = new System.Data.SqlClient.SqlDataAdapter( );          DataAdapter.SelectCommand = myCommand;          DataAdapter.TableMappings.Add( "Table", "Customers" );          // Tell the DataAdapter object to fill the DataSet          DataAdapter.Fill( myDataSet );          // display it in the grid          dataGridView1.DataSource =             myDataSet.Tables["Customers"].DefaultView;       }    } }