Fluent SQL

Filespecs

A SQL Server database has at least two files, a primary file that holds the data and a transaction log file. When you create a database, these are created automatically if you don’t specify them, but you can optionally define their names, file names, initial size, maximum size, file growth increment.

The simplest Filespec constructor takes a name and filename.

var filespec = new Filespec("my_file", "/path/to/file.mdf");

(NAME = [my_file], FILENAME = N'/path/to/file.mdf')

You can instantiate a Filespec by itself, but you typically nest it inside database creation code.

var createStatement = Database.Create("MyDatabase")
    .On(new Filespec("my_file", "/path/to/file.mdf"))
    .LogOn(new Filespec("my_log", "/path/to/file.ldf"));

CREATE DATABASE [MyDatabase]
ON (NAME = [my_file], FILENAME = N'/path/to/file.mdf')
LOG ON (NAME = [my_log], FILENAME = N'/path/to/file.ldf');

Optional Size Properties

The optional properties, size, maximum size, and file growth increment, are added with the WithSize(), WithMaxSize(), and WithGrowthIncrement() methods. It is possible to chain the same method twice. If you do that, the property specified latest will be used.

WithSize() sets the initial size of the file. Its argument is an InformationSize, a unit type that specifies a size in bytes. You can construct an InformationSize object directly by giving it a size in bytes or, more fluently, you can use the InformationSize integer extension methods Bytes(), Kilobytes(), Megabytes(), Gigabytes(), Terabytes().

WithMaxSize() sets the maximum size of the file. It takes either an InformationSize or an Unlimited object, constructed with the static Unlimited.Value method.

WithGrowthIncrement() sets the amount the file grows by as it fills up. It can be specified either as an absolute InformationSize or as a Percentage. (Percent() is also used in the SELECT TOP clause.)

var createStatement = Database.Create("MyDatabase")
    .On(new Filespec("my_file", "/path/to/file.mdf")
        .WithSize(100.Megabytes())
        .WithMaxSize(10.Gigabytes())
        .WithGrowthIncrement(15.Percent()))
    .LogOn(new Filespec("my_log", "/path/to/file.ldf"));

CREATE DATABASE [MyDatabase]
ON (NAME = [my_file], FILENAME = N'/path/to/file.mdf', SIZE = 100MB, MAXSIZE = 10GB, FILEGROWTH = 15%)
LOG ON (NAME = [my_log], FILENAME = N'/path/to/file.ldf');