Data binding in ADO.NET - Part 4

Naimishforu
Posted by in ADO.NET category on for Beginner level | Points: 250 | Views : 10440 red flag
Rating: 5 out of 5  
 1 vote(s)

We will look into the overview of Data binding in ADO.NET
The DataGrid in .NET is a new control that allows you to view the data in a dataset. The DataGrid object is bound to the dataset and automatically displays the data it contains. This means you don't have to manually request the information and write it to a list box.

The following program creates a simple DataGrid, which will view the Customers table of the EasyNomad database.

The dataset in this program is populated from the database, just as if the data was to be displayed in a list box.

But then the dataset is bound to the DataGrid through the DataGrid's DataSource property.

// create the data set command object // and the DataSet OleDbDataAdapter DataAdapter = new OleDbDataAdapter(command, connection); DataSet CustomerDataSet = new DataSet(); // fill the data set object DataAdapter.Fill(CustomerDataSet,"Customers"); // Get the one table from the DataSet DataTable dataTable = CustomerDataSet.Tables[0]; // bind the DataSet to the grid CustomersDataGrid.DataSource = CustomerDataSet;

Visual Studio completes much of the code required to create a basic DataGrid automatically when you create the form and add a DataGrid control to it. You need to change only basic properties like the name of the DataGrid. 

When you run the program, you must expand the tables contained in the DataGrid to see the information.

You can customize the dataset by explicitly creating the OleDbConnection, OleDbCommand, OleDbDataAdapter and DataSet objects. This gives you more control over their properties. 

First you need to create four new class members.

private System.Windows.Forms.DataGrid CustomersDataGrid /// <summary> /// Required designer variable. /// </summary> private System.CcomponentModel._ Container components = null; // declare class members private System.Data.OleDb.OleDbConnection CustomersConnection; private System.Data._ OleDb.OleDbCommand CustomersCommand; private System.Data.OleDb.OleDbDataAdapter CustomerDataAdapter; private System.Data.DataSet CustomersDataSet;


You explicitly create the connection object that links the dataset to the database.

This connection is then opened using the Open method. Because you have opened this connection in the program, you will be able to reuse it if necessary.

// connect to EasyNomal Access database string connection = "provider=Microsoft.JET.OLEDB.4.0; " + "data source = c:\\EasyNomad.mdb"; CustomersConnection = new System.Data.OleDb.OleDbConnection(connection); CustomersConnection.Open(); CustomersDataSet = new System.Data.DataSet(); CustomersCommand =_ new System.Data.OleDb.OleDbCommand(); CustomersCommand.Connection =_ CustomersConnection;

The following code creates the CustomersCommand object. This object uses its Connection property to specify the connection. It specifies what data to select by setting the CommandText property rather than simply defining the strings that would be passed to the DataAdapter object.

When the DataAdapter is created, it uses the CustomersCommand object that has just been created to set the SelectCommand property.

CustomersConnection.Open(); CustomersDataSet = new System.Data.DataSet(); CustomersCommand_ = new System.Data.OleDb.OleDbCommand(); CustomersCommand.Connection_ = CustomersConnection; CustomersCommand.CommandText_ = "Select * from Customers"; CustomersDataAdaptor = new System.Data.OleDb.OleDbDataAdapter(); CustomersDataAdaptor.SelectCommand_ = CustomersCommand; CustomersDataAdaptor.TableMappings.Add_ ("Table", "Customers"); CustomersDataAdaptor.Fill(CustomersDataSet);

The tables in a database are usually all related to each other. The first column in one table, the key to that table, often appears as a secondary column in another table, which is used to create relationships between the two tables.

The dataset extracted from the database will often contain more than one table, so you can create a DataGrid that will be able to display these tables and the relationships between them.

