CRUD With Database First generation

Discussion of open issues, suggestions and bugs regarding Entity Developer - ORM modeling and code generation tool
Post Reply
DGilbert
Posts: 4
Joined: Tue 02 Jul 2013 17:59

CRUD With Database First generation

Post by DGilbert » Tue 02 Jul 2013 21:20

Good day,

I started an evaluation the Entity Developer and I am trying to find out the best way in our case to generate the physical model. This is certainly more than just a reverse engineering of our database because of CRUD generation we are performing in our model.

We are actually generating CRUD stored proc (Create, Read, Update, Delete), triggers, views from templates with another product. With our current case tool I have control of the generation in our template with the help of attributes (table level, package level, model level and so on)

For examples:

- Some stored proc need to be generated 'by hand' if they don't fit the standard
definition (I am blocking it in generation and coding it in another section to
generate the appropriate code)
- I can exclude some columns based on attributes
- I can generate 'code before', 'code after', 'validation code' in templates base on
specific table attributes.

I noticed we can give templates during the 'Database first' approach but I am not sure of the level of control I can get. What would be the best approach or does Entity Developer can really fulfill our needs ?

Thanks for any advice or suggestions.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: CRUD With Database First generation

Post by Shalex » Wed 03 Jul 2013 16:18

Please specify:
1) your ORM framework (Entity Framework / NHibernate / LinqConnect)
2) approach (Database First / Model First /Code First)
3) predefined templates used in your model

DGilbert
Posts: 4
Joined: Tue 02 Jul 2013 17:59

Re: CRUD With Database First generation

Post by DGilbert » Wed 03 Jul 2013 16:29

Hi,

I am using the Entity Framework with a Database First approach. I did not develop any templates yet but I need it to define: database stored proc, triggers and views. I am not sure if there is pre-defined templates availaible for theses tasks but the important thing is the control I can have over them with model/packages/table properties.

This is all concerning the physical model for now.

Thanks

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: CRUD With Database First generation

Post by Shalex » Thu 04 Jul 2013 10:50

DGilbert wrote:- Some stored proc need to be generated 'by hand' if they don't fit the standard definition (I am blocking it in generation and coding it in another section to generate the appropriate code)
Please create a Devart Entity Model (*.edml) with Entity Developer (standalone or embedded in Visual Studio), include one (e.g.: DbContext template) or several templates in the model and generate the code. If this doesn't fit your needs, give us an example of current generation and what you want to be generated.
DGilbert wrote:I can exclude some columns based on attributes
You can remove unnecessary properties from the model (from CSDL only or from both CSDL and SSDL) but leave them in the database.
DGilbert wrote:I can generate 'code before', 'code after', 'validation code' in templates base on specific table attributes.
Give us an example of 'code before' and 'code after'. As for 'validation code', you can set the needed validation framework via template's properties.

DGilbert
Posts: 4
Joined: Tue 02 Jul 2013 17:59

Re: CRUD With Database First generation

Post by DGilbert » Thu 04 Jul 2013 14:45

Here is a sample of what I am doing for my CRUD generation in templates and
I would like to do something similar in Entity Developer. I am not sure where to start with Entity Developer and if it can really fit our needs.

Of course the aim is to generate a DDL script that will create our database. So this is more than creating table, constraints, indexes, triggers. Our Data Access is implemented with then generated stored proc which are build from templates.

.// *******************************
.// First I have access to metadata of the model in my current case tool
.// for example the packages, the tables and so on
.// *******************************

.foreach_item( Packages )
.// I can test base on object name with %Code% variable
.// Here I will not generate CRUD for the table in one of the packages
if %Code% != "TablesOfCodes"
.foreach_item( Tables )
%Generate CRUD%
.next()
.endif
.next()

.// *******************************
.// The %Generate CRUD% template will perform some decisions too
.// These boolean attributes are defined at table level model metadata and I
.// can have many type like string (code before, code after, validation code ...)
.// *******************************

.if %Generate Create Procedure% == true
%Create Template%
.endif

.if %Generate Read Procedure% == true
%Read Template%
.endif

.if %Generate Update Procedure% == true
%Update Template%
.endif

.if %Generate Delete Procedure% == true
%Delete Template%
.endif

.// *******************************
.// A quick %Read Template% sample
.// *******************************

.// First I have control statement for the script
.// As you can see, I have many metadata available, like Schema name,
.// standard prefix (a General attribute)
.// for stored proc and the procedure name will be the table name
.// (remember I am looping for each table in my package)
.// followed by _Read

