I've used LLBLGen on previous projects. I am at a client where I feel this LLBLGen would do wonders. I'm preparing to make a case to the architecture team in favor of using LLBLGen, and am trying to identify possible objections that the architects will make. Some of the objections I've heard so far include security (using Dynamic SQL is typically associated with security risks) and performance (i.e. it will not perform well). Are there answers to these concerns that I can be armed with? Is there an appropriate forum to have this discussion under?

The Dynamic sql is always using parameters, so no sql injection attacks are possible. The Dynamic Sql is generated using the data at hand, so updates, inserts only affect fields which are changed, making the queries be more compact and efficient. For fetching, the developer can specify advanced prefetching paths which will produce 1 query per related type in the graph to fetch, which can greatly enhance performance. The SQL queries use full catalog/schema names for target elements so sql optimizers have an easier job. The queries generated are equal for the same piece of code executed, which means that cached query plans are re-used because the query is equal to an already executed query.

People who think stored procs are more secure often forget that if I have access to pr_DeleteCustomer(@id) I can remove any customer I want as well (example). With the authorization logic in the framework, developers can easily inject security code (through dependency injection) to prevent users from performing low-level actions like changing fields on a given entity, removing it, and even viewing data.

Writing that all by hand on a stored proc API takes a lot of time and it has to be maintained and debugged.

Good luck

Thanks for your response. I'm actually still coming up with the approach for how to get the LLBLGen framework to work with the existing architecture at this company. So, I thought I would describe this architecture to see if you have heard of anything similar, and, if so, what you would recommend.

The architecture that the company uses is called a "Message Broker" scheme. The purpose is to separate the database from applications that consume the data. The tiers are 1) Application, 2) Message Broker, 3) Service Layer and 4) Database. The Service Layer contains stored procedures, business logic, and the entity/collection definitions that it can return. The Message Broker receives requests from the Application (a string indicating the method name), and invokes the correct assembly/class/method from the Service Layer. The Message Broker sends an XML-representation of the requested data to the Application, which translates it using a definition of the entity that the application expects. The Application has to use the same definition of the various entities that are defined in the Service Layer. So, essentially, it's a little bit like how in classic ASP applications you would call Server.CreateObject and send in the string of the COM class that you want... COM would look up the method and determine what to send back. In this case, it's similar - the Application sends a string that represents the function, the Service Broker determines what you want and sends the XML data back to the Application, which the Application then has to translate.

This seems a little convoluted, but the reason for it is that the company is in the process of converting from a pure SQL Server environment to a mixed SQL Server/Oracle one, and the Message Broker scheme is meant to separate the applications from the databases while this migration is taking place. What I'm trying to figure out is how to get the many benefits that LLBLGen provides and still operate in this Message Broker architecture. Is there anything based on the plug-ins that could possibly be utilized?

Anyway, any thoughts on the matter would be appreciated. If you would like any more details of the Message Broker architecture, let me know. I think LLBLGen would help this company tremendously, and I'm trying to position the product in such a way as to maximize the possibility of it being adopted.

lbergman wrote:

Thanks for your response. I'm actually still coming up with the approach for how to get the LLBLGen framework to work with the existing architecture at this company. So, I thought I would describe this architecture to see if you have heard of anything similar, and, if so, what you would recommend.

The architecture that the company uses is called a "Message Broker" scheme. The purpose is to separate the database from applications that consume the data. The tiers are 1) Application, 2) Message Broker, 3) Service Layer and 4) Database. The Service Layer contains stored procedures, business logic, and the entity/collection definitions that it can return. The Message Broker receives requests from the Application (a string indicating the method name), and invokes the correct assembly/class/method from the Service Layer. The Message Broker sends an XML-representation of the requested data to the Application, which translates it using a definition of the entity that the application expects. The Application has to use the same definition of the various entities that are defined in the Service Layer. So, essentially, it's a little bit like how in classic ASP applications you would call Server.CreateObject and send in the string of the COM class that you want... COM would look up the method and determine what to send back. In this case, it's similar - the Application sends a string that represents the function, the Service Broker determines what you want and sends the XML data back to the Application, which the Application then has to translate.

Why would anyone go through all this trouble, make an application so unmaintainable and complex to just avoid a connection between application and database? I'm pretty sure there's no solid reason why that connection is 'bad'. (after all, the database IS part of the application, no way around that)

This seems a little convoluted, but the reason for it is that the company is in the process of converting from a pure SQL Server environment to a mixed SQL Server/Oracle one, and the Message Broker scheme is meant to separate the applications from the databases while this migration is taking place. What I'm trying to figure out is how to get the many benefits that LLBLGen provides and still operate in this Message Broker architecture. Is there anything based on the plug-ins that could possibly be utilized?

LLBLGen Pro offers the ability (with Adapter) to write an application against a single set of entities (the dbgeneric project) and against the interface IDataAccessAdapter for data-access to the db in question.

Roughly it goes like this: - you create a project on sqlserver for example - you generate Adapter code, which produces a DB generic and a DB specific project - you use the project converter from the customer area and convert the sqlserver .lgp to an Oracle .lgp (so you have two files now) - you use the DDL SQL templates to produce an Oracle DDL SQL file which produces the schema on oracle - You load the oracle lgp file and make sure the types match. (e.g. if you use bit fields on sqlserver, you have to use NUMBER(1,0) for example and a typeconverter) - you generate Adapter code from the oracle project as well. Entity names etc. have to be the same as the sqlserver one - you throw away the dbgeneric project, and keep the db specific project.

You now have three vs.net projects: one db generic (with the entity classes) and two db specific projects. You create a simple factory which produces the DataAccessAdapter instance for the db to use.

Your code then looks like: using(IDataAccessAdapter adapter = YourAdapterFactory.Create(dbType)) { adapter.SaveEntity(e); }

this is db generic code, and you can fetch entities from sqlserver and save in oracle or vice versa.

Of course, if part of the schema is in oracle and the other half is in sqlserver, you have a problem, however I would find such a setup highly unusual.

Anyway, any thoughts on the matter would be appreciated. If you would like any more details of the Message Broker architecture, let me know. I think LLBLGen would help this company tremendously, and I'm trying to position the product in such a way as to maximize the possibility of it being adopted.

From what I understand from that setup, it's a typical webservice setup where the database is placed as a webservice in the application. This is a typical anti-pattern, as it makes the service very chatty and there's a lot of overhead (data has to be converted to XML, sent back to the application etc.).

Keep in mind that things which are invented for just a 'transition' shouldn't be used for the final version, and everyone should pay attention that that indeed is also the case. Many projects fail miserably because some temporary drivel is kept alive forever.

I mean: passing strings to a broker which then will call the method in the string... that's unmaintainable: you don't have compile time checking, and therefore making changes is highly risky.

Hi Bergman -

Were you able to convince the Architecture Team?

Ryan