Building a Robust Web Based Email Client (WebMail) Using the IP*Works! ADO.NET Data Provider
By Lance Robinson - Technical Evangelist, /n software.
In the fall of 2004, /n software introduced a first in the world of Internet development: Internet ADO.Net Providers. Some of the most commonly used objects from the popular IP*Works! toolkit were included a new product called IP*Works! ADO.Net Provider, which allows you to work with these protocols in a way that database developers are more familiar. To get all of the mail from your Inbox, you can SELECT * from INBOX. To read the latest RSS feed you can SELECT * from feed. To send an email, just insert a new row into the OUTBOX table.
In this article, I will talk about the features and use of the Provider, and along the way I'll create an IMAP and POP capable Webmail interface with a minimal amount of code. You can also download the full sample project here.
Overview Of The Provider
In the beginning, the protocols available are IMAP, POP, SMTP, NNTP, and RSS. Others will be added, like WebDAV (aka HTTPMail), LDAP, and more. IP*Works! ADO.Net Data Provider makes integration of Internet capabilities into applications quick and easy - without the need to become familiar with the inner-workings of the protocols themselves.
You are familiar with the DbConnection, DbDataAdapter, and DbCommand objects. Now let me introduce the IPWorksConnection, IPWorksDataAdapter, and IPWorksCommand objects. They work in the same familiar way, except with something a little bit different happening on the inside - updating from a server instead of a database. Here is a brief description of the different objects in the Provider. Later in this article I'll get into more specifics and show how to use each one.
IPWorksConnection
The IPWorksConnection object manages a connection to some server, whether it be an IMAP, POP, HTTP, SMTP, or NNTP server. The IPWorksConnection can be opened and closed explicitly, or the connection can be managed automatically (opened only when necessary) by the IPWorksDataAdapter.
IPWorksDataAdapter
The IPWorksDataAdapter is just like the DataAdapter you're used to. It will allow you to execute commands to populate a DataSet or DataTable. If a connection is not open, one will be created automatically in order to perform the necessary operation.
IPWorksCommand
The IPWorksCommand object represents a SQL command: SELECT, INSERT, DELETE, or UPDATE.
IPWorksCommandBuilder
The IPWorksCommandBuilder object will automatically generate INSERT, DELETE, and UPDATE commands for an IPWorksDataAdapter, given a defined SELECT command.
Connection
To get started, first I'll setup an IPWorksConnection, so I need to determine the connection string. To form the connection string, simply specify all of the properties of the connection as semi-colon separated values. One connection property is required in all instances of IPWorksConnection: protocol. The protocol connection property specifies the Internet protocol to use for this connection. Depending on the protocol, other connection properties will apply. For example, if I use the IMAP protocol, I'll need to specify mailserver, user, and password properties in order to connect. If I use the RSS protocol, I'll need a URL at least. Here are a few examples:
ipWorksConnection ipwConnection1 = new ipWorksConnection("protocol=imap;
mailserver=myserver; user=myusername; password=mypassword");
ipWorksConnection ipwConnection2 = new ipWorksConnection("protocol=rss;
url=http://slashdot.org/");
ipWorksConnection ipwConnection3 = new ipWorksConnection("protocol=nntp;
newsserver=msnews.microsoft.com");
ipWorksConnection ipwConnection4 = new ipWorksConnection("protocol=smtp;
mailserver=myserver");
ipWorksConnection ipwConnection5 = new ipWorksConnection("protocol=pop;
mailserver=myserver; user=myusername; password=mypassword");
For a full list of connection string properties, please consult the documentation for the IP*Works! ADO.Net Data Provider.
My goal is to create a webmail application - so my connection string will need to take values from a web form where the user can specify their username, password, etc. My connectionstring will look like so:
ipWorksConnection1 = new ipWorksConnection(String.Format("protocol={0};
mailserver={1}; user={2}; password={3};",
rblProtocol.SelectedItem.Value, txtServer.Text,
txtUser.Text, txtPass.Text));
Columns
For the next step, I'll create a data adapter, and configure its commands:
ipWorksDataAdapter ipworksDataAdapter1 = new ipWorksDataAdapter();
Each command will be associated with a connection that should be used when performing that command. For the webmail application, I need to be able to select using an IMAP connection, and a POP connection. Since my connection string allows the user to select whether to use IMAP or POP, I just need to make sure that my SELECT command will be valid for either. Each protocol has its own list of valid columns and tables.
For example, the POP protocol supports the following columns:
messageid
messagefrom
messagesubject
messagetext
messagedate
messagecc
messageheaders
messagereplyto
messagesize
messageto IMAP, being a much more featured protocol, supports many more columns. In addition to all of those provided by POP above, IMAP also provides the following columns:
messageflags
messagedeliverytime
messagesender
messagebcc
messageinreplyto
messagenetid
messagecontenttype
messagecontentencoding In order to make the same SELECT statement work for either protocol, I'll limit myself to only those columns supported by both protocols. Optionally, I could use different SELECT statements depending on the protocol, or I could use a SELECT * to get all of the columns.
ipWorksDataAdapter1.SelectCommand = new ipWorksCommand("SELECT messageid,
messagefrom, messagesubject, messagedate, messagesize FROM INBOX",
ipWorksConnection1);
Tables
The above SELECT command gets a few columns from the INBOX table. But what other tables are available? The available tables depend on the protocol. For POP, INBOX is the only table there. Similarly RSS has only one table (the feed itself). But for other protocols like IMAP and NNTP, there are multiple tables.
For the IMAP protocol, the table is simply the name of the IMAP mailbox (folder) you are interested in. So INBOX will normally work for both POP and IMAP, but again IMAP has additional abilities over POP, so if your IMAP INBOX had a sub folder called "ToDo", you would also have an "INBOX.ToDo" table.
A special table, the "Schema Table", can be used to get a table of table names. This would be useful with the IMAP protocol to get a list of all of the mailbox folders, or in NNTP to get a list of all of the groups available on a particular news server. To use the schema table, you would use the following SELECT statement:
"SELECT * FROM INFORMATION_SCHEMA.TABLES"
Filling a Data Table
For the purposes of this article, I will stick to using the INBOX table for both POP and IMAP. At this point, we're almost done with the basics. We have a connection, an adapter with a select command. All thats left is to fill a table with the results.
ipWorksConnection1 = new ipWorksConnection(String.Format("protocol={0};
mailserver={1}; user={2}; password={3};",
rblProtocol.SelectedItem.Value, txtServer.Text,
txtUser.Text, txtPass.Text));
ipWorksDataAdapter ipworksDataAdapter1 = new ipWorksDataAdapter();
ipWorksDataAdapter1.SelectCommand = new ipWorksCommand("SELECT messageid,
messagefrom, messagesubject, messagedate, messagesize FROM INBOX",
ipWorksConnection1);
System.Data.DataTable dataTable1 = new System.Data.DataTable();
ipWorksDataAdapter1.Fill(dataTable1);
Webmail - Login
In this webmail application - I don't have to think much about the network communications (IMAP, POP, SMTP), since I'll be using the IP*Works! ADO.Net Provider. I do have to think about session management, and data presentation. I need to provide a login mechanism, so I'll add a forms authentication section to the web.config:
<authentication mode="Forms">
<forms name="Login" loginUrl="login.aspx" />
</authentication>
Then I'll implement the login form (login.aspx). The login form will contain textBoxes for the user to enter the necessary user, password, and server information. It will also contain a radioButtonList to select either POP or IMAP, and of course a "login" button. When the login button is clicked, an ipWorksConnection object will be created using the user input.
When I use the ipWorksDataAdapter, it could automatically open a connection when it is required, rather than me keeping the connection open. However, since the user will be interacting over the lifetime of the session with postbacks, I'll maintain the connection by adding the ipWorksConnection instance to the session as soon as the user logs in, and manually opening it, to keep it open until the session ends or until the user logs out.
Finally, after all of this I'll RedirectFromLoginpage back to the URL where we came from (ie, default.aspx).
private void bLogin_Click(object sender, System.EventArgs e)
{
ipWorksConnection1 = new IPWorksConnection(String.Format("protocol={0};
mailserver={1}; user={2}; password={3};",
rblProtocol.SelectedItem.Value, txtServer.Text,
txtUser.Text, txtPass.Text));
Session.Clear();
try
{
ipWorksConnection1.Open();
Session.Add("IPWorksConnection", ipWorksConnection1);
FormsAuthentication.RedirectFromLoginPage(txtUser.Text, false);
}
catch (Exception ex)
{
lError.Text = ex.Message;
}
}
Browsing Emails
In default.aspx, I will use a datagrid to display the emails in the INBOX. In the Page_Load of this main page, I'll check to see if there is a dataView object in the session. If it is not there, I know that this is the first time this session has been logged into, and I will perform some steps to setup this session:
Create an ipWorksConnection instance and set it equal to the one in the session (which was created in login.aspx).
Create an ipWorksDataAdapter and SELECT command to select the message columns I want.
Fill a DataTable with the ipWorksDataAdapter.
Set a dataView object equal to the default view of the newly filled DataTable, and add the new dataView to the session. If the dataView already exists in the session, I know not to perform these steps, and instead just databind the datagrid.
private void Page_Load(object sender, System.EventArgs e)
{
DataView dv = (DataView)Session["MessageView"];
if (dv == null)
{
DataTable dt = new DataTable();
IPWorksConnection dataConnection =
(IPWorksConnection)Session["IPWorksConnection"];
IPWorksDataAdapter dataAdapter = new IPWorksDataAdapter();
dataAdapter.SelectCommand = new IPWorksCommand("SELECT messageid,
messagefrom, messagesubject, (DateTime)messagedate,
(int)messagesize FROM INBOX", dataConnection);
dataAdapter.Fill(dt);
dv = dt.DefaultView;
Session.Add("MessageView", dv);
}
dgMessages.DataSource = dv;
dgMessages.DataBind();
}
Presentation
At this point, the user has logged in and has a bound datagrid containing information about emails. Notice that the SELECT statement used only gets the messageid, messagefrom, messagesubject, messagedate, and messagesize. I did not select the messagetext. This is best because the contents of the actual message body could be huge (if say there were large attachments, or just many emails). I will instead only select the messagetext after a user has clicked on a particular message.
To create a way for a user to click on a message, I'll make some of the columns of the datagrid HyperLink Columns, formatted so that the hyperlink created includes the messageID of the message they've clicked on. For example, the Subject column text value will be bound to the "messagesubject" column, but its URL Field will be "messageid". The URL Format String of the column will be "read.aspx?uid={0}". This gives us the ability to make the text of this column into a link to read.aspx where the uid querystring variable value will come from the messageid column of the selected row. I could of course do the same with the From, Date, and/or Size columns if so desired.
Reading Emails
read.aspx will contain a form to display the contents of the email, as well as the important message headers like From, To, Subject, etc.
Inside the Page_Load, I'll simply SELECT the columns from before, in addition to the messagetext column. This time I'll use the WHERE clause to select only those columns from the row where the messageid is the one passed in the querystring, like so:
private void Page_Load(object sender, System.EventArgs e)
{
IPWorksConnection dataConnection =
(IPWorksConnection)Session["IPWorksConnection"];
IPWorksDataAdapter dataAdapter = new IPWorksDataAdapter();
dataAdapter.SelectCommand = new IPWorksCommand("SELECT messageid,
messagefrom, (String)messageto, messagesubject,
(DateTime)messagedate, (int)messagesize, messagetext FROM INBOX
WHERE messageid = " + Request.QueryString["uid"], dataConnection);
dataAdapter.Fill(dt);
this.DataBind();
}
In design time, I have bound the Text properties of the textBoxes on the form to their respective columns in the DataTable. In the databindings of the textBox named txtMessage, which I have placed on the form to contain the contents of the message body, I have set the custom binding expression of the Text property to dt.Rows[0]["messagetext"]. I'll also set the databindings of the other labels on this form (lSubject, lTo, lFrom, etc). This way, as soon as I call DataBind in the Page_Load, all of the control values will be populated automatically from the DataTable.
For the databinding of the lSize label, I'll bind it to a function called FormatSize (passing in the messagesize column value) instead. This way I can format the size text in Mb, Kb, or just bytes.
public static string FormatSize(object o)
{
int i = Convert.ToInt32(o);
string[] suffix = {"b","kb", "mb", "gb"};
int j = 0;
while (i > 1024 && j < 4)
{
i = i / 1024;
j++;
}
return (i + " " + suffix[j]);
}
Compose Mail
When sending an email, and thinking about that with respect to a DataTable - it follows naturally that to send a new mail, you should insert a new row.
One way to do this is to just create a new ipWorksCommand object, open the connection, and call the ipWorksCommand objects ExecuteReader method. To use the INSERT command, it is necessary to set the Parameters array of the command object to the values that match their respective column names from the INSERT command itself, like in the example below:
private void bSend_Click(object sender, System.EventArgs e)
{
IPWorksConnection dataConnection = new IPWorksConnection(
String.Format("protocol=smtp; mailserver={0};",
getConnParam("mailserver")));
try
{
IPWorksCommand ipWorksCommand1 = new IPWorksCommand("INSERT INTO outbox
(sendto, subject, messagetext, messagefrom)", dataConnection);
((IDataParameter)ipWorksCommand1.Parameters[0]).Value = txtTo.Text;
((IDataParameter)ipWorksCommand1.Parameters[1]).Value = txtSubj.Text;
((IDataParameter)ipWorksCommand1.Parameters[2]).Value = txtMsg.Text;
((IDataParameter)ipWorksCommand1.Parameters[3]).Value = txtFrom.Text;
dataConnection.Open();
ipWorksCommand1.ExecuteReader();
}
catch (Exception ex)
{
lError.Text = ex.Message;
}
finally
{
dataConnection.Close();
}
}
For more details on the Provider itself, please download the IP*Works! ADO.Net Provider, which provides demo code (both winforms and webforms) and complete documentation for all of the Provider capabilities and protocols.
We appreciate your feedback. If you have any questions, comments, or suggestions about this article please contact our support team at kb@nsoftware.com.
