Dynamic SQL Connection in ASP.NET 3.5 with User Controls

• Download source (RAR) - 7.7 MB
• Download source (ZIP) - 9.8 MB

Introduction

This article is about Dynamic SQL Connection in ASP.NET 3.5 or earlier version. This article focuses on the run time connection to the remote SQL server or the local SQL Server.

You Should Be Familiar with the Below Topics Before Going Through This Tip

1. SQL Server authentication and authorization
2. Path access permission both in Windows and web
3. Basic controls of ASP.NET
4. Basic programming syntax of C#

Who Should Read It?

This tip will be very useful for beginners in ASP.NET and C#. The need of Dynamic SQL connection can be seen everywhere while developing application whether in Web or Windows. You can customize this code for Windows application also.

Background

The idea for this tip has been taken from the CodeProject article posted by Adrian Pasik, thanks to Adrian Pasik.

The basic idea behind this tip is to take a back up from the Remote server and restore from elsewhere.

The most interesting thing in this post is using .ini file everywhere. You can learn how you can save configuration data in .ini file and retrieve the data back at run time easily.

Using the Code

The below code is the basic code for the dynamic connection. Once you download the code, you can easily understand the logic behind dynamic connection.

protected void btnTestConnection_Click(object sender, EventArgs e)
{
    server_name = txtServerName.Text != string.Empty ?
    txtServerName.Text.Trim() : comboBoxSQL_SERVER_INSTANCES.Text;
    sql_user_name = textBoxSERVER_USERNAME.Text;
    sql_password = textBoxPASSWORD.Text;
    database_name = txtDataBaseName.Text != string.Empty ?
    txtDataBaseName.Text.Trim() : comboBoxDATABASE_NAMES.Text;
    this._DataBaseServerType = Session["DataBaseServerType"] != null ?
    (DataBaseServerType)Session["DataBaseServerType"] : DataBaseServerType.LOCAL_DATABASE;

    if (this._DataBaseServerType == DataBaseServerType.NETWORK_DATABASE)
    {
        connectionString = "Data Source=" + server_name +
        ";Database=" + database_name + ";
        User ID=" + sql_user_name + ";Password=" + sql_password + ";";
    }
    else if (this._DataBaseServerType == DataBaseServerType.LOCAL_DATABASE_WITH_SQL_AUTH)
    {
        connectionString = "Data Source=" + server_name +
        ";Database=" + database_name + "; User ID=" +
        sql_user_name + ";Password=" + sql_password + ";";
    }
    else if (this._DataBaseServerType == DataBaseServerType.LOCAL_DATABASE_HARD_DISK)
    {
        connectionString = "Server=.\\SQLExpress;AttachDbFilename=" +
        textBoxDATA_BASE_FILE_LOCATION.Text + ";Trusted_Connection=Yes;";
    }
    else if (this._DataBaseServerType == DataBaseServerType.LOCAL_DATABASE)
    {
        connectionString = "Data Source=" + server_name + ";
        Database=" + database_name + "; Integrated Security=true";
    }
    else if (this._DataBaseServerType == DataBaseServerType.NETWORK_DATABASE_BY_IP_ADDRESS)
    {
        connectionString = "Data Source=" +
        textBoxDATA_BASE_FILE_LOCATION.Text + "\\SQLExpress," +
        "666;Network Library=DBMSSOCN;" + "Initial Catalog=" +
        "JMS_HN" + ";User ID=" +
        sql_user_name + ";Password=" + sql_password + ";";
    }

    System.Data.SqlClient.SqlConnection con = new SqlConnection();
    con.ConnectionString = connectionString;
    try
    {
        con.Open();
        if (con.State == ConnectionState.Open)
        {
            if (checkEmptyFields() == 0)
            {
                saveConnectionInfoFromINIFile();
                lblMessage.Text = "Connection Saved Successfully...";
            }
            else
            {
                lblMessage.Text =
                "Please fill all the field to make connection to the server.";
            }
        }
    }
    catch
    {
        lblMessage.Text = "Unable to connect to server.";
        con.Close();
    }
}

The below code is the core of SQL backup (See this code in Default.aspx page of the code).

