Discussion Forum Board Beta2
Add Comment| Download File | SDK |
| forumbeta2.zip (39kb) | Beta2 |
Introduction
This example updates the previous forum example that I had written for beta1.
A lot has changed since then, even though from outside the forum has the same
looks internally a lot has changed! Due to this I have taken the trouble to elaborate
this example into easy to understand pieces. This example will teach you to
work with OleDb.NET, Databind DataGrid's to DataReaders, Custom Paging in
DataGrid's and Templates in DataGrid's. This is a long and detailed one so get
your jugs of coffee ready :)
Explanation
This example consists of 3 pages.
1) forum.aspx - The Main Forum page. It lists all the topics in a
DataGrid object with Custom Paging enabled. It also contains a form to post new
topics.
2) reply.aspx - This page is called from the forum page when you
click on any topic. It contains the details of the topic as well as
all replies made to the topic. This page contains the form to reply
to the topic.
3) postmessage.aspx - This page is called whenever any form is posted.
It parses and stores the posted data into the Database.
Requirements
1) .NET SDK beta 2 (Note: This example may not work with future
versions of the SDK).
2) Ms Access 2000 (Optional, only if you want to edit the database).
3) ASP.NET support on your web server.
Database Structure
| Table Name - newpost / This table will contain the new topics posted. | ||
| Column Name | Data Type | Description |
| postid | AutoNumber | The primary key field. The unique id for each new topic. |
| name | Text | The name of the author of the message. |
| Text | E-mail address of the author. | |
| subject | Text | Subject of the message. |
| ip | Text | IP address of the author. |
| dt | Text | Date / Time of the Post |
| message | Memo | Message posted. |
| replies | Integer | Number of replies to the post (if any) |
| views | Integer | Number of times the message has been viewed. |
| Table Name- reply / This table will contain the replies made to the new topics. | ||
| Column Name | Data Type | Description |
| replyid | AutoNumber | The primary key field. The unique id for each reply. |
| name | Text | Name of the author. |
| Text | E-mail address of the author. | |
| subject | Text | Subject of the post |
| ip | Text | IP of the author. |
| dt | Text | Date / Time of post. |
| message | Memo | Message posted. |
| postid | Integer | postid from the "newpost" table for which this message is a reply. |
Code
1) forum.aspx :- The main
forum
page. (Only relevant pieces shown)
This page is one of the most important pages, read this code carefully!
<%@ Page Language="C#" EnableSessionState="False" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System" %>
<html><head>
<title>Welcome to My Forum!</title>
<script language="C#" runat="server" >
int startIndex;
void Page_Load(Object Src, EventArgs E)
{
//check if the page is loaded for the first time.
if(!IsPostBack)
{
//Call the Binding Method
Binding() ;
}
}
.....
......
</script>
|
Above, after importing the necessary namespaces, I have declared a global Integer variable 'startIndex' whose work we shall see a bit later. Next I declare the 'Page_Load' method, this method is an Event Handler of the Page class and gets fired automatically every time the page gets loaded. Within this method I check if the page is being loaded for the first time by checking the 'IsPostBack' property of the Page class. If the Page is called for the first time, 'IsPostBack' returns a 'false' and if the page is reloaded, it returns a 'true'. Since I want the DataGrid to be databound from the 'Page_Load' method only when the Page is first loaded, I call the 'Binding' method (defined a bit later) within the 'if' block.
public void DataGrid_Updt(object sender, DataGridPageChangedEventArgs e)
{
//store the next page index
DataGrid1.CurrentPageIndex = e.NewPageIndex;
//update the variable
startIndex = (DataGrid1.CurrentPageIndex * DataGrid1.PageSize);
//call the method to data bind the DataGrid
Binding();
}
|
The 'DataGrid_Updt' method defined above gets called every time the DataGrid is Paged. Generally in an active forum hundreds topics are listed everyday. Displaying all the topics to every user is a waste of bandwidth, users time, as well as the time taken to load the page will be very very high! So we use Paging support in the DataGrid object, whereby only fixed number of records are displayed on each page and a link to other pages like (1,2,3...) is given below the grid. This saves users time and bandwidth.
When the user changes the page, 'PageIndexChanged' Event is fired by the DataGrid. In my example the above method is an Event Handler for this Event. You will also note that this method has one different argument of the type 'DataGridPageChangedEventArgs'. This argument contains some important information regarding the DataGrid Page. I use the 'NewPageIndex' property to find out the Page number the user has clicked on the DataGrid. So if the user clicks on the third page, this variable will return the value '3'. I set the 'CurrentPageIndex' property of the DataGrid that denotes the current page being displayed to the value returned by the 'NewPageIndex'.
The 'startIndex' variable which I had declared as global (remember!) is set to the value obtained by multiplying the 'CurrentPageIndex' and the 'PageSize' of the DataGrid. 'CurrentPageIndex' denotes the current page number being viewed while the 'PageSize' property denotes the total number of records shown per page. So if the user click's on Page 3, then we store (3*20) = 60 in the variable 'startIndex' (Note: I have set the 'PageSize' to 20, so I am using the value 20 above). Why do I do this? Answer to this a bit later....
Next, the 'Binding' method is given a call. As you know, this method will databind the DataGrid.
One point I would like to make here is, that when the user click's on a new page number to change the page, the page gets refreshed and the 'Page_Load' method is called first, why? (I have answered the question above, read carefully) and then the 'DataGrid_Updt' method is called. If within the 'Page_Load' method I would not have put a check on 'IsPostBack' property and directly declared the 'Binding' method. The code would still work, but it would be a waste of resources since in the 'DataGrid_Updt' too calls the same 'Binding' method after setting some different arguments and the DataGrid would get re-binded twice! This is a common error made my many programmers (I too used to make it!), so always remember to check for 'IsPostBack' property in the 'Page_Load' method before DataBinding while using DataGrid's !
public void Binding()
{
//Connection string
string strConn=@"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=";
strConn+=Server.MapPath(".\\db\\board.mdb");
//Make a new connection
OleDbConnection myConn = new OleDbConnection(strConn) ;
string strCom;
//Check is the Page is loaded for the first time
if(!IsPostBack)
{
//Set the variable to its default value
startIndex =0;
//SQL statement to choose the last record
strCom = "SELECT TOP 1 postid FROM newpost ORDER BY postid DESC" ;
//Create a Command
OleDbCommand countCommand = new OleDbCommand(strCom, myConn);
//Open the connection
myConn.Open();
//Create a DataReader
OleDbDataReader countReader = countCommand.ExecuteReader();
//Check if the DataReader has any records
if(!countReader.Read())
{
//Set VirtualItemCount to 0 since no records exist
DataGrid1.VirtualItemCount=0;
}
else
{
//Set VirtualItemCount to the total number of records in the table
DataGrid1.VirtualItemCount= countReader.GetInt32(0);
}
//Close the connection
myConn.Close();
}
//SQL statement to choose just 20 records in Descending order
strCom = "SELECT TOP 20 postid ,subject ,name ,replies ,views ,dt FROM newpost ";
strCom+= "WHERE postid < "+(DataGrid1.VirtualItemCount-(startIndex-1) );
strCom+=" ORDER BY postid DESC" ;
OleDbCommand forumCommand = new OleDbCommand(strCom, myConn);
myConn.Open();
OleDbDataReader forumReader = forumCommand.ExecuteReader();
//Set the DataSource
DataGrid1.DataSource = forumReader ;
//DataBind the grid
DataGrid1.DataBind();
myConn.Close();
}
|
Before diving into the above code, I would like to make a few comments first.
DataSet vs DataReader
When we started programming with .NET SDK beta1, we were introduced to the
idea of DataSet's. A DataSet object is in-memory representation of the
database, but it's always disconnected from the database. Also the DataSet
object does not have any idea of the Data Source from which the Data has been
populated into it. Its neutral to the source of the data, and data can be either fed
into it through DataAdapters (DataSetCommand in beta1) or can be programmatically
entered. The DataSet object is so powerful and easy to use that everyone
started using it instantly.
When .NET SDK beta2 was released, Microsoft has clearly indicated that, even
though DataSet's are powerful and very customizable, they should be used with
caution! Since DataSet are created in-memory, they have a high memory
requirement, and bring down the performance of your Web Application. As
against DataSet, a DataReader object represents a fast, forward-only record
cursor, which can be used to only read data in a systematic manner. Since the
DataReader reads one record at a time its memory requirement is very low and
its speed is very high.
Also note that when a DataSet object is populated through a DataAdapter,
internally a DataReader is created to populate the DataSet. So whenever you
are using a DataSet, a DataReader object is always created! Hence DataReader's
offer a better performance for your Applications and they should be used
wherever the need is to just display the Data without much customization.
Hence I too will be using a DataReader in this example!
DataReader and DataGrid Paging
Above I have explained the need and use of Paging in DataGrid's. While data
binding DataReader's to DataGrid's with just Paging enabled (through the 'AllowPaging'
property) you get a error, Why?
The reason for this is that a DataReader does not implement the 'ICollection'
Interface. The 'ICollection' Interface defines a property called 'Count' which
helps the DataGrid object know at runtime the number of records present and
hence it can compute the number of pages to show. i.e. If the DataGrid object
knows that there are 200 records and the 'PageSize' (number of records
displayed per page) is set to 20 then it will draw links for pages 1 to 10
(200/20=10, right !!! :) ).
You might think that while using DataSet's you don't get such a error even
though DataSet's also don't implement the 'ICollection' Interface? (Rub your
eye's hard!!) While using DataSet's we never DataBind to the DataSet, we
always data bind to a DataView object, remember!! And the DataView object
implements the 'ICollection' Interface so we have never faced this problem!
So what to do now? Well we start of by setting the 'AllowPaging' and 'AllowCustomPaging'
properties of the DataGrid to 'true' indicating that we want Paging support in
our grid, but we will manually handle the Paging code. We also wire-up the 'PagingIndexChanged'
event through which we shall manually handle paging of the DataGrid.
Next we have to set the 'VirtualItemCount' property of the DataGrid programmatically
(it cannot be set directly with the DataGrid declaration) to the total number of records present in the Table.
One advantage this approach gives us is that since actually only some records
are shown per page and a fresh set of records are retrieved every time the
page changes, in our custom paging code we can set the SQL Query in such a way
that it only retrieves the data that will be shown in the DataGrid. This way
we save a lot of traffic on our network!
I hope you have been able to gasp some of the changes from beta1 to beta2.
Now time to kick some heavy code! Re-fill your coffee cups and go through the
above code of the 'Binding' method.
Let's start... First I declare a 'OleDbConnection' variable and then I have
put another If-Block which checks if the Page is loaded for the first time.
If '!IsPostBack' (not PostBack) evaluates to true, that means that the page
is being loaded from the first time. Within this If-Block, I initialize the
global integer variable 'startIndex' to zero, its default value, then I have
written a SQL statement, which will return only the 'postid' column of the
last record in the database. Why am I doing this? As I had mentioned in the
"DataReader and DataGrid Paging" section, that in order to enable
custom paging in a DataReader bound DataGrid you have to set the 'VirtualItemCount'
property of the DataGrid to the total number or records. If you check the
schema of the 'newpost' table you will find that the 'postid' column is of the
type 'Auto Number' and contains sequential numbers in increasing order. So if
we get the 'postid' of the last record, we can get the total number of records
present in the database, since the 'postid' of the last record will be equal
to the number of records in the table.
One possible problem to this approach might be that if in the future someone
deletes a record from the table for moderation purpose, then we will have
change this approach. Since this feature is yet not implemented, I will
continue with this approach.
Finally, a OleDBCommand and OleDbDataReader objects are made and the command
is executed. Following it is one more If-block. This block check's if the 'Read' method of the DataReader returned any records, if so then a
true is
returned. I have implemented this check since the first time you run this
code, there are no records to be read, at this time attempting to perform any
operations on the DataReader will generate an exception. So in the If-block, if
this condition evaluates to false i.e. no records have been read then I set
the 'VirtualItemCount' property of the DataGrid to zero, else its set to the
total number of records in the table, represented by the 'postid' field of the
last record. Note that the 'VirualItemCount' is only set once, when the page
is first loaded.
Moving ahead, I have formed one more SQL statement, which actually will retrieve
the data to be shown in the DataGrid. This statement looks a bit too weird
since I am trying to achieve many things from this statement. for simplicity
purpose I will break down this statement into parts.
"SELECT TOP 20 postid ,subject ,name ,replies ,views ,dt FROM newpost"
- This part selects the respective fields from the 'newpost' table. The
directive 'TOP 20' only returns 20 records. If you remember I had mentioned
before that you can customize the SQL statement in such a way that it should
return only those records that have to be displayed on the page. Since the 'PageSize'
property of our DataGrid is 20 I have chosen only 'TOP 20' records from the
database.
"WHERE postid < "+(DataGrid1.VirtualItemCount-(startIndex-1)
)" - This is where the real magic of the global variable 'startIndex'
comes. In order to achieve our goal of returning only the necessary number of
records (20), I have created a condition where I subtract the current value of
'startIndex' from the total number of rows (represented by the 'VirtualItemCount')
property. If you would recall, in the 'DataGrid_Updt' I first update the 'startIndex'
field and then I call this method. So say for example, the user clicked on
'Page 3', the page will refresh, and call the 'Page_Load' method, right? (I
hope ur awake!!) and then it will call the 'DataGrid_Updt' method. In this
method the 'startIndex' variable will be set to 60 (3*20, where 3 is the page
number and 20 is the number of records displayed per page). Next the 'Binding'
method will be called from the 'DataGrid_Updt' method. Here the above SQL
statement will be evaluated to,
"WHERE postid < " +(200 - (60 -1))" - (assuming
the total number of records being 200)
"WHERE postid < 141" - hence all records below 141
will be selected.
Also applying the above described "TOP 20" condition only records
from 120 to 140 (total 20 records) get selected!! Thus we achieve our target
of only retrieving 20 records from the Database!!!!!
"ORDER BY postid DESC" - Lastly we sort these 20
selected records in descending order, Why? Simply since we want the latest/newest
post to appear first and the oldest post to appear last!
Once the SQL statement is ready, be make a command and DataReader object and execute
the command. The derived reader is then databound to the DataGrid, displaying
the records fetched by us. If you would observe carefully, while I have made
multiple commands and readers, I have used the same instance of
OleDbConnection class, just opening it and closing it. This is because there
is a huge performance gain while using a single connection object to perform
various activities rather than creating a new instance every time you want to
connect to the database.
Now wasn't that easy ..... (I am kidding .... :))
public void Submit_Click(Object sender, EventArgs e)
{
//proceed only if the page is valid
if(Page.IsValid){
DateTime now = DateTime.Now ;
errmess.Text="" ;
string req = "name="+ System.Web.HttpUtility.UrlEncode(name.Text,
System.Text.Encoding.UTF8);
req+="&&email="+ System.Web.HttpUtility.UrlEncode(email.Text,
System.Text.Encoding.UTF8);
req+="&&subject="+ System.Web.HttpUtility.UrlEncode(subject.Text,
System.Text.Encoding.UTF8);
//Get the HostAddress of the Author
req+="&&ip="+ System.Web.HttpUtility.UrlEncode(Request.UserHostAddress.ToString(),
System.Text.Encoding.UTF8);
req+="&&date="+ System.Web.HttpUtility.UrlEncode(now.ToString(),
System.Text.Encoding.UTF8);
req+="&&message="+ System.Web.HttpUtility.UrlEncode(message.Text,
System.Text.Encoding.UTF8);
//A 'yes' is used below to tell the postmessage page that this is a new topic
req+="&&newpost="+ System.Web.HttpUtility.UrlEncode("yes",
System.Text.Encoding.UTF8);
//call the postmessage.aspx page and append the query to it.
Response.Redirect("postmessage.aspx?" + req);
}
else
{
errmess.Text="Fill in all the Required Fields before Posting!<br>" ;
}
}
|
The above method is called when a form (code shown below) is submitted. In ASP.NET 'Form's' that run at sever behave a bit differently than the forms we are used to in HTML and ASP. In ASP.NET we have the concept of Post-Back, i.e instead of sending the form details to some other page, the same page gets posted/called again and it can handle all the logic for updating/inserting the data into the database. Example of this is that when you click on any page number in the DataGrid, the same page gets called again, but with the necessary coding you can change the contents of the page. In my example to have maintainability of posting the form from various pages, I did not want this, rather I wanted to have the same old functionality that HTML/ASP gave us. I found 2 solutions to solve my problem:
1) Use a normal <form> tag without the 'runat=server' attribute and specify its action attribute to the new page. There was one drawback to this solution, I could no longer the Form Validating Web Controls like 'RequiredFieldValidator' etc which make form validation task in ASP.NET very easy!
2) The second solution is to declare a 'Form' to run at server, and in the Event Handling code, take the all the values of the form, append it as a query string manually and call the next page. I have taken this path. Just one last hitch to this is that while appending the query after a page you should convert it essentially to the UTF-8 format. So in the above method I take all the fields from the Form, convert it into UTF-8 format and finally redirect the response to the 'postmessage.aspx' page after appending all the form contents as a query. Beta1 users note that the 'Page.Navigate' method available in beta1 has been removed in beta2 :(.
<form method="post" runat="server" ID=Form1> <%-- The DataGrid settings. Its very interesting how much you can play with it --%> <asp:DataGrid id=DataGrid1 runat="server" ForeColor="Black" OnPageIndexChanged="DataGrid_Updt" PageSize="20" AllowPaging="True" width="80%" autogeneratecolumns="False" AllowCustomPaging="True" > <PagerStyle BackColor="Coral" Mode="NumericPages"> </PagerStyle> <AlternatingItemStyle BorderColor="#FFC080" BackColor="#FF9966"> </AlternatingItemStyle> <FooterStyle ForeColor="White" BackColor="DarkOrange"> </FooterStyle> <ItemStyle BackColor="Moccasin"> </ItemStyle> <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="Coral"> </HeaderStyle> <%-- I am setting up the Individual columns myself using Templates --%> <Columns> <%-- Manipulate the subject entry so that it contains a link to the reply page --%> <asp:TemplateColumn HeaderText="Subject" itemstyle-width=50%> <ItemTemplate> <asp:Label runat="server" Text= '<%#"<a href=reply.aspx?postid="+DataBinder.Eval(Container, "DataItem.postid")+">" +DataBinder.Eval(Container, "DataItem.subject")+"</a>" %>' ID=Label2></asp:Label> </ItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="Author Name" itemstyle-width=20%> <ItemTemplate> <asp:Label runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.name") %>' ID=Label3></asp:Label> </ItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="Replies" itemstyle-width=10%> <ItemTemplate> <asp:Label runat="server" width=10% Text='<%# DataBinder.Eval(Container, "DataItem.replies") %>' ID=Label4> </asp:Label> </ItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="Views" itemstyle-width=10%> <ItemTemplate> <asp:Label runat="server" width=10% Text='<%# DataBinder.Eval(Container, "DataItem.views") %>' ID=Label5> </asp:Label> </ItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="Date of Post" itemstyle-width=10%> <ItemTemplate> <asp:Label runat="server" width=10% Text=' <%# DateTime.Parse(DataBinder.Eval(Container, "DataItem.dt").ToString()).ToShortDateString() %>' ID=Label6> </asp:Label> </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid> |
Above, is the very interesting definition of the DataGrid, that will display the data from the 'newpost' table. Firstly, I wire-up the Event Handler for the 'OnPageIndexChange' Event, set the 'PageSize' property to 20 and the 'AllowPaging' / 'AllowCustomPaging' properties to 'true' since I want to enable paging in my grid with my custom method. I also set the 'AutoGenerateColumns' property to 'false' since I want to manually handle the creation of each column.
Next, I define a few Templates, Templates allow you to easily customize different parts of the DataGrid to suit your needs. The 'PagerStyle', 'AlternatingItemStyle', 'FooterStyle', 'ItemStyle' and the 'HeaderStyle' templates are used to modify the display of the respective parts of the DataGrid. Under the 'Columns' template we define individual settings for each column. The 'TemplateColumn' control (note its a control not a template!) is used within the 'Columns' template to represent each column. The 'ItemTemplate' template within each is 'TemplateColumn' control used to define the custom display. Under the 'ItemTemplate' template we can include various other controls and program them to display required output. In the above code I have done some customization to each of the columns within the 'ItemTemplate' template. Beta1 users note how the different template names have changed, they are much more self-explanatory and easy to use in beta2!! Novice users should note that I have encapsulated the DataGrid within a ASP.NET 'Form' that run's at server. This is very important, or your DataGrid Paging won't work!!
<table border="0" width="80%" align="center">
<tr >
<td class="fohead" colspan=2><b>Post New Topic</b></td>
</tr>
<tr class="folight" >
<td>Name :</td>
<td ><asp:textbox text="" id="name" runat="server" />
<asp:RequiredFieldValidator ControlToValidate=name display=static runat=server>
*</asp:RequiredFieldValidator></td>
</tr>
<tr class="folight">
<td>E-Mail :</td>
<td><asp:textbox text="" id="email" runat="server"/>
<asp:RequiredFieldValidator ControlToValidate=email display=static runat=server>
*</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator runat="server"
ControlToValidate="Email"
ValidationExpression="[\w-]+@([\w-]+\.)+[\w-]+"
Display="Static"
Font-Name="verdana" Font-Size="10pt"
ErrorMesage="Please enter a valid e-mail address">
</asp:RegularExpressionValidator></td>
</tr>
<tr class="folight">
<td> Subject:</td>
<td><asp:textbox test="" id="subject" width=200 runat="server"/>
<asp:RequiredFieldValidator ControlToValidate=subject display=static runat=server>
*</asp:RequiredFieldValidator>
</td></tr>
<tr class="folight">
<td>Message :</td>
<td>
<asp:TextBox id=message runat="server"
Columns="30" Rows="15" TextMode="MultiLine"></asp:TextBox></td>
</tr>
<tr class=folight>
<td colspan=2>
<asp:Button class=fodark id=write onClick=Submit_Click runat="server" Text="Submit">
</asp:Button></td></tr>
</table>
</form>
|
The final piece of the forum.aspx page is the form that can be used to post new topics to the forum. Nothing special here except a normal form and a few form validators.
Phew! One down 2 to go...... Take a round, freshen your mind... and COME BACK!!
2) reply.aspx : The topic viewing and replying
page.
I am not describing this page in detail since it just retrieves some content
from the database and updates the necessary fields. Later it displays the
content in a tabular form. One exercise I have left for you to solve is to
convert the code to use a DataReader and a Repeater.
<%@ Page Language="C#" EnableSessionState="False" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html><head>
<title>Post New Topic.</title>
<script Language="C#" runat="server">
DataSet ds ,rs;
DataRow dr ;
string postid ;
public void Page_Load(object sender , EventArgs e)
{
//Check if the page is Post Back
if(!Page.IsPostBack)
{
//Get the postid from the Query string
postid = Request.Params["postid"] ;
if(postid!=null)
{
//Database connection string.
string strConn=@"Provider=Microsoft.Jet.OLEDB.4.0;";
strConn+="Data Source="+Server.MapPath(".\\db\\board.mdb") ;
//Make a connection to the Database
OleDbConnection myConn = new OleDbConnection(strConn) ;
//string to select the records from the newpost table
string strCon ="SELECT subject, name, email, message ,dt FROM newpost ";
strCon+="WHERE postid="+postid ;
//set a OleDbDataAdapter
OleDbDataAdapter myCommand =new OleDbDataAdapter(strCon,strConn);
ds = new DataSet();
//Fill the DataSet
myCommand.Fill(ds,"newpost") ;
//Get the Row at position '0' and store it in a DataRow object
//Why put into a DataRow ? Its easy to access data from a DataRow
dr = ds.Tables["newpost"].Rows[0] ;
//Get the "subject" from the DataRow and set it up in the post reply
//form's subject field
subject.Text="Re:"+dr["subject"].ToString() ;
//Select the replies to the post from the reply table
strCon ="SELECT name , email, subject, message ,dt FROM reply ";
strCon+="WHERE postid="+postid ;
//Make a new OleDbDataAdapter and DataSet for the reply table
OleDbDataAdapter myCommand2 =new OleDbDataAdapter(strCon,strConn);
rs = new DataSet() ;
//fill the DataSet
myCommand2.Fill(rs, "reply") ;
//Code to update the "views" field for the newpost Table
//Select the views field from the table for a given postid
strCon ="SELECT views FROM newpost WHERE postid = "+postid ;
//Make a OleDbCommand here since we want a OleDbDataReader later
OleDbCommand vicomm = new OleDbCommand(strCon, myConn) ;
myConn.Open();
OleDbDataReader reader ;
//execute the statement and create a OleDbDataReader
reader = vicomm.ExecuteReader() ;
//Read the First record (there can only be one record remember !)
reader.Read() ;
//Get a "Int32" value from the first Column
int i = reader.GetInt32(0) ;
//Increase the views count
i++ ;
reader.Close() ;
//Update the newpost table with the new views value
strCon ="UPDATE newpost SET views = "+i+" WHERE (postid= "+postid+")" ;
//since we are using the same OleDbCOmmand object for this statement
//too, so set the CommandText property
vicomm.CommandText = strCon ;
//Since this statement will result in no output we use "ExecuteNonQuery()"
vicomm.ExecuteNonQuery() ;
//close the connection
myConn.Close();
}
}
}
// This method is called when the submit button is clicked
public void Submit_Click(Object sender, EventArgs e)
{
//Get the postid
postid = Request.Params["postid"] ;
//proceed only if Page is valid
if(Page.IsValid){
DateTime now = DateTime.Now ;
errmess.Text="" ;
//We have to call the postmessage.aspx page with a query to post the data
//Hence we have to first build the custom query from the Data posted by the user
//also since we are using a query we have to encode the data into UTF8 format
string req = "name="+ System.Web.HttpUtility.UrlEncode(name.Text,
System.Text.Encoding.UTF8);
req+="&&email="+ System.Web.HttpUtility.UrlEncode(email.Text,
System.Text.Encoding.UTF8);
req+="&&subject="+System.Web.HttpUtility.UrlEncode(subject.Text,
System.Text.Encoding.UTF8);
req+="&&ip="+System.Web.HttpUtility.UrlEncode(Request.UserHostAddress.ToString(),
System.Text.Encoding.UTF8);
req+="&&date="+ System.Web.HttpUtility.UrlEncode(now.ToString(),
System.Text.Encoding.UTF8);
req+="&&message="+ System.Web.HttpUtility.UrlEncode(message.Text,
System.Text.Encoding.UTF8);
//Encode "no" to indicate that the post is not a new post
//but its a reply to a earlier message
req+="&&newpost="+ System.Web.HttpUtility.UrlEncode("no",
System.Text.Encoding.UTF8);
req+="&&previd="+ System.Web.HttpUtility.UrlEncode(postid,
System.Text.Encoding.UTF8);
//Call the postmessage page with our custom query
Response.Redirect("postmessage.aspx?" + req);
}
else
{
errmess.Text="Fill in all the Required Fields !" ;
}
}
</script>
<LINK href="mystyle.css" type=text/css rel=stylesheet></head>
<body topmargin="0" leftmargin="0" rightmargin="0" marginwidth="0" marginheight="0">
<%-- Include a header file 'header.inc' --%>
<!-- #Include File="header.inc" -->
<br>
<div align=center>
<table border=0 width=80% cellspacing=2>
<tr class=fohead><th width=20%>Author Name</th>
<th width=80%>Message</th></tr>
<%-- Below I am encapsulating the email of the author over the name of the author
so that when you click on the author a e-mail gets sent to him
Also I am getting the DateTime from the DataBase and
Displaying the Date and Time separately --%>
<tr class=folight><td rowspan=2 align="center">
<%= "<a href=mailto:"+dr["email"]+">"+dr["name"]+"</a>" %><br>
<font size=1><%= DateTime.Parse(dr["dt"].ToString()).ToShortDateString() %><br>
<%= DateTime.Parse(dr["dt"].ToString()).ToShortTimeString() %></font>
</td>
<td><b>Subject: </b><%=dr["subject"] %></td></tr>
<tr class=folight>
<td><%=dr["message"] %> </td>
</tr>
<%-- Get all the replies to the Original post and show them --%>
<% int no = rs.Tables["reply"].Rows.Count ;
if(no>0)
{
for(int j=0 ;j<no ; j++)
{
DataRow rd = rs.Tables["reply"].Rows[j] ;
%>
<tr class=fodark>
<td align="center"><%="<a href=mailto:"+rd["email"]+">"+rd["name"]+"</a>" %><br>
<font size=1><%= DateTime.Parse(rd["dt"].ToString()).ToShortDateString() %><br>
<%= DateTime.Parse(rd["dt"].ToString()).ToShortTimeString() %></font>
</td>
<td><%=rd["message"] %> </td>
</tr>
<%
}
}
%>
</table>
</div>
<h3 align="center" class="fodark">
<a href=forum.aspx>Click Here</a> to go to back to Forum.
<br>Reply to the Above Post.</h3>
<br>
<asp:label id="errmess" text="" style="COLOR:#ff0000" runat="server" />
<form runat="server">
<table border="0" width="80%" align="center">
<tr >
<td class="fohead" colspan=2><b>Reply to the Post</b></td>
</tr>
<tr class="folight" >
<td>Name :</td>
<td ><asp:textbox text="" id="name" runat="server" />
<asp:RequiredFieldValidator ControlToValidate=name display=static runat=server>
*</asp:RequiredFieldValidator></td>
</tr>
<tr class="folight">
<td>E-Mail :</td>
<td><asp:textbox text="" id="email" runat="server"/>
<asp:RequiredFieldValidator ControlToValidate=email display=static runat=server>
*</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator runat="server"
ControlToValidate="Email"
ValidationExpression="[\w-]+@([\w-]+\.)+[\w-]+"
Display="Static"
Font-Name="verdana" Font-Size="10pt"
ErrorMesage="Please enter a valid e-mail address">
</asp:RegularExpressionValidator></td>
</tr>
<tr class="folight">
<td> Subject:</td>
<td><asp:textbox test="" id="subject" width=200 runat="server"/>
<asp:RequiredFieldValidator ControlToValidate=subject display=static runat=server>
*</asp:RequiredFieldValidator>
</td></tr>
<tr class="folight">
<td>Message :</td>
<td>
<asp:TextBox id=message runat="server"
Columns="30" Rows="15" TextMode="MultiLine"></asp:TextBox></td>
</tr>
<tr class=folight>
<td colspan=2>
<asp:Button class=fodark id=write onClick=Submit_Click runat="server" Text="Submit">
</asp:Button></td></tr>
</table>
</form><br>
<br><!-- #Include File="footer.inc" -->
</body></html>
|
3) postmessage.aspx : The page to save the posted data to the
database.
Here too, I am presenting the full code in one go! See inline comments for
more information. One major change you will find here is that I am using a
Parameterized SQL statements to insert a new record into the Database. This is
to solve the BUG 'Brian R. Bondy' brought to my notice, thanks pal!!
The bug is in using normal Insert SQL statements, eg:
string sqlString = "INSERT INTO newpost (name, subject) VALUES ('
"+name+" ', ' "+subject+" ')";
Where the variables 'name' and 'subject' refer to string's which the user
inputs.
Say if the user inputs a subject as "This prog's get a prob", then
our sqlString evaluates to
"INSERT INTO newpost (name,subject) VALUES ('saurabh' , 'This prog's get
a prob')"
The extra apostrophe
( ' ) in the subject (prog's) will throw a exception since the SQL string
becomes a faulty. That's why I have used Parameterized SQL statement which
solves this BUG!!
Note: One more important BUG (or feature whatever U call it) in OleDb.NET
supplied with the .NET SDK beta2 is that than within a Ms Access column of the
type 'Memo' you can only input some 4000 characters only! So if your message
extends that limit a exception is thrown :(. The only solution is to use ODBC.NET, which can be downloaded from msdn.microsoft.com/net
separately. ODBC.NET supplies a lot of left out features from OleDb.NET and is
a MUST download.
You will also see that below I have made use of the 'parsetext' method. This method is used to apply HTML formatting to the Message posted. (If your heart is still beating you can check out this article where I explain in detail the working of this method).
<%@ Page Language="C#" EnableSessionState="False" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Text" %>
<%@ Import Namespace="System.IO" %>
<html>
<head>
<title>Thank You for Posting !</title>
<script language="C#" runat="server" >
void Page_Load(Object Src, EventArgs E)
{
//Check id the page is loaded for the first time
if (!Page.IsPostBack) {
//Get the Parameters from the Query string and store it
string name = Request.Params["name"] ;
string email = Request.Params["email"] ;
string subject = Request.Params["subject"] ;
string ip = Request.Params["ip"] ;
string date = Request.Params["date" ];
string message = Request.Params["message"] ;
bool newmess =true ;
string previd ="1";
//Check of the 'newpost' paramater is 'no'
//indicating that its a reply to a previous post
if(Request.Params["newpost"].Equals("no"))
{
newmess =false ;
//Since its a reply, we get the ID of the topic
//to which this post is a reply
previd = Request.Params["previd"] ;
}
if(newmess)
{
//Execute the code below to insert a new topic
string strConn=@"Provider=Microsoft.Jet.OleDb.4.0 ;Data Source=";
strConn+=Server.MapPath(".\\db\\board.mdb") ;
OleDbConnection myConn = new OleDbConnection(strConn) ;
//SQL query with Parameters
string insertStr =" INSERT INTO newpost (name, email, subject, ip, dt, message) VALUES ";
insertStr+="(@name, @email, @subject, @ip, @dt, @message)";
//Create a new OleDbCommand
OleDbCommand insertCommand = new OleDbCommand(insertStr, myConn);
//Add a new Parameter '@name' of the type 'VarChar'
//and set its value
insertCommand.Parameters.Add(new OleDbParameter("@name", OleDbType.VarChar));
insertCommand.Parameters["@name"].Value = name;
insertCommand.Parameters.Add(new OleDbParameter("@email", OleDbType.VarChar));
insertCommand.Parameters["@email"].Value = email;
insertCommand.Parameters.Add(new OleDbParameter("@subject", OleDbType.VarChar));
insertCommand.Parameters["@subject"].Value = subject;
insertCommand.Parameters.Add(new OleDbParameter("@ip", OleDbType.VarChar));
insertCommand.Parameters["@ip"].Value = ip;
insertCommand.Parameters.Add(new OleDbParameter("@dt", OleDbType.VarChar));
insertCommand.Parameters["@dt"].Value = date;
insertCommand.Parameters.Add(new OleDbParameter("@message", OleDbType.VarChar));
//Give a call the the 'parsetext' method to parse the message
insertCommand.Parameters["@message"].Value = parsetext(message);
myConn.Open();
//Execute Non Query to insert a new topic in the database
insertCommand.ExecuteNonQuery();
myConn.Close() ;
}
else
{
//Insert a reply to a previous topic
string strConn=@"Provider=Microsoft.Jet.OleDb.4.0 ;Data Source=";
strConn+=Server.MapPath(".\\db\\board.mdb") ;
OleDbConnection myConn = new OleDbConnection(strConn);
//SQL statement with Parameters
string insertStr =" INSERT INTO reply (name, email, subject, ip, dt, ";
insertStr+="message, postid) VALUES ";
insertStr+="(@name, @email, @subject, @ip, @dt, @message, @postid)";
//Create a new OleDbCommand
OleDbCommand insertCommand = new OleDbCommand(insertStr, myConn);
//Add a new Parameter and set its value
insertCommand.Parameters.Add(new OleDbParameter("@name", OleDbType.VarChar));
insertCommand.Parameters["@name"].Value = name;
insertCommand.Parameters.Add(new OleDbParameter("@email", OleDbType.VarChar));
insertCommand.Parameters["@email"].Value = email;
insertCommand.Parameters.Add(new OleDbParameter("@subject", OleDbType.VarChar));
insertCommand.Parameters["@subject"].Value = subject;
insertCommand.Parameters.Add(new OleDbParameter("@ip", OleDbType.VarChar));
insertCommand.Parameters["@ip"].Value = ip;
insertCommand.Parameters.Add(new OleDbParameter("@dt", OleDbType.VarChar));
insertCommand.Parameters["@dt"].Value = date;
insertCommand.Parameters.Add(new OleDbParameter("@message", OleDbType.VarChar));
//Give a call the the 'parsetext' method to parse the message
insertCommand.Parameters["@message"].Value = parsetext(message);
insertCommand.Parameters.Add(new OleDbParameter("@postid", OleDbType.Integer));
insertCommand.Parameters["@postid"].Value = previd;
myConn.Open();
//Update the Database
insertCommand.ExecuteNonQuery() ;
myConn.Close();
//SQL string to get the 'replies' column of the topic
//to which this post is a reply
string replyno = "SELECT replies FROM newpost WHERE postid ="+previd ;
insertCommand.CommandText =replyno ;
myConn.Open();
OleDbDataReader reader =insertCommand.ExecuteReader() ;
reader.Read();
//Get the number of replies to this post
int rep =reader.GetInt16(0) ;
myConn.Close();
rep++ ;
//SQL statement to update the number of replies
//of the topic to which this post is a reply
string updtStr ="UPDATE newpost SET replies = "+rep
+" WHERE (postid = "+previd+")" ;
insertCommand.CommandText = updtStr;
myConn.Open();
//Execute the command
insertCommand.ExecuteNonQuery();
myConn.Close() ;
}
//Set the text of various textboxes to inform
//the user of the text entered into the database
NameLabel.Text = name;
EmailLabel.Text= email ;
SubjectLabel.Text=subject;
MessageLabel.Text=message ;
}
else
{
errmess.Text="This Page Cannot be called directly.";
errmess.Text+=" It has to be called from the Form posting page.<br>" ;
}
}
//Class to parse the Message into HTML format
public string parsetext(string text)
{
//Create a StringBuilder object from the string input
//parameter
StringBuilder sb = new StringBuilder(text) ;
//Replace all double white spaces with a single white space
//and
sb.Replace(" "," ");
//Check if HTML tags are not allowed
//Convert the brackets into HTML equivalents
sb.Replace("<","<") ;
sb.Replace(">",">") ;
//Convert the double quote
sb.Replace("\"",""");
//Create a StringReader from the processed string of
//the StringBuilder
StringReader sr = new StringReader(sb.ToString());
StringWriter sw = new StringWriter();
//Loop while next character exists
while(sr.Peek()>-1)
{
//Read a line from the string and store it to a temp
//variable
string temp = sr.ReadLine();
//write the string with the HTML break tag
//Note here write method writes to a Internal StringBuilder
//object created automatically
sw.Write(temp+"<br>") ;
}
//Return the final processed text
return sw.GetStringBuilder().ToString();
}
</script>
<LINK href="mystyle.css" type=text/css rel=stylesheet>
</head>
<body topmargin="0" leftmargin="0" rightmargin="0" marginwidth="0" marginheight="0">
<!-- #Include File="header.inc" -->
<center>
<asp:label id="errmess" text="" style="color:#FF0000" runat="server" />
<h2 class="fodark"><b>Thank You , for posting on the Message Board.</b></h2>
<table align=center width="60%" border="0" cellspacing="2" cellpadding="1" >
<tr class="fohead"><td colspan="2">The information You Posted!</td></tr>
<tr class="folight">
<td>Name :</td>
<td><asp:label id="NameLabel" text="" runat="server" /></td>
</tr>
<tr class="folight">
<td>E-Mail :</td>
<td><asp:label id="EmailLabel" text="" runat="server" /></td>
</tr>
<tr class="folight">
<td>Subject :</td>
<td><asp:label id="SubjectLabel" text="" runat="server" /></td>
</tr>
<tr class="folight">
<td>Message :</td>
<td><asp:label id="MessageLabel" text="" runat="server" /></td>
</tr>
</table>
<br>
<h4 class="fodark"><a href="forum.aspx">Click here </a> to go back to the Forum.<br>
<%-- A little work to show the link to return back to the page if,
the post was a reply --%>
<% if(Request.Params["previd"]!=null)
{ %>
<a href='reply.aspx?postid=<%=Request.Params["previd"] %>'>
Click here </a>to go back where you came from.
<% } %>
</h4>
</center>
<!-- #Include File="footer.inc" -->
</body>
</html>
|
Conclusion
Congratulations! You have JUST read through a HUGE article. And since you have
reached till here, you might as well scroll a bit more and empty your mind
below in the feedback form........ Your views will let me know if what I did
over 2 days really made sense or was it a waste??
Till next time ..." Asta Lavista .... I will be BACK ..... "
:)