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