public List <Run> ListRuns()
{
RunDB run = new RunDB();
DataTable data = run.GetFullRunInfo();
return(GetRuns(data));
}
/// <summary>
/// 得到第一行的第一个记录
/// </summary>
/// <param name="sqlWhere"></param>
/// <param name="ht"></param>
/// <param name="col"></param>
/// <returns></returns>
public object GetObject(string sql, Hashtable ht)
{
//string sql = String.Format("select {0} from {1} ", col, DbTable);
//if (!String.IsNullOrEmpty(sqlWhere) && sqlWhere.Trim().IndexOf(" ") > 0)
//{
// sqlWhere = sqlWhere.Trim();
// if (sqlWhere.StartsWith("select ", StringComparison.OrdinalIgnoreCase))
// {
// sql = sqlWhere;
// }
// else
// {
// if (!sqlWhere.StartsWith("where ", StringComparison.OrdinalIgnoreCase)) sqlWhere = " where " + sqlWhere;
// sql += sqlWhere;
// }
//}
//else if (String.IsNullOrWhiteSpace(sqlWhere))
//{
// Hashtable htWhere = new Hashtable();
// string sqlWhere2 = IOHelper.GetSqlWhere(ht, htWhere);
// if (!String.IsNullOrEmpty(sqlWhere2) && sqlWhere2.Trim().IndexOf(" ") > 0)
// {
// sqlWhere = sqlWhere2.Trim();
// if (!sqlWhere.StartsWith("where ", StringComparison.OrdinalIgnoreCase)) sqlWhere = " where " + sqlWhere;
// sql += sqlWhere;
// }
// ht = htWhere;
//}
return(RunDB.GetObject(sql, ht, DbConnectionString));
}
public int BulkInsert(List <T> list, bool usePage = false, int maxPage = 1000)
{
if (list == null || list.Count() < 1)
{
return(0);
}
int effectCount = 0;
if (usePage)
{
int pagesize = maxPage;
int page = 1;
int pageCount = Common.GetPageCount(list.Count(), pagesize);
for (; page <= pageCount; page++)
{
DataTable dt = GenericListToDataTable(list.Skip((page - 1) * pagesize).Take(pagesize).ToList());
effectCount += RunDB.Bulk(dt, dt.TableName, DbConnectionString);
}
}
else
{
DataTable dt = GenericListToDataTable(list);
effectCount = RunDB.Bulk(dt, dt.TableName, DbConnectionString);
}
return(effectCount);
}
/// <summary>
/// 得到汇总
/// </summary>
/// <param name="sqlWhere">查询条件</param>
/// <param name="ht">查询序列值</param>
/// <returns></returns>
public int GetCount(string sqlWhere, Hashtable ht, String fromtable = null)
{
if (string.IsNullOrWhiteSpace(fromtable))
{
fromtable = DbTable + MSSQL_NOLOCK;
}
string sql = "select count(0) from " + fromtable;
if (!String.IsNullOrEmpty(sqlWhere) && (sqlWhere.Trim().IndexOf(" ") > 0 || sqlWhere.Length > 5))
{
sqlWhere = sqlWhere.Trim();
if (sqlWhere.StartsWith("select ", StringComparison.OrdinalIgnoreCase))
{
sql = sqlWhere;
}
else
{
if (!sqlWhere.StartsWith("where ", StringComparison.OrdinalIgnoreCase))
{
sqlWhere = " where " + sqlWhere;
}
sql += sqlWhere;
}
}
return(RunDB.GetCount(sql, ht, DbConnectionString));
}
private string AutoLoadColumns()
{
if (_p_缓存的数据库列.ContainsKey(DbTable))
{
return(_p_缓存的数据库列[DbTable]);
}
var rawto = string.Empty;
lock (olock)
{
var __p = new Dictionary <string, bool>();
System.Data.DataTable colsTable = null;
string fetchSql = string.Format("select * from {0} where 0", DbTable);
colsTable = RunDB.GetDataTable(fetchSql, null, DbConnectionString);
if (colsTable == null || colsTable.Columns == null || colsTable.Columns.Count < 1)
{
throw new Exception("数据表读取失败");
}
foreach (System.Data.DataColumn xi in colsTable.Columns)
{
if (xi.ColumnName.Equals(PkName, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
if (!__p.ContainsKey(xi.ColumnName))
{
__p.Add(xi.ColumnName, true);
}
}
rawto = (string.Join("|", __p.Keys).GetForkColumn());
_p_缓存的数据库列.Add(DbTable, rawto);//.ToLower()
}
return(rawto);
}
public static int updateRunStatus(int runNum, char status)
{
RunDB db = new RunDB();
if (db.updateRunStatusDB(runNum, status) != 0)
{
return(1);
}
return(0);
}
public Z GetOne <Z>(string sql, Hashtable ht = null)
{
var dr = RunDB.GetRow(sql, ht, DbConnectionString);
if (dr == null)
{
return(default(Z));
}
return(FlushTo <Z>(dr));
}
public int WUpdate(Hashtable setht, Hashtable wht)
{
if (setht == null || wht == null)
{
return(0);
}
string sql = IOHelper.GetSqlUpdate(DbTable, setht, wht);
return(RunDB.ExecuteNonQuery(sql, setht, DbConnectionString));
}
protected void Page_Load(object sender, EventArgs e)
{
RunDB db = new RunDB();
DataTable dt = db.GetRunCount();
BranchGrid.DataSource = dt;
BranchGrid.DataBind();
dt.Dispose();
BranchGrid.Dispose();
}
public int WUpdate(Hashtable setht, object pkval)
{
if (setht == null || setht.Count < 1 || pkval == null)
{
return(0);
}
var wht = new Hashtable();
wht[Spring.PKName] = pkval;
string sql = IOHelper.GetSqlUpdate(DbTable, setht, wht);
return(RunDB.ExecuteNonQuery(sql, setht, DbConnectionString));
}
public int Update(T obj, Hashtable ht)
{
if (obj == null)
{
return(0);
}
if (ht == null || ht.Count < 1)
{
return(0);
}
Type t = obj.GetType();
var _T_PkName = PkName;
var _T_TableName = DbTable;
if (String.IsNullOrEmpty(_T_PkName) || String.IsNullOrEmpty(_T_TableName))
{
return(0);
}
PropertyInfo pk = t.GetProperty(_T_PkName);
object pkvalue = pk.GetValue(obj, null);
if (pkvalue == null)
{
return(0);
}
Hashtable htupdate = new Hashtable();
htupdate[_T_PkName] = pkvalue;
string sql = String.Format("update {0} set ", _T_TableName);
string updatesql = "";
foreach (string x in ht.Keys)
{
updatesql += "," + x + "=@" + x;
htupdate[x] = ht[x];
}
updatesql = updatesql.Trim(',');
sql += updatesql;
sql += " where " + _T_PkName + "=@" + _T_PkName;
return(RunDB.Execute(sql, htupdate, DbConnectionString));
}
public int DeleteByHashtable(Hashtable ht)
{
if (ht == null || ht.Count < 1)
{
return(-1); //不允许清空一个表
}
Hashtable htWhere = new Hashtable();
string sql = "delete from " + DbTable + " ";
string sqlWhere = IOHelper.GetSqlWhere(ht, htWhere, User_MSSQL);
if (!String.IsNullOrEmpty(sqlWhere) && (sqlWhere.Trim().IndexOf(" ") > 0 || sqlWhere.Length > 4))
{
sqlWhere = sqlWhere.Trim();
if (!sqlWhere.StartsWith("where ", StringComparison.OrdinalIgnoreCase))
{
sqlWhere = " where " + sqlWhere;
}
sql += sqlWhere;
}
return(RunDB.ExecuteNonQuery(sql, htWhere, DbConnectionString));
}
public static int getNumAvailableLargeRuns(DateTime start, DateTime end)
{
RunDB db = new RunDB();
return(db.getNumAvailableLargeRunsDB(start, end));
}
public List <T> ToList(string sql, Hashtable ht = null, string connstr = null)
{
return(this.ToList <T>(RunDB.GetDataTable(sql, ht, connstr ?? DbConnectionString)));
}
public int Execute(string sql, Hashtable ht = null)
{
return(RunDB.Execute(sql, ht, DbConnectionString));
}
public object AddOrUpdate(T obj, params object[] paras)
{
Type t = obj.GetType();
//NEED CACHE
var sp = Spring;
string TableName = sp.TableName, PKName = sp.PKName, Columns = sp.Columns;
bool 自增编号 = sp.IdentityPK;
object temp操作 = null;
DBAction 插入数据 = DBAction.插入;
if (paras != null && paras.Length > 0)
{
temp操作 = paras.FirstOrDefault(x => x.GetType().Equals(typeof(DBAction)));
if (temp操作 != null)
{
插入数据 = (DBAction)temp操作;
}
}
PropertyInfo pk = t.GetProperty(PKName);
if (temp操作 == null || 插入数据.Equals(DBAction.自动))
{
if (pk != null && pk.CanWrite)
{
object vpk = pk.GetValue(obj, null);
//var vpk = PropertyCallAdapterProvider<T>.GetInstance(PKName).InvokeGet(obj);
if (vpk != null)
{
if (!(vpk.ToString().Equals("0")))
{
插入数据 = DBAction.更新;
}
else
{
插入数据 = DBAction.插入;
}
}
}
}
string sql = "";
Hashtable ht = new Hashtable();
Dictionary <string, string> _扩展叠加字段 = null;
if (paras != null)
{
_扩展叠加字段 = (Dictionary <string, string>)paras.FirstOrDefault(x => x.GetType().Equals(typeof(Dictionary <string, string>)));
}
if (_扩展叠加字段 == null)
{
_扩展叠加字段 = new Dictionary <string, string>();
}
var hashAppendKey = obj.GetHashCode().ToString();
foreach (PropertyInfo pi in t.GetProperties())
{
string piName = pi.Name;//不做处理，注意大小写
object v = pi.GetValue(obj, null);
try
{
if ((string.IsNullOrWhiteSpace(Columns) || Columns.IndexOf(piName.GetForkColumn()) != -1))
{
bool 赋值 = true;
if (v != null && v.GetType().Equals(typeof(DateTime)))
{
//对日期联系的做特殊处理
if (((DateTime)v).Year < 1753)
{
赋值 = false;
}
}
if (赋值)
{
if (piName.Equals(PKName, StringComparison.CurrentCultureIgnoreCase))
{
//是主键
if (插入数据.Equals(DBAction.插入) && v != null && v.ToString() != "0")
{
//是插入,有值，OK
sql += piName + ",";
}
}
else
{
//OK
sql += piName + ",";
}
var tmpt = pi.PropertyType;
if (!tmpt.IsValueType && tmpt != typeof(string))
{
v = JSONHelper.ToJSON(v);
}
ht[piName + hashAppendKey] = v;
}
}
}
catch (Exception ep)
{
Console.WriteLine(ep.ToString());
}
}
if (ht == null || ht.Count < 1)
{
return(null);
}
foreach (var xi in _扩展叠加字段)
{
var mi = xi.Key + hashAppendKey;
if (ht.ContainsKey(mi))
{
continue; //不允许更新已经被修改的值
}
sql += xi.Key + ",";
ht[mi] = xi.Value;
}
sql = sql.Trim(',');
if (string.IsNullOrEmpty(sql))
{
return(null);
}
if (插入数据.Equals(DBAction.插入))
{
var preargs = string.Join(",", from xi in sql.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) select "@" + xi.Trim() + obj.GetHashCode());
sql = String.Format(" insert into {0} ({1}) values ({2}) ", TableName, sql, preargs);
}
else
{
var preargs = string.Join(",", from xi in sql.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
select xi.Trim() + "=@" + xi.Trim() + obj.GetHashCode());
sql = String.Format(" update {0} set {1} where {2}=@{2}{3}", TableName, preargs, PKName, obj.GetHashCode());
}
bool 就地执行 = false;
if (paras != null)
{
for (int i = 0; i < paras.Length; i++)
{
object o = paras[i];
if (null == o)
{
continue;
}
if (o.GetType().Equals(typeof(StringBuilder)))
{
StringBuilder sb = o as StringBuilder;
if (i.Equals(0))
{
sb.AppendFormat(" {0}", sql);
}
else
{
sb.Insert(0, sql);
}
sql = sb.ToString();
}
else if (o.GetType().Equals(typeof(Hashtable)))
{
Hashtable newht = o as Hashtable;
foreach (DictionaryEntry de in ht)
{
if (!newht.ContainsKey(de.Key))
{
newht.Add(de.Key, de.Value);
}
}
ht = newht;
}
else if (o.GetType().Equals(typeof(bool)))
{
bool 是否执行 = (bool)o;
if (是否执行)
{
就地执行 = true;
}
}
}
}
if (就地执行)
{
bool 需返回值 = false;
//直接执行
PropertyInfo pi = t.GetProperty(PKName);
if (自增编号 && 插入数据.Equals(DBAction.插入))
{
object defaultvalue = pi.GetValue(obj, null);
if (defaultvalue == null || defaultvalue.ToString().Equals("0"))
{
需返回值 = true;
sql += " ; " + RunDB.GetAutoCols(PKName + obj.GetHashCode().ToString());
}
}
DataSet ds = null;
if (需返回值)
{
ds = RunDB.GetDataSet(sql, ht, DbConnectionString);
if (!Common.IsNull(ds))
{
if (插入数据.Equals(DBAction.插入) && ds.Tables.Count > 0 && !Common.IsNull(ds.Tables[ds.Tables.Count - 1]))
{
if (ds.Tables[ds.Tables.Count - 1].Columns.Contains(PKName + obj.GetHashCode().ToString()))
{
if (pi != null && pi.CanWrite)
{
var od = ds.Tables[ds.Tables.Count - 1].Rows[0][PKName + obj.GetHashCode().ToString()];
var re_id_obj = od.ToString();
if (pi.PropertyType == typeof(int))
{
pi.SetValue(obj, int.Parse(re_id_obj), null);
}
else if (pi.PropertyType == typeof(long))
{
pi.SetValue(obj, long.Parse(re_id_obj), null);
}
else if (pi.PropertyType == typeof(ulong))
{
pi.SetValue(obj, ulong.Parse(re_id_obj), null);
}
else if (pi.PropertyType == typeof(uint))
{
pi.SetValue(obj, uint.Parse(re_id_obj), null);
}
else
{
pi.SetValue(obj, od, null);
}
}
}
}
}
}
else
{
RunDB.Execute(sql, ht, DbConnectionString);
}
return(ds);
}
return(new ArrayList()
{
sql, ht
});
}
