Configuring Database Access for an ASP.NET Application

• Download source files - 9.02 KB

Introduction

Everybody who has tried out the SqlDataSource and GridView ASP.NET 2.0 classes knows how easy it is to create a database view form for a single table. But even this simple task becomes more difficult if your database access settings should be changed at run-time by the end user.

This article shows the easiest way to include a database access configuration form, save these settings in the Web.config file, and use them.

WarningManager

I've created a simple helper class called WarningManager. It uses an instance of Label to display information messages in green and warnings in red.

Application and database

I've used the database (MS SQL Server 2005) from one of my projects. It contains a base called payments and the departments table within it. The table contains the columns ID, number, and name.

The purpose of the application is to display the table's records in a GridView. The main difficulty is that I want to give the user the ability to configure the database access settings using a form in the application.

Okay, we can implement a SELECT statement and do the table populating manually, but this is a mess compared to SqlDataSource automatic configuration, when you need some additional functionality such as data updating.

The trick

The trick is the following: we store several settings in the Web.config appSettings section. They represent the server's address, user ID, and password, and the connection string format.

At the same time, we create a working "development" connection string, which would be replaced on the configuration settings saved.

SqlDataSource

Let's start with adding a SqlDataSource to the page. Now we can use the data source configuration wizard, provide the development databse server address, the user ID, and password. Then we agree with saving the ConnectionString in the Web.config file and configure the SELECT statement.

This will create code like this:

<asp:SqlDataSource ID="SqlDataSource1" 
        runat="server" ConflictDetection="CompareAllValues" 
        ConnectionString="<%$ ConnectionStrings:DataConnect %>" 
        ProviderName="System.Data.SqlClient" 
        OldValuesParameterFormatString="original_{0}" 
        SelectCommand="SELECT [id], [number], [name] 
                       FROM [departments] ORDER BY [id]">

Mention the "<%$ ConnectionStrings:DataConnect %>" value - it indicates that the DataSource will automatically read the DataConnect ConnectionString from the Web.Config.

We also add a GridView to the page and set its DataSourceID attribute to SqlDataSource1.

Web.config

Now we open the Web.config file and add four parameters to the appSettings node. The file should look like this:

<configuration>
  <appSettings>
    <add key="sConnectionString" 
            value="Data Source={0};Initial 
                   Catalog=payments;Persist Security Info=True;
                   User ID={1};Password={2}" />
    <add key="sServer" value="localhost" />
    <add key="sUsername" value="payments_user" />
    <add key="sPassword" value="pPassword" />
  </appSettings>

  <connectionStrings>
    <add name="DataConnect" providerName="System.Data.Sql"
      connectionString="Data Source=localhost;Initial 
                        Catalog=payments;Persist Security Info=True;User 
                        ID=payments_user;Password=pPassword" />
  </connectionStrings>
  
  <system.web>
    <compilation debug="true" />
    <authentication mode="Windows" />
  </system.web>
</configuration>

Nice. Now we need to edit the values of these five nodes and our DataSource will automatically bind to a new server.

Settings form

We add another page for accessing the configuration form and add the following fields to it:

