Introduction
When I was reading Binding a Server Control to a Data Source section from ASP.NET Unleashed book, authored by Stephen Walther, I was tempted to find another way around to navigate and control the records on the web page.
It might be a good idea in terms of challenge, but not in terms of practical web development. An old proverb says: There are many ways to go to Rome, someone may take the shortest cut, others probably may take the most common way people do. What I am doing here is just for challenge and fun. So, I take my own way with the risk to be lost on the way (LOL).
Background
Unlike Windows applications that allow the user's computer to directly communicate to the database server through client database drivers, web based applications don't have such a thing. Any request that comes from the user through the browser has to be sent to the web server, then the web server passes that request to the database server. If the database server finds out that the received request is valid (in terms of query syntaxes, permissions, stored procedure names), it will execute that request and then send the result to IIS to be relayed onto the client browser.
In this occasion, I want to use another way to create an interface which will enable web users to navigate and control the records. I want to get rid of Data Binding and basically just use several simple SQL queries for that purpose. In terms of appearance, it won't look different than the other one which uses Data Binding.
The Class We Need
Considering that C# is a programming language that fully supports Object Oriented Programming, I have decided to create one class that consists all necessary methods and properties to enable the user to navigate and control the records. So, that class has to follow these requirements:
- It has an input property that enables the user to move to a specific record based on the given primary key value, and at the same time, it tells the user what the current primary key value is.
public long id
{
get
{
return p_id;
}
set
{
p_id = value;
if(p_id==-1)
p_id = GetMaxID();
}
}
On the code above, you see GetMaxID(). This is a private function that is used to find the location of the current last record on the table. Just imagine, the first time the web page is loaded on the client browser, it has to display the last record from the table. But the last record on the table is always changed, especially when other users add new records or delete the existing ones. So, in order to know which record becomes the last record at the moment, the program has to look for the maximum value of the primary key values that are currently available on the table, as seen in the following code:
private long GetMaxID()
{
OleDbCommand p_com;
OleDbDataReader p_reader;
string p_query;
long p_temp;
p_query = "select max(id) as id from guest";
p_com = new OleDbCommand(p_query,p_con);
p_com.ExecuteNonQuery();
p_reader = p_com.ExecuteReader();
try
{
p_reader.Read();
p_temp = p_reader.GetInt32(0);
}
catch(Exception e)
{
p_temp = -1;
}
p_reader.Close();
return p_temp;
}
The key is on the query. It uses Max, one of MS SQL aggregate functions, to filter all the available primary key values in the table and captures the highest one. As you noticed, on Guest.mdb I have created, there is a table named Guest as well. The primary key of this table is id. It has numeric data type and auto increment. Auto increment means every time a new record is added, the primary key value for that new record is equal to the biggest primary key value of the previous records plus 1.
- It has another four properties that return the primary key values. The first one will return the primary key value of the first record on the table. The second one returns the primary key value of the record that is positioned before the current record. The third one returns the primary key value of the record after the current record. And the last one is the primary key value of the last record on the table.
public long first
{
get
{
return p_first;
}
}
public long before
{
get
{
return p_before;
}
}
public long after
{
get
{
return p_after;
}
}
public long last
{
get
{
return p_last;
}
}
p_first, p_after, p_before and p_last are private variables inside the Guest class. They are exposing other primary key values which then will be used by navigation buttons, including GetMaxID() that has been discussed before. We use GetMaxID() once again here to get the highest primary key value that is currently available on the table that points to the location of the last record on the table.
public void Get()
{
.....
p_first = GetMinID();
p_before = GetBeforeID();
p_after = GetAfterID();
p_last = GetMaxID();
.....
}
Get is one of the Guest methods that passes primary key values into those local variables by assigning those variables to the four functions.
private long GetBeforeID()
{
OleDbCommand p_com;
OleDbDataReader p_reader;
string p_query;
long p_temp;
p_query = "select top 1 id from guest where id < " +
p_id.ToString() + " order by id desc";
p_com = new OleDbCommand(p_query,p_con);
p_com.ExecuteNonQuery();
p_reader = p_com.ExecuteReader();
if(p_reader.Read())
p_temp = p_reader.GetInt32(0);
else
p_temp = -1;
p_reader.Close();
return p_temp;
}
private long GetAfterID()
{
OleDbCommand p_com;
OleDbDataReader p_reader;
string p_query;
long p_temp;
p_query = "select top 1 id from guest where id > " +
p_id.ToString() + " order by id asc";
p_com = new OleDbCommand(p_query,p_con);
p_com.ExecuteNonQuery();
p_reader = p_com.ExecuteReader();
if(p_reader.Read())
p_temp = p_reader.GetInt32(0);
else
p_temp = -1;
p_reader.Close();
return p_temp;
}
private long GetMinID()
{
OleDbCommand p_com;
OleDbDataReader p_reader;
string p_query;
long p_temp;
p_query = "select min(id) as id from guest";
p_com = new OleDbCommand(p_query,p_con);
p_com.ExecuteNonQuery();
p_reader = p_com.ExecuteReader();
if(p_reader.Read())
p_temp = p_reader.GetInt32(0);
else
p_temp = -1;
p_reader.Close();
return p_temp;
}
private long GetMaxID()
{
OleDbCommand p_com;
OleDbDataReader p_reader;
string p_query;
long p_temp;
p_query = "select max(id) as id from guest";
p_com = new OleDbCommand(p_query,p_con);
p_com.ExecuteNonQuery();
p_reader = p_com.ExecuteReader();
try
{
p_reader.Read();
p_temp = p_reader.GetInt32(0);
}
catch(Exception e)
{
p_temp = -1;
}
p_reader.Close();
return p_temp;
}
GetBeforeID is to get the primary key value from the record that is a record before the current record. GetAfterID is to get the primary key value from the record that is a record after the current record. GetMinID is to get the primary key value of the record that is the first record on the table. And I won't discuss GetMaxID, because it has been mentioned above.
As you can notice, what makes them distinct is their SQL queries. GetMinID has the opposite operation to GetMaxID. It looks for the lowest primary key value. Look at Min, it is also one of the MS SQL aggregate functions that does the opposite operation to Max. GetBeforeID is to look for the primary key value of the record before the current record. It selects any primary key value that is less than the current primary value, puts them in descending order, then selects the top one. GetAfterID does the opposite operation of GetBeforeID.
- There are still several other properties needed. They have to return field values from the selected record. For this example, I have created a small MDB file, named as guest.mdb. It consists of one primary key (named as
id) and five regular fields (firstname, lastname, company, address and job). public string firstname
{
get
{
return p_firstname;
}
set
{
p_firstname = value;
}
}
public string lastname
{
get
{
return p_lastname;
}
set
{
p_lastname = value;
}
}
public string company
{
get
{
return p_company;
}
set
{
p_company = value;
}
}
public string address
{
get
{
return p_address;
}
set
{
p_address = value;
}
}
public string job
{
get
{
return p_job;
}
set
{
p_job = value;
}
}
These properties are used to accept the values when the user does an update or insert operation. At the same time, they also provide return values from the selected record when the user navigates the record forward or backward through navigation buttons.
- The
con property is to pass database connection into the class. This database connection is the only connection used by the entire operations inside this class. public OleDbConnection con
{
set
{
p_con = value;
}
}
- The last two properties are used to return an error number and description. It will return 0 for error number and blank string for error description whenever there is nothing wrong during the operation.
public string ed
{
get
{
return p_ed;
}
}
public long en
{
get
{
return p_en;
}
}
Besides properties, the class also need to have several basic methods that have to be exposed to the user for control operation purposes. So through these methods, the user is able to add a new record, update the existing ones or delete them.
Add method. public void Add()
{
OleDbCommand p_com;
string p_query;
try
{
p_query = "insert into guest(firstname," +
"lastname,company,address,job)";
p_query += "values('" + p_firstname
+ "','" + p_lastname + "','";
p_query += p_company + "','" +
p_address + "','" + p_job + "')";
p_com = new OleDbCommand(p_query,p_con);
p_com.ExecuteNonQuery();
p_id = GetMaxID();
p_last = p_id;
p_first = GetMinID();
p_before = GetBeforeID();
p_after = GetAfterID();
}
catch(OleDbException n)
{
p_id = -1;
p_en = 100;
p_ed = n.ToString();
}
}
Update method. public void Update()
{
OleDbCommand p_com;
string p_query;
try
{
p_query = "update guest set firstname='" +
p_firstname + "',lastname='";
p_query += p_lastname + "',company='" +
p_company + "',address='" + p_address;
p_query += "',job='" + p_job + "' where id=" + p_id;
p_com = new OleDbCommand(p_query,p_con);
p_com.ExecuteNonQuery();
}
catch(OleDbException n)
{
p_en = 200;
p_ed = n.ToString();
}
}
Delete method. public void Delete()
{
OleDbCommand p_com;
string p_query;
try
{
p_query = "delete from guest where id=" + p_id;
p_com = new OleDbCommand(p_query,p_con);
p_com.ExecuteNonQuery();
if(GetBeforeID()!= -1)
p_id = GetBeforeID();
else
p_id = GetAfterID();
}
catch(OleDbException n)
{
p_en = 300;
p_ed = n.ToString();
}
}
Navigation Buttons
There are four buttons to let the user navigate the records. First one will drive the user to the first record on the table. Second one drives user to the record before the current record being displayed. Third one takes the user to the next record. The last one will directly pop up the last record on the table. Because each button will drive the user forward or backward through the records, we need to keep the current information and update it later after the user is completely driven to a destined record. In this occasion, I declare several hidden variables to keep those information.
<input type="hidden" id="hdn_current" value="-1" runat="server">
<input type="hidden" id="hdn_first" runat="server">
<input type="hidden" id="hdn_before" runat="server">
<input type="hidden" id="hdn_after" runat="server">
<input type="hidden" id="hdn_last" runat="server">
I set hdn_current value to -1 as a default value. This value is used when the web page is loaded for the first time. It will trigger the program to find the current highest primary key value and assign it into the private variable p_id. This change also occurs on the value of hdn_current. The program then uses this new value to get other values for the rest of the four hidden values that maintain the current state of each navigation button.
When the user clicks the buttons, the program will trigger the function that is related to each button.
...
...
void f_first(Object o,EventArgs e)
{
f_display(Convert.ToInt32(hdn_first.Value));
}
void f_before(Object o,EventArgs e)
{
f_display(Convert.ToInt32(hdn_before.Value));
}
void f_after(Object o,EventArgs e)
{
f_display(Convert.ToInt32(hdn_after.Value));
}
void f_last(Object o,EventArgs e)
{
f_display(Convert.ToInt32(hdn_last.Value));
}
...
...
<asp:Button id="btn_first" Text="<<" Runat="server" Width="40"
Font-Name="Arial" Font-Size="12pt" Font-Bold
OnClick="f_first"/>
<asp:Button id="btn_before" Text="<" Runat="server" Width="40"
Font-Name="Arial" Font-Size="12pt" Font-Bold
OnClick="f_before"/>
<asp:Button id="btn_after" Text=">" Runat="server" Width="40"
Font-Name="Arial" Font-Size="12pt" Font-Bold
OnClick="f_after"/>
<asp:Button id="btn_last" Text=">>" Runat="server" Width="40"
Font-Name="Arial" Font-Size="12pt" Font-Bold
OnClick="f_last"/>
...
f_display is a function that will pop up the information from the selected record based on the given primary key value. Then it will attach the information onto web controls on the web form. It is used by those navigation buttons. Their Click event will cause this function to be executed.
void f_display(long id)
{
Guest g = new Guest(objCon);
g.id = id;
g.Get();
if(g.en==0)
{
txt_firstname.Text = g.firstname;
txt_lastname.Text = g.lastname;
txt_company.Text = g.company;
txt_address.Text = g.address;
f_get_job_index(lst_position,g.job);
hdn_current.Value = g.id.ToString();
hdn_first.Value = g.first.ToString();
hdn_before.Value = g.before.ToString();
hdn_after.Value = g.after.ToString();
hdn_last.Value = g.last.ToString();
btn_update.Enabled = true;
btn_delete.Enabled = true;
if(g.after==-1)
{
btn_after.Enabled = false;
btn_last.Enabled = false;
}
else
{
btn_after.Enabled = true;
btn_last.Enabled = true;
}
if(g.before==-1)
{
btn_before.Enabled = false;
btn_first.Enabled = false;
}
else
{
btn_before.Enabled = true;
btn_first.Enabled = true;
}
}
else
{
txt_firstname.Text = "";
txt_lastname.Text = "";
txt_company.Text = "";
txt_address.Text = "";
lst_position.SelectedIndex = 0;
btn_first.Enabled = false;
btn_before.Enabled = false;
btn_after.Enabled = false;
btn_last.Enabled = false;
hdn_current.Value = "-1";
hdn_first.Value = "-1";
hdn_before.Value = "-1";
hdn_after.Value = "-1";
hdn_last.Value = "-1";
btn_update.Enabled = false;
btn_delete.Enabled = false;
}
}
Control Buttons
There are only three buttons that allow the user to add a new record, update the selected existing record or to delete one. Each one of these buttons is co-related to one of three server side functions. Those functions just need to instantiate the Guest class we have created before and use each exposed method accordingly.
These three functions are exposing error messages as well, in case an error happens. It is very important to give the user a proper message when something goes wrong or the operation is not successful.
void f_add(Object o,EventArgs e)
{
if(btn_add.Text == "Add New")
{
btn_add.Text = "Save";
btn_first.Enabled = false;
btn_before.Enabled = false;
btn_after.Enabled = false;
btn_last.Enabled = false;
btn_update.Text = "Cancel";
btn_update.Enabled = true;
btn_delete.Enabled = false;
txt_firstname.Text = "";
txt_lastname.Text = "";
txt_company.Text = "";
txt_address.Text = "";
lst_position.SelectedIndex = 0;
}
else
{
Guest g = new Guest(objCon);
g.firstname = txt_firstname.Text;
g.lastname = txt_lastname.Text;
g.company = txt_company.Text;
g.address = txt_address.Text;
g.job = lst_position.SelectedItem.Value;
g.Add();
if(g.en!=0) strMsg = g.ed;
f_display(g.id);
btn_add.Text = "Add New";
btn_update.Text = "Update";
btn_update.Enabled = true;
btn_delete.Enabled = true;
}
}
void f_update(Object o,EventArgs e)
{
if(btn_update.Text=="Update")
{
Guest g = new Guest(objCon);
g.id = Convert.ToInt32(hdn_current.Value);
g.firstname = txt_firstname.Text;
g.lastname = txt_lastname.Text;
g.company = txt_company.Text;
g.address = txt_address.Text;
g.job = lst_position.SelectedItem.Value;
g.Update();
if(g.en!=0) strMsg = g.ed;
}
else
{
btn_update.Text="Update";
btn_add.Text="Add New";
f_display(Convert.ToInt32(hdn_current.Value));
}
}
void f_delete(Object o,EventArgs e)
{
Guest g = new Guest(objCon);
g.id = Convert.ToInt32(hdn_current.Value);
g.Delete();
if(g.en==0) f_display(g.id); else strMsg = g.ed;
}
Last Word
Perhaps, many of you will think that the way I do here is not efficient. They may prefer to use Data Binding instead of doing what I just did. I will not deny their reasons. But it does make me feel good to try something different!
