0

I am developing a SharePoint 2013 custom WebPart using Visual Studio 2012 where on Page_Load event, I am calling a method RunProc() which executes a SQL Stored Procedure in some other server. When I deploy the solution (.wsp) and test in my Dev environment, it runs fine but while deploying the .wsp in another environment (say test/prod), I need to modify the Connection String as Server name will change accordingly. Providing a separate .wsp for each environment is not an affordable solution. Can anyone help me on how to configure this Connection String dynamically so that I can modify it accordingly before deploying it to a specific environment?

Here is the code snippet for a better understanding. The variable in question is the string variable connStr in the method RunProc()

protected void Page_Load(object sender, EventArgs e)
    {
       if  !(Page.IsPostBack)
        {
           RunProc();
        }
    }

protected void RunProc()
    {
        string connStr = @"Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=SSPI;Persist Security Info=False";
        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand com = new SqlCommand("[dbo].[RunJobStatus]", conn);
        com.CommandType = CommandType.StoredProcedure;
        conn.Open();
        com.ExecuteNonQuery();
        conn.Close();       
    }
  • In addition to Trevors suggestion about storing the connection string in the propertybag, this solution is excellent to be able to add properties via the UI. We are using it to store encrypted connection strings in our 2013 environment. pbs2010.codeplex.com – Christoffer Mar 12 '15 at 12:58
3

You could store the connection string in an SPPersistedObject which you could then modify via PowerShell. You can also store it in the SPFarm or SPWeb property bag. I prefer the SPPersistedObject. You can see a real-world implementation of that here:

https://github.com/Nauplius/FoundationSync/blob/2013/FoundationSyncSettings.cs

  • Hi Trevor, thanks a ton for your suggestion! If possible, can you please modify the snippet provided by me? I am not aware of this SPPersistedObject and also let me know how you configure the variable dynamically. Thanks in advance. – Koushik Mar 12 '15 at 12:53
  • So there is a bit of work involved in doing what you want to do. I'd suggest looking over the working example I provided. As for modifying the string once it is in an SPPersistedObject, see the wiki for that solution as an example: github.com/Nauplius/FoundationSync/wiki/Settings-Configuration – Trevor Seward Mar 12 '15 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.