Menu
I am making an application to connect to a local SQL Server database, and I am using the following method to create the connection string and test for exceptions.
public void connect(string user, string password, string server, string database)
{
connectString = "user id=" + user + ";" +
"password=" + password + ";" +
"server=" + server + ";" +
"Trusted_Connection=yes;" +
"database=" + database + ";" +
"connection timeout=5";
myConnection = new SqlConnection(connectString);
try
{
myConnection.Open();
isConnected = true;
}
catch (SqlException)
{
isConnected = false;
}
catch (InvalidOperationException)
{
isConnected = false;
}
}
From my research there should be an exception thrown if the connection cannot be opened, but if I pass nonsense credentials or empty strings no exception is thrown. If I pass in correct information I am able to connect and use the connection string to pull in data. With the bad connection string I get an exception later when trying to fill a SqlDataAdapter with the data.
Also, with the nonsense connection strings myConnection.State is open when I debug and check that.
Is there a problem with my understanding of how the exception catching should work? Am I correct in thinking that the SqlConnection State should not be open when I am unable to connect to the database because of a bad credentials?
935
Yes, you need to Open your connection inside the using block. If you don't explicitly Close() the connection, the end of the using block will do it for you as the using disposes of the connection. When a connection is disposed it is automatically closed.
A SqlConnection is an object, just like any other C# object. Most of the time, you just declare and instantiate the SqlConnection all at the same time, as shown below: SqlConnection conn = new SqlConnection( "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
What happens if SqlConnection is not closed? Not closing connections could cause timeouts as the connection pool may run out of available connections that can be used. A side point to this. If you use the using keyword, that will automatically close the connection for you.
The connection string is an expression that contains the parameters required for the applications to connect a database server. In terms of SQL Server, connection strings include the server instance, database name, authentication details, and some other settings to communicate with the database server.
In your connection string you have "Trusted_Connection=yes;" that will use the current windows account for authentication and will ignore the username, password supplied in the connection string.
See: Integrated Security -or- Trusted_Connection from SqlConnection.ConnectionString Property
When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication. Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.
If User ID and Password are specified and Integrated Security is set to true, the User ID and Password will be ignored and Integrated Security will be used. SqlCredential is a more secure way to specify credentials for a connection that uses SQL Server Authentication (Integrated Security=false).
61
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
