In every case binding is performed using collections type. But the normal collection types in RDBMS operations are DataSet & DataReader. The problems with these items are you have no control on your items/collections, especially in a well structured/well designed layer based architect. So this article is mainly looking for data access through various user defined layers and my experience is that you always use your own defined collections to handle RDBMS data rather than built in ADO.NET collections of DatSet, DataReader etc..., if you are using user defined classes for data access operations.
Ok I already mentioned that I used the Employees table of NorthWind (a built in database in SQL Server). I am going to fetch certain fields from Employees table and then store those values to my own defined collection and later bind it to a DataGrid. Open a new C# web project and add 2 classes to it with names EmployeeInfo and EmployeeList.
EmployeeInfo class is the storing area of a single row and each field of the particular single row will be stored in the corresponding properties. So you should be care about to define as much of properties as the number of fields in EmployeeInfo. My plan is to display employeeid, lastname, firstname, title, birthdate, hiredate and address fields and defined that much of properties. But in real world we are not handling a single row but a collection of rows. For that we want to save each single row stored in EmployeeInfo class in a form of collection. EmployeeList is the class for that purpose. So one core part is that EmployeeList type contains various EmployeeInfo types which (EmployeeInfo) stores various Employees row. So ultimately EmployeeList is our own defined collection and this is the class will bind to the DataGrid control. If we want to make EmployeeList class as a collection, then we should extend it from other built in collection types. I used the CollectionBase class which is defined in the name space System.Collections. Because of the sub class of CollectionBase our own EmployeeList class can use various methods, properties of the parent CollectionBase. But I used only a single one and override it in our own EmployeeList as follows.
public int Add (EmployeeInfo employeeInfo)
{
List.Add (employeeInfo);
return 0;
}
We can use the above method to add a particular EmployeeInfo type to its container EmployeeList. So in effect when each EmployeeInfo type is adding to EmployeeList using method Add(), a new row of Employees table is creating. Because you know each EmployeeInfo contains a single row of Employees table.
In the form load event just fetch the required fields first in a DatSet and then create EmployeeInfo for each row in that DatSet like below
EmployeeList employeeList = new EmployeeList();
foreach(DataRow row in dataSetEmployees.Tables["Employees"].Rows)
{
//This should be in the loop as each row want to be a stored as a separate object
employeeInfo = new EmployeeInfo();
employeeInfo.EmployeeID = int.Parse( row["employeeid"].ToString() );
employeeInfo.LastName = row["lastname"].ToString();
employeeInfo.FirstName = row["firstname"].ToString();
employeeInfo.Title = row["title"].ToString();
employeeInfo.BirthDate = string.Format(((DateTime)row["birthdate"]).ToString(),"DD/mm/yyyy");
employeeInfo.HireDate = string.Format(((DateTime)row["hiredate"]).ToString() ,"DD/mm/yyyy");
employeeInfo.Address = row["address"].ToString();
//Add the present row with it's values to the employeelist collection
employeeList.Add(employeeInfo);
}
Hi nothing here, just looping through each field of the particular DataSet and assign the value of each field to that specific property in the EmployeeInfo. After assigning all fields of a particular row, just stores that row i.e. that EmployeeInfo in EmployeeList using Add() method. Then the next rows so on. This process will continues until all fields of all rows of that particular DatSet is stored in EmployeeList. After EmployeeList is filled with data, you can just bind it to the DataGrid as bellow
employeeDataGrid.DataSource = <BuildEmployeeList Type>
employeeDataGrid.DataBind();
So all our items are creating and handling by our won, isn't it a good thing. Following this way you can try other tables also. For e.g. Customers, where you should define a CustomerInfo and CustomerList. Creating Info and List for each table may feel time consuming but after your development just enjoy the relaxation of maintenance and additional modification. You can also try other database operations like Inert, Update, Delete etc... with the particular Info and List types of each table.