CODEWITHSUNDEEP
c#sqlsql-servert-sqlado.net
Lauro182
Lauro182

Reputation: 37

Create database with variable name in sql server

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.

Upvotes: 0

Views: 833

Answers (3)

Chris Noffke
Chris Noffke

Reputation: 317

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.

Upvotes: 2

T McKeown
T McKeown

Reputation: 12857

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.

Upvotes: 2

Tsukasa
Tsukasa

Reputation: 6562

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.

Upvotes: 3

Related Questions