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

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

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

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

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

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

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


这是我在BAL中的方法：

public List GetCustomerListItem()
{
List customerlist = null;
CustomerListItem item = null;
using (CustomerTableAdapter adp = new CustomerTableAdapter())
{
using (DAL.dstCustomer.CustomerDataTable tbl = adp.GetCustomerDataList())
{
customerlist = new List();
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;
...
}