public override string GenerateStoredProcedureCreate(StoredProcedure storedProcedure)
{
CodeWriter writer = new CodeWriter();
writer.WriteLineFormat("CREATE PROCEDURE [{0}].[{1}]", storedProcedure.Schema.Name, storedProcedure.Name);
int paramCount = storedProcedure.Parameters.Count;
if (paramCount > 0)
{
writer.WriteLine("(");
}
writer.PushIdent();
for (int i = 0; i < paramCount; i++)
{
Parameter parameter = storedProcedure.Parameters[i];
DataType dt = TypeMap == null ? parameter.DataType : TypeMap.MapDataType(parameter.DataType);
string modeDef = parameter.Mode == "OUT" ? parameter.Mode : "";
string paramName = parameter.Name.StartsWith("@") ? parameter.Name : "@" + parameter.Name;
string pDef = string.Format("{0} {1} {2}", paramName, dt.FullName, modeDef).Trim();
string comma = i < paramCount - 1 ? "," : "";
writer.WriteLineFormat("{0}{1}", pDef, comma);
}
writer.PopIdent();
if (paramCount > 0)
{
writer.WriteLine(")");
}
writer.WriteLine("as");
writer.WriteLine("begin");
writer.PushIdent();
writer.WriteLine(storedProcedure.Definition);
writer.PopIdent();
writer.Write("end");
return writer.Code;
}
public override string GenerateStoredProcedureCreate(StoredProcedure storedProcedure)
{
CodeWriter writer = new CodeWriter();
writer.WriteLine("DELIMITER $$");
writer.WriteLineFormat("create procedure `{0}`.`{1}`(", storedProcedure.Schema.Name, storedProcedure.Name);
writer.PushIdent();
int paramCount = storedProcedure.Parameters.Count;
for (int i = 0; i < paramCount; i++)
{
Parameter parameter = storedProcedure.Parameters[i];
DataType dt = TypeMap == null ? parameter.DataType : TypeMap.MapDataType(parameter.DataType);
string modeDef = parameter.Mode;// != "IN" ? parameter.Mode : "";
string pDef = string.Format("{0} {1} {2}", modeDef, parameter.Name.Replace("@", ""), dt.FullName).Trim();
string comma = i < paramCount - 1 ? "," : "";
writer.WriteLineFormat("{0}{1}", pDef, comma);
}
writer.PopIdent();
writer.WriteLine(")");
writer.WriteLine("begin");
writer.PushIdent();
writer.WriteLine(storedProcedure.Definition);
writer.PopIdent();
writer.Write("end$$");
return writer.Code;
}
private void buildParamsNode(StoredProcedure storedProcedure, TreeNode parentNode)
{
foreach (Parameter p in storedProcedure.Parameters)
{
string s = p.Name + " [" + p.DataType.FullName + "] [" + p.Mode + "]";
TreeNode n = new TreeNode(s, 3, 3);
n.Tag = p;
parentNode.Nodes.Add(n);
}
}
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 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 GenerateStoredProcedureCreate(StoredProcedure storedProcedure);
private string GenerateMethodBody(StoredProcedure sp)
{
CodeWriter writer = new CodeWriter();
writer.WriteLine("{");
writer.PushIdent();
writer.WriteLine("List<IDbDataParameter> parameters = null;");
if (sp.Parameters.Count > 0)
{
writer.WriteLine("parameters = new List<IDbDataParameter>();");
writer.WriteLine("IDbCommand cmd = connection.CreateCommand();");
writer.WriteLine("if (this.transaction != null)");
writer.WriteLine("{");
writer.PushIdent();
writer.WriteLine("cmd.Transaction = this.transaction;");
writer.PopIdent();
writer.WriteLine("}");
}
IEnumerable<Parameter> inParameters = sp.Parameters.Where<Parameter>(p => p.Mode.ToLower() == "in" || p.Mode.ToLower() == "inout");
foreach (Parameter p in sp.Parameters)
{
string pname = p.Name.Replace("@", "");
if (hasNamingAdapter)
{
pname = Options.NamingAdapter.GetParameterName(pname);
}
writer.WriteLineFormat("IDbDataParameter p{0} = cmd.CreateParameter();", pname);
writer.WriteLineFormat("p{0}.ParameterName = \"{1}\";", pname, p.Name);
if (p.Mode.ToLower() == "in" || p.Mode.ToLower() == "inout")
{
writer.WriteLineFormat("p{0}.Value = ({0} == null)? (object)DBNull.Value : {0};", pname);
}
string mode = p.Mode.ToLower();
string pDirection = mode == "in" ? "ParameterDirection.Input" : mode == "out" ? "ParameterDirection.Output" : "ParameterDirection.InputOutput";
writer.WriteLineFormat("p{0}.Direction = {1};", pname, pDirection);
writer.WriteLineFormat("parameters.Add(p{0});", pname);
writer.WriteLine();
}
string namingAdapterName = "";
if (Options != null && Options.NamingAdapter != null)
{
namingAdapterName = this.Options.NamingAdapter.GetType().Name;
}
else
{
namingAdapterName = "SameNamingAdapter";
}
writer.WriteLineFormat("StoredProcResult result = ExecProc(\"{0}\", parameters, new {1}(), {2}, resultTypes);", sp.Name, namingAdapterName, Options.SupportsReturnValue.ToString().ToLower() );
IEnumerable<Parameter> outParameters = sp.Parameters.Where<Parameter>(p => p.Mode.ToLower() == "out" || p.Mode.ToLower() == "inout");
int index = 0;
foreach (Parameter p in outParameters)
{
string pname = p.Name.Replace("@", "");
if (hasNamingAdapter)
{
pname = Options.NamingAdapter.GetParameterName(pname);
}
string ptype = TypeMap == null ? p.DataType.Name : TypeMap.MapDataType(p.DataType).Name;
writer.WriteLineFormat("{0} = ({1})result.OutPramaters.ElementAt<IDbDataParameter>({2}).Value;", pname, ptype, index);
index++;
}
writer.WriteLine();
writer.WriteLine("return result;");
writer.PopIdent();
writer.WriteLine("}");
return writer.Code;
}
private string generateMethodHeader(StoredProcedure sp)
{
StringBuilder sb = new StringBuilder();
List<string> paramDefs = new List<string>();
string methodName = hasNamingAdapter? Options.NamingAdapter.GetMethodName(sp.Name): sp.Name;
foreach (Parameter p in sp.Parameters)
{
string mode = p.Mode.ToLower();
string prefix = mode == "inout" ? "ref" : mode == "out" ? "out" : "";
string type = TypeMap == null ? p.DataType.Name : TypeMap.MapDataType(p.DataType).Name;
string name = p.Name.Replace("@", "");
if (hasNamingAdapter)
{
name = Options.NamingAdapter.GetParameterName(name);
}
string def = string.Format("{0} {1} {2}", prefix, type, name);
paramDefs.Add(def);
}
paramDefs.Add("params Type[] resultTypes");
string paramDefString = StringUtils.GenerateCommaSeparatedString(paramDefs, "", "");
string header = string.Format("StoredProcResult {0}({1})", methodName, paramDefString.Trim());
return header;
}
