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