Adapting Data to Windows Controls

If you are familiar with it, you probably know already that the DataSet class was developed to help you create and manage any type of list-based application. The high level of flexibility that this class offers also allows it to directly integrate with a data-based application, such as one created with Microsoft SQL Server. The elements of a DataSet object directly relate to those of a database application.

As mentioned already, a DataSet object is primarily used to create a list, not a formal database in the strict sense of Microsoft SQL Server, Microsoft Access, or Corel Paradox, etc. This means that a list-based application lead by a DataSet is primarily a list. In order to read information of a formal database and use it in a DataSet list, you must "convert" or adapt it. A data adapter is an object that takes data from a database, reads that data, and "fills" a DataSet object with that data. In reverse, a data adapter can get the data stored in, or manipulated by, a DataSet object and fill or update a database with that data. To be able to apply these scenarios to any database, the .NET Framework provides various data adapters, each adapted for a particular category of database.

1. Start Microsoft Visual Studio .NET or Visual C#
2. Display the Server Explorer. Expand the Servers node, followed by the name of the computer, followed by SQL Servers, followed by the name of the server
3. Right-click the server and click New Database
4. Set the New Database Name to GCS and accept to use Windows NT Persist Security Info
5. Click OK
6. Under the name of the server in Server Explorer, expand the GCS node
7. Right-click the Tables node and click New Table
8. Create the table with the following columns (change only the indicated information; anything that is not mentioned should be ignored and use the default):
    

   Column Name	Data Type	Length	Allow Nulls	Other Properties
   EmployeeID	int			Primary Key Identity: Yes
   EmployeeNbr	char	6
   DateHired	smalldatetime
   FirstName	varchar	20
   LastName	varchar	20	Unchecked
   HourlySalary	smallmoney
   IsMarried	bit

9. Save the table as Employees and close it
10. Create a new Windows Application named GCS1
11. Design the form as follows:
     

    Control Name Text/CaptionText Additional Properties DataGrid   Employees Records Anchor: Top, Bottom, Left, Right AutoFormat: Colorful 2 Button btnClose Close Anchor: Bottom, Right

12. Save all

The most regularly performed operation of a data adapter is to read the values stored in a table of a database. This reading operation is commonly referred to as selection. To proceed, you use the following SQL formula:

SELECT What FROM TableName

In this formula, the SELECT and the FROM keywords are required. The keyword SELECT in this case means "read". In other words, you would be asking the data adapter to read something.

The TableName placeholder of our formula allows you to specify the name of the table that holds the data you want to read. You must make sure the table exists and is part of the connection established in the current application.

The What factor of our formula allows you to specify one or more columns of the table. If you want to use the values of only one column, specify its name in the What placeholder of our formula. An example would be:

SELECT VideoTitle FROM Videos

In this case, you are asking the data adapter to read all values stored under the column named VideoTitle from the table named Videos.

If you want to read data from more than one column, specify their list in the What placeholder of our formula and separate the names of columns with a comma. Here is an example:

SELECT VideoTitle, Director, Rating FROM Videos

If you want to select all columns of the table, you can list them in the same way. As an alternative, to represent all columns of a table, you can use the * in the What placeholder of our formula. Here is an example:

SELECT * FROM Videos

This means that the data adapter would be asked to read all values under all columns of the table named Videos.

As done in the previous lessons, remember that you must first establish a connection to the server and to the database. This is an example that creates a connection to a database named VideoCollection in the local server and creates a SELECT statement to read all values of a table named Videos:

private void Form1_Load(object sender, System.EventArgs e)
{
	MySqlConnection cnnVideos = new MySqlConnection(
		"Data Source=localhost;Database='VideoCollection';Persist Security Info=yes");

	string strVideos = "SELECT * FROM Videos;";
}

In order to read information from a Microsoft SQL Server database and make it available to a DataSet object, you can use an object created from the SqlDataAdapter class. This class is defined in the MySql.Data.MySqlClient namespace of the System.Data.dll library. The SqlDataAdapter class is derived from the DbDataAdapter class, itself derived from the DataAdapter class. The DbDataAdapter and the DataAdapter classes are defined in the System.Data.Common namespaces of the System.Data.dll library.

To use the SqlDataAdapter class, you can declare a pointer to SqlDataAdapter using one of its constructors. If you don't want to manually declare the variable, in the Data section of the Toolbox, you can click the SqlDataAdapter button and click the form. This would launch the Data Adapter Configuration Wizard:

If you want to continue with the wizard, you can click Next. If you plan to "manually" configure the data adapter as we will describe in the next few sections, you can click Cancel. A SqlDataAdapter object would be added to your application. You can then use the Properties window to configure it:

