Unable to create database using parameter for database name SQL Server & C#

Question

I am trying to use this code:

var connString = "Server=localhost\\SQLEXPRESS;Integrated Security = SSPI; database = master";
string cmdText = "CREATE DATABASE @userDatabase";

using (var sqlConnection = new SqlConnection(connString))
{
    using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
    {
        sqlCmd.Parameters.Add("@userDatabase", System.Data.SqlDbType.NVarChar).Value = databaseName;

        sqlConnection.Open();
        sqlCmd.ExecuteNonQuery();
    }
}

I get an error on sqlCmd:

'Incorrect syntax near '@userDatabase'

However, when I add the database using dynamic SQL code, I get no errors and the query runs perfectly (I heard that will be dangerous though).

Yes this is a well known 'problem' Parameters cannot be used to represent tables, columns or database names — Steve, May 18 '18 at 15:13
so what about the security then if textbox can be used to insert text into the create database query? — positive perspective, May 18 '18 at 15:14
You shouldn't let your user type these sensitive things. Perhaps you could explain why you need this functionality. Perhaps we could find a logical way to create the database name without involving a user that types the name — Steve, May 18 '18 at 15:15
It's pretty rare to allow users to create databases willy-nilly. — LarsTech, May 18 '18 at 15:15
If you need to create a database programmatically based on an input, and can tolerate the reference, use the SMO library, which has a Database.Create method — Andrew, May 18 '18 at 15:17
I was adviced to create separate database for each user rather than adding username identifier to records in shared database. Is that wrong? — positive perspective, May 18 '18 at 15:29
It is difficult to answer to your last comment without understanding your problem. Generally creating a lot of identical database has its drawbacks (think to keeping them updated with the schema changes) — Steve, May 18 '18 at 15:33
the structure is the same(Tables, columns etxc). Only data inserted changes. I am still on initial phase of sql part. I can still make a decision. But, since I already use SQLite for each user's client app, it is for me less messy merging the SQLite data with SQL. That is probably the main reason why I opted for designated database for each user. And then, if one user is down, it does not have to affect all the others. — positive perspective, May 18 '18 at 15:34
You have to create a 'Dynamic SQL' script and EXEC (@sql) or you have to have sqlcmd mode enabled. You can find endless examples by searching 'SQL variable server name' or table name, or sqlcmd mode, or 'dynamin sql examples'. — user7396598, May 18 '18 at 16:12
Possible duplicate of [Using Parameters.AddWithValue to pass database name fails](https://stackoverflow.com/questions/23667713/using-parameters-addwithvalue-to-pass-database-name-fails) — Tab Alleman, May 18 '18 at 17:36
Does this answer your question? [How can I do something like: USE @databaseName](https://stackoverflow.com/questions/3788566/how-can-i-do-something-like-use-databasename) — Wai Ha Lee, Jun 24 '21 at 10:02

score 0 · Answer 1 · answered May 18 '18 at 19:50

You can create a dynamic query to perform this.

private void SetupDatabase(string dbFolder, string dbName)
{
  if (Directory.Exists(String.Format("{0}\\db", dbFolder)) == false)
  {
    Directory.CreateDirectory(String.Format("{0}\\db", dbFolder));
  }
  if (File.Exists(String.Format("{0}\\db\\{1}_Data.mdf", dbFolder, dbName)) == false)
  {
    ExecuteQuery(
      "master",
      String.Format("CREATE DATABASE {1} ON PRIMARY (NAME = {1}_Data, FILENAME = '{0}\\db\\{1}_Data.mdf', SIZE = 2MB, FILEGROWTH = 10%) LOG ON (NAME = {1}_Log, FILENAME = '{0}\\db\\{1}_Log.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%)", dbFolder, dbName));
  }
}

private void ExecuteQuery(
  string db,
  string query)
{
  SqlConnection connection = null;
  try
  {
    connection = new SqlConnection(string.Format(connectionString, db));
    connection.Open();

    // Creates DB
    using (SqlCommand command = new SqlCommand(query, connection))
    {
      command.ExecuteNonQuery();
    }
  }
  catch (Exception ex)
  {
    throw ex;
  }
  finally
  {
    connection.Close();
  }
}

Unable to create database using parameter for database name SQL Server & C#

1 Answers1