///<summary>Creates a TableSynchronizer for the specified table.</summary>
public TableSynchronizer(Table table, SchemaMapping mapping, ISqlProvider sqlProvider)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (mapping == null)
{
throw new ArgumentNullException("mapping");
}
if (sqlProvider == null)
{
throw new ArgumentNullException("sqlProvider");
}
if (table.Schema != mapping.Schema)
{
throw new ArgumentException("Table and mapping must have the same schema", "mapping");
}
Table = table;
Mapping = mapping;
SqlProvider = sqlProvider;
Table.RowAdded += Table_RowAdded;
Table.ValueChanged += Table_ValueChanged;
Table.RowRemoved += Table_RowRemoved;
Changes = new ReadOnlyCollection <RowChange>(changes);
}
///<summary>Applies an inserted row to the database.</summary>
public override void ApplyInsert(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null) throw new ArgumentNullException("context");
if (schema == null) throw new ArgumentNullException("schema");
if (row == null) throw new ArgumentNullException("row");
var sql = new StringBuilder();
//INSERT INTO [SchemaName].[TableName]
// ([FirstColumn], [SecondColumn])
//VALUES(@Col0, @Col1);
sql.Append("INSERT INTO ").AppendLine(QualifyTable(schema));
sql.Append("\t(").AppendJoin(
schema.Columns.Select(c => c.SqlName.EscapeSqlIdentifier()), ", "
).AppendLine(")");
sql.Append("VALUES(").AppendJoin(
schema.Columns.Select((c, i) => "@Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).Append(");");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
if (command.ExecuteNonQuery() != 1)
throw new DataException("INSERT command didn't work");
}
RefreshVersion(context, schema, row);
}
public void ColumnEventsTest()
{
var schema = new TableSchema("ColumnEventsTest");
schema.Columns.AddValueColumn("Col1", typeof(int), 5);
schema.Columns.AddValueColumn("Col2", typeof(int), 5);
var cc = schema.Columns.AddCalculatedColumn<int>("Calculated", row => 4);
schema.Columns.AddValueColumn("Col4", typeof(int), 5);
var mapping = new SchemaMapping(schema);
var cc2 = schema.Columns.AddCalculatedColumn<long>("Other Calculated", row => row.Field<int>("Col1") * (long)row["Col2"]);
Assert.AreEqual(3, mapping.Columns.Count);
var lastCol = schema.Columns.AddValueColumn("Col6", typeof(int), 7);
Assert.AreEqual(4, mapping.Columns.Count);
Assert.AreEqual(lastCol, mapping.Columns[3].Column);
schema.Columns.RemoveColumn(cc2);
schema.Columns.RemoveColumn("Col2");
Assert.AreEqual(3, mapping.Columns.Count);
Assert.AreEqual(lastCol, mapping.Columns[2].Column);
mapping.Columns.RemoveMapping(lastCol);
Assert.AreEqual(2, mapping.Columns.Count);
Assert.AreEqual(schema.Columns["Col4"], mapping.Columns[1].Column);
Assert.AreEqual("Col4", mapping.Columns[1].SqlName);
}
static AdReminderEmail()
{
#region Create Schema
Schema = new TypedSchema<AdReminderEmail>("ReminderEmailLog");
Schema.PrimaryKey = RowIdColumn = Schema.Columns.AddValueColumn("RowId", typeof(Guid), null);
RowIdColumn.Unique = true;
RowIdColumn.AllowNulls = false;
RecipientColumn = Schema.Columns.AddForeignKey("Recipient", MelaveMalkaInvitation.Schema, "ReminderEmailLogs");
RecipientColumn.AllowNulls = false;
DateColumn = Schema.Columns.AddValueColumn("Date", typeof(DateTime), null);
DateColumn.AllowNulls = false;
EmailSubjectColumn = Schema.Columns.AddValueColumn("EmailSubject", typeof(String), null);
EmailSubjectColumn.AllowNulls = false;
EmailSourceColumn = Schema.Columns.AddValueColumn("EmailSource", typeof(String), null);
EmailSourceColumn.AllowNulls = false;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "ReminderEmailLog";
SchemaMapping.SqlSchemaName = "MelaveMalka";
SchemaMapping.Columns.AddMapping(RowIdColumn, "RowId");
SchemaMapping.Columns.AddMapping(RecipientColumn, "InviteId");
SchemaMapping.Columns.AddMapping(DateColumn, "Date");
SchemaMapping.Columns.AddMapping(EmailSubjectColumn, "EmailSubject");
SchemaMapping.Columns.AddMapping(EmailSourceColumn, "EmailSource");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
///<summary>Applies a deleted row to the database.</summary>
public void ApplyDelete(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (row == null)
{
throw new ArgumentNullException("row");
}
//Should work fine for SQLCE too
using (var command = context.CreateCommand(
"DELETE FROM " + QualifyTable(schema) + " WHERE " + schema.PrimaryKey.SqlName.EscapeSqlIdentifier() + " = @ID AND RowVersion = @version",
new { ID = row[row.Schema.PrimaryKey], version = row.RowVersion }
)) {
if (command.ExecuteNonQuery() != 1)
{
ThrowRowModified(context, schema, row);
}
}
}
///<summary>Adds parameters for the value columns in a row to a DbCommand.</summary>
protected static void PopulateParameters(DbCommand command, SchemaMapping schema, Row row)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (row == null)
{
throw new ArgumentNullException("row");
}
for (int i = 0; i < schema.Columns.Count; i++)
{
var parameter = command.CreateParameter();
parameter.ParameterName = "@Col" + i.ToString(CultureInfo.InvariantCulture);
parameter.Value = GetColumnValue(row, schema.Columns[i].Column) ?? DBNull.Value;
command.Parameters.Add(parameter);
}
}
///<summary>Reads a SchemaMapping from an XML element.</summary>
///<param name="xml">An XML element created by <see cref="SchemaMapping.ToXml"/>.</param>
///<param name="schemas">The schema mapped by the mapping.</param>
public static SchemaMapping FromXml(XElement xml, IEnumerable<TableSchema> schemas)
{
if (xml == null) throw new ArgumentNullException("xml");
if (schemas == null) throw new ArgumentNullException("schemas");
var schemaName = xml.Attribute("SchemaName").Value;
var schema = schemas.SingleOrDefault(ts => ts.Name == schemaName);
if (schema == null)
throw new ArgumentException("Schema '" + schemaName + "' not found", "schemas");
var retVal = new SchemaMapping(schema, false);
retVal.SqlName = xml.Attribute("SqlName").Value;
if (xml.Attribute("SqlSchemaName") != null)
retVal.SqlSchemaName = xml.Attribute("SqlSchemaName").Value;
foreach (var cm in xml.Elements("ColumnMapping")) {
retVal.Columns.AddMapping(
schema.Columns[cm.Attribute("ColumnName").Value],
cm.Attribute("SqlName").Value
);
}
return retVal;
}
///<summary>Applies an update row to the database.</summary>
public override void ApplyUpdate(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null) throw new ArgumentNullException("context");
if (schema == null) throw new ArgumentNullException("schema");
if (row == null) throw new ArgumentNullException("row");
var sql = new StringBuilder();
//UPDATE [SchemaName].[TableName]
//SET [FirstColumn] = @Col0, [SecondColumn] = @Col1
//WHERE @IDColumn = @ID AND RowVersion = @version;
sql.Append("UPDATE ").AppendLine(QualifyTable(schema));
sql.Append("SET").AppendJoin(
schema.Columns.Select((c, i) => c.SqlName.EscapeSqlIdentifier() + " = @Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).AppendLine();
sql.Append("WHERE ").Append(schema.PrimaryKey.SqlName.EscapeSqlIdentifier()).Append(" = @Col").Append(schema.Columns.IndexOf(schema.PrimaryKey))
.Append(" AND RowVersion = @version;");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
var versionParameter = command.CreateParameter();
versionParameter.ParameterName = "@version";
versionParameter.Value = row.RowVersion;
command.Parameters.Add(versionParameter);
if (command.ExecuteNonQuery() != 1)
ThrowRowModified(context, schema, row);
}
RefreshVersion(context, schema, row);
}
///<summary>Refreshes a row's RowVersion.</summary>
///<remarks>.Net's SQL CE client doesn't
///support multiple SQL statements in a
///single command. Therefore, I need to
///use a separate command to update the
///RowVersion after updating or adding a
///row. This method will update the field.</remarks>
void RefreshVersion(TransactionContext context, SchemaMapping schema, Row row)
{
using (var command = context.CreateCommand(
"SELECT RowVersion FROM " + QualifyTable(schema) + " WHERE " + schema.PrimaryKey.SqlName.EscapeSqlIdentifier() + " = @ID",
new { ID = row[schema.PrimaryKey.Column] }
)) {
context.SetRowVersion(row, command.ExecuteScalar());
}
}
///<summary>Gets the name of the SQL Server table referenced by a SchemaMapping.</summary>
///<returns>The escaped table name.</returns>
protected override string QualifyTable(SchemaMapping mapping)
{
if (mapping == null)
{
throw new ArgumentNullException("mapping");
}
return(mapping.SqlName.EscapeSqlIdentifier());
}
public void NoCalculatedColumnsTest()
{
var schema = new TableSchema("NoCalculatedColumnsTest");
var cc = schema.Columns.AddCalculatedColumn<int>("Calculated", row => 4);
var mapping = new SchemaMapping(schema);
Assert.AreEqual(0, mapping.Columns.Count);
mapping.Columns.AddMapping(cc);
}
///<summary>Sets a SchemaMapping as the default for its table.</summary>
///<remarks>Both the schema and the mapping will be held in memory forever.
///The same (mutable) SchemaMapping instance will be returned.</remarks>
public static void SetPrimaryMapping(SchemaMapping mapping)
{
if (mapping == null)
{
throw new ArgumentNullException("mapping");
}
using (primariesLock.WriteLock())
primaryMappings[mapping.Schema] = mapping;
}
static StagedPayment()
{
#region Create Schema
Schema = new TypedSchema<StagedPayment>("StagedPayments");
Schema.PrimaryKey = StagedPaymentIdColumn = Schema.Columns.AddValueColumn("StagedPaymentId", typeof(Guid), null);
StagedPaymentIdColumn.Unique = true;
StagedPaymentIdColumn.AllowNulls = false;
StagedPersonColumn = Schema.Columns.AddForeignKey("StagedPerson", StagedPerson.Schema, "StagedPayments");
StagedPersonColumn.AllowNulls = false;
DateColumn = Schema.Columns.AddValueColumn("Date", typeof(DateTime), null);
DateColumn.AllowNulls = false;
MethodColumn = Schema.Columns.AddValueColumn("Method", typeof(String), null);
MethodColumn.AllowNulls = false;
CheckNumberColumn = Schema.Columns.AddValueColumn("CheckNumber", typeof(String), null);
CheckNumberColumn.AllowNulls = true;
AccountColumn = Schema.Columns.AddValueColumn("Account", typeof(String), null);
AccountColumn.AllowNulls = false;
AmountColumn = Schema.Columns.AddValueColumn("Amount", typeof(Decimal), null);
AmountColumn.AllowNulls = false;
CommentsColumn = Schema.Columns.AddValueColumn("Comments", typeof(String), null);
CommentsColumn.AllowNulls = true;
ExternalIdColumn = Schema.Columns.AddValueColumn("ExternalId", typeof(String), null);
ExternalIdColumn.AllowNulls = false;
CompanyColumn = Schema.Columns.AddValueColumn("Company", typeof(String), null);
CompanyColumn.AllowNulls = true;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "StagedPayments";
SchemaMapping.SqlSchemaName = "BillingMigration";
SchemaMapping.Columns.AddMapping(StagedPaymentIdColumn, "StagedPaymentId");
SchemaMapping.Columns.AddMapping(StagedPersonColumn, "StagedPersonId");
SchemaMapping.Columns.AddMapping(DateColumn, "Date");
SchemaMapping.Columns.AddMapping(MethodColumn, "Method");
SchemaMapping.Columns.AddMapping(CheckNumberColumn, "CheckNumber");
SchemaMapping.Columns.AddMapping(AccountColumn, "Account");
SchemaMapping.Columns.AddMapping(AmountColumn, "Amount");
SchemaMapping.Columns.AddMapping(CommentsColumn, "Comments");
SchemaMapping.Columns.AddMapping(ExternalIdColumn, "ExternalId");
SchemaMapping.Columns.AddMapping(CompanyColumn, "Company");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
///<summary>Applies an update row to the database.</summary>
public virtual void ApplyUpdate(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (row == null)
{
throw new ArgumentNullException("row");
}
var sql = new StringBuilder();
//UPDATE [SchemaName].[TableName]
//SET [FirstColumn] = @Col0, [SecondColumn] = @Col1
//OUTPUT INSERTED.RowVersionINTO @version
//WHERE [IDColumnName] = @ColN AND RowVersion = @version;
sql.Append("UPDATE ").AppendLine(QualifyTable(schema));
sql.Append("SET").AppendJoin(
schema.Columns.Select((c, i) => c.SqlName.EscapeSqlIdentifier() + " = @Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).AppendLine();
sql.AppendLine("OUTPUT INSERTED.RowVersion");
sql.Append("WHERE ").Append(schema.PrimaryKey.SqlName.EscapeSqlIdentifier()).Append(" = @Col").Append(schema.Columns.IndexOf(schema.PrimaryKey))
.Append(" AND RowVersion = @version;");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
var versionParameter = command.CreateParameter();
versionParameter.ParameterName = "@version";
versionParameter.Value = row.RowVersion;
command.Parameters.Add(versionParameter);
using (var reader = command.ExecuteReader()) {
if (!reader.Read())
{
reader.Close(); //A single connection cannot have two DataReaders at once
ThrowRowModified(context, schema, row);
}
row.RowVersion = reader.GetValue(0);
if (reader.Read())
{
throw new DBConcurrencyException("Concurrency FAIL!"); //Exception will be handled by TableSynchronizer
}
}
}
}
public DataReaderTablePopulator(Table table, SchemaMapping mapping, DbDataReader reader)
: base(table)
{
this.reader = reader;
this.mapping = mapping;
columnIndices = mapping.Columns.Select(c => reader.GetOrdinal(c.SqlName)).ToArray();
if (table.Schema.PrimaryKey != null)
{
primaryKeyIndex = reader.GetOrdinal(mapping.Columns[table.Schema.PrimaryKey].SqlName);
}
rowVersionIndex = reader.GetOrdinal("RowVersion");
}
///<summary>Creates a SELECT command for the given SchemaMapping.</summary>
public DbCommand CreateSelectCommand(DbConnection connection, SchemaMapping schema)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (schema == null)
{
throw new ArgumentNullException("schema");
}
return(connection.CreateCommand(BuildSelectCommand(schema).ToString()));
}
///<summary>Applies an inserted row to the database.</summary>
public virtual void ApplyInsert(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (row == null)
{
throw new ArgumentNullException("row");
}
var sql = new StringBuilder();
//INSERT INTO [SchemaName].[TableName]
// ([FirstColumn], [SecondColumn])
//OUTPUT INSERTED.RowVersion
//VALUES(@Col0, @Col1);
sql.Append("INSERT INTO ").AppendLine(QualifyTable(schema));
sql.Append("\t(").AppendJoin(
schema.Columns.Select(c => c.SqlName.EscapeSqlIdentifier()), ", "
).AppendLine(")");
sql.AppendLine("OUTPUT INSERTED.RowVersion");
sql.Append("VALUES(").AppendJoin(
schema.Columns.Select((c, i) => "@Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).Append(");");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
using (var reader = command.ExecuteReader()) {
if (!reader.Read())
{
throw new DataException("INSERT command returned no rows");
}
row.RowVersion = reader.GetValue(0);
if (reader.Read())
{
throw new DataException("INSERT command returned multiple rows");
}
}
}
}
public void ColumnsTest()
{
var schema = new TableSchema("ColumnsTest");
schema.Columns.AddValueColumn("Col1", typeof(int), 5);
schema.Columns.AddValueColumn("Col2", typeof(int), 5);
var cc = schema.Columns.AddCalculatedColumn<int>("Calculated", row => 4);
schema.Columns.AddValueColumn("Col4", typeof(int), 5);
var mapping = new SchemaMapping(schema);
Assert.AreEqual(3, mapping.Columns.Count);
Assert.IsNull(mapping.Columns[cc]);
Assert.AreEqual(schema.Columns.Last(), mapping.Columns[2].Column);
Assert.AreEqual(schema.Columns[1], mapping.Columns["Col2"].Column);
}
///<summary>Applies a deleted row to the database.</summary>
public void ApplyDelete(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null) throw new ArgumentNullException("context");
if (schema == null) throw new ArgumentNullException("schema");
if (row == null) throw new ArgumentNullException("row");
//Should work fine for SQLCE too
using (var command = context.CreateCommand(
"DELETE FROM " + QualifyTable(schema) + " WHERE " + schema.PrimaryKey.SqlName.EscapeSqlIdentifier() + " = @ID AND RowVersion = @version",
new { ID = row[row.Schema.PrimaryKey], version = row.RowVersion }
)) {
if (command.ExecuteNonQuery() != 1)
ThrowRowModified(context, schema, row);
}
}
//TODO: Move to ListMaker
#region DDL
///<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() }
);
}
///<summary>Gets the name of the SQL Server table referenced by a SchemaMapping.</summary>
///<returns>The escaped table name, with the schema name if any.</returns>
///<remarks>SQL CE doesn't support schemas, so SqlCeProvider overrides this method.</remarks>
protected virtual string QualifyTable(SchemaMapping mapping)
{
if (mapping == null)
{
throw new ArgumentNullException("mapping");
}
string retVal = null;
if (!String.IsNullOrEmpty(mapping.SqlSchemaName))
{
retVal = mapping.SqlSchemaName.EscapeSqlIdentifier() + ".";
}
retVal += mapping.SqlName.EscapeSqlIdentifier();
return(retVal);
}
///<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());
}
///<summary>Builds a SELECT command for the given schema.</summary>
///<returns>
///SELECT [FirstColumn], [SecondColumn], [RowVersion]
///FROM [SchemaName].[TableName]
///</returns>
protected StringBuilder BuildSelectCommand(SchemaMapping schema)
{
if (schema == null)
{
throw new ArgumentNullException("schema");
}
var sql = new StringBuilder();
sql.Append("SELECT ");
sql.AppendJoin(
schema.Columns.Select(c => c.SqlName.EscapeSqlIdentifier()), ", "
);
sql.AppendLine(", [RowVersion]");
sql.Append("FROM ").Append(QualifyTable(schema));
return(sql);
}
///<summary>Adds a SchemaMapping for a table in the DataContext to the collection.</summary>
public TableSynchronizer AddMapping(SchemaMapping mapping)
{
if (mapping == null)
{
throw new ArgumentNullException("mapping");
}
var table = SyncContext.DataContext.Tables[mapping.Schema];
if (table == null)
{
throw new ArgumentException("Mapping must map a table in the DataContext");
}
var retVal = new TableSynchronizer(table, mapping, SyncContext.SqlProvider);
Add(retVal);
return(retVal);
}
///<summary>Applies an update row to the database.</summary>
public override void ApplyUpdate(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (row == null)
{
throw new ArgumentNullException("row");
}
var sql = new StringBuilder();
//UPDATE [SchemaName].[TableName]
//SET [FirstColumn] = @Col0, [SecondColumn] = @Col1
//WHERE @IDColumn = @ID AND RowVersion = @version;
sql.Append("UPDATE ").AppendLine(QualifyTable(schema));
sql.Append("SET").AppendJoin(
schema.Columns.Select((c, i) => c.SqlName.EscapeSqlIdentifier() + " = @Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).AppendLine();
sql.Append("WHERE ").Append(schema.PrimaryKey.SqlName.EscapeSqlIdentifier()).Append(" = @Col").Append(schema.Columns.IndexOf(schema.PrimaryKey))
.Append(" AND RowVersion = @version;");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
var versionParameter = command.CreateParameter();
versionParameter.ParameterName = "@version";
versionParameter.Value = row.RowVersion;
command.Parameters.Add(versionParameter);
if (command.ExecuteNonQuery() != 1)
{
ThrowRowModified(context, schema, row);
}
}
RefreshVersion(context, schema, row);
}
///<summary>Throws a RowModifiedException for a row, reading the row's current values from the database.</summary>
protected void ThrowRowModified(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (row == null)
{
throw new ArgumentNullException("row");
}
//SELECT [FirstColumn], [SecondColumn], [RowVersion]
//FROM [SchemaName].[TableName]
//WHERE [IDColumnName] = @ID
var sql = BuildSelectCommand(schema);
sql.AppendLine().Append("WHERE ").Append(schema.PrimaryKey.SqlName.EscapeSqlIdentifier()).Append(" = @ID");
using (var command = context.CreateCommand(sql.ToString(), new { ID = row[schema.PrimaryKey.Column] }))
using (var reader = command.ExecuteReader()) {
if (!reader.Read())
{
row.RowVersion = null;
throw new RowDeletedException(row);
}
var dict = schema.Columns.ToDictionary(cm => cm.Column, cm => reader[cm.SqlName]);
row.RowVersion = reader["RowVersion"];
if (reader.Read())
{
throw new InvalidOperationException("Duplicate ID");
}
throw new RowModifiedException(row, dict);
}
}
///<summary>Adds a synchronizer for a table using the primary SchemaMapping.</summary>
///<remarks>If no primary SchemaMapping has been registered by calling <see cref="SchemaMapping.SetPrimaryMapping"/>, an exception will be thrown.</remarks>
public void AddTablePrimary(Table table)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (table.Context != SyncContext.DataContext)
{
throw new ArgumentException("Table must belong to parent DataContext", "table");
}
var mapping = SchemaMapping.GetPrimaryMapping(table.Schema);
if (mapping == null)
{
throw new InvalidOperationException("The " + table.Schema.Name + " schema has no primary mapping");
}
Items.Add(new TableSynchronizer(table, mapping, SyncContext.SqlProvider));
}
public DataPreview(SchemaMapping[] schemas)
{
InitializeComponent();
tables = schemas
.SortDependencies(sm => sm.Schema)
.Reverse()
.Select(sm => new LoadableTable(context, sm))
.ToList();
schemaTables = tables.ToDictionary(t => t.Schema);
schemaTree.BeginUnboundLoad();
schemaTree.ClearNodes();
foreach (var table in tables) {
AddSchemaTree(table, null);
}
schemaTree.BestFitColumns();
schemaTree.EndUnboundLoad();
}
///<summary>Applies an inserted row to the database.</summary>
public override void ApplyInsert(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (row == null)
{
throw new ArgumentNullException("row");
}
var sql = new StringBuilder();
//INSERT INTO [SchemaName].[TableName]
// ([FirstColumn], [SecondColumn])
//VALUES(@Col0, @Col1);
sql.Append("INSERT INTO ").AppendLine(QualifyTable(schema));
sql.Append("\t(").AppendJoin(
schema.Columns.Select(c => c.SqlName.EscapeSqlIdentifier()), ", "
).AppendLine(")");
sql.Append("VALUES(").AppendJoin(
schema.Columns.Select((c, i) => "@Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).Append(");");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
if (command.ExecuteNonQuery() != 1)
{
throw new DataException("INSERT command didn't work");
}
}
RefreshVersion(context, schema, row);
}
static ImportedPayment()
{
#region Create Schema
Schema = new TypedSchema<ImportedPayment>("ImportedPayments");
Schema.PrimaryKey = ImportedPaymentIdColumn = Schema.Columns.AddValueColumn("ImportedPaymentId", typeof(Guid), null);
ImportedPaymentIdColumn.Unique = true;
ImportedPaymentIdColumn.AllowNulls = false;
PaymentColumn = Schema.Columns.AddForeignKey("Payment", Payment.Schema, "ImportedPayments");
PaymentColumn.Unique = true;
PaymentColumn.AllowNulls = false;
SourceColumn = Schema.Columns.AddValueColumn("Source", typeof(String), null);
SourceColumn.AllowNulls = false;
ExternalIdColumn = Schema.Columns.AddValueColumn("ExternalId", typeof(String), null);
ExternalIdColumn.AllowNulls = false;
DateImportedColumn = Schema.Columns.AddValueColumn("DateImported", typeof(DateTime), null);
DateImportedColumn.AllowNulls = false;
ImportingUserColumn = Schema.Columns.AddValueColumn("ImportingUser", typeof(String), null);
ImportingUserColumn.AllowNulls = false;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "ImportedPayments";
SchemaMapping.SqlSchemaName = "Billing";
SchemaMapping.Columns.AddMapping(ImportedPaymentIdColumn, "ImportedPaymentId");
SchemaMapping.Columns.AddMapping(PaymentColumn, "PaymentId");
SchemaMapping.Columns.AddMapping(SourceColumn, "Source");
SchemaMapping.Columns.AddMapping(ExternalIdColumn, "ExternalId");
SchemaMapping.Columns.AddMapping(DateImportedColumn, "DateImported");
SchemaMapping.Columns.AddMapping(ImportingUserColumn, "ImportingUser");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
///<summary>Reads a SchemaMapping from an XML element.</summary>
///<param name="xml">An XML element created by <see cref="SchemaMapping.ToXml"/>.</param>
///<param name="schemas">The schema mapped by the mapping.</param>
public static SchemaMapping FromXml(XElement xml, IEnumerable <TableSchema> schemas)
{
if (xml == null)
{
throw new ArgumentNullException("xml");
}
if (schemas == null)
{
throw new ArgumentNullException("schemas");
}
var schemaName = xml.Attribute("SchemaName").Value;
var schema = schemas.SingleOrDefault(ts => ts.Name == schemaName);
if (schema == null)
{
throw new ArgumentException("Schema '" + schemaName + "' not found", "schemas");
}
var retVal = new SchemaMapping(schema, false);
retVal.SqlName = xml.Attribute("SqlName").Value;
if (xml.Attribute("SqlSchemaName") != null)
{
retVal.SqlSchemaName = xml.Attribute("SqlSchemaName").Value;
}
foreach (var cm in xml.Elements("ColumnMapping"))
{
retVal.Columns.AddMapping(
schema.Columns[cm.Attribute("ColumnName").Value],
cm.Attribute("SqlName").Value
);
}
return(retVal);
}
///<summary>Applies an inserted row to the database.</summary>
public virtual void ApplyInsert(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null) throw new ArgumentNullException("context");
if (schema == null) throw new ArgumentNullException("schema");
if (row == null) throw new ArgumentNullException("row");
var sql = new StringBuilder();
//INSERT INTO [SchemaName].[TableName]
// ([FirstColumn], [SecondColumn])
//OUTPUT INSERTED.RowVersion
//VALUES(@Col0, @Col1);
sql.Append("INSERT INTO ").AppendLine(QualifyTable(schema));
sql.Append("\t(").AppendJoin(
schema.Columns.Select(c => c.SqlName.EscapeSqlIdentifier()), ", "
).AppendLine(")");
sql.AppendLine("OUTPUT INSERTED.RowVersion");
sql.Append("VALUES(").AppendJoin(
schema.Columns.Select((c, i) => "@Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).Append(");");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
using (var reader = command.ExecuteReader()) {
if (!reader.Read())
throw new DataException("INSERT command returned no rows");
row.RowVersion = reader.GetValue(0);
if (reader.Read())
throw new DataException("INSERT command returned multiple rows");
}
}
}
///<summary>Reads SchemaMappings from XML elements.</summary>
///<param name="elements">A set of XML elements created by <see cref="SchemaMapping.ToXml"/>.</param>
///<param name="schemas">The schemas mapped by the mappings.</param>
public static IEnumerable <SchemaMapping> FromXml(IEnumerable <XElement> elements, IEnumerable <TableSchema> schemas)
{
return(elements.Select(xml => SchemaMapping.FromXml(xml, schemas)));
}
static LoggedStatement()
{
#region Create Schema
Schema = new TypedSchema<LoggedStatement>("StatementLog");
Schema.PrimaryKey = IdColumn = Schema.Columns.AddValueColumn("Id", typeof(Guid), null);
IdColumn.Unique = true;
IdColumn.AllowNulls = false;
PersonColumn = Schema.Columns.AddForeignKey("Person", Person.Schema, "LoggedStatements");
PersonColumn.AllowNulls = false;
DateGeneratedColumn = Schema.Columns.AddValueColumn("DateGenerated", typeof(DateTime), null);
DateGeneratedColumn.AllowNulls = false;
MediaColumn = Schema.Columns.AddValueColumn("Media", typeof(String), null);
MediaColumn.AllowNulls = false;
StatementKindColumn = Schema.Columns.AddValueColumn("StatementKind", typeof(String), null);
StatementKindColumn.AllowNulls = false;
StartDateColumn = Schema.Columns.AddValueColumn("StartDate", typeof(DateTime), null);
StartDateColumn.AllowNulls = false;
EndDateColumn = Schema.Columns.AddValueColumn("EndDate", typeof(DateTime), null);
EndDateColumn.AllowNulls = false;
UserNameColumn = Schema.Columns.AddValueColumn("UserName", typeof(String), null);
UserNameColumn.AllowNulls = false;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "StatementLog";
SchemaMapping.SqlSchemaName = "Billing";
SchemaMapping.Columns.AddMapping(IdColumn, "Id");
SchemaMapping.Columns.AddMapping(PersonColumn, "PersonId");
SchemaMapping.Columns.AddMapping(DateGeneratedColumn, "DateGenerated");
SchemaMapping.Columns.AddMapping(MediaColumn, "Media");
SchemaMapping.Columns.AddMapping(StatementKindColumn, "StatementKind");
SchemaMapping.Columns.AddMapping(StartDateColumn, "StartDate");
SchemaMapping.Columns.AddMapping(EndDateColumn, "EndDate");
SchemaMapping.Columns.AddMapping(UserNameColumn, "UserName");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
///<summary>Creates a ColumnMappingCollection that wraps a list of ColumnMapping objects.</summary>
internal ColumnMappingCollection(SchemaMapping schema, IList <ColumnMapping> list)
: base(list)
{
SchemaMapping = schema;
}
static Deposit()
{
#region Create Schema
Schema = new TypedSchema<Deposit>("Deposits");
Schema.PrimaryKey = DepositIdColumn = Schema.Columns.AddValueColumn("DepositId", typeof(Guid), null);
DepositIdColumn.Unique = true;
DepositIdColumn.AllowNulls = false;
DateColumn = Schema.Columns.AddValueColumn("Date", typeof(DateTime), null);
DateColumn.AllowNulls = false;
NumberColumn = Schema.Columns.AddValueColumn("Number", typeof(Int32), null);
NumberColumn.AllowNulls = false;
AccountColumn = Schema.Columns.AddValueColumn("Account", typeof(String), null);
AccountColumn.AllowNulls = false;
AmountColumn = Schema.Columns.AddCalculatedColumn<Deposit, Decimal>("Amount", row => row.Payments.Sum(p => p.Amount));
CountColumn = Schema.Columns.AddCalculatedColumn<Deposit, Int32>("Count", row => row.Payments.Count);
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "Deposits";
SchemaMapping.SqlSchemaName = "Billing";
SchemaMapping.Columns.AddMapping(DepositIdColumn, "DepositId");
SchemaMapping.Columns.AddMapping(DateColumn, "Date");
SchemaMapping.Columns.AddMapping(NumberColumn, "Number");
SchemaMapping.Columns.AddMapping(AccountColumn, "Account");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
static Caller()
{
#region Create Schema
Schema = new TypedSchema<Caller>("Callers");
Schema.PrimaryKey = RowIdColumn = Schema.Columns.AddValueColumn("RowId", typeof(Guid), null);
RowIdColumn.Unique = true;
RowIdColumn.AllowNulls = false;
PersonColumn = Schema.Columns.AddForeignKey("Person", Person.Schema, "Callers");
PersonColumn.AllowNulls = false;
DateAddedColumn = Schema.Columns.AddValueColumn("DateAdded", typeof(DateTime), null);
DateAddedColumn.AllowNulls = false;
YearColumn = Schema.Columns.AddValueColumn("Year", typeof(Int32), null);
YearColumn.AllowNulls = false;
EmailAddressesColumn = Schema.Columns.AddCalculatedColumn<Caller, String>("EmailAddresses", row => String.Join(", ", row.Person.EmailAddresses.Select(e => e.Email)));
CalleeCountColumn = Schema.Columns.AddCalculatedColumn<Caller, Int32>("CalleeCount", row => row.Callees.Count);
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "Callers";
SchemaMapping.SqlSchemaName = "MelaveMalka";
SchemaMapping.Columns.AddMapping(RowIdColumn, "RowId");
SchemaMapping.Columns.AddMapping(PersonColumn, "PersonId");
SchemaMapping.Columns.AddMapping(DateAddedColumn, "DateAdded");
SchemaMapping.Columns.AddMapping(YearColumn, "Year");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
public void SimpleTableTest()
{
using (var connection = SqlProvider.OpenConnection())
connection.ExecuteNonQuery(@"
CREATE TABLE [Numbers](
[ID] UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL PRIMARY KEY DEFAULT(newid()),
[Value] INTEGER NOT NULL,
[IsEven] BIT NOT NULL,
[String] NVARCHAR(1024) NOT NULL,
[RowVersion] RowVersion
);");
try {
var schema = new TableSchema("Numbers");
schema.PrimaryKey = schema.Columns.AddValueColumn("ID", typeof(Guid), null);
schema.Columns.AddValueColumn("Value", typeof(int), 2);
schema.Columns.AddValueColumn("IsEven", typeof(bool), false);
schema.Columns.AddValueColumn("String", typeof(string), null).AllowNulls = false;
var mapping = new SchemaMapping(schema);
var table = new Table(schema);
var syncer = new TableSynchronizer(table, mapping, SqlProvider);
table.Rows.AddFromValues(Guid.NewGuid(), 2, true, "2");
syncer.ReadData();
Assert.AreEqual(0, table.Rows.Count);
for (int i = 0; i < 10; i++) {
var row = table.Rows.AddFromValues(Guid.NewGuid(), i, i % 2 == 0, "t");
row["String"] = i.ToString();
}
Assert.IsTrue(syncer.Changes.All(c => c.ChangeType == RowChangeType.Added));
Assert.AreEqual(10, syncer.Changes.Count);
Assert.IsTrue(syncer.Changes.Select(c => c.Row).SequenceEqual(table.Rows));
syncer.WriteData();
foreach (var row in table.Rows.Where(r => r.Field<bool>("IsEven"))) {
row["String"] += "E";
}
Assert.IsTrue(syncer.Changes.All(c => c.ChangeType == RowChangeType.Changed));
Assert.AreEqual(5, syncer.Changes.Count);
syncer.WriteData();
var newTable = new Table(schema);
var newSyncer = new TableSynchronizer(newTable, mapping, SqlProvider);
newSyncer.ReadData();
AssertTablesEqual(table, newTable);
table.Rows[4].RemoveRow();
table.Rows[7].RemoveRow();
table.Rows[2].RemoveRow();
syncer.WriteData();
newSyncer.ReadData();
AssertTablesEqual(table, newTable);
newTable = new Table(schema);
newSyncer = new TableSynchronizer(newTable, mapping, SqlProvider);
newSyncer.ReadData();
AssertTablesEqual(table, newTable);
syncer.ReadData();
AssertTablesEqual(newTable, table);
} finally {
using (var connection = SqlProvider.OpenConnection())
connection.ExecuteNonQuery(@"DROP TABLE Numbers;");
}
}
static Person()
{
#region Create Schema
Schema = new TypedSchema<Person>("MasterDirectory");
Schema.PrimaryKey = IdColumn = Schema.Columns.AddValueColumn("Id", typeof(Guid), null);
IdColumn.Unique = true;
IdColumn.AllowNulls = false;
YKIDColumn = Schema.Columns.AddValueColumn("YKID", typeof(Int32), null);
YKIDColumn.AllowNulls = true;
LastNameColumn = Schema.Columns.AddValueColumn("LastName", typeof(String), null);
LastNameColumn.AllowNulls = false;
HisNameColumn = Schema.Columns.AddValueColumn("HisName", typeof(String), null);
HisNameColumn.AllowNulls = true;
HerNameColumn = Schema.Columns.AddValueColumn("HerName", typeof(String), null);
HerNameColumn.AllowNulls = true;
FullNameColumn = Schema.Columns.AddValueColumn("FullName", typeof(String), null);
FullNameColumn.AllowNulls = true;
AddressColumn = Schema.Columns.AddValueColumn("Address", typeof(String), null);
AddressColumn.AllowNulls = true;
CityColumn = Schema.Columns.AddValueColumn("City", typeof(String), null);
CityColumn.AllowNulls = true;
StateColumn = Schema.Columns.AddValueColumn("State", typeof(String), null);
StateColumn.AllowNulls = true;
ZipColumn = Schema.Columns.AddValueColumn("Zip", typeof(String), null);
ZipColumn.AllowNulls = true;
PhoneColumn = Schema.Columns.AddValueColumn("Phone", typeof(String), null);
PhoneColumn.AllowNulls = false;
SourceColumn = Schema.Columns.AddValueColumn("Source", typeof(String), null);
SourceColumn.AllowNulls = false;
SalutationColumn = Schema.Columns.AddValueColumn("Salutation", typeof(String), null);
SalutationColumn.AllowNulls = false;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "MasterDirectory";
SchemaMapping.SqlSchemaName = "Data";
SchemaMapping.Columns.AddMapping(IdColumn, "Id");
SchemaMapping.Columns.AddMapping(YKIDColumn, "YKID");
SchemaMapping.Columns.AddMapping(LastNameColumn, "LastName");
SchemaMapping.Columns.AddMapping(HisNameColumn, "HisName");
SchemaMapping.Columns.AddMapping(HerNameColumn, "HerName");
SchemaMapping.Columns.AddMapping(FullNameColumn, "FullName");
SchemaMapping.Columns.AddMapping(AddressColumn, "Address");
SchemaMapping.Columns.AddMapping(CityColumn, "City");
SchemaMapping.Columns.AddMapping(StateColumn, "State");
SchemaMapping.Columns.AddMapping(ZipColumn, "Zip");
SchemaMapping.Columns.AddMapping(PhoneColumn, "Phone");
SchemaMapping.Columns.AddMapping(SourceColumn, "Source");
SchemaMapping.Columns.AddMapping(SalutationColumn, "Salutation");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
static Payment()
{
#region Create Schema
Schema = new TypedSchema<Payment>("Payments");
Schema.PrimaryKey = PaymentIdColumn = Schema.Columns.AddValueColumn("PaymentId", typeof(Guid), null);
PaymentIdColumn.Unique = true;
PaymentIdColumn.AllowNulls = false;
PersonColumn = Schema.Columns.AddForeignKey("Person", Person.Schema, "Payments");
PersonColumn.AllowNulls = false;
DateColumn = Schema.Columns.AddValueColumn("Date", typeof(DateTime), null);
DateColumn.AllowNulls = false;
MethodColumn = Schema.Columns.AddValueColumn("Method", typeof(String), null);
MethodColumn.AllowNulls = false;
CheckNumberColumn = Schema.Columns.AddValueColumn("CheckNumber", typeof(String), null);
CheckNumberColumn.AllowNulls = true;
AccountColumn = Schema.Columns.AddValueColumn("Account", typeof(String), null);
AccountColumn.AllowNulls = false;
AmountColumn = Schema.Columns.AddValueColumn("Amount", typeof(Decimal), null);
AmountColumn.AllowNulls = false;
DepositColumn = Schema.Columns.AddForeignKey("Deposit", Deposit.Schema, "Payments");
DepositColumn.AllowNulls = true;
CommentsColumn = Schema.Columns.AddValueColumn("Comments", typeof(String), null);
CommentsColumn.AllowNulls = true;
ModifiedColumn = Schema.Columns.AddValueColumn("Modified", typeof(DateTime), null);
ModifiedColumn.AllowNulls = false;
ModifierColumn = Schema.Columns.AddValueColumn("Modifier", typeof(String), null);
ModifierColumn.AllowNulls = false;
ExternalSourceColumn = Schema.Columns.AddValueColumn("ExternalSource", typeof(String), null);
ExternalSourceColumn.AllowNulls = true;
ExternalIdColumn = Schema.Columns.AddValueColumn("ExternalId", typeof(Int32), null);
ExternalIdColumn.AllowNulls = true;
CompanyColumn = Schema.Columns.AddValueColumn("Company", typeof(String), null);
CompanyColumn.AllowNulls = true;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "Payments";
SchemaMapping.SqlSchemaName = "Billing";
SchemaMapping.Columns.AddMapping(PaymentIdColumn, "PaymentId");
SchemaMapping.Columns.AddMapping(PersonColumn, "PersonId");
SchemaMapping.Columns.AddMapping(DateColumn, "Date");
SchemaMapping.Columns.AddMapping(MethodColumn, "Method");
SchemaMapping.Columns.AddMapping(CheckNumberColumn, "CheckNumber");
SchemaMapping.Columns.AddMapping(AccountColumn, "Account");
SchemaMapping.Columns.AddMapping(AmountColumn, "Amount");
SchemaMapping.Columns.AddMapping(DepositColumn, "DepositId");
SchemaMapping.Columns.AddMapping(CommentsColumn, "Comments");
SchemaMapping.Columns.AddMapping(ModifiedColumn, "Modified");
SchemaMapping.Columns.AddMapping(ModifierColumn, "Modifier");
SchemaMapping.Columns.AddMapping(ExternalSourceColumn, "ExternalSource");
SchemaMapping.Columns.AddMapping(ExternalIdColumn, "ExternalID");
SchemaMapping.Columns.AddMapping(CompanyColumn, "Company");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
static Pledge()
{
#region Create Schema
Schema = new TypedSchema<Pledge>("Pledges");
Schema.PrimaryKey = PledgeIdColumn = Schema.Columns.AddValueColumn("PledgeId", typeof(Guid), null);
PledgeIdColumn.Unique = true;
PledgeIdColumn.AllowNulls = false;
PersonColumn = Schema.Columns.AddForeignKey("Person", Person.Schema, "Pledges");
PersonColumn.AllowNulls = false;
DateColumn = Schema.Columns.AddValueColumn("Date", typeof(DateTime), null);
DateColumn.AllowNulls = false;
TypeColumn = Schema.Columns.AddValueColumn("Type", typeof(String), null);
TypeColumn.AllowNulls = false;
SubTypeColumn = Schema.Columns.AddValueColumn("SubType", typeof(String), "");
SubTypeColumn.AllowNulls = false;
AccountColumn = Schema.Columns.AddValueColumn("Account", typeof(String), null);
AccountColumn.AllowNulls = false;
AmountColumn = Schema.Columns.AddValueColumn("Amount", typeof(Decimal), null);
AmountColumn.AllowNulls = false;
NoteColumn = Schema.Columns.AddValueColumn("Note", typeof(String), null);
NoteColumn.AllowNulls = true;
CommentsColumn = Schema.Columns.AddValueColumn("Comments", typeof(String), null);
CommentsColumn.AllowNulls = true;
ModifiedColumn = Schema.Columns.AddValueColumn("Modified", typeof(DateTime), null);
ModifiedColumn.AllowNulls = false;
ModifierColumn = Schema.Columns.AddValueColumn("Modifier", typeof(String), null);
ModifierColumn.AllowNulls = false;
ExternalSourceColumn = Schema.Columns.AddValueColumn("ExternalSource", typeof(String), null);
ExternalSourceColumn.AllowNulls = true;
ExternalIdColumn = Schema.Columns.AddValueColumn("ExternalId", typeof(Int32), null);
ExternalIdColumn.AllowNulls = true;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "Pledges";
SchemaMapping.SqlSchemaName = "Billing";
SchemaMapping.Columns.AddMapping(PledgeIdColumn, "PledgeId");
SchemaMapping.Columns.AddMapping(PersonColumn, "PersonId");
SchemaMapping.Columns.AddMapping(DateColumn, "Date");
SchemaMapping.Columns.AddMapping(TypeColumn, "Type");
SchemaMapping.Columns.AddMapping(SubTypeColumn, "SubType");
SchemaMapping.Columns.AddMapping(AccountColumn, "Account");
SchemaMapping.Columns.AddMapping(AmountColumn, "Amount");
SchemaMapping.Columns.AddMapping(NoteColumn, "Note");
SchemaMapping.Columns.AddMapping(CommentsColumn, "Comments");
SchemaMapping.Columns.AddMapping(ModifiedColumn, "Modified");
SchemaMapping.Columns.AddMapping(ModifierColumn, "Modifier");
SchemaMapping.Columns.AddMapping(ExternalSourceColumn, "ExternalSource");
SchemaMapping.Columns.AddMapping(ExternalIdColumn, "ExternalID");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
static RelativeLink()
{
#region Create Schema
Schema = new TypedSchema<RelativeLink>("Relatives");
Schema.PrimaryKey = RowIdColumn = Schema.Columns.AddValueColumn("RowId", typeof(Guid), null);
RowIdColumn.Unique = true;
RowIdColumn.AllowNulls = false;
MemberColumn = Schema.Columns.AddForeignKey("Member", Person.Schema, "ForeignRelatives");
MemberColumn.AllowNulls = false;
RelativeColumn = Schema.Columns.AddForeignKey("Relative", Person.Schema, "Relatives");
RelativeColumn.AllowNulls = false;
RelationTypeColumn = Schema.Columns.AddValueColumn("Relation", typeof(String), null);
RelationTypeColumn.AllowNulls = false;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "Relatives";
SchemaMapping.SqlSchemaName = "Data";
SchemaMapping.Columns.AddMapping(RowIdColumn, "RowId");
SchemaMapping.Columns.AddMapping(MemberColumn, "MemberId");
SchemaMapping.Columns.AddMapping(RelativeColumn, "RelativeId");
SchemaMapping.Columns.AddMapping(RelationTypeColumn, "Relation");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
static SeatingReservation()
{
#region Create Schema
Schema = new TypedSchema<SeatingReservation>("SeatingReservations");
Schema.PrimaryKey = IdColumn = Schema.Columns.AddValueColumn("Id", typeof(Guid), null);
IdColumn.Unique = true;
IdColumn.AllowNulls = false;
PledgeColumn = Schema.Columns.AddForeignKey("Pledge", Pledge.Schema, "SeatingReservations");
PledgeColumn.Unique = true;
PledgeColumn.AllowNulls = false;
MensSeatsColumn = Schema.Columns.AddValueColumn("MensSeats", typeof(Int32), 0);
MensSeatsColumn.AllowNulls = false;
WomensSeatsColumn = Schema.Columns.AddValueColumn("WomensSeats", typeof(Int32), 0);
WomensSeatsColumn.AllowNulls = false;
BoysSeatsColumn = Schema.Columns.AddValueColumn("BoysSeats", typeof(Int32), 0);
BoysSeatsColumn.AllowNulls = false;
GirlsSeatsColumn = Schema.Columns.AddValueColumn("GirlsSeats", typeof(Int32), 0);
GirlsSeatsColumn.AllowNulls = false;
NotesColumn = Schema.Columns.AddValueColumn("Notes", typeof(String), "");
NotesColumn.AllowNulls = false;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "SeatingReservations";
SchemaMapping.SqlSchemaName = "Seating";
SchemaMapping.Columns.AddMapping(IdColumn, "Id");
SchemaMapping.Columns.AddMapping(PledgeColumn, "PledgeId");
SchemaMapping.Columns.AddMapping(MensSeatsColumn, "MensSeats");
SchemaMapping.Columns.AddMapping(WomensSeatsColumn, "WomensSeats");
SchemaMapping.Columns.AddMapping(BoysSeatsColumn, "BoysSeats");
SchemaMapping.Columns.AddMapping(GirlsSeatsColumn, "GirlsSeats");
SchemaMapping.Columns.AddMapping(NotesColumn, "Notes");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
///<summary>SQL CE does not support schemas; this method does nothing.</summary>
protected override void CreateSchema(DbConnection connection, SchemaMapping mapping)
{
}
static MelaveMalkaSeat()
{
#region Create Schema
Schema = new TypedSchema<MelaveMalkaSeat>("SeatReservations");
Schema.PrimaryKey = RowIdColumn = Schema.Columns.AddValueColumn("RowId", typeof(Guid), null);
RowIdColumn.Unique = true;
RowIdColumn.AllowNulls = false;
PersonColumn = Schema.Columns.AddForeignKey("Person", Person.Schema, "SeatReservations");
PersonColumn.AllowNulls = false;
DateAddedColumn = Schema.Columns.AddValueColumn("DateAdded", typeof(DateTime), null);
DateAddedColumn.AllowNulls = false;
YearColumn = Schema.Columns.AddValueColumn("Year", typeof(Int32), null);
YearColumn.AllowNulls = false;
MensSeatsColumn = Schema.Columns.AddValueColumn("MensSeats", typeof(Int32), 0);
MensSeatsColumn.AllowNulls = true;
WomensSeatsColumn = Schema.Columns.AddValueColumn("WomensSeats", typeof(Int32), 0);
WomensSeatsColumn.AllowNulls = true;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "SeatReservations";
SchemaMapping.SqlSchemaName = "MelaveMalka";
SchemaMapping.Columns.AddMapping(RowIdColumn, "RowId");
SchemaMapping.Columns.AddMapping(PersonColumn, "PersonId");
SchemaMapping.Columns.AddMapping(DateAddedColumn, "DateAdded");
SchemaMapping.Columns.AddMapping(YearColumn, "Year");
SchemaMapping.Columns.AddMapping(MensSeatsColumn, "MensSeats");
SchemaMapping.Columns.AddMapping(WomensSeatsColumn, "WomensSeats");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
public void NullValuesTest()
{
using (var connection = SqlProvider.OpenConnection())
connection.ExecuteNonQuery(@"
CREATE TABLE [NullableTest](
[ID] UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL PRIMARY KEY DEFAULT(newid()),
[Integer] INTEGER NULL,
[String] NVARCHAR(1024) NULL,
[RowVersion] RowVersion
);");
var schema = new TableSchema("NullableTest");
schema.PrimaryKey = schema.Columns.AddValueColumn("ID", typeof(Guid), null);
schema.Columns.AddValueColumn("Integer", typeof(int?), null);
schema.Columns.AddValueColumn("String", typeof(string), null);
var mapping = new SchemaMapping(schema);
var table = new Table(schema);
var syncer = new TableSynchronizer(table, mapping, SqlProvider);
table.Rows.AddFromValues(Guid.NewGuid());
table.Rows.AddFromValues(Guid.NewGuid(), 4, null);
table.Rows.AddFromValues(Guid.NewGuid(), null, "4");
table.Rows.AddFromValues(Guid.NewGuid(), 4, "4");
syncer.WriteData();
var newTable = new Table(schema);
var newSyncer = new TableSynchronizer(newTable, mapping, SqlProvider);
newSyncer.ReadData();
AssertTablesEqual(table, newTable);
table.Rows[0]["Integer"] = 5;
table.Rows[0]["String"] = "5";
table.Rows[3]["Integer"] = null;
table.Rows[3]["String"] = null;
syncer.WriteData();
newTable = new Table(schema);
newSyncer = new TableSynchronizer(newTable, mapping, SqlProvider);
newSyncer.ReadData();
AssertTablesEqual(table, newTable);
syncer.ReadData();
AssertTablesEqual(newTable, table);
}
static MelaveMalkaInvitation()
{
#region Create Schema
Schema = new TypedSchema<MelaveMalkaInvitation>("Invitees");
Schema.PrimaryKey = RowIdColumn = Schema.Columns.AddValueColumn("RowId", typeof(Guid), null);
RowIdColumn.Unique = true;
RowIdColumn.AllowNulls = false;
PersonColumn = Schema.Columns.AddForeignKey("Person", Person.Schema, "Invitees");
PersonColumn.AllowNulls = false;
YearColumn = Schema.Columns.AddValueColumn("Year", typeof(Int32), null);
YearColumn.AllowNulls = false;
SourceColumn = Schema.Columns.AddValueColumn("Source", typeof(String), null);
SourceColumn.AllowNulls = false;
DateAddedColumn = Schema.Columns.AddValueColumn("DateAdded", typeof(DateTime), null);
DateAddedColumn.AllowNulls = false;
ShouldCallColumn = Schema.Columns.AddValueColumn("ShouldCall", typeof(Boolean), false);
ShouldCallColumn.AllowNulls = false;
CallerColumn = Schema.Columns.AddForeignKey("Caller", Caller.Schema, "Callees");
CallerColumn.AllowNulls = true;
CallerNoteColumn = Schema.Columns.AddValueColumn("CallerNote", typeof(String), null);
CallerNoteColumn.AllowNulls = true;
AdAmountColumn = Schema.Columns.AddCalculatedColumn<MelaveMalkaInvitation, Decimal>("AdAmount", row => row.Person.Pledges.Where(p => p.ExternalSource == "Journal " + row.Year).Sum(p => p.Amount));
ShouldEmailColumn = Schema.Columns.AddValueColumn("ShouldEmail", typeof(Boolean), false);
ShouldEmailColumn.AllowNulls = false;
EmailSubjectColumn = Schema.Columns.AddValueColumn("EmailSubject", typeof(String), null);
EmailSubjectColumn.AllowNulls = true;
EmailSourceColumn = Schema.Columns.AddValueColumn("EmailSource", typeof(String), null);
EmailSourceColumn.AllowNulls = true;
EmailAddressesColumn = Schema.Columns.AddCalculatedColumn<MelaveMalkaInvitation, String>("EmailAddresses", row => String.Join(", ", row.Person.EmailAddresses.Select(e => e.Email)));
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "Invitees";
SchemaMapping.SqlSchemaName = "MelaveMalka";
SchemaMapping.Columns.AddMapping(RowIdColumn, "RowId");
SchemaMapping.Columns.AddMapping(PersonColumn, "PersonId");
SchemaMapping.Columns.AddMapping(YearColumn, "Year");
SchemaMapping.Columns.AddMapping(SourceColumn, "Source");
SchemaMapping.Columns.AddMapping(DateAddedColumn, "DateAdded");
SchemaMapping.Columns.AddMapping(ShouldCallColumn, "ShouldCall");
SchemaMapping.Columns.AddMapping(CallerColumn, "Caller");
SchemaMapping.Columns.AddMapping(CallerNoteColumn, "CallerNote");
SchemaMapping.Columns.AddMapping(ShouldEmailColumn, "ShouldEmail");
SchemaMapping.Columns.AddMapping(EmailSubjectColumn, "EmailSubject");
SchemaMapping.Columns.AddMapping(EmailSourceColumn, "EmailSource");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
public void ConflictsTest()
{
using (var connection = SqlProvider.OpenConnection())
connection.ExecuteNonQuery(@"
CREATE TABLE [ConflictsTest](
[ID] UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL PRIMARY KEY DEFAULT(newid()),
[Key] INTEGER NOT NULL UNIQUE,
[Value] INTEGER NULL,
[RowVersion] RowVersion
);");
try {
var schema = new TableSchema("ConflictsTest");
schema.PrimaryKey = schema.Columns.AddValueColumn("ID", typeof(Guid), null);
schema.Columns.AddValueColumn("Key", typeof(int), null).Unique = true;
schema.Columns.AddValueColumn("Value", typeof(int?), null);
var mapping = new SchemaMapping(schema);
var table1 = new Table(schema);
var syncer1 = new TableSynchronizer(table1, mapping, SqlProvider);
var table2 = new Table(schema);
var syncer2 = new TableSynchronizer(table2, mapping, SqlProvider);
Func<Table, int, Row> GetRow = (t, key) => t.Rows.SingleOrDefault(r => r.Field<int>("Key") == key);
Func<int, Row> R1 = k => GetRow(table1, k),
R2 = k => GetRow(table2, k);
table1.Rows.AddFromValues(Guid.NewGuid(), 2, 67);
table2.Rows.AddFromValues(Guid.NewGuid(), 1, null);
table2.Rows.AddFromValues(Guid.NewGuid(), 2, 43);
syncer1.WriteData();
try {
syncer2.WriteData();
Assert.Fail("Conflict succeeded");
} catch (RowException ex) {
Assert.IsNotNull(ex.InnerException);
Assert.AreEqual(R2(2), ex.Row);
}
table2.Rows.Clear();
syncer2.ReadData();
AssertTablesEqual(table1, table2);
table1.Rows.AddFromValues(Guid.NewGuid(), 1, null);
syncer1.WriteData();
syncer2.ReadData();
R1(1)["Value"] = 100;
R2(1)["Value"] = 200;
syncer1.WriteData();
try {
syncer2.WriteData();
Assert.Fail("Conflict succeeded - Update / Update");
} catch (RowModifiedException ex) {
Assert.IsNull(ex.InnerException);
Assert.AreEqual(R2(1), ex.Row);
Assert.AreEqual(R1(1)["Key"], ex.DatabaseValues[schema.Columns["Key"]]);
Assert.AreEqual(R1(1)["Value"], ex.DatabaseValues[schema.Columns["Value"]]);
}
syncer2.ReadData();
AssertTablesEqual(table1, table2);
R1(1)["Value"] = 123;
R2(1).RemoveRow();
syncer1.WriteData();
try {
syncer2.WriteData();
Assert.Fail("Conflict succeeded - Update / Delete");
} catch (RowModifiedException ex) {
Assert.IsNull(ex.InnerException);
Assert.AreEqual(1, ex.Row["Key"]);
Assert.AreEqual(R1(1)["Value"], ex.DatabaseValues[schema.Columns["Value"]]);
}
syncer2.ReadData();
AssertTablesEqual(table1, table2);
R1(1).RemoveRow();
R2(1)["Value"] = -1;
syncer1.WriteData();
try {
syncer2.WriteData();
Assert.Fail("Conflict succeeded - Delete / Update");
} catch (RowDeletedException ex) {
Assert.IsNull(ex.InnerException);
Assert.AreEqual(R2(1), ex.Row);
}
syncer2.ReadData();
AssertTablesEqual(table1, table2);
R1(2).RemoveRow();
R2(2).RemoveRow();
syncer1.WriteData();
syncer2.WriteData(); //Delete / Delete - should not throw
syncer1.ReadData();
Assert.AreEqual(0, table1.Rows.Count);
} finally {
using (var connection = SqlProvider.OpenConnection())
connection.ExecuteNonQuery(@"DROP TABLE ConflictsTest;");
}
}
static MelaveMalkaInfo()
{
#region Create Schema
Schema = new TypedSchema<MelaveMalkaInfo>("MelaveMalkaInfo");
Schema.PrimaryKey = RowIdColumn = Schema.Columns.AddValueColumn("RowId", typeof(Guid), null);
RowIdColumn.Unique = true;
RowIdColumn.AllowNulls = false;
YearColumn = Schema.Columns.AddValueColumn("Year", typeof(Int32), null);
YearColumn.Unique = true;
YearColumn.AllowNulls = false;
AdDeadlineColumn = Schema.Columns.AddValueColumn("AdDeadline", typeof(DateTime), null);
AdDeadlineColumn.AllowNulls = false;
MelaveMalkaDateColumn = Schema.Columns.AddValueColumn("MelaveMalkaDate", typeof(DateTime), null);
MelaveMalkaDateColumn.AllowNulls = false;
HonoreeColumn = Schema.Columns.AddForeignKey("Honoree", Person.Schema, "Honorees");
HonoreeColumn.AllowNulls = false;
SpeakerColumn = Schema.Columns.AddValueColumn("Speaker", typeof(String), null);
SpeakerColumn.AllowNulls = false;
HonoreeTitleColumn = Schema.Columns.AddValueColumn("HonoreeTitle", typeof(String), null);
HonoreeTitleColumn.AllowNulls = false;
Honoree2Column = Schema.Columns.AddForeignKey("Honoree2", Person.Schema, "MelaveMalkaInfoes");
Honoree2Column.AllowNulls = true;
Honoree2TitleColumn = Schema.Columns.AddValueColumn("Honoree2Title", typeof(String), null);
Honoree2TitleColumn.AllowNulls = true;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "MelaveMalkaInfo";
SchemaMapping.SqlSchemaName = "MelaveMalka";
SchemaMapping.Columns.AddMapping(RowIdColumn, "RowId");
SchemaMapping.Columns.AddMapping(YearColumn, "Year");
SchemaMapping.Columns.AddMapping(AdDeadlineColumn, "AdDeadline");
SchemaMapping.Columns.AddMapping(MelaveMalkaDateColumn, "MelaveMalkaDate");
SchemaMapping.Columns.AddMapping(HonoreeColumn, "Honoree");
SchemaMapping.Columns.AddMapping(SpeakerColumn, "Speaker");
SchemaMapping.Columns.AddMapping(HonoreeTitleColumn, "HonoreeTitle");
SchemaMapping.Columns.AddMapping(Honoree2Column, "Honoree2");
SchemaMapping.Columns.AddMapping(Honoree2TitleColumn, "Honoree2Title");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
static PledgeLink()
{
#region Create Schema
Schema = new TypedSchema<PledgeLink>("PledgeLinks");
Schema.PrimaryKey = LinkIdColumn = Schema.Columns.AddValueColumn("LinkId", typeof(Guid), null);
LinkIdColumn.Unique = true;
LinkIdColumn.AllowNulls = false;
PledgeColumn = Schema.Columns.AddForeignKey("Pledge", Pledge.Schema, "LinkedPayments");
PledgeColumn.AllowNulls = false;
PaymentColumn = Schema.Columns.AddForeignKey("Payment", Payment.Schema, "LinkedPledges");
PaymentColumn.AllowNulls = false;
AmountColumn = Schema.Columns.AddValueColumn("Amount", typeof(Decimal), null);
AmountColumn.AllowNulls = false;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "PledgeLinks";
SchemaMapping.SqlSchemaName = "Billing";
SchemaMapping.Columns.AddMapping(LinkIdColumn, "LinkId");
SchemaMapping.Columns.AddMapping(PledgeColumn, "PledgeId");
SchemaMapping.Columns.AddMapping(PaymentColumn, "PaymentId");
SchemaMapping.Columns.AddMapping(AmountColumn, "Amount");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
static EmailAddress()
{
#region Create Schema
Schema = new TypedSchema<EmailAddress>("EmailAddresses");
NameColumn = Schema.Columns.AddValueColumn("Name", typeof(String), null);
NameColumn.AllowNulls = true;
EmailColumn = Schema.Columns.AddValueColumn("Email", typeof(String), null);
EmailColumn.Unique = true;
EmailColumn.AllowNulls = false;
RandomCodeColumn = Schema.Columns.AddValueColumn("RandomCode", typeof(String), null);
RandomCodeColumn.AllowNulls = true;
ActiveColumn = Schema.Columns.AddValueColumn("Active", typeof(Boolean), true);
ActiveColumn.AllowNulls = false;
DateAddedColumn = Schema.Columns.AddValueColumn("DateAdded", typeof(DateTime), null);
DateAddedColumn.AllowNulls = false;
UseHtmlColumn = Schema.Columns.AddValueColumn("UseHtml", typeof(Boolean), true);
UseHtmlColumn.AllowNulls = false;
PersonColumn = Schema.Columns.AddForeignKey("Person", Person.Schema, "EmailAddresses");
PersonColumn.AllowNulls = true;
Schema.PrimaryKey = RowIdColumn = Schema.Columns.AddValueColumn("RowId", typeof(Guid), null);
RowIdColumn.Unique = true;
RowIdColumn.AllowNulls = false;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "tblMLMembers";
SchemaMapping.SqlSchemaName = "dbo";
SchemaMapping.Columns.AddMapping(NameColumn, "Name");
SchemaMapping.Columns.AddMapping(EmailColumn, "Email");
SchemaMapping.Columns.AddMapping(RandomCodeColumn, "ID_Code");
SchemaMapping.Columns.AddMapping(ActiveColumn, "Active");
SchemaMapping.Columns.AddMapping(DateAddedColumn, "Join_Date");
SchemaMapping.Columns.AddMapping(UseHtmlColumn, "HTMLformat");
SchemaMapping.Columns.AddMapping(PersonColumn, "PersonId");
SchemaMapping.Columns.AddMapping(RowIdColumn, "RowId");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
static StagedPerson()
{
#region Create Schema
Schema = new TypedSchema<StagedPerson>("StagedPeople");
Schema.PrimaryKey = StagedPersonIdColumn = Schema.Columns.AddValueColumn("StagedPersonId", typeof(Guid), null);
StagedPersonIdColumn.Unique = true;
StagedPersonIdColumn.AllowNulls = false;
PersonColumn = Schema.Columns.AddForeignKey("Person", Person.Schema, "StagedPeople");
PersonColumn.AllowNulls = true;
HisNameColumn = Schema.Columns.AddValueColumn("HisName", typeof(String), null);
HisNameColumn.AllowNulls = true;
HerNameColumn = Schema.Columns.AddValueColumn("HerName", typeof(String), null);
HerNameColumn.AllowNulls = true;
LastNameColumn = Schema.Columns.AddValueColumn("LastName", typeof(String), null);
LastNameColumn.AllowNulls = false;
FullNameColumn = Schema.Columns.AddValueColumn("FullName", typeof(String), null);
FullNameColumn.AllowNulls = false;
AddressColumn = Schema.Columns.AddValueColumn("Address", typeof(String), null);
AddressColumn.AllowNulls = false;
CityColumn = Schema.Columns.AddValueColumn("City", typeof(String), null);
CityColumn.AllowNulls = true;
StateColumn = Schema.Columns.AddValueColumn("State", typeof(String), null);
StateColumn.AllowNulls = true;
ZipColumn = Schema.Columns.AddValueColumn("Zip", typeof(String), null);
ZipColumn.AllowNulls = true;
PhoneColumn = Schema.Columns.AddValueColumn("Phone", typeof(String), "");
PhoneColumn.AllowNulls = false;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "StagedPeople";
SchemaMapping.SqlSchemaName = "BillingMigration";
SchemaMapping.Columns.AddMapping(StagedPersonIdColumn, "StagedPersonId");
SchemaMapping.Columns.AddMapping(PersonColumn, "PersonId");
SchemaMapping.Columns.AddMapping(HisNameColumn, "HisName");
SchemaMapping.Columns.AddMapping(HerNameColumn, "HerName");
SchemaMapping.Columns.AddMapping(LastNameColumn, "LastName");
SchemaMapping.Columns.AddMapping(FullNameColumn, "FullName");
SchemaMapping.Columns.AddMapping(AddressColumn, "Address");
SchemaMapping.Columns.AddMapping(CityColumn, "City");
SchemaMapping.Columns.AddMapping(StateColumn, "State");
SchemaMapping.Columns.AddMapping(ZipColumn, "Zip");
SchemaMapping.Columns.AddMapping(PhoneColumn, "Phone");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}
static RaffleTicket()
{
#region Create Schema
Schema = new TypedSchema<RaffleTicket>("RaffleTickets");
Schema.PrimaryKey = RowIdColumn = Schema.Columns.AddValueColumn("RowId", typeof(Guid), null);
RowIdColumn.Unique = true;
RowIdColumn.AllowNulls = false;
PersonColumn = Schema.Columns.AddForeignKey("Person", Person.Schema, "RaffleTickets");
PersonColumn.AllowNulls = false;
DateAddedColumn = Schema.Columns.AddValueColumn("DateAdded", typeof(DateTime), null);
DateAddedColumn.AllowNulls = false;
YearColumn = Schema.Columns.AddValueColumn("Year", typeof(Int32), null);
YearColumn.AllowNulls = false;
TicketIdColumn = Schema.Columns.AddValueColumn("TicketId", typeof(Int32), null);
TicketIdColumn.AllowNulls = false;
PaidColumn = Schema.Columns.AddValueColumn("Paid", typeof(Boolean), null);
PaidColumn.AllowNulls = false;
CommentsColumn = Schema.Columns.AddValueColumn("Comments", typeof(String), null);
CommentsColumn.AllowNulls = true;
#endregion
#region Create SchemaMapping
SchemaMapping = new SchemaMapping(Schema, false);
SchemaMapping.SqlName = "RaffleTickets";
SchemaMapping.SqlSchemaName = "MelaveMalka";
SchemaMapping.Columns.AddMapping(RowIdColumn, "RowId");
SchemaMapping.Columns.AddMapping(PersonColumn, "PersonId");
SchemaMapping.Columns.AddMapping(DateAddedColumn, "DateAdded");
SchemaMapping.Columns.AddMapping(YearColumn, "Year");
SchemaMapping.Columns.AddMapping(TicketIdColumn, "TicketId");
SchemaMapping.Columns.AddMapping(PaidColumn, "Paid");
SchemaMapping.Columns.AddMapping(CommentsColumn, "Comments");
#endregion
SchemaMapping.SetPrimaryMapping(SchemaMapping);
}