Populate the DataGridView with a stored procedure (which has connections) through the C # WCF service

Question

I call the WCF service, which gives me a list of clients with the specified field values ​​in the BAL.

When I initialize it to display data DataGridView, there is all that have corresponding data type value from the table Customer(for example, FirstName, LastNameand Phone1).

However, the values ​​that I want to extract from the table Countryusing inner joinin the stored procedure do not want to display the corresponding values ​​in the table Customer.

And the error is that the table CountryIdis equal DBNullin the table Customer, how to solve it for this specific example.

Here is my data binding code (which works for certain fields):

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

This is my method in 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;
...
}

Answer 2

, . :

, :

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

, . , ( ).

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;

                //Here the new line, where we actually populate our new model property
                item.CountryName  = row.CountryName;
                customerlist.Add(item);
            }
        }
    }
    return customerlist;
}

TableAdapter, , sproc.

- ( ), "configure". , DataTable . ( select , , , ).

, , GetCustomerDataList() "configure", , sproc. , , .

MSDN TableAdapters DataSets .

https://msdn.microsoft.com/library/04y282hb(v=VS.140).aspx https://msdn.microsoft.com/en-us/library/ms171902(v=vs.140).aspx

, .