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 :-)