As another alternative, if you already know the table you want to use from a database, in the Server Explorer, locate the table under the Tables node of its database, drag the table to the form. A data adapter and its connection would automatically be created and configured. You can also drag more than one table if your data adapter would need to read from many tables.

To allow the data adapter to use values produced from reading in a table, the SqlDataAdapter class is equipped with a property named SelectCommand of type SqlCommand. To use it, you can first declare a pointer to SqlDataAdapter using its default constructor.

SqlDataAdapter dadVideoCollection = new SqlDataAdapter();

To specify how data would be read, you can first create a MySqlCommand object that would carry a SQL SELECT statement:

MySqlConnection cnnVideos = new MySqlConnection(
		"Data Source=localhost;Database='VideoCollection';Persist Security Info=yes");

string strVideos = "SELECT VideoTitle FROM Videos;";
MySqlCommand    cmdVideos = new MySqlCommand(strVideos, cnnVideos);

Equipped with a MySqlCommand object that holds a SELECT statement, you can assign it to the SqlDataAdapter.SelectCommand property of your data adapter. This would be done as follows:

private void Form1_Load(object sender, System.EventArgs e)
{
	MySqlConnection cnnVideos = new MySqlConnection(
		"Data Source=localhost;Database='VideoCollection';Persist Security Info=yes");

	string strVideos = "SELECT * FROM Videos;";
	MySqlCommand    cmdVideos = new MySqlCommand(strVideos, cnnVideos);

	SqlDataAdapter dadVideoCollection = new SqlDataAdapter();
	dadVideoCollection.SelectCommand = cmdVideos;

	cnnVideos.Open();
	cnnVideos.Close();
}

If you don't want to use the default constructor and the SelectCommand property separately, you can use the second constructor of the SqlDataAdapter class. Its syntax is:

public SqlDataAdapter(MySqlCommand selectCommand);

This constructor takes as argument a MySqlCommand object. This time, instead of assign the command to the SelectCommand property, you can pass that MySqlCommand object to the SqlDataAdapter variable when declaring it. This would be done as follows:

private void Form1_Load(object sender, System.EventArgs e)
{
	MySqlConnection cnnVideos = new MySqlConnection(
		"Data Source=localhost;Database='VideoCollection';Persist Security Info=yes");

string strVideos = "SELECT VideoID, VideoTitle, Director, YearReleased, VideoLength, Rating FROM Videos;";
	MySqlCommand    cmdVideos = new MySqlCommand(strVideos, cnnVideos);

	SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

	cnnVideos.Open();
	cnnVideos.Close();
}

Notice that with both constructors reviewed above, you must pass the connection to a MySqlCommand object. As an alternative, you can create a connection but pass it directly to the data adapter when declaring its variable. To do this, you can use the third constructor of the SqlDataAdapter class. Its syntax is:

public SqlDataAdapter(String selectCommandText,  MySqlConnection selectConnection);

The first argument of this constructor expects the statement, passed as string, that specifies how the data would be read. The second argument is a MySqlConnection object that specifies how the connection to database would be handled. Here is an example:

private void Form1_Load(object sender, System.EventArgs e)
{
	MySqlConnection cnnVideos = new MySqlConnection(
		"Data Source=localhost;Database='VideoCollection';Persist Security Info=yes");

	string strVideos = "SELECT * FROM Videos;";

	SqlDataAdapter dadVideoCollection = new SqlDataAdapter(strVideos, cnnVideos);

	cnnVideos.Open();
	cnnVideos.Close();
}

Instead of separately defining a MySqlConnection and a SqlDataAdapter objects, you can directly provide a connection string to the SqlDataAdapter object when declaring it. To do this, you can use the fourth constructor of the SqlDataAdapter class. Its syntax is:

public SqlDataAdapter(String selectCommandText, String selectConnectionString);

The first argument to this constructor is the statement that specifies how data would be read. The second argument is a connection string. Here is an example of declaring a data adapter using this version of the SqlDataAdapter class:

private void Form1_Load(object sender, System.EventArgs e)
{
	string strSelVideos = "SELECT * FROM Videos;";
	string strConVideos = "Data Source=localhost;Database='VideoCollection';Persist Security Info=yes";

	SqlDataAdapter dadVideoCollection = new SqlDataAdapter(strSelVideos, strConVideos);
}

