/// <summary>
/// Returns the sql parameter definition based on the sql data type
/// </summary>
/// <param name="column"></param>
string FormatSqlParameter(Column column)
{
string[] MaxTypes = new string[] { "NVarCharMax", "VarBinaryMax", "VarCharMax" };
string[] LenTypes = new string[] { "Binary", "Char", "DateTime2", "DateTimeOffset", "NChar", "NVarChar", "Time", "VarBinary", "VarChar" };
string[] PreTypes = new string[] { "Decimal", "Numeric" };
// allow null values
string asNull = column.AllowNulls ? " = null" : "";
// @ColumnName VarChar(MAX)
if (MaxTypes.Contains(column.SqlDataTypeString))
return string.Format("@{0} {1}(MAX){2}", column.Name, column.SqlDataTypeString.Replace("Max", ""), asNull);
// @ColumnName VarChar(50)
if (LenTypes.Contains(column.SqlDataTypeString))
return string.Format("@{0} {1}({2}){3}", column.Name, column.SqlDataTypeString, column.Length, asNull);
// @ColumnName Decimal(18, 9)
if (PreTypes.Contains(column.SqlDataTypeString))
return string.Format("@{0} {1}({2}, {3}){4}", column.Name, column.SqlDataTypeString, column.Precision, column.Scale, asNull);
// @ColumnName BigInt
return string.Format("@{0} {1}{2}", column.Name, column.SqlDataTypeString, asNull);
}
/// <summary>
/// Replaces key words in a column format based on the column definition
/// </summary>
/// <param name="format"></param>
/// <param name="column"></param>
/// <returns></returns>
string FormatColumn(string format, Column column)
{
format = format.Replace("ColumnName", column.Name);
format = format.Replace("SystemType", column.SystemTypeString);
format = format.Replace("[?]", column.AllowNulls && column.SystemTypeString != "String" ? "?" : "");
format = format.Replace("SqlParameterDefinition", FormatSqlParameter(column));
format = format.Replace("SqlType", column.SqlDataTypeString.Replace("Max", "")); // VarCharMax / VarBinaryMax sql type doesn't contain the word max in c# code
return format;
}