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