Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

C# Application - Should I Use Stored Procedures or ADO.NET with C# Programming Techniques?

I have a C# Application I am creating that stores all data in SQL Server.

Sometimes it's easier for me to make data changes programmatically and sometimes it's easier to have stored procedures and functions in the SQL Server database and call them.

I am rather new to programming and I don't know what the subtle advantages and/or disadvantages of each method are.

I figure I should probably be doing things one way or the other instead of using several methods.

My code is starting to look like a random collection of thoughts all just shoved into one spot. No offense to Joel Spolsky. I love his books.

This is a solo project, so I can refactor any amount of code I want to make it better. All options are on the table.

Thanks,

J3r3myK

like image 473
J3r3myK Avatar asked Jan 19 '09 06:01

J3r3myK


People also ask

What C is used for?

C programming language is a machine-independent programming language that is mainly used to create many types of applications and operating systems such as Windows, and other complicated programs such as the Oracle database, Git, Python interpreter, and games and is considered a programming foundation in the process of ...

What is the full name of C?

In the real sense it has no meaning or full form. It was developed by Dennis Ritchie and Ken Thompson at AT&T bell Lab. First, they used to call it as B language then later they made some improvement into it and renamed it as C and its superscript as C++ which was invented by Dr.

What is C in C language?

What is C? C is a general-purpose programming language created by Dennis Ritchie at the Bell Laboratories in 1972. It is a very popular language, despite being old. C is strongly associated with UNIX, as it was developed to write the UNIX operating system.

Is C language easy?

C is a general-purpose language that most programmers learn before moving on to more complex languages. From Unix and Windows to Tic Tac Toe and Photoshop, several of the most commonly used applications today have been built on C. It is easy to learn because: A simple syntax with only 32 keywords.


1 Answers

Well, Stored Procs can be an additional level of abstraction or a set of functions/methods if you look at the database like an object or service. This can be beneficial, since you can hide underlying implementation details and change it when need be without breaking the app (as long as you leave the interface, e.g. the stored proc parameters, alone). Also, if you can hide your table details behind a set of stored procs, no matter how someone gets access to your database, they'll only be able to interact with it using the methods you designed and wrote for it --> there's less risk of someone firing up Excel and hacking into your database.

On the other hand, Stored Proc require extensive T-SQL knowledge and you'll be spreading your business and app logic across two sets of code bases, which can be a downside. Anything you can do in a stored proc can also be done in ADO.NET with straight SQL statements, and it's not even slower anymore.

A further plus for Stored Procs is the fact that if something is wrong in your proc, you can fix it merely by deploying the proc with the fix - you don't need to touch your C# app. This can be a great plus in a "hosted" environment if you only get 3 releases per year - applying a hotfix by means of fixing an Stored Proc can be your live-saver then! :-)

All in all: there are lots of pros and cons for or against stored procs; I'd suggest, if you feel comfortable with writing T-SQL, why not give it a try and see how it works for you. If it feels to cumbersome or too inflexible or like too much work in the long run, you can always switch back to using straight SQL in your C# app.

Just my $0.02. Marc

like image 138
marc_s Avatar answered Oct 02 '22 16:10

marc_s