/// <summary>
/// Print the custom type linked to PKList select
/// </summary>
/// <returns></returns>
private string PrintPKCustomType()
{
var pkFieldParams = String.Join(Environment.NewLine + ", ",
_pkColumns.Select(col =>
{
var colName = col.Name.Parts[2];
return($"[{colName}] {TSqlModelHelper.GetColumnSqlDataType(col)}");
})
);
var grants = String.Join(Environment.NewLine + Environment.NewLine,
_settings.GrantExecuteToRoles.Select(roleName =>
"GRANT EXECUTE" + Environment.NewLine
+ $"ON TYPE::[dbo].[udt{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_PK] TO [{roleName}] AS [dbo];"
+ Environment.NewLine + "GO"));
string output = $@"
CREATE TYPE [dbo].[udt{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_PK] AS TABLE
(
{pkFieldParams}
)
GO
{grants}";
return(output);
}
//TODO to be tested with composite pk and with Non-Identity PK --- And in general
//TODO see if it's ok to not specify the db type and only the ouput direction
/// <summary>
/// Insert template
/// </summary>
/// <returns></returns>
private string PrintInsertMethod()
{
var paramName = FirstCharacterToLower(_entityClassName);
//Exclude de PK identity field to put "Direction Output" in Dapper params
bool isOneColumnIdentity = _pkColumns.Count() == 1 && TSqlModelHelper.IsColumnIdentity(_pkColumns.ToList()[0]);
var normalColumns = isOneColumnIdentity ? _allColumns.Except(_pkColumns) : _allColumns;
string returnType = isOneColumnIdentity
? TSqlModelHelper.GetDotNetDataType(TSqlModelHelper.GetColumnSqlDataType(_pkColumns.ToArray()[0]))
: "bool"; // return bool if insert ok => we cannot return the new Id generated by Identity
//If the PK is one identity field + one another field, we are f...
string returnStatement = (returnType == "bool")
? "return true;"
: $@"return p.Get<{returnType}> (""@{_pkColumns.ToArray()[0].Name.Parts[2]}"");";
string spPkParams = isOneColumnIdentity
? String.Join(Environment.NewLine + " ",
_pkColumns.Select(col =>
{
var colName = col.Name.Parts[2];
return($@"p.Add(""@{colName}"", dbType:{TSqlModelHelper.GetDotNetDataType_SystemDataDbTypes(TSqlModelHelper.GetColumnSqlDataType(col,false))}, direction: ParameterDirection.Output);");
}))
: string.Empty; // no identity PK
//Excluded columns in the SP
var tmpColumns = TableSettings.SqlInsertSettings.FieldNamesExcluded != null
? normalColumns.Where(c => !TableSettings.SqlInsertSettings.FieldNamesExcluded.Split(',').Contains(c.Name.Parts[2]))
: normalColumns;
string spNormalParams = String.Join(Environment.NewLine + " ",
tmpColumns.Select(col =>
{
var colName = col.Name.Parts[2];
var entityProp = TSqlModelHelper.PascalCase(colName);
return($@"p.Add(""@{colName}"", {paramName}.{entityProp});");
}));
string output = $@"
/// <summary>
/// Insert
/// </summary>
public async Task<{returnType}> Insert({_entityClassFullName} {paramName})
{{
var p = new DynamicParameters();
{spPkParams}
{spNormalParams}
_ = await _dbContext.Connection.ExecuteAsync
(""usp{_entityClassName}_insert"", p, commandType: CommandType.StoredProcedure, transaction: _dbContext.Transaction);
{returnStatement}
}}";
return(output);
}
//TODO see if it's ok to use Dot net type or if we need to use true System.Data.SqlTypes to create the table type
//that will be injected for bulk insert
/// <summary>
/// Table type template for bulkinsert
/// </summary>
/// <returns></returns>
private string PrintTableTypeForBulkInsert()
{
var removeIdentityColumns = _allColumns.Where(col => !col.GetProperty <bool>(Column.IsIdentity));
// Hint: 'addRows' will be filled in addColumns
string addRows = String.Join(Environment.NewLine + " ",
removeIdentityColumns.Select(c =>
{
var colName = c.Name.Parts[2];
var colIsNullable = c.IsColumnNullable();
var colSqlType = TSqlModelHelper.GetDotNetDataType_SystemDataSqlTypes(TSqlModelHelper.GetColumnSqlDataType(c, false));
// TODO: Better check if column type in settings is an enum
var forceIntForEnum = colSqlType == "SqlInt32" ? "(int)" : string.Empty;
return(!colIsNullable || colSqlType == "SqlString" || colSqlType == "SqlBinary"
? $@"row[""{colName}""] = new {colSqlType}({forceIntForEnum}curObj.{TSqlModelHelper.PascalCase(colName)});"
: $@"row[""{colName}""] = curObj.{TSqlModelHelper.PascalCase(colName)} == null ? "
+ $@"{colSqlType}.Null"
+ $@" : new {colSqlType}({forceIntForEnum}curObj.{TSqlModelHelper.PascalCase(colName)}.Value);");
})
);
string addColumns = String.Join(Environment.NewLine + " ",
removeIdentityColumns.Select(c =>
{
var colName = c.Name.Parts[2];
var colSqlType = TSqlModelHelper.GetDotNetDataType_SystemDataSqlTypes(TSqlModelHelper.GetColumnSqlDataType(c, false));
return($@"dt.Columns.Add(""{colName}"", typeof({colSqlType}));");
})
);
string output = $@"
/// <summary>
/// Create special db table for bulk insert
/// </summary>
private object Create{_entityClassName}DataTable(IEnumerable<{_entityClassFullName}> {_entityClassName}List)
{{
DataTable dt = new DataTable();
{addColumns}
if ({_entityClassName}List != null)
foreach (var curObj in {_entityClassName}List)
{{
DataRow row = dt.NewRow();
{addRows}
dt.Rows.Add(row);
}}
return dt.AsTableValuedParameter();
}}";
return(output);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private string PrintPKTypeForSelectByPKList()
{
string addColumns = String.Join(Environment.NewLine + " ",
_pkColumns.Select(c =>
{
var colName = c.Name.Parts[2];
var colSqlType = TSqlModelHelper.GetDotNetDataType_SystemDataSqlTypes(TSqlModelHelper.GetColumnSqlDataType(c, false));
return($@" dt.Columns.Add(""{colName}"", typeof({colSqlType}));");
}));
string addRows = String.Join(Environment.NewLine + " ",
_pkColumns.Select(c =>
{
var colName = c.Name.Parts[2];
var colSqlType = TSqlModelHelper.GetDotNetDataType_SystemDataSqlTypes(TSqlModelHelper.GetColumnSqlDataType(c, false));
// TODO: Better check if column type in settings is an enum
var forceIntForEnum = colSqlType == "SqlInt32" ? "(int)" : string.Empty;
return($@"row[""{colName}""] = new {colSqlType}({forceIntForEnum}curObj.{TSqlModelHelper.PascalCase(colName)});");
}));
string output = $@"
/// <summary>
/// Create special db table for select by PK List
/// </summary>
private object Create{_entityClassName}PKDataTable(IEnumerable<{_entityClassFullName}_PK> pkList)
{{
DataTable dt = new DataTable();
{addColumns}
if (pkList != null)
foreach (var curObj in pkList)
{{
DataRow row = dt.NewRow();
{addRows}
dt.Rows.Add(row);
}}
return dt.AsTableValuedParameter();
}}";
return(output);
}