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 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 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 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);
}
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 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 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));
}