Implementing a 3-Tier architecture with C# – Part 3

The Fill method we have developed so far, executes the Select command on the underlaying server and picks up RowCount records from startRowIndex position. For example, if Select * from Person returnS 20 rows, and startRowIndex is 0, only the first RowCount records will be fetched. This means that the complete result set will be generated on server and will be fetched into an IDataReader, but ADO.NET transmits only RowCount number of records into the DataTable. If the results are too many, that will not be helpful that much, so we have to do something about it.

For doing this, we may define the Fill method virtual and each DAL class may override it to fetch records based on startRowIndex and RowCount from the data base. The 2nd way is to assume that every SelectCommand has two paramerers names startRowIndex and RowCount! This means that we always must supply two int parameters to Fill method. I propose you to have several overloaded Fill methods to use a proper one in different occasions.

Anyway, if we suppose that every SelectCommand executes a stored procedure having two int parameters, we might change the Fill method (or develop an overloaded one) this way:

public void Fill(DataTable table, int startRowIndex, int RowCount)
DbParameter p;

p = ProviderFactory.CreateParameter();
p.ParameterName = “StartIndex”;
p.Value = startRowIndex;

p = ProviderFactory.CreateParameter();
p.ParameterName = “RowCount”;
p.Value = RowCount;
if (table.Rows.Count > 0)

This Fill method adds two parameters to SelectCommand property of the innerAdapter, fills the DataTable by calling the innerAdapter.Fill method. Notice that we must clear up the DataTable because the innerAdapter.Fill method will append new records to the DataTable if any records exists in it.

But how to implement the paging? The answer might differ fro different data bases. However, I will provide a simple method for SQL Server 2005. SQL Server 2005 provides Ranking funtions, i.e. Row_Number() funtion, so that we may easily rank the resulting records of each table. For more information about Row_number function, please check out the SQL Server 2005 Books Online or MSDN.

Anyway, for our sample database, I have written a stored procedure named FetchAllPerson which has two int parameters, called @StartIndex and @RowCount:

create procedure FetchAllPerson
@StartIndex int,
@RowCount int

with NoFilterRecords
Select Row_Number() over (order by Serial) as RIndex , Serial, FirstName, LastName from Person
Select * from NoFilterRecords where RIndex>=@StartIndex and RIndex <@StartIndex+@RowCount
order by 1

This procedure first ranks all records, then fetch only those records positioned in the specific range. Therefore, the innerAdapter.SelectCommand.CommandType must be set to CommandType .StoredProcedure .

innerAdapter.SelectCommand.CommandText = “FetchAllPerson”;
innerAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

Working with identity fields

The value of Identity fields is generated automatically. Therefore, a new row in a DataTable, may not have a value for its Identity fields or their value may not be identical to the mapping record in database after the record is inserted. Thus, we have to reflect the new value of Identity fields to the related row inside the DataTable. For doing this, add a Select statement to the end of Insert statements and seperate them with a semi colon :

innerAdapter.InsertCommand.CommandText = “Insert into Person (Serial, FirstName, LastName) \n” +
“Values (@Serial, @FirstName, @LastName); \n” +
“Select * from Person where Serial = SCOPE_IDENTITY()”;

The Select statement will fetch the new record from database and reflect any changes to the inserted row. To make this method work, we must set the UpdatedRowSource property to FirstReturnedRecord:

innerAdapter.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.FirstReturnedRecord;

This may be done in the base DAL class. This setting will fetch one record and will synchronize the inserted row with the data base. The SCOPE_IDENTITY() funtion returns the last identity value assigned to a field. This works in SQL SERVER only and you must perform a similar task for other data bases.

Ok this is all about the Data Access Layer. We will discuss about the business layer on the next post.


One thought on “Implementing a 3-Tier architecture with C# – Part 3

  1. why oh why are you passing a datatable as a parameter to be filled and returning void… This code sucks so much a black hole formed…

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s