public MySqlCommand(string commandText, MySqlConnection connection, MySqlTransaction transaction)
{
CommandText = commandText;
DbConnection = connection;
DbTransaction = transaction;
m_parameterCollection = new MySqlParameterCollection();
}
public Boolean TransaccionMysql(String query, MySqlParameterCollection parametros, System.Data.CommandType tipo_comando)
{
Boolean respuesta = false;
int f;
try{
transaccion = canal.BeginTransaction();
MySqlCommand comando = new MySqlCommand(query, canal);
comando.CommandType = tipo_comando;
if (parametros != null) {
foreach (var item in parametros){
comando.Parameters.Add(item);
}
}
comando.Transaction = transaccion;
f= comando.ExecuteNonQuery();
transaccion.Commit();
if (f>=0){
respuesta = true;
}
else{
respuesta = false;
}
}catch (Exception ex){
transaccion.Rollback();
MessageBox.Show("error sql:" + ex.Message);
respuesta = false;
}
return respuesta;
}
public System.Data.DataTable ConsultaMySQL(String query,MySqlParameterCollection parametros,System.Data.CommandType tipo_comando)
{
System.Data.DataTable tabla = new System.Data.DataTable();
try {
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand comando = new MySqlCommand(query, canal);
comando.CommandType = tipo_comando;
if (parametros != null)
{
foreach (var item in parametros)
{
comando.Parameters.Add(item);
}
}
adapter.SelectCommand = comando;
adapter.Fill(tabla);
if (tabla.Rows.Count > 0)
{
return tabla;
}
else {
return null;
}
}
catch (Exception er) { MessageBox.Show("error: " + er.Message); return null; }
}
public MySqlStatementPreparer(string commandText, MySqlParameterCollection parameters, StatementPreparerOptions options)
{
m_commandText = commandText;
m_parameters = parameters;
m_options = options;
m_hasBoundParameters = string.IsNullOrWhiteSpace(m_commandText);
}
/// <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;
}
/// <include file='docs/mysqlcommand.xml' path='docs/ctor1/*'/>
public MySqlCommand()
{
cmdType = CommandType.Text;
parameters = new MySqlParameterCollection(this);
cmdText = String.Empty;
useDefaultTimeout = true;
Constructor();
}
public CommandResult Execute(MySqlParameterCollection parameters)
{
PacketWriter packetWriter = new PacketWriter();
packetWriter.Driver = (NativeDriver)this.driver;
BitArray bitArray = new BitArray(parameters.Count);
for (int i = 0; i < this.paramList.Length; i++)
{
MySqlParameter mySqlParameter = parameters[this.paramList[i].ColumnName];
if (mySqlParameter.Value == DBNull.Value || mySqlParameter.Value == null)
{
bitArray[i] = true;
}
}
byte[] array = new byte[(parameters.Count + 7) / 8];
if (array.Length > 0)
{
bitArray.CopyTo(array, 0);
}
packetWriter.WriteInteger((long)this.StatementId, 4);
packetWriter.WriteByte(0);
packetWriter.WriteInteger(1L, 4);
packetWriter.Write(array);
packetWriter.WriteByte(1);
MySqlField[] array2 = this.paramList;
for (int j = 0; j < array2.Length; j++)
{
MySqlField mySqlField = array2[j];
MySqlParameter mySqlParameter2 = parameters[mySqlField.ColumnName];
packetWriter.WriteInteger((long)mySqlParameter2.GetPSType(), 2);
}
array2 = this.paramList;
for (int j = 0; j < array2.Length; j++)
{
MySqlField mySqlField2 = array2[j];
int num = parameters.IndexOf(mySqlField2.ColumnName);
if (num == -1)
{
throw new MySqlException("Parameter '" + mySqlField2.ColumnName + "' is not defined.");
}
MySqlParameter mySqlParameter3 = parameters[num];
if (mySqlParameter3.Value != DBNull.Value && mySqlParameter3.Value != null)
{
packetWriter.Encoding = mySqlField2.Encoding;
mySqlParameter3.Serialize(packetWriter, true);
}
}
this.executionCount++;
return(this.driver.ExecuteStatement(((MemoryStream)packetWriter.Stream).ToArray()));
}
internal MySqlParameterCollection?CloneRawParameters()
{
if (m_parameterCollection is null)
{
return(null);
}
var parameters = new MySqlParameterCollection();
foreach (var parameter in (IEnumerable <MySqlParameter>)m_parameterCollection)
{
parameters.Add(parameter.Clone());
}
return(parameters);
}
/// <include file='docs/mysqlcommand.xml' path='docs/ctor1/*'/>
public MySqlCommand()
{
designTimeVisible = true;
cmdType = CommandType.Text;
parameterMap = new ArrayList();
parameters = new MySqlParameterCollection();
updatedRowSource = UpdateRowSource.Both;
cursorPageSize = 0;
cmdText = String.Empty;
commandTimeout = 30;
canCancel = false;
timedOut = false;
querySent = new AutoResetEvent(false);
}
internal MySqlParameterCollection CloneRawParameters()
{
if (m_parameterCollection is null)
{
return(null);
}
var parameters = new MySqlParameterCollection();
foreach (MySqlParameter parameter in m_parameterCollection)
{
parameters.Add(parameter.Clone());
}
return(parameters);
}
public static string KeyToWhere(IDataStoreKey key, MySqlParameterCollection parameters)
{
string where = null;
if (key is CounterDataStoreKey)
{
where = "T1.COUNTER = ?CTR";
var par = new MySqlParameter();
par.ParameterName = "?CTR";
par.Value = ((CounterDataStoreKey)key).Counter;
parameters.Add(par);
}
else
if (key is GDID)
{
where = "T1.GDID = ?CTR";
var par = new MySqlParameter();
par.ParameterName = "?CTR";
par.Value = key;
parameters.Add(par);
}
else
if (key is NameValueDataStoreKey)
{
var dict = key as NameValueDataStoreKey;
var s = new StringBuilder();
var idx = 0;
foreach (var e in dict)
{
s.AppendFormat(" (T1.`{0}` = ?P{1}) AND", e.Key, idx);
var par = new MySqlParameter();
par.ParameterName = "?P" + idx.ToString();
par.Value = e.Value;
parameters.Add(par);
idx++;
}
if (s.Length > 0) s.Remove(s.Length - 3, 3);//cut "AND"
where = s.ToString();
}
else
throw new MySQLDataAccessException(StringConsts.INVALID_KEY_TYPE_ERROR);
return where;
}
protected virtual void BindParameters()
{
MySqlParameterCollection parameters = command.Parameters;
int index = 0;
while (true)
{
InternalBindParameters(ResolvedCommandText, parameters, null);
// if we are not batching, then we are done. This is only really relevant the
// first time through
if (command.Batch == null) return;
while (index < command.Batch.Count)
{
MySqlCommand batchedCmd = command.Batch[index++];
MySqlPacket packet = (MySqlPacket)_buffers[_buffers.Count - 1];
// now we make a guess if this statement will fit in our current stream
long estimatedCmdSize = batchedCmd.EstimatedSize();
if (((packet.Length - 4) + estimatedCmdSize) > Connection.driver.MaxPacketSize)
{
// it won't, so we setup to start a new run from here
parameters = batchedCmd.Parameters;
break;
}
// looks like we might have room for it so we remember the current end of the stream
_buffers.RemoveAt(_buffers.Count - 1);
//long originalLength = packet.Length - 4;
// and attempt to stream the next command
string text = ResolvedCommandText;
if (text.StartsWith("(", StringComparison.Ordinal))
packet.WriteStringNoNull(", ");
else
packet.WriteStringNoNull("; ");
InternalBindParameters(text, batchedCmd.Parameters, packet);
if ((packet.Length - 4) > Connection.driver.MaxPacketSize)
{
//TODO
//stream.InternalBuffer.SetLength(originalLength);
parameters = batchedCmd.Parameters;
break;
}
}
if (index == command.Batch.Count)
return;
}
}
private MySqlParameter GetParameter(MySqlParameterCollection parameters, string name)
{
int num = parameters.IndexOf(name);
if (num == -1)
{
name = name.Substring(1);
num = this.Parameters.IndexOf(name);
if (num == -1)
{
return(null);
}
}
return(parameters[num]);
}
/// <summary>
/// We use a separate method here because we want to support using parameter
/// names with and without a leading marker but we don't want the indexing
/// methods of MySqlParameterCollection to support that.
/// </summary>
/// <param name="parameters"></param>
/// <param name="name"></param>
/// <returns></returns>
private MySqlParameter GetParameter(MySqlParameterCollection parameters, string name)
{
int index = parameters.IndexOf(name);
if (index == -1)
{
name = name.Substring(1);
index = Parameters.IndexOf(name);
if (index == -1)
{
return(null);
}
}
return(parameters[index]);
}
/// <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,
MySqlStream stream, 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(stream, false);
return true;
}
/// <summary>
/// Add range with value
/// </summary>
/// <param name="coll"></param>
/// <param name="values"></param>
public static void AddRangeWithValue(this MySqlParameterCollection coll, Dictionary <string, object> values)
{
coll.CheckNull(nameof(coll));
#if NETFRAMEWORK || NETSTANDARD2_0
foreach (var pair in values)
{
var key = pair.Key;
var value = pair.Value;
#else
foreach (var(key, value) in values)
{
#endif
coll.AddWithValue(key, value);
}
}
}
private void InternalBindParameters(string sql, MySqlParameterCollection parameters, MySqlPacket packet)
{
bool sqlServerMode = this.command.Connection.Settings.SqlServerMode;
if (packet == null)
{
packet = new MySqlPacket(this.Driver.Encoding);
packet.Version = this.Driver.Version;
packet.WriteByte(0);
}
MySqlTokenizer mySqlTokenizer = new MySqlTokenizer(sql);
mySqlTokenizer.ReturnComments = true;
mySqlTokenizer.SqlServerMode = sqlServerMode;
int num = 0;
string text = mySqlTokenizer.NextToken();
int num2 = 0;
while (text != null)
{
packet.WriteStringNoNull(sql.Substring(num, mySqlTokenizer.StartIndex - num));
num = mySqlTokenizer.StopIndex;
if (MySqlTokenizer.IsParameter(text))
{
if ((!parameters.containsUnnamedParameters && text.Length == 1 && num2 > 0) || (parameters.containsUnnamedParameters && text.Length > 1))
{
throw new MySqlException(Resources.MixedParameterNamingNotAllowed);
}
parameters.containsUnnamedParameters = (text.Length == 1);
if (this.SerializeParameter(parameters, packet, text, num2))
{
text = null;
}
num2++;
}
if (text != null)
{
if (sqlServerMode && mySqlTokenizer.Quoted && text.StartsWith("[", StringComparison.Ordinal))
{
text = string.Format("`{0}`", text.Substring(1, text.Length - 2));
}
packet.WriteStringNoNull(text);
}
text = mySqlTokenizer.NextToken();
}
this.buffers.Add(packet);
}
private void InternalBindParameters(string sql, MySqlParameterCollection parameters, MySqlPacket packet)
{
bool sqlServerMode = command.Connection.Settings.SqlServerMode;
if (packet == null)
{
packet = new MySqlPacket(Driver.Encoding) {Version = Driver.Version};
packet.WriteByte(0);
}
MySqlTokenizer tokenizer = new MySqlTokenizer(sql)
{
ReturnComments = true,
SqlServerMode = sqlServerMode
};
int pos = 0;
string token = tokenizer.NextToken();
int parameterCount = 0;
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 ((!parameters.containsUnnamedParameters && token.Length == 1 && parameterCount > 0) || parameters.containsUnnamedParameters && token.Length > 1)
throw new MySqlException(Resources.MixedParameterNamingNotAllowed);
parameters.containsUnnamedParameters = token.Length == 1;
if (SerializeParameter(parameters, packet, token, parameterCount))
token = null;
parameterCount++;
}
if (token != null)
{
if (sqlServerMode && tokenizer.Quoted && token.StartsWith("[", StringComparison.Ordinal))
token = String.Format("`{0}`", token.Substring(1, token.Length - 2));
packet.WriteStringNoNull(token);
}
token = tokenizer.NextToken();
}
_buffers.Add(packet);
}
protected virtual void BindParameters()
{
MySqlParameterCollection parameters = this.command.Parameters;
int i = 0;
while (true)
{
this.InternalBindParameters(this.ResolvedCommandText, parameters, null);
if (this.command.Batch == null)
{
break;
}
while (i < this.command.Batch.Count)
{
MySqlCommand mySqlCommand = this.command.Batch[i++];
MySqlPacket mySqlPacket = this.buffers[this.buffers.Count - 1];
long num = mySqlCommand.EstimatedSize();
if ((long)(mySqlPacket.Length - 4) + num > this.Connection.driver.MaxPacketSize)
{
parameters = mySqlCommand.Parameters;
break;
}
this.buffers.RemoveAt(this.buffers.Count - 1);
string resolvedCommandText = this.ResolvedCommandText;
if (resolvedCommandText.StartsWith("(", StringComparison.Ordinal))
{
mySqlPacket.WriteStringNoNull(", ");
}
else
{
mySqlPacket.WriteStringNoNull("; ");
}
this.InternalBindParameters(resolvedCommandText, mySqlCommand.Parameters, mySqlPacket);
if ((long)(mySqlPacket.Length - 4) > this.Connection.driver.MaxPacketSize)
{
parameters = mySqlCommand.Parameters;
break;
}
}
if (i == this.command.Batch.Count)
{
return;
}
}
}
private void InternalBindParameters(string sql, MySqlParameterCollection parameters, MySqlStream stream)
{
// tokenize the sql
ArrayList tokenArray = TokenizeSql(sql);
if (stream == null)
{
stream = new MySqlStream(Driver.Encoding);
stream.Version = Driver.Version;
}
// make sure our token array ends with a ;
string lastToken = (string)tokenArray[tokenArray.Count - 1];
if (lastToken != ";")
{
tokenArray.Add(";");
}
foreach (String token in tokenArray)
{
if (token.Trim().Length == 0)
{
continue;
}
if (token == ";")
{
buffers.Add(stream);
stream = new MySqlStream(Driver.Encoding);
continue;
}
if (token.Length >= 2 &&
((token[0] == '@' && token[1] != '@') ||
token[0] == '?'))
{
if (SerializeParameter(parameters, stream, token))
{
continue;
}
}
// our fall through case is to write the token to the byte stream
stream.WriteStringNoNull(token);
}
}
private MySqlParameterCollection CheckParameters(string spName)
{
MySqlParameterCollection mySqlParameterCollection = new MySqlParameterCollection(this.command);
MySqlParameter returnParameter = this.GetReturnParameter();
ProcedureCacheEntry parameters = this.GetParameters(spName);
if (parameters.procedure == null || parameters.procedure.Rows.Count == 0)
{
throw new InvalidOperationException(string.Format(Resources.RoutineNotFound, spName));
}
bool realAsFloat = parameters.procedure.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
foreach (MySqlSchemaRow current in parameters.parameters.Rows)
{
mySqlParameterCollection.Add(this.GetAndFixParameter(spName, current, realAsFloat, returnParameter));
}
return(mySqlParameterCollection);
}
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 SetUserVariables(MySqlParameterCollection parms, bool preparing)
{
StringBuilder stringBuilder = new StringBuilder();
if (this.serverProvidingOutputParameters)
{
return(stringBuilder.ToString());
}
string text = string.Empty;
foreach (MySqlParameter mySqlParameter in parms)
{
if (mySqlParameter.Direction == ParameterDirection.InputOutput)
{
string arg = "@" + mySqlParameter.BaseName;
string arg2 = "@_cnet_param_" + mySqlParameter.BaseName;
string text2 = string.Format("SET {0}={1}", arg2, arg);
if (this.command.Connection.Settings.AllowBatch && !preparing)
{
stringBuilder.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", new object[]
{
text,
text2
});
text = "; ";
}
else
{
new MySqlCommand(text2, this.command.Connection)
{
Parameters =
{
mySqlParameter
}
}.ExecuteNonQuery();
}
}
}
if (stringBuilder.Length > 0)
{
stringBuilder.Append("; ");
}
return(stringBuilder.ToString());
}
/// <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("@", StringComparison.Ordinal) && ShouldIgnoreMissingParameter(parmName))
{
return(false);
}
throw new MySqlException(
String.Format(Resources.ParameterMustBeDefined, parmName));
}
parameter.Serialize(packet, false, Connection.Settings);
return(true);
}
private MySqlParameterCollection CheckParameters(string spName)
{
MySqlParameterCollection newParms = new MySqlParameterCollection(command);
MySqlParameter returnParameter = GetReturnParameter();
ProcedureCacheEntry entry = GetParameters(spName);
if (entry.procedure == null || entry.procedure.Rows.Count == 0)
{
throw new InvalidOperationException(String.Format(Resources.RoutineNotFound, spName));
}
bool realAsFloat = entry.procedure.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
foreach (MySqlSchemaRow param in entry.parameters.Rows)
{
newParms.Add(GetAndFixParameter(spName, param, realAsFloat, returnParameter));
}
return(newParms);
}
/// <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,
MySqlStream stream, string parmName)
{
MySqlParameter parameter = GetParameter(parameters, parmName);
if (parameter == null)
{
// if we are using old syntax, we can't throw exceptions for parameters
// not defined.
if (Connection.Settings.UseOldSyntax)
{
return(false);
}
throw new MySqlException(
String.Format(Resources.ParameterMustBeDefined, parmName));
}
parameter.Serialize(stream, false);
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());
}
public override void Resolve(bool preparing)
{
if (this.resolvedCommandText != null)
{
return;
}
this.serverProvidingOutputParameters = (base.Driver.SupportsOutputParameters && preparing);
string text = this.commandText;
if (text.IndexOf(".") == -1 && !string.IsNullOrEmpty(base.Connection.Database))
{
text = base.Connection.Database + "." + text;
}
text = this.FixProcedureName(text);
MySqlParameter returnParameter = this.GetReturnParameter();
MySqlParameterCollection parms = this.command.Connection.Settings.CheckParameters ? this.CheckParameters(text) : base.Parameters;
string arg = this.SetUserVariables(parms, preparing);
string arg2 = this.CreateCallStatement(text, returnParameter, parms);
string arg3 = this.CreateOutputSelect(parms, preparing);
this.resolvedCommandText = string.Format("{0}{1}{2}", arg, arg2, arg3);
}
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;
}
public ParameterCollection()
{
m_command = new MySqlCommand();
m_parameterCollection = m_command.Parameters;
}
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 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);
}
internal static void ConvertParameters(MySQLDataStoreBase store, MySqlParameterCollection pars)
{
if (pars==null) return;
for(var i=0; i<pars.Count; i++)
{
var par = pars[i];
MySqlDbType? convertedDbType;
par.Value = CLRValueToDB(store, par.Value, out convertedDbType);
if (convertedDbType.HasValue)
par.MySqlDbType = convertedDbType.Value;
}
}
internal static void ConvertParameters(MySQLDataStoreBase store, MySqlParameterCollection pars)
{
if (pars==null) return;
for(var i=0; i<pars.Count; i++)
{
var par = pars[i];
par.Value = CLRValueToDB(store, par.Value);
}
}
private static void Prepare(MySqlParameterCollection parameters)
{
foreach (MySqlParameter paramenter in parameters)
{
if (paramenter.Value == null)
paramenter.Value = DBNull.Value;
}
}
/// <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, int parameterIndex)
{
MySqlParameter parameter = null;
if (!parameters.containsUnnamedParameters)
parameter = parameters.GetParameterFlexible(parmName, false);
else
{
if (parameterIndex <= parameters.Count)
parameter = parameters[parameterIndex];
else
throw new MySqlException("Resources.ParameterIndexNotFound");
}
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("@", StringComparison.Ordinal) && 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();
int parameterCount = 0;
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 ((!parameters.containsUnnamedParameters && token.Length == 1 && parameterCount > 0) || parameters.containsUnnamedParameters && token.Length > 1)
throw new MySqlException("Resources.MixedParameterNamingNotAllowed");
parameters.containsUnnamedParameters = token.Length == 1;
if (SerializeParameter(parameters, packet, token, parameterCount))
token = null;
parameterCount++;
}
if (token != null)
{
if (sqlServerMode && tokenizer.Quoted && token.StartsWith("[", StringComparison.Ordinal))
token = String.Format("`{0}`", token.Substring(1, token.Length - 2));
packet.WriteStringNoNull(token);
}
token = tokenizer.NextToken();
}
buffers.Add(packet);
}
private MySqlParameterCollection CheckParameters(string spName)
{
MySqlParameterCollection newParms = new MySqlParameterCollection(command);
MySqlParameter returnParameter = GetReturnParameter();
ProcedureCacheEntry entry = GetParameters(spName);
if (entry.procedure == null || entry.procedure.Rows.Count == 0)
throw new InvalidOperationException(String.Format(Resources.RoutineNotFound, spName));
bool realAsFloat = entry.procedure.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
foreach (MySqlSchemaRow param in entry.parameters.Rows)
newParms.Add(GetAndFixParameter(spName, param, realAsFloat, returnParameter));
return newParms;
}
public MySqlDataReader ExecuteSPReader(string procedureName)
{
MySqlDataReader reader;
MySqlCommand cmd = new MySqlCommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
CommandBehavior behavior = CommandBehavior.Default;
if (this.AutoCloseConnection) behavior = behavior | CommandBehavior.CloseConnection;
if (_isSingleRow) behavior = behavior | CommandBehavior.SingleRow;
reader = cmd.ExecuteReader(behavior);
_parameterCollection = cmd.Parameters;
cmd.Dispose();
return reader;
}
/// <summary>
/// Based on store settings, converts query parameters into MySQL-acceptable values, i.e. GDID -> BYTe[].
/// This function is not idempotent
/// </summary>
public void ConvertParameters(MySqlParameterCollection pars)
{
CRUDGenerator.ConvertParameters(DataStore, pars);
}
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;
}
public MySqlStatementPreparer(string commandText, MySqlParameterCollection parameters, StatementPreparerOptions options)
{
m_commandText = commandText;
m_parameters = parameters;
m_options = options;
}
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();
}
/// <summary>
/// We use a separate method here because we want to support using parameter
/// names with and without a leading marker but we don't want the indexing
/// methods of MySqlParameterCollection to support that.
/// </summary>
/// <param name="parameters"></param>
/// <param name="name"></param>
/// <returns></returns>
private MySqlParameter GetParameter(MySqlParameterCollection parameters, string name)
{
int index = parameters.IndexOf(name);
if (index == -1)
{
name = name.Substring(1);
index = Parameters.IndexOf(name);
if (index == -1)
return null;
}
return parameters[index];
}
/// <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;
}
/// <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,
MySqlStream stream, string parmName)
{
MySqlParameter parameter = GetParameter(parameters, parmName);
if (parameter == null)
{
// if we are using old syntax, we can't throw exceptions for parameters
// not defined.
if (Connection.Settings.UseOldSyntax)
return false;
throw new MySqlException(
String.Format(Resources.ParameterMustBeDefined, parmName));
}
parameter.Serialize(stream, false);
return true;
}
private void InternalBindParameters(string sql, MySqlParameterCollection parameters,
MySqlPacket packet)
{
if (packet == null)
{
packet = new MySqlPacket(Driver.Encoding);
packet.Version = Driver.Version;
packet.WriteByte(0);
}
int startPos = 0;
MySqlTokenizer tokenizer = new MySqlTokenizer(sql);
tokenizer.ReturnComments = true;
string parameter = tokenizer.NextParameter();
while (parameter != null)
{
packet.WriteStringNoNull(sql.Substring(startPos, tokenizer.StartIndex - startPos));
bool serialized = SerializeParameter(parameters, packet, parameter);
startPos = tokenizer.StopIndex;
if (!serialized)
startPos = tokenizer.StartIndex;
parameter = tokenizer.NextParameter();
}
packet.WriteStringNoNull(sql.Substring(startPos));
buffers.Add(packet);
}
private void InternalBindParameters(string sql, MySqlParameterCollection parameters, MySqlStream stream)
{
// tokenize the sql
ArrayList tokenArray = TokenizeSql(sql);
if (stream == null)
{
stream = new MySqlStream(Driver.Encoding);
stream.Version = Driver.Version;
}
// make sure our token array ends with a ;
string lastToken = (string) tokenArray[tokenArray.Count - 1];
if (lastToken != ";")
tokenArray.Add(";");
foreach (String token in tokenArray)
{
if (token.Trim().Length == 0)
continue;
if (token == ";")
{
buffers.Add(stream);
stream = new MySqlStream(Driver.Encoding);
continue;
}
if (token.Length >= 2 &&
((token[0] == '@' && token[1] != '@') ||
token[0] == '?'))
{
if (SerializeParameter(parameters, stream, token))
continue;
}
// our fall through case is to write the token to the byte stream
stream.WriteStringNoNull(token);
}
}
public CommandResult Execute(MySqlParameterCollection parameters)
{
PacketWriter packet = new PacketWriter();
packet.Driver = (NativeDriver)driver;
//TODO: support long data here
// create our null bitmap
BitArray nullMap = new BitArray(parameters.Count); //metaData.Length );
for (int x = 0; x < parameters.Count; x++)
{
if (parameters[x].Value == DBNull.Value ||
parameters[x].Value == null)
{
nullMap[x] = true;
}
}
byte[] nullMapBytes = new byte[(parameters.Count + 7) / 8];
nullMap.CopyTo(nullMapBytes, 0);
// start constructing our packet
packet.WriteInteger(StatementId, 4);
packet.WriteByte(0); // flags; always 0 for 4.1
packet.WriteInteger(1, 4); // interation count; 1 for 4.1
packet.Write(nullMapBytes);
//if (parameters != null && parameters.Count > 0)
packet.WriteByte(1); // rebound flag
//else
// packet.WriteByte( 0 );
//TODO: only send rebound if parms change
// write out the parameter types
foreach (MySqlField param in paramList)
{
MySqlParameter parm = parameters[param.ColumnName];
packet.WriteInteger((long)parm.GetPSType(), 2);
}
// now write out all non-null values
foreach (MySqlField param in paramList)
{
int index = parameters.IndexOf(param.ColumnName);
if (index == -1)
{
throw new MySqlException("Parameter '" + param.ColumnName +
"' is not defined.");
}
MySqlParameter parm = parameters[index];
if (parm.Value == DBNull.Value || parm.Value == null)
{
continue;
}
packet.Encoding = param.Encoding;
parm.Serialize(packet, true);
}
executionCount++;
// send the data packet and return the CommandResult
return(driver.ExecuteStatement(((System.IO.MemoryStream)packet.Stream).ToArray()));
}
public void Reset()
{
if (_parameters != null)
{
_parameters.Clear();
}
if (_parameterCollection != null)
{
_parameterCollection = null;
}
}
private string CreateCallStatement(string spName, MySqlParameter returnParameter, MySqlParameterCollection parms)
{
StringBuilder stringBuilder = new StringBuilder();
string text = string.Empty;
foreach (MySqlParameter mySqlParameter in parms)
{
if (mySqlParameter.Direction != ParameterDirection.ReturnValue)
{
string text2 = "@" + mySqlParameter.BaseName;
string text3 = "@_cnet_param_" + mySqlParameter.BaseName;
bool flag = mySqlParameter.Direction == ParameterDirection.Input || this.serverProvidingOutputParameters;
stringBuilder.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", new object[]
{
text,
flag ? text2 : text3
});
text = ", ";
}
}
if (returnParameter == null)
{
return(string.Format("CALL {0}({1})", spName, stringBuilder.ToString()));
}
return(string.Format("SET @{0}{1}={2}({3})", new object[]
{
"_cnet_param_",
returnParameter.BaseName,
spName,
stringBuilder.ToString()
}));
}
public void ExecuteSP(string procedureName)
{
MySqlCommand cmd = new MySqlCommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
cmd.ExecuteNonQuery();
_parameterCollection = cmd.Parameters;
cmd.Dispose();
if (this.AutoCloseConnection) this.Disconnect();
}
public MysqlParaAdder(MySqlParameterCollection parameters)
{
_parameters = parameters;
}
public void ExecuteSPDataSet(ref DataSet dataSet, string procedureName, string tableName)
{
MySqlCommand cmd = new MySqlCommand();
this.Connect();
MySqlDataAdapter da = new MySqlDataAdapter();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
da.SelectCommand = cmd;
da.Fill(dataSet, tableName);
_parameterCollection = cmd.Parameters;
da.Dispose();
cmd.Dispose();
if (this.AutoCloseConnection) this.Disconnect();
}
