public TesterTypeCollection generateTesterTable(TestPassTesterRoles tester) { TesterTypeCollection TesterTypeDataTable = new TesterTypeCollection(); List <TesterType> _lst1 = new List <TesterType>(); if (tester != null) { if (tester.roleArray != null) { if (tester.action.ToLower() == "edit") { _lst1 = getTesterByUserIdTestPassId(tester.oldTesterspUserId, tester.testPassId); } foreach (var item in _lst1) { string _roleId = item.Role_ID.ToString(); if (tester.roleArray.IndexOf(_roleId) == -1) { continue; } ClsTesterTypeDataTable dr = new ClsTesterTypeDataTable(); dr.Tester_ID = item.Tester_ID; dr.TestPass_ID = (tester.testPassId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.testPassId); dr.Area_ID = (tester.areaId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.areaId); dr.Role_ID = item.Role_ID; if (tester.action.ToLower() == "edit") { dr.User_ID = (tester.oldTesterspUserId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.oldTesterspUserId); } else { dr.User_ID = (tester.spUserId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.spUserId); } TesterTypeDataTable.Add(dr); /*old record added to datatable*/ } int flagTesterId = 0;// counter for rowid for (int i = 0; i <= tester.roleArray.Count - 1; i++) { if (tester.action.ToLower() == "edit") {/*checking if new tester role exists for old tester*/ List <TesterType> _dr1 = new List <TesterType>(); //_dr1= _lst1.Select( "Role_ID = " + tester.roleArray[i]); _dr1 = (from x in _lst1 where x.Role_ID == Convert.ToInt32(tester.roleArray[i]) select x).ToList(); if (tester.spUserId.Trim() == tester.oldTesterspUserId.Trim()) { if (_dr1.Count > 0)//a new record is not inserted if same record is changed { continue; } } } ClsTesterTypeDataTable dr = new ClsTesterTypeDataTable(); dr.Tester_ID = (tester.testerID == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.testerID); if (string.IsNullOrEmpty(tester.testerID) && tester.spUserId.Trim() != tester.oldTesterspUserId.Trim()) {/*block to execute when tester has changed*/ string whClause = "t.TestPass_ID =" + tester.testPassId + " and t.User_ID =" + tester.oldTesterspUserId + " and t.role_id =" + tester.roleArray[i]; try { //getting testerId of old tester in case of tester update so that old tester gets replaced with new tester details, role is considered here string resultVal = GetSingleValue("Testers t", "t.Tester_ID", whClause, null); if (string.IsNullOrEmpty(resultVal)) { resultVal = "0"; if (tester.action.ToLower() == "edit") { List <TesterType> _dr2 = new List <TesterType>(); _dr2 = (from x in _lst1 where x.Role_ID == Convert.ToInt32(tester.roleArray[i]) select x).ToList(); //DataRow[] dataRowTester = dtEditTester.Select("Role_ID = " + tester.roleArray[i]); if (_dr2.Count == 0)//a new record is not inserted if same record is changed { // role is not considered here, tester id is fetched by rowid from database whClause = "TestPass_ID = " + tester.testPassId + " and User_ID = " + tester.oldTesterspUserId + ")as temp where row=" + (flagTesterId + 1); resultVal = GetSingleValue("Testers", "Tester_ID from(select Tester_ID ,row_number() over(order by tester_id) as 'row'", whClause, ""); flagTesterId++;// counter for rowid } } dr.Tester_ID = Convert.ToInt32(resultVal);//can be repetative, which is set to '0' in code block below } else { dr.Tester_ID = Convert.ToInt32(resultVal); } } catch (Exception e) { } } dr.TestPass_ID = (tester.testPassId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.testPassId); dr.Area_ID = (tester.areaId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.areaId); dr.Role_ID = Convert.ToInt32(tester.roleArray[i]); dr.User_ID = (tester.spUserId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.spUserId); TesterTypeDataTable.Add(dr); } } // r if (tester.action.ToLower() == "edit") { if (tester.spUserId.Trim() != tester.oldTesterspUserId.Trim()) { //dtTester = dtTester.AsEnumerable().OrderBy(z => z.Field<int>("Tester_Id")).OrderBy(z => z.Field<int>("Role_Id")).OrderBy(z => z.Field<int>("User_Id")).CopyToDataTable(); Dictionary <int, int> oldData = new Dictionary <int, int>(); // IEnumerable<DataRow> rows = from row in TesterTypeDataTable.AsQueryable() select row; foreach (var row in TesterTypeDataTable) {/*getting old testers' data for further use in dictionary*/ if (row.User_ID.ToString().Trim() == tester.oldTesterspUserId.Trim()) { oldData.Add(row.Tester_ID, row.Role_ID); } } foreach (var row in TesterTypeDataTable) { if (row.User_ID.ToString().Trim() == tester.spUserId.Trim())/*processing datatable for new user only*/ { foreach (KeyValuePair <int, int> pair in oldData) { int oldval = pair.Value; int oldKey = pair.Key; if (row.Tester_ID == oldKey) { if (row.Role_ID != oldval) { /*if new users testerID + roleId combination is not same as old tester's combination then set new users tester id as '0' to treat it as new record to be inserted*/ /*if roleId + testerId combination is matched then no change in datatable here, data is updated in sp*/ row.Tester_ID = 0; } } } } } //dtTester = dtTester.AsEnumerable().OrderBy(z => z.Field<int>("RowID")).CopyToDataTable(); /*to bring dttester in original sorting as received*/ } } } return(TesterTypeDataTable); }
public Dictionary <string, string> InsertUpdateTester([FromBody] TestPassTesterRoles tester) { Dictionary <string, string> _result = new Dictionary <string, string>(); try { string SpUserId = HttpContext.Request.Headers["LoggedInUserSPUserId"]; if (string.IsNullOrEmpty(tester.oldTesterspUserId)) { tester.oldTesterspUserId = tester.spUserId; } if (string.IsNullOrEmpty(tester.spUserId)) { _result.Add(this._errorText, "spUserId is required"); return(_result); } string AppUrl = HttpContext.Request.Headers["appurl"]; string SchemaName = ""; if (!string.IsNullOrEmpty(AppUrl)) { SchemaName = new clsUatClient(_context).GetClientSchema(AppUrl); } else { _result.Add(this._errorText, "Invalid Appurl"); return(_result); } TesterTypeCollection TesterTypeDataTable = new TesterTypeCollection(); TesterTypeDataTable = generateTesterTable(tester); using (var cmd = _context.Database.GetDbConnection().CreateCommand()) { string statementType = string.Empty; if (tester.action.ToLower() == "add") { statementType = "Insert"; } else { statementType = "Update"; } cmd.CommandText = "UAT.spTester"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@TesterTypeCollection", SqlDbType.Structured) { Value = TesterTypeDataTable }); cmd.Parameters.Add(new SqlParameter("@TestPassId", SqlDbType.Int) { Value = Convert.ToInt32(tester.testPassId) }); cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 500) { Value = tester.testerName }); cmd.Parameters.Add(new SqlParameter("@UserAlias", SqlDbType.VarChar, 500) { Value = tester.testerAlias }); cmd.Parameters.Add(new SqlParameter("@UserEmailId", SqlDbType.VarChar, 500) { Value = tester.testerEmail }); cmd.Parameters.Add(new SqlParameter("@SPUserId", SqlDbType.Int) { Value = tester.spUserId }); cmd.Parameters.Add(new SqlParameter("@OldSPUserId", SqlDbType.Int) { Value = Convert.ToInt32(tester.oldTesterspUserId) }); cmd.Parameters.Add(new SqlParameter("@UserCId", SqlDbType.Int) { Value = Convert.ToInt32(SpUserId) }); cmd.Parameters.Add(new SqlParameter("@StatementType", SqlDbType.NVarChar, 500) { Value = statementType }); SqlParameter outparam = new SqlParameter("@Ret_Parameter", SqlDbType.VarChar, 500) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(new SqlParameter("@SchemaName", SqlDbType.NVarChar, 500) { Value = SchemaName }); cmd.Parameters.Add(outparam); if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); } int retValPos = cmd.ExecuteNonQuery(); string ReturnParamValue = Convert.ToString(cmd.Parameters["@Ret_Parameter"].Value); if (!string.IsNullOrEmpty(ReturnParamValue) || ReturnParamValue.ToLower() == "success") { if (string.IsNullOrEmpty(tester.testPassId)) { _result.Add("roleId", cmd.Parameters[cmd.Parameters.Count - 1].Value.ToString()); _result.Add(this._statusText, "Done"); } else { _result.Add(this._statusText, "Done"); } } else { List <System.Data.Common.DbParameter> _outParameter = new List <System.Data.Common.DbParameter>(); foreach (System.Data.Common.DbParameter outP in _outParameter) { if (outP.Direction == ParameterDirection.Output) { _outParameter.Add(outP); } } _result.Add(this._errorText, "Error "); } } } catch (Exception ex) { _result.Add(this._errorText, ex.Message); } return(_result); }