Results 1 to 7 of 7

Thread: [RESOLVED] What would syntax of this be in C# code?

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,259

    Resolved [RESOLVED] What would syntax of this be in C# code?

    Simply put, how do I write this same filter in my C# code?
    select * from [dbo].[xtblMROrderItems] where (convert(varchar(10), [ReceivedDate], 101)) = '06/24/2021'

    I am trying to see if all rows in the datatable that I've populated in my application have the same date without regard to the timestamp.
    So the C# code goes like this:
    Code:
    DateTime rcvdDate = drItems.ReceivedDate;
    string sFilter = String.Format("ReceivedDate = '{0}'", rcvdDate);
    //dv = new DataView(dtItems, sFilter, "", DataViewRowState.CurrentRows);
    //if (dv.ToTable().Rows.Count == dtItems.Rows.Count)
    //    e.DisplayText = rcvdDate.ToString("MM/dd/yyyy");
    //else
    //    e.DisplayText = "*";
    Or went like that before I changed column ReceivedDate in the table schema to be type datetime. Now I have to somehow strip off the time.
    Before I do it brute force; that is, get the first row and loop through all the rows comparing one by one, I wanted to try to get this filter working. But I am having a hard time getting the datatypes and synatx right.

    Here is the (bad) fruit of my labor over the past hour... (FYI I started hardcoding the date to 6/24/21 just to simplify it and get rid of a variable, but ultimately that will be the date of the first row that I want to compare all remaining rows to. e.DisplayText is the value of a grid cell and I want it to display the date if they all match or an * if any are on different days).
    Code:
    //sFilter = String.Format("CONVERT(Date, [ReceivedDate]) = '{0}'", rcvdDate.Date);
    //dv = new DataView(dtItems, sFilter, "", DataViewRowState.CurrentRows);
    //if (dv.ToTable().Rows.Count == dtItems.Rows.Count)
    //    e.DisplayText = rcvdDate.ToString("MM/dd/yyyy");
    //else
    //    e.DisplayText = "*";
    
    //sFilter = String.Format("convert(CONVERT([ReceivedDate], 'System.DateTine'), 'System.String') Like '{0}%'", rcvdDate.Date);
    sFilter = String.Format("convert(CONVERT([ReceivedDate], 'System.DateTime'), 'System.String') Like '{0}%'", "6/4/2021");
    dv = new DataView(dtItems, sFilter, "", DataViewRowState.CurrentRows);
    if (dv.ToTable().Rows.Count == dtItems.Rows.Count)
        e.DisplayText = rcvdDate.ToString("MM/dd/yyyy");
    else
        e.DisplayText = "*";
    
    // It has to go something like this:
    //  select * from [dbo].[xtblMROrderItems] where (convert(varchar(10), [ReceivedDate], 101)) = '06/24/2021'
    sFilter = "(convert(varchar(10), [ReceivedDate], 101)) = '06/24/2021'";
    dv = new DataView(dtItems, sFilter, "", DataViewRowState.CurrentRows);
    Thank you!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,624

    Re: What would syntax of this be in C# code?

    I'm not a .NET-ter, but.....
    If everything fails, remember, a DateTime is an Alias/TypeDef for a Double (64-Bit floating Point), the integer part representing the Date, the fraction part representing the time.
    Now, how to strip out the Time.....
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,636

    Re: What would syntax of this be in C# code?

    The SQL code supported by the DataTable class, as detailed in the documentation for the DataColumn.Expression property, is very limited. Your best bet might be to use LINQ, which gives you everything that .NET supports. You can get an IEnumerable(Of DataRow) by performing a LINQ query on a DataTable and then, if you specifically need it, you can call CopyToDataTable on that to create a new DataTable containing just those rows. If all you actually want is to know whether all rows in your table fall on a the same date:
    csharp Code:
    1. if (dtItems.AsEnumerable()
    2.            .Select(row => row.ReceivedDate.Date)
    3.            .Distinct()
    4.            .Count() == 1)
    5. {
    6.     // All ReceivedDate values fall on the same date.
    7. }
    If you actually want that date value:
    csharp Code:
    1. var receivedDates = dtItems.AsEnumerable()
    2.                            .Select(row => row.ReceivedDate.Date)
    3.                            .Distinct()
    4.                            .ToArray()
    5.  
    6. if (receivedDates.Length == 1)
    7. {
    8.     var receivedDate = receivedDates[0];
    9.  
    10.     // ...
    11. }
    12. else
    13. {
    14.     // There are multiple received dates.
    15. }

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,484

    Re: What would syntax of this be in C# code?

    I would do a Cast(RecieveDate AS DATE) No need to convert to string
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,636

    Re: What would syntax of this be in C# code?

    Quote Originally Posted by GaryMazzone View Post
    I would do a Cast(RecieveDate AS DATE) No need to convert to string
    There is no such thing. The question was how to do it in a DataTable in C# code, not in a database. The small subset of SQL available in that context can be found here.

    That said, perhaps you meant to change the original query that populated the DataTable in the first place. If you still needed the time portion, you'd have to select two columns: one containing the date and time and one just the date. Unless every millisecond is important, I'd suggest that LINQ is a better option.

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,259

    Re: What would syntax of this be in C# code?

    @jmcilhinney Yes, I am implementing/testing your suggestion right now.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,259

    Re: What would syntax of this be in C# code?

    @jmcilhinney - That worked beautifully, thank you!!!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width