Wednesday, December 24, 2008

Using VB JoinView class in C# web application

JoinView is a class developed by Microsoft to address a feature that is lacking in ADO.Net, up to .Net framework 3.5. The main functionality is to provide something like a DataView for when two or more tables are joined via DataRelations. For more details, and the VB.Net source code, see
HOW TO: Implement a Custom DataView Class in Visual Basic .NET
http://support.microsoft.com/kb/325682

Using the VB JoinView class in a C# application is explained nicely at in the article:
Data From Multiple Tables in a DataGridView
http://www.onteorasoftware.net/post/Data-From-Multiple-Tables-in-a-DataGridView.aspx

However the article above does not explain how to actually make the VB code into a usable product, ie. a dll, so that it can be used in C#.
This blog explains how to construct the VB JoinView class into a dll and how to link it to a C# application.

1. Download the source file JoinView.vb from the first link above. The actual file to be downloaded is called JoinView.exe.

2. Assuming you are working in a C# project called Cproj, inside the solution called Soln. Then in the Soln solution, add a new VB project called JoinViewProj.

3. Manually copy the JOinView.vb file into the location of JoinViewProj.

4. In VisualStudio, use "Add Existing Item" to add the JoinView.vb file into the JoinViewProj project.

5. Build the JoinViewProj project.

6. Go to the Cproj project and add reference. Browse and locate the file JoinViewProj\bin\Debug\JoinView.dll and add this as the reference.

7. In the C# code add this statement: "using JoinViewProj;"

8. Now we are ready to use the JoinView class in our C# code. For example: "JoinView jv;"

The actual usage of the JoinView class is as follows:

ds.Relations.Add("CustOrd", ds.Tables["Cust"].Columns["CustomerID"], ds.Tables["Ord"].Columns["CustomerID"]);
ds.Relations.Add("EmpOrd", ds.Tables["Emp"].Columns["EmployeeID"], ds.Tables["Ord"].Columns["EmployeeID"]);
JoinView jv;
jv = new JoinView(ds.Tables["Ord"],
"OrderID,CustomerID,EmployeeID,OrderDate,CustOrd.CompanyName Company,CustOrd.ContactName Contact,CustOrd.ContactTitle Position,EmpOrd.FirstName,EmpOrd.LastName",
"OrderID='312'", "CustomerID DESC");

First argument: This appears to be the common child table in the two DataRelations.

Second argument: This is the names of the columns of the tables directly, such as "OrderID" which belongs to the "Ord" table. Other names like "CustOrd.CompanyName Company" comes from the Data Relation "CustOrd" and field "CompanyName"; and the name "Company" is an alias that will appear in the grid view.

Third argument: Filter the rows. In the example, only OrderID being 312 are selected. Note it appears taht the field, eg OrderID, must belong to the specified table, in this case, the "ord" table.

Fourth argument: Sort the rows in ASC or DESC order. In the example, it is sorted by CustomerID in a descending way. Note it appears the field, eg CustomerID, must belong to the specified table, in this case, the "ord" table.