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

You can but I would just change the connection to point to the database you want to query.

Clarification:
To clarify, you can do it using dynamic sql. e.g. passing the database name to a sproc which can then build up the complete statement and execute it. Of course, it's invalid to use a variable directly ("SELECT * FROM [@DatabaseName].dbo.TableName" is NOT valid). However, this would not be the recommended approach, hence best thing is to change the connection.

Answer 3

Try something like the DBProviderFactory instead

Answer 4

multiple Connections strings are your friend in this case!

Answer 5

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

Answer 6

No, T-SQL parameters cannot refer to an entity (such as a database or table), if for no other reason than it'd blow the optimizer's mind.

If just the DB name is different, the suggestion of dynamically building the connection string is a good one. You could use the SqlConnectionStringBuilder class to make that less error-prone.

You could also just build up a C# string dynamically, but be careful about opening yourself up to Sql injection attacks.

Otherwise, just do an IF statement in your T-SQL code:

IF (@dbName = 'Database1') BEGIN
   SELECT SomeValue FROM [Database1].[dbo].[Klienci]
END ELSE BEGIN
   SELECT SomeValue FROM [Database2].[dbo].[Klienci]
END

Answer 7

If your schemas are identical, you can change database context by sending the USE statement at the beginning of your query:

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

The USE can't be parameterized but should be safe to add since there's no user-supplied data. Once it's added, you can handle the rest of the query as a prepared statement.

This approach is useful if you're equally using many, many databases. Many unique connectionstrings can mess with your ConnectionPool. They make it more likely the pool won't have the connection you need and then have to establish a new connection while throwing out another pooled connection. The USE statement let's you query across databases with a single connectionstring.

Don't use this option if you only have a few databases. It has drawbacks. Specifically, it flattens your security model. For it to work, a single user must have access to all of your databases. Even with a couple hundred databases, it's probably better to increase your ConnectionPool's Max Pool Size and use unique connectionstrings.