CODEWITHSUNDEEP
c#databaseado.net
Marcus
Marcus

Reputation: 9439

How to abstract the database layer?

Every time I get something from the db, my code is this:

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.v3ConnString))
            {
                conn.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "SELECT ...";
                    command.Parameters.AddWithValue("...", ...);
                    using (SqlDataReader dr = command.ExecuteReader())
                    {
                        if (dr.HasRows)
                            someVar = true;
                  }
                }
            }

Instead I want to do myArray = Db.sql("SELECT ...") or something else if there is a better way. Can somebody point me in the right direction?

EDIT: I'm not looking for code to generate SQL for me, rather an easy way of getting an array result from an SQL query.

Upvotes: 2

Views: 1035

Answers (4)

Disposer
Disposer

Reputation: 769

NHibernate is one of the most powerful ORMs frameworks. Using NHibernate you still can write SQL queries, however NHibernate will take the job of data transformation from Sql types to .net types.

Frankly speaking in your situation there is no difference to use ORM or not. If you repeat your code from time to time it means that your application has very bad design and you need to improve it. Firstly, you should divide yours application on several layers(I'm speaking only about database access).

  1. Layer which provides with ability to execute custom sql queries, you will use this code from another layers.
  2. Layer which provides you with beautiful interface and encapsulate all database logic, including queries and objects transformation.

and so on. you can add settings layer which will read connection string from settings, but it is all optional.

Upvotes: 0

Serban Murariu
Serban Murariu

Reputation: 127

That's "the old way of doing it". If you're working with a newer version of the framework (>2.0) then please use linq to sql. You have a very good tutorial here: http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

  1. Entity Framework
  2. NHibernate
  3. Linq 2 SQL (distant third, because it is in maintenance mode).

Upvotes: 1

Darin Dimitrov
Darin Dimitrov

Reputation: 1038720

Checkout Dapper.NET. It's a lightweight ORM which will simplify this task.

Upvotes: 2

Related Questions