代码之家 › 专栏 › 技术社区 › Nadeem Taj

数据表C#-如何根据ID从一个表中获取详细记录,并从另一个表获取其关联的子记录

datatable linq json c#

Nadeem Taj · 技术社区 · 5 年前

我在数据表中有一个学生记录,如果我点击任何一条记录,它会执行两个操作。

获取学生详细信息,如绿色圆圈所示,它工作正常使用getJson。
我还想获得所选学生的所有发票及其付款。

注意:发票可以是一张或多张,付款也可以是一笔或多笔。

问题-红色圆圈

a.我在数据表的表第一行中没有可用数据。

b.只从发票表获取数据,不从付款表获取数据。

c.无法从表中选择记录。

寻找解决方案/解决方案

当用户从列表中选择任何学生时,它应该显示所有发票及其相应的付款,然后转到下一张发票及其相关付款。

例子:

学生证:123

发票编号:12345、12347、123479

付款ID:发票ID 12345为12,12345为14,12347为17,以此类推。

JSON代码:

var oTable1;
        oTable1 = $('#invoiceNo').dataTable();
        $('#invoiceNo').on('click', 'tr', function ()
        {
            var stu_id = $(this).find('td').eq(0).text();
                var aid = parseInt(stu_id);
            //alert(aid);
                $.getJSON("/Transaction/showStu_Inv_Pay",
                {
                    id: aid
                },
                function (data) {
                    num_rows = data.length;
                    alert(num_rows);
                    var myTable = $('#inv_pay').DataTable();
                    myTable.clear().rows.add(myTable.data).draw();

                        $(data).each(function (index, item) {
                  
                            $('#inv_pay tbody').append(
                            '<tr><td>' + this.InvoiceID +
                            '</td><td>' + this.InvoiceIDate +
                            '</td><td>' + this.Invoice_Type_Name +
                            '</td></tr>'
                        )

                    });
                });
        });

Linq查询

public IList<Transaction> GetInvByStuID()
        {
            try
            {
                DBAPPSEntities _db = new DBAPPSEntities();
                this.Invoice_Type_Name = "Invoice";
                IList<Transaction> List = (from q in _db.DEL_STU_INV
                                           where q.STU_ID == this.Stu_ID
                                           select new Transaction
                                           {
                                               InvoiceID = q.INVOICE_ID,
                                               InvoiceIDate = q.Inv_Issue_Date,
                                               InvoiceDDate = q.Inv_Due_Date,
                                               Invoice_Month1 = q.Inv_Month_1,
                                               Invoice_Month2 = q.Inv_Month_2,
                                               Invoice_Month3 = q.Inv_Month_3,
                                               Invoice_Note = q.Inv_Note,
                                               Invoice_Adjustment = q.Adjust_Type,
                                               Invoice_Type_Name = this.Invoice_Type_Name,
                                           }).ToList();
                return List;
            }
            catch
            {
                return null;
            }
        }

注意:linq查询仅来自发票表,不确定如何与付款表结合,并且可以更改。

第101节

控制器-设置学生ID

 public JsonResult showStu_Inv_Pay(int id)
        {
            var model = new Transaction { Stu_ID = id }; // student id set to model 

            var data = model.GetInvByStuID();
            return Json(data, JsonRequestBehavior.AllowGet);
        }

第102节

示例数据

第103节-更新-1

第104节-更新-2

0 回复 | 直到 5 年前

jdweng 5 年前