IF EXISTS (
SELECT 0 FROM sysobjects
WHERE id = object_id('%Schema%%GenOptions.Read Prefix Name%%Code%_Read') AND type = 'P')
DROP PROCEDURE %Schema%%GenOptions.Read Prefix Name%%Code%_Read
GO

---------------------------------------------------------
-- %Schema%%Code% Read Stored Procedure
---------------------------------------------------------

.// %FIND_IDENTITY_KEY% is a template that will return the identity key of a table
.// base on column attributes

CREATE PROCEDURE %Schema%%GenOptions.Read Prefix Name%%Code%_Read
@p%FIND_IDENTITY_KEY% Identifier,
@pReadDeleted int = 0
AS
BEGIN

SET NOCOUNT ON

.// Here another verification, where some table do not have RowDeleted column
.if %EXIST_ROW_DELETE%
IF @pReadDeleted = 0
BEGIN
SELECT
.// In this template I have returning the column name of the table
.// In some other case it can decide to return or not some columns
%RETURNED_COLUMNS_AFTER_READ%

.if %ReadWithNoLock% == true
FROM %Schema%%Code% WITH (NOLOCK)
.else
FROM %Schema%%Code%
.endif
WHERE %FIND_IDENTITY_KEY% = @p%FIND_IDENTITY_KEY%
[%EXIST_ROW_DELETE%? AND %EXIST_ROW_DELETE% = 0 : ]
END
ELSE
...
.else
...
.endif

END
GO

.// Of course I am also managing access right where roles are defined also in table attributes
GRANT EXECUTE ON %Schema%%GenOptions.Read Prefix Name%%Code%_Read TO %READER_ROLE%
GO

.// *******************************
.// Here is some other examples of control that I need in templates
.// base on attributes
.// *******************************

-- SETUP default value
.foreach_item(Columns)
.if (%Computed% == TRUE) or (%Identity% == TRUE) or
(%Is Creatable% == false) or
(%SymbolDataType% == %ROW_DELETE_TYPE%) or
(%SymbolDataType% == %ROW_VERSION_TYPE%)
.else
.if %DefaultValue% != ""
.if %SymbolDataType% == "UUID"
IF (@p%Code% IS NULL) OR (@p%Code% = '00000000-0000-0000-0000-000000000000')
.else
IF @p%Code% IS NULL
.endif

...

.endif
.endif

-- Code based on column that exist
.if (%Exist_Field(ColumnName)%)
...
.endif

-- Validation code before operations

.// Can be anything like special constraints verifications
.// all define in table attributes (model metadata)
%ValidationCodeBeforeCreate%

%ValidationCodeBeforeDelete%


-- Here I am returning the 'readable' column list with some control for
.foreach_item(Columns)
.if (%Is Readable%==false)
.else
%Code%,
.endif
.next(\n)


Hope my requirements are clear enough. For the procedure wrtten by hand, I have a section to store the code in the model as is. It is simply generated. Same place I am generating database functions.

Denis

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: CRUD With Database First generation

Post by Shalex » Fri 05 Jul 2013 13:43

DGilbert wrote:Our Data Access is implemented with then generated stored proc which are build from templates.
Our templates do not generate DDL for stored procedures but do generate C# code basing on the model created by hand or by reverse engineering from database. Why don't you want to create stored procedures in your database via SqlScript (http://www.devart.com/dotconnect/sqlser ... cript.html)?

DGilbert
Posts: 4
Joined: Tue 02 Jul 2013 17:59

Re: CRUD With Database First generation

Post by DGilbert » Fri 05 Jul 2013 14:43

I was hoping to generate and maintain the physical model as well as the logical model (ORM) in a single tool with T4, entity framework and your model file. Actually we are producing a database upgrade script for any database modifications, then, with our own tool, we generate the DataAccess layer. I am looking for an integrated solution to improve our process and maintenance cost. If I cannot manage the physical model, then, I am not improving anything where multiple versions of code and database must be maintained in parallel.

Hope you will consider this need in the futur. With this in hand, you will compete easily with other case tools. Keep on your good work.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: CRUD With Database First generation

Post by Shalex » Mon 08 Jul 2013 15:28

DGilbert wrote:I was hoping to generate and maintain the physical model
You can generate and update tables, constraints, triggers in your database with Entity Developer basing on the *.edml model. But *.edml (and *.edmx as well) does not include logic for generating DDL of stored procedures in the database. The only information about stored procedures in Entity Framework model is the name of stored procedure and its signature.

Post Reply