Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm currently trying to write a class library for a connection to a database. Looking over it, there are several different types of queries: Select From, Update, Insert, etc.

My question is, what is the best practice for writing these queries in a C# application?

Should I have a separate method for each of them(i.e. Update(), Insert()), or have a generic Query() that would be able to handle all of these? Thanks for any and all help!

share|improve this question
add comment

closed as not constructive by Doc Brown, Walter, ChrisF Oct 6 '12 at 21:52

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

6 Answers

up vote 7 down vote accepted

First, a bit about the lingo. Insert, update are not queries. A query in RDBMS is strictly a SELECT (Or a a sub-statement having WHERE clause). The set of verbs: SELECT, INSERT, UPDATE, DELETE are action statements in Data Manipulation Language (DML).

Most answers so far revlove around how to achieve your objective, but your question is:

Should I have a separate method for each of them(i.e. Update(), Insert()), or have a generic Query()

The answer is yes. Each method should perform 1 function and return 1 type of result. You can re-use the connection logic or the ObjectContext (if you use Entity Framework) in each.

Most of the code for handling the database would typically live in a separate tier or layer from your presentation logic.

share|improve this answer
2  
Having worked on more than one project where there was a generic "Query" method, yes, separate them! It's very common to see generic query code that allows SQL injection attacks. –  jfrankcarr Oct 4 '12 at 18:57
    
Thank you. I know of Entity and NHibernate, but I wanted to try to do something on my own. –  MyCodeSucks Oct 4 '12 at 20:16
add comment

Best practice is not to write such a lib at all. Use an OR mapper like MS entity framework or any of the available lightweight micro-ORMs (for example, see here http://stackoverflow.com/questions/5829891/which-micro-orm-to-use)

share|improve this answer
5  
...best practice would be to first evaluate if ORM is even appropriate. –  FrustratedWithFormsDesigner Oct 4 '12 at 17:59
add comment

As others have suggested, you can go with ORMs (unless you feel that ORMs are an antipattern). If for whatever reason you can't go with an existing ORM, you'll want to look up some DAO (Data Access Object) patterns to see if what fits. Another option would be to have the actual DML/SQL in a stored procedure and call the procedure by name. This would be more useful if your operations are a bit more complicated than just SELECT/UPDATE/INSERT.

share|improve this answer
add comment

(Generic, language independent answer)

Often in DB abstraction classes you see two methods - Query() and Execute(). Query() returns results, Execute() returns success/failure.

This allows for

results = db.Query('select....', parms)
while (row = results.whatever())
{
...
}    

and

if (db.Query( 'update...', parms))
{
  //do something else
}

I dont see the need to split up the Execute() method into Insert() and Update() methods unless you're trying to make a query builder and are using your db layer to actually construct the sql.

share|improve this answer
add comment

One of the practices is the usage of ORM mapping frameworks (like Entity Framework, nHibernate, etc.)

You may utilize the power of LINQ syntax with any .NET framework languages (C# and VB.NET particularly).

As a quick introduction, you may look at Getting Started with LINQ in C# and then with LINQ samples for C# while using these mentioned ORM's.

For some considerations, if you plan to skip ORM's then you may safely utilize stored procedures as a common way of maintaining SQL outside of C#.

share|improve this answer
add comment

It depends

There are many different factors that go into designing your interface. The goal of this interface is to get out of the way of the caller, so that they can go about their business without worrying about the details of data persistence and retrieval (which are many).

That being said, one good place to start is to research the work of others who have come before you. Your application doesn't seem like it's doing anything groundbreaking, so don't reinvent too much of the wheel.

ORMs

EntityFramework
NHibernate

RollYourOwn

Other ways to approach the problem of interfacting .NET code and databases include, but are not limited to, LINQ to SQL, CQRS, Repository Pattern, any and all of which could be used in rolling your own variety of ORM
As a bonus, RavenDB can serve as an example of a persistence library that serves up a fairly robust C# API.

All that being said, I STRONGLY recommend against rolling your own persistence layer. Use an ORM that is widely used and vetted, and you'll save yourself A TON of pain.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.