private static void AppendUseDBExpression(string dbName, StoredProcedureConfig spConfig, StringBuilder stringBuilder)
{
if (spConfig.UseDataBase)
{
stringBuilder.AppendFormat("Use {0};\r\nGo\r\n", dbName);
}
}
public static string GenerateInsertSp(string dbName, IEnumerable <vw_SPGenenerator> allColumnsOfTable, StoredProcedureConfig spConfig)
{
//todo ye seri az code haye zir too method encapsulate shode ke bayad hazf beshe
bool hasIdentity = allColumnsOfTable.HasIdentity();
var selectTableSchema = allColumnsOfTable.GetTableSchema(spConfig.Name.TableName);
var sss = new StringBuilder(DataBaseHelper.GetCommentForSp());
AppendUseDBExpression(dbName, spConfig, sss);
sss.AppendFormat("Create Procedure {0} {1}" +
" ",
spConfig.Name, Environment.NewLine);
sss.Append(allColumnsOfTable.GetNoIdentityColumns().GetUpdateParams());
if (spConfig.EnableEncryption)
{
sss.AppendFormat(" {0}" +
"With Encryption ", Environment.NewLine);
}
if (hasIdentity == true)
{
#region if (hasIdentity == true)
sss.AppendFormat(@",{0}" +
" @InsertedId int output{0}"+
"", Environment.NewLine);
#endregion
}
sss.AppendFormat("{0}" +
"As{0}" +
"Begin {0}" +
" Set NoCount On;{0}" +
"", Environment.NewLine);
sss.Append(allColumnsOfTable.NormalizeTextColumnsQuery());
sss.AppendFormat(
"{3}" +
"{3}" +
" Insert Into [{0}].[{1}].[{2}]{3}" +
" (",
dbName, selectTableSchema, spConfig.Name.TableName, Environment.NewLine);
int i = 0;
foreach (var row in allColumnsOfTable.Where(x => x.COLUMN_NAME.ToLower() != "lastmodificationtime"))
{
if (row.IsIdentity == 1)
{
continue;
}
if (i > 0)
{
sss.Append(", ");
}
sss.Append(row.COLUMN_NAME);
i++;
}
sss.AppendFormat("){0}" +
" Values{0}"+
" (",
Environment.NewLine);
i = 0;
foreach (var row in allColumnsOfTable.Where(x => x.COLUMN_NAME.ToLower() != "lastmodificationtime"))
{
if (row.IsIdentity == 1)
{
continue;
}
if (i > 0)
{
sss.Append(@", ");
}
sss.Append("@");
sss.Append(row.COLUMN_NAME);
i++;
}
if (hasIdentity == true)
{
sss.AppendFormat(@") {0}" +
" {0}" +
" Set @InsertedId = SCOPE_IDENTITY();{0}" +
"End{0}" +
"", Environment.NewLine);
}
else
{
sss.AppendFormat(@") {0}" +
" {0}" +
"End{0}" +
"", Environment.NewLine);
}
if (spConfig.UseGo)
{
sss.AppendLine("GO");
}
return(sss.ToString());
}
public static string GenerateOldSearchWithPageSortTotalCountSp(string dbName, IEnumerable <vw_SPGenenerator> allColumnsOfTable, StoredProcedureConfig spConfig)
{
var primaryKeys = allColumnsOfTable.Where(p => p.Constraint_Type == "PK");
var selectTableSchema = allColumnsOfTable.GetTableSchema(spConfig.Name.TableName);
StringBuilder sss = new StringBuilder(GetCommentForSp());
AppendUseDBExpression(dbName, spConfig, sss);
sss.AppendFormat("Create Procedure {0}" +
"( {1}" +
" ",
spConfig.Name.ToString(), Environment.NewLine);
//int i = 0;
foreach (var PKRow in primaryKeys)
{
//if (i > 0) stringBuilder.Append(", ");
sss.AppendFormat("@{0} {1}", PKRow.COLUMN_NAME, PKRow.DATA_TYPE);
//stringBuilder.Append((PKRow.CHARACTER_MAXIMUM_LENGTH != null) ? "(" + PKRow.CHARACTER_MAXIMUM_LENGTH + ")" : "");
sss.Append((PKRow.CHARACTER_MAXIMUM_LENGTH != null && (PKRow.DATA_TYPE != "ntext") && (PKRow.DATA_TYPE != "image"))
? "(" + ((PKRow.CHARACTER_MAXIMUM_LENGTH == -1) ? "MAX" : PKRow.CHARACTER_MAXIMUM_LENGTH.ToString()) + ")"
: "");
sss.Append(", ");
//i++;
}
sss.Append(" ");
sss.AppendFormat("{0}" +
" @startRowIndex int,{0}"+
" @maximumRows int,{0}"+
" @sortBy nvarchar(30),{0}"+
" @TotalCount int OUTPUT{0}"+
" ){0}",
Environment.NewLine);
if (spConfig.EnableEncryption)
{
sss.AppendFormat("{0}" +
"With Encryption ", Environment.NewLine);
}
sss.AppendFormat(
@"{3}" +
"AS{3}" +
"BEGIN{3}" +
" Set NoCount On;{3}" +
" {3}" +
" DECLARE{3}" +
" @sqlStatement nvarchar(max), -- SQL statement to execute{3}" +
" @upperBound int{3}" +
" {3}" +
" IF @startRowIndex < 1 SET @startRowIndex = 1{3}" +
" IF @maximumRows < 1 SET @maximumRows = 1{3}" +
" {3}" +
" SET @upperBound = @startRowIndex + @maximumRows{3}" +
" {3}" +
" Select @TotalCount = Count(*) From [{0}].[{1}].[{2}]{3}" +
" {3}" +
" SET @sqlStatement = ' {3}" +
" SELECT tbl.*{3}" +
" FROM ({3}" +
" SELECT ROW_NUMBER() OVER(ORDER BY ' + @SortBy + ') AS rowNumber, *{3}" +
" FROM [{0}].[{1}].[{2}]{3}" +
" ) AS tbl{3}" +
" WHERE rowNumber >= ' + CONVERT(varchar(9), @startRowIndex) + ' AND{3}" +
" rowNumber < ' + CONVERT(varchar(9), @upperBound){3}" +
" exec (@sqlStatement){3}" +
" {3}" +
"END{3}",
dbName, selectTableSchema, spConfig.Name.TableName, Environment.NewLine);
if (spConfig.UseGo)
{
sss.AppendLine("GO");
}
return(sss.ToString());
}
public static string GenerateSearchWithPagingAndSortingTotalCountSp(string dbName, IEnumerable <vw_SPGenenerator> allColumnsOfTable, StoredProcedureConfig spConfig)
{
var SelectTABLE_SCHEMA = allColumnsOfTable.GetTableSchema(spConfig.Name.TableName);
StringBuilder sss = new StringBuilder(GetCommentForSp());
AppendUseDBExpression(dbName, spConfig, sss);
var nonPrimaryKeys = allColumnsOfTable.Where(p => p.Constraint_Type != "PK");
var textColumns = allColumnsOfTable.GetTextColumns();
var nonTextColumns = allColumnsOfTable.GetNonTextualColumns();
sss.Append("Create Procedure ");
sss.Append(spConfig.Name.ToString());
sss.AppendFormat(@"{0}" +
"({0}" +
" ", Environment.NewLine);
sss.Append(nonPrimaryKeys.Where(x => x.COLUMN_NAME.ToLower() != "lastmodificationtime" && x.COLUMN_NAME.ToLower() != "lastmodifiedby").GetUpdateParams());
sss.AppendFormat(@", {0}" +
" {0}" +
" @PageIndex int = 0,{0}"+
" @PageSize int = 20,{0}"+
" @SortField varchar(100),{0}"+
" @SortDirection varchar(5){0}"+
"){0}" +
"", Environment.NewLine);
if (spConfig.EnableEncryption)
{
sss.AppendFormat(@"{0}" +
"With Encryption ", Environment.NewLine);
}
sss.AppendFormat(@"{0}" +
"AS{0}" +
"BEGIN{0}" +
" Set NoCount On;{0}" +
" {0}" +
" Declare @UpPageNumber int;{0}" +
" Declare @DownPageNumber int;{0}" +
" Set @UpPageNumber = @PageIndex * @PageSize ;{0}" +
" Set @DownPageNumber = @UpPageNumber + @PageSize;{0}" +
" Set @UpPageNumber = @UpPageNumber + 1;{0}" +
" {0}" +
"", Environment.NewLine);
AppendNormalizeQueryForTextColumnsForSearch(allColumnsOfTable, sss);
sss.AppendFormat(@"{0}" +
" {0}" +
" Select{0}" +
" AllResults.RowNumber,{0}" +
" AllResults.DataCount", Environment.NewLine);
foreach (var col in allColumnsOfTable)
{
sss.AppendFormat(",{0}" +
" ", Environment.NewLine);
sss.AppendFormat(@"AllResults.{0}", col.COLUMN_NAME);
}
sss.AppendFormat("{0}" +
" From{0}" +
" (", Environment.NewLine);
sss.AppendFormat("{0}" +
" Select{0}" +
" DataCount = Count(*) over(),{0}" +
" ROW_NUMBER() OVER (ORDER BY ", Environment.NewLine);
var ll = 0;
foreach (var allCol in allColumnsOfTable)
{
if (ll > 0)
{
sss.Append(@",");
}
sss.AppendFormat("{4}" +
" Case When @SortField = '{3}' AND @SortDirection = 'ASC' Then [{0}].[{1}].[{2}].[{3}] End,{4}" +
" Case When @SortField = '{3}' AND @SortDirection = 'DESC' Then [{0}].[{1}].[{2}].[{3}] End DESC",
dbName, SelectTABLE_SCHEMA, spConfig.Name.TableName, allCol.COLUMN_NAME, Environment.NewLine);
ll++;
}
sss.AppendFormat("{0}" +
" ) as RowNumber", Environment.NewLine);
foreach (var col in allColumnsOfTable)
{
sss.AppendFormat(",{4}" +
" [{0}].[{1}].[{2}].[{3}]",
dbName, SelectTABLE_SCHEMA, spConfig.Name.TableName, col.COLUMN_NAME, Environment.NewLine);
}
sss.AppendFormat(@"{3}" +
" From{3}" +
" [{0}].[{1}].[{2}]{3}" +
" Where{3}" +
" ",
dbName, SelectTABLE_SCHEMA, spConfig.Name.TableName, Environment.NewLine);
int j = 0;
foreach (var col in textColumns.Where(x => x.Constraint_Type != "PK"))
{
if (j > 0)
{
sss.AppendFormat("And {0}" +
" ", Environment.NewLine);
}
sss.AppendFormat("(@{3} is null Or [{0}].[{1}].[{2}].[{3}] like @{3}) ", dbName, SelectTABLE_SCHEMA, spConfig.Name.TableName, col.COLUMN_NAME);
j++;
}
foreach (var col in nonTextColumns.Where(x => x.Constraint_Type != "PK").Where(x => x.COLUMN_NAME.ToLower() != "lastmodificationtime" && x.COLUMN_NAME.ToLower() != "lastmodifiedby"))
{
if (j > 0)
{
sss.AppendFormat("And {0}" +
" ", Environment.NewLine);
}
sss.AppendFormat("(@{3} is null Or [{0}].[{1}].[{2}].[{3}] = @{3}) ", dbName, SelectTABLE_SCHEMA, spConfig.Name.TableName, col.COLUMN_NAME);
j++;
}
sss.AppendFormat("{0}" +
" )AllResults{0}" +
" WHERE RowNumber >= @UpPageNumber AND RowNumber <= @DownPageNumber{0}"+
" ORDER BY RowNumber{0}"+
"End{0}" +
"",
Environment.NewLine);
if (spConfig.UseGo)
{
sss.AppendLine("GO");
}
return(sss.ToString());
}
public static string GenerateDeleteRowSp(string dbName, IEnumerable <vw_SPGenenerator> allColumnsOfTable, StoredProcedureConfig spConfig)
{
var conditionFields = allColumnsOfTable.Where(p => p.Constraint_Type == "PK");
if (conditionFields.Count() == 0)
{
conditionFields = allColumnsOfTable.ToList();
}
var SelectTABLE_SCHEMA = allColumnsOfTable.GetTableSchema(spConfig.Name.TableName);
StringBuilder sss = new StringBuilder(GetCommentForSp());
AppendUseDBExpression(dbName, spConfig, sss);
sss.AppendFormat("Create Procedure {0} {1}" +
" ",
spConfig.Name.ToString(), Environment.NewLine);
int i = 0;
foreach (var PKRow in conditionFields)
{
if (i > 0)
{
sss.Append(", ");
}
sss.AppendFormat("@{0} {1}", PKRow.COLUMN_NAME, PKRow.DATA_TYPE);
//stringBuilder.Append((PKRow.CHARACTER_MAXIMUM_LENGTH != null) ? "(" + PKRow.CHARACTER_MAXIMUM_LENGTH + ")" : "");
sss.Append((PKRow.CHARACTER_MAXIMUM_LENGTH != null && (PKRow.DATA_TYPE != "ntext") && (PKRow.DATA_TYPE != "image"))
? "(" + ((PKRow.CHARACTER_MAXIMUM_LENGTH == -1) ? "MAX" : PKRow.CHARACTER_MAXIMUM_LENGTH.ToString()) + ")"
: "");
i++;
}
sss.Append(" ");
if (spConfig.EnableEncryption)
{
sss.AppendFormat(@"{0}" +
"With Encryption ", Environment.NewLine);
}
sss.AppendFormat(@"{3}" +
"AS{3}" +
"BEGIN{3}" +
" Set NoCount On;{3}" +
" Begin Try{3}" +
" DELETE FROM [{0}].[{1}].[{2}]{3}"+
" WHERE ",
dbName, SelectTABLE_SCHEMA, spConfig.Name.TableName, Environment.NewLine);
i = 0;
foreach (var PKRow in conditionFields)
{
if (i > 0)
{
sss.Append(" AND ");
}
sss.AppendFormat("{0} = @{1}", PKRow.COLUMN_NAME, PKRow.COLUMN_NAME);
i++;
}
sss.AppendFormat(@" {0}" +
" End Try{0}"+
" Begin Catch{0}"+
" Return ERROR_NUMBER(){0}"+
" End Catch{0}"+
"END{0}" +
"",
Environment.NewLine);
if (spConfig.UseGo)
{
sss.AppendLine("GO");
}
return(sss.ToString());
}
public static string GenerateQueryFromTemplate(string dbName, IEnumerable <vw_SPGenenerator> allColumnsOfTable, StoredProcedureConfig spConfig, string template)
{
bool hasIdentity = allColumnsOfTable.HasIdentity();
var primaryKeys = allColumnsOfTable.GetPrimaryKeys();
var selectTableSchema = allColumnsOfTable.GetTableSchema(spConfig.Name.TableName);
//AppendUseDBExpression(dbName, spConfig, sss); //todo
template = template.Replace(TemplateKeywords.DatabaseName, dbName);
template = template.Replace(TemplateKeywords.SchemaName, selectTableSchema);
template = template.Replace(TemplateKeywords.TableName, spConfig.Name.TableName);
template = template.Replace(TemplateKeywords.UpdateParams, allColumnsOfTable.GetUpdateParams().ToString());
template = template.Replace(TemplateKeywords.UpdateColumnList, allColumnsOfTable.GetUpdateColumnListString().ToString());
template = template.Replace(TemplateKeywords.InputParams, allColumnsOfTable.GetNoIdentityColumns().GetUpdateParams().ToString());
template = template.Replace(TemplateKeywords.NormalizeNationalTexts, allColumnsOfTable.NormalizeTextColumnsQuery().ToString());
template = template.Replace(TemplateKeywords.InsertColumnList, allColumnsOfTable.GetInsertColumnList().ToString());
template = template.Replace(TemplateKeywords.InsertValueList, allColumnsOfTable.GetInsertValueList().ToString());
template = template.Replace(TemplateKeywords.WhereStatement, primaryKeys.GetWhereStatement().ToString());
return(template);
}
public static string GenerateSelectAllSp(string dbName, IEnumerable <vw_SPGenenerator> allColumnsOfTable, StoredProcedureConfig spConfig)
{
var SelectTABLE_SCHEMA = allColumnsOfTable.GetTableSchema(spConfig.Name.TableName);
StringBuilder stringBuilder = new StringBuilder(GetCommentForSp());
AppendUseDBExpression(dbName, spConfig, stringBuilder);
stringBuilder.AppendFormat("Create Procedure {0}{1}" +
" ",
spConfig.Name, Environment.NewLine);
if (spConfig.EnableEncryption)
{
stringBuilder.AppendFormat(@"{0}" +
"With Encryption ", Environment.NewLine);
}
stringBuilder.AppendFormat(@"{0}" +
"AS{0}" +
"BEGIN{0}" +
" Set NoCount On;{0}" +
" SELECT ", Environment.NewLine);
int i = 0;
foreach (var row in allColumnsOfTable)
{
if (i > 0)
{
stringBuilder.Append(", ");
}
stringBuilder.Append(row.COLUMN_NAME);
i++;
}
stringBuilder.AppendFormat(@"{3}" +
" FROM [{0}].[{1}].[{2}]",
dbName, SelectTABLE_SCHEMA, spConfig.Name.TableName, Environment.NewLine);
// stringBuilder.Append(@"
// WHERE ");
// i = 0;
// foreach (var PKRow in primaryKeys)
// {
// if (i > 0) stringBuilder.Append(" AND ");
// stringBuilder.Append(PKRow.COLUMN_NAME);
// stringBuilder.Append(" = ");
// stringBuilder.Append("@");
// stringBuilder.Append(PKRow.COLUMN_NAME);
// i++;
// }
// stringBuilder.Append(" ");
stringBuilder.AppendFormat(@"{0}" +
"END{0}", Environment.NewLine);
if (spConfig.UseGo)
{
stringBuilder.AppendLine("GO");
}
return(stringBuilder.ToString());
}
public static string GenerateSelectRowSp(string dbName, IEnumerable <vw_SPGenenerator> allColumnsOfTable, StoredProcedureConfig spConfig)
{
var primaryKeys = allColumnsOfTable.Where(p => p.Constraint_Type == "PK");
var SelectTABLE_SCHEMA = allColumnsOfTable.GetTableSchema(spConfig.Name.TableName);
if (primaryKeys.Count() == 0)
{
return("");
}
StringBuilder sss = new StringBuilder(GetCommentForSp());
AppendUseDBExpression(dbName, spConfig, sss);
sss.Append("Create Procedure ");
sss.Append(spConfig.Name.ToString());
sss.AppendFormat(" {0}" +
" ", Environment.NewLine);
int i = 0;
foreach (var PKRow in primaryKeys)
{
if (i > 0)
{
sss.Append(", ");
}
sss.AppendFormat("@{0} {1}", PKRow.COLUMN_NAME, PKRow.DATA_TYPE);
//stringBuilder.Append((PKRow.CHARACTER_MAXIMUM_LENGTH != null) ? "(" + PKRow.CHARACTER_MAXIMUM_LENGTH + ")" : "");
sss.Append((PKRow.CHARACTER_MAXIMUM_LENGTH != null && (PKRow.DATA_TYPE != "ntext") &&
(PKRow.DATA_TYPE != "image"))
? "(" + ((PKRow.CHARACTER_MAXIMUM_LENGTH == -1) ? "MAX" : PKRow.CHARACTER_MAXIMUM_LENGTH.ToString()) + ")"
: "");
i++;
}
sss.Append(" ");
if (spConfig.EnableEncryption)
{
sss.AppendFormat("{0}" +
"With Encryption {0}", Environment.NewLine);
}
sss.AppendFormat(@"{0}" +
"AS{0}" +
"BEGIN{0}" +
" Set NoCount On;{0}" +
" SELECT ",
Environment.NewLine);
int iii = 0;
foreach (var row in allColumnsOfTable)
{
if (iii > 0)
{
sss.Append(", ");
}
sss.Append(row.COLUMN_NAME);
iii++;
}
sss.AppendFormat(@"{3}" +
" FROM [{0}].[{1}].[{2}]{3}"+
" WHERE{3}"+
" ",
dbName, SelectTABLE_SCHEMA, spConfig.Name.TableName, Environment.NewLine);
i = 0;
foreach (var PKRow in primaryKeys)
{
if (i > 0)
{
sss.Append(" AND ");
}
sss.AppendFormat("{0} = @{1}", PKRow.COLUMN_NAME, PKRow.COLUMN_NAME);
i++;
}
sss.Append(" ");
sss.AppendFormat(@"{0}" +
"END{0}", Environment.NewLine);
if (spConfig.UseGo)
{
sss.AppendLine("GO");
}
return(sss.ToString());
}
public static string GenerateUpdateSp(string dbName, IEnumerable <vw_SPGenenerator> allColumnsOfTable, StoredProcedureConfig spConfig)
{
var primaryKeys = allColumnsOfTable.GetPrimaryKeys();
var selectTableSchema = allColumnsOfTable.GetTableSchema(spConfig.Name.TableName);
if (!primaryKeys.Any())
{
return("");
}
StringBuilder sss = new StringBuilder(GetCommentForSp());
AppendUseDBExpression(dbName, spConfig, sss);
sss.AppendFormat(@"Create Procedure {0}", spConfig.Name.ToString());
sss.AppendFormat(" {0}" +
" ", Environment.NewLine);
sss.Append(allColumnsOfTable.GetUpdateParams());
sss.Append(" ");
if (spConfig.EnableEncryption)
{
sss.AppendFormat("{0}" +
"With Encryption ", Environment.NewLine);
}
sss.AppendFormat("{0}" +
"AS{0}" +
"BEGIN{0}" +
" Set NoCount On;{0}" +
"", Environment.NewLine);
sss.Append(allColumnsOfTable.NormalizeTextColumnsQuery());
sss.AppendFormat("{3}" +
" {3}" +
" Update [{0}].[{1}].[{2}]{3}"+
" Set{3}"+
" ",
dbName, selectTableSchema, spConfig.Name.TableName, Environment.NewLine);
sss.Append(allColumnsOfTable.GetUpdateColumnListString());
sss.AppendFormat(" {0}" +
" Where{0}"+
" ",
Environment.NewLine);
sss.Append(primaryKeys.GetWhereStatement());
sss.AppendFormat("{0}" +
"END{0}",
Environment.NewLine);
if (spConfig.UseGo)
{
sss.AppendLine("GO");
}
return(sss.ToString());
}
