locked
How to set the connection string in Code, not in configiuration? RRS feed

  • Question

  • The connection string usaually contain the sensitive information, and I do not want to set it in the configiuration.

    So how to set the connnection string manually in code? Thanks, Cloud.
    Friday, April 3, 2009 8:06 AM

Answers

All replies

  • Here is how you can set it in code and encrypt it:

    private void EncryptConnString()
    {
    	Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
    	string protectionProvider = "DataProtectionConfigurationProvider";
    
    	// Create a connection string element.
    	ConnectionStringSettings csSettings = new ConnectionStringSettings("MyConn", "SqlServer: data source=127.0.0.1;Integrated Security=SSPI; Initial Catalog=aspnetdb", "System.Data.SqlClient");
    
    	// Get the connection strings section.
    	ConnectionStringsSection csSection = config.ConnectionStrings;
    	csSection.ConnectionStrings.Add(csSettings);
    	
    	ConfigurationSection connStrings = config.ConnectionStrings;
    	connStrings.SectionInformation.ProtectSection(protectionProvider);
    	connStrings.SectionInformation.ForceSave = true;
    	config.Save(ConfigurationSaveMode.Full);
    } 
    
    private void DecryptConnString()
    {
    	Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
    	string protectionProvider = "DataProtectionConfigurationProvider";
    	
    	ConfigurationSection connStrings = config.ConnectionStrings;
    	connStrings.SectionInformation.UnprotectSection();
    	//connStrings.SectionInformation.ForceSave = true;
    	//config.Save(ConfigurationSaveMode.Full);
    
    	// Get connection string
    	string str = ConfigurationManager.ConnectionStrings["theConnStrName"].ConnectionString;
    } 
    

    Tomi Airaksinen - MCPD [Remember to click "mark as answered" when you get a correct reply to your question]
    Friday, April 3, 2009 8:50 AM
  • Thanks for your replay. The encryption and decryption are great, but the problem is that when you set the encrypted connection string in web.config, where do you decrypt it in the code?
    Friday, April 3, 2009 8:56 AM
  • If you set the connection string in code, is your connection string always the same?
    If not, you can change that DecryptConnString() to return your decrypted connection string and call that method just before you are instantiating your connection to DB:

    string connStr = DecryptConnString();
    SqlConnection conn = new SqlConnection(connStr);
    conn.Open();
    

    Tomi Airaksinen - MCPD [Remember to click "mark as answered" when you get a correct reply to your question]
    Friday, April 3, 2009 9:16 AM
  • Yes, your anwser is correct if I access the database by ado.net. The ado.net data services handle all the database access for the developer, consequentlly I cannot find the way to set the connnetion string when the entity is instantiated. Thanks, Cloud.
    Friday, April 3, 2009 9:33 AM
  • You can always override CreateDataSource method in your dataservice class and create the provider instance yourself using the connection string.

    Hope this helps.

    Thanks
    Pratik
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, April 3, 2009 3:53 PM
    Moderator
  • I tried to override CreateDataSource method in the data service, and what I only can do is to specify the connection string in DBConnection object in CurrentDataSource, but unfortunatly it does not work. The error is not clear and let me check the log. Where is the log of ASP.NET Development Server located?
    I also add the connection string in the data entity construct method, but it does not work either. The error is just like "Parameter is not support data source".
    Friday, April 3, 2009 4:01 PM
  • Are you using EF as the underlying provider? If yes, you can do the following in your CreateDataSource method:
    protected override MyObjectContext CreateDataSource()
    {
         MyObjectContext dataContext = new MyObjectContext(connectionString);
         return dataContext;
    }

     


    Thanks
    Pratik
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, April 3, 2009 4:21 PM
    Moderator
  • protected override ETRACK_POCEntities CreateDataSource()
    { //string connectionString = @"metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.;Initial Catalog=ETRACK_POC;User ID=sa;Password=sa2005;MultipleActiveResultSets=True"";
    string connectionString = "Data Source=.;Initial Catalog=ETRACK_POC;User ID=sa;Password=sa2005;MultipleActiveResultSets=True";
    ETRACK_POCEntities instance = new ETRACK_POCEntities(connectionString);
    return instance;
    }

    The error occurs when I request the service. Above is my code, the error is : Keyword not supported: 'data source'.
    The exception is :ArgumentException.
    ETRACK_POCEntities is entity framework.

    I'm sorry that I cannot paste my code as order as you are. I add the p or br by myself which is the html tag I mean. If I dont add the tag all the word are in one paragraph...

    Friday, April 3, 2009 4:35 PM
  • Which did you comment out the first connection string? That looks more like the right connection string for EF provider. Did you run into any issues passing that?

    Pratik
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, April 3, 2009 4:47 PM
    Moderator
  • Both of the connection strings have the same error.. Keyword not supported: 'data source'.
    Friday, April 3, 2009 4:49 PM
  • This article should help - i think its the issue with double quotes. http://blogs.msdn.com/rickandy/archive/2008/12/09/explicit-connection-string-for-ef.aspx

    Hope this helps.

    Thanks
    Pratik
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, April 3, 2009 4:53 PM
    Moderator
  • The problem is too tricky. Thank you very much for your help.
    Friday, April 3, 2009 5:20 PM