private string GenerateInterface()
{
var repoMemberDeclarations = String.Join(Environment.NewLine + " ",
_tables.Select(currTable =>
{
var pascalTableName = TSqlModelHelper.PascalCase(currTable.Name.Parts[1]);
var lowerTableName = currTable.Name.Parts[1].ToLower();
var tableSettings = GeneratorSettings.TablesSettings.ContainsKey(lowerTableName) ?
GeneratorSettings.TablesSettings[lowerTableName]
: GeneratorSettings.GlobalSettings;
var repoNamespace = tableSettings.CsRepositorySettings.Namespace;
var repoInterfaceName = "I" + pascalTableName + "Repo";
var repoPropertyName = pascalTableName + "Repo";
return($"{repoNamespace}.{repoInterfaceName} {repoPropertyName} {{ get; }}");
})
);
string output = $@"
public interface {_interfaceName} : IDisposable
{{
IDbConnection Connection {{ get; }}
IDbTransaction Transaction {{ get; }}
Task<IDbTransaction> OpenTransaction();
Task<IDbTransaction> OpenTransaction(IsolationLevel level);
void CommitTransaction(bool disposeTrans = true);
void RollbackTransaction(bool disposeTrans = true);
{repoMemberDeclarations}
}}
";
return(output);
}
/// <summary>
/// If composite pk, need to define a custom type for select by PKList
/// </summary>
/// <returns></returns>
private string PrintPkType()
{
if (!TableSettings.GenerateSelectByPkList)
{
return(string.Empty);
}
var memberDeclarations = String.Join(Environment.NewLine + " ", TSqlModelHelper.GetPrimaryKeyColumns(Table).Select(col =>
{
var colName = col.Name.Parts[2];
var memberName = TSqlModelHelper.PascalCase(col.Name.Parts[2]);
var colDataType = col.GetColumnSqlDataType(false);
//Search for custom member type or use the conversion from Sql Types
var hasCustomMemberType = _settings?.FieldNameCustomTypes?.ContainsKey(colName) ?? false;
var memberType = hasCustomMemberType ? _settings.FieldNameCustomTypes[colName]
: TSqlModelHelper.GetDotNetDataType(colDataType);
return($"public {memberType} {memberName} {{ get; set; }}");
}));
return
($@"
/// =================================================================
/// Author: {GeneratorSettings.AuthorName}
/// Description: PK class for the table {Table.Name}
/// It's bit heavy (a lot of useless types in the DB) but you can
/// use get by PKList even if your pk is a composite one...
/// =================================================================
public class {TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_PK
{{
{memberDeclarations}
}}");
}
/// <summary>
/// Load the dacpac model
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void ButtonLoadModel_Click(object sender, RoutedEventArgs e)
{
try
{
if (string.IsNullOrEmpty(DacpacPath))
{
MessageBox.Show("No dacpac file selected.", "Error to load", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
else
{
_loading++;
buttonLoadModel.IsEnabled = false;
IsEnabled = false;
Model = await Task.Run(() => TSqlModelHelper.LoadModel(DacpacPath));
LoadTablesFromModel();
Roles = Model.GetAllRoles();
ucGlobalSettings.InitGlobalSettings();
ucGlobalSettings.IsEnabled = true;
IsEnabled = true;
buttonLoadConfig.IsEnabled = true;
buttonSaveConfig.IsEnabled = true;
_loading--;
MessageBox.Show("Model loaded successfully", "Info", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
catch (Exception exc)
{
MessageBox.Show(exc.ToString(), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
buttonLoadModel.IsEnabled = true;
}
}
/// <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);
}
public override string Generate()
{
if (!TableSettings.GenerateSelectByPkList && !PreviewMode)
{
return(string.Empty);
}
_pkColumns = Table.GetPrimaryKeyColumns();
var innerJoins = String.Join(Environment.NewLine + " AND ",
_pkColumns.Select(col =>
{
var colName = col.Name.Parts[2];
return($"INNER JOIN @pk_list [B] ON [A].[{colName}] = [B].[{colName}]");
})
);
var grants = String.Join(Environment.NewLine + Environment.NewLine,
_settings.GrantExecuteToRoles.Select(roleName =>
"GRANT EXECUTE" + Environment.NewLine
+ $"ON OBJECT::[dbo].[usp{ TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_selectByPKList] TO [{roleName}] AS [dbo];"
+ Environment.NewLine + "GO")
);
string output =
$@"
-- =================================================================
-- Author: {GeneratorSettings.AuthorName}
-- Description: Select By PKList Procedure for the table {Table.Name}
-- =================================================================
CREATE PROCEDURE [dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_selectByPKList]
(
@pk_list [dbo].[udt{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_PK] READONLY
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@strErrorMessage NVARCHAR(4000),
@intErrorSeverity INT,
@intErrorState INT,
@intErrorLine INT;
BEGIN TRY
SELECT [A].*
FROM {Table.Name} [A]
{innerJoins}
END TRY
BEGIN CATCH
SELECT
@strErrorMessage = ERROR_MESSAGE()
+ ' Line:'
+ CONVERT(VARCHAR(5), ERROR_LINE()),
@intErrorSeverity = ERROR_SEVERITY(),
@intErrorState = ERROR_STATE();
RAISERROR(
@strErrorMessage, -- Message text.
@intErrorSeverity, -- Severity.
@intErrorState -- State.
);
END CATCH;
END
GO
{grants}
";
return(output + PrintPKCustomType());
}
public override string Generate()
{
if (!TableSettings.GenerateBulkInsertSP && !PreviewMode)
{
return(string.Empty);
}
var allColumns = Table.GetAllColumns();
//Exclude columns according to settings
allColumns = _settings.FieldNamesExcluded != null
? allColumns.Where(c => !_settings.FieldNamesExcluded.Split(',').Contains(c.Name.Parts[2]))
: allColumns;
// Identity columns will appear as output parameters
var nonIdentityColumns = allColumns.Where(col => !col.GetProperty <bool>(Column.IsIdentity));
var identityColumns = allColumns.Where(col => col.GetProperty <bool>(Column.IsIdentity));
var tableTypeName = $"[dbo].[udt{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}]";
var insertClause_columns = String.Join(Environment.NewLine + " , ",
nonIdentityColumns.Select(col =>
{
var colName = col.Name.Parts[2];
return($"[{colName}]");
}));
var grants = String.Join(Environment.NewLine + Environment.NewLine,
_settings.GrantExecuteToRoles.Select(roleName =>
"GRANT EXECUTE" + Environment.NewLine
+ $"ON OBJECT::[dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_bulkInsert] TO [{roleName}] AS [dbo];"
+ Environment.NewLine + "GO")
);
string output =
$@"
-- =================================================================
-- Author: {this.GeneratorSettings.AuthorName}
-- Description: Bulk Insert Procedure for the table {Table.Name}
-- =================================================================
CREATE PROCEDURE [dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_bulkInsert]
(
@items {tableTypeName} READONLY
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@strErrorMessage NVARCHAR(4000),
@intErrorSeverity INT,
@intErrorState INT,
@intErrorLine INT;
BEGIN TRY
INSERT INTO {Table.Name}
SELECT {insertClause_columns}
FROM @items
END TRY
BEGIN CATCH
SELECT
@strErrorMessage = ERROR_MESSAGE()
+ ' Line:'
+ CONVERT(VARCHAR(5), ERROR_LINE()),
@intErrorSeverity = ERROR_SEVERITY(),
@intErrorState = ERROR_STATE();
RAISERROR(
@strErrorMessage, -- Message text.
@intErrorSeverity, -- Severity.
@intErrorState -- State.
);
END CATCH;
END
GO
{grants}
";
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);
}
//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);
}
/// <summary>
/// Loop on each uk to generate the output
/// </summary>
/// <returns></returns>
private IEnumerable <string> GenerateForEachUk()
{
var uks = Table.GetUniqueKeysWithColumns();
foreach (var ukColumns in uks)
{
var inputParamDeclarations = String.Join(Environment.NewLine + ", ",
ukColumns.Select(col =>
{
var colName = col.Name.Parts[2];
var colDataType = col.GetColumnSqlDataType();
return($"@{colName} {colDataType}");
})
);
var whereClause_conditions = String.Join(Environment.NewLine + " AND ",
ukColumns.Select(col =>
{
var colName = col.Name.Parts[2];
return($"[{colName}] = @{colName}");
})
);
var ukFieldNames = String.Join("And",
ukColumns.Select(col =>
{
var colName = col.Name.Parts[2];
return($"{TSqlModelHelper.PascalCase(colName)}");
})
);
var grants = String.Join(Environment.NewLine + Environment.NewLine,
_settings.GrantExecuteToRoles.Select(roleName =>
"GRANT EXECUTE" + Environment.NewLine
+ $"ON OBJECT::[dbo].[usp{ TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_selectBy{ukFieldNames}] TO [{roleName}] AS [dbo];"
+ Environment.NewLine + "GO")
);
string output =
$@"
-- =================================================================
-- Author: {GeneratorSettings.AuthorName}
-- Description: Select By UK Procedure for the table {Table.Name}
-- =================================================================
CREATE PROCEDURE [dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_selectBy{ukFieldNames}]
(
{inputParamDeclarations}
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@strErrorMessage NVARCHAR(4000),
@intErrorSeverity INT,
@intErrorState INT,
@intErrorLine INT;
BEGIN TRY
SELECT * FROM {Table.Name}
WHERE {whereClause_conditions}
END TRY
BEGIN CATCH
SELECT
@strErrorMessage = ERROR_MESSAGE()
+ ' Line:'
+ CONVERT(VARCHAR(5), ERROR_LINE()),
@intErrorSeverity = ERROR_SEVERITY(),
@intErrorState = ERROR_STATE();
RAISERROR(
@strErrorMessage, -- Message text.
@intErrorSeverity, -- Severity.
@intErrorState -- State.
);
END CATCH;
END
GO
{grants}
";
yield return(output);
}
}
public override string Generate()
{
if (!TableSettings.GenerateEntities && !PreviewMode)
{
return(string.Empty);
}
var allColumns = Table.GetAllColumns();
var pkColumns = TSqlModelHelper.GetPrimaryKeyColumns(Table);
// ICloneable interface
var iCloneableFuncStr = "public object Clone()" + Environment.NewLine +
" {" + Environment.NewLine +
" return this.MemberwiseClone();" + Environment.NewLine +
" }";
var iCloneable = _settings.ImplementICloneable ? " : System.ICloneable" : null;
var iCloneableMethod = _settings.ImplementICloneable ? iCloneableFuncStr : null;
// Custom interface names
string interfaceNames = "";
if (!string.IsNullOrEmpty(_settings.ImplementCustomInterfaceNames))
{
interfaceNames = (_settings.ImplementICloneable)
? ", " + _settings.ImplementCustomInterfaceNames
: " : " + _settings.ImplementCustomInterfaceNames;
}
var memberDeclarations = String.Join(Environment.NewLine + " ", allColumns.Select(col =>
{
var colName = col.Name.Parts[2];
var memberName = TSqlModelHelper.PascalCase(col.Name.Parts[2]);
var colDataType = col.GetColumnSqlDataType(false);
var isNullable = col.IsColumnNullable();
bool isPk = (pkColumns.SingleOrDefault(c => c.Name.Parts[2] == colName) != null) ? true : false;
//Search for custom member type or use the conversion from Sql Types
var hasCustomMemberType = _settings?.FieldNameCustomTypes?.ContainsKey(colName) ?? false;
var memberType = hasCustomMemberType ? _settings.FieldNameCustomTypes[colName]
: TSqlModelHelper.GetDotNetDataType(colDataType, isNullable);
//Decorators
var decorators = "";
//String length
if (_settings.StandardStringLengthDecorator)
{
if (memberType == "string")
{
var colLen = col.GetProperty <int>(Column.Length);
if (colLen > 0)
{
decorators += $"[System.ComponentModel.DataAnnotations.StringLength({colLen})]"
+ Environment.NewLine + " ";
}
}
}
// TODO : I don't think the condition is correct, check this with fab
//Required
if (_settings.StandardRequiredDecorator)
{
if (!isNullable && !isPk)
{
decorators += $"[System.ComponentModel.DataAnnotations.Required]"
+ Environment.NewLine + " ";
}
}
//Json ignore
if (_settings.StandardJsonIgnoreDecorator)
{
var colFound = _settings.FieldNamesWithJsonIgnoreDecorator
.Split(',').Any(c => c == colName);
if (colFound)
{
decorators += $"[Newtonsoft.Json.JsonIgnore]"
+ Environment.NewLine + " ";
}
}
//Custom field decorator
if (_settings.FieldNameCustomDecorators != null && _settings.FieldNameCustomDecorators.Count > 0)
{
if (_settings.FieldNameCustomDecorators.TryGetValue(colName, out string customDecorator))
{
decorators += customDecorator
+ Environment.NewLine + " ";
}
}
return($"{decorators}public {memberType} {memberName} {{ get; set; }}" + Environment.NewLine);
}));
string pkType = PrintPkType();
string output =
$@"
namespace { _settings.Namespace } {{
{pkType}
/// =================================================================
/// Author: {GeneratorSettings.AuthorName}
/// Description: Entity class for the table {Table.Name}
/// =================================================================
public class {TSqlModelHelper.PascalCase(Table.Name.Parts[1])}{iCloneable}{interfaceNames}
{{
{memberDeclarations}
{iCloneableMethod}
}}
}}
";
return(output);
}
public override string Generate()
{
if (!TableSettings.GenerateBulkUpdateSP && !PreviewMode)
{
return(string.Empty);
}
var allColumns = Table.GetAllColumns();
//Exclude columns according to settings
allColumns = _settings.FieldNamesExcluded != null
? allColumns.Where(c => !_settings.FieldNamesExcluded.Split(',').Contains(c.Name.Parts[2]))
: allColumns;
// Identity columns will appear as output parameters
var nonIdentityColumns = allColumns.Where(col => !col.GetProperty <bool>(Column.IsIdentity));
var identityColumns = allColumns.Where(col => col.GetProperty <bool>(Column.IsIdentity));
var pkColumns = Table.GetPrimaryKeyColumns();
var tableTypeName = $"[dbo].[udt{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}]";
var inputParamDeclarations = String.Join(Environment.NewLine + ", ",
allColumns.Select(col =>
{
var colName = col.Name.Parts[2];
var colDataType = col.GetColumnSqlDataType();
return($"@{colName} {colDataType}");
})
);
var updateClause_setStatements = String.Join(Environment.NewLine + " , ", nonIdentityColumns.Select(col =>
{
var colName = col.Name.Parts[2];
return($"a.[{colName}] = i.[{colName}]");
}));
//var whereClause_conditions = String.Join(" AND ", pkColumns.Select(col =>
//{
// var colName = col.Name.Parts[2];
// return $"[{colName}] = @{colName}";
//}));
//Only support one field PKS for the moment
var colNameStr = pkColumns.First().Name.Parts[2];
var joinClause = $"INNER JOIN @items i ON " + $"a.[{colNameStr}] = i.{colNameStr}";
var grants = String.Join(Environment.NewLine + Environment.NewLine,
_settings.GrantExecuteToRoles.Select(roleName =>
"GRANT EXECUTE" + Environment.NewLine
+ $"ON OBJECT::[dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_bulkUpdate] TO [{roleName}] AS [dbo];"
+ Environment.NewLine + "GO")
);
string output =
$@"
-- =================================================================
-- Author: {this.GeneratorSettings.AuthorName}
-- Description: Bulk Update Procedure for the table {Table.Name}
-- =================================================================
CREATE PROCEDURE [dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_bulkUpdate]
(
@items {tableTypeName} READONLY
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@strErrorMessage NVARCHAR(4000),
@intErrorSeverity INT,
@intErrorState INT,
@intErrorLine INT;
BEGIN TRY
UPDATE a
SET
{updateClause_setStatements}
FROM {Table.Name} a
{joinClause}
END TRY
BEGIN CATCH
SELECT
@strErrorMessage = ERROR_MESSAGE()
+ ' Line:'
+ CONVERT(VARCHAR(5), ERROR_LINE()),
@intErrorSeverity = ERROR_SEVERITY(),
@intErrorState = ERROR_STATE();
RAISERROR(
@strErrorMessage, -- Message text.
@intErrorSeverity, -- Severity.
@intErrorState -- State.
);
END CATCH;
END
GO
{grants}
";
return(output);
}
public override string Generate()
{
if (!TableSettings.GenerateDeleteSP && !PreviewMode)
{
return(string.Empty);
}
var pkColumns = Table.GetPrimaryKeyColumns();
var inputParamDeclarations = String.Join(Environment.NewLine + ", ", pkColumns.Select(col =>
{
var colName = col.Name.Parts[2];
var colDataType = col.GetColumnSqlDataType();
return($"@{colName} {colDataType}");
}));
var whereClause_conditions = String.Join(" AND ", pkColumns.Select(col =>
{
var colName = col.Name.Parts[2];
return($"[{colName}] = @{colName}");
}));
var grants = String.Join(Environment.NewLine + Environment.NewLine,
_settings.GrantExecuteToRoles.Select(roleName =>
"GRANT EXECUTE" + Environment.NewLine
+ $"ON OBJECT::[dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_delete] TO [{roleName}] AS [dbo];"
+ Environment.NewLine + "GO")
);
string output =
$@"
-- =================================================================
-- Author: {GeneratorSettings.AuthorName}
-- Description: Delete Procedure for the table {Table.Name}
-- =================================================================
CREATE PROCEDURE [dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_delete]
(
{inputParamDeclarations}
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@strErrorMessage NVARCHAR(4000),
@intErrorSeverity INT,
@intErrorState INT,
@intErrorLine INT;
BEGIN TRY
DELETE FROM {Table.Name}
WHERE {whereClause_conditions}
END TRY
BEGIN CATCH
SELECT
@strErrorMessage = ERROR_MESSAGE()
+ ' Line:'
+ CONVERT(VARCHAR(5), ERROR_LINE()),
@intErrorSeverity = ERROR_SEVERITY(),
@intErrorState = ERROR_STATE();
RAISERROR(
@strErrorMessage, -- Message text.
@intErrorSeverity, -- Severity.
@intErrorState -- State.
);
END CATCH;
END
GO
{grants}
";
return(output);
}
private string GenerateClass()
{
var repoMemberDefinitions = String.Join(Environment.NewLine,
_tables.Select(currTable =>
{
var pascalTableName = TSqlModelHelper.PascalCase(currTable.Name.Parts[1]);
var lowerTableName = currTable.Name.Parts[1].ToLower();
var tableSettings = GeneratorSettings.TablesSettings.ContainsKey(lowerTableName) ?
GeneratorSettings.TablesSettings[lowerTableName]
: GeneratorSettings.GlobalSettings;
var repoNamespace = tableSettings.CsRepositorySettings.Namespace;
var repoInterfaceName = "I" + pascalTableName + "Repo";
var repoClassName = pascalTableName + "Repo";
var repoPropertyName = pascalTableName + "Repo";
var repoProtectedFieldName = $"_{FirstCharacterToLower(repoPropertyName)}";
return($@"
protected {repoNamespace}.{repoInterfaceName} {repoProtectedFieldName};
public {repoNamespace}.{repoInterfaceName} {repoPropertyName} {{
get {{
if ({repoProtectedFieldName} == null)
{repoProtectedFieldName} = new {repoNamespace}.{repoClassName}(this);
return {repoProtectedFieldName};
}}
}}
");
})
);
string output = $@"
/// <summary>
///Interface for {_settings.ClassName}Factory
/// </summary>
public interface {_interfaceName}Factory
{{
{_settings.ClassName} Create();
}}
/// <summary>
/// Used when the DBcontext itself is not suffisent to manage its lifecycle
/// Ex in WPF app you need to dispose the DBContexts to allow connection pooling and to be thread safe
/// Very simple implementation = with only one DB connection (can be extended to support multiple DB con)
/// </summary>
public class {_settings.ClassName}Factory : {_interfaceName}Factory
{{
private readonly string _conString;
public {_settings.ClassName}Factory(string dbConnectionString)
{{
_conString = dbConnectionString;
}}
public {_settings.ClassName} Create()
{{
return new {_settings.ClassName}(_conString);
}}
}}
public class {_settings.ClassName} : {_interfaceName}
{{
protected readonly IConfiguration _config;
protected readonly IHostEnvironment _env;
protected IDbConnection _cn = null;
public IDbConnection Connection {{
get => _cn;
}}
protected IDbTransaction _trans = null;
public IDbTransaction Transaction {{
get => _trans;
}}
{repoMemberDefinitions}
/// <summary>
/// Main constructor, inject standard config : Default connection string
/// Need to be reviewed to be more generic (choose the connection string to inject)
/// </summary>
public {_settings.ClassName}(IConfiguration config, IHostEnvironment env)
{{
_config = config;
_env = env;
DefaultTypeMap.MatchNamesWithUnderscores = true;
_cn = new SqlConnection(_config.GetConnectionString(""{_settings.ConnectionStringName}""));
}}
/// <summary>
/// Main constructor, inject standard config : Default connection string
/// Pass the connection string directly (in case of usage with WPF or dekstop app, can be heavy to always inject)
/// </summary>
public {_settings.ClassName}(string connectionString)
{{
DefaultTypeMap.MatchNamesWithUnderscores = true;
_cn = new SqlConnection(connectionString);
}}
/// <summary>
/// Open a transaction
/// </summary>
public async Task<IDbTransaction> OpenTransaction()
{{
if(_trans != null)
throw new Exception(""A transaction is already open, you need to use a new {_settings.ClassName} for parallel job."");
if (_cn.State == ConnectionState.Closed)
{{
if (!(_cn is DbConnection))
throw new Exception(""Connection object does not support OpenAsync."");
await (_cn as DbConnection).OpenAsync();
}}
_trans = _cn.BeginTransaction();
return _trans;
}}
/// <summary>
/// Open a transaction with a specified isolation level
/// </summary>
public async Task<IDbTransaction> OpenTransaction(IsolationLevel level)
{{
if(_trans != null)
throw new Exception(""A transaction is already open, you need to use a new {_settings.ClassName} for parallel job."");
if (_cn.State == ConnectionState.Closed)
{{
if (!(_cn is DbConnection))
throw new Exception(""Connection object does not support OpenAsync."");
await (_cn as DbConnection).OpenAsync();
}}
_trans = _cn.BeginTransaction(level);
return _trans;
}}
/// <summary>
/// Commit the current transaction, and optionally dispose all resources related to the transaction.
/// </summary>
public void CommitTransaction(bool disposeTrans = true)
{{
if (_trans == null)
throw new Exception(""DB Transaction is not present."");
_trans.Commit();
if (disposeTrans) _trans.Dispose();
if (disposeTrans) _trans = null;
}}
/// <summary>
/// Rollback the transaction and all the operations linked to it, and optionally dispose all resources related to the transaction.
/// </summary>
public void RollbackTransaction(bool disposeTrans = true)
{{
if (_trans == null)
throw new Exception(""DB Transaction is not present."");
_trans.Rollback();
if (disposeTrans) _trans.Dispose();
if (disposeTrans) _trans = null;
}}
/// <summary>
/// Will be call at the end of the service (ex : transient service in api net core)
/// </summary>
public void Dispose()
{{
_trans?.Dispose();
_cn?.Close();
_cn?.Dispose();
}}
}}
";
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);
}
public override string Generate()
{
if (!TableSettings.GenerateInsertSP && !PreviewMode)
{
return(string.Empty);
}
var allColumns = Table.GetAllColumns();
var nonIdentityColumns = allColumns.Where(col => !col.GetProperty <bool>(Column.IsIdentity));
var identityColumns = allColumns.Where(col => col.GetProperty <bool>(Column.IsIdentity));
// Join output & input param declarations
var paramDeclarations = String.Join(Environment.NewLine + ", ",
identityColumns.Select(col =>
{
var colName = col.Name.Parts[2];
var colDataType = col.GetColumnSqlDataType();
return($"@{colName} {colDataType} OUT");
})
.Concat(
nonIdentityColumns.Select(col =>
{
var colName = col.Name.Parts[2];
var colDataType = col.GetColumnSqlDataType();
return($"@{colName} {colDataType}");
}))
);
var tmpNonIdentiyColumns = _settings.FieldNamesExcluded != null
? nonIdentityColumns.Where(c => !_settings.FieldNamesExcluded.Split(',').Contains(c.Name.Parts[2]))
: nonIdentityColumns;
var insertClause_columns = String.Join(Environment.NewLine + " , ",
tmpNonIdentiyColumns.Select(col =>
{
var colName = col.Name.Parts[2];
return($"[{colName}]");
}));
var insertClause_values = String.Join(Environment.NewLine + " , ",
tmpNonIdentiyColumns.Select(col =>
{
var colName = col.Name.Parts[2];
return($"@{colName}");
})
);
var grants = String.Join(Environment.NewLine + Environment.NewLine,
_settings.GrantExecuteToRoles.Select(roleName =>
"GRANT EXECUTE" + Environment.NewLine
+ $"ON OBJECT::[dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_insert] TO [{roleName}] AS [dbo];"
+ Environment.NewLine + "GO")
);
string output =
$@"
-- =================================================================
-- Author: {GeneratorSettings.AuthorName}
-- Description: Insert Procedure for the table {Table.Name}
-- =================================================================
CREATE PROCEDURE [dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_insert]
(
{paramDeclarations}
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@strErrorMessage NVARCHAR(4000),
@intErrorSeverity INT,
@intErrorState INT,
@intErrorLine INT;
BEGIN TRY
INSERT INTO {Table.Name}
(
{insertClause_columns}
)
VALUES
(
{insertClause_values}
)
SET @id = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
SELECT
@strErrorMessage = ERROR_MESSAGE()
+ ' Line:'
+ CONVERT(VARCHAR(5), ERROR_LINE()),
@intErrorSeverity = ERROR_SEVERITY(),
@intErrorState = ERROR_STATE();
RAISERROR(
@strErrorMessage, -- Message text.
@intErrorSeverity, -- Severity.
@intErrorState -- State.
);
END CATCH;
END
GO
{grants}
";
return(output);
}
public override string Generate()
{
if (!TableSettings.GenerateSelectAllSP && !PreviewMode)
{
return(string.Empty);
}
var grants = String.Join(Environment.NewLine + Environment.NewLine,
_settings.GrantExecuteToRoles.Select(roleName =>
"GRANT EXECUTE" + Environment.NewLine
+ $"ON OBJECT::[dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_selectAll] TO [{roleName}] AS [dbo];"
+ Environment.NewLine + "GO")
);
string output =
$@"
-- =================================================================
-- Author: {GeneratorSettings.AuthorName}
-- Description: Select All Procedure for the table {Table.Name}
-- =================================================================
CREATE PROCEDURE [dbo].[usp{TSqlModelHelper.PascalCase(Table.Name.Parts[1])}_selectAll]
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@strErrorMessage NVARCHAR(4000),
@intErrorSeverity INT,
@intErrorState INT,
@intErrorLine INT;
BEGIN TRY
SELECT * FROM {Table.Name}
END TRY
BEGIN CATCH
SELECT
@strErrorMessage = ERROR_MESSAGE()
+ ' Line:'
+ CONVERT(VARCHAR(5), ERROR_LINE()),
@intErrorSeverity = ERROR_SEVERITY(),
@intErrorState = ERROR_STATE();
RAISERROR(
@strErrorMessage, -- Message text.
@intErrorSeverity, -- Severity.
@intErrorState -- State.
);
END CATCH;
END
GO
{grants}
";
return(output);
}
