/// <include file='docs/MyCatConnection.xml' path='docs/BeginTransaction1/*'/> public new MyCatTransaction 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)); } MyCatTransaction t = new MyCatTransaction(this, iso); MyCatCommand cmd = new MyCatCommand("", 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(); MyCatCommand cmd = new MyCatCommand(sql, Connection); cmd.CommandTimeout = Timeout; return(cmd.ExecuteNonQuery()); } finally { if (openedConnection) { connection.Close(); } } }
/// <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; MyCatCommand command = new MyCatCommand("SET SQL_SELECT_LIMIT=DEFAULT", connection); command.internallyCreated = true; command.ExecuteNonQuery(); } }
/// <include file='docs/MyCatTransaction.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"); } MyCatCommand cmd = new MyCatCommand("COMMIT", conn); cmd.ExecuteNonQuery(); open = false; }
/// <include file='docs/MyCatTransaction.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"); } MyCatCommand cmd = new MyCatCommand("ROLLBACK", conn); cmd.ExecuteNonQuery(); open = false; }
public void CancelQuery(int timeout) { MyCatConnectionStringBuilder cb = new MyCatConnectionStringBuilder( Settings.ConnectionString); cb.Pooling = false; cb.AutoEnlist = false; cb.ConnectionTimeout = (uint)timeout; using (MyCatConnection c = new MyCatConnection(cb.ConnectionString)) { c.isKillQueryConnection = true; c.Open(); string commandText = "KILL QUERY " + ServerThread; MyCatCommand cmd = new MyCatCommand(commandText, c); cmd.CommandTimeout = timeout; cmd.ExecuteNonQuery(); } }
private string SetUserVariables(MyCatParameterCollection parms, bool preparing) { StringBuilder setSql = new StringBuilder(); if (serverProvidingOutputParameters) { return(setSql.ToString()); } string delimiter = String.Empty; foreach (MyCatParameter 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 { MyCatCommand cmd = new MyCatCommand(sql, command.Connection); cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); } } if (setSql.Length > 0) { setSql.Append("; "); } return(setSql.ToString()); }
protected override int ExecuteBatch() { int recordsAffected = 0; int index = 0; while (index < commandBatch.Count) { MyCatCommand cmd = (MyCatCommand)commandBatch[index++]; for (int index2 = index; index2 < commandBatch.Count; index2++, index++) { MyCatCommand cmd2 = (MyCatCommand)commandBatch[index2]; if (cmd2.BatchableCommandText == null || cmd2.CommandText != cmd.CommandText) { break; } cmd.AddToBatch(cmd2); } recordsAffected += cmd.ExecuteNonQuery(); } return(recordsAffected); }
/// <summary> /// Executes a single command against a MySQL database. The <see cref="MyCatConnection"/> is assumed to be /// open when the method is called and remains open after the method completes. /// </summary> /// <param name="connection"><see cref="MyCatConnection"/> object to use</param> /// <param name="commandText">SQL command to be executed</param> /// <param name="commandParameters">Array of <see cref="MyCatParameter"/> objects to use with the command.</param> /// <returns></returns> public static int ExecuteNonQuery(MyCatConnection connection, string commandText, params MyCatParameter[] commandParameters) { //create a command and prepare it for execution MyCatCommand cmd = new MyCatCommand(); cmd.Connection = connection; cmd.CommandText = commandText; cmd.CommandType = CommandType.Text; if (commandParameters != null) { foreach (MyCatParameter p in commandParameters) { cmd.Parameters.Add(p); } } int result = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return(result); }
public virtual void Configure(MyCatConnection 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 (MyCatException 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 MyCatException("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 MyCatCommand charSetCmd = new MyCatCommand("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)) { MyCatCommand setNamesCmd = new MyCatCommand("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(); }
/// <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; MyCatCommand cmd = new MyCatCommand(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(); } } }