Home > Developing Using CodeFluent Entities, SQL Server > How to work with existing database tables with CodeFluent Entities

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)

New CodeFluent Project

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

Raw Method

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.

Settings

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.

Settings

3. Last, we need to disable the concurrency mode. Let us set the Concurrency Mode to none.

Settings

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!

Table List

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!

Vincent Patry

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s