1. In the Toolbox, click Data, then click the SqlDataAdapter button and click the form
2. In the first page of the wizard, read the text and click Next
3. In the second page of the wizard, click the New Connection button
4. In the Connection property page of the Data Link Properties sheet, in the 1 section, select the name of your computer
5. In the 2 section, click the Use Windows NT Persist Security Info radio button
6. In the 3 section, select GCS in the combo box
7. Click Test Connection
    
   
8. Click OK twice
    
   
9. In the second page of the wizard, click Next
10. In the third page, accept the Use SQL Statements radio button and click Next
     
    
11. In the fourth page of the wizard, click the Query Builder button
12. In the Add Table dialog box, click Employees, click Add, and click Close
13. In the Query Builder dialog box, click the check box on the left side of each field in the list except *(AllColumns):
     
    
14. Click OK
     
    
15. In the fourth page of the wizard, click Next
     
    
16. In the fifth page of the wizard, read the messages and click Finish
17. To have an idea of what the SELECT statement looks like, while the sqlDataAdapter1 control is selected under the form, in the Properties window, click the + button of the SelectCommand field and position the mouse on its CommandText field

Before using a data set if your application, you would need a DataSet object. You have two main options. You can declare a DataSet variable. Here is an example:

private void Form1_Load(object sender, System.EventArgs e)
{
	MySqlConnection cnnVideos = new MySqlConnection(
		"Data Source=localhost;Database='VideoCollection';Persist Security Info=yes");

	string strVideos = "SELECT VideoID, VideoTitle, Director, YearReleased, " +
		"VideoLength, Rating FROM Videos;";
	MySqlCommand    cmdVideos = new MySqlCommand(strVideos, cnnVideos);

	SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

	DataSet setVideos = new DataSet("VideoCollection");

	cnnVideos.Open();
	cnnVideos.Close();
}

If you declare your own DataSet variable, you would also eventually have to take care of some detailed operations such as reading from XML, writing to XML, or serializing.

The alternative to declaring your own DataSet class is to ask Microsoft Visual Studio .NET to create a DataSet object for you. To do this, after adding a SqlDataAdapter to your project, you can right-click it and click Generate Dataset... Or, on the main menu, you can click Data . Generate Dataset... This would display the Generate Dataset dialog box. If there was no prior DataSet object in your project, you can click the New radio button and type the desired name for the new DataSet object. The default suggested name is DataSet1:

If there was already at least one DataSet object in your project, the Existing radio button would be selected and the existing name would be suggested. You still have the option of accepting the existing DataSet or creating a new one. After making your choice, you can click OK.

1. To create a DataSet object for the current data adapter, under the form, right-click the sqlDataAdapter1 button and click Generate DataSet...
2. In the Generate Dataset dialog box, accept the New radio button and change the name with dsGCS
    
   
3. Click OK
4. In the Solution Explorer, find the file named dsGCS.xsd and double-click it to examine it

After reading data using a SqlDataAdapter object, you can used it to fill a DataSet object. To support this operation, the SqlDataAdapter class inherits the Fill() method from the DbDataAdapter class. This method is overloaded with 8 versions. The first version of this method uses the following syntax:

public override int Fill(DataSet dataSet);

This version takes as argument a pointer to DataSet. After this call, the dataset argument would be filled with the records of the table read by the data adapter. When calling this method, you can pass it a pointer to DataSet created as described above. Here is an example:

private void Form1_Load(object sender, System.EventArgs e)
{
	MySqlConnection cnnVideos = new MySqlConnection(
		 "Data Source=localhost;Database='VideoCollection';Persist Security Info=yes");


	string strVideos = "SELECT VideoID, VideoTitle, Director, YearReleased, " +
				"VideoLength, Rating FROM Videos;";
	MySqlCommand    cmdVideos = new MySqlCommand(strVideos, cnnVideos);

	SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);
	DataSet setVideos = new DataSet("VideoCollection");

	dadVideoCollection.Fill(setVideos);

	cnnVideos.Open();
	cnnVideos.Close();
}

Once a DataSet contains records, you can use it as a data source for Windows controls. For example, you can use it to populate a DataGrid control. Here is an example:

private void Form1_Load(object sender, System.EventArgs e) { MySqlConnection cnnVideos = new MySqlConnection( "Data Source=localhost;Database='VideoCollection';Persist Security Info=yes"); string strVideos = "SELECT VideoID, VideoTitle, Director, YearReleased, " + "VideoLength, Rating FROM Videos;"; MySqlCommand cmdVideos = new MySqlCommand(strVideos, cnnVideos); SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos); DataSet setVideos = new DataSet("VideoCollection"); dadVideoCollection.Fill(setVideos); this.dataGrid1.DataSource = setVideos; cnnVideos.Open(); cnnVideos.Close(); }

Once a DataSet has received data from a data adapter, it is made aware of the table(s), the column(s), and the record(s) that belong to the SELECT statement of the data adapter. Based on this, you can bind the Windows controls of your application's form to the columns of a DataSet.

1. Display the form. Double-click an occupied area of the form and implement the event as follows:
    

   private void Form1_Load(object sender, System.EventArgs e) { this.sqlDataAdapter1.Fill(this.dsGCS1); }

2. Save all

