/// <summary>
/// Build a T-SQL update statement using the given table specification and key column.
/// </summary>
/// <param name="tableSpecification">The table to target for the update statement.</param>
/// <param name="keyColumn">The key column to use for the update predicate.</param>
/// <returns>A string update statement. Empty string if keyColumn is null.</returns>
private string BuildUpdateStatement(TableSpecification tableSpecification, ColumnSpecification keyColumn)
{
if (keyColumn == null)
{
return(_keyNotFound);
}
var updateSetList = new StringBuilder();
foreach (var col in tableSpecification.ColumnSpecifications)
{
if (col.IsIdentity || col.IsComputed)
{
continue;
}
if (updateSetList.Length > 0)
{
updateSetList.Append(", ");
}
updateSetList.Append(QuoteName(col.ColumnName) + " = @" + col.ColumnName);
}
return(string.Format(TemplateLibrary.TSql.Update, QuoteName(tableSpecification.SchemaName), QuoteName(tableSpecification.TableName), updateSetList.ToString(), QuoteName(keyColumn.ColumnName), keyColumn.ColumnName));
}
/// <summary>
/// Build a T-SQL insert statement using the given table specification.
/// </summary>
/// <param name="tableSpecification">The table to target for the insert statement.</param>
/// <returns>A string insert statement.</returns>
private string BuildInsertStatement(TableSpecification tableSpecification)
{
var insertColumnList = new StringBuilder();
var insertParameterList = new StringBuilder();
foreach (var col in tableSpecification.ColumnSpecifications)
{
if (col.IsIdentity || col.IsComputed)
{
continue;
}
// Column list
if (insertColumnList.Length > 0)
{
insertColumnList.Append(", ");
}
insertColumnList.Append(QuoteName(col.ColumnName));
// Parameter list
if (insertParameterList.Length > 0)
{
insertParameterList.Append(", ");
}
insertParameterList.Append("@" + col.ColumnName);
}
return(string.Format(TemplateLibrary.TSql.Insert, QuoteName(tableSpecification.SchemaName), QuoteName(tableSpecification.TableName), insertColumnList.ToString(), insertParameterList.ToString()));
}
/// <summary>
/// Build a T-SQL delete statement using the given table specification and key column.
/// </summary>
/// <param name="tableSpecification">The table to target for the delete statement.</param>
/// <param name="keyColumn">The key column to use for the delete predicate.</param>
/// <returns>A string delete statement. Empty string if keyColumn is null.</returns>
private string BuildDeleteStatement(TableSpecification tableSpecification, ColumnSpecification keyColumn)
{
if (keyColumn == null)
{
return(_keyNotFound);
}
return(string.Format(TemplateLibrary.TSql.Delete, QuoteName(tableSpecification.SchemaName), QuoteName(tableSpecification.TableName), QuoteName(keyColumn.ColumnName), keyColumn.ColumnName));
}
public static ForeignKey CreateForeignKey(this TableSpecification table, Entity entity)
{
var fkid = table.CreateIntColumn($"{entity.Name}Id");
var index = table.CreateIndex(fkid);
var fk = index.CreateForeignKey(entity.PrimaryKey);
return(new ForeignKey
{
Column = fkid,
Index = index,
Specification = fk
});
}
/// <summary>
/// Build a T-SQL delete statement using the given table specification.
/// </summary>
/// <param name="tableSpecification">The table to target for the select (get all) statement.</param>
/// <returns>A string select statement.</returns>
private string BuildGetAllStatement(TableSpecification tableSpecification)
{
var getColumnList = new StringBuilder();
foreach (var col in tableSpecification.ColumnSpecifications)
{
if (getColumnList.Length > 0)
{
getColumnList.Append(", ");
}
getColumnList.Append(QuoteName(col.ColumnName));
}
return(string.Format(TemplateLibrary.TSql.GetAll, getColumnList.ToString(), QuoteName(tableSpecification.SchemaName), QuoteName(tableSpecification.TableName)));
}
/// <summary>
/// Attempt to find the key column for the given column specification.
/// </summary>
/// <param name="tableSpecification">The table specification to analyze.</param>
/// <returns>The key ColumSpecification, if found.</returns>
private ColumnSpecification FindKeyColumn(TableSpecification tableSpecification)
{
var id = tableSpecification.ColumnSpecifications.SingleOrDefault(c => string.Equals(c.ColumnName, "id", StringComparison.OrdinalIgnoreCase));
if (id == null)
{
id = tableSpecification.ColumnSpecifications.SingleOrDefault(c => string.Equals(c.ColumnName, tableSpecification.TableName + "id", StringComparison.OrdinalIgnoreCase));
}
if (id == null)
{
id = tableSpecification.ColumnSpecifications.SingleOrDefault(c => string.Equals(c.ColumnName, tableSpecification.TableName + "_id", StringComparison.OrdinalIgnoreCase));
}
if (id != null)
{
return(id);
}
var key = tableSpecification.ColumnSpecifications.SingleOrDefault(c => string.Equals(c.ColumnName, "key", StringComparison.OrdinalIgnoreCase));
if (key == null)
{
key = tableSpecification.ColumnSpecifications.SingleOrDefault(c => string.Equals(c.ColumnName, tableSpecification.TableName + "key", StringComparison.OrdinalIgnoreCase));
}
if (key == null)
{
key = tableSpecification.ColumnSpecifications.SingleOrDefault(c => string.Equals(c.ColumnName, tableSpecification.TableName + "_key", StringComparison.OrdinalIgnoreCase));
}
if (key != null)
{
return(key);
}
var identity = tableSpecification.ColumnSpecifications.SingleOrDefault(c => c.IsIdentity);
if (identity != null)
{
return(identity);
}
return(null);
}
/// <summary>
/// Build a T-SQL delete statement using the given table specification and key column.
/// </summary>
/// <param name="tableSpecification">The table to target for the select (get by key) statement.</param>
/// <param name="keyColumn">The key column to use for the select predicate.</param>
/// <returns>A string select statement. Empty string if keyColumn is null.</returns>
private string BuildGetByKeyStatement(TableSpecification tableSpecification, ColumnSpecification keyColumn)
{
if (keyColumn == null)
{
return(_keyNotFound);
}
var getColumnList = new StringBuilder();
foreach (var col in tableSpecification.ColumnSpecifications)
{
if (getColumnList.Length > 0)
{
getColumnList.Append(", ");
}
getColumnList.Append(QuoteName(col.ColumnName));
}
return(string.Format(TemplateLibrary.TSql.GetByKey, getColumnList.ToString(), QuoteName(tableSpecification.SchemaName), QuoteName(tableSpecification.TableName), QuoteName(keyColumn.ColumnName), keyColumn.ColumnName));
}
/// <summary>
/// Generates a T-SQL code file (.sql) containing queries for GetByKey, GetAll, Insert, Update, and Delete operations.
/// </summary>
/// <param name="tableSpecification">The table specification upon which to base the generated queries.</param>
/// <returns>A GeneratedFile of type TSql.</returns>
public GeneratedFile GenerateFile(TableSpecification tableSpecification)
{
var keyColumn = FindKeyColumn(tableSpecification);
var getById = BuildGetByKeyStatement(tableSpecification, keyColumn);
var getAll = BuildGetAllStatement(tableSpecification);
var insert = BuildInsertStatement(tableSpecification);
var update = BuildUpdateStatement(tableSpecification, keyColumn);
var delete = BuildDeleteStatement(tableSpecification, keyColumn);
var result = new GeneratedFile
{
FileName = tableSpecification.TableName + ".sql",
FileContents = string.Format(TemplateLibrary.TSql.QueryFile, getById, getAll, insert, update, delete),
FileType = OutputFileType
};
return(result);
}
/// <summary>
/// Returns a simple customer table specification.
/// </summary>
/// <param name="addXmlProp">Whether or not an XML property should be included.</param>
/// <param name="addSqlSpecificProps">Whether or not SQL Server specific property types should be included (Geography, Geometry, etc.).</param>
/// <param name="addExtendedProps">Whether or not to add extended properties (e.g., Password, EmailAddress, etc.).</param>
/// <returns>Customer TableSpecification.</returns>
internal static TableSpecification GetCustomerTableSpec(bool addXmlProp = false, bool addSqlSpecificProps = false, bool addExtendedProps = false)
{
var spec = new TableSpecification
{
TableName = "Customer",
SchemaName = "dbo",
TableOrigin = SpecificationProvider.SqlServer,
ColumnSpecifications = new List <ColumnSpecification>()
{
new ColumnSpecification
{
ColumnName = "Id",
ColumnType = 56,
IsNullable = false,
IsIdentity = true
},
new ColumnSpecification
{
ColumnName = "FirstName",
ColumnType = 167,
IsNullable = false,
ColumnLength = 50
},
new ColumnSpecification
{
ColumnName = "LastName",
ColumnType = 167,
IsNullable = false,
ColumnLength = 50
},
new ColumnSpecification
{
ColumnName = "ContactPhone",
ColumnType = 167,
IsNullable = true,
ColumnLength = 12
},
new ColumnSpecification
{
ColumnName = "ZipCode",
ColumnType = 167,
IsNullable = true,
ColumnLength = 12
}
}
};
if (addExtendedProps)
{
spec.ColumnSpecifications.AddRange(new List <ColumnSpecification>()
{
new ColumnSpecification
{
ColumnName = "Password",
ColumnType = 167,
IsNullable = true,
ColumnLength = 50
},
new ColumnSpecification
{
ColumnName = "EmailAddress",
ColumnType = 167,
IsNullable = true,
ColumnLength = 100
},
new ColumnSpecification
{
ColumnName = "CreditCardNumber",
ColumnType = 167,
IsNullable = true,
ColumnLength = 16
},
new ColumnSpecification
{
ColumnName = "Age",
ColumnType = 56,
IsComputed = true
}
});
}
if (addXmlProp)
{
spec.ColumnSpecifications.Add(new ColumnSpecification
{
ColumnName = "Preferences",
ColumnType = 241,
IsNullable = true
});
}
if (addSqlSpecificProps)
{
spec.ColumnSpecifications.AddRange(new List <ColumnSpecification>()
{
new ColumnSpecification
{
ColumnName = "HierarchyId",
ColumnType = 128,
IsNullable = true,
IsIdentity = false
},
new ColumnSpecification
{
ColumnName = "Location",
ColumnType = 130,
IsNullable = true
},
new ColumnSpecification
{
ColumnName = "DropboxDimensions",
ColumnType = 129,
IsNullable = true
}
});
}
return(spec);
}
/// <summary>
/// Generates a C# code file (.cs) containing a class definition matching the given table specification.
/// </summary>
/// <param name="tableSpecification">The table specification upon which to base the C# class file.</param>
/// <returns>A GeneratedFile of type CSharp.</returns>
public GeneratedFile GenerateFile(TableSpecification tableSpecification)
{
tableSpecification.TableName = RemoveInvalidCharacters(tableSpecification.TableName);
var properties = new StringBuilder();
var addXmlNameSpace = false;
var addSqlTypeNameSpace = false;
foreach (var col in tableSpecification.ColumnSpecifications)
{
col.ColumnName = RemoveInvalidCharacters(col.ColumnName);
if (properties.Length > 0)
{
properties.Append("\n\n\t\t");
}
properties.Append(BuildPropertyString(col, tableSpecification.TableOrigin));
// There are using statements that potentially need to be added for certain SQL Server column types.
if (tableSpecification.TableOrigin == SpecificationProvider.SqlServer)
{
if (!addXmlNameSpace && (SqlServerColumnType)col.ColumnType == SqlServerColumnType.Xml)
{
addXmlNameSpace = true;
}
if (!addSqlTypeNameSpace &&
((SqlServerColumnType)col.ColumnType == SqlServerColumnType.HierarchyId ||
(SqlServerColumnType)col.ColumnType == SqlServerColumnType.Geometry ||
(SqlServerColumnType)col.ColumnType == SqlServerColumnType.Geography))
{
addSqlTypeNameSpace = true;
}
}
}
var usings = new StringBuilder();
_usingStatements.ForEach(u => { if (usings.Length > 0)
{
usings.Append("\n");
}
usings.Append(u); });
if (addXmlNameSpace)
{
usings.Append("\nusing System.Linq.Xml;");
}
if (addSqlTypeNameSpace)
{
usings.Append("\nusing Microsoft.SqlServer.Types;");
}
if (AddAnnotations)
{
usings.Append("\nusing System.ComponentModel.DataAnnotations;");
}
var result = new GeneratedFile
{
FileName = tableSpecification.TableName + ".cs",
FileContents = string.Format(TemplateLibrary.CSharp.ClassFile, usings.ToString(), ModelNameSpace, tableSpecification.TableName, properties.ToString()),
FileType = OutputFileType
};
return(result);
}