location of business logic: database vs. C# code

Question

location of business logic: database vs. C# code

Jesse Reich

Hall of Fame

Points: 3020
More actions
September 15, 2009 at 4:33 pm

#226144

At my job, there is new requirement for one of our products to work in "offline" mode. That means the user must be able to view and manipulate data without a SQL Server connection. The decided-upon approach will be to get the critical data from the SQL Server tables when there is a connection, transform the data into objects in C#, and then store those objects locally (either as xml files or using some other method devised by the developers).
But the overall goal is to create a database logic layer that can be used to manipulate and query the data wherever it may be (whether it is local or on SQL Server). This layer will basically do all the things that our stored procedures used to do and our SQL Server database will essentially be used for storage and nothing else.
I have heard of other companies using this approach on a very large scale with all the business logic written in Java and the data stored in MySql.
Is there a place for SQL Server in this type of architecture? I understand the desire to have all the logic in one place, and if you want an off-line mode (and don't want to use SQL Server CE), I don't see how stored procedures and logic in the database can fit into the equation.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

If the picture is to not allow ANY server component, even local, then, no - how could SQL Server be involved?

At best you would have to use something like XML from within .NET executables, with the server component kicking off whenever the client is online/connected to the server. This is essentially how the java components are working.

It's a shame to head in that direction since that essentially defeats ANY ability to secure your information, business logic, proprietary processes, etc....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Glenn Dorling SSCertifiable Points: 7876 More actions · Answer 2

We've had similar requirements in the past that we've satisfied using a local version of MSDB (now fortunately replaced with SQL Server Express) and replicating to the central database(s).

The reference data is replicated to the "client" database using transactional replication (make sure you manage obsolete connections so your log file doesn't blow up and/or keep that database separate to the client database), while the client-entered data is replicated using transactional or merge replication. Both subscriptions are set to fire off whenever they're connected to the central database.

It's a bit fiddly to set up the first time, especially when you haven't used replication before (although no doubt it's better under SQL2k5+: it was a pain using SQL7/MSDB), but it ends up as a pretty robust solution once you've got everything set up and scripted so you can manage obsolete subscriptions and deploy it to new ones.

You may need to look at encryption of that local data too, but that's a separate matter. At least you get to keep all your database logic intact.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

2 important things to take note of before deciding on your approach.

1) Whats the volume of data and what happens when the data grows large.

2) How do you propose to revert the data changes back to the server.

I have come across such situations before and these were the 2 important points. There are of course other things to consider.

"Keep Trying"

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

At my job, there is new requirement for one of our products to work in "offline" mode. That means the user must be able to view and manipulate data without a SQL Server connection

It is called ADO.NET dataset and it is not just SQL Server there are more than ten RDBMS supported and it comes with asynch classes. In Winform application you can do a lot before writing back to SQL Server.

Kind regards,
Gift Peddie

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

8kb (9/15/2009)
At my job, there is new requirement for one of our products to work in "offline" mode. That means the user must be able to view and manipulate data without a SQL Server connection.

I assume that's the definition coming from Business, isn't it?

Let me translate.

What they want is "offline" users to work with no permanent conection with the "central" database; it doesn't mean you can't install SSExpress on their laptops and sync when needed 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.