/// <summary> /// Example of MS SQL Server Stored Procedure. /// </summary> /// <exception cref="AceQLException">If any Exception occurs.</exception> public async Task CallStoredProcedure() { string sql = "{call ProcedureName(@parm1, @parm2, @parm3)}"; AceQLCommand command = new AceQLCommand(sql, connection); command.CommandType = CommandType.StoredProcedure; AceQLParameter aceQLParameter2 = new AceQLParameter("@parm2", 2) { Direction = ParameterDirection.InputOutput }; AceQLParameter aceQLParameter1 = new AceQLParameter("@parm1", 0); AceQLParameter aceQLParameter3 = new AceQLParameter("@parm3") { Direction = ParameterDirection.Output }; command.Parameters.Add(aceQLParameter1); command.Parameters.Add(aceQLParameter2); command.Parameters.Add(aceQLParameter3); AceQLConsole.WriteLine(sql); AceQLConsole.WriteLine("BEFORE execute @parm1: " + aceQLParameter1.ParameterName + " / " + aceQLParameter1.Value + " (" + aceQLParameter2.Value.GetType() + ")"); AceQLConsole.WriteLine("BEFORE execute @parm2: " + aceQLParameter2.ParameterName + " / " + aceQLParameter2.Value + " (" + aceQLParameter2.Value.GetType() + ")"); AceQLConsole.WriteLine("BEFORE execute @parm3: " + aceQLParameter3.ParameterName + " / " + aceQLParameter3.Value); AceQLConsole.WriteLine(); // Our dataReader must be disposed to delete underlying downloaded files using (AceQLDataReader dataReader = await command.ExecuteReaderAsync()) { //await dataReader.ReadAsync(new CancellationTokenSource().Token) while (dataReader.Read()) { int i = 2; AceQLConsole.WriteLine("GetValue: " + dataReader.GetValue(i)); } } AceQLConsole.WriteLine(); AceQLConsole.WriteLine("AFTER execute @parm2: " + aceQLParameter2.ParameterName + " / " + aceQLParameter2.Value + " (" + aceQLParameter2.Value.GetType() + ")"); AceQLConsole.WriteLine("AFTER execute @parm3: " + aceQLParameter3.ParameterName + " / " + aceQLParameter3.Value); }
/// <summary> /// Gets the prepared statement parameters. /// </summary> /// <returns>The Parameters List</returns> internal Dictionary <string, string> GetPreparedStatementParameters() { Dictionary <String, int> paramsIndexInPrepStatement = GetPreparedStatementParametersDic(); Dictionary <string, string> parametersList = new Dictionary <string, string>(); // For each parameter 1) get the index 2) get the dbType foreach (KeyValuePair <String, int> parameter in paramsIndexInPrepStatement) { AceQLParameter aceQLParameter = this.Parameters.GetAceQLParameter(parameter.Key); int paramIndex = parameter.Value; AceQLNullType aceQLNullType = aceQLParameter.SqlNullType; Object ParmValue = aceQLParameter.Value; //Reconvert SqlType original Java value by diving per 10000 and multiplying per -1: int sqlType = (int)aceQLNullType; sqlType = sqlType / (int)AceQLNullType.CHAR; // For OUT parameters that may be null value if (ParmValue == null) { ParmValue = "NULL"; } Debug("paramIndex: " + paramIndex); Debug("ParmValue : " + ParmValue + ":"); if (aceQLParameter.IsNullValue) { String paramType = "TYPE_NULL" + sqlType; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, "NULL"); } else if (ParmValue is Stream) { // All streams are blob for now // This will be enhanced in future version String blobId = BuildUniqueBlobId(); blobIds.Add(blobId); blobStreams.Add((Stream)ParmValue); blobLengths.Add(aceQLParameter.BlobLength); String paramType = "BLOB"; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, blobId); } else if (ParmValue is string || ParmValue is String) { String paramType = "VARCHAR"; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, ParmValue.ToString()); } else if (ParmValue is long) { String paramType = "BIGINT"; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, ParmValue.ToString()); } else if (ParmValue is int) { String paramType = "INTEGER"; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, ParmValue.ToString()); } else if (ParmValue is short) { String paramType = "TINYINT"; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, ParmValue.ToString()); } else if (ParmValue is bool || ParmValue is Boolean) { String paramType = "BIT"; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, ParmValue.ToString()); } else if (ParmValue is float) { String paramType = "REAL"; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, ParmValue.ToString().Replace(",", ".")); } else if (ParmValue is double || ParmValue is Double) { String paramType = "DOUBLE_PRECISION"; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, ParmValue.ToString().Replace(",", ".")); } else if (ParmValue is DateTime) { String paramType = "TIMESTAMP"; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, ConvertToTimestamp((DateTime)ParmValue)); } else if (ParmValue is TimeSpan) { String paramType = "TIME"; parametersList.Add("param_type_" + paramIndex, paramType); parametersList.Add("param_value_" + paramIndex, ConvertToTimestamp((DateTime)ParmValue)); } else { throw new AceQLException("Type of value is not supported. Value: " + ParmValue + " / Type: " + ParmValue.GetType(), 2, (Exception)null, HttpStatusCode.OK); } if (!aceQLParameter.IsNullValue && !(ParmValue is Stream)) { if (aceQLParameter.Direction == Api.ParameterDirection.InputOutput) { parametersList.Add("param_direction_" + paramIndex, "inout"); parametersList.Add("out_param_name_" + paramIndex, aceQLParameter.ParameterName); } else if (aceQLParameter.Direction == Api.ParameterDirection.Output) { parametersList.Add("param_direction_" + paramIndex, "out"); parametersList.Add("out_param_name_" + paramIndex, aceQLParameter.ParameterName); } else { // Defaults to "in" on server } } } return(parametersList); }