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