none
Fields not displaying in a view (or displaying a weird date format RRS feed

  • Question

  • Someone else wrote the code and I'm asked to look after it.

    OK - there is a simple web page on a c#.NET website.
    It ~should~ be based on a view called "[SVR.Database].[dbo].[EmployeeAttendeesFromOutsideUk]"

    At to a certain extent it is - if you, for example, change the view, the the many hundreds of other objects that c#sharp pointlessly forces you to change, then the changes you make, are reflected in the view.

    As an example, because I'm having issues with a Visit Date column, I got shot of it, and added two columns instead "Visit Date Convert" and "Visit Date No Convert".
    In SQL, the view displays correctly ALL the data:
    Id, SiteId, Employee Name, - every field, is populated, and populated correctly.

    C~ seems to be mucking it up:
    Doesn't display anything Except Id, Upin, Subsidary, and Days Visited.
    All the other fields are missing. At one stage it displayed the vist date (incorrectly as 01.01.0001 01:01:01:01) instead of the actual visit date as displayed in the view.

    The code that populates it is almost impossible to interpret, its like this:

    return base.GetListItems(siteId, options, _reportsRepository.GetVisitorsFromOutsideUK,
                    new Expression<Func<NonUkAttendee, dynamic>>[]
                    {
                        x => x.SiteId,
                        x => x.FullName,
                        x => x.VisitDateConvert,
                        x => x.VisitDateNoConvert,
                        //x => DateTime.Now.ToShortTimeString(),
                        x => x.Upin,
                        x => x.Organisation,
                        x => x.Subsidiary,
                        x => x.Hospitality,
                        x => x.CostCentre,
                        x => x.DaysVisited
                    },
                    SVRUserRole.Roles.Facilities);

    i.e Complete gibberish.

    Anyway care to explain what x is? 
    Any ideas why C Sharp can't perform the basic task "display me data in an SQL view". I've done all the work for it.
    Here's some more of the code. There is endless streams of it, all over the place, typically making it very difficult to work with:

    public ActionResult InlandRevenue(string siteId, OptionsViewModel options)
            {
                const string dateColumn = "Visit Date No Convert";
                const string dateColumn2 = "Visit Date No Convert";
               // DateTime dateColumn2 = Convert.ToDateTime("Visit Date");
                //string dateColumn = "Visit Date";

                options.CurrentSiteId = options.CurrentSiteId ?? siteId;
                options.LinkToController = "People";
                options.LinkToAction = "Person";
                options.Title = "Title of view";
                options.Filters.Clear();

                var taxYearSelection = DateRangeQuickSelection.CurrentUKTaxYear();

                var dateRangeFilter = new DateRangeFilterProvider
                {
                    PropertyName = dateColumn,
                    //PropertyName = dateColumn2,
                    //PropertyName = Convert.ToDateTime(dateColumn),
                    DefaultDateFrom = taxYearSelection.DateFrom,
                    DefaultDateTo = taxYearSelection.DateTo
                };

                dateRangeFilter.DateRangeQuickSelections.AddRange(
                    new[]
                    {
                        taxYearSelection
                    });

                options.Filters.Add(dateRangeFilter);

                if (string.IsNullOrEmpty(options.SortOn))
                {
                    options.SortOn = dateColumn;
                    options.SortOn = dateColumn.ToString();
                    options.SortOn = dateColumn;
                    options.SortAscending = false;
                    //options.
                }

    Monday, November 11, 2019 3:57 PM

Answers

  • If the properties aren't set then that is a mapping problem inside that Query<T> call. Since that code is not framework code you're going to have to go digging. Most likely it is using a simple naming convention (e.g. 'Name' column maps to the 'Name' property). If they don't match then it won't set it. Most "mapping" frameworks provide options to allow you to handle this manually. For example EF allows you to use the `Column` attribute to map to an arbitrary column.

    If this is the only issue you have then I'd just fix the mapping. If however you have lots more issues with this code then I'd lean toward switching to something that is easier like EF or nHibernate. This would be a huge undertaking if you have lots of DB calls but ultimately it would simplify your code. Perhaps consider a refactoring into your project schedule if you have time or add it to your backlog later. Since your code clearly knows what it is querying for (given the generic type) having a generic query engine seems overkill to me.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, November 12, 2019 4:33 PM
    Moderator

All replies

  • I would suggest using the following forum as this forum is for C# specific questions

    https://forums.asp.net/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, November 11, 2019 6:03 PM
    Moderator
  • And the code's in C#Sharp and not working?
    Tuesday, November 12, 2019 9:03 AM
  • Hi SSIS_Newbie_1234,

    According to your description, it’s hard to tell where it went wrong in your code.

    I recommend you should ask this question at ASP.NET forums where you can get better chance to solve the problem.

    Also, please provide the complete related View and Controller code which can make it easier for the community to help.

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 12, 2019 9:06 AM
    Moderator
  • The whole use of Expression is overarchitected. Not sure who wrote that code but it can be replaced with a lot simpler code. Unfortunately it completely depends upon what data access library you're using.

    If you want to determine whether the issue is a C# or MVC issue (and hence where to post) then put a breakpoint on the line of code where you retrieve the data from the DB and put it into your business objects. If the data is correct in your business objects then the C# code is fine. The problem lies in the MVC (which is posted in the ASP.NET forums). If the business objects are wrong then either your query or C# code is messed up or the DB doesn't have the data you think it does. Both are easy to confirm. We're not going to be able to help with most of the C# code because what you posted is calling types that are defined in your code so we have no way of knowing what it is doing. Nowhere in this code do I see any actual calls to the DB.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, November 12, 2019 2:53 PM
    Moderator
  • Agree it's hard to find the database call.

    Here is the code, I believe, you are asking for:

    public ListViewItemsResult<T> GetListItemsResult<T>(OptionsBase options, string schema, string tableOrView) where T : new()
            {
                //Get the list of columns available for this table/view.
                var columnsAvailable = DbConnection.Query<string>(@"SELECT
                             columns.name
                            FROM
                             sys.columns
                            INNER JOIN sys.all_objects
                            ON all_objects.object_id = columns.object_id
                            INNER JOIN sys.schemas
                            ON schemas.schema_id = all_objects.schema_id
                            WHERE
                             schemas.name = @schema
                            AND all_objects.name = @TableOrView", new { schema, tableOrView }).ToList();

                //var sqlString = new StringBuilder(string.Format("SELECT * FROM [{0}].[{1}] WHERE SiteId = @currentSiteId ", schema, tableOrView));
                var sqlString =
                    new StringBuilder(string.Format("SELECT * FROM [{0}].[{1}] WHERE {2} ", schema, tableOrView,
                        string.IsNullOrEmpty(options.CurrentSiteId) || !columnsAvailable.Any(column => column.Equals("SiteId", StringComparison.InvariantCultureIgnoreCase)) ? "1=1 " : "SiteId = @currentSiteId"));

                foreach (var filterProvider in options.Filters)
                {
                    var dFilter = filterProvider as DateRangeFilterProvider;
                    if (dFilter != null)
                    {
                        if (dFilter.DateFrom.HasValue && dFilter.DateTo.HasValue)
                        {
                            sqlString.Append(string.Format(" AND CAST([{0}] as DATE) BETWEEN '{1:dd-MMM-yyyy}' AND '{2:dd-MMM-yyyy}'", dFilter.PropertyName,
                                dFilter.DateFrom, dFilter.DateTo));
                        }
                        else if (dFilter.DateFrom.HasValue)
                        {
                            sqlString.Append(string.Format(" AND CAST([{0}] as DATE) >= '{1:dd-MMM-yyyy}'", dFilter.PropertyName,
                                dFilter.DateFrom));
                        }
                        else if (dFilter.DateTo.HasValue)
                        {
                            sqlString.Append(string.Format(" AND CAST([{0}] as DATE) <= '{1:dd-MMM-yyyy}' ", dFilter.PropertyName,
                                dFilter.DateTo));
                        }

                        continue;
                    }

                    var ftFilter = filterProvider as FreeTextFilterProvider;
                    if (ftFilter != null)
                    {
                        if (!string.IsNullOrEmpty(ftFilter.Value))
                        {
                            ftFilter.Value = string.Format("%{0}%", ftFilter.Value).Replace("'", "''");


                            sqlString.Append(
                                " AND (" + string.Join(" OR ",
                                    columnsAvailable.Select(x => string.Format("CAST([{0}] AS VARCHAR) LIKE '{1}'", x, ftFilter.Value))) +
                                ")");

                        }

                        continue;
                    }

    Or at least that's the code that buiilds the SQL string.
    And this bit calls it:

    var returnVal = new ListViewItemsResult<T>
                {
                    Items = options.PageNo > 0 ? DbConnection.Query<T>(sqlString.ToString(), options) : new List<T>(),
                    TotalItemsCount = DbConnection.Query<long>(countString, options).Single()
                };

                return returnVal;

    This bit sets the view into it (how I know the view is relevant):

    publicListViewItemsResult<NonUkAttendee> GetVisitorsFromOutsideUK(OptionsBase options)

            {

               

    returnbase.GetListItemsResult<NonUkAttendee>(options, "dbo", "EmployeeAttendeesFromOutsideUk");

            }

    The whole thing seems pointlessly complicated to be honest.

    Tuesday, November 12, 2019 3:15 PM
  • Is this a generic query program or something? The first query retrieves the columns defined on a given table/schema.  Then it builds up a series of where clauses. It starts with looking for rows that have a specific SiteId column set to whatever that option value has. Alternatively if there is no `SiteId` column in the table it selects them all. Honestly at this point your DBA should be having a heart attack. 

    It then enumerates some sort of filter providers to allow each of them to apply their filtering as well. However this particular call is not very flexible as it only supports 2 types of filter providers. The first provider is a datetime provider and it just looks at the options set and applies the appropriate where clause. The second provider it looks for is a free text provider which it does a similar thing. 

    The second block of code is simply building up a model to contain the results of the query along with a total count. Unfortunately this code is buried in that custom DbConnection type that we don't have visibility into. Hopefully it doesn't run the query twice but whatever. That code looks reasonable.

    The last set is just running the query. So to figure out where the problem lies put a breakpoint on the second code block where it news up `ListViewItemsResult`. Execute that line of code and then look at the `Items` property. It should have all the data you expect. Furthermore the type of that content is defined as `T` which based upon the third code block is of type `NonUkAttendee`. 

    If the returned data is missing rows then the problem lines in the filter providers/options being passed or the DB itself.

    If the correct rows are being returned but it doesn't have all the data in properties then the problem is that `NonUkAttendee` needs to be updated to expose properties for the columns you want. How that mapping works is buried in DbConnection.Query though. Most likely it does a direct mapping (e.g. column 'Name' maps to public property 'Name').

    If `NonUkAttendee` has the correct properties but they aren't being set then the problem resides in `DbConnection.Query` so you'll need to look at how it maps the columns to properties.

    If `NonUkAttendee` has all the columns/rows of data you expect but the UI isn't showing it then the problem is on the MVC side, likely in the UI for rendering the grid.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, November 12, 2019 3:35 PM
    Moderator
  • Why can't you post to the ASP.NET forums? They know C# there just as well, and there is a MVC forum there too.

    BTW, the code is horrible. 

    http://forums.asp.net/

    Tuesday, November 12, 2019 3:39 PM
  • I can do "ReturnVal" "items", attached a screenshot.

    So, they aren't set OK looks like, e.g. things that are set in the database aren't set here, like the name etc. It does seem to load multiple times which is wierd, but there seems to be a relationship between what is returned below, and what is shown on the screen, e.g. if it's populated in the screenshot, it's populated on the table on the screen.

    Tuesday, November 12, 2019 3:59 PM
  • Not saying I won't: I think the other poster is helping me determine whether this is a C# problem, or an ASP.NET. It's not always clear, and I'm still learning so appreciate your patience.

    Certainly I agree the code is horrible, and I have to support it.

    Tuesday, November 12, 2019 4:01 PM
  • If the properties aren't set then that is a mapping problem inside that Query<T> call. Since that code is not framework code you're going to have to go digging. Most likely it is using a simple naming convention (e.g. 'Name' column maps to the 'Name' property). If they don't match then it won't set it. Most "mapping" frameworks provide options to allow you to handle this manually. For example EF allows you to use the `Column` attribute to map to an arbitrary column.

    If this is the only issue you have then I'd just fix the mapping. If however you have lots more issues with this code then I'd lean toward switching to something that is easier like EF or nHibernate. This would be a huge undertaking if you have lots of DB calls but ultimately it would simplify your code. Perhaps consider a refactoring into your project schedule if you have time or add it to your backlog later. Since your code clearly knows what it is querying for (given the generic type) having a generic query engine seems overkill to me.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, November 12, 2019 4:33 PM
    Moderator
  • If I click on the list view items result I get:

    namespace SVR.Data.Classes.Dtos
    {
        public class ListViewItemsResult
        {
            public IEnumerable<dynamic> Items { get; set; }
            public long TotalItemsCount { get; set; }
        }

        public class ListViewItemsResult<T> where T : new()
        {
            public IEnumerable<T> Items { get; set; }
            public long TotalItemsCount { get; set; }
        }

    Certainly the "names" specified here seem to relate to the column names in the view (you can't just change them to anything):
    return base.GetListItems(siteId, options, _reportsRepository.GetVisitorsFromOutsideUK,
                    new Expression<Func<NonUkAttendee, dynamic>>[]
                    {
                        x => x.SiteId,
                        x => x.FullName,
                        x => x.VisitDateConvert,
                        x => x.VisitDateNoConvert,

    I don't know where the "mapping" you describe is.

    I don't know what "EF" is.
    I don't know what "nHibernate" is.

    Wednesday, November 13, 2019 10:58 AM
  • OK, I've not finished here but I found a clue.
    In the part below there is these "Display Name" things.
    They seem to control the name of the columns; so that's another problem.
    But getting shot of them, and changing the view, so the columns are named without Spaces, seems to mean we can pull back (at least some more) data, especially the Employee Name,
    e.g. I commented out this line:
     //[Display(Name = "Employee Name")]
    Renamed the column in the view to  EmployeeName and then just kept/altered this line:
    public string EmployeeName { get; set; } > With the affect that I can now return the employee name. Preivously this was set to null when debugging, and was blank on the website: now its set in both places, but somewhat annoyingly the column name for it is set to EmployeeName instead of Employee Name. Still investigating but wanted to post initial findings.

    namespace SVR.Data.Classes.Dtos.Reports
    {
        public class NonUkAttendee : UpdateableItem
        {
            public string SiteId { get; set; }

            //[Display(Name = "Employee Name")]
            // public string FullName { get; set; }
            public string EmployeeName { get; set; }

            //[Display(Name = "Visit Date Convert")]
            //[DisplayFormat(DataFormatString = "{0:dd-MMM-yyyy hh:mm tt}")]
            //public DateTime VisitDate { get; set; }

    Wednesday, November 13, 2019 11:54 AM
  • but somewhat annoyingly the column name for it is set to EmployeeName instead of Employee Name.

    That's because the MVC will use the name of the property as the display name of the view unless you override it and the MVC yo use DisplayName attribute on the property.

    //[Display(Name = "Employee Name")]  <----- the attribute you have commented out.

            // public string FullName { get; set; }
            public string EmployeeName { get; set; }

    I encourage you to start posting to the MVC forum in ASP.NET forums for further help.

    http://forums.asp.net/

    Wednesday, November 13, 2019 1:57 PM
  • OK,

    Since my original post is now solved, I'll mark your earlier answer as the answer, since it encouraged further digging about the mapping. And if I can't correct the column names I'll put another post on the other forum.

    Thanks for your help.

    Wednesday, November 13, 2019 2:01 PM