How to work with existing database tables with CodeFluent Entities
Did you know that you could use CodeFluent Entities to work with existing database tables, such as the sys.object table for example, with zero code, and without modifying them?
In this post, we will demonstrate how to display a list of all tables in your SQL database. To follow this demo, you need to be familiar with CodeFluent Entities, and understand concepts such as entities, CFQL and raw methods.
The first thing is to create a CodeFluent project (DemoRaw.Model), a class library project for our bom (DemoRaw), and an empty MVC3 web project (DemoRaw.Web) to display our list. Then, we need to add 3 producers:
- the BOM producer
- the SQL Server producer
- the MVC3 producer
If you need help with creating a CodeFluent Entities project, you may need to look at other articles on this blog before continuing.
Next, we create an entity called SysObject, and add three properties:
- ObjectId (int)
- Name (string)
- Schema (string)
In our entity, let us add a CFQL method called LoadMyObjects. We use load raw in our stored procedure to return a collection of SysObject. Our stored procedure retrieves three properties from our sys.object table. You need to make sure that the fields name match the ones of our entity in the CodeFluent model. That way, the mapping will be handled automatically. The SQL stored procedure is like this:
select name as [SysObject_Name], object_id as [SysObject_ObjectId], schema_name(schema_id) as [SysObject_Schema] from sys.objects
Now, we need to update three settings before generating our model:
1. Because we do not need to create the SysObject table in SQL, we need to set the Produce Table property to false.
2. Our table does not need to have the tracking properties enabled, so we can remove them using the Tracking Modes property. Let us remove the User and Time from the list.
3. Last, we need to disable the concurrency mode. Let us set the Concurrency Mode to none.
Our model is ready to be built. Let us build it! Do not forget to add the DemoRaw reference to your DemoRaw.Web project before running it! Your default web browser opens, click on Sys Object, then Load My Objects, and you should see the list of tables!
This is how you can retrieve data from a specific SQL table, map them to a collection of objects, and display them on a webpage, all of this in a few minutes. If you want to go further, you can add more methods to manipulate the object. Happy CodeFluenting!