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.

Cade Roux · Accepted Answer · 2010-01-27 21:32:09Z

7

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

answered Jan 27, 2010 at 21:32

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MadBoy Over a year ago

Don't know why i haven't thought about that. I already have defined 2 connection strings for 2 databases and i am unnecessary writing FROM [Database2].[dbo].[Klienci] when i could just use FROM [Klienci] since connection string would make the choice for me.

Asad · Accepted Answer · 2010-01-27 22:02:21Z

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

Hope it helps

sdmiller · Accepted Answer · 2010-01-27 21:34:30Z

1

multiple Connections strings are your friend in this case!

answered Jan 27, 2010 at 21:34

sdmiller

4032 gold badges6 silver badges18 bronze badges

Comments

Mark Brackett · Accepted Answer · 2010-01-27 22:09:41Z

1

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

answered Jan 27, 2010 at 22:09

Mark Brackett

85.9k17 gold badges113 silver badges157 bronze badges

Comments

Paul Creasey · Accepted Answer · 2010-01-27 21:34:30Z

0

Try something like the DBProviderFactory instead

answered Jan 27, 2010 at 21:34

Paul Creasey

28.9k10 gold badges60 silver badges91 bronze badges

Comments

AdaTheDev · Accepted Answer · 2010-01-27 21:48:21Z

0

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.

edited Jan 27, 2010 at 21:48

answered Jan 27, 2010 at 21:32

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

3 Comments

Cade Roux Over a year ago

Actually, you can't do what he's doing. No part of an entity identifier in a SQL statement is allowed to be a variable. You could build the statement dynamically, but it cannot be parametrized.

John M Gant Over a year ago

You could, but you'd have to do an exec in the stored proc, which generally defeats the purpose of a stored proc.

AdaTheDev Over a year ago

@Cade Roux - I know, but you can if you use dynamic sql e.g. I envisaged passing the variable into a sproc, which then builds up the statement dynamically, But I didn't go into that as I don't think it's the right approach

Collectives™ on Stack Overflow

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

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related