private string generateUpdateBody(Table table, List<Column> keyColumns, List<Column> updateColumns) { CodeWriter writer = new CodeWriter(); writer.WriteLineFormat("UPDATE [{0}].[{1}]", table.Schema.Name, table.Name); writer.WriteLine("SET"); writer.PushIdent(); for (int i = 0; i < updateColumns.Count; i++) { Column column = updateColumns[i]; string and = i < updateColumns.Count - 1 ? "," : ""; string cond = string.Format("[{0}] = @{0} {1}", column.Name, and).Trim(); writer.WriteLine(cond); } writer.PopIdent(); writer.WriteLine("WHERE"); writer.PushIdent(); for (int i = 0; i < keyColumns.Count; i++) { Column column = keyColumns[i]; string and = i < keyColumns.Count - 1 ? "AND" : ""; string cond = string.Format("[{0}] = @{0} {1}", column.Name, and).Trim(); writer.WriteLine(cond); } writer.PopIdent(); return writer.Code; }
private void buildColumnsNode(Table table, TreeNode parentNode) { foreach (Column col in table.Columns) { string s = col.Name + " [" + col.DataType.FullName + "]"; TreeNode node = new TreeNode(s, 3, 3); node.Tag = col; parentNode.Nodes.Add(node); } }
public override string GenerateSqlCreate(Table table) { CodeWriter writer = new CodeWriter(); writer.WriteLineFormat("create table [{0}].[{1}](", table.Schema.Name, table.Name); writer.PushIdent(); int colCount = table.Columns.Count; int conCount = table.Constraints.Count; for (int i = 0; i < colCount; i++) { Column col = table.Columns[i]; DataType dt = TypeMap == null ? col.DataType : TypeMap.MapDataType(col.DataType); string nullDef = col.IsNullable? "null": "not null"; string autoIncrementDef = col.IsAutoIncremented ? "identity(1, 1)" : ""; string colDef = string.Format("[{0}] {1} {2} {3}", col.Name, dt.FullName, nullDef, autoIncrementDef).Trim(); string comma = (i < colCount - 1 || conCount > 0) ? "," : ""; writer.WriteLineFormat("{0}{1}", colDef, comma); } for (int i = 0; i < conCount; i++) { TableConstraint con = table.Constraints[i]; string conDef = null; if (con.Type == ConstraintType.PrimaryKey) { string colDef = StringUtils.GenerateCommaSeparatedString(con.Columns, "[", "]"); conDef = string.Format("constraint [{0}] primary key({1})", con.Name, colDef); } else if (con.Type == ConstraintType.Unique) { string colDef = StringUtils.GenerateCommaSeparatedString(con.Columns, "[", "]"); conDef = string.Format("constraint [{0}] unique({1})", con.Name, colDef); } else if (con.Type == ConstraintType.Check) { CheckConstraint chk = (CheckConstraint)con; conDef = string.Format("constraint [{0}] check {1}", chk.Name, chk.Clause); } else if (con.Type == ConstraintType.ForeignKey) { ForeignKeyConstraint fk = (ForeignKeyConstraint)con; string colDef = StringUtils.GenerateCommaSeparatedString(fk.Columns, "[", "]"); string refColDef = StringUtils.GenerateCommaSeparatedString(fk.ReferencedColumns, "[", "]"); conDef = string.Format("constraint [{0}] foreign key({1}) references [{2}].[{3}]({4}) on update {5} on delete {6}", fk.Name, colDef, con.Table.Schema.Name, fk.ReferencedTable, refColDef, fk.UpdateRule, fk.DeleteRule); } string comma = i < conCount - 1 ? "," : ""; writer.WriteLineFormat("{0}{1}", conDef, comma); } writer.PopIdent(); writer.Write(")"); return writer.Code; }
public override string GenerateSqlCreate(Table table) { CodeWriter writer = new CodeWriter(); writer.WriteLine("DELIMITER $$"); writer.WriteLineFormat("create table `{0}`.`{1}`(", table.Schema.Name, table.Name); writer.PushIdent(); int colCount = table.Columns.Count; int conCount = table.Constraints.Where(c => c.Type != ConstraintType.Check).Count(); for (int i = 0; i < colCount; i++) { Column col = table.Columns[i]; DataType dt = TypeMap == null ? col.DataType : TypeMap.MapDataType(col.DataType); string nullDef = col.IsNullable? "null": "not null"; string autoIncrementDef = col.IsAutoIncremented ? "auto_increment" : ""; string colDef = string.Format("`{0}` {1} {2} {3}", col.Name, dt.FullName, nullDef, autoIncrementDef).Trim(); string comma = (i < colCount - 1 || conCount > 0) ? "," : ""; writer.WriteLineFormat("{0}{1}", colDef, comma); } for (int i = 0; i < conCount; i++) { TableConstraint con = table.Constraints[i]; string conDef = null; if (con.Type == ConstraintType.PrimaryKey) { string colDef = StringUtils.GenerateCommaSeparatedString(con.Columns, "`", "`"); conDef = string.Format("constraint `{0}` primary key({1})", con.Name, colDef); } else if (con.Type == ConstraintType.Unique) { string colDef = StringUtils.GenerateCommaSeparatedString(con.Columns, "`", "`"); conDef = string.Format("constraint `{0}` unique({1})", con.Name, colDef); } else if (con.Type == ConstraintType.ForeignKey) { ForeignKeyConstraint fk = (ForeignKeyConstraint)con; string colDef = StringUtils.GenerateCommaSeparatedString(fk.Columns, "`", "`"); string refColDef = StringUtils.GenerateCommaSeparatedString(fk.ReferencedColumns, "`", "`"); conDef = string.Format("constraint `{0}` foreign key({1}) references `{2}`.`{3}`({4}) on update {5} on delete {6}", fk.Name, colDef, con.Table.Schema.Name, fk.ReferencedTable, refColDef, fk.UpdateRule, fk.DeleteRule); } string comma = i < conCount - 1 ? "," : ""; writer.WriteLineFormat("{0}{1}", conDef, comma); } writer.PopIdent(); writer.Write(")$$"); return writer.Code; }
private string generateInsertBody(Table table, List<Column> insertableColumns) { List<string> colNames = new List<string>(); foreach (Column col in insertableColumns) { colNames.Add(col.Name); } CodeWriter writer = new CodeWriter(); writer.WriteLineFormat("INSERT INTO [{0}].[{1}]", table.Schema.Name, table.Name); writer.WriteLineFormat("({0})", StringUtils.GenerateCommaSeparatedString(colNames, "[", "]")); writer.WriteLine("VALUES"); writer.WriteLineFormat("({0});", StringUtils.GenerateCommaSeparatedString(colNames, "@", "")); writer.WriteLine("RETURN SCOPE_IDENTITY();"); return writer.Code; }
private void buildConstraintsNode(Table table, TreeNode parentNode) { foreach (TableConstraint con in table.Constraints) { string s = con.Name + " [" + con.Type + "]"; TreeNode node = new TreeNode(s, 4, 4); node.Tag = con; TreeNode conColumns = new TreeNode("Columns", 1, 1); TreeNode conDetails = new TreeNode("Details", 1, 1); node.Nodes.Add(conColumns); node.Nodes.Add(conDetails); buildConstraintColumnsNode(con, conColumns); buildConstraintDetailsNode(con, conDetails); parentNode.Nodes.Add(node); } }
public abstract string GenerateDelete(Table table);
public abstract string GenerateUpdate(Table table);
public abstract string GenerateGet(Table table);
public abstract string GenerateInsert(Table table);
public string generateDeleteBody(Table table, List<Column> keyColumns) { CodeWriter writer = new CodeWriter(); writer.WriteLineFormat("DELETE FROM [{0}].[{1}]", table.Schema.Name, table.Name); writer.WriteLine("WHERE"); writer.PushIdent(); for (int i = 0; i < keyColumns.Count; i++) { Column column = keyColumns[i]; string and = i < keyColumns.Count - 1 ? "and" : ""; string cond = string.Format("[{0}] = @{0} {1}", column.Name, and).Trim(); writer.WriteLine(cond); } writer.PopIdent(); return writer.Code; }
public override string GenerateDelete(Table table) { CodeWriter writer = new CodeWriter(); foreach (TableConstraint constraint in table.Constraints) { if (constraint.Type == ConstraintType.PrimaryKey || constraint.Type == ConstraintType.Unique) { StoredProcedure sp = new StoredProcedure(); sp.Parameters = new List<Parameter>(); sp.Schema = table.Schema; List<string> colNames = constraint.Columns; string sepStrings = StringUtils.GenerateSeparatedString(colNames, "_AND_", "", ""); sp.Name = string.Format("HXF_{0}_DELETE_BY_{1}", table.Name, sepStrings); IEnumerable<Column> keyColumns = table.Columns.Where<Column>(c => colNames.Contains(c.Name)); string body = generateDeleteBody(table, keyColumns.ToList<Column>()); foreach (Column column in keyColumns) { Parameter p = new Parameter(); p.StoredProcedure = sp; p.Name = "@" + column.Name; //p.Direction = ParameterDirection.In; p.Mode = "IN"; p.DataType = TypeMap != null ? TypeMap.MapDataType(column.DataType) : column.DataType; sp.Parameters.Add(p); sp.Definition = body; } SqlServerDatabaseGenerator gen = new SqlServerDatabaseGenerator(TypeMap); string s = gen.GenerateStoredProcedureCreate(sp).Trim(); writer.WriteLineFormat("-- {0} --", sp.Name); writer.WriteLine(GenerateDropIfExists(sp.Name)); writer.WriteLine(); writer.WriteLine(s); writer.WriteLine("GO"); writer.WriteLine(); } } return writer.Code; }
public override string GenerateUpdate(Table table) { CodeWriter writer = new CodeWriter(); foreach (TableConstraint constraint in table.Constraints) { if (constraint.Type == ConstraintType.PrimaryKey || constraint.Type == ConstraintType.Unique) { StoredProcedure sp = new StoredProcedure(); sp.Parameters = new List<Parameter>(); sp.Schema = table.Schema; List<string> colNames = constraint.Columns; string sepStrings = StringUtils.GenerateSeparatedString(colNames, "_AND_", "", ""); sp.Name = string.Format("HXF_{0}_UPDATE_BY_{1}", table.Name, sepStrings); // columns that are part of key constraint is used in where clause IEnumerable<Column> keyColumns = table.Columns.Where<Column>(c => colNames.Contains(c.Name)); // columns that can be updated IEnumerable<Column> updatableColumns = table.Columns.Where<Column>(c => !(c.IsComputed || c.IsAutoIncremented)); // columns that will be part of update statement IEnumerable<Column> updateColumns = updatableColumns.Except(keyColumns); if (updateColumns.Count() > 0) { string body = generateUpdateBody(table, keyColumns.ToList<Column>(), updateColumns.ToList<Column>()); // create parameters for columns used in the stored procedure foreach (Column column in keyColumns.Union(updatableColumns)) { Parameter p = new Parameter(); p.StoredProcedure = sp; p.Name = "@" + column.Name; //p.Direction = ParameterDirection.In; p.Mode = "IN"; p.DataType = TypeMap != null ? TypeMap.MapDataType(column.DataType) : column.DataType; sp.Parameters.Add(p); sp.Definition = body; } SqlServerDatabaseGenerator gen = new SqlServerDatabaseGenerator(TypeMap); string s = gen.GenerateStoredProcedureCreate(sp).Trim(); writer.WriteLineFormat("-- {0} --", sp.Name); writer.WriteLine(GenerateDropIfExists(sp.Name)); writer.WriteLine(); writer.WriteLine(s); writer.WriteLine("GO"); writer.WriteLine(); } } } return writer.Code; }
public override string GenerateGetAll(Table table) { CodeWriter writer = new CodeWriter(); StoredProcedure sp = new StoredProcedure(); sp.Parameters = new List<Parameter>(); sp.Schema = table.Schema; sp.Name = string.Format("HXF_{0}_GET_ALL", table.Name); string body = string.Format("SELECT * from [{0}].[{1}]", table.Schema.Name, table.Name); sp.Definition = body; string s = new SqlServerDatabaseGenerator(this.TypeMap).GenerateStoredProcedureCreate(sp).Trim(); writer.WriteLineFormat("-- {0} --", sp.Name); writer.WriteLine(GenerateDropIfExists(sp.Name)); writer.WriteLine(); writer.WriteLine(s); return writer.Code; }
public override string GenerateInsert(Table table) { CodeWriter writer = new CodeWriter(); StoredProcedure sp = new StoredProcedure(); sp.Schema = table.Schema; sp.Name = string.Format("HXF_{0}_INSERT", table.Name); IEnumerable<Column> insertableColumns = table.Columns.Where<Column>(c => !(c.IsComputed || c.IsAutoIncremented)); List<Parameter> parameters = new List<Parameter>(); int i = 0; foreach (Column col in insertableColumns) { Parameter p = new Parameter(); p.Name = "@" + col.Name; p.Position = i++; p.DataType = col.DataType.Clone(); p.Mode = "IN"; parameters.Add(p); } sp.Parameters = parameters; sp.Definition = generateInsertBody(table, insertableColumns.ToList<Column>()); string s = new SqlServerDatabaseGenerator(TypeMap).GenerateStoredProcedureCreate(sp).Trim(); writer.WriteLineFormat("-- {0} --", sp.Name); writer.WriteLine(GenerateDropIfExists(sp.Name)); writer.WriteLine(); writer.WriteLineFormat(s); return writer.Code; }
public abstract string GenerateSqlCreate(Table table);