public static bool RemoveRole(DBManager dbMgr, int roleID)
{
bool ret = false;
string today = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
MySQLConnection conn = null;
try
{
conn = dbMgr.DBConns.PopDBConnection();
string cmdText = string.Format("UPDATE t_roles SET isdel=1, deltime='{0}' WHERE rid={1}", today, roleID);
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", cmdText), EventLevels.Important);
MySQLCommand cmd = new MySQLCommand(cmdText, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", cmdText));
}
cmd.Dispose();
cmd = null;
ret = true;
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
return(ret);
}
private bool _Util_ExecNonQuery(DBManager dbMgr, string sql)
{
bool bRet = false;
MySQLConnection conn = null;
try
{
conn = dbMgr.DBConns.PopDBConnection();
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
MySQLCommand cmd = new MySQLCommand(sql, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
bRet = false;
LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", sql), null, true);
}
cmd.Dispose();
cmd = null;
bRet = true;
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
return(bRet);
}
//获得数据库数据
private void GetUserData()
{
MySQLConnection DBConn = null;
string connectStr = new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString;
//System.Windows.Forms.MessageBox.Show(connectStr);
DBConn = new MySQLConnection(connectStr);
DBConn.Open();
MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn);
setformat.ExecuteNonQuery();
setformat.Dispose();
string sql = "select User_Id,User_Name,User_Dept,User_Job,User_Mail,User_Cell from tb_user";
MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn);
DataTable ds = new DataTable();
mda.Fill(ds);
DBConn.Close();
foreach (DataRow dr in ds.Rows)
{
UserModel um = new UserModel();
um.ID = dr["User_Id"].ToString();
um.Name = dr["User_Name"].ToString();
um.Department = dr["User_Dept"].ToString();
um.Email = dr["User_Mail"].ToString();
um.PersonPosition = dr["User_Job"].ToString();
um.Telephone = dr["User_Cell"].ToString();
models.Add(um);
}
view.Source = models;
this.listView1.DataContext = view;
}
private JieriRecvKingItemData QueryRoleJieriRecvKing(DBManager dbMgr, string fromDate, string toDate, int roleID)
{
JieriRecvKingItemData result = null;
MySQLConnection conn = null;
try
{
conn = dbMgr.DBConns.PopDBConnection();
string cmdText = "SELECT t_roles.rid, t_roles.rname, t_roles.zoneid, x.totalrecv from t_roles, (SELECT t_jierizengsong.receiver, SUM(t_jierizengsong.goodscnt) AS totalrecv " + string.Format(" FROM t_jierizengsong WHERE t_jierizengsong.receiver={0} AND sendtime>= '{1}' AND sendtime<='{2}') x ", roleID, fromDate, toDate) + " where t_roles.isdel=0 and t_roles.rid = x.receiver ";
MySQLCommand cmd = new MySQLCommand(cmdText, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
if (reader.Read())
{
result = new JieriRecvKingItemData();
result.RoleID = Convert.ToInt32(reader["rid"].ToString());
result.Rolename = reader["rname"].ToString();
result.ZoneID = Convert.ToInt32(reader["zoneid"].ToString());
result.TotalRecv = Convert.ToInt32(reader["totalrecv"].ToString());
result.Rank = -1;
}
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", cmdText), EventLevels.Important);
cmd.Dispose();
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
return(result);
}
protected int update(string sql)
{
MySQLConnection conn = null;
int resultCount = -1;
try
{
conn = dbMgr.DBConns.PopDBConnection();
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
MySQLCommand cmd = new MySQLCommand(sql, conn);
try
{
resultCount = cmd.ExecuteNonQuery();
}
catch (Exception)
{
LogManager.WriteLog(LogTypes.Error, string.Format("向数据库更新数据失败: {0}", sql));
}
cmd.Dispose();
cmd = null;
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
return(resultCount);
}
private bool ExecNonQuery(string sql)
{
bool bResult = false;
MySQLConnection conn = null;
try
{
conn = DBManager.getInstance().DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(sql, conn);
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
cmd.ExecuteNonQuery();
cmd.Dispose();
bResult = true;
return(true);
}
catch (Exception ex)
{
LogManager.WriteException(ex.Message);
bResult = false;
}
finally
{
if (null != conn)
{
DBManager.getInstance().DBConns.PushDBConnection(conn);
}
}
return(bResult);
}
//获得数据库数据
private void GetUserData()
{
MySQLConnection DBConn = null;
DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "template", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString);
DBConn.Open();
MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn);
setformat.ExecuteNonQuery();
setformat.Dispose();
string type = MainWindow.wfmodel.WFModel_Type;
string sql = "select name, description,type from template where type = '"+type+"'";
MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn);
DataTable ds = new DataTable();
mda.Fill(ds);
DBConn.Close();
foreach (DataRow dr in ds.Rows)
{
TemplateModel um = new TemplateModel();
um.name = dr["name"].ToString();
um.desc = dr["description"].ToString();
um.type = dr["type"].ToString();
models.Add(um);
}
view.Source = models;
this.listView1.DataContext = view;
}
//获得数据库数据
private void GetUserData()
{
MySQLConnection DBConn = null;
DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "template", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString);
DBConn.Open();
MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn);
setformat.ExecuteNonQuery();
setformat.Dispose();
string type = MainWindow.wfmodel.WFModel_Type;
string sql = "select name, description,type from template where type = '" + type + "'";
MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn);
DataTable ds = new DataTable();
mda.Fill(ds);
DBConn.Close();
foreach (DataRow dr in ds.Rows)
{
TemplateModel um = new TemplateModel();
um.name = dr["name"].ToString();
um.desc = dr["description"].ToString();
um.type = dr["type"].ToString();
models.Add(um);
}
view.Source = models;
this.listView1.DataContext = view;
}
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);
}
/// <summary>
/// Gets the volunteer timestamps to an array.@deprecated Use getLastActiveTimestamp. unstable!!
/// </summary>
/// <param name="VolunteerID"></param>
/// <param name="ACTIVE_ONLY"></param>
/// <param name="ONLY_TODAY"></param>
/// <returns>A 2 dimension array of timestamps or an empty array</returns>
public object [][] getVolunteerTimestamps(uint VolunteerID,Boolean ACTIVE_ONLY=false,Boolean ONLY_TODAY=true){
//Build Query
string qry="SELECT * FROM Checkin WHERE VolunteerID="+VolunteerID;
if(ACTIVE_ONLY==true)
qry+=" AND Active=1";
if(ONLY_TODAY==true)
qry+=" AND DATE(TimeIn)=DATE(NOW())";
qry+=" ORDER BY CheckID DESC;";
//Query Data
MySQLCommand mc = new MySQLCommand(qry, vlcon);
MySQLDataReader dr = mc.ExecuteReaderEx();
List<object>[] rval = { new List<object>(), new List<object>(), new List<object>(), new List<object>()};//data string list
int cnt=0;
try{
while (dr.Read())
{
for (byte curfield = 0; curfield < rval.Length; curfield++)
{
rval[curfield].Insert(cnt, dr.GetString(curfield));
Console.WriteLine(rval.GetValue(curfield).ToString());
}
cnt++;
}
}finally{
dr.Close();
mc.Dispose();
}
return new object[][]{rval[0].ToArray(),rval[1].ToArray(),rval[2].ToArray(),rval[3].ToArray()};
}
public void DelItemLogTable(DBManager dbMgr)
{
MySQLConnection conn = null;
for (int i = 0; i < 3; i++)
{
try
{
string strTableName = DateTime.Now.AddDays((double)(-16 - i)).ToString("yyyyMMdd");
conn = dbMgr.DBConns.PopDBConnection();
string strCmd = string.Format("DROP TABLE IF EXISTS `t_log_{0}`;", strTableName);
MySQLCommand cmd = new MySQLCommand(strCmd, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", strCmd));
}
cmd.Dispose();
cmd = null;
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
}
}
protected int delete(string sql, object[] param)
{
MySQLConnection conn = null;
int resultCount = -1;
try
{
conn = this.dbMgr.DBConns.PopDBConnection();
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
MySQLCommand cmd = new MySQLCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
try
{
resultCount = cmd.ExecuteNonQuery();
}
catch (Exception)
{
LogManager.WriteLog(LogTypes.Error, string.Format("向数据库删除数据失败: {0}", sql), null, true);
}
cmd.Dispose();
cmd = null;
}
finally
{
if (null != conn)
{
this.dbMgr.DBConns.PushDBConnection(conn);
}
}
return(resultCount);
}
//获得数据库数据
private void GetUserData()
{
MySQLConnection DBConn = null;
DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString);
DBConn.Open();
MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn);
setformat.ExecuteNonQuery();
setformat.Dispose();
string sql = "select distinct User_Dept from tb_user";
MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn);
DataTable ds = new DataTable();
mda.Fill(ds);
DBConn.Close();
foreach (DataRow dr in ds.Rows)
{
UserModel um = new UserModel();
um.Department = dr["User_Dept"].ToString();
models.Add(um);
}
view.Source = models;
this.listView1.DataContext = view;
}
private MySQLDataReader ExecSelect(string sql)
{
MySQLConnection conn = null;
MySQLDataReader result;
try
{
conn = DBManager.getInstance().DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(sql, conn);
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
MySQLDataReader reader = cmd.ExecuteReaderEx();
cmd.Dispose();
result = reader;
}
catch (Exception ex)
{
LogManager.WriteException(ex.Message);
result = null;
}
finally
{
if (null != conn)
{
DBManager.getInstance().DBConns.PushDBConnection(conn);
}
}
return(result);
}
private void Del_Click(object sender, RoutedEventArgs e)
{
MySQLConnection DBConn = null;
DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString);
try
{
DBConn.Open();
MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn);
setformat.ExecuteNonQuery();
setformat.Dispose();
string sql = "delete from wf_model where model_name = '";
sql += ((WFModel)listView1.SelectedItem).WFModel_Name;
sql += "'";
//MessageBox.Show(sql);
MySQLCommand cmd = new MySQLCommand(sql, DBConn);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception e1)
{
MessageBox.Show("数据库连接失败，请检查网络连接或者数据库配置");
return;
}
GetTemplateData();
}
public void AddItemLogTable(DBManager dbMgr)
{
MySQLConnection conn = null;
for (int i = 0; i < 3; i++)
{
try
{
string strTableName = DateTime.Now.AddDays((double)i).ToString("yyyyMMdd");
conn = dbMgr.DBConns.PopDBConnection();
string strCmd = string.Format("CREATE TABLE if not exists `t_log_{0}` (`Id` int(11) NOT NULL AUTO_INCREMENT,`DBId` int(11) DEFAULT NULL COMMENT '物品在物品表的数据库ID，不是物品时Id为-1',`ObjName` varchar(255) DEFAULT NULL COMMENT '操作对象名称',`optFrom` varchar(255) DEFAULT NULL COMMENT '操作产生点',`currEnvName` varchar(255) DEFAULT NULL COMMENT '对象所在当前环境名称',`tarEnvName` varchar(255) DEFAULT NULL COMMENT '对象将要到达的环境名称',`optType` char(6) DEFAULT NULL COMMENT '操作类型，如下：增加、销毁、修改、移动',`optTime` datetime DEFAULT NULL COMMENT '操作时间',`optAmount` int(11) DEFAULT NULL COMMENT '操作数量',`zoneID` int(11) DEFAULT NULL COMMENT '区编号',`optSurplus` int(11) DEFAULT NULL COMMENT '属性操作后剩余值',`extData` varchar(255) DEFAULT NULL COMMENT '物品附加属性信息',PRIMARY KEY (`Id`),KEY `DBId` (`DBId`),KEY `tarEnvName` (`tarEnvName`),KEY `idx_optTime` (`optTime`)) ENGINE=MyISAM AUTO_INCREMENT=76528 DEFAULT CHARSET=utf8 COMMENT='物品操作日志表';", strTableName);
MySQLCommand cmd = new MySQLCommand(strCmd, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", strCmd));
}
cmd.Dispose();
cmd = null;
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
}
}
public static string GetAward(DBManager dbMgr, int zoneID, int roleID)
{
string result = "";
MySQLConnection conn = null;
try
{
conn = dbMgr.DBConns.PopDBConnection();
string cmdText = string.Format("SELECT type,state FROM t_spread_award WHERE zoneID = '{0}' AND roleID = '{1}'", zoneID, roleID);
MySQLCommand cmd = new MySQLCommand(cmdText, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
while (reader.Read())
{
if (result != "")
{
result += "$";
}
result = result + reader["type"].ToString() + "#";
result += reader["state"].ToString();
}
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", cmdText), EventLevels.Important);
cmd.Dispose();
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
return(result);
}
//获得数据库数据
private void GetUserData()
{
MySQLConnection DBConn = null;
DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString);
DBConn.Open();
MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn);
setformat.ExecuteNonQuery();
setformat.Dispose();
string sql = "select distinct User_Dept from tb_user";
MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn);
DataTable ds = new DataTable();
mda.Fill(ds);
DBConn.Close();
foreach (DataRow dr in ds.Rows)
{
UserModel um = new UserModel();
um.Department = dr["User_Dept"].ToString();
models.Add(um);
}
view.Source = models;
this.listView1.DataContext = view;
}
public void SelectBlob(MySQLConnection con)
{
try
{
con.Open();
MySQLCommand cmd = new MySQLCommand("select col1, col2 from `trntipos` where col1=3", con); //, `SerialNumberLastUsed`
cmd.UsePreparedStatement = true;
cmd.ServerCursor = true;
cmd.Prepare();
MySQLDataReader reader = (MySQLDataReader)cmd.ExecuteReader();
while (reader.Read())
{
getBLOBFile("output.jpg", reader, 1);
}
reader.Close();
cmd.Dispose();
con.Close();
}
catch (Exception e)
{
if (con != null)
{
con.Close();
}
throw e;
}
}
public void InsertBlob(MySQLConnection con)
{
try
{
con.Open();
MySQLCommand cmd = new MySQLCommand("INSERT INTO `trntipos`(col1, col2) values (?, ?)", con); //, `SerialNumberLastUsed`
MySQLParameter p1 = new MySQLParameter("@col1", DbType.Int32);
MySQLParameter p2 = new MySQLParameter("@col2", DbType.Binary);
p2.Value = GetBinary(@"myblob.jpg");
p1.Value = 1;
cmd.UsePreparedStatement = true;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Prepare();
int affectedrows = cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
catch (Exception e)
{
if (con != null)
{
con.Close();
}
throw e;
}
}
/// <summary>
/// 查询返回多个实例
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>List<T>形式查询结果集s</returns>
protected List <T> queryForList(string sql)
{
MySQLConnection conn = null;
List <T> list = null;
try
{
conn = dbMgr.DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(sql, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
int columnNum = reader.FieldCount;
//list = new List<T>();
while (reader.Read())
{
//索引下标
int index = 0;
T obj = Activator.CreateInstance <T>();
for (int i = 0; i < columnNum; i++)
{
int _index = index++;
string columnName = reader.GetName(_index);
Object columnValue = reader.GetValue(_index);
if (null == list)
{
list = new List <T>();
}
setValue(obj, columnName, columnValue);
}
list.Add(obj);
}
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
cmd.Dispose();
cmd = null;
}
catch (Exception e)
{
LogManager.WriteLog(LogTypes.Error, string.Format("查询数据库失败: {0},exception:{1}", sql, e));
return(null);
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
return(list);
}
protected Dictionary <object, T> queryForDictionary(string sql, string keyName)
{
MySQLConnection conn = null;
Dictionary <object, T> objDictionary = null;
try
{
conn = this.dbMgr.DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(sql, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
int columnNum = reader.FieldCount;
objDictionary = new Dictionary <object, T>();
string[] nameArray = new string[columnNum];
while (reader.Read())
{
int index = 0;
T obj = Activator.CreateInstance <T>();
object key = null;
for (int i = 0; i < columnNum; i++)
{
int _index = index++;
if (null == nameArray[_index])
{
nameArray[_index] = reader.GetName(_index);
}
string columnName = nameArray[_index];
object columnValue = reader.GetValue(_index);
this.setValue(obj, columnName, columnValue);
if (null != key)
{
if (keyName.Equals(reader.GetName(_index)) || keyName == reader.GetName(_index))
{
key = reader.GetValue(_index);
}
}
}
if (null != key)
{
objDictionary.Add(key, obj);
}
}
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
cmd.Dispose();
}
catch (Exception)
{
LogManager.WriteLog(LogTypes.Error, string.Format("查询数据库失败: {0}", sql), null, true);
return(null);
}
finally
{
if (null != conn)
{
this.dbMgr.DBConns.PushDBConnection(conn);
}
}
return(objDictionary);
}
private Dictionary <int, int> _QueryEachAwardIdFlag(DBManager dbMgr, string fromDate, string toDate, string userid, int zoneId, string[] AwardIdArray)
{
Dictionary <int, int> result;
if (dbMgr == null || string.IsNullOrEmpty(fromDate) || string.IsNullOrEmpty(toDate) || string.IsNullOrEmpty(userid) || AwardIdArray == null || AwardIdArray.Length == 0)
{
result = null;
}
else
{
Dictionary <int, int> eachAwardIdFlag = new Dictionary <int, int>();
MySQLConnection conn = null;
try
{
string cmdText = string.Format("SELECT keystr,hasgettimes FROM t_huodongawarduserhist WHERE userid='{0}' AND activitytype={1} AND keystr LIKE '{2}%'", userid, 61, Global.GetHuoDongKeyString(fromDate, toDate));
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", cmdText), EventLevels.Important);
conn = dbMgr.DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(cmdText, conn);
try
{
MySQLDataReader reader = cmd.ExecuteReaderEx();
while (reader.Read())
{
string extKey = reader["keystr"].ToString();
int awardFlag = Convert.ToInt32(reader["hasgettimes"].ToString());
int awardId = 0;
if (this._GetAwardIdByExtKey(extKey, out awardId))
{
int flag;
if (!eachAwardIdFlag.TryGetValue(awardId, out flag))
{
eachAwardIdFlag[awardId] = awardFlag;
}
else
{
eachAwardIdFlag[awardId] = (flag | awardFlag);
}
}
}
}
catch (MySQLException)
{
LogManager.WriteLog(LogTypes.Error, string.Format("读取数据库失败: {0}", cmdText), null, true);
}
cmd.Dispose();
cmd = null;
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
result = eachAwardIdFlag;
}
return(result);
}
/// <summary>
/// 插入万魔塔场数据
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public int insertWanMoTaData(DBManager dbMgr, WanMotaInfo data)
{
int ret = -1;
MySQLConnection conn = null;
try
{
bool error = false;
conn = dbMgr.DBConns.PopDBConnection();
string cmdText = string.Format("INSERT INTO t_wanmota (roleID, roleName, flushTime, passLayerCount, sweepLayer, sweepReward, sweepBeginTime) VALUES({0}, '{1}', {2}, {3}, {4}, '{5}', {6})",
data.nRoleID, data.strRoleName, data.lFlushTime, data.nPassLayerCount,
data.nSweepLayer, data.strSweepReward, data.lSweepBeginTime);
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", cmdText), EventLevels.Important);
MySQLCommand cmd = new MySQLCommand(cmdText, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
error = true;
LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", cmdText));
}
cmd.Dispose();
cmd = null;
try
{
if (!error)
{
cmd = new MySQLCommand("SELECT LAST_INSERT_ID() AS LastID", conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
if (reader.Read())
{
ret = Convert.ToInt32(reader[0].ToString());
}
}
}
catch (MySQLException)
{
ret = -2;
}
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
return(ret);
}
private Dictionary <string, int> _QueryEachDayChargeYB(DBManager dbMgr, string fromDate, string toDate, string userid, int zoneid)
{
Dictionary <string, int> result;
if (dbMgr == null || string.IsNullOrEmpty(fromDate) || string.IsNullOrEmpty(toDate) || string.IsNullOrEmpty(userid))
{
result = null;
}
else
{
Dictionary <string, int> eachDayChargeYB = new Dictionary <string, int>();
MySQLConnection conn = null;
try
{
string cmdText = string.Format("SELECT SUM(amount) as total,DATE_FORMAT(inputtime,'%Y-%m-%d') as inputdate FROM t_inputlog WHERE u='{0}' AND inputtime >='{1}' AND inputtime <= '{2}' AND result='success' GROUP BY inputdate UNION ALL SELECT SUM(amount) as total,DATE_FORMAT(inputtime,'%Y-%m-%d') as inputdate FROM t_inputlog2 WHERE u='{0}' AND inputtime >='{1}' AND inputtime <= '{2}' AND result='success' GROUP BY inputdate", userid, fromDate, toDate);
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", cmdText), EventLevels.Important);
conn = dbMgr.DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(cmdText, conn);
try
{
MySQLDataReader reader = cmd.ExecuteReaderEx();
while (reader.Read())
{
string inputDate = reader["inputdate"].ToString();
int inputMoney = Convert.ToInt32(reader["total"].ToString());
int inputYuanBao = Global.TransMoneyToYuanBao(inputMoney);
if (!eachDayChargeYB.ContainsKey(inputDate))
{
eachDayChargeYB.Add(inputDate, inputYuanBao);
}
else
{
Dictionary <string, int> dictionary;
string key;
(dictionary = eachDayChargeYB)[key = inputDate] = dictionary[key] + inputYuanBao;
}
}
}
catch (MySQLException)
{
LogManager.WriteLog(LogTypes.Error, string.Format("读取数据库失败: {0}", cmdText), null, true);
}
cmd.Dispose();
cmd = null;
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
result = eachDayChargeYB;
}
return(result);
}
public void GetYaoSaiBossFightLog(GameServerClient client, int nID, byte[] cmdParams, int count)
{
try
{
Dictionary <int, List <YaoSaiBossFightLog> > bossFightDict = new Dictionary <int, List <YaoSaiBossFightLog> >();
MySQLConnection conn = null;
try
{
RoleParamType roleParamType = RoleParamNameInfo.GetRoleParamType("20008", null);
string cmdText = "select * from t_yaosaiboss_fight";
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", cmdText), EventLevels.Important);
conn = DBManager.getInstance().DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(cmdText, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
while (reader.Read())
{
int rid = int.Parse(reader["rid"].ToString());
int otherrid = int.Parse(reader["otherrid"].ToString());
string otherrname = reader["otherrname"].ToString();
int invitetype = int.Parse(reader["invitetype"].ToString());
int fightlife = int.Parse(reader["fightlife"].ToString());
List <YaoSaiBossFightLog> fightLogList = null;
if (!bossFightDict.TryGetValue(rid, out fightLogList))
{
fightLogList = new List <YaoSaiBossFightLog>();
bossFightDict[rid] = fightLogList;
}
fightLogList.Add(new YaoSaiBossFightLog
{
OtherRid = otherrid,
OtherRname = otherrname,
InviteType = invitetype,
FightLife = fightlife
});
}
cmd.Dispose();
}
catch (Exception ex)
{
LogManager.WriteException(ex.Message);
}
finally
{
if (null != conn)
{
DBManager.getInstance().DBConns.PushDBConnection(conn);
}
}
client.sendCmd <Dictionary <int, List <YaoSaiBossFightLog> > >(nID, bossFightDict);
}
catch (Exception ex)
{
LogManager.WriteLog(LogTypes.Error, string.Format("YaoSaiBoss :: 获取角色boss信息错误 cmd={0}, ex={1}", nID, ex.Message), null, true);
}
}
private void HandleSaveZhanBao(GameServerClient client, int nID, byte[] cmdParams, int count)
{
bool bResult = false;
MySQLConnection conn = null;
try
{
CoupleArenaZhanBaoSaveDbData data = DataHelper.BytesToObject <CoupleArenaZhanBaoSaveDbData>(cmdParams, 0, count);
lock (this.Mutex)
{
long union = this.GetUnionCouple(data.FromMan, data.FromWife);
Queue <CoupleArenaZhanBaoItemData> q = this.GetZhanBao(union);
string sql = string.Format("INSERT INTO t_couple_arena_zhan_bao(`union_couple`,`man_rid`,`wife_rid`,`to_man_rid`,`to_man_zoneid`,`to_man_rname`,`to_wife_rid`,`to_wife_zoneid`,`to_wife_rname`,`is_win`,`get_jifen`,`week`,`time`) VALUES({0},{1},{2},{3},{4},'{5}',{6},{7},'{8}',{9},{10},{11},'{12}');", new object[]
{
union,
data.FromMan,
data.FromWife,
data.ToMan,
data.ZhanBao.TargetManZoneId,
data.ZhanBao.TargetManRoleName,
data.ToWife,
data.ZhanBao.TargetWifeZoneId,
data.ZhanBao.TargetWifeRoleName,
data.ZhanBao.IsWin ? 1 : 0,
data.ZhanBao.GetJiFen,
data.FirstWeekday,
TimeUtil.NowDateTime().ToString("yyyy-MM-dd HH:mm:ss")
});
conn = DBManager.getInstance().DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(sql, conn);
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
cmd.ExecuteNonQuery();
cmd.Dispose();
q.Enqueue(data.ZhanBao);
while (q.Count > this.MaxZhanBaoNum)
{
q.Dequeue();
}
bResult = true;
}
}
catch (Exception ex)
{
LogManager.WriteLog(LogTypes.Error, "CoupleArenaDbManager.HandleSaveZhanBao " + ex.Message, null, true);
bResult = false;
}
finally
{
if (null != conn)
{
DBManager.getInstance().DBConns.PushDBConnection(conn);
}
}
client.sendCmd <bool>(nID, bResult);
}
public int TestSelect(MySQLConnection con, bool useNew)
{
int affectedrows = 0;
try
{
con.Open();
MySQLCommand cmd = new MySQLCommand("SELECT col1, col2, col3, col4 FROM `test_table`", con);
if (useNew)
{
cmd.UsePreparedStatement = true;
cmd.ServerCursor = true;
cmd.Prepare();
}
IDataReader reader = cmd.ExecuteReader();
MySQLCommand cmd1 = new MySQLCommand("SELECT col1, col2, col3, col4 FROM `test_table`", con);
if (useNew)
{
cmd1.UsePreparedStatement = true;
//cmd.ServerCursor = true;
cmd.FetchSize = 10;
cmd1.Prepare();
}
while (reader.Read())
{
affectedrows++;
IDataReader reader1 = cmd1.ExecuteReader();
while (reader1.Read())
{
affectedrows++;
long s1 = reader1.GetInt64(0);
string st1 = reader1.GetString(1);
DateTime dt1 = reader1.GetDateTime(3);
}
reader1.Close();
long s = reader.GetInt64(0);
string st = reader.GetString(1);
DateTime dt = reader.GetDateTime(3);
}
reader.Close();
cmd.Dispose();
con.Close();
return(affectedrows);
}
catch (Exception e)
{
if (con != null)
{
con.Close();
}
throw e;
}
}
//�ΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡΡι��캯��
public UserManager(string name)
: base(name, 560, 300)
{
MySQLConnection obSql;
string linkStr = new MySQLConnectionString("localhost", "flox", "root", "00", 3306).AsString;
linkStr += ";Character Set=GBK";
obSql = new MySQLConnection(linkStr);
obSql.Open(); // ִ�в�ѯ���
MessageBox.Show(linkStr, "���ӳɹ�");
MySQLCommand obCommand = null;
MySQLDataReader obReader = null;
string str = "";
try
{
obCommand = new MySQLCommand("select username from cdb_members", obSql);
obReader = obCommand.ExecuteReaderEx();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "ִ�д���");
}
if (obReader != null)
{
try
{
str += obReader.FieldCount.ToString() + "\r\n";
while (obReader.Read())
{
for (int i = 0; i < obReader.FieldCount; i++)
{
string inputStr = obReader.GetString(i);
#region //note
///Encoding inputCode = Encoding.GetEncoding("GBK");
///Encoding outCode = Encoding.Unicode;
///byte[] bta = inputCode.GetBytes(inputStr);
///byte[] bta2 = Encoding.Convert(inputCode, outCode, bta);
///string outStr = outCode.GetString(bta2);
#endregion
str += inputStr;
}
str += "\r\n";
}
}
finally
{
obReader.Close();
obCommand.Dispose();
obSql.Close();
}
}
MessageBox.Show(str, "�û��б�");
}
public static void SelectBuyBoCai(int bocaiType, long DataPeriods, out List <BuyBoCai2SDB> dList, bool isNoSend)
{
MySQLConnection conn = null;
DBManager dbMgr = DBManager.getInstance();
dList = null;
try
{
conn = dbMgr.DBConns.PopDBConnection();
string sql;
if (isNoSend)
{
sql = string.Format("SELECT `rid`,`RoleName`,`ZoneID`,`UserID`,`ServerID`,`BuyNum`,`BuyValue`,`IsSend`,`IsWin` FROM t_bocai_buy_history WHERE `BocaiType`={0} AND `DataPeriods`={1} AND `IsSend`={2};", bocaiType, DataPeriods, 0);
}
else
{
sql = string.Format("SELECT `rid`,`RoleName`,`ZoneID`,`UserID`,`ServerID`,`BuyNum`,`BuyValue`,`IsSend`,`IsWin` FROM t_bocai_buy_history WHERE `BocaiType`={0} AND `DataPeriods`={1};", bocaiType, DataPeriods);
}
MySQLCommand cmd = new MySQLCommand(sql, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
dList = new List <BuyBoCai2SDB>();
while (reader.Read())
{
BuyBoCai2SDB Item = new BuyBoCai2SDB
{
m_RoleName = reader["RoleName"].ToString(),
strUserID = reader["UserID"].ToString(),
strBuyValue = reader["BuyValue"].ToString(),
m_RoleID = Convert.ToInt32(reader["rid"].ToString()),
ZoneID = Convert.ToInt32(reader["ZoneID"].ToString()),
ServerId = Convert.ToInt32(reader["ServerID"].ToString()),
BuyNum = Convert.ToInt32(reader["BuyNum"].ToString()),
IsSend = (Convert.ToInt32(reader["IsSend"].ToString()) > 0),
IsWin = (Convert.ToInt32(reader["IsWin"].ToString()) > 0),
BocaiType = bocaiType,
DataPeriods = DataPeriods
};
dList.Add(Item);
}
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
cmd.Dispose();
}
catch (Exception ex)
{
LogManager.WriteLog(LogTypes.Exception, string.Format("[ljl]{0}", ex.ToString()), null, true);
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
}
public static bool UpdateMerlinData(DBManager dbMgr, int nRoleID, string[] fields, int nStartIndex)
{
bool result;
if (fields == null || fields.Length != 15 || nStartIndex >= fields.Length)
{
result = false;
}
else
{
bool ret = false;
MySQLConnection conn = null;
try
{
conn = dbMgr.DBConns.PopDBConnection();
if (fields[6] != "*")
{
string endTime = new DateTime(Convert.ToInt64(fields[6]) * 10000L).ToString("yyyy-MM-dd HH:mm:ss");
fields[6] = endTime;
}
for (int i = 7; i <= 14; i++)
{
if (fields[i] != "*")
{
fields[i] = (Convert.ToDouble(fields[i]) * 100.0).ToString();
}
}
string cmdText = DBWriter.FormatUpdateSQL(nRoleID, fields, nStartIndex, MerlinDBOperate.t_fieldNames, "t_merlin_magic_book", MerlinDBOperate.t_fieldTypes, "roleID");
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", cmdText), EventLevels.Important);
MySQLCommand cmd = new MySQLCommand(cmdText, conn);
try
{
cmd.ExecuteNonQuery();
ret = true;
}
catch (Exception)
{
LogManager.WriteLog(LogTypes.Error, string.Format("写入数据库失败: {0}", cmdText), null, true);
}
cmd.Dispose();
cmd = null;
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
result = ret;
}
return(result);
}
public static MerlinGrowthSaveDBData QueryMerlinData(DBManager dbMgr, int nRoleID)
{
MySQLConnection conn = null;
MerlinGrowthSaveDBData MerlinData = null;
try
{
conn = dbMgr.DBConns.PopDBConnection();
string cmdText = string.Format("SELECT roleID, (SELECT rname FROM t_roles WHERE rid = roleID ) AS roleName, occupation, level, level_up_fail_num, starNum, starExp, luckyPoint, toTicks, addTime, activeFrozen, activePalsy, activeSpeedDown, activeBlow, unActiveFrozen, unActivePalsy, unActiveSpeedDown, unActiveBlow FROM t_merlin_magic_book WHERE roleID = {0}", nRoleID);
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", cmdText), EventLevels.Important);
MySQLCommand cmd = new MySQLCommand(cmdText, conn);
try
{
MySQLDataReader reader = cmd.ExecuteReaderEx();
if (reader.Read())
{
MerlinData = new MerlinGrowthSaveDBData();
MerlinData._RoleID = Global.SafeConvertToInt32(reader["roleID"].ToString(), 10);
MerlinData._Occupation = Global.SafeConvertToInt32(reader["occupation"].ToString(), 10);
MerlinData._Level = Global.SafeConvertToInt32(reader["level"].ToString(), 10);
MerlinData._LevelUpFailNum = Global.SafeConvertToInt32(reader["level_up_fail_num"].ToString(), 10);
MerlinData._StarNum = Global.SafeConvertToInt32(reader["starNum"].ToString(), 10);
MerlinData._StarExp = Global.SafeConvertToInt32(reader["starExp"].ToString(), 10);
MerlinData._LuckyPoint = Global.SafeConvertToInt32(reader["luckyPoint"].ToString(), 10);
MerlinData._ToTicks = DataHelper.ConvertToTicks(reader["toTicks"].ToString());
MerlinData._AddTime = DataHelper.ConvertToTicks(reader["addTime"].ToString());
MerlinData._ActiveAttr[0] = (double)(Global.SafeConvertToInt32(reader["activeFrozen"].ToString(), 10) / 100);
MerlinData._ActiveAttr[1] = (double)(Global.SafeConvertToInt32(reader["activePalsy"].ToString(), 10) / 100);
MerlinData._ActiveAttr[2] = (double)(Global.SafeConvertToInt32(reader["activeSpeedDown"].ToString(), 10) / 100);
MerlinData._ActiveAttr[3] = (double)(Global.SafeConvertToInt32(reader["activeBlow"].ToString(), 10) / 100);
MerlinData._UnActiveAttr[0] = (double)(Global.SafeConvertToInt32(reader["unActiveFrozen"].ToString(), 10) / 100);
MerlinData._UnActiveAttr[1] = (double)(Global.SafeConvertToInt32(reader["unActivePalsy"].ToString(), 10) / 100);
MerlinData._UnActiveAttr[2] = (double)(Global.SafeConvertToInt32(reader["unActiveSpeedDown"].ToString(), 10) / 100);
MerlinData._UnActiveAttr[3] = (double)(Global.SafeConvertToInt32(reader["unActiveBlow"].ToString(), 10) / 100);
}
}
catch (Exception)
{
LogManager.WriteLog(LogTypes.Error, string.Format("查询数据库失败: {0}", cmdText), null, true);
}
cmd.Dispose();
cmd = null;
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
return(MerlinData);
}
/// <summary>
/// Gets a volunteer id from a concatenated full name string
/// </summary>
/// <param name="FULL_NAME">The full name of the users in the format{FirstName LastName}</param>
/// <returns>A volunteerID or null</returns>
public uint? getVolunteerIDFromName(string FULL_NAME){
MySQLCommand mc = new MySQLCommand(String.Format("SELECT VolunteerID FROM Volunteer WHERE CONCAT(FirstName,\" \",LastName)='{0}' LIMIT 1;", FULL_NAME), vlcon);
object qres;
try { qres = mc.ExecuteScalar(); }
catch (MySQLException) { throw new DatabaseNotOpenException(); }
mc.Dispose();
if(qres==null){
return null;
}else{
return uint.Parse(qres.ToString());
}
}
public List <YaoSaiMissionData> GetYaoSaiMissionDataByRid(int rid)
{
try
{
List <YaoSaiMissionData> roleMissionList = new List <YaoSaiMissionData>();
MySQLConnection conn = null;
try
{
string cmdText = string.Format("select * from t_yaosaimission where rid='{0}'", rid);
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", cmdText), EventLevels.Important);
conn = DBManager.getInstance().DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(cmdText, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
while (reader.Read())
{
int siteid = int.Parse(reader["siteid"].ToString());
int missionid = int.Parse(reader["missionid"].ToString());
int state = int.Parse(reader["state"].ToString());
string zhipaijingling = reader["zhipaijingling"].ToString();
DateTime starttime = DateTime.Parse(reader["starttime"].ToString());
roleMissionList.Add(new YaoSaiMissionData
{
SiteID = siteid,
MissionID = missionid,
State = state,
ZhiPaiJingLing = zhipaijingling,
StartTime = starttime
});
}
cmd.Dispose();
}
catch (Exception ex)
{
LogManager.WriteException(ex.Message);
}
finally
{
if (null != conn)
{
DBManager.getInstance().DBConns.PushDBConnection(conn);
}
}
return(roleMissionList);
}
catch (Exception ex)
{
LogManager.WriteLog(LogTypes.Error, string.Format("YaoSaiMission :: 获取角色任务信息错误, ex={1}", ex.Message), null, true);
}
return(null);
}
//获得数据库数据
void GetTemplateData()
{
models.Clear();
MySQLConnection DBConn = null;
DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString);
try
{
DBConn.Open();
MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn);
setformat.ExecuteNonQuery();
setformat.Dispose();
string sql = "select model_name,owner,model_content,lastedit_time,model_disc,create_time from wf_model";
MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn);
DataTable ds = new DataTable();
mda.Fill(ds);
DBConn.Close();
foreach (DataRow dr in ds.Rows)
{
WFModel wfm = new WFModel();
wfm.WFModel_CreateTime = dr["create_time"].ToString();
wfm.WFModel_LasteditTime = dr["lastedit_time"].ToString();
wfm.WFModel_Name = dr["model_name"].ToString();
wfm.WFModel_Owner = dr["owner"].ToString();
string test = dr["model_content"].ToString();
if (dr["model_content"] == null || dr["model_content"].ToString().Length<=0)
{
wfm.WFModel_Content = "";
}else
wfm.WFModel_Content = Encoding.Default.GetString((Byte[])dr["model_content"]);
models.Add(wfm);
}
view.Source = models;
this.listView1.DataContext = view;
}
catch (Exception e)
{
MessageBox.Show("数据库连接失败，请检查网络连接或者数据库配置");
return;
}
}
public void GetPeopleDate(string str)
{
models.Clear();
MySQLConnection DBConn = null;
DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString);
try
{
DBConn.Open();
MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn);
setformat.ExecuteNonQuery();
setformat.Dispose();
string sql = "select tb_per.User_Name , tb_user.User_Mail from tb_user, tb_per where tb_per.user_name = tb_user.User_Name and tb_per.user_authority = ";
sql += "'" + str + "'";
//MessageBox.Show(sql);
MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn);
DataTable ds = new DataTable();
mda.Fill(ds);
DBConn.Close();
foreach (DataRow dr in ds.Rows)
{
peopleInfo wfm = new peopleInfo();
wfm.peopleName = dr["user_name"].ToString();
wfm.peopleEmail = dr["User_Mail"].ToString();
models.Add(wfm);
}
view.Source = models;
this.peopleListView.DataContext = view;
}
catch (System.Exception ex)
{
MessageBox.Show("数据库连接失败，请检查网络连接或者数据库配置");
return;
}
}
//执行SQL语句，返回影响的记录数
//<param name="sqlString">sql语句</param>
public static int ExecuteNonQuery(string SQLString)
{
using (MySQLConnection connection = new MySQLConnection(connectionString))
{
using (MySQLCommand cmd = new MySQLCommand(SQLString, connection))
{
try
{
connection.Open();
MySQLCommand setformat = new MySQLCommand("set names gb2312", connection);
setformat.ExecuteNonQuery();
setformat.Dispose();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (MySQLException e)
{
connection.Close();
throw e;
}
}
}
}
public static MySQLDataReader ExecuteReader(string SQLString, params MySQLParameter[] cmdParms)
{
MySQLConnection connection = new MySQLConnection(connectionString);
MySQLCommand cmd = new MySQLCommand();
MySQLDataReader myReader = null;
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
myReader = cmd.ExecuteReaderEx();
cmd.Parameters.Clear();
return myReader;
}
catch (MySQLException e)
{
throw e;
}
finally
{
myReader.Close();
cmd.Dispose();
connection.Close();
}
}
} //end
//保存流程
Boolean saveWorkflowToFile()
{
MySQLConnection DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString);
if (DBConn != null)
{
try
{
DBConn.Open();
DateTime now = DateTime.Now;
String content = designer.Text;//.Replace("\"","\\\"");
// content=Encoding.UTF8.GetString(Encoding.Default.GetBytes(content));// ((Byte[])dr["model_content"]);
// String name = Encoding.UTF8.GetString(Encoding.Default.GetBytes(wfmodel.WFModel_Name));
String name = wfmodel.WFModel_Name;
string sql = "insert into wf_model (model_name,model_type,owner,lastedit_time,model_content,create_time) values ('"
+ name + "','" + wfmodel.WFModel_Type+ "','" + wfmodel.WFModel_Owner + "','" + now.ToLocalTime().ToString() + "','"
+ content + "','" + now.ToLocalTime().ToString() + "' ) on duplicate key update lastedit_time='"
+ now.ToLocalTime().ToString() + "',model_content='" + content + "'";
// MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn);
MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn);
setformat.ExecuteNonQuery();
setformat.Dispose();
MySQLCommand mcd = new MySQLCommand(sql, DBConn);
mcd.ExecuteNonQuery();
}
catch (Exception e)
{
// parentWindow.statusInfo.Text = "数据库连接失败，请检查网络设置和数据库连接配置";
return false;
}
}
else { return false; }
return true;
}
private void Del_Click(object sender, RoutedEventArgs e)
{
MySQLConnection DBConn = null;
DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString);
try
{
DBConn.Open();
MySQLCommand setformat = new MySQLCommand("set names gb2312", DBConn);
setformat.ExecuteNonQuery();
setformat.Dispose();
string sql = "delete from wf_model where model_name = '";
sql += ((WFModel)listView1.SelectedItem).WFModel_Name;
sql += "'";
//MessageBox.Show(sql);
MySQLCommand cmd = new MySQLCommand(sql, DBConn);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception e1)
{
MessageBox.Show("数据库连接失败，请检查网络连接或者数据库配置");
return;
}
GetTemplateData();
}
public static ToolboxCategoryItems loadUserbox()
{
MySQLConnection DBConn = new MySQLConnection(new MySQLConnectionString(Configuration.getDBIp(), "workflow", Configuration.getDBUsername(), Configuration.getDBPassword()).AsString);
if (DBConn != null)
{
try
{
DBConn.Open();
string sql1 = "set names gb2312";
MySQLCommand DBComm = new MySQLCommand(sql1, DBConn); //設定下達 command
DBComm.ExecuteNonQuery();
DBComm.Dispose();
string sql = "select * from tb_user";
MySQLDataAdapter mda = new MySQLDataAdapter(sql, DBConn);
DataTable UserDataTable = new DataTable();
mda.Fill(UserDataTable);
DBConn.Close();
loadSystemIcon();
ToolboxCategoryItems toolboxCategoryItems = new ToolboxCategoryItems();
ToolboxCategory users = new System.Activities.Presentation.Toolbox.ToolboxCategory("系统工作人员");
foreach (DataRow dr in UserDataTable.Rows)
{
//byte[] temp = Encoding.Default.GetBytes(dr["User_Name"].ToString());
//temp = System.Text.Encoding.Convert(Encoding.GetEncoding("utf8"), Encoding.GetEncoding("gb2312"), temp);
//string username = Encoding.Default.GetString(temp);
//ToolboxItemWrapper User = new ToolboxItemWrapper(typeof(Wxwinter.BPM.WFDesigner.User), username);
ToolboxItemWrapper User = new ToolboxItemWrapper(typeof(Wxwinter.BPM.WFDesigner.User), dr["User_Name"].ToString());
users.Add(User);
}
toolboxCategoryItems.Add(users);
parentWindow.statusInfo.Text = "";
return toolboxCategoryItems;
}
catch (Exception e)
{
parentWindow.statusInfo.Text = "数据库连接失败，请检查网络设置和数据库连接配置";
return null;
}
}
else
{
return null;
}
}
public void Update(string tableName, string set, string where)
{
MySQLCommand commn = null;
try
{
string insertString = "UPDATE TABLE_NAME SET SET_CONDITION WHERE WHERE_CONDITION";
insertString = insertString.Replace("TABLE_NAME", tableName);
insertString = insertString.Replace("SET_CONDITION", set);
insertString = insertString.Replace("WHERE_CONDITION", where);
Common.Log(insertString);
conn.Open();
commn = new MySQLCommand();
commn.CommandText = insertString;
commn.CommandType = CommandType.Text;
commn.Connection = conn;
commn.ExecuteNonQuery();
}
catch (MySQLException mySqlException)
{
Common.Log(mySqlException.ToString());
}
finally
{
if (commn != null)
{
commn.Dispose();
}
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
public void Query(string tableName, string where, IDictionary<string, string> item)
{
MySQLCommand commn = null;
MySQLDataReader mySqlReader = null;
MySQLDataAdapter mySqlAdapter = null;
try
{
conn.Open();
Common.Log("Query columns from table: " + tableName);
// Query table column name
string[] columns = null;
commn = new MySQLCommand();
string queryString =
"SELECT column_name FROM information_schema.columns WHERE table_schema = 'DB_NAME' AND table_name = 'TABLE_NAME'";
queryString = queryString.Replace("DB_NAME", databaseName);
queryString = queryString.Replace("TABLE_NAME", tableName);
commn.CommandText = queryString;
commn.CommandType = CommandType.Text;
commn.Connection = conn;
mySqlAdapter = new MySQLDataAdapter(commn);
DataSet myDataSet = new DataSet();
mySqlAdapter.Fill(myDataSet);
columns = new string[myDataSet.Tables[0].Rows.Count];
for (int rowIndex = 0; rowIndex < myDataSet.Tables[0].Rows.Count; rowIndex++)
{
columns[rowIndex] = myDataSet.Tables[0].Rows[rowIndex][0].ToString();
}
myDataSet.Clear();
// Query table by where condition
commn.Dispose();
commn = new MySQLCommand();
string queryString2 = "SELECT * FROM TABLE_NAME WHERE CONDITION";
queryString2 = queryString2.Replace("TABLE_NAME", tableName);
queryString2 = queryString2.Replace("CONDITION", where);
commn.CommandText = queryString2;
commn.CommandType = CommandType.Text;
commn.Connection = conn;
Common.Log(queryString2);
mySqlAdapter = new MySQLDataAdapter(commn);
myDataSet = new DataSet();
mySqlAdapter.Fill(myDataSet);
for (int columnIndex = 0; columnIndex < myDataSet.Tables[0].Columns.Count; columnIndex++)
{
item[columns[columnIndex]] = myDataSet.Tables[0].Rows[0][columnIndex].ToString();
}
}
catch (MySQLException mySqlException)
{
Common.Log(mySqlException.ToString());
}
finally
{
if (mySqlAdapter != null)
{
mySqlAdapter.Dispose();
}
if (mySqlReader != null)
{
mySqlReader.Close();
mySqlReader.Dispose();
}
if (commn != null)
{
commn.Dispose();
}
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
public void Insert(string tableName, IDictionary<string, string> item)
{
MySQLCommand commn = null;
try
{
List<string> columns = item.Keys.ToList<string>();
List<string> values = item.Values.ToList<string>();
for (int index = 0; index < values.Count; index++)
{
if (string.IsNullOrEmpty(values[index]))
{
values.RemoveAt(index);
columns.RemoveAt(index);
index--;
}
}
sqlCommnBuilder = new StringBuilder();
sqlCommnBuilder.Append("INSERT INTO ");
sqlCommnBuilder.Append(tableName);
sqlCommnBuilder.Append(" (");
for (int index = 0; index < columns.Count; index++)
{
sqlCommnBuilder.Append(columns[index]);
sqlCommnBuilder.Append(", ");
}
sqlCommnBuilder.Remove(sqlCommnBuilder.Length - 2, 2);
sqlCommnBuilder.Append(") VALUES ('");
for (int index = 0; index < values.Count; index++)
{
sqlCommnBuilder.Append(values[index]);
sqlCommnBuilder.Append("', '");
}
sqlCommnBuilder.Remove(sqlCommnBuilder.Length - 4, 4);
sqlCommnBuilder.Append("')");
conn.Open();
Common.Log(sqlCommnBuilder.ToString());
commn = new MySQLCommand();
commn.CommandType = CommandType.Text;
commn.CommandText = sqlCommnBuilder.ToString();
commn.Connection = conn;
int id = commn.ExecuteNonQuery();
int t = id;
}
catch (MySQLException mySQLException)
{
Common.Log(mySQLException.ToString());
}
finally
{
if (commn != null)
{
commn.Dispose();
}
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
private bool addOneUrl(int pos)
{
//添加第pos条团购条目信息
string query;
int link_id;
MD5 md5 = new MD5CryptoServiceProvider(); ;
//XmlNode oneUrl = lashouDocument.DocumentElement.ChildNodes[pos];
oneUrl oneUrl = xmlparse.getOneUrl(pos);
StatisticOutput sta = staContext.statistic(oneUrl);
Console.WriteLine("Insert the data to the database....");
query = "insert into links (site_id, url, title, description) values(5, \"" +
sta.url + "\",\"" + sta.title + "\",\"" + sta.description + "\")";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = "select link_id from links where url = \"" + sta.url + "\"";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
IDataReader dt = cmd.ExecuteReader();
link_id = Convert.ToInt32(dt.GetSchemaTable().Rows[0]["link_id"]);
foreach (DictionaryEntry de in sta.keywordList)
{
int keyword_id = 0;
query = "select keyword_id from keywords where keyword = \"" + de.Key + "\"";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
dt = cmd.ExecuteReader();
if (dt.GetSchemaTable().Rows.Count == 0)
{
query = "insert into keywords (keyword) values (\"" + de.Key + "\")";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = "select keyword_id from keywords where keyword = \"" + de.Key + "\"";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
dt = cmd.ExecuteReader();
keyword_id = Convert.ToInt32(dt.GetSchemaTable().Rows[0]["keyword_id"]);
}
else
keyword_id = Convert.ToInt32(dt.GetSchemaTable().Rows[0]["keyword_id"]);
byte[] hash = md5.ComputeHash(System.Text.Encoding.Default.GetBytes(de.Key.ToString()));
string flag;
switch (hash[0] >> 4)
{
case 10:
flag = "a";
break;
case 11:
flag = "b";
break;
case 12:
flag = "c";
break;
case 13:
flag = "d";
break;
case 14:
flag = "e";
break;
case 15:
flag = "f";
break;
default:
flag = (hash[0] >> 4).ToString();
break;
}
query = "insert into link_keyword" + flag + "(link_id, keyword_id, weight, time, feat, discount) values (" +
link_id + "," + keyword_id + "," + de.Value + "," + sta.time + "," + sta.feat + "," +
sta.discount + ")";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
Console.WriteLine("Write one link sucessfully!");
return true;
}
