public StoredProcedure GenerateSp(DBTableInfo tableInfo, string nodeName, List<DBTableColumnInfo> selectedFields, List<DBTableColumnInfo> whereConditionFields)
{
BaseSPGenerator spGenerator = SPFactory.GetSpGeneratorObject(nodeName);
StoredProcedure procedure = spGenerator.GenerateSp(tableInfo, selectedFields, whereConditionFields);
SaveProcedureToFile(procedure);
return procedure;
}
protected override string GenerateStatement(DBTableInfo tableInfo, List<DBTableColumnInfo> selectedFields, List<DBTableColumnInfo> whereConditionFields)
{
List<string> values = new List<string>();
List<string> fields = new List<string>();
foreach (DBTableColumnInfo colInf in selectedFields.Where(x => !x.Exclude))
{
values.Add(PrefixInputParameter + colInf.ColumnName);
fields.Add(Wrap(colInf.ColumnName));
}
var sb = new StringBuilder();
sb.Append("\tINSERT INTO " + tableInfo.FullTableName);
if (values.Any())
{
sb.AppendLine(" (" + string.Join(", ", fields) + ")");
sb.Append("\tVALUES (" + string.Join(", ", values) + ")");
}
else
{
sb.AppendLine();
sb.Append("\tDEFAULT VALUES");
}
if (tableInfo.Columns.Any(x => x.IsIdentity))
{
sb.AppendLine();
sb.Append("\tSELECT @@IDENTITY");
}
return sb.ToString();
}
protected override string GenerateStatement(DBTableInfo tableInfo, List<DBTableColumnInfo> selectedFields, List<DBTableColumnInfo> whereConditionFields)
{
if(!selectedFields.Any()){
throw new ArgumentException("selectedFields");
}
var sb = new StringBuilder();
sb.AppendLine("\tUPDATE " + tableInfo.FullTableName + " SET");
var statements = new List<string>();
foreach (DBTableColumnInfo colInf in selectedFields.Where(x => !x.Exclude))
{
statements.Add("\t\t"+Wrap(colInf.ColumnName) + "=" + PrefixInputParameter + colInf.ColumnName);
}
sb.AppendLine(string.Join("," + Environment.NewLine, statements));
sb.Append(GenerateWhereStatement(whereConditionFields));
return sb.ToString();
}
protected override string GenerateStatement(DBTableInfo tableInfo, List<DBTableColumnInfo> selectedFields, List<DBTableColumnInfo> whereConditionFields)
{
string selectFieldsStr;
if (selectedFields.Count == tableInfo.Columns.Count)
{
selectFieldsStr = "*";
}
else
{
List<string> fields = new List<string>();
foreach (DBTableColumnInfo colInf in selectedFields.Where(x => !x.Exclude))
{
fields.Add(Wrap(colInf.ColumnName));
}
selectFieldsStr = string.Join(", ", fields);
}
return
$"\tSELECT {selectFieldsStr} FROM {tableInfo.FullTableName}";
}
private DBTableInfo GetTableInformation(string tableName, SqlConnection connection)
{
string sql = "Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableName + "'";
var dt = ExecuteDataTable(sql, connection);
var sqlTableInfo = new DBTableInfo();
List<DBTableColumnInfo> colList = new List<DBTableColumnInfo>();
foreach (DataRow dr in dt.Rows)
{
var colinfo = new DBTableColumnInfo();
colinfo.ColumnName = dr["COLUMN_NAME"].ToString();
colinfo.DataType = dr["DATA_TYPE"].ToString();
if (dr["CHARACTER_MAXIMUM_LENGTH"] != null && dr["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim() != "")
colinfo.CharacterMaximumLength = int.Parse(dr["CHARACTER_MAXIMUM_LENGTH"].ToString());
if (dr["NUMERIC_PRECISION"] != null && dr["NUMERIC_PRECISION"].ToString().Trim() != "")
colinfo.NumericPrecision = int.Parse(dr["NUMERIC_PRECISION"].ToString());
if (dr["NUMERIC_PRECISION_RADIX"] != null && dr["NUMERIC_PRECISION_RADIX"].ToString().Trim() != "")
colinfo.NumericPrecisionRadix = int.Parse(dr["NUMERIC_PRECISION_RADIX"].ToString());
if (dr["NUMERIC_SCALE"] != null && dr["NUMERIC_SCALE"].ToString().Trim() != "")
colinfo.NumericScale = int.Parse(dr["NUMERIC_SCALE"].ToString());
if (dr["TABLE_SCHEMA"] != null && dr["TABLE_SCHEMA"].ToString().Trim() != "")
colinfo.Schema = dr["TABLE_SCHEMA"].ToString();
colinfo.IsIdentity = IsDinityColumn(dr["COLUMN_NAME"].ToString(), tableName);
colinfo.IsPrimaryKey = IsPrimaryColumn(dr["COLUMN_NAME"].ToString(), tableName);
colinfo.Exclude = IsExcludeColumn(colinfo);
colList.Add(colinfo);
}
sqlTableInfo.TableName = tableName;
sqlTableInfo.Columns = colList;
return sqlTableInfo;
}
public StoredProcedure GenerateSp(DBTableInfo tableInfo, List<DBTableColumnInfo> selectedFields,
List<DBTableColumnInfo> whereConditionFields)
{
var name = GetSpName(tableInfo.TableName);
var statementBuilder = new StringBuilder();
statementBuilder.AppendLine("CREATE PROCEDURE " + string.Format("[{0}].[{1}]", tableInfo.Schema, name));
//GenerateErrorNumberOutParameter(statementBuilder);
var inputs = GenerateInputParameters(selectedFields);
var wheres = GenerateWhereParameters(whereConditionFields);
if (!string.IsNullOrWhiteSpace(inputs))
{
statementBuilder.Append(inputs);
if (!string.IsNullOrWhiteSpace(wheres))
{
statementBuilder.Append(',');
}
statementBuilder.AppendLine();
}
if (!string.IsNullOrWhiteSpace(wheres))
{
statementBuilder.AppendLine(wheres);
}
statementBuilder.AppendLine("AS");
//GenerateStartTryBlock(statementBuilder);
statementBuilder.AppendLine(GenerateStatement(tableInfo, selectedFields, whereConditionFields));
//GenerateEndTryBlock(statementBuilder);
//GenerateCatchBlock(statementBuilder);
var res = new StoredProcedure
{
Name = name,
DropScript = GenerateDropScript(name),
Script = statementBuilder.ToString()
};
return res;
}
private TreeViewNode CreateTableNode(DBTableInfo sqlTableInfo, TreeViewNode parent)
{
TreeViewNode tblNode = new TreeViewNode(sqlTableInfo.TableName, parent);
tblNode.Tag = sqlTableInfo;
TreeViewNode insertSp = new TreeViewNode(Constants.insertTreeNodeText, tblNode);
TreeViewNode updateSp = new TreeViewNode(Constants.updateTreeNodeText, tblNode);
TreeViewNode whereCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, updateSp);
AddColumnNodes(insertSp, sqlTableInfo.Columns, true);
AddColumnNodes(updateSp, sqlTableInfo.Columns, true);
AddColumnNodes(whereCondition, sqlTableInfo.Columns, false);
updateSp.Children.Add(whereCondition);
tblNode.Children.Add(insertSp);
tblNode.Children.Add(updateSp);
return tblNode;
}
protected override string GenerateStatement(DBTableInfo tableInfo, List<DBTableColumnInfo> selectedFields, List<DBTableColumnInfo> whereConditionFields)
{
return "\tDELETE FROM " + tableInfo.FullTableName + Environment.NewLine
+ GenerateWhereStatement(whereConditionFields);
}
protected abstract string GenerateStatement(DBTableInfo tableInfo, List<DBTableColumnInfo> selectedFields, List<DBTableColumnInfo> whereConditionFields);
private TreeViewNode CreateTableNode(DBTableInfo sqlTableInfo, TreeViewNode parent)
{
string tableDisplayName = sqlTableInfo.Schema == "dbo"
? sqlTableInfo.TableName
: sqlTableInfo.Schema + "." + sqlTableInfo.TableName;
TreeViewNode tblNode = new TreeViewNode(tableDisplayName, parent);
tblNode.Tag = sqlTableInfo;
TreeViewNode insertSp = new TreeViewNode(Constants.insertTreeNodeText, tblNode);
AddColumnNodes(insertSp, sqlTableInfo.Columns.Where(x => !x.Exclude), true);
tblNode.Children.Add(insertSp);
TreeViewNode deleteSp = new TreeViewNode(Constants.deleteTreeNodeText, tblNode);
TreeViewNode whereDelCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, deleteSp);
deleteSp.Children.Add(whereDelCondition);
tblNode.Children.Add(deleteSp);
AddColumnNodes(whereDelCondition, sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToList(), true);
var updateCols = sqlTableInfo.Columns.Where(x => !x.Exclude).ToArray();
var updateWhereCols = sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToArray();
if (updateCols.Any() && updateCols.All(x => !updateWhereCols.Contains(x)))
{
TreeViewNode updateSp = new TreeViewNode(Constants.updateTreeNodeText, tblNode);
AddColumnNodes(updateSp, updateCols, true);
tblNode.Children.Add(updateSp);
TreeViewNode whereUpdateCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, updateSp);
updateSp.Children.Add(whereUpdateCondition);
AddColumnNodes(whereUpdateCondition, updateWhereCols, true);
}
AddColumnNodes(deleteSp, new List<DBTableColumnInfo>(), true);
var selectColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
TreeViewNode selectSp = new TreeViewNode(Constants.selectTreeNodeText, tblNode);
sqlTableInfo.Columns.CopyTo(selectColumns, 0);
Array.ForEach(selectColumns, x =>
{
x.Exclude = false;
});
AddColumnNodes(selectSp, selectColumns, true);
tblNode.Children.Add(selectSp);
var selectOneColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
var selectOneWhereCols = sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToArray();
sqlTableInfo.Columns.CopyTo(selectOneColumns, 0);
Array.ForEach(selectColumns, x =>
{
x.Exclude = false;
});
if (selectOneColumns.Any() &&
selectOneWhereCols.All(x => selectOneColumns.Any(y => x.ColumnName != y.ColumnName)))
{
TreeViewNode selectOneSp = new TreeViewNode(Constants.selectOneTreeNodeText, tblNode);
AddColumnNodes(selectOneSp, selectOneColumns, true);
tblNode.Children.Add(selectOneSp);
TreeViewNode whereSelectOneCondition = new TreeViewNode(Constants.whereConditionTreeNodeText,
selectOneSp);
selectOneSp.Children.Add(whereSelectOneCondition);
AddColumnNodes(whereSelectOneCondition, selectOneWhereCols, true);
}
return tblNode;
}
