c# - 通过 WCF 服务 c# 使用存储过程(具有连接)填充 DataGridView

Question

我正在调用 WCF 服务，该服务为我提供了 BAL 中具有指定字段值的客户列表。

当我初始化它以在 DataGridView 中显示数据时，所有具有来自 Customer 表的相应数据类型值的内容都会显示(例如 FirstName、LastName 和 Phone1)。

但是，我想在存储过程中使用 inner join 从 Country 表中检索的值不想在 Customer< 中显示相应的值 表。

错误是 Customer 表中的 CountryId 是 DBNull，我该如何解决这个特定示例。

这是我绑定(bind)数据的代码(适用于某些领域):

IHotRes res = new MHotServiceProvider().Service;
List<CustomerListItem> customerlist = res.GetCustomerListItem();
_ListData = ToDataTable(customerlist);

这是我在 BAL 中的方法:

public List<CustomerListItem> GetCustomerListItem()
{
    List<CustomerListItem> customerlist = null;
    CustomerListItem item = null;
    using (CustomerTableAdapter adp = new CustomerTableAdapter())
    {
        using (DAL.dstCustomer.CustomerDataTable tbl = adp.GetCustomerDataList())
        {
            customerlist = new List<CustomerListItem>();
            foreach (var row in tbl)
            {
                item = new CustomerListItem();
                item.FirstName = row.FirstName;
                item.LastName = row.LastName;
                item.Phone1 = row.Phone1;
                string mystring = row.CountryId.ToString(); //i tried to convert it to string but it still gives me the error that 'The value for column 'CountryId' in table 'Customer' is DBNull.'
                item.CountryId = mystring;
                //item.NationalityId = row.NationalityId;
                customerlist.Add(item);
            }
        }
    }
    return customerlist;
}

这是我的存储过程:

CREATE PROCEDURE [dbo].[sp_CustomerDataList] 
AS
BEGIN
    SET NOCOUNT ON;
SELECT cu.FirstName, cu.LastName, cu.Phone1, co.CountryName, n.Nationality 
FROM Customer cu
Left Join Country co
ON cu.CountryId = co.CountryId
Left Join Nationality n
ON cu.NationalityId = n.NationalityId
WHERE cu.IsDeleted = 0
END

如果需要更多代码示例，请告诉我。

Answer 1

将 CountryId 添加到存储过程:

CREATE PROCEDURE [dbo].[sp_CustomerDataList] 
AS
BEGIN
    SET NOCOUNT ON;
SELECT cu.FirstName, cu.LastName, cu.Phone1, co.CountryId, co.CountryName, n.Nationality 
FROM Customer cu
Left Join Country co
ON cu.CountryId = co.CountryId
Left Join Nationality n
ON cu.NationalityId = n.NationalityId
WHERE cu.IsDeleted = 0
END

然后将 CountryName 添加到模型中:

public class CustomerListItem
{
    string CountryName = string.Empty;
...
}