This post describes the CodeSmith Template I created to generate business objects from views. A SQL view is not used to generate the business object directly, but rather a temporary SQL table is used. The results are the same in the end, however.
The template is located here (and at the CodeSmith Shared Templates Forum.) The resulting business object from this process is shown here.
Process overview
- Create a temporary SQL table from View
- Add Primary Key to temporary table
- Generate Business Object.
- Delete temporary table.
Details
We're going to create a Business Object called UserEdit, based on a view called V_USER_EDIT.
1. Create a temporary SQL table from View. In VS.NET right-click on the view, select “Design View.” Copy-n-paste SQL code into MSQuery and use “insert into tmp_yourtable“ to create a temporary table from which we will generate the business object. As in
SELECT dbo.users_ids.uid, dbo.users_ids.last_name, dbo.users_ids.first_name, dbo.users_details.member_bit,
dbo.users_details.cid, dbo.users_details.ustid, dbo.users_details.work_phone,
INTO TMP_USEREDIT
FROM dbo.users_ids INNER JOIN
dbo.users_details ON dbo.users_ids.uid = dbo.users_details.uid
Screenshot below. (Click on images to view full-size.)
2. Now we have our table called TMP_USEREDIT to use with our CodeSmith template, which in our application will be instantiated from V_USER_EDIT. The only thing we need to do with the temporary table is to change UID into a Primary Key.
3. Now we can create the business object in CodeSmith. Fire-up the CreateBusObjectFromView.CST template where we have to provide 3 context variables and the source data table.
ClassName: We'll use “UserEdit.” This will be the name of our business object class.
KeyField: UID. The primary key field we created in the temporary table.
SQLView: V_USER_EDIT. The view our business object will use to be populated.
SourceTable: TMP_USEREDIT. Our temporary table.
A screenshot is below. A .TXT copy of the .CS generated class is located here, where you'll see the role of the four variables you just entered.
4. Delete the temporary table. You don't need it anymore. Enjoy your business object! Get the CodeSmith template here in case you missed the earlier link.