[RESOLVED]My page doesn't update in real time if there is some null value from SQL Server

Hello,

I’m working on a project where I need that the pages update in real time when a new record is added to a SQL Server database table or an existing recerd has been updated. I found an working example and implemented it and it works quite good. Here some information:

HttpPop3.cs

    [Table("HttpPop3")]
    public class HttpPop3
    {
        [Key]
        [DisplayName("ID Messaggio")]
        public long MessageID { get; set; }

        public long DeviceID { get; set; }

        public long? UserID { get; set; }

        [DisplayName("Mittente")]
        [StringLength(50)]
        [Required]
        public string sms_num { get; set; }

        [DisplayName("Data")]
        [StringLength(50)]
        [Required]
        public string sms_date { get; set; }

        [DisplayName("Testo")]
        [StringLength(255)]
        [Required]
        public string sms_text { get; set; }

        [DisplayName("Code")]
        [StringLength(50)]
        public string sms_code { get; set; }

        [DisplayName("Ricezione")]
        public DateTime? Received { get; set; }

        public virtual Device Device { get; set; }
        public virtual User User { get; set; }

        [NotMapped]
        [DisplayName("Numero di serie")]
        public string SerialNumber { get; set; }

        [NotMapped]
        [DisplayName("Nome")]
        public string Name { get; set; }

        [NotMapped]
        [DisplayName("Cognome")]
        public string Surname { get; set; }

        [NotMapped]
        [DisplayName("CLI")]
        public string CLI { get; set; }
    }

Device.cs

    [Table("Devices")]
    public class Device
    {
        [Key]
        [DisplayName("ID Dispositivo")]
        public long DeviceID { get; set; }

        [DisplayName("ID Modello")]
        public long ModelID { get; set; }

        [DisplayName("ID Fornitore")]
        public long SupplierID { get; set; }

        [DisplayName("Numero di serie")]
        [StringLength(50)]
        [Required(AllowEmptyStrings = false, ErrorMessage = "E' richiesto un numero di serie")]
        public string SerialNumber { get; set; }

        [DisplayName("Codice fornitore")]
        [StringLength(50)]
        public string SupplierCode { get; set; }

        [DisplayName("Data di acquisto")]
        public DateTime DatePurchase { get; set; }

        public virtual Model Model { get; set; }
        public virtual Supplier Supplier { get; set; }

        [NotMapped]
        [DisplayName("Modello")]
        public string ModelName { get; set; }

        [NotMapped]
        [DisplayName("Fornitore")]
        public string SupplierName { get; set; }
    }

User.cs

    public class User
    {
        [Key]
        public long UserID { get; set; }

        [DisplayName("Nome")]
        [StringLength(50)]
        [Required(AllowEmptyStrings = false, ErrorMessage = "E' richiesto un nome")]
        public string Name { get; set; }

        [DisplayName("Cognome")]
        [StringLength(50)]
        [Required(AllowEmptyStrings = false, ErrorMessage = "E' richiesto un cognome")]
        public string Surname { get; set; }

        [DisplayName("CLI")]
        [StringLength(50)]
        [Required(AllowEmptyStrings = false, ErrorMessage = "E' richiesto un CLI")]
        public string CLI { get; set; }
    }

GetAllHttpPop3s()

        public IEnumerable<HttpPop3> GetAllHttpPop3s()
        {
            List<HttpPop3> httpPop3s = new List<HttpPop3>();
            using (var connection = new SqlConnection(connString))
            {
                StringBuilder query = new StringBuilder();
                query.Append("SELECT dbo.HttpPop3.MessageID, dbo.Devices.SerialNumber, dbo.Users.Name, ");
                query.Append("dbo.Users.Surname, dbo.Users.CLI, dbo.HttpPop3.sms_num, ");
                query.Append("dbo.HttpPop3.sms_date, dbo.HttpPop3.sms_text, dbo.HttpPop3.sms_code, ");
                query.Append("dbo.HttpPop3.Received ");
                query.Append("FROM dbo.HttpPop3 ");
                query.Append("INNER JOIN dbo.Devices ON dbo.Devices.DeviceID = dbo.HttpPop3.DeviceID ");
                query.Append("INNER JOIN dbo.Users ON dbo.Users.UserID = dbo.HttpPop3.UserID");

                connection.Open();
                using (SqlCommand command = new SqlCommand(query.ToString(), connection))
                {
                    command.Notification = null;

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

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

                    var reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        string name, surname, cli;
                        DateTime? received;

                        if (reader["Name"].Equals(DBNull.Value))
                        {
                            name = null;
                        }
                        else
                        {
                            name = (string)reader["Name"];
                        }

                        if (reader["Surname"].Equals(DBNull.Value))
                        {
                            surname = null;
                        }
                        else
                        {
                            surname = (string)reader["Surname"];
                        }

                        if (reader["CLI"].Equals(DBNull.Value))
                        {
                            cli = null;
                        }
                        else
                        {
                            cli = (string)reader["CLI"];
                        }

                        if (reader["Received"].Equals(DBNull.Value))
                        {
                            received = null;
                        }
                        else
                        {
                            received = (DateTime)reader["Received"];
                        }

                        httpPop3s.Add(new HttpPop3 { MessageID = (long)reader["MessageID"], SerialNumber = (string)reader["SerialNumber"], Name = name, 
                                                        Surname = surname, CLI = cli, sms_num = (string)reader["sms_num"],
                                                        sms_date = (string)reader["sms_date"], sms_text = (string)reader["sms_text"], sms_code = (string)reader["sms_code"],
                                                        Received = received
                        });
                    }
                }
            }
            return httpPop3s;
        }

