public LockStudyParameters()
: base("LockStudy")
{
// This is output from the stored procedure
SubCriteria["Successful"] = new ProcedureParameter<bool>("Successful");
SubCriteria["FailureReason"] = new ProcedureParameter<string>("FailureReason");
}
private string GetFormattedParameterLine(ProcedureParameter parameter)
{
return string.Format(
"{0} {1}{2}",
parameter.Name,
GetFormattedDataType(parameter),
GetFormattedDirection(parameter));
}
public override bool Equals(ProcedureParameter other)
{
if (!base.Equals(other)) return false;
var otherDecimal = (DecimalProcedureParameter)other;
if (Precision != otherDecimal.Precision) return false;
if (Scale != otherDecimal.Scale) return false;
return true;
}
public override bool Equals(ProcedureParameter other)
{
if (!base.Equals(other)) return false;
var otherSize = (VariableSizeProcedureParameter)other;
if (IsMaximumSize && otherSize.IsMaximumSize) return true;
if (Size != otherSize.Size) return false;
return true;
}
public ParameterSignature (string fullProcedureName, ProcedureParameter parameter)
{
Name = parameter.Name;
Type = parameter.Type;
// Check the parameter type is valid
if (!TypeUtils.IsAValidType (Type))
throw new ServiceException (Type + " is not a valid Procedure parameter type, in " + fullProcedureName);
HasDefaultValue = parameter.HasDefaultValue;
if (HasDefaultValue)
DefaultValue = parameter.DefaultValue;
}
private string GetFormattedDataType(ProcedureParameter parameter)
{
var sizeColumn = parameter as VariableSizeProcedureParameter;
if (sizeColumn != null)
{
string size = sizeColumn.IsMaximumSize ? "max" : sizeColumn.Size.ToString();
return string.Format("{0}({1})", parameter.DataType, size);
}
var decimalColumn = parameter as DecimalProcedureParameter;
if (decimalColumn != null)
{
return string.Format("{0}({1},{2})", parameter.DataType, decimalColumn.Precision, decimalColumn.Scale);
}
return parameter.DataType.ToString();
}
/// <summary>
/// Creates a Column-derived field for a stored procedure parameter.
/// </summary>
/// <param name="parameter">The stored procedure parameter</param>
/// <returns>The field declaration</returns>
private MemberDeclarationSyntax CreateFieldForParameter(ProcedureParameter parameter)
{
TypeSyntax typeName;
List <ArgumentSyntax> arguments = new List <ArgumentSyntax>
{
Argument(LiteralExpression(SyntaxKind.StringLiteralExpression, Literal(parameter.VariableName.Value))),
};
if (TryGetSqlDbTypeForParameter(parameter, out SqlDbType sqlDbType))
{
// new ParameterDefinition<int>("@paramName", SqlDbType.Int, nullable, maxlength,...)
typeName = GenericName("ParameterDefinition")
.AddTypeArgumentListArguments(SqlDbTypeToClrType(sqlDbType, nullable: parameter.Value != null).ToTypeSyntax(true));
arguments.Add(
Argument(
MemberAccessExpression(
SyntaxKind.SimpleMemberAccessExpression,
typeof(SqlDbType).ToTypeSyntax(true),
IdentifierName(sqlDbType.ToString()))));
arguments.Add(
Argument(
LiteralExpression(parameter.Value == null ? SyntaxKind.FalseLiteralExpression : SyntaxKind.TrueLiteralExpression)));
arguments.AddRange(GetDataTypeSpecificConstructorArguments(parameter.DataType, null));
}
else
{
// new MyTableValuedParameterDefinition("@paramName");
typeName = IdentifierName(GetClassNameForTableValuedParameterDefinition(parameter.DataType.Name));
}
return(FieldDeclaration(
VariableDeclaration(typeName)
// call it "_myParam" when the parameter is named "@myParam"
.AddVariables(VariableDeclarator(FieldNameForParameter(parameter))
.WithInitializer(
EqualsValueClause(
ObjectCreationExpression(typeName)
.AddArgumentListArguments(arguments.ToArray())))))
.AddModifiers(Token(SyntaxKind.PrivateKeyword), Token(SyntaxKind.ReadOnlyKeyword)));
}
public DataTable GetGZB(DateTime startDate, DateTime endDate, string fundCode, string itemCode)
{
DBServiceClient client = new DBServiceClient();
ProcedureParameter[] paraList = new ProcedureParameter[5];
ProcedureParameter para0 = new ProcedureParameter();
para0.Name = "o_cursor";
para0.Direction = ParameterDirection.Output;
para0.Type = ProcedureParameter.DBType.Cursor;
paraList[0] = para0;
ProcedureParameter para1 = new ProcedureParameter();
para1.Name = "i_fundcode"; para1.Value = fundCode;
para1.Direction = ParameterDirection.Input;
para1.Type = ProcedureParameter.DBType.NVarChar;
paraList[1] = para1;
ProcedureParameter para2 = new ProcedureParameter();
para2.Name = "i_startdate"; para2.Value = startDate.ToString("yyyyMMdd");
para2.Direction = ParameterDirection.Input;
para2.Type = ProcedureParameter.DBType.NVarChar;
paraList[2] = para2;
ProcedureParameter para3 = new ProcedureParameter();
para3.Name = "i_enddate"; para3.Value = endDate.ToString("yyyyMMdd");
para3.Direction = ParameterDirection.Input;
para3.Type = ProcedureParameter.DBType.NVarChar;
paraList[3] = para3;
ProcedureParameter para4 = new ProcedureParameter();
para4.Name = "i_itemcode"; para4.Value = itemCode;
para4.Direction = ParameterDirection.Input;
para4.Type = ProcedureParameter.DBType.NVarChar;
paraList[4] = para4;
return(client.ExecuteStoredProcedure("db5_proc_selectgzb", paraList).Tables[0]);
}
public DataTable GetAnalyst()
{
try
{
DataTable dtAnalyst;
DBServiceClient client = new DBServiceClient();
ProcedureParameter[] paraList = new ProcedureParameter[1];
paraList[0] = new ProcedureParameter(); paraList[0].Name = "o_cursor"; paraList[0].Direction = ParameterDirection.Output; paraList[0].Type = ProcedureParameter.DBType.Cursor;
DataSet ds = client.ExecuteStoredProcedure("db1_proc_selectanalyst", paraList);
dtAnalyst = ds.Tables[0];
return(dtAnalyst);
}
catch (Exception ex)
{
throw ex;
}
}
public void ProcedureParameterWithT_SetupDbCommand_SetsCommandAsExpected()
{
var parameterName = "parameter_name";
var parameterValue = "a-string-value";
var subject = new ProcedureParameter <string>(parameterName, parameterValue);
var dbCommandMock = new Mock <IDbCommand>();
var dbParameterMock = new Mock <IDbDataParameter>();
var dbParameterCollectionMock = new Mock <IDataParameterCollection>();
var myParameterList = new List <IDbDataParameter>();
var actualParameterName = default(string);
var actualParameterValue = default(object);
dbCommandMock
.Setup(m => m.CreateParameter())
.Returns(() => dbParameterMock.Object);
dbCommandMock
.SetupGet(m => m.Parameters)
.Returns(() => dbParameterCollectionMock.Object);
dbParameterMock
.SetupSet(m => m.ParameterName = It.IsAny <string>())
.Callback <string>(value => actualParameterName = value);
dbParameterMock
.SetupSet(m => m.Value = It.IsAny <string>())
.Callback <object>(value => actualParameterValue = value);
dbParameterCollectionMock
.Setup(m => m.Add(It.IsAny <IDbDataParameter>()))
.Callback <object>(parameter => myParameterList.Add(parameter as IDbDataParameter));
subject.SetupDbCommand(dbCommandMock.Object);
Assert.AreEqual(1, myParameterList.Count);
var actualParameter = myParameterList.First();
Assert.AreEqual("@parameter_name", actualParameterName);
Assert.AreEqual("a-string-value", actualParameterValue);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
string ID = context.Request["ID"];
string name = context.Request["name"];
string value = context.Request["value"];
try
{
DataRow dr = TaskBll.GetTaskInfo(ID);
if (dr == null || dr["Status"] == null)
{
context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { status = -1, msg = "计划的TaskID非法！" }));
return;
}
var status = dr["Status"] == DBNull.Value ? "-1" : dr["Status"].ToString();
if (status != "1") // 非配料状态
{
context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { status = -1, msg = "状态异常，该计划为无料状态！" }));
return;
}
ProcedureParameter[] para = new ProcedureParameter[] {
new ProcedureParameter("@CurBuildPlanID", int.Parse(dr["BuildPlanID"].ToString())),
new ProcedureParameter("@RealExpectedTime", value)
};
new DAL.DbHelper().Execute("P_UpdateExpectedTime", para, true);
context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { status = 0, msg = "操作完成！" }));
}
catch (Exception ex)
{
BLL.ErrorBll.LogError($"更新计划Taskid:[{ID}]的realExpectedTime异常", ex);
context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { status = -1, msg = "系统繁忙！" }));
}
}
/// <summary>
/// update Feeder Card status
/// </summary>
/// <param name="taskID"></param>
/// <param name="FeederCarNo"></param>
/// <returns></returns>
public static SerResult UpdataFeederCard(string taskID, string FeederCarNo)
{
string sql = $@" update [dbo].[EKS_T_Task] set FeederCar=@FeederCar,Feeder='OK' where ID=@ID ";
ProcedureParameter[] para = new ProcedureParameter[] {
new ProcedureParameter("@FeederCar", FeederCarNo),
new ProcedureParameter("@ID", taskID),
};
try
{
new DAL.DbHelper().Execute(sql, para);
}
catch (Exception ex)
{
ErrorBll.LogError("更新喂料车状态异常！", ex);
return(SerResult.Create(-1, "系统繁忙！"));
}
return(SerResult.Success());
}
public static SerResult UpdataStencil(string taskID, string stencil)
{
string sql = $@" update [dbo].[EKS_T_Task] set Stencil=@Stencil,StencilCount=1 where ID=@ID ";
ProcedureParameter[] para = new ProcedureParameter[] {
new ProcedureParameter("@Stencil", stencil),
new ProcedureParameter("@ID", taskID),
};
try
{
new DAL.DbHelper().Execute(sql, para);
}
catch (Exception ex)
{
ErrorBll.LogError("更新钢网状态异常！", ex);
return(SerResult.Create(-1, "系统繁忙！"));
}
return(SerResult.Success());
}
private TextBox DajInputBox(ProcedureParameter paParameter, SqlClassAttribute paAttribut = null)
{
var box = new TextBox
{
Tag = paParameter,
Margin = new Thickness(5, 0, 5, 0),
FontSize = 20,
MinWidth = 100,
MaxLength = (paAttribut?.Length ?? 0) != 0 ? paAttribut.Length : 50
};
box.SetBinding(TextBox.TextProperty, new Binding()
{
Path = new PropertyPath(nameof(paParameter.HodnotaParametra)),
Source = paParameter,
Mode = BindingMode.TwoWay,
ConverterCulture = CultureInfo.CurrentCulture,
StringFormat = paAttribut?.SpecialFormat
});
return(box);
}
public DataTable GetFundSecurityPoolHistory(string stockCode, DateTime startdate, DateTime enddate, bool?isEquity, int?hedgefundid)
{
try
{
if (stockCode == null || stockCode.Length == 0)
{
stockCode = "%";
}
stockCode = stockCode.Replace("*", "%");
DBServiceClient client = new DBServiceClient();
ProcedureParameter[] paraList = new ProcedureParameter[6];
paraList[0] = new ProcedureParameter(); paraList[0].Name = "i_stockcode"; paraList[0].Value = stockCode; paraList[0].Direction = ParameterDirection.Input; paraList[0].Type = ProcedureParameter.DBType.NVarChar;
paraList[1] = new ProcedureParameter(); paraList[1].Name = "o_cursor"; paraList[1].Type = ProcedureParameter.DBType.Cursor; paraList[1].Direction = ParameterDirection.Output;
paraList[2] = new ProcedureParameter(); paraList[2].Name = "i_startdate"; paraList[2].Value = startdate.ToString("yyyyMMdd"); paraList[2].Direction = ParameterDirection.Input; paraList[2].Type = ProcedureParameter.DBType.NVarChar;
paraList[3] = new ProcedureParameter(); paraList[3].Name = "i_enddate"; paraList[3].Value = enddate.ToString("yyyyMMdd"); paraList[3].Direction = ParameterDirection.Input; paraList[3].Type = ProcedureParameter.DBType.NVarChar;
paraList[4] = new ProcedureParameter(); paraList[4].Name = "i_sectype"; paraList[4].Value = (isEquity == null || isEquity.Value == false) ? "B" : "E"; paraList[4].Direction = ParameterDirection.Input; paraList[4].Type = ProcedureParameter.DBType.Char;
paraList[5] = new ProcedureParameter(); paraList[5].Name = "i_hedgefundid"; paraList[5].Value = hedgefundid; paraList[5].Direction = ParameterDirection.Input; paraList[5].Type = ProcedureParameter.DBType.Int;
if (isEquity == null)
{
paraList[4].Value = null;
}
else
{
paraList[4].Value = (isEquity.Value == true ? "E" : "B");
}
DataSet ds = client.ExecuteStoredProcedure("db2_proc_selectfundequityhist", paraList);
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetIndustryCodes()
{
try
{
if (_dtIndustryCode == null)
{
DBServiceClient client = new DBServiceClient();
ProcedureParameter[] paraList = new ProcedureParameter[1];
paraList[0] = new ProcedureParameter(); paraList[0].Name = "o_cursor"; paraList[0].Direction = ParameterDirection.Output; paraList[0].Type = ProcedureParameter.DBType.Cursor;
DataSet ds = client.ExecuteStoredProcedure("db2_proc_selectIndustrycodes", paraList);
_dtIndustryCode = ds.Tables[0];
}
return(_dtIndustryCode);
}
catch (Exception ex)
{
throw ex;
}
}
private void GenerateParameters(ProcedureParameter parameter)
{
_sb.AppendLine($"var parameter{parameter.Name} = new SqlParameter");
_sb.AppendLine("{");
using (_sb.Indent())
{
_sb.AppendLine($"ParameterName = \"{parameter.Name}\",");
if (parameter.Precision > 0)
{
_sb.AppendLine($"Precision = {parameter.Precision},");
}
if (parameter.Scale > 0)
{
_sb.AppendLine($"Scale = {parameter.Scale},");
}
if (parameter.Length > 0)
{
_sb.AppendLine($"Size = {parameter.Length},");
}
if (parameter.Output)
{
_sb.AppendLine("Direction = System.Data.ParameterDirection.Output,");
}
_sb.AppendLine($"SqlDbType = System.Data.SqlDbType.{parameter.DbType()},");
_sb.AppendLine($"Value = {parameter.Name},");
if (parameter.DbType() == System.Data.SqlDbType.Structured)
{
_sb.AppendLine($"TypeName = \"{parameter.TypeName}\",");
}
}
_sb.AppendLine("};");
_sb.AppendLine();
}
public ParameterSignature(string fullProcedureName, ProcedureParameter parameter)
{
Name = parameter.Name;
Type = parameter.Type;
// Check the parameter type is valid
if (!TypeUtils.IsAValidType (Type))
throw new ServiceException (Type + " is not a valid Procedure parameter type, in " + fullProcedureName);
// Encode the default value as a ByteString
if (parameter.HasDefaultValue) {
var value = parameter.DefaultValue;
if (TypeUtils.IsAClassType (Type))
DefaultArgument = ProtocolBuffers.WriteValue (ObjectStore.Instance.AddInstance (value), typeof(ulong));
else if (ProtocolBuffers.IsAnEnumType (Type) || TypeUtils.IsAnEnumType (Type)) {
// TODO: Assumes it's underlying type is int
DefaultArgument = ProtocolBuffers.WriteValue ((int)value, typeof(int));
} else if (ProtocolBuffers.IsAMessageType (Type))
DefaultArgument = ProtocolBuffers.WriteMessage (value as IMessage);
else
DefaultArgument = ProtocolBuffers.WriteValue (value, Type);
}
}
private string CreateParameterDefinition(ProcedureParameter parameter, IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
var builder = new StringBuilder();
builder.Append(parameter.ParameterName.AsSqlIndentifier());
builder.Append(" ");
if (context.EmptyBodiesEnabled)
{
builder.Append(SqlHelper.GetDataType(parameter.Field, sourceMetadata.MetadataCharacterSets.CharacterSetsById, sourceMetadata.MetadataDatabase.CharacterSet.CharacterSetId));
}
else
{
builder.Append(SqlHelper.GetDataType(parameter, sourceMetadata.MetadataCharacterSets.CharacterSetsById, sourceMetadata.MetadataDatabase.CharacterSet.CharacterSetId));
}
var notNullClause = SqlHelper.HandleNullable(parameter);
if (notNullClause != null)
{
builder.Append(" ");
builder.Append(notNullClause);
}
var collateClause = SqlHelper.HandleCollate(parameter, sourceMetadata.MetadataCollations.CollationsByKey);
if (collateClause != null)
{
builder.Append(" ");
builder.Append(collateClause);
}
var defaultClause = SqlHelper.HandleDefault(parameter);
if (defaultClause != null)
{
builder.Append(" ");
builder.Append(defaultClause);
}
return(builder.ToString());
}
public DataAccessMetadata GetMetadata(DataAccessMetadataType metadataType, string database = null, string schema = null, string schemaObject = null, string schemaSubObject = null)
{
DataTable metadataTable = zGetMetadata(metadataType, database, schema, schemaObject, schemaSubObject);
DataAccessMetadata metadata = new DataAccessMetadata();
foreach (DataRow row in metadataTable.Rows)
{
switch (metadataType)
{
case DataAccessMetadataType.Tables:
metadata.Tables.Add(Table.FromRow(row));
break;
case DataAccessMetadataType.TableColumns:
metadata.TableColumns.Add(Column.FromRow(row));
break;
case DataAccessMetadataType.Views:
metadata.Views.Add(View.FromRow(row));
break;
case DataAccessMetadataType.ViewColumns:
metadata.ViewColumns.Add(Column.FromRow(row));
break;
case DataAccessMetadataType.Procedures:
metadata.Procedures.Add(Procedure.FromRow(row));
break;
case DataAccessMetadataType.ProcedureParameters:
metadata.ProcedureParameters.Add(ProcedureParameter.FromRow(row));
break;
}
}
return(metadata);
}
public WebWorkQueueQueryParameters()
: base("WebQueryWorkQueue")
{
//Declare output parameters here
SubCriteria["ResultCount"] = new ProcedureParameter <int>("ResultCount");
}
/// <summary>参数类型转化</summary>
/// <param name="Par"></param>
/// <returns></returns>
private MySqlParameter ParameterTransform(ProcedureParameter Par)
{
/*车毅修改 支持无类型参数*/
if (Par.ParType == ProcParType.Default)
{
return(new MySqlParameter(Par.Name, Par.Value == null ? DBNull.Value : Par.Value));
}
MySqlParameter p = new MySqlParameter();
p.ParameterName = Par.Name;
switch (Par.ParType)
{
case ProcParType.Int16:
p.MySqlDbType = MySqlDbType.Int16;
break;
case ProcParType.Int32:
p.MySqlDbType = MySqlDbType.Int32;
break;
case ProcParType.Int64:
p.MySqlDbType = MySqlDbType.Int64;
break;
case ProcParType.Single:
p.MySqlDbType = MySqlDbType.Float;
break;
case ProcParType.Double:
p.MySqlDbType = MySqlDbType.Double;
break;
case ProcParType.Decimal:
p.MySqlDbType = MySqlDbType.Decimal;
break;
case ProcParType.Char:
p.MySqlDbType = MySqlDbType.VarChar;
break;
case ProcParType.VarChar:
p.MySqlDbType = MySqlDbType.VarChar;
break;
case ProcParType.NVarchar:
p.MySqlDbType = MySqlDbType.VarChar;
break;
case ProcParType.Image:
p.MySqlDbType = MySqlDbType.Binary;
break;
case ProcParType.DateTime:
p.MySqlDbType = MySqlDbType.DateTime;
break;
default:
throw new Exception("未知类型ProcParType：" + Par.ParType.ToString());
}
p.Size = Par.Size;
p.Direction = Par.Direction;
switch (Par.Direction)
{
case ParameterDirection.Input:
case ParameterDirection.InputOutput:
if (Par.Value == null)
{
p.Value = DBNull.Value;
}
else
{
p.Value = Par.Value;
}
break;
}
return(p);
}
public override void ExplicitVisit(ProcedureParameter s)
{
ValidateName(s);
}
private string GetFormattedDirection(ProcedureParameter parameter)
{
if (parameter.Direction == ParameterDirection.Input) return null;
return " OUTPUT";
}
private static string PropertyNameForParameter(ProcedureParameter parameter)
{
string parameterName = parameter.VariableName.Value;
return($"{char.ToUpperInvariant(parameterName[1])}{(parameterName.Length > 2 ? parameterName.Substring(2) : string.Empty)}");
}
private static string ParameterNameForParameter(ProcedureParameter parameter)
{
return(parameter.VariableName.Value.Substring(1));
}
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);
}
}
}
private async Task <DataBaseParameter> GetDbParameter(ProcedureName procedureName, DbRequestToken token, ProcedureParameter parameter)
{
var dbParam = new DataBaseParameter(parameter);
if (parameter.DbType.HasValue)
{
return(dbParam);
}
var procNameParam = new ProcedureParameter("procedureName", procedureName.GetStringName())
{
DbType = SqlDbType.NVarChar
};
var res = (await ExecProcedure <ProcedureParameterDescription>(ProcedureName.spInternalGetProcedureParameters, token, procNameParam)).Single();
dbParam.DbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), res.ToString());
return(dbParam);
}
public void TestInitialize()
{
parameter1 = new StandardProcedureParameter("p1", SqlDbType.DateTime, ParameterDirection.Input);
definition = new ProcedureDefinition(procedureName, new[] { parameter1 });
}
public override void Visit(ProcedureParameter node) { this.action(node); }
public override void ExplicitVisit(ProcedureParameter fragment)
{
_fragments.Add(fragment);
}
public override void ExplicitVisit(ProcedureParameter node)
{
_parameters.Add(new ProcedureParameterInfo(
node.VariableName.Value,
node.DataType.Name.BaseIdentifier.Value)
);
base.ExplicitVisit(node);
}
public InsertStudyIntegrityQueueParameters()
: base("InsertStudyIntegrityQueue")
{
// This is output from the stored procedure
SubCriteria["Inserted"] = new ProcedureParameter<bool>("Inserted");
}
public WebQueryArchiveQueueParameters()
: base("WebQueryArchiveQueue")
{
//Declare output parameters here
SubCriteria["ResultCount"] = new ProcedureParameter<int>("ResultCount");
}
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;
}
}
}
}
private List <ProcedureParameter> GetStoredProcedureParameters(SqlConnection connection, string schema, string name)
{
var dtResult = new DataTable();
var result = new List <ProcedureParameter>();
// Validate this - based on https://stackoverflow.com/questions/20115881/how-to-get-stored-procedure-parameters-details/41330791
var sql = $@"
SELECT
'Parameter' = name,
'Type' = type_name(system_type_id),
'Length' = CAST(max_length AS INT),
'Precision' = CAST(case when type_name(system_type_id) = 'uniqueidentifier'
then precision
else OdbcPrec(system_type_id, max_length, precision) end AS INT),
'Scale' = CAST(OdbcScale(system_type_id, scale) AS INT),
'Order' = CAST(parameter_id AS INT),
'Collation' = convert(sysname,
case when system_type_id in (35, 99, 167, 175, 231, 239)
then ServerProperty('collation') end),
is_output AS output,
is_nullable AS nullable,
'TypeName' = QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(TYPE_NAME(user_type_id))
from sys.parameters where object_id = object_id('{schema}.{name}')
ORDER BY parameter_id;";
var adapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand(sql, connection)
};
adapter.Fill(dtResult);
foreach (DataRow par in dtResult.Rows)
{
var parameter = new ProcedureParameter()
{
Name = par["Parameter"].ToString().Replace("@", ""),
StoreType = par["Type"].ToString(),
Length = par["Length"].GetType() == typeof(DBNull) ? (int?)null : int.Parse(par["Length"].ToString()),
Precision = par["Precision"].GetType() == typeof(DBNull) ? (int?)null : int.Parse(par["Precision"].ToString()),
Scale = par["Scale"].GetType() == typeof(DBNull) ? (int?)null : int.Parse(par["Scale"].ToString()),
Output = (bool)par["output"],
Nullable = (bool)par["nullable"],
TypeName = par["TypeName"].ToString(),
};
result.Add(parameter);
}
// Add parameter to hold the standard return value
result.Add(new ProcedureParameter()
{
Name = "returnValue",
StoreType = "int",
Output = true,
Nullable = false,
});
return(result);
}
private static bool TryGetSqlDbTypeForParameter(ProcedureParameter parameter, out SqlDbType sqlDbType)
{
return(Enum.TryParse <SqlDbType>(parameter.DataType.Name.BaseIdentifier.Value, ignoreCase: true, out sqlDbType));
}
private static string getDbCommandParameterCreationExpression(ProcedureParameter parameter)
{
var parameterValueExpression = parameter.GetParameterValueExpression(parameter.Direction != ParameterDirection.Output ? parameter.Name : "null");
return("new DbCommandParameter( \"{0}\", {1} )".FormatWith(parameter.Name, parameterValueExpression));
}
private static string FieldNameForParameter(ProcedureParameter parameter)
{
return($"_{parameter.VariableName.Value.Substring(1)}");
}
public ProcedureParameterViewModel(ProcedureParameter model)
{
Name = model.Name;
Alias = model.Alias;
}
public override DatabaseObjectsCollection <IProcedure> LoadProcedures(IDatabase database)
{
DatabaseObjectsCollection <IProcedure> procedures = new DatabaseObjectsCollection <IProcedure>(database);
SqlConnection sqlConnection = new SqlConnection(database.ConnectionString);
using (SqlDataAdapter da = new SqlDataAdapter(_getProcedures, sqlConnection))
{
DataTable dtProcedures = new DataTable("Procedures");
da.Fill(dtProcedures);
for (int i = 0; i < dtProcedures.Rows.Count; i++)
{
Procedure procedure = new Procedure();
procedure.Name = (string)dtProcedures.Rows[i]["ROUTINE_NAME"];
procedure.SchemaName = (string)dtProcedures.Rows[i]["ROUTINE_SCHEMA"];
if (dtProcedures.Rows[i]["DATA_TYPE"] != DBNull.Value)
{
procedure.DataType = (string)dtProcedures.Rows[i]["DATA_TYPE"];
}
if (dtProcedures.Rows[i]["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
procedure.Length = Convert.ToInt32(dtProcedures.Rows[i]["CHARACTER_MAXIMUM_LENGTH"]);
}
if (procedure.Length == -1)
{
switch (procedure.DataType.ToLower())
{
case "varchar":
case "nvarchar":
procedure.DataType += "(max)";
break;
default:
break;
}
}
string procedureType = (string)dtProcedures.Rows[i]["ROUTINE_BODY"];
if (procedureType == "SQL")
{
procedure.ProcedureType = ProcedureType.SQL;
}
else
{
procedure.ProcedureType = ProcedureType.External;
}
procedure.Items = new DatabaseObjectsCollection <IProcedureParameter>(procedure);
DatabaseObjectsCollection <IProcedureParameter> procedureParameters = new DatabaseObjectsCollection <IProcedureParameter>(procedure);
da.SelectCommand = new SqlCommand(string.Format(_getProcedureParameters, procedure.Name, procedure.SchemaName, database.Name), sqlConnection);
DataTable dtProcedureParameters = new DataTable("ProcedureParameters");
da.Fill(dtProcedureParameters);
for (int j = 0; j < dtProcedureParameters.Rows.Count; j++)
{
if (string.IsNullOrEmpty((string)dtProcedureParameters.Rows[j]["PARAMETER_NAME"]) &&
(string)dtProcedureParameters.Rows[j]["IS_RESULT"] == "YES") // = ReturnValue
{
continue;
}
ProcedureParameter procedureParameter = new ProcedureParameter();
procedureParameter.Name = (string)dtProcedureParameters.Rows[j]["PARAMETER_NAME"];
if (procedureParameter.Name.StartsWith("@"))
{
procedureParameter.Name = procedureParameter.Name.Substring(1);
}
if (dtProcedureParameters.Rows[j]["DATA_TYPE"] != DBNull.Value)
{
procedureParameter.DataType = (string)dtProcedureParameters.Rows[j]["DATA_TYPE"];
}
if (dtProcedureParameters.Rows[j]["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
procedureParameter.Length = Convert.ToInt32(dtProcedureParameters.Rows[j]["CHARACTER_MAXIMUM_LENGTH"]);
}
string parameterMode = (string)dtProcedureParameters.Rows[j]["PARAMETER_MODE"];
if (parameterMode == "IN")
{
procedureParameter.ParameterMode = ParameterMode.In;
}
else if (parameterMode == "OUT")
{
procedureParameter.ParameterMode = ParameterMode.Out;
}
else
{
procedureParameter.ParameterMode = ParameterMode.InOut;
}
procedure.Items.Add(procedureParameter);
}
procedures.Add(procedure);
}
}
return(procedures);
}
public override void Visit(ProcedureParameter node)
{
ProcedureParameters.Add(node);
}
public InsertStudyIntegrityQueueParameters()
: base("InsertStudyIntegrityQueue")
{
// This is output from the stored procedure
SubCriteria["Inserted"] = new ProcedureParameter <bool>("Inserted");
}
public WebQueryStudyIntegrityQueueParameters()
: base("WebQueryStudyIntegrityQueue")
{
//Declare output parameters here
SubCriteria["ResultCount"] = new ProcedureParameter <int>("ResultCount");
}
