Passing database name as variable to SQL from C#. Is it possible?

Question

I've 2 tables having exactly same columns. But they are stored in Database1 and Database2 (on same server). Can i pass database name as variable? For example:

                        SELECT  [SomeValue]
                        FROM    [Database2].[dbo].[Klienci]

                        SELECT  [SomeValue]
                        FROM    [Database1].[dbo].[Klienci]

Is there a way to pass whole [FROM] as @variable thru following code in C#:

            SqlCommand sqlQuery = new SqlCommand(preparedCommand, varConnection);
            sqlQuery.Prepare();
            sqlQuery.Parameters.AddWithValue("@varDatabase", varDatabase);

Where @varDatabase would hold database name and/or table name ie. [Database1].[dbo].[Klienci] in one format or another.

I'm talking about C# 3.5 / MSSQL 2005/2008.

Answer 1

Why don't you just set your database when you make the connection? Then the code doesn't even change.

Answer 2

It looks you are need to set the database name and the connection string, dynamically, depending upon type of request. Even if you have, say (just for instance) 60 Databases, you might connect to, using hard coded statements is what no one will ever recommend, because

• It is hard to write
• It is hard to manage
• It is hard to update
• You can use a separate code file for that but it is some thing like reinventing the wheel.

Configuration files serve this purpose, along with many others, and think of your colleagues for a second, looking at this "CustomConnectionStringsFile" and scratching their head.

The best way is to save them as connectionStrings, in your configuration file and use the one you need

Something as this

Adding Connection string to Web.Config

<connectionStrings>
  <add 
    name="NorthwindConnectionString" 
    connectionString="Data Source=serverName;Initial 
    Catalog=Northwind;Persist Security Info=True;User 
    ID=userName;Password=password"
    providerName="System.Data.SqlClient"
  />
  <add 
    name="BestDBConnectionString" 
    connectionString="Data Source=serverName;Initial 
    Catalog= BestDB;Persist Security Info=True;User 
    ID=userName;Password=password"
    providerName="System.Data.SqlClient"
  />
</connectionStrings>

Accessing Connectionstring

    string myConnString ="";
    if(ThisIsThat("A"))
    {
        myConnString = 
                               rootWebConfig.ConnectionStrings.ConnectionStrings["NorthwindConnectionString"];
    }
    else if(ThisIsThat("B"))
    {        
        myConnString =
                                rootWebConfig.ConnectionStrings.ConnectionStrings["BestDBConnectionString"]
    }

    { else // Can go on}

For more info on

• How to read Connection Strings
• Set Connection Strings

Hope it helps