public async Task <IActionResult> ExecuteStoredProcedure(ProcedureParameters procedureParameters)
{
var result = await _dapperRepo.ExecuteStoredProcedure(
procedureParameters.ProcedureName,
procedureParameters.ParamsDictionary);
return(Ok(result));
}
protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
if (ProcedureType == ProcedureProcedureType.LEGACY)
{
throw new NotSupportedException("Legacy type stored procedures are not supported.");
}
var command = new PSqlCommand();
command.Append($"CREATE OR ALTER PROCEDURE {ProcedureName.AsSqlIndentifier()}");
if (ProcedureInputs > 0)
{
var inputs = ProcedureParameters
.Where(o => o.ParameterType == ProcedureParameterType.IN)
.OrderBy(o => o.ParameterNumber);
command.Append(" (");
command.AppendLine();
command.Append(CreateParametersDefinitions(inputs, sourceMetadata, targetMetadata, context));
command.Append(")");
}
command.AppendLine();
if (ProcedureOutputs > 0)
{
var outputs = ProcedureParameters
.Where(o => o.ParameterType == ProcedureParameterType.OUT)
.OrderBy(o => o.ParameterNumber);
command.Append("RETURNS (");
command.AppendLine();
command.Append(CreateParametersDefinitions(outputs, sourceMetadata, targetMetadata, context));
command.Append(")");
command.AppendLine();
}
command.Append("AS");
command.AppendLine();
if (context.EmptyBodiesEnabled)
{
command.Append("BEGIN");
command.AppendLine();
if (ProcedureType == ProcedureProcedureType.SELECTABLE)
{
command.Append($" SUSPEND{SqlHelper.Terminator}");
command.AppendLine();
}
command.Append("END");
}
else
{
command.Append(ProcedureSource);
}
yield return(command);
}
protected static void SetParameters(SqlCommand command, ProcedureParameters parms)
{
foreach (SearchCriteria parm in parms.EnumerateSubCriteria())
{
String sqlParmName = "@" + parm.GetKey();
if (parm is ProcedureParameter<DateTime?>)
{
ProcedureParameter<DateTime?> parm2 = (ProcedureParameter<DateTime?>)parm;
if (!parm2.Output)
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.DateTime);
sqlParm.IsNullable = true;
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter<DateTime>)
{
ProcedureParameter<DateTime> parm2 = (ProcedureParameter<DateTime>)parm;
if (!parm2.Output)
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.DateTime);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter<int>)
{
ProcedureParameter<int> parm2 = (ProcedureParameter<int>)parm;
if (!parm2.Output)
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.Int);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter<ServerEntityKey>)
{
sqlParmName = sqlParmName.Replace("Key", "GUID");
ProcedureParameter<ServerEntityKey> parm2 = (ProcedureParameter<ServerEntityKey>)parm;
if (!parm2.Output)
{
if (parm2.Value!=null)
command.Parameters.AddWithValue(sqlParmName, parm2.Value.Key);
else
{
command.Parameters.AddWithValue(sqlParmName, DBNull.Value);
}
}
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.UniqueIdentifier);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter<bool>)
{
ProcedureParameter<bool> parm2 = (ProcedureParameter<bool>)parm;
if (!parm2.Output)
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.Bit);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter<string>)
{
ProcedureParameter<string> parm2 = (ProcedureParameter<string>)parm;
if (!parm2.Output)
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.NVarChar, 1024);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter<ServerEnum>)
{
ProcedureParameter<ServerEnum> parm2 = (ProcedureParameter<ServerEnum>)parm;
if (parm2.Value == null)
command.Parameters.AddWithValue(sqlParmName, null);
else
{
if (parm2.Output)
throw new PersistenceException("Unsupported output parameter type: ServerEnum",null);
command.Parameters.AddWithValue(sqlParmName, parm2.Value.Enum);
}
}
else if (parm is ProcedureParameter<Decimal>)
{
ProcedureParameter<Decimal> parm2 = (ProcedureParameter<Decimal>)parm;
if (!parm2.Output)
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.Decimal);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter<XmlDocument>)
{
ProcedureParameter<XmlDocument> parm2 = (ProcedureParameter<XmlDocument>)parm;
if (parm2.Value == null)
command.Parameters.AddWithValue(sqlParmName, null);
else
{
if (parm2.Output)
throw new PersistenceException("Unsupported output parameter type: XmlDocument", null);
if (parm2.Value.DocumentElement!=null)
{
XmlNodeReader reader = new XmlNodeReader(parm2.Value.DocumentElement);
SqlXml xml = new SqlXml(reader);
command.Parameters.AddWithValue(sqlParmName, xml);
}
else
{
SqlXml xml = new SqlXml();
command.Parameters.AddWithValue(sqlParmName, xml);
}
}
}
else
throw new PersistenceException("Unknown procedure parameter type: " + parm.GetType(), null);
}
}
protected static void GetOutputParameters(SqlCommand command, ProcedureParameters parms)
{
foreach (SearchCriteria parm in parms.EnumerateSubCriteria())
{
String sqlParmName = "@" + parm.GetKey();
if (parm is ProcedureParameter<DateTime?>)
{
ProcedureParameter<DateTime?> parm2 = (ProcedureParameter<DateTime?>)parm;
if (!parm2.Output)
continue;
SqlParameter sqlParm = command.Parameters[sqlParmName];
parm2.Value = (DateTime?)sqlParm.Value;
}
else if (parm is ProcedureParameter<DateTime>)
{
ProcedureParameter<DateTime> parm2 = (ProcedureParameter<DateTime>)parm;
if (!parm2.Output)
continue;
SqlParameter sqlParm = command.Parameters[sqlParmName];
parm2.Value = (DateTime)sqlParm.Value;
}
else if (parm is ProcedureParameter<int>)
{
ProcedureParameter<int> parm2 = (ProcedureParameter<int>)parm;
if (!parm2.Output)
continue;
SqlParameter sqlParm = command.Parameters[sqlParmName];
//object o = command.Connection.Get
if (sqlParm.Value != null)
parm2.Value = (int)sqlParm.Value;
}
else if (parm is ProcedureParameter<ServerEntityKey>)
{
sqlParmName = sqlParmName.Replace("Key", "GUID");
ProcedureParameter<ServerEntityKey> parm2 = (ProcedureParameter<ServerEntityKey>)parm;
if (!parm2.Output)
continue;
SqlParameter sqlParm = command.Parameters[sqlParmName];
parm2.Value = new ServerEntityKey("", sqlParm.Value);
}
else if (parm is ProcedureParameter<bool>)
{
ProcedureParameter<bool> parm2 = (ProcedureParameter<bool>)parm;
if (!parm2.Output)
continue;
SqlParameter sqlParm = command.Parameters[sqlParmName];
parm2.Value = (bool)sqlParm.Value;
}
else if (parm is ProcedureParameter<Decimal>)
{
ProcedureParameter<Decimal> parm2 = (ProcedureParameter<Decimal>)parm;
if (!parm2.Output)
continue;
SqlParameter sqlParm = command.Parameters[sqlParmName];
parm2.Value = (Decimal)sqlParm.Value;
}
else if (parm is ProcedureParameter<string>)
{
ProcedureParameter<string> parm2 = (ProcedureParameter<string>)parm;
if (!parm2.Output)
continue;
SqlParameter sqlParm = command.Parameters[sqlParmName];
if (sqlParm.Value != DBNull.Value)
parm2.Value = (string)sqlParm.Value;
}
}
}
protected static void SetParameters(SqlCommand command, ProcedureParameters parms)
{
foreach (SearchCriteria parm in parms.EnumerateSubCriteria())
{
String sqlParmName = "@" + parm.GetKey();
if (parm is ProcedureParameter <DateTime?> )
{
ProcedureParameter <DateTime?> parm2 = (ProcedureParameter <DateTime?>)parm;
if (!parm2.Output)
{
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
}
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.DateTime);
sqlParm.IsNullable = true;
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter <DateTime> )
{
ProcedureParameter <DateTime> parm2 = (ProcedureParameter <DateTime>)parm;
if (!parm2.Output)
{
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
}
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.DateTime);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter <int> )
{
ProcedureParameter <int> parm2 = (ProcedureParameter <int>)parm;
if (!parm2.Output)
{
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
}
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.Int);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter <ServerEntityKey> )
{
sqlParmName = sqlParmName.Replace("Key", "GUID");
ProcedureParameter <ServerEntityKey> parm2 = (ProcedureParameter <ServerEntityKey>)parm;
if (!parm2.Output)
{
if (parm2.Value != null)
{
command.Parameters.AddWithValue(sqlParmName, parm2.Value.Key);
}
else
{
command.Parameters.AddWithValue(sqlParmName, DBNull.Value);
}
}
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.UniqueIdentifier);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter <bool> )
{
ProcedureParameter <bool> parm2 = (ProcedureParameter <bool>)parm;
if (!parm2.Output)
{
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
}
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.Bit);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter <string> )
{
ProcedureParameter <string> parm2 = (ProcedureParameter <string>)parm;
if (!parm2.Output)
{
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
}
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.NVarChar, 1024);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter <ServerEnum> )
{
ProcedureParameter <ServerEnum> parm2 = (ProcedureParameter <ServerEnum>)parm;
if (parm2.Value == null)
{
command.Parameters.AddWithValue(sqlParmName, null);
}
else
{
if (parm2.Output)
{
throw new PersistenceException("Unsupported output parameter type: ServerEnum", null);
}
command.Parameters.AddWithValue(sqlParmName, parm2.Value.Enum);
}
}
else if (parm is ProcedureParameter <Decimal> )
{
ProcedureParameter <Decimal> parm2 = (ProcedureParameter <Decimal>)parm;
if (!parm2.Output)
{
command.Parameters.AddWithValue(sqlParmName, parm2.Value);
}
else
{
SqlParameter sqlParm = command.Parameters.Add(sqlParmName, SqlDbType.Decimal);
sqlParm.Direction = ParameterDirection.Output;
}
}
else if (parm is ProcedureParameter <XmlDocument> )
{
ProcedureParameter <XmlDocument> parm2 = (ProcedureParameter <XmlDocument>)parm;
if (parm2.Value == null)
{
command.Parameters.AddWithValue(sqlParmName, null);
}
else
{
if (parm2.Output)
{
throw new PersistenceException("Unsupported output parameter type: XmlDocument", null);
}
if (parm2.Value.DocumentElement != null)
{
XmlNodeReader reader = new XmlNodeReader(parm2.Value.DocumentElement);
SqlXml xml = new SqlXml(reader);
command.Parameters.AddWithValue(sqlParmName, xml);
}
else
{
SqlXml xml = new SqlXml();
command.Parameters.AddWithValue(sqlParmName, xml);
}
}
}
else
{
throw new PersistenceException("Unknown procedure parameter type: " + parm.GetType(), null);
}
}
}
protected static void GetOutputParameters(SqlCommand command, ProcedureParameters parms)
{
foreach (SearchCriteria parm in parms.EnumerateSubCriteria())
{
String sqlParmName = "@" + parm.GetKey();
if (parm is ProcedureParameter <DateTime?> )
{
ProcedureParameter <DateTime?> parm2 = (ProcedureParameter <DateTime?>)parm;
if (!parm2.Output)
{
continue;
}
SqlParameter sqlParm = command.Parameters[sqlParmName];
parm2.Value = (DateTime?)sqlParm.Value;
}
else if (parm is ProcedureParameter <DateTime> )
{
ProcedureParameter <DateTime> parm2 = (ProcedureParameter <DateTime>)parm;
if (!parm2.Output)
{
continue;
}
SqlParameter sqlParm = command.Parameters[sqlParmName];
parm2.Value = (DateTime)sqlParm.Value;
}
else if (parm is ProcedureParameter <int> )
{
ProcedureParameter <int> parm2 = (ProcedureParameter <int>)parm;
if (!parm2.Output)
{
continue;
}
SqlParameter sqlParm = command.Parameters[sqlParmName];
//object o = command.Connection.Get
if (sqlParm.Value != null)
{
parm2.Value = (int)sqlParm.Value;
}
}
else if (parm is ProcedureParameter <ServerEntityKey> )
{
sqlParmName = sqlParmName.Replace("Key", "GUID");
ProcedureParameter <ServerEntityKey> parm2 = (ProcedureParameter <ServerEntityKey>)parm;
if (!parm2.Output)
{
continue;
}
SqlParameter sqlParm = command.Parameters[sqlParmName];
parm2.Value = new ServerEntityKey("", sqlParm.Value);
}
else if (parm is ProcedureParameter <bool> )
{
ProcedureParameter <bool> parm2 = (ProcedureParameter <bool>)parm;
if (!parm2.Output)
{
continue;
}
SqlParameter sqlParm = command.Parameters[sqlParmName];
parm2.Value = (bool)sqlParm.Value;
}
else if (parm is ProcedureParameter <Decimal> )
{
ProcedureParameter <Decimal> parm2 = (ProcedureParameter <Decimal>)parm;
if (!parm2.Output)
{
continue;
}
SqlParameter sqlParm = command.Parameters[sqlParmName];
parm2.Value = (Decimal)sqlParm.Value;
}
else if (parm is ProcedureParameter <string> )
{
ProcedureParameter <string> parm2 = (ProcedureParameter <string>)parm;
if (!parm2.Output)
{
continue;
}
SqlParameter sqlParm = command.Parameters[sqlParmName];
if (sqlParm.Value != DBNull.Value)
{
parm2.Value = (string)sqlParm.Value;
}
}
}
}
/// <summary>
/// Generates this instance.
/// </summary>
/// <returns></returns>
/// <exception cref="System.MissingMemberException">PropertyNotFound: " + ci.ColumnName</exception>
/// <exception cref="System.NotImplementedException"></exception>
public string Generate()
{
Padding = PaddingForMethodDeclaration;
if (Model.Comment != null)
{
WriteLine("/// <summary>");
WriteLine("///" + PaddingForComment + Model.Comment);
WriteLine("/// </summary>");
}
WriteLine("public GenericResponse<{1}> {0}({2})", Model.DotNetMethodName, GenericResponseMethodReturnType, GetMethodParametersPart());
WriteLine("{");
Padding++;
if (Model.ExecutionType == ExecutionType.ExecuteReader)
{
WriteLine("var returnObject = CreateResponse<{0}>();", GenericResponseMethodReturnType);
WriteLine();
}
if (Model.ExecutionType == ExecutionType.ExecuteReaderForOneColumn)
{
WriteLine("var returnObject = CreateResponse<{0}>();", GenericResponseMethodReturnType);
WriteLine();
}
WriteLine("var command = DBLayer.GetDBCommand(Databases.{0}, \"{1}\");", Model.DatabaseEnumName, Model.ProcedureFullName);
WriteLine();
if (ProcedureParameters.Any())
{
WriteLine("// Parameters");
foreach (var procedureParameter in ProcedureParameters)
{
var sqlDatabaseTypeName = SqlDataType.GetSqlDbType(procedureParameter.SqlDataType);
var parameterValue = procedureParameter.Name.AsMethodParameter();
if (ParameterIsContract)
{
parameterValue = "contract." + procedureParameter.Name;
if (procedureParameter.SqlDataTypeIsVarChar() && Model.ReturnValueType.PropertyTypeIsGuid(procedureParameter.Name))
{
parameterValue = $"contract.{procedureParameter.Name} == Guid.Empty ? null : contract.{procedureParameter.Name}.ToString()";
}
}
if (procedureParameter.Name == Names.UpdateUserName)
{
parameterValue = Names.UpdateUserNameValue;
}
if (procedureParameter.Name == Names.UpdateHostName)
{
parameterValue = Names.UpdateHostNameValue;
}
if (procedureParameter.Name == Names.UpdateHostIP)
{
parameterValue = Names.UpdateHostIPValue;
}
if (procedureParameter.Name == Names.UpdateSystemDate)
{
parameterValue = Names.UpdateSystemDateValue;
}
if (procedureParameter.Name == Names.UserName)
{
parameterValue = Names.UserNameValue;
}
if (procedureParameter.Name == Names.HostName)
{
parameterValue = Names.HostNameValue;
}
if (procedureParameter.Name == Names.HostIP)
{
parameterValue = Names.HostIPValue;
}
if (procedureParameter.Name == Names.SystemDate)
{
parameterValue = Names.SystemDateValue;
}
if (procedureParameter.Name == Names.ChannelId)
{
parameterValue = Names.ChannelIdValue;
}
WriteLine("DBLayer.AddInParameter(command, \"{0}\", SqlDbType.{1}, {2});",
procedureParameter.Name, sqlDatabaseTypeName, parameterValue);
}
WriteLine();
}
if (Model.ExecutionType == ExecutionType.ExecuteScalar)
{
WriteLine("return DBLayer.ExecuteScalar<{0}>(command);", GenericResponseMethodReturnType);
}
else if (Model.ExecutionType == ExecutionType.ExecuteNonQuery)
{
WriteLine("return DBLayer.ExecuteNonQuery(command);");
//WriteLine("#region returnObject.Value = DBLayer.ExecuteNonQuery(command);");
//WriteLine("var sp = DBLayer.ExecuteNonQuery(command);");
//WriteLine("if (!sp.Success)");
//WriteLine("{");
//Padding++;
//WriteLine("return returnObject.Add(sp);");
//Padding--;
//WriteLine("}");
//WriteLine();
//WriteLine("returnObject.Value = sp.Value;");
//WriteLine("#endregion");
//WriteLine();
//WriteLine("return returnObject;");
}
else if (Model.ExecutionType == ExecutionType.ExecuteReader)
{
WriteLine("var sp = DBLayer.ExecuteReader(command);");
WriteLine("if (!sp.Success)");
WriteLine("{");
Padding++;
WriteLine("return returnObject.Add(sp);");
Padding--;
WriteLine("}");
WriteLine();
WriteLine("var reader = sp.Value;");
WriteLine();
if (Model.ReturnOnlyOneRecord)
{
WriteLine("{0} dataContract = null;", GenericResponseMethodReturnType);
}
else
{
WriteLine("var listOfDataContract = new {0}();", GenericResponseMethodReturnType);
}
WriteLine("while (reader.Read())");
WriteLine("{");
Padding++;
if (!Model.ReturnOnlyOneRecord)
{
Write("var ");
}
WriteLine("dataContract = new {0}();", Model.GenericResponseMethodReturnType);
var columns = ProcedureInfoReturnColumns;
foreach (var ci in columns)
{
var readerMethod = SqlReaderMethods.GetStringValue.ToString();
var prop = Model.ReturnValueType.Properties.FirstOrDefault(p => p.Name == ci.ColumnName);
if (prop != null)
{
readerMethod = SqlDataType.GetSqlReaderMethod(prop.PropertyType).ToString();
}
if (readerMethod == SqlReaderMethods.GetGUIDValue.ToString() && Model.ReturnValueType.PropertyTypeIsGuid(ci.ColumnName))
{
WriteLine("dataContract." + ci.ColumnName + " = string.IsNullOrWhiteSpace(reader[" + '"' + ci.ColumnName + '"' + "] +" + '"' + '"' + ") ? Guid.Empty : Guid.Parse(reader[" + '"' + ci.ColumnName + '"' + "].ToString());");
continue;
}
WriteLine(@"dataContract.{0} = SQLDBHelper.{1}(reader[{2}]);", ci.ColumnName, readerMethod, '"' + ci.ColumnName + '"');
}
if (Model.ReturnOnlyOneRecord)
{
WriteLine("break;");
}
else
{
WriteLine("listOfDataContract.Add(dataContract);");
}
Padding--;
WriteLine("}");
WriteLine("reader.Close();");
WriteLine();
if (Model.ReturnOnlyOneRecord)
{
WriteLine("returnObject.Value = dataContract;");
}
else
{
WriteLine("returnObject.Value = listOfDataContract;");
}
WriteLine();
WriteLine("return returnObject;");
}
else if (Model.ExecutionType == ExecutionType.ExecuteReaderForOneColumn)
{
WriteLine("var sp = DBLayer.ExecuteReader(command);");
WriteLine("if (!sp.Success)");
WriteLine("{");
Padding++;
WriteLine("return returnObject.Add(sp);");
Padding--;
WriteLine("}");
WriteLine();
WriteLine("var list = new {0}();", GenericResponseMethodReturnType.RemoveFromStart("I"));
WriteLine();
WriteLine("var reader = sp.Value;");
WriteLine("while (reader.Read())");
WriteLine("{");
Padding++;
var ci = ProcedureInfoReturnColumns.First();
var readerMethod = SqlDataType.GetSqlReaderMethod(ci.DataType).ToString();
WriteLine(@"list.Add(SQLDBHelper.{0}(reader[{1}]));", readerMethod, '"' + ci.ColumnName + '"');
Padding--;
WriteLine("}");
WriteLine("reader.Close();");
WriteLine();
WriteLine("returnObject.Value = list;");
WriteLine();
WriteLine("return returnObject;");
}
else
{
throw new NotImplementedException(Model.ExecutionType.ToString());
}
Padding--;
WriteLine("}");
return(GeneratedString);
}
private void Save()
{
EditingInfo.ConnectionType = connectionViewModel.ConnectionType;
EditingInfo.ConnectionString = connectionViewModel.ConnectionString;
EditingInfo.StoredProcedure = StoredProcedure;
if (ShouldUpdateParameters)
{
EditingInfo.ProcedureParameters = new DatabaseModel.ProcedureParameters(ProcedureParameters.Select(p => p.ProcedureParameter));
}
if (ShouldUpdateResultSets)
{
EditingInfo.ResultSets = ResultSets;
}
DialogResult = true;
}
protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
if (ProcedureType == ProcedureProcedureType.Legacy)
{
throw new NotSupportedException("Legacy type stored procedures are not supported.");
}
var command = SqlHelper.IsValidExternalEngine(this) ? new Command() : new PSqlCommand();
command.Append($"CREATE OR ALTER PROCEDURE {ProcedureName.AsSqlIndentifier()}");
if (ProcedureInputs > 0)
{
var inputs = ProcedureParameters
.Where(o => o.ParameterType == ProcedureParameterType.In)
.OrderBy(o => o.ParameterNumber);
command.Append(" (");
command.AppendLine();
command.Append(CreateParametersDefinitions(inputs, sourceMetadata, targetMetadata, context));
command.Append(")");
}
command.AppendLine();
if (ProcedureOutputs > 0)
{
var outputs = ProcedureParameters
.Where(o => o.ParameterType == ProcedureParameterType.Out)
.OrderBy(o => o.ParameterNumber);
command.Append("RETURNS (");
command.AppendLine();
command.Append(CreateParametersDefinitions(outputs, sourceMetadata, targetMetadata, context));
command.Append(")");
command.AppendLine();
}
if (SqlHelper.IsValidExternalEngine(this))
{
if (context.EmptyBodiesEnabled)
{
yield break;
}
command.Append($"EXTERNAL NAME '{SqlHelper.DoubleSingleQuotes(EntryPoint)}'");
command.AppendLine();
command.Append($"ENGINE {EngineName.AsSqlIndentifier()}");
}
else
{
command.Append("AS");
command.AppendLine();
if (context.EmptyBodiesEnabled)
{
command.Append("BEGIN");
command.AppendLine();
if (ProcedureType == ProcedureProcedureType.Selectable)
{
command.Append($" SUSPEND{SqlHelper.Terminator}");
command.AppendLine();
}
command.Append("END");
}
else
{
command.Append(ProcedureSource);
}
}
yield return(command);
}
public override void ExplicitVisit(ProcedureParameter node)
{
ProcedureParameters.Add(node);
}
public string Generate()
{
WriteLine("USE {0}", NameOfSqlProceduresWillBeRunCatalogName);
WriteLine("GO");
WriteLine("");
WriteLine("IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[{1}]') AND type in (N'P', N'PC'))",
DatabaseTargetSchemaForProcedureNames, NameOfSqlProcedureUpdate);
Padding++;
WriteLine("DROP PROCEDURE {0}", DatabaseTargetSchemaForProcedureNames + "." + NameOfSqlProcedureUpdate);
Padding--;
WriteLine("GO");
WriteLine();
WriteSqlComment(new List <string>
{
Comment
});
WriteLine("CREATE PROCEDURE " + DatabaseTargetSchemaForProcedureNames + "." + NameOfSqlProcedureUpdate);
WriteLine("(");
Padding++;
var lastColumn = ProcedureParameters.Last();
foreach (var c in ProcedureParameters)
{
var format = "@{0} {1}";
if (c.IsNullable)
{
format += " = NULL";
}
if (c != lastColumn)
{
format += ",";
}
var dataType = c.DataType;
if (dataType == SqlDataType.VarBinary)
{
dataType = dataType + "(MAX)";
}
WriteLine(format, c.ColumnName, dataType);
}
Padding--;
WriteLine(")");
WriteLine("AS");
if (CanGenerateSetNoCountOn)
{
WriteLine("SET NOCOUNT ON");
}
WriteLine("BEGIN");
WriteLine("");
Padding++;
WriteLine("UPDATE T SET");
Padding++;
lastColumn = GetUpdateColumns().Last();
foreach (var c in GetUpdateColumns())
{
var format = "T.{0} = @{0}";
if (c != lastColumn)
{
format += ",";
}
WriteLine(format, c.ColumnName.NormalizeColumnNameForReversedKeywords());
}
Padding--;
WriteLine();
Padding++;
var whereParameters = string.Join(" AND ", from c in WhereColumns select "T." + c.ColumnName + " = @" + c.ColumnName);
WriteLine("FROM {0} AS T WHERE {1}", Context.Config.TablePathForSqlScript, whereParameters);
Padding--;
WriteLine("");
Padding--;
WriteLine("END");
return(GeneratedString);
}