The tables of a DataSet object are stored in the DataSet.Tables property that is of type DataTableCollection. After filling up a DataSet, if the selection statement of the data adapter includes only one table (in future lessons, we will see that a SELECT statement can include more than one table), as done in the above data adapter, the first table of the statement can be identified with the index of 0 as in DataTableCollection[0]. If the statement includes only one table, only a 0 index can be used. As the DataTableCollection[0] value allows you to identify a table, you can retrieve any table-related information with this information. For example, you can get the object name of the table and specify it as the DataMember property of a DataGrid control. Here is an example:

private void Form1_Load(object sender, System.EventArgs e) { MySqlConnection cnnVideos = new MySqlConnection( "Data Source=localhost;Database='VideoCollection';Persist Security Info=yes"); string strVideos = "SELECT VideoID, VideoTitle, Director, YearReleased, " + "VideoLength, Rating FROM Videos;"; MySqlCommand cmdVideos = new MySqlCommand(strVideos, cnnVideos); cnnVideos.Open(); SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos); DataSet setVideos = new DataSet("VideoCollection"); dadVideoCollection.Fill(setVideos); this.dataGrid1.DataSource = setVideos; this.dataGrid1.DataMember = setVideos.Tables[0].TableName; cnnVideos.Close(); }

Remember that the DataSet.Tables[Index] value gives you access to a table as an object and you can use it as necessary.

1. Display the form and click its DataGrid control
2. In the Properties window, set its DataSource to dsGCS1.Employees
3. Save all

Just as you can use the filled DataSet to locate a table by its index, inside of the identified table, you can also locate a particular column you need. As reviewed in lessons 8 and 9, the columns of a table are stored in the Columns property of a DataTable object and the Columns property is of type DataColumnCollection. Each column inside of the table can be identified by its index. The first column has an index of 0. The second has an index of 1, and so on. Once you have identified a column, you can manipulate it as you see fit. In the following example, since we (behave like we) don't know the name of the second column, a message box displays that information for us:

private void Form1_Load(object sender, System.EventArgs e) { MySqlConnection cnnVideos = new MySqlConnection( "Data Source=localhost;Database='VideoCollection';Persist Security Info=yes"); string strVideos = "SELECT VideoID, VideoTitle, Director, YearReleased, " + "VideoLength, Rating FROM Videos;"; MySqlCommand cmdVideos = new MySqlCommand(strVideos, cnnVideos); cnnVideos.Open(); SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos); DataSet setVideos = new DataSet("VideoCollection"); dadVideoCollection.Fill(setVideos); this.dataGrid1.DataSource = setVideos; this.dataGrid1.DataMember = setVideos.Tables[0].TableName; DataColumn colSecond = setVideos.Tables[0].Columns[1]; MessageBox.Show("The name of the second column is " + colSecond.ColumnName); cnnVideos.Close(); }

When visiting the records of a table using a form of your application, if you provide the means for the user to move from one record to another, if the user gets to a record and changes something in it, that record would not be automatically updated when the user moves to another record. To update a record using the data adapter, the SqlDataAdapter class inherits the Update() method from its parent the DbDataAdapter. The Update() method is overloaded with 5 versions. One of its versions uses the following syntax:

public override int Update(DataSet dataSet);

This version takes a DataSet object as argument. This means that the data adapter would read the information stored in the DataSet and update the database with it. This is probably one of the easiest or fastest means of updating data of a table.

1. Display the form and click its DataGrid control
2. In the Properties window, click the Events button
3. Double-click the right field to CurrentCellChanged and implement its event as follows:
    

   private void dataGrid1_CurrentCellChanged(object sender, System.EventArgs e) { this.sqlDataAdapter1.Update(this.dsGCS1); }

4. Execute the application and, omitting the EmployeeID column, create a few records:
    
   
5. Close the form and return to your programming environment
6. On the form, double-click the Close button and implement its event as follows:
    

   private void btnClose_Click(object sender, System.EventArgs e) { Close(); }

7. Execute the application again and notice that the previously created records were saved
8. Close the form and return to your programming environment

After filling out a DataSet with information from a data adapter, the records of the table(s) included in the selection statement become available from the DataSet object. As reviewed in Lesson 9, the records of a table are stored in the Rows property of the table. We have already seen how to locate a table and how to identify a column. To locate a record, you can use the techniques reviewed in Lesson 9.

Data entry with a data adapter is performed just a few steps once you have properly bound the controls of your form to a DataSet object. To start, you can access the form's BindingContext property to get its BindingContext.Item property. The second version of this property allows you to specify the data source and the table name. After specifying the DataSet object that holds the records and the table that holds the data, you can first call the EndCurrentEdit() method to suspend any record editing that was going on. After this, call the AddNew() to get the table ready for a new record. This allows the user to enter values in the Windows control.