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?
Re: Multiple SQL connection strings
Quote:
Originally Posted by
charlie20
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?
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.
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
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.