namespace CombiningDataTables { /// ///Summary description for Form1. public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.DataGrid CustomersDataGrid; /// Required designer variable. /// private System.ComponentModel.Container components = null; } }
The following program will select the Customers and the Bookings tables from the EasyNomad database, allowing you to view the relationships between the two.

This section of code selects the Customers table from the database and adds it into the CustBookDataSet.

CustomersCommand = new System.Data.OleDb.OleDbCommand(); CustomersCommand.Connection = CustomersConnection; CustomersCommand.CommandText = "Select * from Customers"; CustomersDataAdaptor = new System.Data.OleDb.OleDbDataAdapter(); CustomersDataAdaptor.SelectCommand = CustomersCommand; CustomersDataAdaptor.TableMappings.Add("Table","Customers"); CustomersDataAdaptor.Fill(CustBookDataSet);

This section of code selects the Bookings table from the database and also adds it into the CustBookDataSet.

BookingsCommand = new System.Data.OleDb.OleDbCommand(); BookingsCommand.Connection = CustomersConnection; BookingsCommand.CommandText = "Select * from Bookings"; BookingsDataAdaptor = new System.Data.OleDb.OleDbDataAdapter(); BookingsDataAdaptor.SelectCommand = BookingsCommand; BookingsDataAdaptor.TableMappings.Add("Table","Bookings"); BookingsDataAdaptor.Fill(CustBookDataSet);
You now have a single dataset called CustBookDataSet containing the data from the Customers and the Bookings tables in the EasyNomad database.

A one-to-many relationship exists between the CustomerID field of the Customers table and the CustomerID field of the Bookings table. 

Now you need to model this relationship. The relationship between the two tables is encapsulated in the dataRelation object, which you declare here.

Note

A one-to-many relationship means that each customer can have one or more bookings.

The relationship between the CustomerID fields of the two tables is modeled by two dataColumn objects, created in these lines. Each of these dataColumn objects is assigned to the CustomerID column in the dataset. The dataRelation object can now be constructed using the two dataColumn objects. You must add the dataRelation object to the Relations collection in the dataset as a new object. You need to create a DataViewManager object and set the DataSource property to that view.

Note

The DataViewManager class contains a default DataViewSettingCollection for each DataTable in a DataSet.

Finally, you must decide which table in the dataset is the parent table and set it in the DataMember property. As each individual customer can have more than one booking, you set the DataMember property to Customers.

//establish the relationship between the tables System.Data.DataRelation dataRelation; System.Data.DataColumn dataColumn1; System.Data.DataColumn dataColumn2; dataColumn1 = CustBookDataSet.Tables["Customers"].Columns_ ["CustomerID"]; dataColumn2 = CustBookDataSet.Tables["Bookings"].Columns_ ["CustomerID"]; dataRelation = new System.Data.DataRelation("CustomersToBookings", dataColumn1,dataColumn2); //add the relation object to the data set CustBookDataSet.Relations.Add(dataRelation); //;set up the grid's view and member data and display it DataViewManager DataSetView = CustBookDataSet.DefaultViewManager; CustomersDataGrid.DataSource = DataSetView; CustomersDataGrid.DataMember= "Customers";

The customer with a CustomerID of 10 has made many bookings.

Thanks and Have Fun!!!!!

Page copy protected against web site content infringement by Copyscape

About the Author

Naimishforu
Full Name: Naimish Pandya
Member Level: Bronze
Member Status: Member,MVP
Member Since: 1/22/2011 7:38:35 AM
Country: India
Thanks, Naimish http://dotnetinterview.zxq.net/ http://naimishpandya.blogspot.in/
http://naimishpandya.blogspot.in/

Login to vote for this post.

Comments or Responses

Posted by: Madhu.b.rokkam on: 2/25/2011 | Points: 25
good one..:)
Posted by: Tripati_tutu on: 2/25/2011 | Points: 25
Good article and it is helpful for us...
Posted by: Naimishforu on: 2/25/2011 | Points: 25
Thanks Madhu and Tripati :)
Posted by: Arefin on: 8/28/2011 | Points: 25
good article about joining. keep it up.

Login to post response

Comment using Facebook(Author doesn't get notification)