Create database with variable name in sql server

Question

This must be very simple but I can't figure it out, or maybe it is not possible.

I have the next function:

private static bool createDB(SqlConnection dbConn, string dbName)
{
    string sqlString = "CREATE DATABASE @dbname";
    using (dbConn)
    {
        using (SqlCommand cmd = new SqlCommand(sqlString, dbConn))
        {
            cmd.Parameters.AddWithValue("@dbname", dbName);
            cmd.CommandType = CommandType.Text;
            dbConn.Open();
            try
            {
                cmd.ExecuteNonQuery();
                MessageBox.Show("Se creo la DB");
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("No se creo la DB");
                return false;
            }
            finally
            {
                //dbConn.Close();
            }
        }
    }
}

But apparently the @dbname is not getting the value, dbName does gets the name I want when I call it, but the exception says incorrect syntax near '@dbname'.

I'm new to C#, please be nice :) I got this from many other posts with prepared statements, but I couldn't find any with a CREATE DATABASE, but I'm assuming this should be very similar.

Tsukasa · Accepted Answer · 2014-02-27 20:14:01Z

3

You aren't allow to do that. Database names and field names will not work this way.

string sqlString = "CREATE DATABASE " + dbname";

Only parameters are allow. Example

string sqlString = "update test set myField = @myVal"

you can then use

cmd.Parameters.AddWithValue("@myVal", yourVar);

You also don't need to add @ in Parameters.AddWithValue as it's just implied already.

You always want to add parameters with Parameters.AddWithValue to avoid people from escaping and performing sql injection.

edited Feb 27, 2014 at 20:14

answered Feb 27, 2014 at 20:08

Tsukasa

6,58216 gold badges71 silver badges105 bronze badges

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

Comments

T McKeown · Accepted Answer · 2014-02-27 20:16:23Z

2

You don't need to use SqlParameters for this, just add the dbName variable to your command text.

private static bool createDB(SqlConnection dbConn, string dbName)
{
    string sqlString = "CREATE DATABASE " + dbname;
    using (dbConn)
    {
        using (SqlCommand cmd = new SqlCommand(sqlString, dbConn))
        {
            cmd.CommandType = CommandType.Text;

            dbConn.Open();
            try
            {
                cmd.ExecuteNonQuery();
                MessageBox.Show("Se creo la DB");
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("No se creo la DB");
                return false;
            }
            finally
            {
                //dbConn.Close();
            }
        }
    }
}

As a side note I wouldn't put a messagebox (I assume this is test code) in a CRUD method like this not to mention this leaves the db connection open until the messagebox is acknowledged.

edited Feb 27, 2014 at 20:16

answered Feb 27, 2014 at 20:08

T McKeown

12.9k1 gold badge27 silver badges35 bronze badges

1 Comment

Lauro182 Over a year ago

Sorry man, your answer didn't explain anything before, that's why I marked Tsukasa answer as the answer, and yes, my messageboxes are only for testing purpose. But I did marked it as useful, thanks.

Chris Noffke · Accepted Answer · 2014-02-27 20:44:22Z

2

If you must "paramertize" the database name, then I'd suggest trying something like this...

string sqlString = string.Format("CREATE DATABASE {0}", dbName.Trim().Replace(" ",""));

It will also help guard against SQL injection, help not prevent, but at least you'd be ok with the littlebobbytables exploits.

answered Feb 27, 2014 at 20:44

Chris Noffke

3171 silver badge7 bronze badges

2 Comments

Chris Noffke Over a year ago

tough crowd... didn't even get a bump for the little bobby tables reference?

Lauro182 Over a year ago

Lol, little bobby tables was awesome.

Collectives™ on Stack Overflow

Create database with variable name in sql server

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related