尝试以下代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication164
{
    class Program
    {
        static void Main(string[] args)
        {
            DBAPPSEntities _db = new DBAPPSEntities();

            _db.DEL_STU_INV = new List<DEL_STU_INV>() {
                new DEL_STU_INV() { INVOICE_ID = 59456, Inv_Issue_Date = new DateTime(20,6,1), Inv_Due_Date = new DateTime(20,6,15), STU_ID = 197, STU_Name = "New_Student1", Amount = 1000},
                 new DEL_STU_INV() { INVOICE_ID = 59457, Inv_Issue_Date = new DateTime(20,6,1), Inv_Due_Date = new DateTime(20,6,10), STU_ID = 197, STU_Name = "New_Student2", Amount = 1000},
                 new DEL_STU_INV() { INVOICE_ID = 59458, Inv_Issue_Date = new DateTime(20,6,1), Inv_Due_Date = new DateTime(20,6,10), STU_ID = 103, STU_Name = "New_Student3", Amount = 1000},
                 new DEL_STU_INV() { INVOICE_ID = 59459, Inv_Issue_Date = new DateTime(20,6,1), Inv_Due_Date = new DateTime(20,6,10), STU_ID = 184, STU_Name = "New_Student4", Amount = 1000},
                 new DEL_STU_INV() { INVOICE_ID = 59460, Inv_Issue_Date = new DateTime(20,6,1), Inv_Due_Date = new DateTime(20,6,10), STU_ID = 197, STU_Name = "New_Student5", Amount = 1000}
            };

            _db.PAYMENT = new List<PAYMENT>() {
                new PAYMENT() { PAYMENT_ID = 1, PDate = new DateTime(20,6,18), STU_ID = 197, INV_ID = 59456, Paid_Amount = 200, Balance = 800},
                new PAYMENT() { PAYMENT_ID = 2, PDate = new DateTime(20,6,17), STU_ID = 934, INV_ID = 59458, Paid_Amount = 500, Balance = 500},
                new PAYMENT() { PAYMENT_ID = 3, PDate = new DateTime(20,6,17), STU_ID = 197, INV_ID = 59456, Paid_Amount = 250, Balance = 550},
                new PAYMENT() { PAYMENT_ID = 4, PDate = new DateTime(20,6,17), STU_ID = 197, INV_ID = 59457, Paid_Amount = 1000, Balance = 0},
                new PAYMENT() { PAYMENT_ID = 5, PDate = new DateTime(20,6,17), STU_ID = 950, INV_ID = 59459, Paid_Amount = 1000, Balance = 0},
                new PAYMENT() { PAYMENT_ID = 6, PDate = new DateTime(20,6,17), STU_ID = 197, INV_ID = 59456, Paid_Amount = 500, Balance = 50},
                new PAYMENT() { PAYMENT_ID = 7, PDate = new DateTime(20,6,17), STU_ID = 196, INV_ID = 59458, Paid_Amount = 250, Balance = 250},
                new PAYMENT() { PAYMENT_ID = 8, PDate = new DateTime(20,6,17), STU_ID = 1060, INV_ID = 59458, Paid_Amount = 250, Balance = 0}
            };

            int Stu_ID = 197;
            var List = (from s in _db.DEL_STU_INV.Where(x => x.STU_ID == Stu_ID)
                        join p in _db.PAYMENT on s.INVOICE_ID equals p.INV_ID into ps
                        from z  in ps.DefaultIfEmpty()
                        select new { s = s, p = (z == null)? null : z }
                        )
                        .GroupBy(x => x.s.INVOICE_ID)
                        .Select(x =>
                            new
                            {
                                invoice = new Result() { type = "Invoice", id = x.Key, date = x.First().s.Inv_Issue_Date, amount = x.First().s.Amount },
                                payments = x.Select(y => (y.p == null)? null : new Result() { type = "Payment", id = y.p.PAYMENT_ID, date = y.p.PDate, amount = y.p.Paid_Amount })
                            }).Select(y => (y.payments.Count() == 1) ? new List<Result>() { y.invoice } : (new List<Result>() { y.invoice }).Concat(y.payments).ToList())
                            .ToList();
        }
    }
    public class DBAPPSEntities
    {
        public List<DEL_STU_INV> DEL_STU_INV { get;set;}
        public List<PAYMENT> PAYMENT { get; set; }
    }
    public class DEL_STU_INV
    {
       public int STU_ID { get; set; }
       public string STU_Name{ get;set;}
       public int INVOICE_ID { get; set; }
       public int Amount { get; set;}
       public DateTime Inv_Issue_Date { get; set; }
       public DateTime Inv_Due_Date { get; set; }
       public string Inv_Month_1 { get; set; }
       public string Inv_Month_2 {get; set; }
       public string Inv_Month_3 { get; set; }
       public string Inv_Note { get; set; }
       public string Adjust_Type { get; set; }
    }
    public class PAYMENT
    {
        public int PAYMENT_ID { get; set; }
        public DateTime PDate { get; set; }
        public int STU_ID { get; set; }
        public int INV_ID { get; set; }
        public int Paid_Amount { get; set; }
        public int Balance { get; set; }
    }
    public class Transaction
    {
        public int InvoiceID { get;set;} 
        public DateTime InvoiceIDate { get;set;} 
        public DateTime InvoiceDDate { get;set;} 
        public string Invoice_Month1 { get;set;} 
        public string Invoice_Month2 { get;set;} 
        public string Invoice_Month3 { get;set;} 
        public string Invoice_Note { get;set;}
        public string Invoice_Adjustment { get; set; }
        public string Invoice_Type_Name { get; set; } 

    }
    public class Result
    {
        public string type { get; set; }
        public int id { get; set; }
        public DateTime date { get; set; }
        public int amount { get; set; }
    }
}