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