Dhananjay Kumar

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

3. using Microsoft.SharePoint.Administration
   

 

To Add or Update connection string

Config config = (Config)_site.WebApplication.GetChild<Config>(“connectionstring”);
if (config == null){

config = newConfig(“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/&#8221;);

}

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.