///<summary>Appends SQL statements to create a schema. The SqlCommand will have a SqlSchema parameter.</summary>
protected virtual void CreateSchema(DbConnection connection, SchemaMapping mapping)
{
if (connection == null) throw new ArgumentNullException("connection");
if (mapping == null) throw new ArgumentNullException("mapping");
connection.ExecuteNonQuery(@"
IF schema_id(@SqlSchemaName) IS NULL
EXECUTE('create schema ' + @EscapedSchemaName);",
new { mapping.SqlSchemaName, EscapedSchemaName = mapping.SqlSchemaName.EscapeSqlIdentifier() }
);
}
static void CreateCallListSheet(DbConnection connection, string sheetName, IEnumerable<MelaveMalkaInvitation> callees)
{
Program.LoadTable<MelaveMalkaSeat>();
connection.ExecuteNonQuery(@"
CREATE TABLE [" + sheetName + @"] (
[Last Name] NVARCHAR(128),
[His Name] NVARCHAR(128),
[Her Name] NVARCHAR(128),
[Address] NVARCHAR(128),
[Phone] NVARCHAR(128),
[Caller] NVARCHAR(128),
[Last Year] NVARCHAR(64),
[Seats] NVARCHAR(64)
);");
foreach (var callee in callees.OrderBy(s => s.Person.LastName)) {
var person = callee.Person;
connection.ExecuteNonQuery(
@"INSERT INTO [" + sheetName + @"]
([Last Name], [His Name], [Her Name], [Address], [Phone], [Caller], [Last Year], [Seats])
VALUES (@LastName, @HisName, @HerName, @Address, @Phone, @Caller, @LastAds, @LastSeats);",
new {
person.LastName,
person.HisName,
person.HerName,
person.Address,
person.Phone,
Caller = (callee.Caller == null) ? "(none)" : callee.Caller.ToString(),
LastAds = person.Pledges
.Where(p => p.ExternalSource == "Journal " + (callee.Year - 1))
.Select(p => Names.AdTypes.FirstOrDefault(a => a.PledgeSubType == p.SubType))
.Select(t => t == null ? "(other)" : t.Name) // Handle custom pledges with unrecognized subtypes
.DefaultIfEmpty(person.Invitees.Any(i => i.Year == callee.Year - 1) ? "(no ad)" : "(not invited)")
.Join(", "),
LastSeats = person.MelaveMalkaSeats
.Where(s => s.Year == callee.Year - 1)
.Select(s => "M: " + s.MensSeats + ", " + "W: " + s.WomensSeats)
.FirstOrDefault() ?? ""
}
);
}
}
///<summary>Creates a table for the given schema mapping.</summary>
///<remarks>In addition to the columns in the SchemaMapping, a RowVersion column will be created.</remarks>
public void CreateTable(DbConnection connection, SchemaMapping schema, IEnumerable<SchemaMapping> parentSchemas)
{
if (connection == null) throw new ArgumentNullException("connection");
if (schema == null) throw new ArgumentNullException("schema");
CreateSchema(connection, schema);
var sql = new StringBuilder();
//CREATE TABLE [SchemaName].[TableName] (
// [FirstColumn] TYPE NOT NULL,
// [SecondColumn] TYPE NULL,
// [RowVersion] RowVersion
//);
sql.Append("CREATE TABLE ").Append(QualifyTable(schema)).AppendLine("(");
foreach (var column in schema.Columns) {
if (column == schema.PrimaryKey)
AppendPrimaryKey(sql, column);
else if (column.Column is ForeignKeyColumn)
AppendForeignKey(sql, column, parentSchemas);
else
AppendColumn(sql, column);
sql.AppendLine(","); //Even the last column gets a comma, because of the RowVersion column
}
sql.AppendLine("\t[RowVersion]\t\tRowVersion");
sql.AppendLine(");");
connection.ExecuteNonQuery(sql.ToString());
}