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);
}
