none
How to create SQL connection outside of the code? RRS feed

  • Question

  • Hi everybody!

    I am quite new in C# programming, but I really want to study it! Smile

     

    I have question, concerning the connection string to SQL Express 2005.

     

    So I have developed small application that allows users to grab some reports from the SQL Express database. What I need to do is to separate SQL connection string from the code. I need to create some configuration window within application where users will be able to define path to the server, user name and password themselves.

     

    Can anyone help me with this?

     

    Thank you in advance.

     

    Monday, July 30, 2007 12:31 PM

Answers


  • Hi,

    If you are creating Windows Forms Application, use Application Settings in App.Config file to store your connection string.
    Reference : link,
    link, link, link

    If you are creating Web Forms Application, use Web.Config file's ConnectionStrings tag in Configuration tag and access it through ConfigurationManager.ConnectionStrings collection.
    Reference : link,
    link, link

    Both are the standard ways to store your connection strings outside the application.
    Monday, July 30, 2007 1:11 PM
  • Are you saying you need to be able to create a connection string at runtime based on what the user enters?

    Here's a function that uses the SqlConnectionStringBuilder object to do that. There are more properties on the SqlConnectionStringBuilder, but this function handles the ones you're most likely to need.

    Code Snippet


            public static string BuildConnectionString(string serverInstanceName, string databaseName, bool integratedSecurity, string username, string password)
            {
                SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder();
                connectionBuilder.DataSource = serverInstanceName;
                connectionBuilder.InitialCatalog = databaseName;
                connectionBuilder.IntegratedSecurity = integratedSecurity;
                if(! integratedSecurity)
                {
                    connectionBuilder.Password = password;
                    connectionBuilder.UserID = username;
                }

                return connectionBuilder.ConnectionString;
            }



    Hope this helps.

    Regards,

    Sean
    Monday, July 30, 2007 8:03 PM

  • Yes, you will need to create a form that has a interface to take database details from the user and convert it to the connectionstring using the method provided by
    Sean Fowler. Then when you get the connection string, store it in the config files like I said in my first post.

    If you get confused what interface your form should provide, check out the connection dialog provided by visual studio or any other application.

    Hope this helps.
    Tuesday, July 31, 2007 8:39 AM

  • Hi,

    Glad to see the progress.... Now, you have to put the code Sean wrote on the click event of the Save button. First you create a connection string using Sean's code, then test the connection by creating a connection object using that connection string and using Open and Close methods. If no exception occurs, the connection is valid. Then proceed and save the values in the application settings otherwise ask user to insert correct details.

    Now, you can access application settings using {Default Namespace}.Properties.Settings.Default.{Setting Name}. Example :

    WindowsApplication.Properties.Settings.Default.UserName will return the user name stored in the application settings. Here, UserName is get and set property, so you change it to something and to save the changes, call Default.Save() method.

    Hope this helps.
    Saturday, August 4, 2007 6:37 AM

All replies


  • Hi,

    If you are creating Windows Forms Application, use Application Settings in App.Config file to store your connection string.
    Reference : link,
    link, link, link

    If you are creating Web Forms Application, use Web.Config file's ConnectionStrings tag in Configuration tag and access it through ConfigurationManager.ConnectionStrings collection.
    Reference : link,
    link, link

    Both are the standard ways to store your connection strings outside the application.
    Monday, July 30, 2007 1:11 PM
  • Thank you for respond.

     

    I will go through all the documents and try to figure it out.

     

     

    Monday, July 30, 2007 6:25 PM
  • Are you saying you need to be able to create a connection string at runtime based on what the user enters?

    Here's a function that uses the SqlConnectionStringBuilder object to do that. There are more properties on the SqlConnectionStringBuilder, but this function handles the ones you're most likely to need.

    Code Snippet


            public static string BuildConnectionString(string serverInstanceName, string databaseName, bool integratedSecurity, string username, string password)
            {
                SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder();
                connectionBuilder.DataSource = serverInstanceName;
                connectionBuilder.InitialCatalog = databaseName;
                connectionBuilder.IntegratedSecurity = integratedSecurity;
                if(! integratedSecurity)
                {
                    connectionBuilder.Password = password;
                    connectionBuilder.UserID = username;
                }

                return connectionBuilder.ConnectionString;
            }



    Hope this helps.

    Regards,

    Sean
    Monday, July 30, 2007 8:03 PM
  • Hi Sean

     

    Yes, you are right. I would like users to be able to change connection parameters from the application when they need it, in order to make the application more flexible and not to change code all the time when users need to connect to different server and bases.

     

    The only question rose is how then you imbed it to the form? I men I will need to create the form within my application where users will change parameters of the connection (server name, db, password and so on)?

     

    Thank you for help

    Tuesday, July 31, 2007 5:24 AM

  • Yes, you will need to create a form that has a interface to take database details from the user and convert it to the connectionstring using the method provided by
    Sean Fowler. Then when you get the connection string, store it in the config files like I said in my first post.

    If you get confused what interface your form should provide, check out the connection dialog provided by visual studio or any other application.

    Hope this helps.
    Tuesday, July 31, 2007 8:39 AM
  • OK, I created configuration from the project properties/settings see image

     

    Then I created the windows form where user will input settings  see image

     

    And I do not know now how to link this two items togather and I am not sure where to put the code example Sean Fowler wrote?

     

    Can you please explain in more detail.

     

    Thank you a lot for help.

     

    this is an app.config file of my application

     

    Code Snippet

    <?xml version="1.0" encoding="utf-8" ?>

    <configuration>

    <configSections>

    <sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >

    <section name="StockFT.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" />

    </sectionGroup>

    <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >

    <section name="StockFT.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

    </sectionGroup>

    </configSections>

    <userSettings>

    <StockFT.Properties.Settings>

    <setting name="serverInstanceName" serializeAs="String">

    <value>server</value>

    </setting>

    <setting name="databaseName" serializeAs="String">

    <value>testdb</value>

    </setting>

    <setting name="username" serializeAs="String">

    <value>user</value>

    </setting>

    <setting name="password" serializeAs="String">

    <value>pass</value>

    </setting>

    </StockFT.Properties.Settings>

    </userSettings>

    <applicationSettings>

    <StockFT.Properties.Settings>

    <setting name="integratedSecurity" serializeAs="String">

    <value>True</value>

    </setting>

    </StockFT.Properties.Settings>

    </applicationSettings>

    </configuration>

     

     

    Friday, August 3, 2007 9:16 PM

  • Hi,

    Glad to see the progress.... Now, you have to put the code Sean wrote on the click event of the Save button. First you create a connection string using Sean's code, then test the connection by creating a connection object using that connection string and using Open and Close methods. If no exception occurs, the connection is valid. Then proceed and save the values in the application settings otherwise ask user to insert correct details.

    Now, you can access application settings using {Default Namespace}.Properties.Settings.Default.{Setting Name}. Example :

    WindowsApplication.Properties.Settings.Default.UserName will return the user name stored in the application settings. Here, UserName is get and set property, so you change it to something and to save the changes, call Default.Save() method.

    Hope this helps.
    Saturday, August 4, 2007 6:37 AM
  • Thank you all for help. It all looks very complicated for me. For this time I will use standard imbedded to the code connection string I think.

     

    But maybe some of you know where I can download detailed user guide with detailed description of my case?

     

    Thank you again

     

    Sunday, August 5, 2007 3:45 PM