How to correctly create BLL and GridView in such situation

I have designed a database, in order to better demonstrate my question, I've simplified it:

TB: CONSULTANTS

      ID        uniqueidentifier

      NAME      NVARCHAR(30)

TB: EXPENSES

      ID                uniqueidentifier

      AMOUNT            money

      CONSULTANTID      uniqueidentifier

CONSULTANTID is FK from Consultants Table, simple right?

I have created a dataset with two Data Tables which are consultants and expenses

with corresponding queries such as get expenses by consultant id, etc

however, what i wanna display in grid view is:

Amount & Consultant's Name instead of Consultant Id

When I tried to return a data table with (ExpenseId, Amount, ConsultantName), it causes error since schema doesn't match with base data table EXPENSES (ExpenseId, Amount, ConsultantId).

What can I do to get (ExpenseId, Amount, ConsultantName(not id)) from BLL ?

Do I have to create another data table with joining two tables together?? because ASP.NET site says that's not a preferable way to create DAL and BLL

OR is that possible that I do some changes on gridview's BoundField (if that's possible, how to bind two different adapters' outputs together).

Solution

Well, you can create a Method in DAL that fetches data from the DB and returns you the DataTable or DataSet as appropriate for you.

Select c.id as ConsultantId,c.Name as Name,e.amount
from consultant c
inner join experses e
on c.ConsultantId=e.ConsultantId

Let your BAL do bussiness logic such as creating Consultant Class object Array and do validation on amount(such as amount can't be less than 0).

BAL returns Array of Consultant objects which you can use to bind with data control(or you can even send back the dataTable from the BAL as well.

I think Linq is using the first approach where it generates the sql script from the Linq Syntax and returns you collaction of object of particular type.