7

A few days ago, I read an answer stating that writing queries within your c# code are long gone.

I'm not sure what the specific person meant with the comment but, it got me thinking. At the company where i work, we maintain an assembly containing all the queries to the database (let's call it Queries). This assembly is reference by a QueryService (Retrieve the correct queries) assembly which in turn is referenced by a UnitOfWork assembly (The database connector classes, we have different connector classes for SQL, MySQL etc.). We use these three assemblies to perform operations on our database and all queries/commands are written by C# code.

Is there a better way to communicate with the database and is there a better way to communicate with different database types?

Improve this question
  • How do you insert parameters into your SQL? And how do you manage the differences in syntax between SQL and MySql? – pdr Sep 28 '12 at 13:07
  • 3
    Please say yes to this question: "Do you use command objects and add command parameters to them to actually execute the SQL?" If you didn't answer yes, you have a serious problem, I cannot understate how large a problem that a no answer to that question is. – Jimmy Hoffa Sep 28 '12 at 15:20
4

ORMs only work with simple database structures. For the most part I guess this is OK. If like me you need to get more out of your database than just select/update etc then I have found ORMs are not a good fit and will make your code longer, less clear, more difficult to manage...

Also SQL scripts and sprocs are essentially database language and we should not be scared to mix languages when they are the best fit for the job at hand

Improve this answer
  • 2
    It's not true that orm's only work with simple database structures. See, for example, nHibernate's Projections API, etc. Joins, cascades, calculated values, etc. are all fully supported by modern orm's. – Clay Fowler Sep 29 '12 at 16:42
10

I'd hazard a guess that the quote is talking about the rise of ORMs for database access.

An ORM will do all the communication to the database and translate between the database and your objects.

Most ORMs will also abstract away the differences between different databases, so you should be able to use the same code.

Examples of ORMs for .NET are nHibernate and Entity Framework (though there are many).

Improve this answer
  • 1
    +1 but since you're likely to get the chosen answer, for future readers maybe add one little note about the fact that there's also stored procedures as a common way of maintaining SQL outside of C#. – Jimmy Hoffa Sep 28 '12 at 15:22
4

Important thing in .NET to mention is the LinQ - a feature in C# programming language. It is very powerful querying tool on collections that support generic IEnumerable<T> interface.

For example, LINQ to SQL (also called Linq2Sql) and LINQ to Entities are both very powerful and getting familiar with it saves tons of time.

This is really handy tool to be used, in times when you are building a quick data-driven application.

Edit: To support different databases you may need to look ORM solutions that have that capability. To my knowledge nHibernate and lately Entity Framework have this capability.

Improve this answer
  • 3
    LINQ to SQL and LINQ to Entities are both ORMS. LINQ itself (Language Integrated Query) is a different thing (Microsoft really messed up the naming on this one). – Oded Sep 28 '12 at 13:27
  • L2S is actually managing relational data as objects. you may call it a mini ORM :) – Yusubov Sep 28 '12 at 13:40
1

Is there a better way to communicate with the database and is there a better way to communicate with different database types?

To this particular question, I would say: no. Relational databases still typically "talk" SQL as their primary language. How you construct those SQL queries, however, does seem to be outdated.

To offer an alternative to the ORM answers (which are correct - ORMs are very popular, and often great for this), consider SQL Builders. Essentially, these are classes which allow you to construct SQL queries in a way which is less open to mistakes, and are more natural. Manual string concatenation is difficult to read, error prone, and often the source of security holes.

Edit: Since you specifically mentioned C#, try Massive or (the somewhat limited) CommandBuilder.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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