Data Binding

 

 

Data Binding (making controls data aware) allows your applications to connect to databases.  There are two different means of connecting to databases, the first of which is through SQL and the second is OLE.  The SQL method is used when you need to connect to a SQL server and the OLE method is used to connect to all the other types of databases.  This means that in order for us to connect to an Access database we will need to use OLE.  Download this database, you will be using it in this lab.

 

Connecting to a Database:

Your means of accessing all of the Database controls needed is via the “Data” panel of the toolbox.

 

To connect to our database you will need three key non-visual controls (again this is for the OLE connection type).

·        oleDbDataAdapter

·        oleDbConnection

·        DataSet

 

 

 

The details of what is going on behind the scenes of the connection to the database is beyond the scope of this lab.  For now just go through this lab to learn the steps to successfully set up a connection to your database and create data bound controls.

 

Step 1: Add the oleDbDataAdapter

 

·        Double click the oleDbDataAdapter to add the control to the project

·        When the Data Adapter Configuration Wizard opens, click Next

·        Click the New Connection button

·        On the Provider tab, select Jet 4.0

·        On the Connection tab, browse to your Access Database

·        On the Connection tab, click the Test Connection button which confirms that the connection was successful

·        Click Next until you reach the Generate the SQL Statements section

·        On the Generate the SQL Statements section click the Query Builder button

·        Use the Query Builder to select the tables and fields you want in your application.

·        Click Next until you can click Finish

·        Click Finish and the creation of the oleDbDataApdapter creation will be complete and an oleDbConnection will be automatically created for you

 

Step 2: Create the DataSet

·        Click the Generate DataSet link in the oleDbDataAdapter’s properties area

·        Confirm the creation of a new DataSet by clicking Ok (this will take care of linking the DataSet with the DataAdapter)

 

 

Step 3: Fill the DataSet

This step is what establishes the connection, fills the DataSet with the data from the DataAdapter, and closes the connection.  To perform this step, you would need to type in the following lines of code within the Load method of your main Form.

 

oleDbConnection1.Open(); oleDbDataAdapter1.Fill(dataSet11); oleDbConnection1.Close();

 

This opens a connection to the Data source (in this case our Access Database), fills the DataSet with the data we obtain through our connection with the data source, and closes the connection with our data source once the data is pulled into the application.

 

Setting DataBindings Properties of C# Controls:

 

Every control in C# can be data-aware (can take in and respond to data). By using the Databindings property for our controls we can set up which field of our DataSet to display in what control.  This means that it is possible to have many different controls on our forms displaying different pieces of the data from our DataSet. Now, it is time to move on to place the visible controls on our form and bind them to their data source.  For this project, you will need six labels (three to display the information from our DataSet and three to label the data that is being displayed).  You will also need to place two textboxes on the form. These will be the means for the user to modify the data in the DataSet, and to insert a new record in the DataSet.  You will also need seven buttons on the form. These will be your Next, Previous, First, Last, Insert, Delete, and Modify buttons (common buttons on a database application).  Now that we have our controls, we can bind them to our DataSet.  To do this, we will need to set the DataBindings properties for our controls (under which there are two possible links to our DataSet).  The Text link will allow the text from our DataSet to be displayed, while the Tag link is hidden.  For now, do not worry about the details of what each link does. Under the DataBindings property (for the controls that will display the data form the DataSet), set the Text option to point to the field of the DataSet you want the control to link to. 

Once you have everything set up correctly, the fields in your DataSet should be displayed in the drop-down menu for the Text DataBindings property for the controls.

 

Navigating Records:

 

Now that our project and database are connected through the first three controls added to the project, and our visible controls are bound to the fields of the DataSet, it is time to set up the navigation of the DataSet.  Every DataSet contains the following information:

 

·        Position (current record)

o       This is how the controls you bind to the DataSet know what record’s data to display. It starts at zero just like arrays do by default)

·        Count

o       This tells your application how many records are in the DataSet

 