<table ID="Table1" runat="server" style="width: 352px">
    <tr runat="server">
        <td runat="server">Server</td>
        <td runat="server" style="width: 132px">
            <asp:TextBox ID="ServerBox" runat="server" 
                      Width="136px"></asp:TextBox>
        </td>
    </tr>
    <tr runat="server">
        <td runat="server">User ID</td>
        <td runat="server" style="width: 132px">
            <asp:TextBox ID="UserBox" runat="server" 
                  Width="136px"></asp:TextBox>
        </td>
    </tr>
    <tr runat="server">
        <td runat="server">Password</td>
        <td runat="server" style="width: 132px">
            <asp:TextBox ID="PasswordBox" runat="server" 
              TextMode="Password" Width="136px"></asp:TextBox> 
        </td>
    </tr>
    <tr runat="server">
        <td runat="server">Confirm password</td>
        <td runat="server" style="width: 132px">
            <asp:TextBox ID="PasswordBox1" runat="server" 
                  TextMode="Password" 
                  Width="136px"></asp:TextBox> 
        </td>
    </tr>
    <tr>
        <td runat="server" colspan="2">
            <asp:Label ID="WarningLabel" runat="server" 
              Font-Bold="True" ForeColor="Red" 
              Text="Label"></asp:Label>
        </td>
    </tr>
    <tr runat="server">
        <td runat="server" colspan="2" style="text-align: right;">
            <asp:Button ID="TestButton" runat="server" 
                  Text="Test Connection" 
                  OnClick="TestButton_Click" CssClass="button" 
                  Width="160px"/>
            <asp:Button ID="SaveButton" runat="server" Text="Save" 
                  OnClick="SaveButton_Click" CssClass="button" 
                  Width="82px" />
        </td>
    </tr>
</table>

The first button is used for testing the connection, the second for saving the settings.

On page initialization, we fill the Server and User fields with values read from the Web.config file using the ConfigurationManager class.

private WarningManager wm = null;

protected void Page_Init(object sender, EventArgs e)
{
    wm = new WarningManager(WarningLabel);
    ServerBox.Text = ConfigurationManager.AppSettings["sServer"];
    UserBox.Text = ConfigurationManager.AppSettings["sUsername"];
    PasswordBox.Text = "";
    PasswordBox1.Text = "";
}
protected void Page_Load(object sender, EventArgs e)
{
    wm.HideWarning();
}

The WarningLabel in the table is used for showing the testing results. We use WarningManager to display these results.

The code to test the connection is simple: we compose the ConnectionString and try to connect to the server:

protected void TestButton_Click(object sender, EventArgs e)
{
    if (PasswordBox.Text != PasswordBox1.Text)
    {
        wm.ShowWarning("Passwords don't match");
        return;
    }
    wm.HideWarning();

    string connectionString = String.Format(
           ConfigurationManager.AppSettings["sConnectionString"],
           ServerBox.Text, UserBox.Text, PasswordBox.Text);
    SqlConnection conn = new SqlConnection(connectionString);

    try
    {
        conn.Open();
        if (conn.State == ConnectionState.Open)
            wm.ShowInfo("Connection OK");
    }
    catch
    {
        wm.ShowWarning("Connection failed");
    }
    finally
    {
        conn.Close();
    }
}

A bit more difficult thing to do is to save the settings, as static ConfigurationManager doesn't save settings. So, we use the System.Web.Configuration namespace and the WebConfigurationManager class. The code for saving the settings is the following:

protected void SaveButton_Click(object sender, EventArgs e)
{
    if (PasswordBox.Text != PasswordBox1.Text)
    {
        wm.ShowWarning("Passwords don't match");
        return;
    }
    wm.HideWarning();

    Configuration config = 
       WebConfigurationManager.OpenWebConfiguration("~");

    config.AppSettings.Settings["sServer"].Value = ServerBox.Text;
    config.AppSettings.Settings["sUsername"].Value = UserBox.Text;
    config.AppSettings.Settings["sPassword"].Value = PasswordBox.Text;
    config.ConnectionStrings.
      ConnectionStrings["DataConnect"].ConnectionString = 
      String.Format(
        ConfigurationManager.AppSettings["sConnectionString"], 
        ServerBox.Text, UserBox.Text, PasswordBox.Text); ;
    try
    {
        config.Save();
    }
    finally { }
}

That's it

Okay, we are ready. We add one more label to the Default.aspx page to show the currently used ConnectionString and two links to jump from one page to another. Ready. Now you can go to the settings configuration page, change settings, test connection (if needed), and save the settings. The Web.config file will be updated and the next time the Default.aspx page loads, SqlDataSource1 reads the new ConnectionString and loads the data.