Using c# string variable with a SQL statement

Question

1.00/5 (2 votes)

See more:

Hello, in C#, i want to use a string variable inside a SQL statement yet i get error.

C#

//...
SqlCommand cmd = new SqlCommand(@"USE " + DatabaseNameString + @" GO", connectionString);
connectionString.Open();
SqlDataReader rdr = cmd.ExecuteReader();//Gets Exception here
//...

Any ideas how to fix this? Appreciating the help.

Posted 9-Sep-14 3:00am

YourAverageCoder

Updated 9-Sep-14 3:02am

v3

Add a Solution

Comments

[no name] 9-Sep-14 9:03am

Yes and what kind of result would you expect a SqlDataReader to return from that command?

YourAverageCoder 9-Sep-14 9:06am

Nothing. I just want C# code to tell SQL Server to use the database i specify in DatabaseNameString.

[no name] 9-Sep-14 9:11am

Then why are you using classes that return results if you do not expect any result?

YourAverageCoder 9-Sep-14 9:12am

Google first page tutorials all use Commands with SqlDataReader. It got stuck. You're right.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2014-09-09T03:04:00

Solution 1

You SQL statements make no sense and don't return anything.

All you did was say "USE thisDatabase GO", which doesn't make any sense in SQL. You didn't execute any query at all.

What are you trying to do??

Posted 9-Sep-14 3:04am

Dave Kreskowiak

Comments

YourAverageCoder 9-Sep-14 9:09am

In SQL Server "USE <databasename> GO" contructs a batch that tells to use the said database name for every default action unless told otherwise. Like i can say "SELECT * FROM tableName" instead of "SELECT * FROM DatabaseName.dbo.tableName"

Dave Kreskowiak 9-Sep-14 9:21am

You're actually telling your SQL Server session to "USE DATABASE". But, first, your command is invalid because it should be "USE myDatabase; GO". And your command is completely useless because once the connection is closed, so is your session. Any future commands don't use that database.

You really don't even have to say "USE myDatabase" at all because your connection string specifies the database you're going to use.

Again, WHAT ARE YOU TRYING TO DO WITH ALL THIS??

YourAverageCoder 9-Sep-14 9:25am

I was just trying to fix the exception i get. I know you're right that code in itself is meaningless. It's just testing if i can use C# string variable with SQL statement as SqlCommand parameter. That's all.

Dave Kreskowiak 9-Sep-14 9:36am

Yes, you can. Your SQL command is nothing but a normal string. It just happens to contain text that an SQL server recognizes.

Richard Deeming · Accepted Answer · 2014-09-09T03:24:00

You can't use GO in a SqlCommand; it's a batch separator which is only recognized by SQL Server Management Studio and associated command-line tools. With ADO.NET, you just issue each batch as a separate command.

The command you're trying to execute doesn't return any records. Therefore, ExecuteReader is the wrong method to use. Try ExecuteNonQuery instead.

And finally, if you just want to change the current database for a SqlConnection, try the ChangeDatabase method[^]:

C#

using (SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE"))
{
    connection.Open();
    connection.ChangeDatabase(DatabaseNameString);
    ...
}