locked
Real time update with SqlDependency and SignalR in MVC with foreign key RRS feed

  • Question

  • User540686086 posted

    Hello, I'm doing a project in MVC where I want to update the webpages in real time when the SQL Server database table changes.

    This is the only working example that I found: http://venkatbaggu.com/signalr-database-update-notifications-asp-net-mvc-usiing-sql-dependency/

    I tried with the Vendor table and it works, but I have some problem with the product table:

    Using the columns Products.ProductID, Products.VendorID, Products.Name all works but I want show the column Vendors.Name instead of Products.VendorID and I don't know how to make this. Here some code to help to understand:

    Vendor.cs

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;
    using System.Web;
    
    namespace iCare.Models
    {
        [Table("Vendors")]
        public class Vendor
        {
            [Key]
            [DisplayName("ID Produttore")]
            public long VendorID { get; set; }
    
            [DisplayName("Produttore")]
            [StringLength(50)]
            [Required(AllowEmptyStrings = false, ErrorMessage = "E' richiesto un produttore")]
            public string Name { get; set; }
        }
    }

    Product.cs

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;
    using System.Web;
    
    namespace iCare.Models
    {
        [Table("Products")]
        public class Product
        {
            [Key]
            [DisplayName("ID Prodotto")]
            public long ProductID { get; set; }
    
            [DisplayName("ID Produttore")]
            public long VendorID { get; set; }
    
            [DisplayName("Prodotto")]
            [StringLength(50)]
            [Required(AllowEmptyStrings = false, ErrorMessage = "E' richiesto un prodotto")]
            public string Name { get; set; }
    
            public virtual Vendor Vendor { get; set; }
        }
    }

    Repositories.cs

    using iCare.Hubs;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    
    namespace iCare.Models
    {
        public class Repositories
        {
            iCareEntities db = new iCareEntities();
            string connString = ConfigurationManager.ConnectionStrings["iCareEntities"].ConnectionString;
    
            public IEnumerable<Vendor> GetAllVendors()
            {
                List<Vendor> vendors = new List<Vendor>();
                using (var connection = new SqlConnection(connString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(@"SELECT [VendorID], [Name] FROM [dbo].[Vendors]", 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())
                        {
                            vendors.Add(new Vendor { VendorID = (long)reader["VendorID"], Name = (string)reader["Name"] });
                        }
                    }
    
                }
    
                return vendors;
            }
    
    
            public IEnumerable<Product> GetAllProducts()
            {
                List<Product> products = new List<Product>();
                using (var connection = new SqlConnection(connString))
                {
                    
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(@"SELECT [ProductID], [VendorID], [Name] FROM [dbo].[Products]", 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())
                        {
                            products.Add(new Product { ProductID = (long)reader["ProductID"], VendorID = (long)reader["VendorID"], Name = (string)reader["Name"] });
                        }
                    }
                }
                return products;
            }
    
    
            private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
            {
                if (e.Type == SqlNotificationType.Change)
                {
                    MyHub.SendVendors();
                    MyHub.SendProducts();
                }
            }
        }
    }

    _ProductsList.cshtml

    @model IEnumerable<iCare.Models.Product>
    
    @{
        ViewBag.Title = "_ProductsList";
    }
    
    <table class="table">
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.ProductID)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.VendorID)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Name)
            </th>
            <th>
                @Html.DisplayName("Comandi")
            </th>
        </tr>
    
    @foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.ProductID)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.VendorID)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Name)
            </td>
            <td>
                @Html.ActionLink("Edit", "Edit", new { id=item.ProductID }) |
                @Html.ActionLink("Details", "Details", new { id=item.ProductID }) |
                @Html.ActionLink("Delete", "Delete", new { id=item.ProductID })
            </td>
        </tr>
    }
    
    </table>

    Index.cshtml

    @model IEnumerable<iCare.Models.Product>
    
    @{
        ViewBag.Title = "Index";
    }
    
    <h2>Index</h2>
    
    <p>
        @Html.ActionLink("Create New", "Create")
    </p>
    <div class="row">
        <div class="col-md-12">
            <div id="productsTable"></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.updateProducts = function () {
                    getAllProducts()
    
                };
                // Start the connection.
                $.connection.hub.start().done(function () {
                    alert("connection started")
                    getAllProducts();
                }).fail(function (e) {
                    alert(e);
                });
            });
    
    
            function getAllProducts() {
                var tbl = $('#productsTable');
                $.ajax({
                    url: '/product/GetProducts',
                    contentType: 'application/html ; charset:utf-8',
                    type: 'GET',
                    dataType: 'html'
                }).success(function (result) {
                    tbl.empty().append(result);
                }).error(function () {
    
                });
            }
        </script>
    }

    In this way works but I want to show the Vendors.Name column. Someone told me to use a ViewModel class:

    ProductViewModel.cs

        [Table("Products")]
        public class ProductsViewModel
        {
            [Key]
            [DisplayName("ID Prodotto")]
            public long ProductID { get; set; }
    
            [DisplayName("Produttore")]
            public string VendorName { get; set; }
    
            [DisplayName("Prodotto")]
            public string Name { get; set; }
    
        }

    But in this way I have an error like "not existing VendorName column", and it's right.

    Can you help me?

    Thank you

    Monday, October 13, 2014 8:30 AM

Answers

  • User197322208 posted

    in this way I have an error like "not existing VendorName column", and it's right.

    Can you help me?

    Add

    [NotMApped]

    attribute

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 13, 2014 8:48 AM

All replies

  • User197322208 posted

    in this way I have an error like "not existing VendorName column", and it's right.

    Can you help me?

    Add

    [NotMApped]

    attribute

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 13, 2014 8:48 AM
  • User-1454326058 posted

    Hi Chen Yiyi,

    For this requirement, you need to base on the VendorID value to get the corresponding related Vendors data. You could join these tables (e.g. left join). Here you could use ViewModel and set the corresponding value.

    For the ViewModel, it is used for the view. What others mean is that you could use the ViewModel in the view.

    There is a link about AutoMapper that may benefit you:

    # AutoMapper

    http://www.codeproject.com/Articles/61629/AutoMapper

    Best Regards

    Starain

    Tuesday, October 14, 2014 3:42 AM
  • User540686086 posted

    Add

    [NotMApped]

    attribute

    Hello ignatandrei,

    your suggestion worked!

    So I added

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

    And my repository method became

            public IEnumerable<Product> GetAllProducts()
            {
                List<Product> products = new List<Product>();
                using (var connection = new SqlConnection(connString))
                {
                    StringBuilder query = new StringBuilder();
                    query.Append("SELECT dbo.Products.ProductID, dbo.Vendors.Name AS VendorName, dbo.Products.Name AS ProductName ");
                    query.Append("FROM dbo.Products ");
                    query.Append("INNER JOIN dbo.Vendors ON dbo.Products.VendorID = dbo.Vendors.VendorID");
    
                    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())
                        {
                            products.Add(new Product { ProductID = (long)reader["ProductID"], VendorName = (string)reader["VendorName"], Name = (string)reader["ProductName"] });
                        }
                    }
                }
                return products;
            }

    And it works!

    Thank you very much.

    Only 1 more question: I made some test and I saw that all the process works if all columns are filled. Not if some value are null. For example, if my Products.Name is null, how have I to change my code?

    Thank you again

    Wednesday, October 15, 2014 4:21 AM