The way we can move around in the DataSet is by updating the position.  This means when we want to move forward in the DataSet we add one to our position, and when we want to move back we subtract one.  The information about our DataSet is held in BindingContext which is a two dimensional array established at the time we connected to the database.  The first dimension of BindingContext contains the name of the DataSet object, and the second dimension contains the table that is in the DataSet.  When you refer to BindingContext, it will return an object with the properties of position and count. In order to change your position within the DataSet, you will need to go through BindingContext.

 

Examples:

 

Next:

 

this.BindingContext[dataSet11, "MyMovies"].Position += 1;

 

This increments your position in the DataSet by one and moves you to the next record in the DataSet.

 

Previous:

 

this.BindingContext[dataSet11, "MyMovies"].Position = this.BindingContext[dataSet11, "MyMovies"].Position -1;

 

This decrements your position in the DataSet by one and moves you back to the previous record in the DataSet.

 

First:

 

this.BindingContext[dataSet11, "MyMovies"].Position = 0;

 

This sets your position to 0, which corresponds to the first record in the DataSet.

 

Last:

 

this.BindingContext[dataSet11, "MyMovies"].Position = this.BindingContext[dataSet11, "MyMovies"].Count-1;

 

This sets your position to Count-1 which correspond to the last record in the DataSet.

 

While you are navigating through the DataSet you will not need to set data bounds since BindingContext establishes them for you.  You will not be able to go below position 0 or beyond position Count-1.

 

Manipulating Records:

 

When you write a database application, you are not limited to reading in data. You are also able to modify, insert, and delete data.

 

Inserting:

When you want to insert data into a database through your application, there are a few things that you will need to do.  The first thing is to read in the data from the user, which can be done through the use of textboxes, drop-downs, etc.  Once you have read in the data, you will need to call the Insert method which was automatically generated for you when you went through the DataAdapter Wizard. The wizard generated template functions for insert, delete, and  modify.  After you call the Insert method, you need to update the DataAdapter which writes the data to the database. After you update the DataAdapter you need to have the DataSet accept the changes (refreshes itself form the DataAdapter's new data).

 

Insert Method Call:

 

this.<DataSet's Name>.<table name>.Add<table name>Row(X1, X2);

 

When you call the AddRow function for your DataSet, keep in mind that that the parameters you pass in must match the data types of the fields your DataSet table holds (these data types are dependant on the Database).

 

Updating the DataAdapter:

 

this.<oleDataAdapter's Name>.Update(<DataSet Object's name>, <table name>);

 

After you have completed the insertion, you need to to tell the DataAdapter to update itself (send the data to the database).  When you call the Update method, you must specify which DataSet and which of the DataSet's tables have been changed via parameters to the Update function.

 

this.<DataSet's Name>.AcceptChanges();

 

After you call the Update function, you must then accept the changes. This is done by calling the AcceptChanges function (there are no parameters for this function).  When you call the AcceptChanges function, you are telling the DataSet to update itself to it contain the new record.

 

Now that you know how to insert a record into a DataSet, let’s incorporate all of the steps.  The below code performs each of the above steps to insert a new record into the DataSet connected to our database.

 

this.dataSet11.MyMovies.AddMyMoviesRow(this.movieNameUpdate.Text, this.whoHasUpdate.Text); this.oleDbDataAdapter1.Update(this.dataSet11, "MyMovies"); this.dataSet11.AcceptChanges();

 

Modifying:

 

From time to time, the templates for the insert, delete, and update functions that were created when you set up the connection to the data source, are not sufficient to complete the necessary actions on the table.  In these cases, you will need to write your own SQL statements for these actions.  One such action is when modifying the data contained within a record in our DataSet.  To perform this modification on the data, we will need to set this.oleDbDataAdapter1.UpdateCommand.CommandText equal to a new SQL statement.

 

