/// <summary> /// Executes a single command against a MySQL database. The <see cref="MySqlConnection"/> is assumed to be /// open when the method is called and remains open after the method completes. /// </summary> /// <param name="connection"><see cref="MySqlConnection"/> object to use</param> /// <param name="commandText">SQL command to be executed</param> /// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command.</param> /// <returns></returns> public static int ExecuteNonQuery(MySqlConnection connection, string commandText, params MySqlParameter[] commandParameters) { //create a command and prepare it for execution MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = commandText; cmd.CommandType = CommandType.Text; if (commandParameters != null) foreach (MySqlParameter p in commandParameters) cmd.Parameters.Add(p); int result = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return result; }
/// <summary> /// Reset SQL_SELECT_LIMIT that could have been modified by CommandBehavior. /// </summary> internal void ResetSqlSelectLimit() { // if we are supposed to reset the sql select limit, do that here if (resetSqlSelect) { resetSqlSelect = false; MySqlCommand command = new MySqlCommand("SET SQL_SELECT_LIMIT=DEFAULT", connection); command.internallyCreated = true; command.ExecuteNonQuery(); } }
public void CancelQuery(int timeout) { MySqlConnectionStringBuilder cb = new MySqlConnectionStringBuilder( Settings.ConnectionString); cb.Pooling = false; cb.AutoEnlist = false; cb.ConnectionTimeout = (uint)timeout; using (MySqlConnection c = new MySqlConnection(cb.ConnectionString)) { c.isKillQueryConnection = true; c.Open(); string commandText = "KILL QUERY " + ServerThread; MySqlCommand cmd = new MySqlCommand(commandText, c); cmd.CommandTimeout = timeout; cmd.ExecuteNonQuery(); } }
/// <include file='docs/MySqlConnection.xml' path='docs/BeginTransaction1/*'/> public new MySqlTransaction BeginTransaction(IsolationLevel iso) { //TODO: check note in help if (State != ConnectionState.Open) Throw(new InvalidOperationException(Resources.ConnectionNotOpen)); // First check to see if we are in a current transaction if (driver.HasStatus(ServerStatusFlags.InTransaction)) Throw(new InvalidOperationException(Resources.NoNestedTransactions)); MySqlTransaction t = new MySqlTransaction(this, iso); MySqlCommand cmd = new MySqlCommand("", this); cmd.CommandText = "SET SESSION TRANSACTION ISOLATION LEVEL "; switch (iso) { case IsolationLevel.ReadCommitted: cmd.CommandText += "READ COMMITTED"; break; case IsolationLevel.ReadUncommitted: cmd.CommandText += "READ UNCOMMITTED"; break; case IsolationLevel.RepeatableRead: cmd.CommandText += "REPEATABLE READ"; break; case IsolationLevel.Serializable: cmd.CommandText += "SERIALIZABLE"; break; case IsolationLevel.Chaos: Throw(new NotSupportedException(Resources.ChaosNotSupported)); break; case IsolationLevel.Snapshot: Throw(new NotSupportedException(Resources.SnapshotNotSupported)); break; } cmd.ExecuteNonQuery(); cmd.CommandText = "BEGIN"; cmd.ExecuteNonQuery(); return t; }
/// <summary> /// Execute the load operation /// </summary> /// <returns>The number of rows inserted.</returns> public int Load() { bool openedConnection = false; if (Connection == null) throw new InvalidOperationException(Resources.ConnectionNotSet); // next we open up the connetion if it is not already open if (connection.State != ConnectionState.Open) { openedConnection = true; connection.Open(); } try { string sql = BuildSqlCommand(); MySqlCommand cmd = new MySqlCommand(sql, Connection); cmd.CommandTimeout = Timeout; return cmd.ExecuteNonQuery(); } finally { if (openedConnection) connection.Close(); } }
public virtual void Configure(MySqlConnection connection) { bool firstConfigure = false; // if we have not already configured our server variables // then do so now if (serverProps == null) { firstConfigure = true; // if we are in a pool and the user has said it's ok to cache the // properties, then grab it from the pool try { if (Pool != null && Settings.CacheServerProperties) { if (Pool.ServerProperties == null) Pool.ServerProperties = LoadServerProperties(connection); serverProps = Pool.ServerProperties; } else serverProps = LoadServerProperties(connection); LoadCharacterSets(connection); } catch (MySqlException ex) { // expired password capability if (ex.Number == 1820) { IsPasswordExpired = true; return; } throw; } } #if AUTHENTICATED string licenseType = serverProps["license"]; if (licenseType == null || licenseType.Length == 0 || licenseType != "commercial") throw new MySqlException( "This client library licensed only for use with commercially-licensed MySQL servers." ); #endif // if the user has indicated that we are not to reset // the connection and this is not our first time through, // then we are done. if (!Settings.ConnectionReset && !firstConfigure) return; string charSet = connectionString.CharacterSet; if (charSet == null || charSet.Length == 0) { if (serverCharSetIndex >= 0 && charSets.ContainsKey(serverCharSetIndex)) charSet = (string)charSets[serverCharSetIndex]; else charSet = serverCharSet; } if (serverProps.ContainsKey("max_allowed_packet")) maxPacketSize = Convert.ToInt64(serverProps["max_allowed_packet"]); // now tell the server which character set we will send queries in and which charset we // want results in MySqlCommand charSetCmd = new MySqlCommand("SET character_set_results=NULL", connection); charSetCmd.InternallyCreated = true; string clientCharSet; serverProps.TryGetValue("character_set_client", out clientCharSet); string connCharSet; serverProps.TryGetValue("character_set_connection", out connCharSet); if ((clientCharSet != null && clientCharSet.ToString() != charSet) || (connCharSet != null && connCharSet.ToString() != charSet)) { MySqlCommand setNamesCmd = new MySqlCommand("SET NAMES " + charSet, connection); setNamesCmd.InternallyCreated = true; setNamesCmd.ExecuteNonQuery(); } charSetCmd.ExecuteNonQuery(); if (charSet != null) Encoding = CharSetMap.GetEncoding(Version, charSet); else Encoding = CharSetMap.GetEncoding(Version, "utf8"); handler.Configure(); }
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(); }
/// <include file='docs/MySqlTransaction.xml' path='docs/Rollback/*'/> public override void Rollback() { if (conn == null || (conn.State != ConnectionState.Open && !conn.SoftClosed)) throw new InvalidOperationException("Connection must be valid and open to rollback transaction"); if (!open) throw new InvalidOperationException("Transaction has already been rolled back or is not pending"); MySqlCommand cmd = new MySqlCommand("ROLLBACK", conn); cmd.ExecuteNonQuery(); open = false; }
/// <include file='docs/MySqlTransaction.xml' path='docs/Commit/*'/> public override void Commit() { if (conn == null || (conn.State != ConnectionState.Open && !conn.SoftClosed)) throw new InvalidOperationException("Connection must be valid and open to commit transaction"); if (!open) throw new InvalidOperationException("Transaction has already been committed or is not pending"); MySqlCommand cmd = new MySqlCommand("COMMIT", conn); cmd.ExecuteNonQuery(); open = false; }
/// <summary> /// Executes this instance. /// </summary> /// <returns>The number of statements executed as part of the script.</returns> public int Execute() { bool openedConnection = false; if (connection == null) throw new InvalidOperationException(Resources.ConnectionNotSet); if (query == null || query.Length == 0) return 0; // next we open up the connetion if it is not already open if (connection.State != ConnectionState.Open) { openedConnection = true; connection.Open(); } // since we don't allow setting of parameters on a script we can // therefore safely allow the use of user variables. no one should be using // this connection while we are using it so we can temporarily tell it // to allow the use of user variables bool allowUserVars = connection.Settings.AllowUserVariables; connection.Settings.AllowUserVariables = true; try { string mode = connection.driver.Property("sql_mode"); mode = StringUtility.ToUpperInvariant(mode); bool ansiQuotes = mode.IndexOf("ANSI_QUOTES") != -1; bool noBackslashEscapes = mode.IndexOf("NO_BACKSLASH_ESCAPES") != -1; // first we break the query up into smaller queries List<ScriptStatement> statements = BreakIntoStatements(ansiQuotes, noBackslashEscapes); int count = 0; MySqlCommand cmd = new MySqlCommand(null, connection); foreach (ScriptStatement statement in statements) { if (String.IsNullOrEmpty(statement.text)) continue; cmd.CommandText = statement.text; try { cmd.ExecuteNonQuery(); count++; OnQueryExecuted(statement); } catch (Exception ex) { if (Error == null) throw; if (!OnScriptError(ex)) break; } } OnScriptCompleted(); return count; } finally { connection.Settings.AllowUserVariables = allowUserVars; if (openedConnection) { connection.Close(); } } }