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.
