public static bool Member_ActiveMember(string userName, string eMail)
{
string cmdSql = string.Format("UPDATE {0} SET ACTIVE = 'Y' WHERE USER_NAME='{1}' AND EMAIL='{2}'", TABLE_MEMBER, userName, eMail);
MySqlDbAccess.ExecuteNonQueryInt(cmdSql, false);
return(true);
}
protected void Page_Load(object sender, EventArgs e)
{
/*
* if(Session["user_name"] == null){
* Response.Redirect("Login.aspx");
* };
*/
//this.session_name = "¶¶°Õ";
DataTable dt;
string cmdSql = string.Format("SELECT * FROM {0}", ComSQL.TABLE_MEMBER);
dt = MySqlDbAccess.ExcuteQueryData(cmdSql, false);
this.json = JsonConvert.SerializeObject(dt.Rows[0]);
//Response.Write(dt.Rows[0]["user_name"]);
//Response.Write(this.json );
/*
* clsMember m = new clsMember();
* m.user_name = this.session_name;
* dt = ComSQL.Member_ExistAccount(m);
* Response.Write(dt.Rows.Count);
*/
}
public static UpdateReview GetNewVersionFiles(Int32 curVersion, String connection)
{
MySqlParameter[] param = new MySqlParameter[2];
param[0] = new MySqlParameter("_versionDesc", 'c');
param[1] = new MySqlParameter("_Curver", curVersion);
UpdateReview u = null;
DbAccessLayer.MySqlDbAccess db = new MySqlDbAccess(connection, ConnectionStringSourceType.MySetting);
IDataReader reader = db.GetDataReaderFromProcedure("stpDad_updateSelCurrentVersion", param);
if (db.HasError)
{
throw db.ErrorException;
}
if (reader.Read())
{
u = GetUpdateReviewFromReader(reader);
}
reader.Close();
return(u);
}
public bool DelExist(string zid, string sid)
{
string st_ipc = string.Format("delete from st_ipc where ztid={0} and sid='{1}'", ztid, sid);
string st_pa = string.Format("delete from st_pa where ztid={0} and sid='{1}'", ztid, sid);
string st_pr = string.Format("delete from st_pr where ztid={0} and sid='{1}'", ztid, sid);
string show_base = string.Format("delete from show_base where ztid={0} and sid='{1}'", ztid, sid);
string st_iv = string.Format("delete from st_iv where ztid={0} and sid='{1}'", ztid, sid);
string st_pns = string.Format("delete from st_pns where ztid={0} and sid='{1}'", ztid, sid);
string st_ans = string.Format("delete from st_ans where ztid={0} and sid='{1}'", ztid, sid);
string st_dmc = string.Format("delete from st_dc where ztid={0} and sid='{1}'", ztid, sid);
string st_fml = string.Format("delete from st_fml where ztid={0} and sid='{1}'", ztid, sid);
string st_dt = string.Format("delete from st_dt where ztid={0} and sid='{1}'", ztid, sid);
using (MySqlConnection conn = MySqlDbAccess.GetMySqlConnection())
{
conn.Open();
MySqlTransaction trans = conn.BeginTransaction();
MySqlDbAccess.ExecNoQuery(trans, CommandType.Text, st_ipc);
MySqlDbAccess.ExecNoQuery(trans, CommandType.Text, st_pa);
MySqlDbAccess.ExecNoQuery(trans, CommandType.Text, st_pr);
MySqlDbAccess.ExecNoQuery(trans, CommandType.Text, show_base);
MySqlDbAccess.ExecNoQuery(trans, CommandType.Text, st_iv);
MySqlDbAccess.ExecNoQuery(trans, CommandType.Text, st_pns);
MySqlDbAccess.ExecNoQuery(trans, CommandType.Text, st_ans);
MySqlDbAccess.ExecNoQuery(trans, CommandType.Text, st_dmc);
MySqlDbAccess.ExecNoQuery(trans, CommandType.Text, st_fml);
MySqlDbAccess.ExecNoQuery(trans, CommandType.Text, st_dt);
trans.Commit();
}
return(true);
}
public static DataTable Member_ExistMail(clsMember m)
{
DataTable dt = new DataTable();
string cmdSql = string.Format("SELECT * FROM {0} WHERE EMAIl='{1}'", TABLE_MEMBER, m.email);
dt = MySqlDbAccess.ExcuteQueryData(cmdSql, false);
return(dt);
}
public static DataTable Member_ActiveCheck(clsMember m)
{
DataTable dt = new DataTable();
string cmdSql = string.Format("SELECT * FROM {0} WHERE USER_NAME='{1}' AND PASSWORD='******' AND ACTIVE='Y'", TABLE_MEMBER, m.user_name, m.password);
dt = MySqlDbAccess.ExcuteQueryData(cmdSql, false);
return(dt);
}
public static DataTable Member_ExistPassword(clsMember m)
{
DataTable dt = new DataTable();
string cmdSql = string.Format("SELECT * FROM {0} WHERE PASSWORD='******'", TABLE_MEMBER, m.password);
dt = MySqlDbAccess.ExcuteQueryData(cmdSql, false);
return(dt);
}
public static DataTable Member_ExistAccount(clsMember m)
{
DataTable dt = new DataTable();
string cmdSql = string.Format("SELECT * FROM {0} WHERE USER_NAME='{1}'", TABLE_MEMBER, m.user_name);
dt = MySqlDbAccess.ExcuteQueryData(cmdSql, false);
return(dt);
}
public static bool MovePt(string _strSid, string _strZid, string _strDesZtId)
{
bool bRs = false;
string strSqlTmp = @"update show_base set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_ans set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_cpc set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_ct set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_dc set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_dt set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_ec set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_fml set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_ipc set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_iv set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_pa set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_pns set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_pr set ztid='{2}' where sid='{0}' and ztid={1};"
+ "update st_ztlist set app_sum=if(isnull(app_sum),1,app_sum-1) where id={1};"
+ "update st_ztlist set app_sum=if(isnull(app_sum),1,app_sum+1) where id={2};";
if (_strSid.Contains("','"))
{
int nCount = _strSid.Split(',').Count();
strSqlTmp = @"update show_base set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_ans set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_cpc set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_ct set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_dc set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_dt set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_ec set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_fml set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_ipc set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_iv set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_pa set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_pns set ztid='{2}' where sid in ({0}) and ztid={1};"
+ "update st_pr set ztid='{2}' where sid in ({0}) and ztid={1};"
+ string.Format("update st_ztlist set app_sum=if(isnull(app_sum),{0},app_sum-{0}) where id={1};", nCount, "{1}")
+ string.Format("update st_ztlist set app_sum=if(isnull(app_sum),{0},app_sum+{0}) where id={1};", nCount, "{2}");
}
else
{
_strSid = _strSid.Replace("'", "");
}
try
{
MySqlDbAccess.ExecNoQuery(CommandType.Text, string.Format(strSqlTmp, _strSid, _strZid, _strDesZtId));
bRs = true;
}
catch (Exception ex)
{
bRs = false;
}
return(bRs);
}
/// <summary>
/// 删除专题库
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static bool DelZTDB(string ids)
{
if (MySqlDbAccess.ExecNoQuery(CommandType.Text, String.Format("update st_ztlist set isdel=1 where id in({0})", ids)).ToString() != "0")
{
return(true);
}
else
{
return(false);
}
}
public static int Painter_SaveStep(clsPainter p)
{
MySqlInsert ins = new MySqlInsert(TABLE_PAINTER);
foreach (var prop in p.GetType().GetProperties())
{
ins.Add(prop.Name, prop.GetValue(p, null));
}
string cmdSql = ins.ToString();
return(MySqlDbAccess.ExecuteNonQueryInt(cmdSql, false));
}
/// <summary>
/// 删除专题库
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static bool DelZTDB(string id)
{
MySqlParameter parm = new MySqlParameter("?id", id);
if (MySqlDbAccess.ExecNoQuery(CommandType.Text, "update st_ztlist set isdel=1 where id=?id", parm).ToString() == "0")
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 判断专题库是否存在
/// </summary>
/// <param name="Name"></param>
/// <returns></returns>
public static bool ExistZTDB(string Name)
{
MySqlParameter parm = new MySqlParameter("?name", Name);
if (MySqlDbAccess.ExecuteScalar(CommandType.Text, "select count(*) from st_ztlist where name=?name and isdel=0", parm).ToString() == "0")
{
return(false);
}
else
{
return(true);
}
}
public static DataTable getPtData(string strWhere, int nPageIdx, int nPageSize)
{
DataTable dt = null;
if (!strWhere.Trim().Equals(""))
{
strWhere = " where " + strWhere;
}
string strSql = string.Format("select {0} from {1} {2} limit {3},{4}", tbShow_base.ToShowString(), tbShow_base.StrTbName, strWhere, (nPageIdx - 1) * nPageSize, nPageSize);
dt = MySqlDbAccess.GetDataTable(CommandType.Text, strSql);
return(dt);
}
public static int Member_Register(clsMember m)
{
MySqlInsert ins = new MySqlInsert(TABLE_MEMBER);
foreach (var prop in m.GetType().GetProperties())
{
ins.Add(prop.Name, prop.GetValue(m, null));
}
string cmdSql = ins.ToString();
return(MySqlDbAccess.ExecuteNonQueryInt(cmdSql, false));
}
public static int Member_Update(clsMember m)
{
MySqlUpdate update = new MySqlUpdate(TABLE_MEMBER);
foreach (var prop in m.GetType().GetProperties())
{
update.Add(prop.Name, prop.GetValue(m, null));
}
update.Remove("verify");
string cmdSql = update.ToString() + " WHERE USER_NAME='" + m.user_name + "'";
return(MySqlDbAccess.ExecuteNonQueryInt(cmdSql, false));
}
public static bool DelPt(string _strSid, string _strZid)
{
bool bRs = false;
string strSqlTmp = @"delete from show_base where sid='{0}' and ztid={1};"
+ "delete from st_ans where sid='{0}' and ztid={1};"
+ "delete from st_cpc where sid='{0}' and ztid={1};"
+ "delete from st_ct where sid='{0}' and ztid={1};"
+ "delete from st_dc where sid='{0}' and ztid={1};"
+ "delete from st_dt where sid='{0}' and ztid={1};"
+ "delete from st_ec where sid='{0}' and ztid={1};"
+ "delete from st_fml where sid='{0}' and ztid={1};"
+ "delete from st_ipc where sid='{0}' and ztid={1};"
+ "delete from st_iv where sid='{0}' and ztid={1};"
+ "delete from st_pa where sid='{0}' and ztid={1};"
+ "delete from st_pns where sid='{0}' and ztid={1};"
+ "delete from st_pr where sid='{0}' and ztid={1};"
+ "update st_ztlist set app_sum=app_sum-1 where id={1};";
if (_strSid.Contains("','"))
{
int nCount = _strSid.Split(',').Count();
strSqlTmp = @"delete from show_base where sid in ({0}) and ztid={1};"
+ "delete from st_ans where sid in ({0}) and ztid={1};"
+ "delete from st_cpc where sid in ({0}) and ztid={1};"
+ "delete from st_ct where sid in ({0}) and ztid={1};"
+ "delete from st_dc where sid in ({0}) and ztid={1};"
+ "delete from st_dt where sid in ({0}) and ztid={1};"
+ "delete from st_ec where sid in ({0}) and ztid={1};"
+ "delete from st_fml where sid in ({0}) and ztid={1};"
+ "delete from st_ipc where sid in ({0}) and ztid={1};"
+ "delete from st_iv where sid in ({0}) and ztid={1};"
+ "delete from st_pa where sid in ({0}) and ztid={1};"
+ "delete from st_pns where sid in ({0}) and ztid={1};"
+ "delete from st_pr where sid in ({0}) and ztid={1};"
+ "update st_ztlist set app_sum=app_sum-" + nCount.ToString() + " where id={1};";
}
try
{
MySqlDbAccess.ExecNoQuery(CommandType.Text, string.Format(strSqlTmp, _strSid, _strZid));
bRs = true;
}
catch (Exception ex)
{
bRs = false;
}
return(bRs);
}
//初始化cbo控件
public static DataTable Select_YongHu(string _strLname)
{
string strSql = "select * from sys_user where Lname=@Lname";
//准备调用的对应参数
MySqlParameter[] SQlCMDpas =
{
new MySqlParameter("@Lname", MySqlDbType.String),
};
SQlCMDpas[0].Value = _strLname;
//实例化DAL层对应的类，调用DAL类，传入参数
DataTable dt = MySqlDbAccess.GetDataTable(CommandType.Text, strSql, SQlCMDpas);;
return(dt);
}
/// <summary>
/// 修改专题库
/// </summary>
/// <param name="id"></param>
/// <param name="Name"></param>
/// <param name="Desc"></param>
/// <returns></returns>
public static bool EditZTDB(string id, string Name, string Desc, string type)
{
MySqlParameter[] parms = new MySqlParameter[]
{
new MySqlParameter("?name", Name),
new MySqlParameter("?des", Desc),
new MySqlParameter("?id", id)
};
if (MySqlDbAccess.ExecNoQuery(CommandType.Text, "update st_ztlist set name = ?name,des=?des where id=?id", parms) == 0)
{
return(false);
}
else
{
return(true);
}
}
public static Int32 GetCurrentVersion(Char versionType, String connection)
{
MySqlParameter[] param = null;
param[0] = new MySqlParameter("_versionDesc", versionType);
DbAccessLayer.MySqlDbAccess db = new MySqlDbAccess(connection, ConnectionStringSourceType.MySetting);
Int32 version = (Int32)db.GetScalarFromProcedure("stpDad_updateSelCurrentVersion", param);
if (db.HasError)
{
throw db.ErrorException;
}
return(version);
}
public static DataTable getBaseData(string _strSid, string _strDbType)
{
DataTable dt = null;
try
{
string strIsFiled = MySqlDbAccess.ExecuteScalar(CommandType.Text, string.Format("select cfg_stcolumn_filed from cfg_dbtype where dbty='{0}'", _strDbType)).ToString();
string strSql = MySqlDbAccess.ExecuteScalar(CommandType.Text, string.Format(@"SELECT GROUP_CONCAT(CONCAT_WS(' as ',col_name,showname) order by id asc separator ',') as fileds
FROM cfg_stcolumn where tb_name='show_base' and {0}=true", strIsFiled)).ToString();
dt = MySqlDbAccess.GetDataTable(CommandType.Text, string.Format("select {0} from show_base", strSql));
}
catch (Exception ex)
{
}
return(dt);
}
public static bool UpdatePt(string _strId, string _strUpFileds, string _strTb)
{
bool bRs = false;
string strSql = string.Format("update {0} set {1} where id={2}", _strTb, _strUpFileds, _strId);
try
{
MySqlDbAccess.ExecNoQuery(CommandType.Text, strSql);
bRs = true;
}
catch (Exception ex)
{
bRs = false;
}
return(bRs);
}
public static int getPtDataCount(string strWhere)
{
int nRs = 0;
try
{
if (!strWhere.Trim().Equals(""))
{
strWhere = " where " + strWhere;
}
string strSql = string.Format("select count(*) from {0} {1}", tbShow_base.StrTbName, strWhere);
nRs = Convert.ToInt32(MySqlDbAccess.ExecuteScalar(CommandType.Text, strSql));
}
catch (Exception ex)
{
}
return(nRs);
}
/// <summary>
/// 添加专题库
/// </summary>
/// <param name="Name"></param>
/// <param name="Desc"></param>
/// <returns></returns>
public static bool AddZTDB(string Name, string Desc, string type)
{
MySqlParameter[] parms = new MySqlParameter[]
{
new MySqlParameter("?name", Name),
new MySqlParameter("?des", Desc),
new MySqlParameter("?dbtype", type),
new MySqlParameter("?createuserid", UserHelper.user.UserId),
new MySqlParameter("?createtime", DateTime.Now.ToString())
};
if (MySqlDbAccess.ExecNoQuery(CommandType.Text, "insert into st_ztlist (name,des,dbtype,createuserid,createtime,isdel) values (?name,?des,?dbtype,?createuserid,?createtime,0)", parms) == 0)
{
return(false);
}
else
{
return(true);
}
}
public static bool DelKeyVal(int _nValID)
{
bool bRs = false;
string strSqlTmp = @"delete from idx_val where id={0};"
+ "delete from idx_keyval where valid={0};";
try
{
MySqlDbAccess.ExecNoQuery(CommandType.Text, string.Format(strSqlTmp, _nValID));
bRs = true;
}
catch (Exception ex)
{
bRs = false;
}
return(bRs);
}
public static int getSysKeyCount(string strWhere)
{
int nRs = 0;
try
{
if (!strWhere.Trim().Equals(""))
{
strWhere = " where " + strWhere;
}
string strSql = string.Format("select count(*) from idx_key {0}", strWhere);
nRs = Convert.ToInt32(MySqlDbAccess.ExecuteScalar(CommandType.Text, strSql));
}
catch (Exception ex)
{
}
return(nRs);
}
public static DataTable getPtData(string strWhere, string strOdery, string strTbName)
{
DataTable dt = null;
if (!strWhere.Trim().Equals(""))
{
strWhere = " where " + strWhere;
}
if (!strOdery.Trim().Equals(""))
{
strOdery = " order by " + strOdery;
}
string strSql = string.Format("select {0} from {1} {2} {3}", "*",
strTbName, strWhere, strOdery);
dt = MySqlDbAccess.GetDataTable(CommandType.Text, strSql);
return(dt);
}
public static DataTable getSysKey(string strWhere, string strOdery, int nPageIdx, int nPageSize)
{
DataTable dt = null;
if (!strWhere.Trim().Equals(""))
{
strWhere = " where " + strWhere;
}
if (!strOdery.Trim().Equals(""))
{
strOdery = " order by " + strOdery;
}
string strSqlTmp = @"SELECT (@rowIdx:=@rowIdx+1) AS 序号,a.id,a.idx_key as 标引项,b.Lname as 创建人,
(SELECT GROUP_CONCAT(val ORDER BY id ASC SEPARATOR ',') FROM idx_val WHERE keyid = a.id)AS 标引词 ,
a.createtime as 创建时间 FROM idx_key a,sys_user b,(SELECT @rowIdx:={2}) c where a.createuser=b.id {0} {1} limit {2},{3}";
string strSql = string.Format(strSqlTmp, strWhere, strOdery, (nPageIdx - 1) * nPageSize, nPageSize);
dt = MySqlDbAccess.GetDataTable(CommandType.Text, strSql);
return(dt);
}
public static DataTable getPtData(string strWhere, string strOdery, int nPageIdx, int nPageSize, string strDbTy)
{
DataTable dt = null;
if ((!strWhere.Trim().Equals("")) && (!strWhere.ToLower().Contains(" where ")))
{
strWhere = " where " + strWhere;
}
if (!strOdery.Trim().Equals(""))
{
strOdery = " order by " + strOdery;
}
//string strSql = string.Format("select (@rowIdx:=@rowIdx+1) AS 序号,{0} from {1} a,(SELECT @rowIdx:={3}) b {2} {5} limit {3},{4}", tbShow_base.ToShowString(strDbTy),
// tbShow_base.StrTbName, strWhere, (nPageIdx - 1) * nPageSize, nPageSize, strOdery);
string strSql = string.Format("select (@rowIdx:=@rowIdx+1) AS 序号,{0} {1} {2} {5} limit {3},{4}",
tbShow_base.ToFiledFromString(strDbTy, string.Format("(SELECT @rowIdx:={0}) b ", (nPageIdx - 1) * nPageSize), strWhere),
"", "", (nPageIdx - 1) * nPageSize, nPageSize, strOdery);
dt = MySqlDbAccess.GetDataTable(CommandType.Text, strSql);
return(dt);
}
public static bool IsNewVersionAccesible(UpdateType versionType, int curversion, String connection)
{
MySqlParameter[] param = new MySqlParameter[2];
param[0] = new MySqlParameter("_updVersion", curversion);
param[1] = new MySqlParameter("_updDescription", versionType.ToString());
DbAccessLayer.MySqlDbAccess db = new MySqlDbAccess(connection, ConnectionStringSourceType.MySetting);
long version = (long)db.GetScalarFromProcedure("stpDad_updateUtiIsExistNewVer", param);
if (db.HasError)
{
throw db.ErrorException;
}
if (version > 0)
{
return(true);
}
return(false);
}