public List <T> Find <T>(string strSql) where T : new()
{
List <T> list = new List <T>();
IDataReader sdr = null;
IDbConnection connection = null;
try
{
connection = GetConnection();
bool closeConnection = GetWillConnectionState();
strSql = strSql.ToUpper();
String columns = SQLBuilderHelper.fetchColumns(strSql);
T entity = new T();
PropertyInfo[] properties = ReflectionHelper.GetProperties(entity.GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.SELECT, properties);
sdr = AdoHelper.ExecuteReader(closeConnection, connection, CommandType.Text, strSql, null);
list = EntityHelper.toList <T>(sdr, tableInfo, properties);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sdr != null)
{
sdr.Close();
}
}
return(list);
}
public List <T> FindBySql <T>(string strSql, ParamMap param) where T : new()
{
List <T> list = new List <T>();
IDataReader sdr = null;
try
{
strSql = strSql.ToLower();
String columns = SQLBuilderHelper.fetchColumns(strSql);
PropertyInfo[] properties = ReflectionHelper.GetProperties(new T().GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(new T(), DbOperateType.SELECT, properties);
if (param.IsPage && !SQLBuilderHelper.isPage(strSql))
{
strSql = SQLBuilderHelper.builderPageSQL(strSql, param.OrderFields, param.IsDesc);
}
sdr = AdoHelper.ExecuteReader(AdoHelper.ConnectionString, CommandType.Text, strSql, param.toDbParameters());
list = EntityHelper.toList <T>(sdr, tableInfo, properties);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sdr != null)
{
sdr.Close();
}
}
return(list);
}
public int ExcuteSQL(string strSQL, ParamMap param)
{
object val = 0;
IDbTransaction transaction = null;
IDbConnection connection = null;
try
{
//获取数据库连接，如果开启了事务，从事务中获取
connection = GetConnection();
{
connection = GetConnection();
IDbDataParameter[] parms = param.toDbParameters();
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
strSQL = SQLBuilderHelper.builderAccessSQL(strSQL, parms);
val = AdoHelper.ExecuteNonQuery(connection, transaction, CommandType.Text, strSQL);
}
else
{
val = AdoHelper.ExecuteNonQuery(connection, transaction, CommandType.Text, strSQL, parms);
}
}
}
catch (Exception e)
{
DBLOG.error(strSQL, e);
}
return(Convert.ToInt32(val));
}
public List <T> FindBySql <T>(string strSql, int pageIndex, int pageSize, string order, bool desc) where T : new()
{
List <T> list = new List <T>();
IDataReader sdr = null;
try
{
strSql = strSql.ToLower();
String columns = SQLBuilderHelper.fetchColumns(strSql);
PropertyInfo[] properties = ReflectionHelper.GetProperties(new T().GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(new T(), DbOperateType.SELECT, properties);
strSql = SQLBuilderHelper.builderPageSQL(strSql, order, desc);
ParamMap param = ParamMap.newMap();
param.setPageIndex(pageIndex);
param.setPageSize(pageSize);
sdr = AdoHelper.ExecuteReader(AdoHelper.ConnectionString, CommandType.Text, strSql, param.toDbParameters());
list = EntityHelper.toList <T>(sdr, tableInfo, properties);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sdr != null)
{
sdr.Close();
}
}
return(list);
}
public int Delete <T>(List <T> entityList)
{
if (entityList == null || entityList.Count == 0)
{
return(0);
}
object val = 0;
IDbTransaction transaction = null;
IDbConnection connection = null;
try
{
//获取数据库连接，如果开启了事务，从事务中获取
connection = GetConnection();
transaction = GetTransaction();
T firstEntity = entityList[0];
Type classType = firstEntity.GetType();
PropertyInfo[] properties = ReflectionHelper.GetProperties(firstEntity.GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(firstEntity, DbOperateType.DELETE, properties);
String strSQL = EntityHelper.GetDeleteByIdSql(tableInfo);
foreach (T entity in entityList)
{
tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.DELETE, properties);
IDbDataParameter[] parms = DbFactory.CreateDbParameters(1);
parms[0].ParameterName = tableInfo.Id.Key;
parms[0].Value = tableInfo.Id.Value;
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
strSQL = SQLBuilderHelper.builderAccessSQL(classType, tableInfo, strSQL, parms);
val = AdoHelper.ExecuteNonQuery(connection, transaction, CommandType.Text, strSQL);
}
else
{
val = AdoHelper.ExecuteNonQuery(connection, transaction, CommandType.Text, strSQL, parms);
}
//val = AdoHelper.ExecuteNonQuery(connection, transaction, CommandType.Text, strSQL, parms);
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (m_Transaction == null)
{
connection.Close();
}
}
return(Convert.ToInt32(val));
}
public int DeleteByProperty(BaseEntity model)
{
int ret = 0;
string sql = SQLBuilderHelper.GetDeleteSqlByChangeProperty(model, this.GetORMDBType());
ret = Excute(sql, model);
return(ret);
}
public int Update(BaseEntity model)
{
int ret = 0;
string sql = SQLBuilderHelper.GetUpdateSql(model, this.GetORMDBType());
ret = Excute(sql, model);
return(ret);
}
public async Task <int> UpdateAsync(BaseEntity model)
{
Task <int> ret;
string sql = SQLBuilderHelper.GetUpdateSql(model, this.GetORMDBType());
ret = ExcuteAsync(sql, model);
return(await ret);
}
/// <summary>
/// 返回影响行数
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int ReplaceInto(BaseEntity entity)
{
int ret = 0;
string sql = SQLBuilderHelper.GetReplaceInsertSQL(entity, this.GetORMDBType());
ret = Excute(sql, entity);
return(ret);
}
public async Task <int> BatchUpdateAsync <T>(List <T> list) where T : BaseEntity
{
if (list != null && list.Count > 0)
{
string sql = SQLBuilderHelper.GetUpdateSql(list.First(), this.GetORMDBType());
return(await ExcuteAsync(sql, list));
}
return(0);
}
public async Task <int> DeleteByPropertyAsync(BaseEntity model)
{
int ret = 0;
string sql = SQLBuilderHelper.GetDeleteSqlByChangeProperty(model, this.GetORMDBType());
ret = await ExcuteAsync(sql, model);
return(ret);
}
public int Update <T>(List <T> entityList)
{
if (entityList == null || entityList.Count == 0)
{
return(0);
}
object val = 0;
//IDbConnection connection = null;
IDbTransaction transaction = null;
try
{
//获取数据库连接，如果开启了事务，从事务中获取
//connection = GetConnection();
transaction = GetTransaction();
T firstEntity = entityList[0];
PropertyInfo[] properties = ReflectionHelper.GetProperties(firstEntity.GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(firstEntity, DbOperateType.UPDATE, properties);
String strSQL = EntityHelper.GetUpdateSql(tableInfo);
foreach (T entity in entityList)
{
TableInfo table = EntityHelper.GetTableInfo(entity, DbOperateType.UPDATE, properties);
IDbDataParameter[] parms = table.GetParameters();
strSQL = SQLBuilderHelper.builderAccessSQL(entity, strSQL, parms);
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
val = AdoHelper.ExecuteNonQuery(transaction, CommandType.Text, strSQL);
}
else
{
val = AdoHelper.ExecuteNonQuery(transaction, CommandType.Text, strSQL, parms);
}
}
Commit(transaction);
}
catch (Exception e)
{
Rollback(transaction);
throw e;
}
finally
{
/*if (transaction == null)
* {
* connection.Close();
* connection.Dispose();
* }*/
}
return(Convert.ToInt32(val));
}
public Task <int> DeleteByPropertyAsync <TModel, TProperty>(Expression <Func <TModel, TProperty> > propertySelector, TModel model) where TModel : BaseEntity
{
var propertyNames = new List <string> {
GetPropertyNameFromExpression(propertySelector)
};
string sql = SQLBuilderHelper.BuildDeleteSqlByProperty(model, propertyNames, this.GetORMDBType());
return(this.ExcuteAsync(sql, model));
}
public int DeleteByProperty <TModel, TProperty1, TProperty2>(Expression <Func <TModel, TProperty1> > propertySelector1, Expression <Func <TModel, TProperty2> > propertySelector2, TModel model) where TModel : BaseEntity
{
var propertyNames = new List <string> {
GetPropertyNameFromExpression(propertySelector1),
GetPropertyNameFromExpression(propertySelector2)
};
string sql = SQLBuilderHelper.BuildDeleteSqlByProperty(model, propertyNames, this.GetORMDBType());
return(this.Excute(sql, model));
}
/// <summary>
/// 批量修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public int BatchUpdate <T>(List <T> list) where T : BaseEntity
{
int ret = 0;
if (list != null && list.Count > 0)
{
string sql = SQLBuilderHelper.GetUpdateSql(list.First(), this.GetORMDBType());
ret = Excute(sql, list);
}
return(ret);
}
public PageResult <T> FindPage <T>(string strSQL, ParamMap param) where T : new()
{
PageResult <T> pageResult = new PageResult <T>();
List <T> list = new List <T>();
IDataReader sdr = null;
IDbConnection connection = null;
try
{
connection = GetConnection();
bool closeConnection = GetWillConnectionState();
strSQL = strSQL.ToLower();
String countSQL = SQLBuilderHelper.builderCountSQL(strSQL);
String columns = SQLBuilderHelper.fetchColumns(strSQL);
T entity = new T();
PropertyInfo[] properties = ReflectionHelper.GetProperties(entity.GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.SELECT, properties);
if (param.IsPage && !SQLBuilderHelper.isPage(strSQL))
{
strSQL = SQLBuilderHelper.builderPageSQL(strSQL, param.OrderFields, param.IsDesc);
}
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
strSQL = SQLBuilderHelper.builderAccessSQL(strSQL, param.toDbParameters());
sdr = AdoHelper.ExecuteReader(closeConnection, connection, CommandType.Text, strSQL);
}
else
{
sdr = AdoHelper.ExecuteReader(closeConnection, connection, CommandType.Text, strSQL, param.toDbParameters());
}
int count = this.Count(countSQL, param);
list = EntityHelper.toList <T>(sdr, tableInfo, properties);
pageResult.Total = count;
pageResult.DataList = list;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sdr != null)
{
sdr.Close();
}
}
return(pageResult);
}
/// <summary>
/// 如果主键是自增返回插入主键 否则返回0
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public long Insert(BaseEntity entity)
{
string sql = SQLBuilderHelper.GetInsertSql(entity, this.GetORMDBType());
return(ExecuteAndTrace(sql, entity, () =>
{
using (ConnectionManager mgr = GetConnection())
{
return mgr.Connection.QuerySingle <long>(sql, entity, mgr.Transaction, null, CommandType.Text);
}
}));
}
public async Task <long> InsertAsync(BaseEntity entity)
{
string sql = SQLBuilderHelper.GetInsertSql(entity, this.GetORMDBType());
return(await ExecuteAndTraceAsync(sql, entity, async() =>
{
using (ConnectionManager mgr = GetConnection())
{
var result = await mgr.Connection.QuerySingleAsync <long>(sql, entity, mgr.Transaction, null, CommandType.Text);
return result;
}
}));
}
public T Get <T>(object id) where T : new()
{
List <T> list = new List <T>();
IDataReader sdr = null;
try
{
T entity = new T();
PropertyInfo[] properties = ReflectionHelper.GetProperties(entity.GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.SELECT, properties);
IDbDataParameter[] parms = DbFactory.CreateDbParameters(1);
parms[0].ParameterName = tableInfo.Id.Key;
parms[0].Value = id;
String strSQL = EntityHelper.GetFindByIdSql(tableInfo);
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
strSQL = SQLBuilderHelper.builderAccessSQL(strSQL, parms);
sdr = AdoHelper.ExecuteReader(AdoHelper.ConnectionString, CommandType.Text, strSQL);
}
else
{
sdr = AdoHelper.ExecuteReader(AdoHelper.ConnectionString, CommandType.Text, strSQL, parms);
}
list = EntityHelper.toList <T>(sdr, tableInfo, properties);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sdr != null)
{
sdr.Close();
}
}
return(list.FirstOrDefault());
}
public int ExcuteSQL(string strSQL, ParamMap param)
{
object val = 0;
//IDbConnection connection = null;
IDbTransaction transaction = null;
try
{
//获取数据库连接，如果开启了事务，从事务中获取
//connection = GetConnection();
transaction = GetTransaction();
IDbDataParameter[] parms = param.toDbParameters();
strSQL = SQLBuilderHelper.builderAccessSQL(strSQL, parms);
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
val = AdoHelper.ExecuteNonQuery(transaction, CommandType.Text, strSQL);
}
else
{
val = AdoHelper.ExecuteNonQuery(transaction, CommandType.Text, strSQL, parms);
}
Commit(transaction);
}
catch (Exception e)
{
Rollback(transaction);
throw e;
}
finally
{
/*if (transaction == null)
* {
* connection.Close();
* connection.Dispose();
* }*/
}
return(Convert.ToInt32(val));
}
public List <T> FindByProperty <T>(string propertyName, object propertyValue) where T : new()
{
List <T> list = new List <T>();
IDataReader sdr = null;
try
{
PropertyInfo[] properties = ReflectionHelper.GetProperties(new T().GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(new T(), DbOperateType.SELECT, properties);
String strSql = EntityHelper.GetFindAllSql(tableInfo);
strSql += string.Format(" WHERE {0} = @{1}", propertyName, propertyName);
strSql = strSql.ToLower();
String columns = SQLBuilderHelper.fetchColumns(strSql);// strSql.Substring(0, strSql.IndexOf("FROM"));
ColumnInfo columnInfo = new ColumnInfo();
columnInfo.Add(propertyName, propertyValue);
IDbDataParameter[] parameters = DbFactory.CreateDbParameters(1);
EntityHelper.SetParameters(columnInfo, parameters);
sdr = AdoHelper.ExecuteReader(AdoHelper.ConnectionString, CommandType.Text, strSql, parameters);
list = EntityHelper.toList <T>(sdr, tableInfo, properties);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sdr != null)
{
sdr.Close();
}
}
return(list);
}
public int Count(string strSql, ParamMap param)
{
int count = 0;
try
{
strSql = strSql.ToLower();
String columns = SQLBuilderHelper.fetchColumns(strSql);
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
strSql = SQLBuilderHelper.builderAccessSQL(strSql, param.toDbParameters());
}
count = Convert.ToInt32(AdoHelper.ExecuteScalar(AdoHelper.ConnectionString, CommandType.Text, strSql, param.toDbParameters()));
}
catch (Exception ex)
{
throw ex;
}
return(count);
}
public int Count(string strSql, ParamMap param)
{
int count = 0;
IDbConnection connection = null;
bool closeConnection = GetWillConnectionState();
try
{
connection = GetConnection();
strSql = strSql.ToLower();
String columns = SQLBuilderHelper.fetchColumns(strSql);
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
strSql = SQLBuilderHelper.builderAccessSQL(strSql, param.toDbParameters());
count = Convert.ToInt32(AdoHelper.ExecuteScalar(connection, CommandType.Text, strSql));
}
else
{
count = Convert.ToInt32(AdoHelper.ExecuteScalar(connection, CommandType.Text, strSql, param.toDbParameters()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
return(count);
}
public async Task <T> GetByPkAsync <T>(BaseEntity model)
{
string sql = SQLBuilderHelper.GetByPkSql(model, this.GetORMDBType());
return(await this.GetAsync <T>(sql, model));
}
/// <summary>
/// 根据主键查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public T GetByPk <T>(BaseEntity model)
{
string sql = SQLBuilderHelper.GetByPkSql(model, this.GetORMDBType());
return(this.Get <T>(sql, model));
}
/// <summary>
/// 获取所有列 逗号分隔 mysql用``,sqlserver用[]
/// </summary>
/// <param name="prefix">如A 最终sql语句就是A.Id，可为空</param>
/// <returns></returns>
public string GetAllColumns <T>(string prefix = "") where T : BaseEntity
{
return(SQLBuilderHelper.GetAllColumns(typeof(T), this.GetORMDBType(), prefix));
}
public string GetTableName <T>() where T : BaseEntity
{
return(SQLBuilderHelper.GetTableName(typeof(T)));
}
public int Insert <T>(List <T> entityList)
{
if (entityList == null || entityList.Count == 0)
{
return(0);
}
object val = 0;
//IDbConnection connection = null;
IDbTransaction transaction = null;
try
{
//获取数据库连接，如果开启了事务，从事务中获取
//connection = GetConnection();
transaction = GetTransaction();
//从实体对象的属性配置上获取对应的表信息
T firstEntity = entityList[0];
PropertyInfo[] properties = ReflectionHelper.GetProperties(firstEntity.GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(firstEntity, DbOperateType.INSERT, properties);
//获取SQL语句
String strSQL = EntityHelper.GetInsertSql(tableInfo);
foreach (T entity in entityList)
{
//从实体对象的属性配置上获取对应的表信息
tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.INSERT, properties);
//获取参数
IDbDataParameter[] parms = tableInfo.GetParameters();
//如果是Access数据库，直接根据参数拼接最终的SQL语句
strSQL = SQLBuilderHelper.builderAccessSQL(entity, strSQL, parms);
//Access数据库执行不需要命名参数
if (AdoHelper.DbType == DatabaseType.ACCESS)
{
//执行Insert命令
val = AdoHelper.ExecuteScalar(transaction, CommandType.Text, strSQL);
//如果是Access数据库，另外执行获取自动生成的ID
String autoSql = EntityHelper.GetAutoSql();
val = AdoHelper.ExecuteScalar(transaction, CommandType.Text, autoSql);
}
else
{
//执行Insert命令
val = AdoHelper.ExecuteScalar(transaction, CommandType.Text, strSQL, parms);
}
//把自动生成的主键ID赋值给返回的对象
if (AdoHelper.DbType == DatabaseType.SQLSERVER || AdoHelper.DbType == DatabaseType.MYSQL || AdoHelper.DbType == DatabaseType.ACCESS)
{
PropertyInfo propertyInfo = EntityHelper.GetPrimaryKeyPropertyInfo(entity, properties);
ReflectionHelper.SetPropertyValue(entity, propertyInfo, val);
}
Commit(transaction);
}
}
catch (Exception e)
{
Rollback(transaction);
throw e;
}
finally
{
/*if (transaction == null)
* {
* connection.Close();
* connection.Dispose();
* }*/
}
return(Convert.ToInt32(val));
}
public async Task <int> DeleteByPkAsync(BaseEntity model)
{
string sql = SQLBuilderHelper.GetDeleteByPkSql(model, this.GetORMDBType());
return(await this.ExcuteAsync(sql, model));
}
public async Task <int> ReplaceIntoAsync(BaseEntity entity)
{
string sql = SQLBuilderHelper.GetReplaceInsertSQL(entity, this.GetORMDBType());
return(await ExcuteAsync(sql, entity));
}