public DataSet Query(string sql, params MySQLParameter[] cmdParms) { DataSet ds = new DataSet(); MySQLCommand cmd = new MySQLCommand(); MyDbConnection3.PrepareCommand(cmd, this.DbConn, null, sql, cmdParms); DataSet result; using (MySQLDataAdapter da = new MySQLDataAdapter(cmd)) { try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); this.LogSql(sql); } catch (MySQLException ex) { throw new Exception(ex.Message); } finally { if (cmd != null) { cmd.Dispose(); } } result = ds; } return(result); }
public void ExecuteSqlTran(Hashtable SQLStringList) { using (DbTransaction trans = this.DbConn.BeginTransaction()) { using (MySQLCommand cmd = new MySQLCommand()) { try { foreach (object obj in SQLStringList) { DictionaryEntry myDE = (DictionaryEntry)obj; string cmdText = myDE.Key.ToString(); MySQLParameter[] cmdParms = (MySQLParameter[])myDE.Value; MyDbConnection3.PrepareCommand(cmd, this.DbConn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); this.LogSql(cmdText); } trans.Commit(); } catch { trans.Rollback(); throw; } } } }
public MySQLDataReader ExecuteReader(string sql, params MySQLParameter[] cmdParms) { try { if (this._MySQLDataReader != null && !this._MySQLDataReader.IsClosed) { this._MySQLDataReader.Close(); this._MySQLDataReader = null; } using (MySQLCommand cmd = new MySQLCommand(sql, this.DbConn)) { if (cmdParms.Length > 0) { MyDbConnection3.PrepareCommand(cmd, this.DbConn, null, sql, cmdParms); } MySQLDataReader myReader = cmd.ExecuteReaderEx(); if (cmdParms.Length > 0) { cmd.Parameters.Clear(); } this._MySQLDataReader = myReader; this.LogSql(sql); return(myReader); } } catch (Exception ex) { LogManager.WriteLog(LogTypes.Exception, string.Format("执行SQL异常: {0}\r\n{1}", sql, ex.ToString()), null, true); LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", sql), null, true); } return(null); }
public object GetSingle(string sql, int commandTimeout = 0, params MySQLParameter[] cmdParms) { try { using (MySQLCommand cmd = new MySQLCommand(sql, this.DbConn)) { if (commandTimeout > 0) { cmd.CommandTimeout = commandTimeout; } if (cmdParms.Length > 0) { MyDbConnection3.PrepareCommand(cmd, this.DbConn, null, sql, cmdParms); } object obj = cmd.ExecuteScalar(); if (cmdParms.Length > 0) { cmd.Parameters.Clear(); } this.LogSql(sql); if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { return(null); } return(obj); } } catch (Exception ex) { LogManager.WriteLog(LogTypes.Exception, string.Format("执行SQL异常: {0}\r\n{1}", sql, ex.ToString()), null, true); LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", sql), null, true); } return(null); }
public void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (DbTransaction trans = this.DbConn.BeginTransaction()) { using (MySQLCommand cmd = new MySQLCommand()) { try { int indentity = 0; foreach (object obj in SQLStringList) { DictionaryEntry myDE = (DictionaryEntry)obj; string cmdText = myDE.Key.ToString(); MySQLParameter[] cmdParms = (MySQLParameter[])myDE.Value; foreach (MySQLParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } MyDbConnection3.PrepareCommand(cmd, this.DbConn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (MySQLParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); this.LogSql(cmdText); } trans.Commit(); } catch { trans.Rollback(); throw; } } } }
public int ExecuteSql(string sql, params MySQLParameter[] cmdParms) { int result = 0; try { using (MySQLCommand cmd = new MySQLCommand(sql, this.DbConn)) { MyDbConnection3.PrepareCommand(cmd, this.DbConn, null, sql, cmdParms); result = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); this.LogSql(sql); } } catch (Exception ex) { LogManager.WriteLog(LogTypes.Exception, string.Format("执行SQL异常: {0}\r\n{1}", sql, ex.ToString()), null, true); LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", sql), null, true); result = -1; } return(result); }
public int ExecuteSqlTran(List <CommandInfo> cmdList) { int result; using (DbTransaction trans = this.DbConn.BeginTransaction()) { using (MySQLCommand cmd = new MySQLCommand()) { try { int count = 0; foreach (CommandInfo myDE in cmdList) { string cmdText = myDE.CommandText; MySQLParameter[] cmdParms = myDE.Parameters; MyDbConnection3.PrepareCommand(cmd, this.DbConn, trans, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { trans.Rollback(); return(0); } object obj = cmd.ExecuteScalar(); if (obj == null && obj == DBNull.Value) { } bool isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { trans.Rollback(); return(0); } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { trans.Rollback(); return(0); } } else { int val = cmd.ExecuteNonQuery(); count += val; if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { trans.Rollback(); return(0); } cmd.Parameters.Clear(); this.LogSql(cmdText); } } trans.Commit(); result = count; } catch { trans.Rollback(); throw; } } } return(result); }
public int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList) { MySQLConnection connection = this.DbConn; int result; using (MySQLCommand cmd = new MySQLCommand()) { cmd.Connection = connection; DbTransaction tx = connection.BeginTransaction(); cmd.Transaction = tx; try { foreach (CommandInfo myDE in list) { string cmdText = myDE.CommandText; MySQLParameter[] cmdParms = myDE.Parameters; MyDbConnection3.PrepareCommand(cmd, connection, tx, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.SolicitationEvent) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式"); } object obj = cmd.ExecuteScalar(); if (obj == null && obj == DBNull.Value) { } bool isHave = Convert.ToInt32(obj) > 0; if (isHave) { myDE.OnSolicitationEvent(); } } if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); } object obj = cmd.ExecuteScalar(); if (obj == null && obj == DBNull.Value) { } bool isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); } } else { int val = cmd.ExecuteNonQuery(); if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); } cmd.Parameters.Clear(); } } tx.Commit(); result = 1; } catch (MySQLException e) { tx.Rollback(); throw e; } } return(result); }
public UserMiniData GetUserMiniData(string userId, int roleId, int OnlyZoneId) { UserMiniData userMimiData = new UserMiniData(); userMimiData.UserId = this.UserID; userMimiData.RealMoney = this.RealMoney; using (MyDbConnection3 conn = new MyDbConnection3(false)) { MySQLConnection dbConn = conn.DbConn; string[] fields = new string[] { "rid", "rname", "sex", "occupation", "level", "zoneid", "changelifecount", "regtime", "lasttime", "logofftime" }; string[] tables = new string[] { "t_roles" }; object[,] array = new object[3, 3]; array[0, 0] = "userid"; array[0, 1] = "="; array[0, 2] = userId; array[1, 0] = "isdel"; array[1, 1] = "="; array[1, 2] = 0; array[2, 0] = "zoneid"; array[2, 1] = "="; array[2, 2] = OnlyZoneId; MySQLSelectCommand cmd = new MySQLSelectCommand(dbConn, fields, tables, array, null, null); if (cmd.Table.Rows.Count > 0) { for (int i = 0; i < cmd.Table.Rows.Count; i++) { int rid = Convert.ToInt32(cmd.Table.Rows[i]["rid"].ToString()); string rname = cmd.Table.Rows[i]["rname"].ToString(); int sex = Convert.ToInt32(cmd.Table.Rows[i]["sex"].ToString()); int occupation = Convert.ToInt32(cmd.Table.Rows[i]["occupation"].ToString()); int level = Convert.ToInt32(cmd.Table.Rows[i]["level"].ToString()); int zoneId = Convert.ToInt32(cmd.Table.Rows[i]["zoneid"].ToString()); int changeLifeCount = Convert.ToInt32(cmd.Table.Rows[i]["changelifecount"].ToString()); DateTime createTime; DateTime.TryParse(cmd.Table.Rows[i]["regtime"].ToString(), out createTime); DateTime lastTime; DateTime.TryParse(cmd.Table.Rows[i]["lasttime"].ToString(), out lastTime); DateTime logoffTime; DateTime.TryParse(cmd.Table.Rows[i]["logofftime"].ToString(), out logoffTime); if (rid == roleId) { userMimiData.RoleCreateTime = createTime; userMimiData.RoleLastLoginTime = lastTime; userMimiData.RoleLastLogoutTime = logoffTime; } if (userMimiData.MinCreateRoleTime > createTime) { userMimiData.MinCreateRoleTime = createTime; } if (userMimiData.LastLoginTime < lastTime) { userMimiData.LastLoginTime = lastTime; userMimiData.LastRoleId = rid; } if (userMimiData.LastLogoutTime < logoffTime) { userMimiData.LastLogoutTime = logoffTime; } if ((userMimiData.MaxChangeLifeCount << 16) + userMimiData.MaxLevel < (changeLifeCount << 16) + level) { userMimiData.MaxChangeLifeCount = changeLifeCount; userMimiData.MaxLevel = level; } } } } return(userMimiData); }