How to change connection string for database class?

[little83]

Dear all

i wrote one class to deal with sql database to insert and read data from that class only...and based on that one of the parmeters or the class is the sqlconnection

SqlConnection connection = new SqlConnection(@ "Data Source=.\SQLEXP RESS;AttachDbFi lename= {0};Integrated Security=True;C onnect Timeout=30;User Instance=True") ;

so the connection string is defined indepented of the class objects of the class (i mean class objects used by the other class)

but I need to change the database file or I want to change the connection string

so how can I change the connection string for the whole class ...

What I did

i define the connection in the beginning of the class

SqlConnection connection = new SqlConnection() ;

then I wrote a method

public void conntstr(string r)
{
connection = new SqlConnection(r );
}

and I passed the connection string to it...however it doesn't worte?

what can I do

[little83]

ok I tried the following solution however i got error

ok
i tried the following solution

 
class sqldatabase
{
public static String connectionString;
SqlConnection connection = new SqlConnection(connectionString);
 
}
 

in my application I made the following

if (open.ShowDialog() == DialogResult.OK)
{
                connectionstr = string.Format(@"Data Source=.\SQLEXPRESS;AttachDbFilename= {0};Integrated Security=True;Connect Timeout=30;User Instance=True", open.FileName);
}                                 
sqldatabase.connectionString = connectionstr;
 

however I got the following error when I start to access my database

The ConnectionStrin g property has not been initialized.

any ideas?

[Plater]

Is your connection string following the formats listed here:
http://www.connectionstrings.com/sql-server-2005

It looks like you are missing the database name when attaching a db file?


It also looks like if you say cancel in your open file dialog, the connectionstrin g is still assigned to the object

[vekipeki]

First problem is that you shouldn't be creating a database connection just like that, "in the middle of nowhere".

Create a private string field which contains the connection string only, and then create a connection only when you need to do a transaction.

// add this to your class, but not as a static field
private string connectionString = null;
 
if (open.ShowDialog() == DialogResult.OK)
{
    // create the string using the specified name
    connectionString = CreateConnectionString(open.FileName);
}
 

Then, just before sending a SQL query, create your connection:

using (SqlConnection connection = new SQLConnection(connectionString)
{
    // do something with database
}

This way you are sure that SqlConnection will be closed and disposed after use. It is also obvious that you have to define your connection string before creating a connection.

The second problem is, the way it is implemented now, you expect from a caller to set that static field before creating a new instance of your 'sqldatabase' class. That is not a common practice in OOP, and you should always try to follow common programming patterns.

Third suggestion would be to try to follow C# naming conventions (you can google it, here is one example). Classes are never named in lower case (i.e. sqldatabase should be SqlDatabase), to distinguish them from variables and class fields.

[little83]

but I create the connection string in one class and the methods which deal with the sql database on other class...ur solution available if both on the same class..

[vekipeki]

but I create the connection string in one class and the methods which deal with the sql database on other class

Nevertheless, you need to pass the connection string to SqlConnection to create it. I didn't say that the second part of the code needs to be in the same class as the first part, but you need to have the connection string ready before creating SqlConnection (that's what the error is telling you, after all).

Your class doesn't enforce that in any way - you are creating an SqlConnection from a static string, without knowing if it was initialized first. If you wrote it like this:

class SqlDatabase
{
    /// <summary>
    /// Contains the connection string.
    /// THIS FIELD CAN ONLY BE INITIALIZED 
    /// IN CONSTRUCTOR.
    /// </summary>
    private readonly string _connectionString;
 
    public SqlDatabase(string connectionString)
    {
        _connectionString = connectionString;
    }
 
    public void DoSomeQueries()
    {
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            // do something
        }
    }
}

Now it is obvious that you can only create SqlDatabase object like this:

SqlDatabase sqlDatabase = new SqlDatabase(connectionString);

[little83]

thanks very much..now it works