_HttpPop3List.cshtml (A partial view)

@model IEnumerable<iCare.Models.HttpPop3>

<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.MessageID)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.SerialNumber)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Surname)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.CLI)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.sms_num)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.sms_date)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.sms_text)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.sms_code)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Received)
        </th>
        <th>
            @Html.DisplayName("Comandi")
        </th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.MessageID)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.SerialNumber)
        </td>
        <td>
            @*@Html.DisplayFor(modelItem => item.Name == null ? "NULL" : item.Name)*@
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @*@Html.DisplayFor(modelItem => item.Surname == null ? "NULL" : item.Surname)*@
            @Html.DisplayFor(modelItem => item.Surname)
        </td>
        <td>
            @*@Html.DisplayFor(modelItem => item.CLI == null ? "NULL" : item.CLI)*@
            @Html.DisplayFor(modelItem => item.CLI)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.sms_num)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.sms_date)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.sms_text)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.sms_code)
        </td>
        <td>
            @*@Html.DisplayFor(modelItem => item.Received == null ? DateTime.Now : item.Received)*@
            @Html.DisplayFor(modelItem => item.Received)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.MessageID }) |
            @Html.ActionLink("Details", "Details", new { id=item.MessageID }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.MessageID })
        </td>
    </tr>
}

</table>

Index.cshtml

@model IEnumerable<iCare.Models.HttpPop3>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<div class="row">
    <div class="col-md-12">
        <div id="httpPop3sTable"></div>
    </div>
</div>
@section Scripts{
    <script src="/Scripts/jquery.signalR-2.1.2.js"></script>
    <!--Reference the autogenerated SignalR hub script. -->
    <script src="/signalr/hubs"></script>
    <script type="text/javascript">
        $(function () {
            // Declare a proxy to reference the hub.
            var notifications = $.connection.myHub;

            //debugger;
            // Create a function that the hub can call to broadcast messages.
            notifications.client.updateHttpPop3s = function () {
                getAllHttpPop3s()

            };
            // Start the connection.
            $.connection.hub.start().done(function () {
                alert("connection started")
                getAllHttpPop3s();
            }).fail(function (e) {
                alert(e);
            });
        });


        function getAllHttpPop3s() {
            var tbl = $('#httpPop3sTable');
            $.ajax({
                url: '/httpPop3/GetHttpPop3s',
                contentType: 'application/html ; charset:utf-8',
                type: 'GET',
                dataType: 'html'
            }).success(function (result) {
                tbl.empty().append(result);
            }).error(function () {

            });
        }
    </script>
}

OK, let me explain something: HttpPop3 is an SMS machine that makes a POST everytime a message arrives. In this page I take the informations and automatically fill a database table (dbo.HttpPop3), in this table there is a trigger:

[...]
UPDATE HttpPop3 SET UserID = (SELECT dbo.Users.UserID FROM dbo.HttpPop3 INNER JOIN dbo.Users ON dbo.HttpPop3.sms_num = dbo.Users.CLI WHERE (dbo.HttpPop3.UserID IS NULL)), Received = CURRENT_TIMESTAMP WHERE UserID IS NULL AND Received IS NULL
[...]

So everytime I receive a message I know which User sent it but only for know dbo.HttpPop3.sms_num/dbo.Users.CLI.

Now In the GetAllHttpPop3s() method I uses a SQL query with INNER JOIN that get only the record with a known users and in this way works: when I receive a message a see my page updates. But I want to get all the records also with an unknown user.

Don’t know if it’s clear…

Thank you

Hi,

It seems you want LEFT JOIN dbo.Users rather than INNER JOIN. See
http://www.w3schools.com/sql/sql_join.asp
 for d├ętails about joins…

Yes, if I use LEFT JOIN and not INNER JOIN gives me what I want: all records also with null values.

But with the null values my page doesn’t update automatically when the table updates.

The flow works only when all the columns have a value.

Chen Yiyi

Yes, if I use LEFT JOIN and not INNER JOIN gives me what I want: all records also with null values.

you can assign a default value on the columns in sql this way you won’t have null values but have an int or something instead.

From http://msdn.microsoft.com/en-us/library/aewzkxxh(VS.80).aspx :

"The statement must not contain subqueries, outer joins, or self-joins."

Instead you could for example have a special table used for just that. A trigger would "post" changes to this table. This way you should be able to handle whatever you want with a single dependency table…

Sqldependency does not support left joins. You should create a unknown user, and use this ID for unknown users, so that a inner join will work.

sp00k

you can assign a default value on the columns in sql this way you won’t have null values but have an int or something instead.

Tried to set a value but in this way I can’t see row.

It seems that SqlDependency doesn’t like LEFT JOIN and NULL values…

its not the null values, its the left join that is not supported. you need to remove the left join.

This is really a bad news…

OK, I will use some work around like an error user like someone suggested.

Thank you

Leave a Reply