CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    7

    Multiple SQL connection strings

    I have a winforms application & depending on the user selection regarding SQL authentication there are 2 possible connection strings :-

    - using (var con = new SqlConnection(String.Format("Data Source={0}; database={1}; Integrated Security=True", Sql_ServerName, Sql_DataBaseName)))
    - using (var con = new SqlConnection(String.Format("Data Source={0}; database={1}; User id={2}; Password={3};", Sql_ServerName, Sql_DataBaseName, txt_SqlAuth_UserName.Text, txt_SqlAuth_Password.Text)))

    First one is for windows authentication with the second being a facility for entering a specific username/password for the SQL server (sa for example).

    Apart from these con strings the remainder of the code is the same. How can I do an IF/THEN type statement depending on which selection the user has made??

    I have attempted an IF/THEN & also declaring the var con in the variables block, both without success.

    Any tips, pointer etc?

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Multiple SQL connection strings

    Quote Originally Posted by charlie20 View Post
    Any tips, pointer etc?
    Yes. Post your code using code tags. It's tough to see what is going wrong without seeing your code. Also, have you tried stepping into the code while debugging?

  3. #3
    Join Date
    Aug 2012
    Posts
    7

    Re: Multiple SQL connection strings

    My initial question was not simple enough then...... generic code is as follows :-

    using (var con ........) // Integrated Authentication
    {
    con.Open();
    .... code block......
    }

    using (var con ........) // SQL Authentication
    {
    con.Open();
    .... block......
    }

    The code block between the braces is identical for both & I am attempting to simplify & remove the duplicate code.......

    This does NOT work but it's what I am attempting to do :-

    If (SQL_Auth == true)
    {
    using (var con ........) // SQL Authentication
    }
    Else
    {
    using (var con ........) // Integrated Authentication
    }

    ....code block......

    The initial example shown with 2 sets of code works just fine. When I try & simplify - I can't even launch / step into the code as no matter what I try the IDE messages saying the con is not defined within the context........ I need to define the con string in such a manner that it is available within multiple code blocks.
    Last edited by charlie20; October 11th, 2015 at 11:30 AM.

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Multiple SQL connection strings

    Don't use a using block until after you have determine the authentication method

    Code:
    var cn = new SqlConnection( sql authentication );
    cn.Open();
    // Authenticate
    
    if (!SQL_Auth) // if sql authentication didn't authenticate, try integrated (sounds backwards, but okay...)
    {
      cn.Close(); // close the previous connection
      cn = new SqlConnection( integrated authentication)
      cn.Open();
    }
    
    using(cn)
    {
      // use the connection
      
      using(var cmd = new SqlCommand(... , cn))
      {
        // use the sql command
    
      } // cmd cleaned up 
    } // connection cleaned up

  5. #5
    Join Date
    Oct 2015
    Posts
    5

    Re: Multiple SQL connection strings

    Hi,

    do the IF/THEN for the string itself:
    Code:
    string connectionString = null;
    if(SQL_Auth)
    {
          connectionString = String.Format("Data Source={0}; database={1}; User id={2}; Password={3};", Sql_ServerName, Sql_DataBaseName, txt_SqlAuth_UserName.Text, txt_SqlAuth_Password.Text));
    }
    else
    {
          connectionString = String.Format("Data Source={0}; database={1}; Integrated Security=True", Sql_ServerName, Sql_DataBaseName);
    }
    
    using (var con = new SqlConnection(connectionString))
    {
    con.Open();
    .... code block......
    }
    If you need the connectionString in more places in your code, create a new class containing the connectionString as a static field.
    Last edited by hoppler; October 18th, 2015 at 03:58 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured