public object ExecuteNonQuery(CommandConfig cmd)
{
using (DbNetData db = CreateDbNetData())
{
return(db.ExecuteNonQuery(cmd));
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private DbNetData CreateDbNetData()
{
DbNetData db = new DbNetData(_connString);
db.Open();
return(db);
}
public void PutDb(DbNetData db)
{
if (db != null)
{
dbp.PutObject(db);
}
}
public long ExecuteInsert(CommandConfig CmdConfig, bool returnVal)
{
DbNetData db = dbp.GetObject();
try
{
if (returnVal)
{
db.ReturnAutoIncrementValue = true;
}
long r = db.ExecuteInsert(CmdConfig);
if (returnVal)
{
db.ReturnAutoIncrementValue = false;
}
return(r);
}
catch
{
throw;
}
finally
{
dbp.PutObject(db);
}
}
public static string AllowAccess(string token, string api, DbNetDataProxy db)
{
DbNetData dbtmp = db.GetDb();
string result = AllowAccess(token, api, dbtmp);
db.PutDb(dbtmp);
return(result);
}
public DataTable ExecuteDataTable(QueryCommandConfig cmd)
{
using (DbNetData db = CreateDbNetData())
{
DataTable tbl = db.GetDataTable(cmd);
return(tbl);
}
}
protected override object Create()
{
DbNetData db = new DbNetData(connectionString);
db.CloseConnectionOnError = false;
db.Open();
return(db);
}
/// <summary>
///
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, IDictionary paramsDict)
{
using (DbNetData db = CreateDbNetData())
{
DataTable tbl = db.GetDataTable(sql, paramsDict);
return(tbl);
}
}
///// <summary>
/////
///// </summary>
///// <param name="connString"></param>
///// <param name="dataProvider"></param>
///// <param name="databaseType"></param>
//public DBIBase(string connString, DataProvider dataProvider, DatabaseType databaseType)
//{
// this._connString = connString;
// this._dataProvider = dataProvider;
// this._dataBaseType = databaseType;
//}
#region ExecuteDataTable
/// <summary>
///
/// </summary>
/// <returns></returns>
public bool IsAccessDataBase()
{
using (DbNetData db = CreateDbNetData())
{
return(db.Database == DatabaseType.Access ||
db.Database == DatabaseType.Access2007);
}
}
public void UpdateLXDB()
{
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\\_t_\\lx.mdb";
DbNetData db = new DbNetData(conn);
db.Open();
UpdateStation(db, 7, "a", "xml ", 1, "strr", "remm");
}
public void TestInsert()
{
DbNetData db = GetDB();
db.Open();
string sql = @"insert into tblStation(stationOridnal, stationName, stationRemark, stationcpConfig) values (1, 'name', 'remark', 'cp config')";
CommandConfig cmd = new CommandConfig(sql);
db.ExecuteScalar(cmd);
}
public void Execute(IJobExecutionContext context)
{
IConfig c = new IniConfigSource(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ConsoleJob.ini")).Configs["config"];
DbNetDataProxy dbp = new DbNetDataProxy(c.Get("DbConnectionString"));
DbNetData db = null;
try
{
db = dbp.GetDb();
QueryCommandConfig qry = new QueryCommandConfig("select adv_id,adv_date,adv_cate,attachments from t_adv_vegsad where adv_date > @startdate and approved = 1 and dealed != 1");
qry.Params["startdate"] = DateTime.Now.AddDays(-4);
DataTable dt = db.GetDataTable(qry);
if (dt.Rows.Count > 0)
{
db.BeginTransaction();
Random ran = new Random();
//do market_xs
foreach (DataRow r in dt.Rows)
{
CommandConfig updCmd = new CommandConfig("update t_adv_vegsad set readcount_fake = readcount_fake + @add where adv_cate=@adv_cate and adv_id=@adv_id and adv_date=@adv_date");
updCmd.Params["adv_cate"] = r["adv_cate"];
updCmd.Params["adv_id"] = r["adv_id"];
updCmd.Params["adv_date"] = r["adv_date"];
if (r["attachments"] != DBNull.Value && r["attachments"].ToString().Length > 0)
{
updCmd.Params["add"] = ran.Next(4);
}
else
{
updCmd.Params["add"] = ran.Next(2);
}
db.ExecuteNonQuery(updCmd);
}
db.Commit();
}
DC.DCLogger.LogTrace("Job successed [Job_AddAdvReadcount]");
}
catch (Exception ex)
{
DC.DCLogger.LogError("Job failed [Job_AddAdvReadcount]:{0}", ex);
}
finally
{
if (db.Transaction != null)
{
db.Transaction.Dispose();
}
dbp.PutDb(db);
}
}
public void Execute(IJobExecutionContext context)
{
IConfig c = new IniConfigSource(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ConsoleJob.ini")).Configs["config"];
DbNetDataProxy dbp = new DbNetDataProxy(c.Get("DbConnectionString"));
DbNetData db = null;
try
{
db = dbp.GetDb();
db.BeginTransaction();
Dictionary <string, VegunitTrans> vtDic = new Dictionary <string, VegunitTrans>();
QueryCommandConfig XsQry = new QueryCommandConfig("select * from t_xs_wdat_xsmx where recdate >= @startdate and recdate <= @enddate and amount=kilo");
XsQry.Params["startdate"] = DateTime.Now.AddDays(-30);
XsQry.Params["enddate"] = DateTime.Now;
DataTable xsDt = db.GetDataTable(XsQry);
foreach (DataRow r in xsDt.Rows)
{
string xs_guid = Convert.ToString(r["xs_guid"]);
if (!vtDic.ContainsKey(xs_guid))
{
vtDic.Add(xs_guid, new VegunitTrans(null, xs_guid.ToString(), dbp));
}
VegunitTrans vt = vtDic[xs_guid];
UpdateCommandConfig upd = new UpdateCommandConfig("t_xs_wdat_xsmx");
upd.FilterParams["recid"] = r["recid"];
upd.FilterParams["recdate"] = r["recdate"];
upd.FilterParams["gs_guid"] = r["gs_guid"];
upd.FilterParams["xs_guid"] = r["xs_guid"];
upd.FilterParams["vegname"] = r["vegname"];
upd.Params["kilo"] = vt.CalWeight(Convert.ToString(r["vegname"]), Convert.ToDecimal(r["amount"]));
db.ExecuteUpdate(upd);
}
db.Commit();
DC.DCLogger.LogTrace("Job successed [Job_GenerateXsmxKilo]");
}
catch (Exception ex)
{
DC.DCLogger.LogError("Job failed [Job_GenerateXsmxKilo]:{0}", ex);
}
finally
{
if (db.Transaction != null)
{
db.Transaction.Dispose();
}
dbp.PutDb(db);
}
}
protected override void Expire(object o)
{
try
{
DbNetData db = (DbNetData)o;
db.Close();
}
catch (Exception ex)
{
DC.DCLogger.LogError(ex.Message);
}
}
public DbNetDataProxy(string connStr)
{
this.dbp = pool.GetOrAdd(connStr, new ObjectPool <DbNetData>(
() =>
{
DbNetData db = new DbNetData(connStr);
db.CloseConnectionOnError = false;
db.Open();
return(db);
}
));
}
protected override bool Validate(object o)
{
try
{
DbNetData db = (DbNetData)o;
return(!db.Conn.State.Equals(System.Data.ConnectionState.Closed));
}
catch (Exception ex)
{
DC.DCLogger.LogError(ex.Message);
return(false);
}
}
internal void Start()
{
IConfigSource cs =
new IniConfigSource(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "BizSYSMAN.ini"));
IConfig c = cs.Configs["config"];
dbConnStr = c.Get("DbConnectionString");
db = new DbNetData(dbConnStr);
db.CloseConnectionOnError = false;
db.Open();
clt = DCClient.Instance("BizSYSMAN", "global");
clt.Srv("/sysman/dic/list", dic_list);
}
internal void Start()
{
IConfigSource cs =
new IniConfigSource(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "BizMOBAPP.ini"));
IConfig c = cs.Configs["config"];
dbConnStr = c.Get("DbConnectionString");
db = new DbNetData(dbConnStr);
db.CloseConnectionOnError = false;
db.Open();
clt = DCClient.Instance("BizMOBAPP", "global");
clt.Srv("/mobapp/manage/register", manage_register);
clt.Srv("/mobapp/manage/login", manage_login);
}
public void Execute(IJobExecutionContext context)
{
IConfig c = new IniConfigSource(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ConsoleJob.ini")).Configs["config"];
DbNetDataProxy dbp = new DbNetDataProxy(c.Get("DbConnectionString"));
DbNetData db = null;
try
{
db = dbp.GetDb();
db.BeginTransaction();
QueryCommandConfig xsQry = new QueryCommandConfig("select org_guid from t_org_reginfo where org_type =3");
DataTable xsDt = db.GetDataTable(xsQry);
foreach (DataRow r in xsDt.Rows)
{
QueryCommandConfig vegQry = new QueryCommandConfig("select vegname,amount from t_xs_wdat_kc where xs_guid = @xs_guid");
vegQry.Params["xs_guid"] = r["org_guid"];
DataTable vegDt = dbp.GetDataTable(vegQry);
if (vegDt.Rows.Count > 0)
{
var vegEnum = from veg in vegDt.AsEnumerable() orderby veg.Field <decimal>("amount") descending select veg.Field <string>("vegname");
UpdateCommandConfig xskcUpd = new UpdateCommandConfig("t_org_reginfo");
xskcUpd.FilterParams["org_guid"] = r["org_guid"];
xskcUpd.Params["business"] = string.Join("|", vegEnum);
db.ExecuteUpdate(xskcUpd);
}
}
db.Commit();
DC.DCLogger.LogTrace("Job successed [Job_GenerateXsBusiness]");
}
catch (Exception ex)
{
DC.DCLogger.LogError("Job failed [Job_GenerateXsBusiness]:{0}", ex);
}
finally
{
if (db.Transaction != null)
{
db.Transaction.Dispose();
}
dbp.PutDb(db);
}
}
public long ExecuteDelete(CommandConfig CmdConfig)
{
DbNetData db = dbp.GetObject();
try
{
long r = db.ExecuteDelete(CmdConfig);
return(r);
}
catch
{
throw;
}
finally
{
dbp.PutObject(db);
}
}
public object ExecuteScalar(QueryCommandConfig QryCmd)
{
DbNetData db = dbp.GetObject();
try
{
object r = db.ExecuteScalar(QryCmd);
return(r);
}
catch
{
throw;
}
finally
{
dbp.PutObject(db);
}
}
public DataTable GetDataTable(string sql)
{
DbNetData db = dbp.GetObject();
try
{
DataTable r = db.GetDataTable(sql);
return(r);
}
catch
{
throw;
}
finally
{
dbp.PutObject(db);
}
}
public void TestInsertWithParams()
{
DbNetData db = GetDB();
db.Open();
string sql =
@"insert into tblStation(stationOrdinal, stationName, stationRemark, stationcpConfig)
values (@ori, @name, @remark, @cp)";
CommandConfig cmd = new CommandConfig(sql);
cmd.Params.Add("cp", "cp_param");
cmd.Params.Add("ori", 123);
cmd.Params.Add("name", "name_param");
cmd.Params.Add("remark", "remark_param");
db.ExecuteScalar(cmd);
}
public int ExecuteNonQuery(CommandConfig CmdConfig)
{
DbNetData db = dbp.GetObject();
try
{
int r = db.ExecuteNonQuery(CmdConfig);
return(r);
}
catch
{
throw;
}
finally
{
dbp.PutObject(db);
}
}
public DataTable GetDataTable(QueryCommandConfig CmdConfig)
{
DbNetData db = dbp.GetObject();
try
{
DataTable r = db.GetDataTable(CmdConfig);
return(r);
}
catch
{
throw;
}
finally
{
dbp.PutObject(db);
}
}
public void UpdateWithParam()
{
DbNetData db = GetDB();
db.Open();
string sql =
@"update tblStation set stationOrdinal = @ori, stationName = @name, stationRemark=@remark, stationcpConfig=@cp
where stationid = @id";
UpdateCommandConfig cmd = new UpdateCommandConfig(sql);
cmd.Params.Add("id", 1);
cmd.Params.Add("ori", 9);
cmd.Params.Add("name", "name_param_up" + DateTime.Now.ToString());
cmd.Params.Add("remark", "remark_param-up");
cmd.Params.Add("cp", "cp_param_up");
db.ExecuteScalar(cmd);
}
public static string AllowAccess(string token, string api, DbNetData db)
{
string result = "无访问权限";
List <string> regLst = GetRegSet(token, db);
if (regLst != null && regLst.Count > 0)
{
foreach (string reg in regLst)
{
if (Regex.IsMatch(api, reg))
{
result = null;
break;
}
}
}
else
{
result = "用户令牌无效";
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="db"></param>
/// <param name="id"></param>
/// <param name="name"></param>
/// <param name="xml"></param>
/// <param name="ordinal"></param>
/// <param name="street"></param>
/// <param name="remark"></param>
internal void UpdateStation(DbNetData db, int id, string name, string xml, int ordinal, string street, string remark)
{
string s = @"update tblStation set StationName = @StationName, StationCPConfig = @StationCPConfig,
StationOrdinal = @StationOrdinal, Street = @street, StationRemark = @stationRemark
where stationid = @stationID";
ListDictionary list = new ListDictionary();
//list.Add("stationID", id);
list.Add("stationName", name);
list.Add("stationCPConfig", xml + DateTime.Now.ToString());
list.Add("stationOrdinal", ordinal);
list.Add("stationRemark", remark + DateTime.Now.ToString());
list["stationID"] = id;
list.Add("street", street);
CommandConfig cmd = new CommandConfig(s);
cmd.Params = list;
//Instance.ExecuteScalar(s, list);
db.ExecuteScalar(cmd);
}
public void Execute(IJobExecutionContext context)
{
IConfig c = new IniConfigSource(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ConsoleJob.ini")).Configs["config"];
DbNetDataProxy dbp = new DbNetDataProxy(c.Get("DbConnectionString"));
DbNetData db = null;
try
{
db = dbp.GetDb();
db.BeginTransaction();
Dictionary <string, VegunitTrans> vtDic = new Dictionary <string, VegunitTrans>();
QueryCommandConfig xskcQry = new QueryCommandConfig("select * from t_xs_wdat_kc");
DataTable xskcDt = db.GetDataTable(xskcQry);
foreach (DataRow r in xskcDt.Rows)
{
string statSql = "SELECT vegname,date_format(recdate, '%Y-%m-%d') as date,round(max(totalprice/kilo),3) as max,round(min(totalprice/kilo),3) as min,round(avg(totalprice/kilo),3) as avg FROM t_xs_wdat_xsmx where recdate >= @startdate and recdate < @enddate and amount != kilo and vegname = @vegname and xs_guid in ({0}) group by vegname,date_format(recdate, '%Y-%m-%d')";
QueryCommandConfig statQry = new QueryCommandConfig(string.Format(statSql, "'" + r["xs_guid"] + "'"));
statQry.Params["startdate"] = DateTime.Now.Date.AddMonths(-6);
statQry.Params["enddate"] = DateTime.Now.Date.AddDays(1);
statQry.Params["vegname"] = r["vegname"];
DataTable statDt = dbp.GetDataTable(statQry);
if (statDt.Rows.Count > 0)
{
var result =
from statTbl in statDt.AsEnumerable()
orderby DateTime.Parse(statTbl.Field <string>("date")) descending
where DateTime.Parse(statTbl.Field <string>("date")) <= DateTime.Now.Date.AddDays(1)
select new ResponseData
{
max = statTbl.Field <decimal?>("max") ?? 0,
min = statTbl.Field <decimal?>("min") ?? 0,
};
decimal min = 0;
decimal max = 0;
foreach (ResponseData d in result)
{
if (d.max > 0 || d.min > 0)
{
min = d.min;
max = d.max;
break;
}
}
DataRow statR = statDt.Rows[0];
UpdateCommandConfig xskcUpd = new UpdateCommandConfig("t_xs_wdat_kc");
xskcUpd.FilterParams["xs_guid"] = r["xs_guid"];
xskcUpd.FilterParams["vegname"] = r["vegname"];
xskcUpd.Params["xs_price_min"] = min;
xskcUpd.Params["xs_price_max"] = max;
db.ExecuteUpdate(xskcUpd);
}
}
db.Commit();
DC.DCLogger.LogTrace("Job successed [Job_CalXsgh]");
}
catch (Exception ex)
{
DC.DCLogger.LogError("Job failed [Job_CalXsgh]:{0}", ex);
}
finally
{
if (db.Transaction != null)
{
db.Transaction.Dispose();
}
dbp.PutDb(db);
}
}
public void Execute(IJobExecutionContext context)
{
IConfig c = new IniConfigSource(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ConsoleJob.ini")).Configs["config"];
DbNetDataProxy dbp = new DbNetDataProxy(c.Get("DbConnectionString"));
DbNetData db = null;
try
{
db = dbp.GetDb();
db.BeginTransaction();
//do market_xs
QueryCommandConfig XsQry = new QueryCommandConfig("select sn,orgs,veg_hidden from t_dic_market_xs where orgs is not null and orgs !='' and veg_hidden is not null and veg_hidden !=''");
DataTable xsDt = db.GetDataTable(XsQry);
foreach (DataRow r in xsDt.Rows)
{
List <string> orgs = new List <string>(Convert.ToString(r["orgs"]).Split(','));
List <string> veg_hidden = new List <string>(Convert.ToString(r["veg_hidden"]).Split(','));
List <string> veg_hidden_new = new List <string>(veg_hidden);
foreach (string veg in veg_hidden)
{
QueryCommandConfig xsmxQry = new QueryCommandConfig(string.Format("select count(*) from t_xs_wdat_xsmx where recdate >= @startdate and vegname = @vegname and xs_guid in ({0})", "'" + string.Join("','", orgs) + "'"));
xsmxQry.Params["startdate"] = DateTime.Now.AddDays(-3);
xsmxQry.Params["vegname"] = veg;
int count = Convert.ToInt32(db.ExecuteScalar(xsmxQry));
if (count > 0)
{
veg_hidden_new.Remove(veg);
}
}
if (veg_hidden.Count > veg_hidden_new.Count)
{
UpdateCommandConfig upd = new UpdateCommandConfig("t_dic_market_xs");
upd.FilterParams["sn"] = r["sn"];
upd.Params["veg_hidden"] = string.Join(",", veg_hidden_new);
db.ExecuteUpdate(upd);
}
}
//do market_lk
QueryCommandConfig LkQry = new QueryCommandConfig("select sn,orgs,veg_hidden from t_dic_market_lk where orgs is not null and orgs !='' and veg_hidden is not null and veg_hidden !=''");
DataTable lkDt = db.GetDataTable(LkQry);
foreach (DataRow r in lkDt.Rows)
{
List <string> orgs = new List <string>(Convert.ToString(r["orgs"]).Split(','));
List <string> veg_hidden = new List <string>(Convert.ToString(r["veg_hidden"]).Split(','));
List <string> veg_hidden_new = new List <string>(veg_hidden);
foreach (string veg in veg_hidden)
{
QueryCommandConfig xsmxQry = new QueryCommandConfig(string.Format("select count(*) from t_lkdesk_wdat_jhd where Date >= @startdate and MerchandiseName = @vegname and lk_guid in ({0})", "'" + string.Join("','", orgs) + "'"));
xsmxQry.Params["startdate"] = DateTime.Now.AddDays(-3);
xsmxQry.Params["vegname"] = veg;
int count = Convert.ToInt32(db.ExecuteScalar(xsmxQry));
if (count > 0)
{
veg_hidden_new.Remove(veg);
}
}
if (veg_hidden.Count > veg_hidden_new.Count)
{
UpdateCommandConfig upd = new UpdateCommandConfig("t_dic_market_lk");
upd.FilterParams["sn"] = r["sn"];
upd.Params["veg_hidden"] = string.Join(",", veg_hidden_new);
db.ExecuteUpdate(upd);
}
}
db.Commit();
DC.DCLogger.LogTrace("Job successed [Job_DelMarketVeghidden]");
}
catch (Exception ex)
{
DC.DCLogger.LogError("Job failed [Job_DelMarketVeghidden]:{0}", ex);
}
finally
{
if (db.Transaction != null)
{
db.Transaction.Dispose();
}
dbp.PutDb(db);
}
}