Why am I getting this database connectivity error in SQL Server?

Question

I'm a beginner in Database programming and am having trouble connecting to my database. I use the following code for connectivity.

public class dbOpnClse
{
    SqlConnection con = new SqlConnection();
    public SqlConnection openConnection()
    {
        con.ConnectionString ="server=SERVERNAME;database=Test;uid=###;pwd=#####";
        con.Open();
        return con;
    }
    public void closeConnection()
    {
        con.Close();
    }
}

The problem is that I am getting the exception "Not allowed to change the 'ConnectionString' property. The connection's current state is open." Is there any error in this connectivity code? Please help! I'm using C#.net 2005 and SQL Server 2000

Answer 1

Binu,

You have a variety of problems in your code. First, you should not embed your connection string in code - you should pull it from a Config file so that you can change it when you deploy. Second, don't log in to your database with the sa account!! Just don't do it - trust me on this one.

Third, I'd strongly recommend that you use an IDisposable interface and a Using block to ensure that your connection is always closed. As it is, you are depending on the consuming code to call closeConnection.

Try this sample code:

public class MyConnClass : IDisposable
{
    public static string ConnectionString { get; set; }
    protected SqlConnection conn;

    public MyConnClass()
    {
        conn = new SqlConnection(ConnectionString);
        conn.Open();
    }

    public void Dispose()
    {
        conn.Close();
    }
 }

In your initialization code (e.g. Global.asax.cs in a Web app) set the static connection string as follows:

    MyConnClass.ConnectionString = ConnectionString;

In your consuming classes you'll use something like:

using (MyConnClass myConn = new MyConnClass())
{

    // Database code

}

Update: The other thing I would suggest that you consider is to modify the above code so that it forms the basis for a Data Access Layer (DAL) and not just the Connection. That is, place a SQL command object in there as well and create code to simplify your overall data access strategy. That is, just putting the Connection in a separate class is a bit useless because, as Joel points out, SqlConnection objects are themselves Disposable. The advantage of a full blown DAL is that it can radically simplify your data access. For example, my DAL let's me write things like:

using (MyQueryClass myQuery = new MyQueryClass())
{
   myQuery.Command("Update...").ParamVal("@P1", 1).ParamVal("@P2", 2).Execute();
   return myQuery.Command("Select ...").ParamVal("@Param", SomeVal).ReturnList<SomeType>();
}

You won't end up with the class that I did but hopefully you can see a few things: the connection string is never an issue in my code (it is encapsulated), I can run multiple commands without all the set up and tear down usually required, and the overall syntax is more fluent.

Answer 2

My best guess is that the 2nd time you call openConnection you haven't closed it yet, and so you try to set the connectionstring property of an already open connection. This isn't allowed.

Instead, in .Net you want your openConnection()-style methods to actually create a new connection every time they're called, along the lines of the factory pattern, so that the connection can be properly disposed. Something more like this:

private SqlConnection CreateConnection()
{
    //even better if the connection string is pulled from a config file
    var result = new SqlConnection("Your connection string here");
    result.Open();
    return result;
}

There will be no corresponding "close" method. Instead, we'll rely on the IDisposable pattern to make sure the connection is always closed correctly. Then you will use that function and connection in a method like this:

public DataTable GetRecords(int SomeValue)
{
    var result = new DataTable();

    string sql = "SELECT * FROM [MyTable] WHERE [SomeIntColumn]= @SomeValue";

    using (var cn = CreateConnection() )
    using (var cmd = new SqlCommand(sql, cn) )   
    {
        cmd.Parameters.Add("@SomeValue", SqlDbType.Int).Value =  SomeValue;

        using (var rdr = cmd.ExecuteReader() )
        {
           result.Load(rdr);
        }
    }
    return result;  
}

The using statements work with any object that implements the IDisposable interface, and they guarantee your connection is closed even if an exception is thrown.

Also notice that I made the CreateConnection() method private. This is because this class will become your data access layer. By making the connection private, you will enforce a good db layer where the only way to talk to the db is to come through this class. Any code that needs to talk to the db should go in here. If this class starts to get too big you can mark this method as internal rather than private and move your data access layer into it's own assembly (separate project in Visual Studio). The key is now to limit your methods to accepting strongly-typed input for parameters. Don't try to build an generic method that lets you pass in the sql directly. That way lies madness.

Finally, NEVER NEVER NEVER connect to a database from your application via the sa account, and posting your sa account details to a public web site just isn't that bright.

Answer 3

You get this error because some of you code is calling openConnection a second time when the connection is already opened.

You should read this article on ADO.NET best practices

Answer 4

    namespace databaseOp 
{ 
public class dbOpnClse 
{ 
SqlConnection con; 

public SqlConnection openConnection() 
{ 
    //Try this...
    con = new SqlConnection("server=CHEMPAKASSERIL;database=Test;uid=sa;pwd=jeevan");

    //Not this...
    //con.ConnectionString ="server=CHEMPAKASSERIL;database=Test;uid=sa;pwd=jeevan"; 
    con.Open(); 
    return con; 
} 

public void closeConnection() 
{ 
    con.Close(); 
}

    }

}

Answer 5

Presumably you are trying to do just that (i.e. change the 'ConnectionString' property) elsewhere in your code.

It is best practice to open connections and close them again as soon as a block of code is finished using the connection (use the using statement), and let connection pooling take care of the connections.