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!
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.
2
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 https://stackoverflow.com/questions/5829891/which-micro-orm-to-use)
1
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.
(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.
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#.
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.