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.
