/// <summary>
/// 发生数据库异常
/// </summary>
/// <param name="sender"></param>
/// <param name="ex"></param>
public void OnDBException(DataAccessBroker sender, Exception ex)
{
if (DBException != null)
{
DBException(sender, ex);
}
}
public static void UpdateAndSave(EntityBase entity, DataAccessBroker broker)
{
string sqlString = "";
if (entity.OringTableSchema.TableName == TransactionProductView || entity.OringTableSchema.TableName == TransactionView)
{
sqlString = "UPDATE " + GetTableName(entity) + " SET " + ParseSQL(entity.OringTableSchema.ValueColumnInfo, ",") + " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, " AND ");
}
else
{
sqlString = "UPDATE " + entity.OringTableSchema.TableName + " SET " + ParseSQL(entity.OringTableSchema.ValueColumnInfo, ",") + " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, " AND ");
}
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName));
}
int i = 0;
i = broker.ExecuteSQL(sqlString, dpc);
if (i == 0)
{
DataAccess.Insert(entity);
}
}
/// <summary>
/// 批量新增-独立事务
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
public static bool Insert <T>(List <T> list)
where T : EntityBase
{
DataAccessBroker broker = DataAccessFactory.Instance();
bool result = true;
try
{
broker.BeginTransaction();
Insert(list, broker);
broker.Commit();
}
catch (Exception ex)
{
result = false;
broker.RollBack();
throw ex;
}
finally
{
broker.Close();
}
return(result);
}
/// <summary>
/// 获取数据通过Command
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="command">SQL</param>
/// <returns></returns>
public static EntityCollection <T> Select <T>(string queryString, DataAccessParameterCollection parameters, CommandType cmdType)
where T : EntityBase, new()
{
EntityCollection <T> result = new EntityCollection <T>();
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
IDataReader reader = broker.ExecuteReader(queryString, parameters, cmdType);
int fieldCount = reader.FieldCount;
List <string> columns = new List <string>();
object[] values = new object[fieldCount];
for (int i = 0; i < fieldCount; i++)
{
columns.Add(reader.GetName(i));
}
while (reader.Read())
{
T t = new T();
reader.GetValues(values);
for (int i = 0; i < fieldCount; i++)
{
if (!(values[i] == DBNull.Value))
{
t.SetData(columns[i], values[i]);
t.GetData(columns[i]);
}
}
result.Add(t);
}
}
return(result);
}
/// <summary>
/// 批量新增-独立事务
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// Jerry 2009年3月23日 老刘修改了此方法 只Creat一次Command 减少内存的损耗 修改后方法见下方395行开始
//public static bool Insert<T>(List<T> list)
// where T : EntityBase
//{
// DataAccessBroker broker = DataAccessFactory.Instance();
// bool result = true;
// try
// {
// broker.BeginTransaction();
// Insert(list, broker);
// broker.Commit();
// }
// catch
// {
// result = false;
// broker.RollBack();
// }
// finally
// {
// broker.Close();
// }
// return result;
//}
public static bool Insert <T>(List <T> list)
where T : EntityBase
{
DataAccessBroker broker = DataAccessFactory.Instance();
bool result = true;
try
{
if (list != null && list.Count > 0)
{
broker.BeginTransaction();
T t = list[0];
//string sqlString = "INSERT INTO " + t.OringTableSchema.TableName + " ( " + ParseInsertSQL(t.OringTableSchema.AllColumnInfo, "") + " ) VALUES( " + ParseInsertSQL(t.OringTableSchema.AllColumnInfo, broker.ParameterPrefix) + ")";
//broker.CreateCommandE(sqlString);
foreach (T entity in list)
{
Insert(entity, broker);
}
broker.Commit();
}
}
catch (Exception ex)
{
result = false;
broker.RollBack();
throw ex;
}
finally
{
broker.Close();
broker.Dispose();
}
return(result);
}
/// <summary>
/// 发生数据库异常
/// </summary>
/// <param name="sender"></param>
/// <param name="ex"></param>
public void OnDBException(DataAccessBroker sender, Exception ex)
{
if (DBException != null)
{
DBException(sender, ex);
}
}
public static PerformanceEntityCollection <T> PerformanceSelect <T>(string queryString, DataAccessParameterCollection parameters, CommandType cmdType)
where T : EntityBase, new()
{
PerformanceEntityCollection <T> result = null;
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
IDataReader reader = broker.ExecuteReader(queryString, parameters, cmdType);
int fieldCount = reader.FieldCount;
List <string> columns = new List <string>();
object[] values = new object[fieldCount];
for (int i = 0; i < fieldCount; i++)
{
columns.Add(reader.GetName(i));
}
result = new PerformanceEntityCollection <T>(columns);
while (reader.Read())
{
reader.GetValues(values);
result.Add(values);
}
reader.Dispose();
}
return(result);
}
public static EntityCollection <T> SelectByBroker <T>(T entity, DataAccessBroker broker)
where T : EntityBase, new()
{
WhereBuilder wb = new WhereBuilder(entity);
return(SelectByBroker <T>(wb, broker));
}
public static Dictionary <string, object> ExecuteStoreProcedure(string spName, DataAccessParameterCollection parameters)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
return(broker.ExecuteProcReturnOutput(spName, parameters));
}
}
/// <summary>
/// 更新-事务支持 - 若为交易视图表则会根据最后修改时间自动计算表名。
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void Update(EntityBase entity, DataAccessBroker broker)
{
List <ColumnInfo> listUpdateColumnInfo = new List <ColumnInfo>();
foreach (ColumnInfo columnInfo in entity.OringTableSchema.ValueColumnInfo)
{
if (columnInfo.ColumnName == "CREATE_TIME" || columnInfo.ColumnName == "CREATED_BY" || columnInfo.ColumnName == "EFFECTIVE_DATE" || columnInfo.ColumnName == "EXPIRATION_DATE")
{
continue;
}
ColumnInfo updateColumnInfo = new ColumnInfo(columnInfo.ColumnName, columnInfo.ColumnCaption, columnInfo.IsPrimaryKey, columnInfo.DataType);
listUpdateColumnInfo.Add(updateColumnInfo);
}
string sqlString = "";
sqlString = "UPDATE " + entity.OringTableSchema.TableName + " SET " + ParseSQL(listUpdateColumnInfo, broker.ParameterPrefix, ",") + " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, broker.ParameterPrefix, " AND ");
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
object tpValue;
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
if (field.ColumnName == "CREATE_TIME" || field.ColumnName == "CREATED_BY" || field.ColumnName == "EFFECTIVE_DATE" || field.ColumnName == "EXPIRATION_DATE")
{
continue;
}
//dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName));
tpValue = entity.GetData(field.ColumnName);
if (tpValue == null)
{
if (field.DataType == typeof(bool))
{
tpValue = false;
}
else if (field.DataType == typeof(string))
{
tpValue = "";
}
else if (field.DataType == typeof(int))
{
tpValue = 0;
}
else if (field.DataType == typeof(decimal))
{
tpValue = 0;
}
else if (field.DataType == typeof(DateTime))
{
tpValue = DateTime.MaxValue;
}
else
{
tpValue = "";
}
}
dpc.AddWithValue(field, tpValue);
}
broker.ExecuteSQL(sqlString, dpc);
}
/// <summary>
/// 删除-事务支持-根据所有非空字段AND条件删除
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void DeleteEntity(EntityBase entity, DataAccessBroker broker)
{
WhereBuilder wb = new WhereBuilder("DELETE FROM " + entity.OringTableSchema.TableName);
wb.AddAndCondition(entity);
broker.ExecuteSQL(wb.SQLString, wb.Parameters);
}
/// <summary>
/// 根据状态保存-独立事务
/// </summary>
/// <param name="entity"></param>
public static void Save(EntityBase entity)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
Save(entity, broker);
}
}
public static void ExcuteNoneQuery(string sqlString, DataAccessParameterCollection para, CommandType type)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
broker.ExecuteNonQuery(sqlString, para, type);
}
}
public static void ExcuteNoneQuery(string sqlString)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
broker.ExecuteNonQuery(sqlString, null, CommandType.Text);
}
}
public static object SelectScalar(string sqlString, DataAccessParameterCollection paraCollection)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
return(broker.ExecuteScalar(sqlString, paraCollection, CommandType.Text));
}
}
public static int ExecuteStoreProcedure(string spName, DataAccessBroker broker)
{
int value = 0;
value = broker.ExecuteNonQuery(spName, null, CommandType.StoredProcedure);
return(value);
}
public static int ExecuteStoreProcedureHasReturn(string spName, DataAccessParameterCollection parameters)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
var rowsaffected = broker.ExecuteCommand(spName, parameters);
return(1);
}
}
/// <summary>
/// 批量新增-事务支持
/// 此方法不替换DateTime为NULL的情况
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="broker"></param>
/// <returns></returns>
public static void InsertEX <T>(List <T> list, DataAccessBroker broker)
where T : EntityBase
{
foreach (T entity in list)
{
InsertEX(entity, broker);
}
}
/// <summary>
/// 批量保存-事务支持
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="broker"></param>
/// <returns></returns>
public static void Save <T>(List <T> list, DataAccessBroker broker)
where T : EntityBase
{
foreach (T entity in list)
{
Save(entity, broker);
}
}
public static void InsertArea(int maxAreaID,AreaEntity areaEntity, DataAccessBroker broker)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append("Insert into " + AreaTable.Current.TableName + " (" + AreaTable.C_Area_ID + "," + AreaTable.C_Area_Name + "," + AreaTable.C_Area_Name2 + "," + AreaTable.C_Area_Level + "," + AreaTable.C_Area_PID + ")");
sbSql.Append(" Values(" + maxAreaID + ",'" + areaEntity.AreaName + "','" + areaEntity.AreaName2 + "'," + areaEntity.AreaLevel + "," + areaEntity.AreaPid + ")");
broker.ExecuteSQL(sbSql.ToString());
}
/// <summary>
/// 批量更新-事务支持
/// 此方法不更新DateTime为null的情况
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="broker"></param>
/// <returns></returns>
public static void UpdateEX <T>(IEnumerable <T> list, DataAccessBroker broker)
where T : EntityBase
{
foreach (T entity in list)
{
UpdateEX(entity, broker);
}
}
public static T SelectSingleByBroker <T>(string id, DataAccessBroker broker)
where T : EntityBase, new()
{
T t = new T();
t.SetData(t.OringTableSchema.KeyColumnInfo[0].ColumnName, id);
return(SelectSingleByBroker <T>(t, broker));
}
public static void DeleteAll <T>()
where T : EntityBase, new ()
{
using (DataAccessBroker broker = DataAccessFactory.Instance(InstanceName))
{
T t = new T();
broker.ExecuteSQL("delete from " + t.OringTableSchema.TableName);
}
}
public static DataSet Select(string queryString, string instanceName, DataAccessParameterCollection parameters)
{
DataSet ds = null;
using (DataAccessBroker broker = DataAccessFactory.Instance(instanceName))
{
ds = broker.FillSQLDataSet(queryString, parameters);
}
return(ds);
}
public static string SelectScalar(string sqlString)
{
object value = null;
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
value = broker.ExecuteSQLScalar(sqlString);
}
return(value == null ? null : value.ToString());
}
public static int ExecuteStoreProcedure(string spName)
{
int value = 0;
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
value = broker.ExecuteNonQuery(spName, null, CommandType.StoredProcedure);
}
return(value);
}
public static int ExecuteNoneQuery(string sqlString)
{
int value = 0;
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
value = broker.ExecuteNonQuery(sqlString, null, CommandType.Text);
}
return(value);
}
public static void UpdateProductCaseSetting(string productID, DataAccessBroker broker)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" update "+ProductTable.Current.TableName+" set "+ProductTable.C_Case_Package+" = ");
sbSql.Append(" (select cc." + CasecalculationTable.C_Case_Package + " from " + CasecalculationTable.Current.TableName + " cc ");
sbSql.Append(" inner join " + ProductTable.Current.TableName + " p on cc." + CasecalculationTable.C_CaseCalculation_ID + "=p." + ProductTable.C_CaseCalculation_ID );
sbSql.Append(" and p." + ProductTable.C_Product_ID + "='" + productID + "') ");
sbSql.Append(" where " + ProductTable.C_Product_ID + "='" + productID + "' ");
broker.ExecuteSQL(sbSql.ToString());
}
/// <summary>
/// 新增-事务支持
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void Insert(EntityBase entity, DataAccessBroker broker)
{
string sqlString = "INSERT INTO " + entity.OringTableSchema.TableName + " ( " + ParseInsertSQL(entity.OringTableSchema.AllColumnInfo, "") + " ) VALUES( " + ParseInsertSQL(entity.OringTableSchema.AllColumnInfo, ParameterPrefix) + ")";
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName));
}
broker.ExecuteSQL(sqlString, dpc);
}
/// <summary>
/// 删除-事务支持-根据主键删除
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void Delete(EntityBase entity, DataAccessBroker broker)
{
string sqlString = "DELETE FROM " + entity.OringTableSchema.TableName + " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, broker.ParameterPrefix, " AND ");
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (ColumnInfo field in entity.OringTableSchema.KeyColumnInfo)
{
dpc.AddWithValue(field, entity.GetData(field.ColumnName));
}
broker.ExecuteSQL(sqlString, dpc);
}
public static T SelectSingleByBroker <T>(T entity, DataAccessBroker broker)
where T : EntityBase, new()
{
T result = null;
EntityCollection <T> list = SelectByBroker <T>(entity, broker);
if (list != null && list.Count > 0)
{
result = list[0];
}
return(result);
}
/// <summary>
/// 删除用户
/// </summary>
/// <param name="userId"></param>
/// <param name="broker"></param>
public static void DeleteUser(string userId, DataAccessBroker broker)
{
DateTime time = DateTime.Now;
string now = time.Year.ToString() + "-" + time.Month.ToString() + "-" + time.Day.ToString() + " " + time.Hour.ToString() + "-" + time.Minute.ToString() + "-" + time.Second.ToString();
//string sql = "update {0} set {1}={2}, {3}=to_date('{4}','YYYY-MM-DD HH24-MI-SS') where {5}='{6}'";
string sql = "update {0} set {1}={2}, {3}=getdate() where {5}='{6}'";
sql = string.Format(sql, SecUserTable.C_TableName,
SecUserTable.C_IS_DELETE, YesNoType.Yes.GetHashCode(),
SecUserTable.C_LAST_MODIFIED_TIME, now,
SecUserTable.C_USER_ID, Utils.ToSQLStr(userId));
broker.ExecuteSQL(sql);
}
/// <summary>
/// ExecuteScalar 并返还回唯一值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="wb"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
public static T ExecuteScalar <T>(WhereBuilder wb, T defaultValue)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
object result = broker.ExecuteScalar(wb);
if (result != null && result != DBNull.Value)
{
defaultValue = (T)result;
}
}
return(defaultValue);
}
/// <summary>
/// 新增-事务支持-若为交易视图表则会根据最后修改时间自动计算表名。
/// 此方法不把时间为NULL的替换为最大时间
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void InsertEX(EntityBase entity, DataAccessBroker broker)
{
string sqlString = "";
sqlString = "INSERT INTO " + entity.OringTableSchema.TableName + " ( " + ParseInsertSQL(entity.OringTableSchema.NoIdentityColumnInfo, "") + " ) VALUES( " + ParseInsertSQL(entity.OringTableSchema.NoIdentityColumnInfo, broker.ParameterPrefix) + ")";
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
object tpValue;
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
if (field.IsIdentity)
{
continue;
}
//dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName));
tpValue = entity.GetData(field.ColumnName);
if (tpValue == null)
{
if (field.DataType == typeof(bool))
{
tpValue = false;
}
else if (field.DataType == typeof(string))
{
tpValue = "";
}
else if (field.DataType == typeof(int))
{
tpValue = 0;
}
else if (field.DataType == typeof(decimal))
{
tpValue = 0;
}
else if (field.DataType == typeof(DateTime))
{
//tpValue = DateTime.MaxValue;
}
else
{
tpValue = "";
}
}
if (field.DataType == typeof(DateTime) && tpValue != null && tpValue.ToString() == DateTime.MaxValue.ToString())
{
tpValue = null;
}
dpc.AddWithValue(field, tpValue);
}
broker.ExecuteSQL(sqlString, dpc);
}
public virtual void UpdateSubjectInformationEntity(SubjectInformationEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void InsertSubjectInformationEntity(SubjectInformationEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public virtual void UpdateStoreEntity(StoreEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void InsertStoreEntity(StoreEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public virtual void UpdateAreaEntity(AreaEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void InsertSecTodolistEntity(SecTodolistEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public virtual void InsertProductCategoryEntity(ProductCategoryEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public virtual void InsertSubjectLicenseVehiclsEntity(SubjectLicenseVehiclsEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public virtual void InsertSecRoleMenuScopeEntity(SecRoleMenuScopeEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public virtual void InsertContractEntity(ContractEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public virtual void UpdateReportEntity(ReportEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void InsertCasecalculationEntity(CasecalculationEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public virtual void UpdateCasecalculationEntity(CasecalculationEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void InsertCriminalDetailEntity(CriminalDetailEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public static void DeleteAllImportStore(DataAccessBroker broker)
{
string sql = "update store set IS_DELETED='Y' where STORE_TYPE='Import' and IS_DELETED='N'";
broker.ExecuteNonQuery(sql, null, CommandType.Text);
}
public virtual void UpdateCriminalDetailEntity(CriminalDetailEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void UpdateContractEntity(ContractEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void InsertOwnerEntity(OwnerEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public virtual void UpdateSecRoleMenuScopeEntity(SecRoleMenuScopeEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void UpdateOwnerEntity(OwnerEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void UpdateSubjectLicenseVehiclsEntity(SubjectLicenseVehiclsEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void InsertSecUserRoleEntity(SecUserRoleEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}
public virtual void UpdateSecTodolistEntity(SecTodolistEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void UpdateSecUserRoleEntity(SecUserRoleEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void UpdateProductCategoryEntity(ProductCategoryEntity entity, DataAccessBroker broker)
{
DataAccess.Update(entity, broker);
}
public virtual void InsertAreaEntity(AreaEntity entity, DataAccessBroker broker)
{
DataAccess.Insert(entity, broker);
}