protected void btnBackUpDB_Click(object sender, EventArgs e)
  {
      //DateTime.Now.ToString("ddMMyyyy_HHmmss")
      int valid = 0;
      Validate(out valid);
      if (valid == 0) //Means No error
      {
          System.Threading.Thread.Sleep(2000);
          string filePath = "~/Runtime/BackupFiles/";

          string fileName = Session["DATABASE"].ToString() +
          DateTime.Now.Year.ToString() + "-" +
              DateTime.Now.Month.ToString() + "-" +

              DateTime.Now.Day.ToString() + "-" +
              DateTime.Now.Millisecond.ToString() + ".bak";
          fileName = txtBackupFileName.Text != string.Empty ?
          txtBackupFileName.Text + ".bak" : DateTime.Now.ToString
          ("ddMMyyyy_HHmmss") + ".bak";

          if (fileName.Contains(".bak.bak"))
              fileName = fileName.Replace(".bak.bak", ".bak");
          BackupDB(filePath, fileName);
      }
  }

The below code is the logical part of the backup utility.

protected void btnRestore_Click(object sender, EventArgs e)
{
    RestoreDB();
}
//See the below function description
public void RestoreDB()
{
    string selectedBAK_File = "";
    foreach (GridViewRow gRows in gvBackups.Rows)
    {
        CheckBox chkSelect = (CheckBox)gRows.FindControl("chkSelect");
        if (chkSelect != null)
            if (chkSelect.Checked)
                selectedBAK_File = ((Label)gRows.FindControl("lblFileName")).Text;
        break;
    }
    string databasename = Session["DATABASE"].ToString();
    string backup_FilePath = Server.MapPath("~/Runtime/BackupFiles/" +
    selectedBAK_File);//Session["BACKUPFILEPATH"].ToString();
    //FileUpload1.HasFile ? FileUpload1.PostedFile.FileName : "";
    //making the default connection by replacing the
    //original database with the master in connection string.
    string default_con_str = Session["CONNECTION_STRING"].ToString().Replace
        (databasename, "master");

    if (selectedBAK_File != string.Empty)
    {
        SqlConnection con1 = new SqlConnection(default_con_str);
        SqlConnection con2 = new SqlConnection(default_con_str);
        try
        {
            con2.Open();
            string restoreSQL = "";
            restoreSQL += " EXEC(' ";
            restoreSQL += " USE master ";
            restoreSQL += " ALTER DATABASE [" + databasename + "] ";
            restoreSQL += " SET OFFLINE WITH ROLLBACK IMMEDIATE ";
            restoreSQL += " USE master ";
            restoreSQL += " ALTER DATABASE [" + databasename + "] _
            SET SINGLE_USER WITH ROLLBACK IMMEDIATE ";
            restoreSQL += " RESTORE DATABASE [" + databasename + "] _
            FROM DISK = ''" + backup_FilePath + "'' _
            WITH REPLACE, NOUNLOAD,  STATS = 10";
            restoreSQL += " ')";
            SqlCommand cmd = new SqlCommand(restoreSQL, con2);
            cmd.ExecuteNonQuery();

            string restoreSQL1 = "";
            restoreSQL1 += " USE master ";
            restoreSQL1 += " ALTER DATABASE [" + databasename + "] ";
            restoreSQL1 += " SET ONLINE WITH ROLLBACK IMMEDIATE ";
            restoreSQL1 += " USE master ";
            restoreSQL1 += " ALTER DATABASE [" + databasename + "] SET MULTI_USER";

            SqlCommand cmd1 = new SqlCommand(restoreSQL1, con1);

            con1.Open();
            cmd1.ExecuteNonQuery();
            lblMessage.Visible = true;
            lblMessage.ForeColor = Color.Green;
            lblMessage.Text = "Database :" + databasename + " Restored Successfully..";
        }
        catch (Exception ex)
        {
            lblMessage.Visible = true;
            lblMessage.ForeColor = Color.Green;
            lblMessage.Text = "Error when restoring database.";
        }
        finally
        {
            con1.Close();
            con2.Close();
        }
    }
    else
    {
        lblMessage.Visible = true;
        lblMessage.ForeColor = Color.Red;
        lblMessage.Text = "No backup file were selected.Please select .bak file.";
    }
}

Points of Interest

I learned a lot from this code. Thanks to Code Project. I always read articles from this website.

History

To be updated soon...