Critique my LINQ to SQL strategy using stored procs, please

[dcs]

My group is about to embark on building a new ASP.Net web application.
LINQ to SQL seems like a good option for our data access needs, but
I'm not sure I'm using it correctly. I'm hoping someone can critique
my strategy before I get too deep into it.

Here's the scenario:
1. We will be using all stored procedures. No SQL will reside in
the .Net code (company policy)
2. All business logic will reside in the stored procs (company
policy)
3. We expect a total of about 500 stored procs will be created
for this app. The vast majority of these procs will be simple, one-
statement SELECT, INSERT, UPDATE, and DELETE procs.
4. The web app will have moderate use on an intranet by no more
than 10 users.

My strategy:
A. Using LINQ to SQL, create a single LINQ to SQL dbml
B. One-by-one, drag my procs into the designer. No db tables
would be added to the designer
C. Call the stored procs directly from code
D. When filling grids, formviews, etc, use: FormView1.DataSource
= myDataClass.mySelectStoredProc(23); FormView1.Bind();
E. When updating, inserting or deleting, call the proc directly,
as in: TextBox txt = (TextBox)FormView1.FindControl("User_ID");
myDataClass.myDeleteStoredProc(txt.Text);
F. All validation would be done with ASP.Net validation controls

I expect little or no custom code to be written at the data access
level. I have played with this and it appears to work very well. My
question: am I being horribly naive? Are there compelling reasons to
move beyond this simple approach given my all-stored-procs mandate?
Please critique.

Thank you!

 

[christoff]

My group is about to embark on building a new ASP.Net web application.
LINQ to SQL seems like a good option for our data access needs, but
I'm not sure I'm using it correctly. I'm hoping someone can critique
my strategy before I get too deep into it.

Here's the scenario:
     1. We will be using all stored procedures. No SQL will reside in
the .Net code (company policy)
     2. All business logic will reside in the stored procs (company
policy)
     3. We expect a total of about 500 stored procs will be created
for this app. The vast majority of these procs will be simple, one-
statement SELECT, INSERT, UPDATE, and DELETE procs.
     4. The web app will have moderate use on an intranet by no more
than 10 users.

My strategy:
     A. Using LINQ to SQL, create a single LINQ to SQL dbml
     B. One-by-one, drag my procs into the designer. No db tables
would be added to the designer
     C. Call the stored procs directly from code
     D. When filling grids, formviews, etc, use: FormView1.DataSource
= myDataClass.mySelectStoredProc(23); FormView1.Bind();
     E. When updating, inserting or deleting, call the proc directly,
as in: TextBox txt = (TextBox)FormView1.FindControl("User_ID");
myDataClass.myDeleteStoredProc(txt.Text);
     F. All validation would be done with ASP.Net validation controls

I expect little or no custom code to be written at the data access
level. I have played with this and it appears to work very well. My
question: am I being horribly naive? Are there compelling reasons to
move beyond this simple approach given my all-stored-procs mandate?
Please critique.

Thank you!

I was writing some time ago an application for BarCode scanner device
with WIn Mobile 5.0 CE and main approach to this was , that all
procedure should be on SQL Server , and it does`t paid off. I think
that user interface is to take values from user , check it and write
to SQL, therefore Insert statement shoud be HardCoded. And according
my experience maintain more than 100 procedures is difficult , and You
are talking about 500 !! You have to remember that application should
be easy to maintain for various programmers .

Regards

 

[dcs]

Thanks for your response. The use of stored procs is mandated by the
organization, so I don't have much choice in that respect. I'm looking
for the most efficient way to access the procs from the web app. My
question is more about my use of LINQ and whether the use of the
unmodified data classes as created by drag-and-drop is valid and
viable.

 

[Hans Kesting]

(e-mail address removed) expressed precisely :

My group is about to embark on building a new ASP.Net web application.
LINQ to SQL seems like a good option for our data access needs, but
I'm not sure I'm using it correctly. I'm hoping someone can critique
my strategy before I get too deep into it.

Here's the scenario:
1. We will be using all stored procedures. No SQL will reside in
the .Net code (company policy)
2. All business logic will reside in the stored procs (company
policy)
3. We expect a total of about 500 stored procs will be created
for this app. The vast majority of these procs will be simple, one-
statement SELECT, INSERT, UPDATE, and DELETE procs.
4. The web app will have moderate use on an intranet by no more
than 10 users.

My strategy:
A. Using LINQ to SQL, create a single LINQ to SQL dbml
B. One-by-one, drag my procs into the designer. No db tables
would be added to the designer
C. Call the stored procs directly from code
D. When filling grids, formviews, etc, use: FormView1.DataSource
= myDataClass.mySelectStoredProc(23); FormView1.Bind();
E. When updating, inserting or deleting, call the proc directly,
as in: TextBox txt = (TextBox)FormView1.FindControl("User_ID");
myDataClass.myDeleteStoredProc(txt.Text);
F. All validation would be done with ASP.Net validation controls

I expect little or no custom code to be written at the data access
level. I have played with this and it appears to work very well. My
question: am I being horribly naive? Are there compelling reasons to
move beyond this simple approach given my all-stored-procs mandate?
Please critique.

Thank you!

Linq to SQL and other O/R mappers are a "grey area" between "SQL in
code" and "stored procedures".
If you use Linq, then you do not build SQL code-strings in your C# (I
can understand why the company doesn't want that), instead the O/R
mapper handles that for you. The code you write even gets checked by
the compiler.
The real power of linq comes from generating the sql on-the-fly, based
on your need of the moment (filtering etc), you won't get that if you
use sp's.

The argument "stored-procs are faster" is not valid anymore.


So maybe the "all-stored-procs" mandate should be modified somewhat to
"do not include sql strings in your code".

Hans Kesting

 

[dcs]

Thank you, Hans. I can see from researching LINQ that the on-the-fly
dynamic SQL creation is very useful, but in my case all of the SQL
must be captured in stored procs. Putting sql in the application is
not acceptable, whether it be hard-coded "SELECT [this] FROM [that],
LINQ "SQL" statements (var products = from p in
northwind.Products...), or LINQ's on-the-fly dynamic SQL. My task is
to identify the best data access strategy given this environment.
Should it be LINQ (as described in the original message),
SQLDataSource, or some other method? I was attracted to LINQ because
it seems like a very clean way of calling procs from the web
application, but I am not locked in to using LINQ.

 

[Cor Ligthert[MVP]]

As the main company policy is to build Stored Procedures then why not do
that.

As Linq to SQL gets its power with DataContexts (which are dynamicly build)
then you can not use that.

Simple don't do that and as your product fails, then you have a main problem
solved.

Cor