Configuring connection string using SPPersistedObject

Objective

In this article, I am going to show how to use SPPersistedObject to save connection string in config database of web application. I will also show how to configure connection string using ADMIN UI. We really do not need to go and configure the web.config manually.

Background

On a fine morning, I got a requirement from my boss that client does not want to read connection string from config file. Client does not want any manual work to modify the connection string. So I had to accommodate the connection string value in code itself .There are lots of options when it comes to choosing configuration information stores, but at the web application level SharePoint offers two choices that are quite natural: the <AppSettings> section of the web application web.config file and the hierarchical object store.

Hierarchical Object Store

This allows data to be stored in config database.

Explanation of Output

There are two sections

1. Admin
2. Operation

Admin section is used to configure the connection string. From here, Connection string could be set. This connection string will be saved in SPPersistedObject in config database of web application.



Operation section is fetching data from database. This is connecting to database using the connection string set by the Admin section.



Design

1. Add two buttons. One for Admin and one for Operation.
2. For Admin, inside a panel adds a textbox and button.
3. Text box is used to set the connection string
4. Button will update the connection string in configuration database.
5. For Operation add a panel and datagrid and button the panel.
6. DataGrid will be bind to the dataset.

Operation

At first time, connection string won't be set. So connection string text box will be empty. After second time connection string will be loaded with previously set connection string. This could be configuring from here, after pressing OK button new connection string will be set. So we are really not using web.config for configuration of connection string.

Codes

1. Add reference of Windows.SharePoint.Services.
2. Add namespace
   using Microsoft.SharePoint
   using Microsoft.SharePoint.Administration

To Add or Update connection string

            Config config = (Config)_site.WebApplication.GetChild<Config>("connectionstring");
            if (config == null)
            {
                config = new Config("connectionstring ", _site.WebApplication);
                if (!string.IsNullOrEmpty(txtConnectionString.Text))
                {
                    config.ConnectionString = txtConnectionString.Text;
                }
                else
                {
                    config.ConnectionString = "Connection String Not set ";
                }

                config.Update();
                MessageBox.Show("Connection String Got added ");
            }
            else
            {

                config.ConnectionString = txtConnectionString.Text.ToString();
                config.Update();
                MessageBox.Show("Connection String Got Updated");

            }
            pnlAdmin.Visible = false;

Explanation

1. Connectionstring is name of the key.
2. Adding the SPPersisted object at web application level.
3. Update() method is used to update the object in config database.
4. GetChild<>() method is used to read the value for the given key.
5. Operation is just fetching data from database. It is simple ADO.Net code. So no need of much explanation.
6. Other code is just hiding and showing the panels.

The whole code is as below

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using System.Data.Sql;
using System.Data.SqlClient;

namespace ConnectionStringTesting
{
    public partial class Form1 : Form
    {
        SPSite _site;
        public Form1()
        {
            InitializeComponent();
            CreateContext();
        }

        private void CreateContext()
        {
            _site = new SPSite("http://adfsaccount:2222/");
        }

        private void btnAdmin_Click(object sender, EventArgs e)
        {

            Config config = (Config)_site.WebApplication.GetChild<Config>("a5");
            if (config == null)
            {
                txtConnectionString.Text = " Connection String is not set yet ";
            }
            else
            {

                txtConnectionString.Text = config.ConnectionString;
            }
            pnlAdmin.Visible = true;
        }

        private void btnOk_Click(object sender, EventArgs e)
        {
            Config config = (Config)_site.WebApplication.GetChild<Config>(" connectionstring ");
            if (config == null)
            {
                config = new Config("connectionstring ", _site.WebApplication);
                if (!string.IsNullOrEmpty(txtConnectionString.Text))
                {
                    config.ConnectionString = txtConnectionString.Text;
                }
                else
                {
                    config.ConnectionString = "Connection String Not set ";
                }

                config.Update();
                MessageBox.Show("Connection String Got added ");
            }
            else
            {

                config.ConnectionString = txtConnectionString.Text.ToString();
                config.Update();
                MessageBox.Show("Connection String Got Updated");
            }
            pnlAdmin.Visible = false;
        }

        private void btnOperation_Click(object sender, EventArgs e)
        {

            Config config = (Config)_site.WebApplication.GetChild<Config>("connectionstring");
            if (config == null)
            {
                MessageBox.Show("Connection String is Not set ");
            }
            else
            {

                txtConnectionString.Text = config.ConnectionString;

                SqlConnection con = new SqlConnection(txtConnectionString.Text);
                try
                {

                    con.Open();
                    DataTable dt = new DataTable();
                    string strQuery = "select * from Person.Address";
                    SqlCommand cmd = new SqlCommand(strQuery, con);
                    cmd.CommandType = CommandType.Text;
                    SqlDataAdapter ada = new SqlDataAdapter();
                    ada.SelectCommand = cmd;
                    ada.Fill(dt);

                    dataGridView1.DataSource = dt;
                    pnlOperation.Visible = true;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Connection String is not Correct!!! Go to Admin link and make it correct ");
                }
            }
        }

        private void btnOperationOK_Click(object sender, EventArgs e)
        {
            pnlOperation.Visible = false;
        }
    }
}

Conclusion

In this article, we saw How to configure connection string using SPPersistedObject. Thanks for reading.