Porting web application to different database server: ASP Alliance


Porting web application to different database server

page

by Lech P. Szczecinski
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 21864/ 47

Preface

       Let’s consider that we’re great developers (aren’t we?) and we have huge (several dozen pages with over a doze database access points each) web application designed for work with MS SQL Server2000 (in fact it could be any other database server; at this point it has no matter). Customer has found our web application very suitable for his needs and wants to buy it. He wants to install it on his web server without any changes (great, yeah?). With no changes but with one exception: he has Oracle database server (again it has no matter that it’s Oracle). So we’ve some work to do: porting.
       About such situation we’ve to think on implementing stage to write our web application in such way to provide as few work as possible with porting. So let’s think how to do that to avoid horror and terrible headache during such work.
       To make my article more clear, let’s consider that we have web page designed as:

<%@ Page language="c#" Codebehind="Article.aspx.cs" AutoEventWireup="false" Inherits="LesioS.Article" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>Article</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
  <asp:TextBox id="txtNumber" runat="server"></asp:TextBox>
  <asp:TextBox id="txtSetting" runat="server"></asp:TextBox>
  <asp:DropDownList id="ddlValue" runat="server" DataTextField="ValueName" DataValueField="ValueId"></asp:DropDownList>
  <asp:Button id="btnLoad" Text="Load" Runat="server"></asp:Button>
  <asp:Button id="btnSave" Text="Save" Runat="server"></asp:Button>
</form>
</body>
</HTML>

And following code-behind:

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace LesioS
{
public class Article : System.Web.UI.Page
{
  protected System.Web.UI.WebControls.TextBox txtNumber;
  protected System.Web.UI.WebControls.TextBox txtSetting;
  protected System.Web.UI.WebControls.Button btnLoad;
  protected System.Web.UI.WebControls.Button btnSave;
  protected System.Web.UI.WebControls.DropDownList ddlValue;

  private void Page_Load(object sender, System.EventArgs e)
  {
   // Put user code to initialize the page here
  }

  #region Web Form Designer generated code
  override protected void OnInit(EventArgs e)
  {
   InitializeComponent();
   base.OnInit(e);
  }

  private void InitializeComponent()
  {
   this.btnLoad.Click += new System.EventHandler(this.btnLoad_Click);
   this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
   this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

  private void btnLoad_Click(object sender, System.EventArgs e)
  {

  }

  private void btnSave_Click(object sender, System.EventArgs e)
  {

  }
}
}

As you can see our page is rather simple due to easy show interesting aspects of problem. Hold in mind that our application has dozen more complicated pages than that one.

1st attempt: how not to do it.

It’s common in application development that first intuitive approach is the worst. Let’s try to animate our page (note for newbies: better close your eyes). We’ve made following changes:

using System.Data.SqlClient;

private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
  SqlConnection connection = new SqlConnection("server=localhost;database=ScubaGielda;user id=sa;password=");
  string selectCommand = "SELECT * FROM [Values] ORDER BY ValueName";
  SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand, connection);
  DataSet ds = new DataSet();
  sqlDataAdapter.Fill(ds, "Values");
  ddlValue.DataSource = ds.Tables["Values"].DefaultView;
  ddlValue.DataBind();
}
}

private void btnLoad_Click(object sender, System.EventArgs e)
{
SqlConnection connection = new SqlConnection("server=localhost;database=ScubaGielda;user id=sa;password=");
string selectCommand = "SELECT * FROM Settings WHERE SettingId = @SettingId";
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand, connection);
sqlDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@SettingId", SqlDbType.Int, 4));
sqlDataAdapter.SelectCommand.Parameters["@SettingId"].Value = txtNumber.Text;
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds, "Settings");
txtSetting.Text = ds.Tables["Settings"].Rows[0]["SettingName"].ToString();
ddlValue.SelectedValue = ds.Tables["Settings"].Rows[0]["ValueId"].ToString();
}

private void btnSave_Click(object sender, System.EventArgs e)
{
SqlConnection connection = new SqlConnection("server=localhost;database=ScubaGielda;user id=sa;password=");
string updateCommand = "UPDATE Settings SET SettingName = @SettingName, ValueId = @ValueId WHERE SettingId = @SettingId";
SqlCommand sqlCommand = new SqlCommand(updateCommand, connection);
sqlCommand.Parameters.Add(new SqlParameter("@SettingName", SqlDbType.NVarChar, 10));
sqlCommand.Parameters["@SettingName"].Value = txtSetting.Text;
sqlCommand.Parameters.Add(new SqlParameter("@ValueId", SqlDbType.Int, 4));
sqlCommand.Parameters["@ValueId"].Value = ddlValue.SelectedValue;
sqlCommand.Parameters.Add(new SqlParameter("@SettingId", SqlDbType.Int, 4));
sqlCommand.Parameters["@SettingId"].Value = txtNumber.Text;
sqlCommand.Connection.Open();
sqlCommand.ExecuteNonQuery();
sqlCommand.Connection.Close();
}

