/// <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
{
DataSet ds = command.Connection.ProcedureCache.GetProcedure(command.Connection, spName, null);
DataTable parameters = ds.Tables["Procedure Parameters"];
DataTable procTable = ds.Tables["Procedures"];
command.Parameters.Clear();
foreach (DataRow row in 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 = procTable.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"].Equals(DBNull.Value))
p.Size = (int)row["CHARACTER_MAXIMUM_LENGTH"];
if (!row["NUMERIC_PRECISION"].Equals(DBNull.Value))
p.Precision = Convert.ToByte(row["NUMERIC_PRECISION"]);
if (!row["NUMERIC_SCALE"].Equals(DBNull.Value))
p.Scale = Convert.ToByte(row["NUMERIC_SCALE"]);
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);
}
}
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());
}
private MySqlParameter GetAndFixParameter(string spName, DataRow 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;
}
public MySqlParameter Clone()
{
MySqlParameter clone = new MySqlParameter(paramName, mySqlDbType, direction,
sourceColumn, paramValue);
// if we have not had our type set yet then our clone should not either
clone.inferType = inferType;
return clone;
}
/// <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");
// 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.IndexOf(value);
}
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);
}
/// <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;
}