/// <summary>
/// 查询数据返回实体
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="idb">数据库连接上下文</param>
/// <param name="sql">查询的sql语句</param>
/// <param name="paras">查询的参数列表，例如WHERE条件中Id=@0，@0 为第一个参数</param>
/// <returns>返回的实体对象列表</returns>
public static IList <T> Query <T>(this IDbHelper idb, string sql, object[] paras) where T : class, new()
{
if (!select.IsMatch(sql))
{
sql = string.Format("SELECT * FROM {0} WHERE {1}", DALUtil.GetTableName <T>(), sql);
}
List <IDataParameter> list = new List <IDataParameter>();
int i = 0;
foreach (var item in paras)
{
var idp = idb.GetIDataParameter();
idp.ParameterName = DALUtil.GetParameterPrefix(idb) + i++.ToString();
idp.Value = item;
list.Add(idp);
}
if (idb.DBType == DataBaseType.Oracle)
{
sql = sql.Replace("@", ":");
}
using (var idr = idb.ExecuteReader(CommandType.Text, sql, list.ToArray()))
{
return(DALUtil.GetEntity <T>(idr));
}
}
/// <summary>
/// 根据主键获取实体
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="idb">数据库连接上下文</param>
/// <param name="pk">主键 例如id=1 写入 GetEntityById(1)</param>
/// <returns>如果存在该实体，返回实体，否则返回null。</returns>
/// <remarks>
/// 对于多主键实体，主键的顺序必须是 实体的顺序否则系统会取不到正确的数据。
///
/// </remarks>
public static T GetEntityById <T>(this IDbHelper idb, params object[] pk) where T : class, new()
{
// 根据实体获取主键
var pklist = DALUtil.GetPKAttribute <T>();
if (pklist.Count != pk.Count())
{
// 实体的主键 个数如果不一致 系统自动抛出异常
throw new Exception("主键设置个数不一致");
}
List <IDataParameter> list = new List <IDataParameter>();
string where = "1=1";
for (int i = 0; i < pklist.Count; i++)
{
where += " AND " + pklist[i] + " = " + DALUtil.GetParameterPrefix(idb) + i.ToString();
var idp = idb.GetIDataParameter();
idp.ParameterName = DALUtil.GetParameterPrefix(idb) + i.ToString();
idp.Value = pk[i];
list.Add(idp);
}
var dt = idb.ExecuteTable(CommandType.Text, string.Format("SELECT * FROM {0} WHERE {1}", DALUtil.GetTableName <T>(), where), list.ToArray());
if (dt.Rows.Count > 0)
{
return(DALUtil.GetEntity <T>(dt)[0]);
}
else
{
return(default(T));
}
}
public void EditMoney(int userId, decimal money)
{
SqlParameter[] pramsModify =
{
DALUtil.MakeInParam("@money", System.Data.SqlDbType.Decimal, 9, money),
};
Modify(pramsModify, userId);
}
/// <summary>
/// 修改一个实体，注意主键必须赋值
/// </summary>
/// <typeparam name="T">实体泛型</typeparam>
/// <param name="idb">数据库连接上下文</param>
/// <param name="entity">需要修改的实体</param>
/// <returns>成功影响的条数，如果没有修改返回0.</returns>
public static int Update <T>(this IDbHelper idb, T entity) where T : class, new()
{
string sql;
IList <IDataParameter> paras = null;
// 获取待更新的参数列表
paras = DALUtil.GetModelUpdateParas <T>(entity, out sql, idb);
return(idb.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()));
}
/// <summary>
///新增
/// <summary>
/// <param name="entity">增加的实体</param>
/// <returns>成功返回自增ID</returns>
public int Add(UserInfoEntity entity)
{
SqlParameter[] pramsAdd =
{
DALUtil.MakeInParam("@CreateTS", System.Data.SqlDbType.DateTime, 8, entity.CreateTS),
DALUtil.MakeInParam("@money", System.Data.SqlDbType.Decimal, 9, entity.money),
};
return(Add(pramsAdd));
}
/// <summary>
/// jlb扩展
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="idb"></param>
/// <param name="query"></param>
/// <returns></returns>
public static IList <T> Query <T>(this IDbHelper idb, Query query) where T : class, new()
{
string sql = "SELECT * FROM {0} WHERE 1=1 {1}";
sql = string.Format(sql, DALUtil.GetTableName <T>(), query.GetCondition(true));
var dt = idb.ExecuteTable(sql);
return(DALUtil.GetEntity <T>(dt));
}
/// <summary>
/// 查询数据并返回对象实体列表
/// </summary>
/// <typeparam name="T">对象实体</typeparam>
/// <param name="idb">数据库连接上下文</param>
/// <param name="query">查询Query对象</param>
/// <param name="pageindex">查询页码</param>
/// <param name="pagesize">每页数据量（0，表示不分页）</param>
/// <param name="totalcount">记录总数，如果不获取记录总数返回0.</param>
/// <param name="bl_getcount">是否获取记录总数</param>
/// <returns>实体对象列表</returns>
public static IList <T> Query <T>(this IDbHelper idb, Query query, int pageindex, int pagesize, out int totalcount, bool bl_getcount) where T : class, new()
{
string sql = "SELECT * FROM {0} WHERE 1=1 {1}";
sql = string.Format(sql, DALUtil.GetTableName <T>(), query.GetCondition(true));
var dt = Query(idb, sql, pageindex, pagesize, out totalcount, bl_getcount);
return(DALUtil.GetEntity <T>(dt));
}
/// <summary>
/// 查询数据返回实体
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="idb">数据库连接上下文</param>
/// <param name="sql">查询的sql语句</param>
/// <returns>返回的实体对象列表</returns>
public static IList <T> Query <T>(this IDbHelper idb, string sql) where T : class, new()
{
if (!select.IsMatch(sql))
{
sql = string.Format("SELECT * FROM {0} WHERE {1}", DALUtil.GetTableName <T>(), sql);
}
using (var idr = idb.ExecuteReader(sql))
{
return(DALUtil.GetEntity <T>(idr));
}
}
private async void Init()
{
await DALUtil.Init();
InstrumentListViewModel.Instance.Instruments = await Task.Run(() => { return(InstrumentDAL.Get()); });
await Task.Run(() => { QuotationCenter.Init(); });
QuotationListViewModel.Instance.Init();
TradeAdapter.Instance.Start();
this.IsBusy = false;
}
public static T GetEntityWithSql <T>(this IDbHelper idb, string sql) where T : class, new()
{
var dt = idb.ExecuteTable(sql);
if (dt.Rows.Count > 0)
{
return(DALUtil.GetEntity <T>(dt)[0]);
}
else
{
return(default(T));
}
}
/// <summary>
/// 根据实体内的主键删除一个实体
/// </summary>
/// <typeparam name="T">实体泛型</typeparam>
/// <param name="idb">数据库连接上下文</param>
/// <param name="entity">待删除的实体，注意：实体的主键必须赋值</param>
/// <returns>返回影响的记录条数，如果该数据不存在返回0</returns>
public static int Delete <T>(this IDbHelper idb, T entity) where T : class, new()
{
string sql;
IList <IDataParameter> paras = null;
// 获取待删除的参数列表
paras = DALUtil.GetModelDeleteParas <T>(entity, out sql, idb);
if (paras.Count == 0)
{
throw new EntityErrorException("没有主键参数的数据不能删除", entity);
}
return(idb.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()));
}
/// <summary>
/// 插入一条新的记录
/// </summary>
/// <typeparam name="T">实体泛型</typeparam>
/// <param name="idb">数据库连接上下文</param>
/// <param name="entity">待新增的实体，注意：如果是Sql server标识列或者oracle使用序列生成主键的，主键请留空。</param>
/// <returns>返回影响的记录条数</returns>
public static int Insert <T>(this IDbHelper idb, T entity) where T : class, new()
{
string sql;
IList <IDataParameter> paras = null;
// 获取插入的参数列表
paras = DALUtil.GetModelInsertParas <T>(entity, out sql, idb);
if (idb.DBType == DataBaseType.SqlServer && DALUtil.IsIdentityTable <T>())
{
return(Convert.ToInt32(idb.ExecuteScalar(CommandType.Text, sql, paras.ToArray())));
}
else if (idb.DBType == DataBaseType.Oracle && DALUtil.SequenceInsert <T>())
{
string seqname = DALUtil.GetSequenceName <T>();
// 如果启用事务
if (idb.IsStartTrans)
{
idb.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray());
int n = Convert.ToInt32(idb.ExecuteScalar(string.Format("SELECT {0}.CURRVAL FROM DUAL", seqname)));
return(n);
}
// 不启动事务
else
{
try
{
idb.BeginTrans();
idb.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray());
int n = Convert.ToInt32(idb.ExecuteScalar(string.Format("SELECT {0}.CURRVAL FROM DUAL", seqname)));
idb.CommitTrans();
return(n);
}
catch (Exception ex)
{
idb.RollTrans();
throw ex;
}
}
}
else
{
return(idb.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()));
}
}
/// <summary>
///新增
/// <summary>
/// <param name="entity">增加的实体</param>
/// <returns>成功返回自增ID</returns>
public int Add(BetRecordEntity entity)
{
SqlParameter[] pramsAdd =
{
DALUtil.MakeInParam("@CreateTS", System.Data.SqlDbType.DateTime, 8, entity.CreateTS),
DALUtil.MakeInParam("@issueNo", System.Data.SqlDbType.BigInt, 8, entity.issueNo),
DALUtil.MakeInParam("@BetValue", System.Data.SqlDbType.NVarChar, 100, entity.BetValue),
DALUtil.MakeInParam("@ResultValue", System.Data.SqlDbType.NVarChar, 100, entity.ResultValue),
DALUtil.MakeInParam("@BetAmount", System.Data.SqlDbType.Int, 4, entity.BetAmount),
DALUtil.MakeInParam("@Balance", System.Data.SqlDbType.Decimal, 9, entity.Balance),
DALUtil.MakeInParam("@isSuccess", System.Data.SqlDbType.Int, 4, entity.isSuccess),
DALUtil.MakeInParam("@UserID", System.Data.SqlDbType.Int, 4, entity.UserID),
DALUtil.MakeInParam("@WinStatu", System.Data.SqlDbType.Decimal, 9, entity.WinStatu),
};
return(Add(pramsAdd));
}
public static T GetEntityWithQuery <T>(this IDbHelper idb, Query query) where T : class, new()
{
string sql = "SELECT * FROM {0} WHERE 1=1 {1}";
sql = string.Format(sql, DALUtil.GetTableName <T>(), query.GetCondition(false));
var dt = idb.ExecuteTable(sql);
if (dt.Rows.Count > 0)
{
return(DALUtil.GetEntity <T>(dt)[0]);
}
else
{
return(default(T));
}
}
/// <summary>
///新增
/// <summary>
/// <param name="entity">增加的实体</param>
/// <returns>成功返回自增ID</returns>
public int Add(JJ_一分快三Entity entity)
{
SqlParameter[] pramsAdd =
{
DALUtil.MakeInParam("@CreateTS", System.Data.SqlDbType.DateTime, 8, entity.CreateTS),
DALUtil.MakeInParam("@D_Date", System.Data.SqlDbType.Date, 3, entity.D_Date),
DALUtil.MakeInParam("@issueNo", System.Data.SqlDbType.BigInt, 8, entity.issueNo),
DALUtil.MakeInParam("@openTime", System.Data.SqlDbType.VarChar, 100, entity.openTime),
DALUtil.MakeInParam("@daxiao", System.Data.SqlDbType.NVarChar, 10, entity.daxiao),
DALUtil.MakeInParam("@danshuang", System.Data.SqlDbType.NVarChar, 10, entity.danshuang),
DALUtil.MakeInParam("@Value_1", System.Data.SqlDbType.Int, 4, entity.Value_1),
DALUtil.MakeInParam("@Value_2", System.Data.SqlDbType.Int, 4, entity.Value_2),
DALUtil.MakeInParam("@Value_3", System.Data.SqlDbType.Int, 4, entity.Value_3),
DALUtil.MakeInParam("@Count", System.Data.SqlDbType.Int, 4, entity.Count),
DALUtil.MakeInParam("@DXGroup", System.Data.SqlDbType.Int, 4, entity.DXGroup),
DALUtil.MakeInParam("@DSGroup", System.Data.SqlDbType.Int, 4, entity.DSGroup),
};
return(Add(pramsAdd));
}
/// <summary>
/// 根据指定主键删除一个实体
/// </summary>
/// <typeparam name="T">实体泛型</typeparam>
/// <param name="idb">数据库连接上下文</param>
/// <param name="pk">主键列表，注意：主键列表的顺序必须与实体完全一致</param>
/// <returns>返回影响的记录条数，如果该数据不存在返回0</returns>
public static int Delete <T>(this IDbHelper idb, object[] pk) where T : class, new()
{
string tablename = DALUtil.GetTableName <T>();
var pks = DALUtil.GetPKAttribute <T>();
if (pks.Count != pk.Length)
{
throw new Exception("主键个数与传入参数不一致");
}
StringBuilder sb = new StringBuilder();
for (int i = 0; i < pk.Length; i++)
{
sb.AppendFormat(" AND {0} = '{1}'", pks[i], pk[i]);
}
string sql = string.Format("DELETE FROM {0} WHERE 1=1{1}", tablename, sb.ToString());
return(idb.ExecuteNonQuery(sql));
}
public void LoadPicsShouldReturnTrue()
{
DALUtil util = new DALUtil();
Assert.IsTrue(util.AddEmployeePicsToDb());
}
public void Employee_LoadPicsTest()
{
DALUtil util = new DALUtil();
Assert.True(util.AddStudentPicsToDb());
}
/// <summary>
/// 执行下级分类
/// </summary>
private void GoChildGategory(long pid, int level)
{
try
{
ApiResult entity = apiClient.GetCategoryID(pid);
Thread.Sleep(300);
if (entity == null)
{
KeyEntity keyEntity = keyList.Dequeue();
apiClient = new Common.APIClient(keyEntity.AppKey, keyEntity.SecretKey);
entity = apiClient.GetCategoryID(pid);
}
CategoryInfoEntity categoryInfo_1688 = entity.categoryInfo[0];
if (categoryInfo_1688 != null)
{
if (categoryInfo_1688.categoryID > 0)
{
long parentID = categoryInfo_1688.parentIDs[0];
#region 保存到数据库
SqlParameter[] pramsWhere =
{
DALUtil.MakeInParam("@categoryID", SqlDbType.Int, 4, categoryInfo_1688.categoryID)
};
CategoryInfoEntity categoryEntity = DAL.CategoryInfoDAL.Get1("categoryID", pramsWhere);
if (categoryEntity != null && categoryEntity.categoryID > 0)
{
DAL.CategoryInfoDAL.Modify(categoryInfo_1688.categoryID, categoryInfo_1688.name, parentID, level, categoryInfo_1688.isLeaf);
msg("存在===" + categoryInfo_1688.name + "->【" + level + "级】" + categoryInfo_1688.name);
}
else
{
DAL.CategoryInfoDAL.Add(categoryInfo_1688.categoryID, categoryInfo_1688.name, parentID, level, categoryInfo_1688.isLeaf);
msg("已添加===" + "->【" + level + "级】" + categoryInfo_1688.name);
}
#endregion
}
//执行子级查询
ChildCategorysEntity[] childCategorys = categoryInfo_1688.childCategorys;
if (childCategorys != null && childCategorys.Length > 0)
{
if (childCategorys != null && childCategorys.Length > 0)
{
foreach (ChildCategorysEntity item in childCategorys)
{
GoChildGategory(item.id, level + 1);
}
}
}
}
}
catch (Exception ex)
{
KeyEntity keyEntity = keyList.Dequeue();
if (keyEntity != null)
{
apiClient = new Common.APIClient(keyEntity.AppKey, keyEntity.SecretKey);
GoChildGategory(pid, level);
}
else
{
msg("-----------没有可用账号");
}
}
}
/// <summary>
/// 蒋林彬扩展
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="idb"></param>
/// <param name="sql"></param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="totalcount"></param>
/// <param name="bl_getcount"></param>
/// <returns></returns>
public static IList <T> Query <T>(this IDbHelper idb, string sql, int pageindex, int pagesize, out int totalcount) where T : class, new()
{
var dt = Query(idb, sql, pageindex, pagesize, out totalcount, true);
return(DALUtil.GetEntity <T>(dt));
}
