2

I'm trying to create a program that can set up its own database without any need for external setup scripts. I found this question on programmatically creating a SQL Server database, but it implicitly assumes you already have a server to connect to.

If I don't, how do I create it programmatically?

const SERVER_NAME = "MyDbServer";

private DbConnection GetConnection() {
   var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
   return new SqlConnection(connectionString);
}

public void Test(){
   try {
      //assume this is valid and calls GetConnection() internally
      var _ = Query<int>('select count(*) from USERS', null);
   } catch (System.Data.SqlClient.SqlException) {
      DO SOMETHING HERE TO CREATE THE SERVER
      var conn = GetConnection();
      var command = SqlCommand('create database MyDatabase;', conn);
      conn.Open();
      command.ExecuteNonQuery();
   }

What do I need to put in the "DO SOMETHING HERE TO CREATE THE SERVER" section?

EDIT: As noted in the title, this is a local server I'm trying to create. For the purposes of this question, assume that SQL Server's SqlLocalDB is installed on this computer, but no local server exists with the name I want to use.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mason Wheeler
  • 82,511
  • 50
  • 270
  • 477
  • 7
    You want to provision a SQL server instance, using the correct disk layout, backup settings and user accounts, including getting a license, from your exception handler in a C# program? – gnud Sep 03 '18 at 20:53
  • 2
    Can I humbly suggest you make your application support SQL Server Compact edition, and ask users without a database server to configure the connection string for Compact edition? Then you can create the database like in this question: https://stackoverflow.com/questions/1487845/create-sql-server-ce-database-file-programmatically – gnud Sep 03 '18 at 20:58
  • There is no easy way to setup a Mssql database since there are many configurations needed. O could recommend you to try docker and the sql server 2017 image, if you have docker available (https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017), if not, i would recommend a setup step to install "by hand" the server, since, the logic to create the server inside your code it will be very hard to create, and the advantage will be only in the first time is run – rekiem87 Sep 03 '18 at 20:58
  • @gnud As the title says, I want to set up a *local* SQL Server instance. (ie. one where the connection string says `server=(localdb)`) This cuts the complexity way down. – Mason Wheeler Sep 03 '18 at 21:12
  • https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-2017 – rene Sep 03 '18 at 21:12
  • 2
    Use SqlLite? It is not SQL server but it does not need SQL server. It's in-file database that you can use. – Shoter Sep 03 '18 at 21:16
  • this is probably better: https://stackoverflow.com/a/30479537/578411 – rene Sep 03 '18 at 21:17
  • @rene That explains how to install the server software, but not how to create a named server on localdb. I'm testing this code on a system that has localdb already installed, but I still can't connect to a server that's not there. The thing I need is a way to create the server. – Mason Wheeler Sep 03 '18 at 21:21
  • 3
    you run sqllocaldb: https://learn.microsoft.com/en-us/sql/tools/sqllocaldb-utility?view=sql-server-2017 with the `create` option? – rene Sep 03 '18 at 21:23
  • @rene Yes, I know about that too, but that's not a programmatic solution; that's something you do from outside of the program. – Mason Wheeler Sep 03 '18 at 21:24
  • You can run that process, right? – rene Sep 03 '18 at 21:25
  • It doesn't look like there is an API that will do that for you but I guess you could do reverse engineer action as I expect it just adds a regular windows service. – rene Sep 03 '18 at 21:28
  • You could use an ini file and do this by running the setup.exe of the sql server installation files. Not clear if that is acceptable. https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-using-a-configuration-file?view=sql-server-2017 – Nachi Sep 03 '18 at 22:01
  • Possible duplicate of [Create SQL Server CE database file programmatically](https://stackoverflow.com/questions/1487845/create-sql-server-ce-database-file-programmatically) – Mick Sep 04 '18 at 04:58

2 Answers2

1

I have done this before, and while it is absolutely possible it isn't trivial and you are bound to run into many edge cases as you install on different machines.

You need the standalone installer for SQL Server (grab the single file installer for SQL Express), then use the article Install SQL Server from the Command Prompt as your guideline for which options to specify for the install.

slugster
  • 49,403
  • 14
  • 95
  • 145
0

Pretty sure that creating a LocalDb "server" is automatic: https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/

LocalDB doesn't create any database services; LocalDB processes are started and stopped automatically when needed. The application is just connecting to "Data Source=(localdb)\v11.0" and LocalDB process is started as a child process of the application. A few minutes after the last connection to this process is closed the process shuts down.

That is to say when you use LocalDb you're not technically running off a server at all - you're just using sqlserver.exe to do database things. Therefore, to answer your question, you don't need to put anything in the "DO SOMETHING HERE TO CREATE THE SERVER" section.

duckbenny
  • 604
  • 7
  • 7
  • If this is the answer it would make the question a duplicate of... https://stackoverflow.com/questions/1487845/create-sql-server-ce-database-file-programmatically – Mick Sep 04 '18 at 05:01
  • I might add the answers to https://stackoverflow.com/questions/1487845/create-sql-server-ce-database-file-programmatically are far more complete than what you've posted – Mick Sep 04 '18 at 05:03
  • 1
    Except that answer refers to SQL Server Compact Edition which is completely different from LocalDb. – duckbenny Sep 04 '18 at 05:11