this.oleDbDataAdapter1.UpdateCommand.CommandText = "UPDATE MyMovies SET " + "MovieName ='" + this.movieNameUpdate.Text + "', WhoHas='" + this.whoHasUpdate.Text + "' WHERE MovieNumber=" + this.MovieNumb.Text;

 

As you can see in the SQL statement, when we execute the Update command, it will update the MyMovies table only were MovieNumber is equal to the value stored in the label displaying the movie number.  In the instance where a new value was not provided for all the modifiable fields, we would just take in the value that was provided, and modify just the field the value represents.  The below code shows how the command should be built under these types of conditions:

 

if (this.movieNameUpdate.Text !="" && this.whoHasUpdate.Text != "") { this.oleDbDataAdapter1.UpdateCommand.CommandText = "UPDATE MyMovies SET " +      "MovieName ='" + this.movieNameUpdate.Text +           "', WhoHas='" + this.whoHasUpdate.Text +           "' WHERE MovieNumber=" + this.MovieNumb.Text; } else if (this.movieNameUpdate.Text != "") {      this.oleDbDataAdapter1.UpdateCommand.CommandText = "UPDATE MyMovies SET " +           "MovieName ='" + this.movieNameUpdate.Text +           "' WHERE MovieNumber=" + this.MovieNumb.Text; } else {      this.oleDbDataAdapter1.UpdateCommand.CommandText = "UPDATE MyMovies SET " +           "WhoHas='" + this.whoHasUpdate.Text +           "' WHERE MovieNumber=" + this.MovieNumb.Text; }

 

Now that you know how the code should look when modifying the Update command, we can expand upon the code to show all of the necessary steps for modifying a record in the DataSet (as shown below).

 

this.oleDbConnection1.Open (); if (this.movieNameUpdate.Text !="" && this.whoHasUpdate.Text != "") { this.oleDbDataAdapter1.UpdateCommand.CommandText = "UPDATE MyMovies SET " +      "MovieName ='" + this.movieNameUpdate.Text +           "', WhoHas='" + this.whoHasUpdate.Text +           "' WHERE MovieNumber=" + this.MovieNumb.Text; } else if (this.movieNameUpdate.Text != "") {      this.oleDbDataAdapter1.UpdateCommand.CommandText = "UPDATE MyMovies SET " +           "MovieName ='" + this.movieNameUpdate.Text +           "' WHERE MovieNumber=" + this.MovieNumb.Text; } else {      this.oleDbDataAdapter1.UpdateCommand.CommandText = "UPDATE MyMovies SET " +           "WhoHas='" + this.whoHasUpdate.Text +           "' WHERE MovieNumber=" + this.MovieNumb.Text; }  oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery(); oleDbDataAdapter1.Fill(dataSet11); oleDbConnection1.Close();

 

As you can see, we first need to reopen the connection to the table (C# requires us to do this when we are modifying an SQL statement that was already created).  After we reopen the connection to the database, we can proceed to modify the SQL statement.  After we have modified the SQL statement, we can execute the modified Update method (which is done by the highlighted line of code).  After you execute the SQL statement, you need to refill the DataSet with the up-to-date data that is in the table and then close the connection to the Database.

 

Deleting:

 

In order to delete a record from the DataSet, you need to use the BindingContext object.  As stated at the beginning, it is the object that holds the information for your position in the DataSet. To perform the deletion, you need to tell the BindingContext the position of the record you need to remove.  You must then call the oleDbDataAdapter's Update method and tell the DataSet to accept the changes.  Keep in mind that when you delete a record from an Access database and are using an auto number as the primary key, you will never be able to use that number again (this is a relic of the database, not a problem caused by your application).  The steps that need to be taken to delete a record from the DataSet can be seen in the below code:

 

this.BindingContext[dataSet11, "MyMovies"].RemoveAt(this.BindingContext[dataSet11, "MyMovies"].Position); this.oleDbDataAdapter1.Update (this.dataSet11, "MyMovies"); this.dataSet11.AcceptChanges();