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

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

但是,我想在存储过程中使用内部联接从Country表中检索的值不希望在Customer表中显示相应的值.

而错误是CountryId在Customer表中是DBNull,我该如何为这个特定的例子解决它.

这是我的绑定数据代码(适用于某些字段)：

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

如果需要更多代码示例,请告诉我们.

解决方法:

将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;
...
}