C# Database Access: Best method to use for getting data

Question

The reason I ask, is that origionally all references and examples pointed towards Method 2.

But Method 1 seems to do so much more in so much less code. Surely its

neater,
Better
Easier to read / follow
and has features like converting DBNulls to null. Rather than having to do it myself.
Handles opening and closing of the connection/ data readers.

I cant think of any reasons for method 2 over using method 1.

(Method 2 could probably have a using clause, but due to so much code, it was put in a seperate function to be called from else wher, and hence closing it closed the datareader making data inacccessable.)

Anyway, I am seriously thinking about going back and changing over my entire webservice to use method 1 as it seems so much better and maintainable. Can anyone suggest otherwise?

Method 1:

using (var db = Database.OpenConnectionString(Library.Properties.Settings.Default.dbConnString, "System.Data.SqlClient"))
        {
            Int32 AccNo = db.QuerySingle("SELECT AccNo FROM Tasks WHERE TaskID = " + TaskID);
        }

Method 2:

        sqlComm = new SqlCommand();
        sqlCon = new SqlConnection();
        sqlComm.Connection = sqlCon;

        sqlCon.ConnectionString = global::Library.Properties.Settings.Default.dbConnString;
        sqlComm.CommandText = "SELECT AccNo FROM Tasks WHERE TaskID = " + TaskID;
        sqlCon.Open();
        SqlDataReader data = sqlComm.ExecuteReader();
        while (data.Read())
        {
            Int32 AccNo = (Int32)data["AccNo"];
        }
        sqlCon.Close();
        sqlComm.Dispose();
        if (data != null)
           data.Close();

For both methods, I advice to use parameters with your SqlCommand. — JP Hellemons
– JP Hellemons, Commented Dec 19, 2011 at 10:56
By the way: SqlCommand also has an ExecuteScalar method, to get just a single value. — Hans Keﬆing
– Hans Keﬆing, Commented Dec 19, 2011 at 11:02

Djoul6 · Accepted Answer · 2011-12-19 10:58:11Z

9

Try using the entity framework

using (var context = new YourDatabaseEntities())
{
    var elements = (from c in context.YourTable where c.TaskId == taskId select c);
}

answered Dec 19, 2011 at 10:58

Djoul6

3091 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ramesh Soni Over a year ago

Or you can use LINQ to SQL, if you just want to run simple SQL queries and SPs

IAmGroot Over a year ago

Can you elaberate on the benefits of this over Method 1. They essentially follow the same pattern. (A using statement, followed by an Sql like statement to aquire the data)

Djoul6 Over a year ago

Yes both approach have nearly the same pattern, but with EF you don't inject your parameters directly in your query, it will also manage your DB Null values by using nullable properties, and some other advantages !

Adam · Accepted Answer · 2011-12-19 11:00:35Z

3

Method 1 looks far better in my opinion.

Method 2 does have a few things you can do to cut down on the code but you would only want Method 2 if you wanted greater control of your connection.

As Djoul6 suggested you can use Entity Framework as well. I prefer EF over everything else. Even though it takes a slight performance hit it is worth it for the amount of code you can push out in such a short space of time and the added security (SQL injection) among many other things.

answered Dec 19, 2011 at 11:00

Adam

16.2k7 gold badges68 silver badges112 bronze badges

Collectives™ on Stack Overflow

C# Database Access: Best method to use for getting data

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related