public UserFiltersOpsmgrDto Get(Int32 pId) { var result = new UserFiltersOpsmgrDto(); string sql = "select id, user_id, descr, filter_expr " + "from " + DBUtils.SCHEMA_NAME + "v_user_filters " + "where id = @id "; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = pId; conn.Open(); using (SqlDataReader dataReader = cmd.ExecuteReader()) { if (dataReader.HasRows) { while (dataReader.Read()) { result.Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString()); result.UserId = dataReader["user_id"].ToString(); result.Descr = dataReader["descr"].ToString(); result.FilterExpr = dataReader["filter_expr"].ToString(); } } } } } return(result); }
public Int32 Update(UserFiltersOpsmgrDto pData) { Int32 rowsUpdated = 0; string sql = "update " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " + "set user_id = @user_id, descr = @descr, filter_expr = @filter_expr " + " where id = @id"; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = pData.Id; cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId); cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr); cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
public Int32 Insert(UserFiltersOpsmgrDto pData) { Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME); string sql = "Insert into " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " + " (id, user_id, descr, filter_expr) " + " Values " + " (@id, @user_id, @descr, @filter_expr) "; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = newId; cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId); cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr); cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr); conn.Open(); cmd.ExecuteNonQuery(); } } return(newId); }
public Int32 Update(UserFiltersOpsmgrDto pData) { Int32 rowsUpdated = 0; string sql = "update " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " + "set user_id = @user_id, descr = @descr, filter_expr = @filter_expr " + " where id = @id"; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = pData.Id; cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId); cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr); cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return rowsUpdated; }
public Int32 Insert(UserFiltersOpsmgrDto pData) { Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME); string sql = "Insert into " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " + " (id, user_id, descr, filter_expr) " + " Values " + " (@id, @user_id, @descr, @filter_expr) "; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = newId; cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId); cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr); cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr); conn.Open(); cmd.ExecuteNonQuery(); } } return newId; }
public UserFiltersOpsmgrDto Get(Int32 pId) { var result = new UserFiltersOpsmgrDto(); string sql = "select id, user_id, descr, filter_expr " + "from " + DBUtils.SCHEMA_NAME + "v_user_filters " + "where id = @id "; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = pId; conn.Open(); using (SqlDataReader dataReader = cmd.ExecuteReader()) { if (dataReader.HasRows) { while (dataReader.Read()) { result.Id = DBUtils.HandleInt32IfNull(dataReader["id"].ToString()); result.UserId = dataReader["user_id"].ToString(); result.Descr = dataReader["descr"].ToString(); result.FilterExpr = dataReader["filter_expr"].ToString(); } } } } } return result; }
public void Test_UserFiltersOpsmgrDal() { messageSeqNo = 0; UserFiltersOpsmgrDal userFiltersDal = new UserFiltersOpsmgrDal(sqlConnectionString); List<UserFiltersOpsmgrDto> userFiltersList = new List<UserFiltersOpsmgrDto>(); userFiltersList = userFiltersDal.GetAll("IFRANKEL"); //expectedCount = 6; //actualCount = userFiltersList.Count; Assert.AreEqual(expectedCount, actualCount, "Selected list count."); expectedValue = "Need Contracts Assigned"; foreach (UserFiltersOpsmgrDto filterData in userFiltersList) { if (filterData.Id == 9002) { actualValue = filterData.Descr; } } Assert.AreEqual(expectedValue, actualValue, "Selected Filter Description."); UserFiltersOpsmgrDto userFiltersData = new UserFiltersOpsmgrDto(); userFiltersData.UserId = "IFRANKEL"; userFiltersData.Descr = "TEST UNIT 01"; userFiltersData.FilterExpr = "[SetcMeth] = 'Our Paper' And [SetcStatus] = 'OK_TO_SEND'"; Int32 filterId = 0; filterId = userFiltersDal.Insert(userFiltersData); Assert.AreNotEqual(0, filterId, "Row was inserted"); userFiltersData = userFiltersDal.Get(filterId); expectedValue = "TEST UNIT 01"; actualValue = userFiltersData.Descr; Assert.AreEqual(expectedValue, actualValue, "Inserted Filter Row."); userFiltersData.Id = filterId; userFiltersData.UserId = "IFRANKEL"; userFiltersData.Descr = "TEST UNIT 99"; userFiltersData.FilterExpr = "[BkrMeth] = 'Broker Paper'"; int rowsUpdated = 0; rowsUpdated = userFiltersDal.Update(userFiltersData); Assert.AreEqual(1, rowsUpdated, "User Filter Row Updated."); userFiltersData = userFiltersDal.Get(filterId); expectedValue = "TEST UNIT 99"; actualValue = userFiltersData.Descr; Assert.AreEqual(expectedValue, actualValue, "User Filter Descr Updated."); expectedValue = "[BkrMeth] = 'Broker Paper'"; actualValue = userFiltersData.FilterExpr; Assert.AreEqual(expectedValue, actualValue, "User Filter FilterExpr Updated."); int rowsDeleted = 0; rowsDeleted = userFiltersDal.Delete(filterId); Assert.AreEqual(1, rowsDeleted, "User Filter Row Deleted."); userFiltersData = userFiltersDal.Get(10); Assert.AreEqual(0, userFiltersData.Id, "User Filter Row Successfully Deleted."); }