CODEWITHSUNDEEP
sqlasp.net-mvct-sqlmodel-view-controllersignalr
Kyle G
Kyle G

Reputation: 149

MVC SignalR TSQL Issue With DateTime

I'm trying to migrate from ASP.NET WebForms to MVC and have used this example
which worked fine using SQL Server 2008 Express hosted on an Azure VM. However once I modified the SQL from the "Step 6: Get the Data from the Repository" section of the tutorial:

    public class MessagesRepository
{
    readonly string _connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

    public IEnumerable<Messages> GetAllMessages()
    {
        var messages = new List<Messages>();
        using (var connection = new SqlConnection(_connString))
        {
            connection.Open();
            using (var command = new SqlCommand(@"SELECT [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages]", connection))
            {
                command.Notification = null;

                var dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    messages.Add(item: new Messages { MessageID = (int)reader["MessageID"], Message = (string)reader["Message"], EmptyMessage =  reader["EmptyMessage"] != DBNull.Value ? (string) reader["EmptyMessage"] : "", MessageDate = Convert.ToDateTime(reader["Date"]) });
                }
            }

        }
        return messages;


    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            MessagesHub.SendMessages();
        }
    }
}

Which I changed the SQL to this:

    @"SELECT [AssignedToUser], [Status], [RequestID] FROM [dbo].[Notifications] WHERE AssignedToUser IS NOT NULL AND Status IS NOT NULL AND RequestID IS NOT NULL AND AssignedToUser = 'Kyle G' AND Status = 'Red'

Which worked fine but when I wanted to get only notifications stamped with today's date and appended my SQL:

     AND CAST(TimeStatusChange AS DATE) = CONVERT(date, getdate())

Or even this:

     AND   TimeStatusChange >= (GetDate() - 1)

It fails to update unless you refresh the page. Any ideas friends?
Note-TimeStatusChange is a datetime field.

I converted the current day and database field "TimeStatusChange" as such:

    SELECT convert(varchar(25), TimeStatusChange, 120) AS  TimeStatusChange, [Id],[AssignedToUser], [Status], [RequestID] FROM [dbo].[Notifications] WHERE AssignedToUser IS NOT NULL AND Status IS NOT NULL AND RequestID IS NOT NULL AND AssignedToUser = 'Kyle G' AND Status = 'Red' AND convert(varchar(25), getdate(), 112) = convert(varchar(25), TimeStatusChange, 112) ORDER BY TimeStatusChange DESC

It should be noted that the issue remains and is not that the SQL when run VS 2015 or SSMS does not work it does. It also works on a page refresh of the MVC page. It does not push the new record to the page in real time without a page refresh.

Here is the results in SSMS using Convert for both the TimeStatusChange and getdate() objects: enter image description here

In other words if I do this (Essentially an "If 1==1" test):

    SELECT TimeStatusChange FROM [dbo].[Notifications] WHERE  convert(varchar(25), TimeStatusChange, 112) = convert(varchar(25), TimeStatusChange, 112)

SignalR Updates the page upon INSERT of new record.

If I change it to compare the DB field to today's date as strings:

    SELECT TimeStatusChange FROM [dbo].[Notifications] WHERE  convert(varchar(25), TimeStatusChange, 112) = convert(varchar(25), getdate(), 112)

SignalR no longer updates the page on INSERT new record unless I refresh it. The SQL works it just breaks SignalR from updating the page without a refresh.

If I do the opposite If 1==1 test by comparing today's date to itself as a string:

    SELECT TimeStatusChange FROM [dbo].[Notifications] WHERE  convert(varchar(25), getdate(), 112) = convert(varchar(25), getdate(), 112)

This also breaks SignalR from updating the page UNLESS I refresh the page. The clue seems to be a problem with:

    convert(varchar(25), getdate(), 112)

Breaking SignalR, not the SQL. Perhaps this is too difficult to troubleshoot without having the full solution available.

Upvotes: 0

Views: 222

Answers (2)

Kyle G
Kyle G

Reputation: 149

I finally got it (Thanks to Kamran & Dan for hints). I do not understand why this worked but by passing the current date into the SQL statement as a string as opposed to using getdate() or DATEADD in SQL made it work! Why is beyond my pay grade.

Here is the working code (yes I will parameterize it!)

     string dateof = DateTime.Today.ToString("MM/dd/yyyy");

            connection.Open();
            using (var command = new SqlCommand(@"SELECT [Id],[AssignedToUser], [Status], [RequestID] FROM [dbo].[Notifications] WHERE  '" + dateof + "' = convert(varchar(25), TimeStatusChange, 101) ORDER BY TimeStatusChange DESC", connection))

Upvotes: 1

Fuzzy
Fuzzy

Reputation: 3810

To answer your question " Do you have a suggestion as to how to get today's date as just the date and no time as a date?"

Here is how you would convert the dates:

SELECT CONVERT(VARCHAR(10),GETDATE(),112),CONVERT(VARCHAR(10),GETDATE(),111),CONVERT(VARCHAR(10),GETDATE(),110)

and the results:

enter image description here

Upvotes: 1

Related Questions