Specifying ON FILENAME for CreateDatabase

[jnm2]

I think this setting fits best in a CreateDatabaseSettings object.

Right now, I only need to be able to specify a single filename and allow SQL server to auto-generate the log filename beside it.

Should it be as simple as this?

+public sealed class CreateDatabaseSettings
+{
+    public string FileName { get; set; }
+}
+
+public void CreateDatabase(string connectionString, string databaseName, CreateDatabaseSettings settings)

Or should we plan ahead for the potential to add a LogFileName which requires you to add a FileName and try to be expose that semantically in the API?

+public sealed class CreateDatabaseSettings
+{
+    public string PrimaryFileName { get; }
+    public string LogFileName { get; }
+
+    public CreateDatabaseSettings WithFileNames(string primaryFileName);
+
+    public CreateDatabaseSettings WithFileNames(string primaryFileName, string logFileName);
+}

What about additional filenames? Is it worth coming up with an API that's compatible with that?

[trailmax]

Sounds like you want to open Pandora's box here. According to the doc there are endless combinations of options.

Something like that terrifies me:

CREATE DATABASE Sales  
ON PRIMARY  
( NAME = SPri1_dat,  
    FILENAME = 'D:\SalesData\SPri1dat.mdf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 15% ),  
( NAME = SPri2_dat,  
    FILENAME = 'D:\SalesData\SPri2dt.ndf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 15% ),  
FILEGROUP SalesGroup1  
( NAME = SGrp1Fi1_dat,  
    FILENAME = 'D:\SalesData\SG1Fi1dt.ndf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 ),  
( NAME = SGrp1Fi2_dat,  
    FILENAME = 'D:\SalesData\SG1Fi2dt.ndf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 ),  
FILEGROUP SalesGroup2  
( NAME = SGrp2Fi1_dat,  
    FILENAME = 'D:\SalesData\SG2Fi1dt.ndf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 ),  
( NAME = SGrp2Fi2_dat,  
    FILENAME = 'D:\SalesData\SG2Fi2dt.ndf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 )  
LOG ON  
( NAME = Sales_log,  
    FILENAME = 'E:\SalesLog\salelog.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 25MB,  
    FILEGROWTH = 5MB ) ;  
GO  

I'm not really sure I'd like to support this monster: multiple filegroups with multiple files, PRIMARY, non-primary, LOG ON, etc. I don't understand any of those, don't think I'll be able to produce coherent SQL out of C# classes.

[jnm2]

What if we added only this to the public API and keep the properties internal so that you can swap them out for complex types if anyone ends up asking for them? Even with complex options for each file, I'd still want to use this overload:

+public sealed class CreateDatabaseSettings
+{
+    public CreateDatabaseSettings WithFileNames(string primaryFileName);
+}

[trailmax]

@jnm2 Yeah, OK. We can try that.

Only I'd be specific about filenames and have this:

public sealed class CreateDatabaseSettings
{
    public CreateDatabaseSettings WithPrimaryFile(string primaryFileName);
    public CreateDatabaseSettings WithLogFile(string primaryFileName);
}

So if somebody needs this scenario

CREATE DATABASE Sales  
ON   
( NAME = Sales_dat,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\saledat.mdf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 )  
LOG ON  
( NAME = Sales_log,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\salelog.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 25MB,  
    FILEGROWTH = 5MB ) ;  
GO  

we can easily extend to

public sealed class CreateDatabaseSettings
{
    public CreateDatabaseSettings WithPrimaryFile(FileSpec primaryFileOptions);
    public CreateDatabaseSettings WithLogFile(FileSpec logFileOptions);
}
public sealed class FileSpec
{
   public String Name { get; set; }
   public String FileName { get; set; }
   public String Size { get; set; }
   public String MaxSize { get; set; }
   public String FileGrowth { get; set; }
}

I'm not willing to go into further complexities of SQL - SQL can be created manually and executed via SqlCommand; trying to mimick SQL in C# objects might end in tears

[jnm2]

Cool. For the foreseeable future for me, the generated log name is good enough. I only care about:

+public sealed class CreateDatabaseSettings
+{
+    public CreateDatabaseSettings WithPrimaryFile(string primaryFileName);
+}

[trailmax]

@jnm2 Looking at this now (finally!). What SQL are you executing? According to spec you need to specify NAME and FILENAME parameters when you need to say where the mdf will be located. I.e.

create database Donno on (name = 'donno', filename= 'd:\tmp\donno.mdf')

If either of the names are missed, I get syntax error from SQL Server

[jnm2]

I've been doing exactly what you have there, but using the database name as the name option for the mdf.

[trailmax]

Implemented in PR #46