/// <summary>
/// Retrieves parameter information from the stored procedure specified
/// in the MySqlCommand and populates the Parameters collection of the
/// specified MySqlCommand object.
/// This method is not currently supported since stored procedures are
/// not available in MySql.
/// </summary>
/// <param name="command">The MySqlCommand referencing the stored
/// procedure from which the parameter information is to be derived.
/// The derived parameters are added to the Parameters collection of the
/// MySqlCommand.</param>
/// <exception cref="InvalidOperationException">The command text is not
/// a valid stored procedure name.</exception>
public static void DeriveParameters(MySqlCommand command)
{
if (command.CommandType != CommandType.StoredProcedure)
throw new InvalidOperationException(Resources.CanNotDeriveParametersForTextCommands);
// retrieve the proc definition from the cache.
string spName = command.CommandText;
if (spName.IndexOf(".") == -1)
spName = command.Connection.Database + "." + spName;
try
{
ProcedureCacheEntry entry = command.Connection.ProcedureCache.GetProcedure(command.Connection, spName, null);
command.Parameters.Clear();
foreach (MySqlSchemaRow row in entry.parameters.Rows)
{
MySqlParameter p = new MySqlParameter();
p.ParameterName = String.Format("@{0}", row["PARAMETER_NAME"]);
if (row["ORDINAL_POSITION"].Equals(0) && p.ParameterName == "@")
p.ParameterName = "@RETURN_VALUE";
p.Direction = GetDirection(row);
bool unsigned = StoredProcedure.GetFlags(row["DTD_IDENTIFIER"].ToString()).IndexOf("UNSIGNED") != -1;
bool real_as_float = entry.procedure.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
p.MySqlDbType = MetaData.NameToType(row["DATA_TYPE"].ToString(),
unsigned, real_as_float, command.Connection);
if (row["CHARACTER_MAXIMUM_LENGTH"] != null)
p.Size = (int)row["CHARACTER_MAXIMUM_LENGTH"];
#if NET452 || DNX452 || NETSTANDARD1_6
if (row["NUMERIC_PRECISION"] != null)
p.Precision = Convert.ToByte(row["NUMERIC_PRECISION"]);
if (row["NUMERIC_SCALE"] != null )
p.Scale = Convert.ToByte(row["NUMERIC_SCALE"]);
#endif
if (p.MySqlDbType == MySqlDbType.Set || p.MySqlDbType == MySqlDbType.Enum)
p.PossibleValues = GetPossibleValues(row);
command.Parameters.Add(p);
}
}
catch (InvalidOperationException ioe)
{
throw new MySqlException(Resources.UnableToDeriveParameters, ioe);
}
}
/// <summary>
/// Adds the specified <see cref="MySqlParameter"/> object to the <see cref="MySqlParameterCollection"/>.
/// </summary>
/// <param name="value">The <see cref="MySqlParameter"/> to add to the collection.</param>
/// <returns>The newly added <see cref="MySqlParameter"/> object.</returns>
public MySqlParameter Add(MySqlParameter value)
{
return InternalAdd(value, -1);
}
private MySqlParameter InternalAdd(MySqlParameter value, int index)
{
if (value == null)
throw new ArgumentException("The MySqlParameterCollection only accepts non-null MySqlParameter type objects.", "value");
// if the parameter is unnamed, then assign a default name
if (String.IsNullOrEmpty(value.ParameterName))
value.ParameterName = String.Format("Parameter{0}", GetNextIndex());
// make sure we don't already have a parameter with this name
if (IndexOf(value.ParameterName) >= 0)
{
throw new MySqlException(
String.Format(Resources.ParameterAlreadyDefined, value.ParameterName));
}
else
{
string inComingName = value.ParameterName;
if (inComingName[0] == '@' || inComingName[0] == '?')
inComingName = inComingName.Substring(1, inComingName.Length - 1);
if (IndexOf(inComingName) >= 0)
throw new MySqlException(
String.Format(Resources.ParameterAlreadyDefined, value.ParameterName));
}
if (index == -1)
{
items.Add(value);
index = items.Count - 1;
}
else
{
items.Insert(index, value);
AdjustHashes(index, true);
}
indexHashCS.Add(value.ParameterName, index);
indexHashCI.Add(value.ParameterName, index);
value.Collection = this;
return value;
}
internal void ParameterNameChanged(MySqlParameter p, string oldName, string newName)
{
int index = IndexOf(oldName);
indexHashCS.Remove(oldName);
indexHashCI.Remove(oldName);
indexHashCS.Add(newName, index);
indexHashCI.Add(newName, index);
}
private void InternalSetParameter(int index, MySqlParameter value)
{
MySqlParameter newParameter = value as MySqlParameter;
if (newParameter == null)
throw new ArgumentException(Resources.NewValueShouldBeMySqlParameter);
CheckIndex(index);
MySqlParameter p = (MySqlParameter)items[index];
// first we remove the old parameter from our hashes
indexHashCS.Remove(p.ParameterName);
indexHashCI.Remove(p.ParameterName);
// then we add in the new parameter
items[index] = newParameter;
indexHashCS.Add(value.ParameterName, index);
indexHashCI.Add(value.ParameterName, index);
}
private void InternalSetParameter(string parameterName, MySqlParameter value)
{
int index = IndexOf(parameterName);
if (index < 0)
throw new ArgumentException("Parameter '" + parameterName + "' not found in the collection.");
InternalSetParameter(index, value);
}
private MySqlParameter GetAndFixParameter(string spName, MySqlSchemaRow param, bool realAsFloat, MySqlParameter returnParameter)
{
string mode = (string)param["PARAMETER_MODE"];
string pName = (string)param["PARAMETER_NAME"];
if (param["ORDINAL_POSITION"].Equals(0))
{
if (returnParameter == null)
throw new InvalidOperationException(
String.Format(Resources.RoutineRequiresReturnParameter, spName));
pName = returnParameter.ParameterName;
}
// make sure the parameters given to us have an appropriate type set if it's not already
MySqlParameter p = command.Parameters.GetParameterFlexible(pName, true);
if (!p.TypeHasBeenSet)
{
string datatype = (string)param["DATA_TYPE"];
bool unsigned = GetFlags(param["DTD_IDENTIFIER"].ToString()).IndexOf("UNSIGNED") != -1;
p.MySqlDbType = MetaData.NameToType(datatype, unsigned, realAsFloat, Connection);
}
return p;
}
private string CreateCallStatement(string spName, MySqlParameter returnParameter, MySqlParameterCollection parms)
{
StringBuilder callSql = new StringBuilder();
string delimiter = String.Empty;
foreach (MySqlParameter p in parms)
{
if (p.Direction == ParameterDirection.ReturnValue) continue;
string pName = "@" + p.BaseName;
string uName = "@" + ParameterPrefix + p.BaseName;
bool useRealVar = p.Direction == ParameterDirection.Input || serverProvidingOutputParameters;
callSql.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", delimiter, useRealVar ? pName : uName);
delimiter = ", ";
}
if (returnParameter == null)
return String.Format("CALL {0}({1})", spName, callSql.ToString());
else
return String.Format("SET @{0}{1}={2}({3})", ParameterPrefix, returnParameter.BaseName, spName, callSql.ToString());
}
public MySqlParameter Clone()
{
#if NETSTANDARD1_6
MySqlParameter clone = new MySqlParameter(paramName, mySqlDbType);
#else
MySqlParameter clone = new MySqlParameter(paramName, mySqlDbType, Direction, SourceColumn, SourceVersion, paramValue);
#endif
// if we have not had our type set yet then our clone should not either
clone.inferType = inferType;
return clone;
}
private static Task<MySqlDataReader> ExecuteReaderAsync(MySqlConnection connection, MySqlTransaction transaction, string commandText, MySqlParameter[] commandParameters, bool ExternalConn, CancellationToken cancellationToken)
{
var result = new TaskCompletionSource<MySqlDataReader>();
if (cancellationToken == CancellationToken.None || !cancellationToken.IsCancellationRequested)
{
try
{
var reader = ExecuteReader(connection, transaction, commandText, commandParameters, ExternalConn);
result.SetResult(reader);
}
catch (Exception ex)
{
result.SetException(ex);
}
}
else
{
result.SetCanceled();
}
return result.Task;
}
/// <summary>
/// Async version of ExecuteReader
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use for the command</param>
/// <param name="transaction"><see cref="MySqlTransaction"/> object to use for the command</param>
/// <param name="commandText">Command text to use</param>
/// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command</param>
/// <param name="ExternalConn">True if the connection should be preserved, false if not</param>
/// <returns><see cref="MySqlDataReader"/> object ready to read the results of the command</returns>
private static Task<MySqlDataReader> ExecuteReaderAsync(MySqlConnection connection, MySqlTransaction transaction, string commandText, MySqlParameter[] commandParameters, bool ExternalConn)
{
return ExecuteReaderAsync(connection, transaction, commandText, commandParameters, ExternalConn, CancellationToken.None);
}
/// <summary>
/// Executes a single command against a MySQL database, possibly inside an existing transaction.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use for the command</param>
/// <param name="transaction"><see cref="MySqlTransaction"/> object to use for the command</param>
/// <param name="commandText">Command text to use</param>
/// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command</param>
/// <param name="ExternalConn">True if the connection should be preserved, false if not</param>
/// <returns><see cref="MySqlDataReader"/> object ready to read the results of the command</returns>
private static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, string commandText, MySqlParameter[] commandParameters, bool ExternalConn)
{
//create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.Transaction = transaction;
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
if (commandParameters != null)
foreach (MySqlParameter p in commandParameters)
cmd.Parameters.Add(p);
//create a reader
MySqlDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
if (ExternalConn)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return dr;
}