Documentation Home

Setup

There are multiple ways to set up the use of this library. Below are the various ways to set it up.

Using Configuration (Recommended)

This is the recommended way to set up the library. It allows you to set up the library through configuration such as appsettings.json, which can be more maintainable and easier to manage in different environments.

{
  "ConnectionStrings": {
    "Default": "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True;"
  },
  "SqlObjectsForSqlServerOptions": {
    "Databases": {
      "Default": null
    }
  }
}

This is the easiest way to setup the library, which exhibits the following specs:

• The ConnectionStrings section is the standard place to configure connection strings. Typically, the connection string is a secret that is stored in a secure location such as Azure Key Valut etc. but for the sake of this example, we are including it in the appsettings.json file.

• The SqlObjectsForSqlServerOptions section is where you configure the options for the library. Under the Databases section, you can specify the name of the database and the configuration for that database. The name of the database should match the name of the connection string in the ConnectionStrings section. In this example, we are using Default as the name of the database and the connection string.

• A null value for the Default database section means that the library will use the connection string from the ConnectionStrings section with the same name. In this example, the library will use the connection string from the ConnectionStrings:Default section. It also means that the library will use the default schema, which is dbo. If you want to specify different schemas, you can do so by providing a configuration object instead of null.

If you don’t want to use the defaults, you can provide a configuration object for the database instead of null. This allows you to specify different connection strings and schemas for the database.

{
  "SqlObjectsForSqlServerOptions": {
    "Databases": {
      "Default": {
        "ConnectionString": "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True;",
        "Schemas": [ "dbo", "reporting" ]
      }
    }
  }
}

• If you omit the ConnectionString it will take from the ConnectionStrings section with the same name.

• If you omit the Schemas it will default to dbo

• If you use the Schemas property to set a different schema, such as reporting, then you must also explicitly add dbo to the list of schemas if you want to include it as well. The library does not include the default schema if you specify a different schema.

Register and Use the Library

using KnightMoves.SqlObjects.ForSqlServer;

...

// This works when using the configuration approach above
builder.Services.AddSqlObjectsForSqlServer(builder.Configuration);

...

var app = builder.Build();

...

// This loads the metadata for the databases in configuration
app.UseSqlServerObjectsForSqlServer();

...

app.Run();

Explicit Setup

If you don’t want to use the configuration approach, you can set up the library explicitly in code. This allows you to have more control over the setup process and can be useful in certain scenarios.

using KnightMoves.SqlObjects.ForSqlServer;

 ...

 var connStr = builder.Configuration.GetConnectionString("Default");

 var connStrBuilder = new SqlConnectionStringBuilder(connStr);

 // Instead of using the configuration approach you do the following
 builder.Services.AddSqlObjectsForSqlServer(options =>
 {
    options.Databases.Add(
      connStrBuilder.InitialCatalog,
      new DatabaseConfig
      {
        ConnectionString = connStrBuilder.ConnectionString,
        Schemas = [ "dbo" ]
      }
    );
 });

 ...

 var app = builder.Build();

 ...

 // This loads the metadata for the databases in configuration
 app.UseSqlServerObjectsForSqlServer();

 ...

 app.Run();

• If the connection string key is the same as the name of the connection string in the ConnectionStrings section, then you can omit the ConnectionString property in the configuration object and it will take it from the ConnectionStrings section. The connection string key in the example above is set to connStrBuilder.InitialCatalog. You can explicitly set it to something like Default to match the name of the connection string in the ConnectionStrings section if you want to use that feature.

• If you omit the Schemas property, it will default to dbo. If you want to specify different schemas, you can do so by providing a list of schemas in the configuration object.

Other

There are other ways to create the options object, which is of type SqlObjectsForSqlServerOptions and pass the options object to the AddSqlObjectsForSqlServer method.

Use the Factory Method to Pull from Configuration

...

var options = SqlObjectsForSqlServerOptions.Create(builder.Configuration);

builder.Services.AddSqlObjectsForSqlServerOptions(options);

...

Use the IConfiguration Extension Method to Pull from Configuration

...

// It will look for the "SqlObjectsForSqlServerOptions" section in configuration and bind it to the options object
var options = builder.Configuration.GetSqlObjectsForSqlServerOptions();

builder.Services.AddSqlObjectsForSqlServerOptions(options);

...

Use a Sub-Section of Configuration

You can put the SqlObjectsForSqlServerOptions section under a different section in configuration and use the factory method or extension method to pull it out.

For example, suppose you have separate sections for multi-tenancy.

{
  "TenantA": {
    "ConnectionStrings": {
      "Default": "Data Source=localhost;Initial Catalog=Northwind_A;Integrated Security=True;"
    },
    "SqlObjectsForSqlServerOptions": {
      "Databases": {
        "Default": null
      }
    }
  },
  "TenantB": {
    "ConnectionStrings": {
      "Default": "Data Source=localhost;Initial Catalog=Northwind_B;Integrated Security=True;"
    },
    "SqlObjectsForSqlServerOptions": {
      "Databases": {
        "Default": null
      }
    }
  }
}

You can pull the options for a particular tenant like this using the Factory Method:

...

var config = builder.Configuration.GetSection("TenantA").Get<SqlObjectsForSqlServerOptions>();

var options = SqlObjectsForSqlServerOptions.Create(config);

builder.Services.AddSqlObjectsForSqlServerOptions(options);

...

Or you can pull the options for a particular tenant like this using the IConfiguration Extension Method:

...

var options = builder.Configuration.GetSqlObjectsForSqlServerOptions("TenantA");

builder.Services.AddSqlObjectsForSqlServerOptions(options);

...