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; }