So now our application can fill dropdown list with dictionary data and load and save main data. First thing that goes behind eyes is that connection string is repeated several times. It inelegant and erroneous, of course. So let’s create simple class which the only one functionality will be providing database connection string (of course we assume that we use Web.config file).

using System.Configuration;
public class ConnectionString
{
public static string Text
{
get { return ConfigurationSettings.AppSettings["ConnectionString"]; }
}
}

Why so weird? It’s simple case: if in the future we’d like to put connection string into Global.asax (to hide it) file or remain it in Web.config but keep it coded (to keep it confidential), we’ve to make change only in one place.
So now all our connection commands should look like:

SqlConnection connection = new SqlConnection(ConnectionString.Text);

Now please pay an attention to 1st line of added code. There’s following line:

using System.Data.SqlClient;

If this line is on every page in our fantastic web application that it means that we have real problem during porting: we have to alter all pages. So our first task is to remove it.

2nd attempt: going better.

Here comes a help from one of the ASP.NET basement: n-tier architecture. In 1st attempt we merged presentation layer and data access layer (DAL). So let’s try to separate them. Let’s create DAL class for communication with database:

using System;
using System.Data;
using System.Data.SqlClient;

namespace LesioS
{
public class DAL
{
  public static DataView LoadDllValues()
  {
   SqlConnection connection = new SqlConnection(ConnectionString.Text);
   string selectCommand = "SELECT * FROM [Values] ORDER BY ValueName";
   SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand, connection);
   DataSet ds = new DataSet();
   sqlDataAdapter.Fill(ds, "Values");
   return ds.Tables["Values"].DefaultView;
  }

  public static DataSet LoadSetting(string settingId)
  {
   SqlConnection connection = new SqlConnection(ConnectionString.Text);
   string selectCommand = "SELECT * FROM Settings WHERE SettingId = @SettingId";
   SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand, connection);
   sqlDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@SettingId", SqlDbType.Int, 4));
   sqlDataAdapter.SelectCommand.Parameters["@SettingId"].Value = settingId;
   DataSet ds = new DataSet();
   sqlDataAdapter.Fill(ds, "Settings");
   return ds;
  }

  public static void SaveSetting(string settingId, string settingName, string settingValue)
  {
   SqlConnection connection = new SqlConnection(ConnectionString.Text);
   string updateCommand = "UPDATE Settings SET SettingName = @SettingName, ValueId = @ValueId WHERE SettingId = @SettingId";
   SqlCommand sqlCommand = new SqlCommand(updateCommand, connection);
   sqlCommand.Parameters.Add(new SqlParameter("@SettingName", SqlDbType.NVarChar, 10));
   sqlCommand.Parameters["@SettingName"].Value = settingName;
   sqlCommand.Parameters.Add(new SqlParameter("@ValueId", SqlDbType.Int, 4));
   sqlCommand.Parameters["@ValueId"].Value = settingValue;
   sqlCommand.Parameters.Add(new SqlParameter("@SettingId", SqlDbType.Int, 4));
   sqlCommand.Parameters["@SettingId"].Value = settingId;
   sqlCommand.Connection.Open();
   sqlCommand.ExecuteNonQuery();
   sqlCommand.Connection.Close();
  }
}
}

And page code-behind comes to:

private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
ddlValue.DataSource = DAL.LoadDllValues();
ddlValue.DataBind();
}
}

private void btnLoad_Click(object sender, System.EventArgs e)
{
DataSet ds = DAL.LoadSetting(txtNumber.Text);
txtSetting.Text = ds.Tables["Settings"].Rows[0]["SettingName"].ToString();
ddlValue.SelectedValue = ds.Tables["Settings"].Rows[0]["ValueId"].ToString();
}

private void btnSave_Click(object sender, System.EventArgs e)
{
DAL.SaveSetting(txtNumber.Text, txtSetting.Text, ddlValue.SelectedValue);
}

Pay attention that using System.Data.SqlClient clause disappear from code. So it could be end of our work with preparing ready-to-porting code. Now you have not to touch presentation layer (in fact all .aspx pages) and the only one thing you have to change is DAL class to force to work with Oracle, Sybase or something else.
End of work now comes mainly from fact that DataSets are independent from database used with application. It’s huge plus. Another benefit is that DataSets have a lot of functionality implemented inside. In the other hand, this functionality in 80% of cases is not used, cause there’s no such need. Minus is that DataSet is rather heavy class. Besides, take a look at btnLoad_Click function: filling textboxes with data is rather inelegant. Let’s try to find some more efficient way.

