DataSets and Identity columns

[Abattoir]

I have been using databinding and datasets in my last few applications, but there's something that I'm missing somewhere. I'm so close, I can see the potential, but something isn't quite right.

DataGridViews and DataSets seem to be very easy to set up when there is a direct one-to-one correlation between tables in a database, and grids on a form. Unfortunately, this is never the case.

My problem is usually a simple one. Say I have a table (Table1) in my database with an autoinc primary key, some data, and then an employeeID referring to a different table (Table2). I would like my DataGridView to display all of the rows in Table1, but substitute employeeID for the employee's name, taken from Table2. There is a foreign key relationship on employeeID, and it is the primary key of Table2.

It should be so simple, but I can't seem to do it without modifying the DataSet, and that doesn't seem right. Can anyone clue me in as to where I'm going wrong?

 

[fpineda101]

Create a function that takes in the employeeId as input and returns the employees name. Embed the function in your SELECT like this...

SELECT get_emp_name(employee_id) FROM table1

get_emp_name is the name of the function. You can name it whatever you please, just using it as an example​

 

[Abattoir]

Thanks, but that doesn't quite address my problem. I'm looking for a database-independent solution, as my problem is more with the architecture of DataSets in general. My DataSet reflected the design of my database, and I wanted to display related data from 2 independent DataTables in one DataGridView. I don't think this is supported by .NET.

In the end, I added a column to the child DataTable, called it EmployeeName, and set the Expression property to automatically look up the name from the parent table. Modifying the DataSet to meet a particular function's needs seemed wrong, but at least it works without any manual data population, stored procedures, or having to manually create the INSERT/UPDATE/DELETE statements.

 

[TechGnome]

*scratches head* what's wrong with a join in your select?

Two ways I see of "fixing" this (even though it's not broke).
1) USe a join in the initial SELECT statement. Problem is that not all DBMS's implement joins the same way.
2) Load Table1 in to a DataSet, then load Table2 into the same DataSet, then Create a DataRelation, linking the two DataTables, then create a DataView based on the two linked DataTables. It's a little more work, but it fits the Database independancy requirement.

-tg