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

Question

I have 2 tables with the same columns. But they are stored in Database1 and Database2 (on the same server). Can I pass the database name as a variable? For instance:

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

Is there a way to pass the integer [FROM] as @variable through the following code in C #:

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

Where @varDatabase will contain the database name and / or table name, i.e. [Database1]. [Dbo]. [Klienci] in one format or another.

I am talking about C # 3.5 / MSSQL 2005/2008.

Answer 1

Why don’t you just install your database when connecting? Then the code does not even change.

Answer 2

It looks like you need to dynamically set the database name and connection string depending on the type of query. Even if you have, say (for example, 60) Databases that you can connect to using hard-coded statements, this is something that no one ever recommends because

• Hard to write
• Hard to manage
• Hard to upgrade
• You can use a separate code file for that, but it's kind of reinventing the wheel.

The configuration files serve this purpose along with many others and think about your colleagues for a second, looking at this “ CustomConnectionStringsFile ” and scratching their head.

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

Something like that

Adding a Web.Config Connection String

 <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> 

Connector Access

  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 information about

• How to read connection strings
• Set connection strings

Hope this helps

Answer 3

A few lines of connections is your friend in this case!

Answer 4

No, T-SQL parameters cannot refer to an entity (for example, a database or a table), unless for any other reason than to smooth the optimizer.

If only the database name is different, the suggestion of dynamically constructing the connection string is good. You can use the SqlConnectionStringBuilder class to make this less error prone.

You can also just create a C # line dynamically, but be careful to open yourself before Sql injections.

Otherwise, just enter the 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 5

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

Clarification:
To clarify, you can do this with dynamic sql. for example, passing the database name to sproc, which can then create a complete statement and execute it. Of course, it is incorrect to use the variable directly ("SELECT * FROM [@DatabaseName] .dbo.TableName" IMPOSSIBLE). However, this will not be the recommended approach, so it is best to change the connection.

Answer 6

Try something like DBProviderFactory instead