/// <summary>
/// 新增设备布局。
/// </summary>
/// <param name="dsParams">包含设备布局数据的数据集对象。</param>
/// <returns>包含执行结果的数据集对象。
/// ExtendedProperties：OUTPUT_MESSAGE
/// </returns>
public DataSet InsertEquipmentLayout(DataSet dsParams)
{
DataSet dsReturn = new DataSet();
DbConnection dbCon = db.CreateConnection();
DbTransaction dbTrans = null;
if (dsParams != null && dsParams.Tables.Count > 0 && dsParams.Tables.Contains(EMS_LAYOUT_MAIN_FIELDS.DATABASE_TABLE_NAME))
{
DataTable dtParams = dsParams.Tables[EMS_LAYOUT_MAIN_FIELDS.DATABASE_TABLE_NAME];
try
{
dbCon.Open();
DbCommand dbCmd = dbCon.CreateCommand();
dbTrans = dbCon.BeginTransaction();
dbCmd.Transaction = dbTrans;
string layoutKey = string.Empty;
string sql = string.Empty;
if (dtParams.Rows.Count > 0)
{
#region 写入主表信息
byte[] data = (byte[])dtParams.Rows[0][EMS_LAYOUT_MAIN_FIELDS.LAYOUT_PIC];
layoutKey = dtParams.Rows[0][EMS_LAYOUT_MAIN_FIELDS.LAYOUT_KEY].ToString();
//执行插入语句，插入一个空对象empty_blob()
sql = string.Format(@"INSERT INTO EMS_LAYOUT_MAIN(LAYOUT_KEY,LAYOUT_NAME,LAYOUT_DESC,LAYOUT_PIC,
STATE_FLAG,CREATE_TIME,CREATOR)
VALUES('{0}','{1}','{2}',@t,'0',GETDATE(),'{3}')",
layoutKey.PreventSQLInjection(),
Convert.ToString(dtParams.Rows[0][EMS_LAYOUT_MAIN_FIELDS.LAYOUT_NAME]).PreventSQLInjection(),
Convert.ToString(dtParams.Rows[0][EMS_LAYOUT_MAIN_FIELDS.LAYOUT_DESC]).PreventSQLInjection(),
Convert.ToString(dtParams.Rows[0][EMS_LAYOUT_MAIN_FIELDS.CREATOR]).PreventSQLInjection());
dbCmd.CommandText = sql;
db.AddInParameter(dbCmd, "t", DbType.Binary, data);
dbCmd.ExecuteNonQuery();
dbCmd.Parameters.Clear();
#endregion
#region 写入明细数据
if (dsParams.Tables.Contains(EMS_LAYOUT_DETAIL_FIELDS.DATABASE_TABLE_NAME))
{
EMS_LAYOUT_DETAIL_FIELDS detailField = new EMS_LAYOUT_DETAIL_FIELDS();
DataTable detailTable = dsParams.Tables[EMS_LAYOUT_DETAIL_FIELDS.DATABASE_TABLE_NAME];
for (int i = 0; i < detailTable.Rows.Count; i++)
{
string sqlCommand = DatabaseTable.BuildInsertSqlStatement(detailField, detailTable, i);
dbCmd.CommandText = sqlCommand;
dbCmd.ExecuteNonQuery();
}
}
#endregion
dbTrans.Commit();
dsReturn.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
dsReturn.ExtendedProperties.Add(PARAMETERS.INPUT_KEY, layoutKey);
}
}
catch (Exception ex)
{
if (dbTrans != null)
{
dbTrans.Rollback();
}
dsReturn.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("InsertEquipmentLayout Error: " + ex.Message);
}
finally
{
dbTrans.Dispose();
if (dbCon.State == ConnectionState.Open)
{
dbCon.Close();
}
dbCon.Dispose();
}
}
else
{
dsReturn.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(dsReturn);
}
/// <summary>
/// 更新设备布局。
/// </summary>
/// <param name="dsParams">包含设备布局数据的数据集对象。</param>
/// <returns>包含执行结果的数据集对象。
/// ExtendedProperties：OUTPUT_MESSAGE
/// </returns>
public DataSet UpdateEquipmentLayout(DataSet dsParams)
{
DataSet dsReturn = new DataSet();
if (dsParams != null && dsParams.Tables.Count > 0 && dsParams.Tables.Contains(EMS_LAYOUT_DETAIL_FIELDS.DATABASE_TABLE_NAME))
{
DbConnection dbConn = null;
DbTransaction dbTran = null;
try
{
dbConn = db.CreateConnection();
dbConn.Open();
dbTran = dbConn.BeginTransaction();
string layoutkey = dsParams.ExtendedProperties[EMS_LAYOUT_DETAIL_FIELDS.LAYOUT_KEY].ToString();
string editor = dsParams.ExtendedProperties[EMS_LAYOUT_DETAIL_FIELDS.EDITOR].ToString();
DataTable detailTable = dsParams.Tables[EMS_LAYOUT_DETAIL_FIELDS.DATABASE_TABLE_NAME];
EMS_LAYOUT_DETAIL_FIELDS detailField = new EMS_LAYOUT_DETAIL_FIELDS();
string equipmentkey = string.Empty;
//-----------------------------------------记录删除的数据---------------------------------
string s = string.Format(@"SELECT T.* FROM EMS_LAYOUT_DETAIL T WHERE T.LAYOUT_KEY = '{0}' AND FLAG=0 ",
layoutkey.PreventSQLInjection());
DataTable dt = db.ExecuteDataSet(CommandType.Text, s).Tables[0];
foreach (DataRow dr in dt.Rows)
{
equipmentkey = dr[EMS_LAYOUT_DETAIL_FIELDS.EQUIPMENT_KEY].ToString();
DataRow[] drs = detailTable.Select(string.Format("EQUIPMENT_KEY='{0}'", equipmentkey));
string s02 = string.Format(@"UPDATE EMS_LAYOUT_DETAIL
SET FLAG = 1, EDITOR = '{2}', EDIT_TIME = GETDATE()
WHERE LAYOUT_KEY = '{0}' AND EQUIPMENT_KEY = '{1}'",
layoutkey.PreventSQLInjection(),
equipmentkey.PreventSQLInjection(),
editor.PreventSQLInjection());
string s03 = string.Format(@"DELETE FROM EMS_LAYOUT_DETAIL WHERE LAYOUT_KEY = '{0}' AND EQUIPMENT_KEY = '{1}'",
layoutkey.PreventSQLInjection(), equipmentkey.PreventSQLInjection());
if (drs != null && drs.Length > 0)
{
db.ExecuteNonQuery(dbTran, CommandType.Text, s03);
}
else
{
db.ExecuteNonQuery(dbTran, CommandType.Text, s02);
}
}
//插入新记录
for (int i = 0; i < detailTable.Rows.Count; i++)
{
string sqlCommand = DatabaseTable.BuildInsertSqlStatement(detailField, detailTable, i);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
dbTran.Commit();
dsReturn.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
}
catch (Exception ex)
{
if (dbTran != null)
{
dbTran.Rollback();
}
dsReturn.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("UpdateEquipmentLayout Error:" + ex.Message);
}
finally
{
dbTran.Dispose();
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
dbConn.Dispose();
}
}
else
{
dsReturn.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据为空");
}
return(dsReturn);
}
