execute SQL script with USE DATABASE in C#

Question

My query file looks like this:

USE DB_A
GO
SELECT * FROM sch.table;

but my connection string, because of some reason, should be forced to set as @"Data Source=SERVER;Initial Catalog=DB_B; ......"

As ExecuteNonQuery() doesn't support GO, this query will be separated into two parts, i.e, actual code looks like this:

OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = ConnString;
OleDbCommand cmd = new OleDbCommand();

conn.open();    
cmd.ComandText = "USE DB_A";
cmd.ExecuteNonQuery();
cmd.ComandText = "SELECT * FROM sch.table";
cmd.ExecuteNonQuery();
conn.close();

it seemsUSE DATABASE doesn't work, it is still reporting errors cannot find object sch.table when executing.

is there any neat solution other than changing my query file or my connection string?

If you connection strings point to particular db then you dont need to use command like `USE DB_A`. just remove that and test it — er-sho, Aug 17 '18 at 06:26
As it is pointed out at https://stackoverflow.com/questions/1949774/what-is-the-point-of-initial-catalog-in-a-sql-server-connection-string, Initial Catalog can be used to specify the database you want to execute your queries. So once you have specified the correct DB there, you don't have to make use of `USE DB_A`. — Christos, Aug 17 '18 at 06:28
you can try with transaction for your first use database query and commit , then try executing your select . Not tested though, just an idea — Sarav, Aug 17 '18 at 06:29
@Christos But he said that thec onnection string should refer to `DB_B`. — Chayim Friedman, Aug 17 '18 at 06:31

score 0 · Answer 1 · answered Aug 17 '18 at 06:40

0

As you've noticed USE doesn't work here. What you can do is fully reference your objects. So instead of

SELECT * FROM sch.table

write

SELECT * FROM DB_A.sch.table

Note that this will only work if you can access DB_A from DB_B with the same credentials you've used to connect to DB_B. If this is not the case you'll have to use a seperate connection to DB_A.

answered Aug 17 '18 at 06:40

Bill Tür stands with Ukraine

3,425
30
38
48

'other than changing my query file' is mentioned; the file content is not controllable and changable in my side, the only thing is expected is that file can be executed normally in the SQL server client. – a4194304 Aug 17 '18 at 07:03
I'm a bit confused. Where in your code do you use a query file? – Bill Tür stands with Ukraine Aug 17 '18 at 07:22

score 0 · Answer 2 · answered Aug 17 '18 at 07:16

0

A Solution available is just remove the initial catalog Initial Catalog=DB_B from connection string. Not a perfect solution to this question, but worked in my case.

answered Aug 17 '18 at 07:16

a4194304

366
2
13

execute SQL script with USE DATABASE in C#

2 Answers2