3rd attempt: better and worse.

       Of course it’s rather simply to find something which is lighter that DataSets and which provides us data from database: data readers. They are light, forward-only, but they have one disadvantage: they’re connected with data provider which are specific to databases. So it means that using System.Data.SqlClient clause will appear in code-behind again.
       We can use one data reader which is common to all databases. It’s OleDbDataReader, but using it bereave us efficiency which comes from specific to database data reader. So it’s not that way.
       Let’s incorporate data readers first. Take a look at changed DAL class (in SaveSetting function we’ve made no changes):

public class DAL
{
public static SqlDataReader LoadDllValues()
{
  SqlConnection connection = new SqlConnection(ConnectionString.Text);
  string selectCommand = "SELECT * FROM [Values] ORDER BY ValueName";
  SqlCommand sqlCommand = new SqlCommand(selectCommand, connection);
  connection.Open();
  SqlDataReader dr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
  return dr;
}

public static SqlDataReader LoadSetting(string settingId)
{
  SqlConnection connection = new SqlConnection(ConnectionString.Text);
  string selectCommand = "SELECT * FROM Settings1 WHERE SettingId = @SettingId";
  SqlCommand sqlCommand = new SqlCommand(selectCommand, connection);
  sqlCommand.Parameters.Add(new SqlParameter("@SettingId", SqlDbType.Int, 4));
  sqlCommand.Parameters["@SettingId"].Value = settingId;
  connection.Open();
  SqlDataReader dr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
  return dr;
}
}

Both functions returns SqlDataReader object. In code-behind we have following changes:

using System.Data.SqlClient;
private void btnLoad_Click(object sender, System.EventArgs e)
{
SqlDataReader dr = DAL.LoadSetting(txtNumber.Text);
dr.Read();
txtSetting.Text = dr["SettingName"].ToString();
ddlValue.SelectedValue = dr["ValueId"].ToString();
}

Nevertheless changing returning object type in LoadDllValues function code-behind didn’t change in this point: there was no need to declare object which takes data for dropdown list. Different situation is with btnLoad_Click function: cause we can’t load separate textboxes from data reader we’ve to declare object, load data into and then assign proper data to proper textbox. Unfortunately using System.Data.SqlClient clause appeared again.
Fortunately we have more elegant solution in btnLoad_Click function and, what is much more important, we have lighter solution what have significant meaning with heavy loaded web servers. So we may suppose that solution goes to right direction, but we have again problem with porting application to another database server.

4th attempt: much better.

My idea is to incorporate another tier (hooray for n-tier architecture) to separate presentation layer and DAL. Let’s create DataBroker class.

using System;

namespace LesioS
{
public class DataBroker
{
private string _settingName;
private string _settingValue;

  public DataBroker(string settingName, string settingValue)
  {
   _settingName = settingName;
   _settingValue = settingValue;
  }

  public string SettingName
  {
   get { return _settingName; }
  }

  public string SettingValue
  {
   get { return _settingValue; }
  }
}
}

It’s extremely simple class with 1 constructor and 2 properties for reading data. Now let’s rewrite LoadSetting function.

public static ArrayList LoadSetting(string settingId)
{
SqlConnection connection = new SqlConnection(ConnectionString.Text);
string selectCommand = "SELECT * FROM Settings1 WHERE SettingId = @SettingId";
SqlCommand sqlCommand = new SqlCommand(selectCommand, connection);
sqlCommand.Parameters.Add(new SqlParameter("@SettingId", SqlDbType.Int, 4));
sqlCommand.Parameters["@SettingId"].Value = settingId;
connection.Open();
SqlDataReader dr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
ArrayList settings = new ArrayList();
while(dr.Read())
settings.Add(new DataBroker(dr["SettingName"].ToString(), dr["ValueId"].ToString()));
dr.Close();
return settings;
}

And btnLoad_Click function.

private void btnLoad_Click(object sender, System.EventArgs e)
{
ArrayList settings = DAL.LoadSetting(txtNumber.Text);
DataBroker db = (DataBroker)settings[0];
txtSetting.Text = db.SettingName;
ddlValue.SelectedValue = db.SettingValue;
}

The most important thing is that using System.Data.SqlClient clause disappeared from code-behind again and using System.Collection clause appeared, but from porting point of view it doesn’t matter. Loading arise from creating ArrayList can be omitted.
On the end I’ve to mention that code presented in LoadSetting function is not what I use in development; it was only for presentation reason. For everyday work I use Microsoft.ApplicationBlocks.Data component and I invite to become familiar with this solution.