Integrate C# SQL CLR Database Project in a MVC3 Web Application?

Question

Background: I have already written a CLR Database Project which holds all the Business Logic for a Data-Store I'm creating. It is deployed on my SQL Server where it is referenced by various CLR StoredProcedures which take care of data imports. Now I'm working on the Web Frontend and have for this purpose started writing an MVC3 Project. Since I already have the complete Business Logic(object definitions, quality checks) I want to use the code in my MVC Application.

I figured the best way to do this is to add the BusinessLogic CLR Project to my MVC Solution and referenced it in the MVC project. That works fine, and I can use the Code. With some patience I get through most difficulties.

Issues:

1) When I want to debug the MVC solution, Visual Studio always wants to deploy the CLR Assembly. I get around this by unloading the CLR Project in the MVC solution before debugging.

2) Getting a UserDefinedType Reference from my SQLServer also seems to be a problem:

3) I deploy the same code twice on my Server, once with the SQL Assemblies, and once with my MVC frontend.

Question:

Is there a best-practice for using the same Code in SQL Server Assemblies as well as in the Frontend Project? Maybe a way to reference the deployed BusinessLogic assembly on the SQL Server? Any changes in Business Logic should be accessible both to front-end code as well as to DataImport stuff on the SQL Server. The current solution doesn't feel right, since I have to deploy the same code 2x on my Server, once with the SQL Assemblies, and once with my MVC frontend.

Any partial answers(i.e. glimmers of making sense) would be appreciated. I'm pretty much on my own with this.

James · Accepted Answer · 2012-07-25 18:27:10Z

2

The Business Logic code could be in it's own class library, and project.

The CLR database solution then includes this BL project, and the MVC solution also includes the BL project. But the 2 solutions are separate. This would seem to solve the immediate problem - although then if you change the BL code, you then have to do 2 deployments...

It may just be that this BL layer is not living in the correct place. It would be nice if this BL lived in a service somewhere, and both the CLR procs and the MVC project accessed it in this one place.

answered Jul 25, 2012 at 18:27

James

2,86124 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sergei Rogovtcev Over a year ago

I agree with the part "BL layer not living in the correct place". SQL Server is (in most cases) not the correct place to do business logic, especially with CLR.

Rafael Emshoff Over a year ago

Can you give a hint at what you mean with "service somewhere". I further have the Issue of the UDT, which is referenced in the BusinessLogic. (The UDT should also be available in the Frontend as well as server assemblies, since it is needed to manage writing the Data.) Is it possible to link the UserDefinedType Assembly from the Server into the seperate BusinessLogic class Library?

Rafael Emshoff Over a year ago

I googled around a bit and understand that by "service somewhere" you mean something like Windows Communication Foundation Service (.NET 4). And it looks like I can add the Service reference both to my CLR Assemblies as well as to my MVC application. Now I just need to find the time to try it out : ) Thx for the hint.

Collectives™ on Stack Overflow

Integrate C# SQL CLR Database Project in a MVC3 Web Application?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related