/// <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); }
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> /// 删除-事务支持-根据所有非空字段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); }
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()); }
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); } }
/// <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 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="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> /// 新增-事务支持-若为交易视图表则会根据最后修改时间自动计算表名。 /// 此方法不把时间为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); }
/// <summary> /// 更新-事务支持 - 若为交易视图表则会根据最后修改时间自动计算表名。 /// </summary> /// <param name="entity"></param> /// <param name="broker"></param> public static void UpdateEX(EntityBase entity, DataAccessBroker broker) { string sqlString = ""; sqlString = "UPDATE " + entity.OringTableSchema.TableName + " SET " + ParseSQL(entity.OringTableSchema.ValueColumnInfo, broker.ParameterPrefix, ",") + " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, broker.ParameterPrefix, " AND "); DataAccessParameterCollection dpc = new DataAccessParameterCollection(); object tpValue; foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo) { //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); }
/// <summary> /// 新增-事务支持-若为交易视图表则会根据最后修改时间自动计算表名。 /// </summary> /// <param name="entity"></param> /// <param name="broker"></param> public static void Insert(EntityBase entity, DataAccessBroker broker) { ParameterPrefix = broker.ParameterPrefix.ToString(); string sqlString = ""; if (entity.OringTableSchema.TableName == TransactionProductView || entity.OringTableSchema.TableName == TransactionView) { sqlString = "INSERT INTO " + GetTableName(entity) + " ( " + ParseInsertSQL(entity.OringTableSchema.AllColumnInfo, "") + " ) VALUES( " + ParseInsertSQL(entity.OringTableSchema.AllColumnInfo, ParameterPrefix) + ")"; } else { 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 Update(EntityBase entity, DataAccessBroker broker) { string sqlString = "UPDATE " + entity.OringTableSchema.TableName + " SET "; DataAccessParameterCollection dpc = new DataAccessParameterCollection(); foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo) { if (field.IsPrimaryKey) { dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName)); } else if (entity.GetData(field.ColumnName) != null) { dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName)); sqlString += field.ColumnName + "=" + ParameterPrefix + field.ColumnName + ", "; } } sqlString = sqlString.Substring(0, sqlString.Length - 2); sqlString += " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, " AND "); broker.ExecuteSQL(sqlString, dpc); }
/// <summary> /// 更新-事务支持 - 若为交易视图表则会根据最后修改时间自动计算表名。 /// </summary> /// <param name="entity"></param> /// <param name="broker"></param> public static void Update(EntityBase entity, DataAccessBroker broker) { string sqlString = ""; ParameterPrefix = broker.ParameterPrefix.ToString(); 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)); } WriteSocketToFile(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss:fff")); WriteSocketToFile(sqlString); broker.ExecuteSQL(sqlString, dpc); }
public bool SaveUpLoadFile(UploadfileEntity upLoadFileEntity, DataAccessBroker objBroker) { string fileID = string.Empty; string actionNO = string.Empty; string target = string.Empty; string categoryID = string.Empty; string fileName = string.Empty; string fileType = string.Empty; string filePath = string.Empty; string uploadUserID = string.Empty; Suzsoft.Smart.EntityCore.DataAccessParameterCollection dpc = new Suzsoft.Smart.EntityCore.DataAccessParameterCollection(); dpc.AddWithValue("@fileID", upLoadFileEntity.FileId); dpc.AddWithValue("@actionNO", upLoadFileEntity.ActionNo); dpc.AddWithValue("@target", upLoadFileEntity.Target); dpc.AddWithValue("@categoryID", upLoadFileEntity.CategoryId); dpc.AddWithValue("@fileName", upLoadFileEntity.Filename); dpc.AddWithValue("@fileType", upLoadFileEntity.Filetype); dpc.AddWithValue("@filePath", upLoadFileEntity.Filepath); dpc.AddWithValue("@uploadUserID", upLoadFileEntity.UploadUserId); string strSql = @" INSERT INTO UploadFile ( File_ID,Action_NO,Target,Category_ID,FileName,FileType,FilePath,Upload_User_ID,Upload_Date ) Values (@fileID ,@actionNO,@target,@categoryID,@fileName,@fileType,@filePath, @uploadUserID,'" + upLoadFileEntity.UploadDate + "' ) "; try { objBroker.ExecuteSQL(strSql, dpc); return true; } catch { return false; } }