MVC create SQL database using SMOLite

Question

I am trying to create a new database from Visual Studio 2010 web setup project using custom action. First I made a From where the user select the name of their sql server and mode of authantication. I get the server name using the following:

public string ServerString { get; set; }
   private void SetServerString()
        {
            string cs;
            if (cbInstant.SelectedIndex == 0) // windows authentication
                cs = cbServer.SelectedValue.ToString();
            else
                cs = string.Format(cbServer.SelectedValue + ";" + txtUser.Text + ";" + txtPassword.Text);

            ServerString = cs;
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
              SetServerString();
              formContext.Parameters["ServerString"] = this.ServerString;
              this.Close();
        }

It works well and I get for example "PCName\SQLEXPRESS".

In the custom action:

public override void Install(System.Collections.IDictionary stateSaver)
        {
  string serverString = "";
            if (this.Context.Parameters["ServerString"] != null)
                serverString = this.Context.Parameters["ServerString"];
 base.Install(stateSaver);
            MakeSQLDatabase(connectionString);
}
   private void MakeSQLDatabase(string serverString)
        {
            FileInfo file = new FileInfo("M:\\script.sql");
            string script = file.OpenText().ReadToEnd();
            file.OpenText().Close();
            SqlConnection sqlConnection = new SqlConnection();
            SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
            sqlConnection.ConnectionString = sqlConnectionStringBuilder.ToString();
            Server server = new Server(sqlConnection);
            server.ConnectionContext.ServerInstance = (serverString);
            server.ConnectionContext.Connect();
            if (server.Databases["databasename"] != null)
            {
                server.KillAllProcesses("databasename");
                server.KillDatabase("databasename");
            }
            Database database = new Database(server, "databasename");
            database.Create();
            database.ExecuteNonQuery(script);
        }

I get an error "Server was not found or accessiable", however if I change the variable serverString to

@"PCName\SQLEXPRESS"

it works!! I can't figure out what is the problem. I could not use Microsoft.SqlServer.Management.Smo, because there is a conflict between version 2 and version 4. Even with adding app.confg

<?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
</configuration>

I would appreciate your assistance, or can you direct me to a better solution to achieve this task. Thanks in advance.

Answer 1

I don't get it, you want to create a new database with .NET 2 instead of .NET 4 ? I nerver used SMO for that but will try too :) Atm I just connect the master database a use SQL todo the job with "CREATE DATABASE". This works as long you have a valid connection to the "master" database.

UPDATE:

var sql = "";
using (var cnn = new SqlConnection(connection))
{
  cnn.Open();
  sql = "CREATE DATABSE <databasename>";
  using (var cmd = new SqlCommand(sql, cnn))
  {
    try
    {
      cmd.CommandTimeout = 120;
      cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
      Console.WriteLine("Execution error!\n\n" + sql + "\n\n\n" + ex);
    }
  }
  sql = "USE DATABSE <databasename>;EXEC sp_dbchangeowner <Username>";
  using (var cmd = new SqlCommand(sql, cnn))
  {
    try
    {
      cmd.CommandTimeout = 120;
      cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
      Console.WriteLine("Execution error!\n\n" + sql + "\n\n\n" + ex);
    }
  }
  cnn.Close();
}

connection has to be a connection to your "master" database. and need to be replaced. If you want to do this dynamic then something like:

sql = string.Format("USE DATABSE {0};EXEC sp_dbchangeowner {1}",database,username);

connection you set with your function you already have, using database name "master". Then you done and can setup any DB like this.

To check assistance in SQL you can add to your first SQL a:

IF ( (SELECT IsNull(db_id('latest'),-1) )>0 )

Hope this helps. I don't know if this is the smartest way, but in this case I can simple connect and then create, change, etc. anything I want. In my case I totally create a database, load a bunch of sql commands with stringbuilder and execute all CREATE table/column/query statements against the new database. Deleteing DB is a simple "DROP DATABASE".