/// <include file='docs/mysqlcommand.xml' path='docs/ctor1/*'/>
public MySqlCommand()
{
designTimeVisible = true;
cmdType = CommandType.Text;
parameters = new MySqlParameterCollection(this);
updatedRowSource = UpdateRowSource.Both;
cmdText = String.Empty;
useDefaultTimeout = true;
}
private void InternalBindParameters(string sql, MySqlParameterCollection parameters,
MySqlPacket packet)
{
bool sqlServerMode = command.Connection.Settings.SqlServerMode;
if (packet == null)
{
packet = new MySqlPacket(Driver.Encoding);
packet.Version = Driver.Version;
packet.WriteByte(0);
}
MySqlTokenizer tokenizer = new MySqlTokenizer(sql);
tokenizer.ReturnComments = true;
tokenizer.SqlServerMode = sqlServerMode;
int pos = 0;
string token = tokenizer.NextToken();
while (token != null)
{
// serialize everything that came before the token (i.e. whitespace)
packet.WriteStringNoNull(sql.Substring(pos, tokenizer.StartIndex - pos));
pos = tokenizer.StopIndex;
if (MySqlTokenizer.IsParameter(token))
{
if (SerializeParameter(parameters, packet, token))
{
token = null;
}
}
if (token != null)
{
if (sqlServerMode && tokenizer.Quoted && token.StartsWith("["))
{
token = String.Format("`{0}`", token.Substring(1, token.Length - 2));
}
packet.WriteStringNoNull(token);
}
token = tokenizer.NextToken();
}
buffers.Add(packet);
}
/// <summary>
/// Serializes the given parameter to the given memory stream
/// </summary>
/// <remarks>
/// <para>This method is called by PrepareSqlBuffers to convert the given
/// parameter to bytes and write those bytes to the given memory stream.
/// </para>
/// </remarks>
/// <returns>True if the parameter was successfully serialized, false otherwise.</returns>
private bool SerializeParameter(MySqlParameterCollection parameters,
MySqlPacket packet, string parmName)
{
MySqlParameter parameter = parameters.GetParameterFlexible(parmName, false);
if (parameter == null)
{
// if we are allowing user variables and the parameter name starts with @
// then we can't throw an exception
if (parmName.StartsWith("@") && ShouldIgnoreMissingParameter(parmName))
{
return(false);
}
throw new MySqlException(
String.Format(ResourceStrings.ParameterMustBeDefined, parmName));
}
parameter.Serialize(packet, false, Connection.Settings);
return(true);
}
private MySqlParameterCollection CheckParameters(string spName)
{
MySqlParameterCollection newParms = new MySqlParameterCollection(command);
MySqlParameter returnParameter = GetReturnParameter();
DataTable procTable;
GetParameters(spName, out procTable, out parametersTable);
if (procTable.Rows.Count == 0)
{
throw new InvalidOperationException(String.Format(Resources.RoutineNotFound, spName));
}
bool realAsFloat = procTable.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
foreach (DataRow param in parametersTable.Rows)
{
newParms.Add(GetAndFixParameter(spName, param, realAsFloat, returnParameter));
}
return(newParms);
}
private string SetUserVariables(MySqlParameterCollection parms, bool preparing)
{
StringBuilder setSql = new StringBuilder();
if (serverProvidingOutputParameters)
{
return(setSql.ToString());
}
string delimiter = String.Empty;
foreach (MySqlParameter p in parms)
{
if (p.Direction != ParameterDirection.InputOutput)
{
continue;
}
string pName = "@" + p.BaseName;
string uName = "@" + ParameterPrefix + p.BaseName;
string sql = String.Format("SET {0}={1}", uName, pName);
if (command.Connection.Settings.AllowBatch && !preparing)
{
setSql.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", delimiter, sql);
delimiter = "; ";
}
else
{
MySqlCommand cmd = new MySqlCommand(sql, command.Connection);
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
}
}
if (setSql.Length > 0)
{
setSql.Append("; ");
}
return(setSql.ToString());
}
private string CreateOutputSelect(MySqlParameterCollection parms, bool preparing)
{
StringBuilder outSql = new StringBuilder();
string delimiter = String.Empty;
foreach (MySqlParameter p in parms)
{
if (p.Direction == ParameterDirection.Input)
{
continue;
}
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Output) &&
serverProvidingOutputParameters)
{
continue;
}
string pName = "@" + p.BaseName;
string uName = "@" + ParameterPrefix + p.BaseName;
outSql.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", delimiter, uName);
delimiter = ", ";
}
if (outSql.Length == 0)
{
return(String.Empty);
}
if (command.Connection.Settings.AllowBatch && !preparing)
{
return(String.Format(";SELECT {0}", outSql.ToString()));
}
outSelect = String.Format("SELECT {0}", outSql.ToString());
return(String.Empty);
}
/// <summary>
/// Serializes the given parameter to the given memory stream
/// </summary>
/// <remarks>
/// <para>This method is called by PrepareSqlBuffers to convert the given
/// parameter to bytes and write those bytes to the given memory stream.
/// </para>
/// </remarks>
/// <returns>True if the parameter was successfully serialized, false otherwise.</returns>
private bool SerializeParameter(MySqlParameterCollection parameters,
MySqlPacket packet, string parmName)
{
MySqlParameter parameter = parameters.GetParameterFlexible(parmName, false);
if (parameter == null)
{
// if we are allowing user variables and the parameter name starts with @
// then we can't throw an exception
if (parmName.StartsWith("@") && ShouldIgnoreMissingParameter(parmName))
return false;
throw new MySqlException(
String.Format(Resources.ParameterMustBeDefined, parmName));
}
parameter.Serialize(packet, false, Connection.Settings);
return true;
}
private void InternalBindParameters(string sql, MySqlParameterCollection parameters,
MySqlPacket packet)
{
bool sqlServerMode = command.Connection.Settings.SqlServerMode;
if (packet == null)
{
packet = new MySqlPacket(Driver.Encoding);
packet.Version = Driver.Version;
packet.WriteByte(0);
}
MySqlTokenizer tokenizer = new MySqlTokenizer(sql);
tokenizer.ReturnComments = true;
tokenizer.SqlServerMode = sqlServerMode;
int pos = 0;
string token = tokenizer.NextToken();
while (token != null)
{
// serialize everything that came before the token (i.e. whitespace)
packet.WriteStringNoNull(sql.Substring(pos, tokenizer.StartIndex - pos));
pos = tokenizer.StopIndex;
if (MySqlTokenizer.IsParameter(token))
{
if (SerializeParameter(parameters, packet, token))
token = null;
}
if (token != null)
{
if (sqlServerMode && tokenizer.Quoted && token.StartsWith("["))
token = String.Format("`{0}`", token.Substring(1, token.Length - 2));
packet.WriteStringNoNull(token);
}
token = tokenizer.NextToken();
}
buffers.Add(packet);
}
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 string SetUserVariables(MySqlParameterCollection parms, bool preparing)
{
StringBuilder setSql = new StringBuilder();
if (serverProvidingOutputParameters) return setSql.ToString();
string delimiter = String.Empty;
foreach (MySqlParameter p in parms)
{
if (p.Direction != ParameterDirection.InputOutput) continue;
string pName = "@" + p.BaseName;
string uName = "@" + ParameterPrefix + p.BaseName;
string sql = String.Format("SET {0}={1}", uName, pName);
if (command.Connection.Settings.AllowBatch && !preparing)
{
setSql.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", delimiter, sql);
delimiter = "; ";
}
else
{
MySqlCommand cmd = new MySqlCommand(sql, command.Connection);
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
}
}
if (setSql.Length > 0)
setSql.Append("; ");
return setSql.ToString();
}
private string CreateOutputSelect(MySqlParameterCollection parms, bool preparing)
{
StringBuilder outSql = new StringBuilder();
string delimiter = String.Empty;
foreach (MySqlParameter p in parms)
{
if (p.Direction == ParameterDirection.Input) continue;
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Output) &&
serverProvidingOutputParameters)
continue;
string pName = "@" + p.BaseName;
string uName = "@" + ParameterPrefix + p.BaseName;
outSql.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", delimiter, uName);
delimiter = ", ";
}
if (outSql.Length == 0) return String.Empty;
if (command.Connection.Settings.AllowBatch && !preparing)
return String.Format(";SELECT {0}", outSql.ToString());
outSelect = String.Format("SELECT {0}", outSql.ToString());
return String.Empty;
}
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 MySqlParameterCollection CheckParameters(string spName)
{
MySqlParameterCollection newParms = new MySqlParameterCollection(command);
MySqlParameter returnParameter = GetReturnParameter();
DataTable procTable;
GetParameters(spName, out procTable, out parametersTable);
if (procTable.Rows.Count == 0)
throw new InvalidOperationException(String.Format(Resources.RoutineNotFound, spName));
bool realAsFloat = procTable.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
foreach (DataRow param in parametersTable.Rows)
newParms.Add(GetAndFixParameter(spName, param, realAsFloat, returnParameter));
return newParms;
}