Background: I work on a small team of around seven developers and DB architects. I'm a mid-level developer at the company, but started as a trainee as a summer job which led to a junior developer position as my first real job in tech. Our tech stack is basically just SQL Server and .NET. Not exactly the tech I hoped to work with, but it's a fine job.
Anyway, I am curious how SQL Server is typically employed in .NET applications since it seems inconvenient to do things the way we do. The bulk, as in almost all, of our business logic resides within SQL Server Stored Procedures that we invoke from within our internal desktop application and integrated Web application.
For example, if we want to get a list of customers born in the '80s, it would go like this: First, we'd start with a bunch of boilerplate SQL to create a Stored Procedure like up_GetCustomersByDOB, accepting DATE arguments such as @dBirthEarliest and @dBirthLatest. Sometimes we do validation checks on parameters before running a Stored Procedure, sometimes we do it within the Stored Procedure before doing any manipulations or returning data, and sometimes we do both. So something like this:
IF OBJECT_ID(N'[dbo].[up_SelectCustomersByDOB]') > 0
DROP PROCEDURE [dbo].[up_SelectCustomersByDOB]
GO
CREATE PROCEDURE [dbo].[up_SelectCustomersByDOB]
@dBirthEarliest DATE,
@dBirthLatest DATE
AS
BEGIN
-- Oversimplified example...
IF @dBirthEarliest >= @dBirthLatest
RAISERROR(11, -1, -1, 'Latest DOB must be later than Earliest DOB.')
SELECT cFirstName
, cFamilyName
, cCustomerID
FROM [dbo].[Customers]
WHERE dBirth BETWEEN @BirthEarliest AND @dBirthLatest
AND lActive = 1
END
GO
And to get our '80s babies we go through hoops after hoops, building parameter lists and passing them around, to eventually run it like you would from SSMS as:
EXEC up_SelectCustomersByDOB
@dBirthEarliest = '1/1/1980',
@dBirthLatest = '12/31/1989'
There is a lot of boilerplate involved in this. Not only do we create a SQL Server Stored Procedure and often repeat the same validation and hard-coded verbiage twice or three times, but we also have to create a functions in a .NET class for each interface (one for the Web and one for the desktop application), then one running on a separate Web API instance which accepts all parameters and passes them all to the business class with usually no manipulation or validation at that step at all. Real redundant.
We get the data back usually with very weak typing. We have to make sure there's a table, count the rows for validation or it'll crash, and then explicitly convert every col by name. On the desktop app, we use typed DataSources which is nice. Not sure why we don't on the Web application... probably some reason.
I'm wondering if it's normal for SQL Server to be used like this. We do the same sort of thing for any update, insertion, etc. It takes forever to get simple things done. Final notes: This is a financial services company. Maybe security related? The application itself is close to twenty years old, but I know the technology is still used.
Just not sure if it's used this way. Happy Friday :-)
I'm not an experienced DBA. I know there are certainly companies who don't do it like that, but I don't have statistics.
But I'd personally favor creating some (stateless) API that does as many of the procedures as possible. For these reasons:
But that's just my personal view, leave a comment if you disagree.
(As for the validation of results you gety back: there are definitely libraries for that in various langauges.)
There's a mindset with SQL Server and like databases that have the capability of having stored procedures to have the database logic in the database itself for reusability and redundancy avoidance.
Yeah, that last bit it somewhat oxymoronic in your situation since you mentioned you have a lot of redundancy. However, it an ideal situation, you wouldn't need to validate in the stored procedure and in the web application. Pick a place and do it one time.
But, the idea behind this is that the same procedures can be used in ANY application that connects to the database. Web application, ETL, reporting, whatever. Single procedure to do one thing is the same across the board. If there's a bug, that bug appears in all those places, but only needs to be fixed in one place. That's the main idea.
So, is this normal? In some respects, yes. A lot of this has been solved multiple times over with ORMs (e.g. NHibernate), though those can often get in the way, too. It's a balancing act.
I normally create my own database wrappers and utility classes that handle a lot of these issues for me, though I still have to map the returned data to strongly typed variables. There's no real getting around that unless you create a smarter wrapper than I have that does that for you by inspecting either the meta data of the returned data to figure out the type, or if you're mapping to a known class, then doing reflection to get the property type and casting that way. It can be done with a little effort.
Austin King
Fullstack Developer
Stored procedures were very popular in the 90s and early 2000s. In my opinion, it started to die out because of a couple of trends: