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