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