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