/// <summary> /// Get devices from database. /// </summary> /// <param name="lscId">lscId</param> /// <param name="devId">devId</param> /// <returns>devices list</returns> public List <DevInfo> GetDevices(Int32 lscId, Int32 devId) { SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int), new SqlParameter("@DevID", SqlDbType.Int) }; parms[0].Value = lscId; parms[1].Value = ComUtility.DBNullInt32Checker(devId); var devices = new List <DevInfo>(); using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SqlText.SQL_SELECT_COMMON_GETDEVICES, parms)) { while (rdr.Read()) { var dev = new DevInfo(); dev.LscID = ComUtility.DBNullInt32Handler(rdr["LscID"]); dev.DevID = ComUtility.DBNullInt32Handler(rdr["DevID"]); dev.DevName = ComUtility.DBNullStringHandler(rdr["DevName"]); dev.DevDesc = ComUtility.DBNullStringHandler(rdr["DevDesc"]); dev.StaID = ComUtility.DBNullInt32Handler(rdr["StaID"]); devices.Add(dev); } } return(devices); }
/// <summary> /// Method to add all the reservation information /// </summary> public void AddReservations(string connectionString, List <BookingInfo> bookings) { SqlParameter[] parms1 = { new SqlParameter("@LscID", SqlDbType.Int), new SqlParameter("@ProjID", SqlDbType.VarChar, 50) }; SqlParameter[] parms2 = { new SqlParameter("@BookingUserID", SqlDbType.Int), new SqlParameter("@ProjID", SqlDbType.VarChar, 50), new SqlParameter("@ProjName", SqlDbType.VarChar, 100), new SqlParameter("@ProjDesc", SqlDbType.VarChar, 200), new SqlParameter("@LscIncluded", SqlDbType.Int), new SqlParameter("@StaIncluded", SqlDbType.VarChar), new SqlParameter("@DevIncluded", SqlDbType.VarChar), new SqlParameter("@StartTime", SqlDbType.DateTime), new SqlParameter("@EndTime", SqlDbType.DateTime), new SqlParameter("@ProjStatus", SqlDbType.Int), new SqlParameter("@IsComfirmed", SqlDbType.Bit), new SqlParameter("@ComfirmedUserID", SqlDbType.Int), new SqlParameter("@ComfirmedTime", SqlDbType.DateTime), new SqlParameter("@IsChanged", SqlDbType.Bit), new SqlParameter("@BookingTime", SqlDbType.DateTime) }; SqlHelper.TestConnection(connectionString); using (var conn = new SqlConnection(connectionString)) { if (conn.State != ConnectionState.Open) { conn.Open(); } var trans = conn.BeginTransaction(IsolationLevel.ReadCommitted); try { foreach (var booking in bookings) { parms1[0].Value = ComUtility.DBNullInt32Checker(booking.LscId); parms1[1].Value = ComUtility.DBNullString2Checker(booking.Id); SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SqlText.SQL_SELECT_LSC_DELETERESERVATION, parms1); foreach (var project in booking.Projects) { parms2[0].Value = ComUtility.DBNullInt32Checker(project.BookingUserID); parms2[1].Value = ComUtility.DBNullString2Checker(project.ProjID); parms2[2].Value = ComUtility.DBNullString2Checker(project.ProjName); parms2[3].Value = ComUtility.DBNullString2Checker(project.ProjDesc); parms2[4].Value = ComUtility.DBNullInt32Checker(project.LscIncluded); parms2[5].Value = ComUtility.DBNullString2Checker(project.StaIncluded); parms2[6].Value = ComUtility.DBNullString2Checker(project.DevIncluded); parms2[7].Value = ComUtility.DBNullDateTimeChecker(project.StartTime); parms2[8].Value = ComUtility.DBNullDateTimeChecker(project.EndTime); parms2[9].Value = ComUtility.DBNullInt32Checker(project.ProjStatus); parms2[10].Value = project.IsComfirmed; parms2[11].Value = ComUtility.DBNullInt32Checker(project.ComfirmedUserID); parms2[12].Value = ComUtility.DBNullDateTimeChecker(project.ComfirmedTime); parms2[13].Value = project.IsChanged; parms2[14].Value = ComUtility.DBNullDateTimeChecker(project.BookingTime); SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SqlText.SQL_SELECT_LSC_ADDRESERVATION, parms2); } } trans.Commit(); } catch { trans.Rollback(); throw; } } }
/// <summary> /// Method to save trend alarm information /// </summary> /// <param name="alarms">alarms</param> public void SaveTrendAlarms(List <TrendAlarmInfo> alarms) { using (var conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction)) { conn.Open(); using (var trans = conn.BeginTransaction(IsolationLevel.ReadCommitted)) { try { SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int), new SqlParameter("@Area1Name", SqlDbType.NVarChar, 50), new SqlParameter("@Area2Name", SqlDbType.NVarChar, 50), new SqlParameter("@Area3Name", SqlDbType.NVarChar, 50), new SqlParameter("@Area4Name", SqlDbType.NVarChar, 50), new SqlParameter("@StaName", SqlDbType.NVarChar, 100), new SqlParameter("@DevName", SqlDbType.NVarChar, 100), new SqlParameter("@NodeID", SqlDbType.Int), new SqlParameter("@NodeType", SqlDbType.Int), new SqlParameter("@NodeName", SqlDbType.NVarChar, 50), new SqlParameter("@AlarmType", SqlDbType.NVarChar, 20), new SqlParameter("@AlarmStatus", SqlDbType.Int), new SqlParameter("@AlarmLevel", SqlDbType.Int), new SqlParameter("@StartValue", SqlDbType.Real), new SqlParameter("@AlarmValue", SqlDbType.Real), new SqlParameter("@DiffValue", SqlDbType.Real), new SqlParameter("@StartTime", SqlDbType.DateTime), new SqlParameter("@AlarmTime", SqlDbType.DateTime), new SqlParameter("@EventTime", SqlDbType.DateTime), new SqlParameter("@ConfirmName", SqlDbType.NVarChar, 20), new SqlParameter("@ConfirmTime", SqlDbType.DateTime), new SqlParameter("@EndName", SqlDbType.NVarChar, 20), new SqlParameter("@EndTime", SqlDbType.DateTime), new SqlParameter("@StartIsAddAlarmList", SqlDbType.Bit), new SqlParameter("@EndIsAddAlarmList", SqlDbType.Bit), new SqlParameter("@ConfirmIsAddAlarmList", SqlDbType.Bit) }; foreach (var alarm in alarms) { parms[0].Value = alarm.LscID; parms[1].Value = ComUtility.DBNullStringChecker(alarm.Area1Name); parms[2].Value = ComUtility.DBNullStringChecker(alarm.Area2Name); parms[3].Value = ComUtility.DBNullStringChecker(alarm.Area3Name); parms[4].Value = ComUtility.DBNullStringChecker(alarm.Area4Name); parms[5].Value = ComUtility.DBNullStringChecker(alarm.StaName); parms[6].Value = ComUtility.DBNullStringChecker(alarm.DevName); parms[7].Value = alarm.NodeID; parms[8].Value = alarm.NodeType; parms[9].Value = ComUtility.DBNullStringChecker(alarm.NodeName); parms[10].Value = ComUtility.DBNullStringChecker(alarm.AlarmType); parms[11].Value = ComUtility.DBNullInt32Checker(alarm.AlarmStatus); parms[12].Value = ComUtility.DBNullInt32Checker(alarm.AlarmLevel); parms[13].Value = ComUtility.DBNullFloatHandler(alarm.StartValue); parms[14].Value = ComUtility.DBNullFloatHandler(alarm.AlarmValue); parms[15].Value = ComUtility.DBNullFloatHandler(alarm.DiffValue); parms[16].Value = ComUtility.DBNullDateTimeChecker(alarm.StartTime); parms[17].Value = ComUtility.DBNullDateTimeChecker(alarm.AlarmTime); parms[18].Value = ComUtility.DBNullDateTimeChecker(alarm.EventTime); parms[19].Value = ComUtility.DBNullStringChecker(alarm.ConfirmName); parms[20].Value = ComUtility.DBNullDateTimeChecker(alarm.ConfirmTime); parms[21].Value = ComUtility.DBNullStringChecker(alarm.EndName); parms[22].Value = ComUtility.DBNullDateTimeChecker(alarm.EndTime); parms[23].Value = alarm.StartIsAddAlarmList; parms[24].Value = alarm.EndIsAddAlarmList; parms[25].Value = alarm.ConfirmIsAddAlarmList; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SqlText.SQL_INSERT_ALARM_SAVETRENDALARMS, parms); } trans.Commit(); } catch { trans.Rollback(); throw; } } } }