What is best approach to get sql data from C#

Question

I'm trying to find optimal (fast vs easiest) way to access SQL Server code thru code in C#.

As I was learning from books I've encountered multiple suggestions usually telling me to do it via drag and drop. However since I wanted to do it in code first approach was to get data by column numbers, but any reordering in SQL Query (like adding/removing columns) was pain for me to fix.

For example (don't laugh, some code is like 2 years old), I even coded special function to pass sqlQueryResult and check if it's null or not):

public static void exampleByColumnNumber(string varValue) {

        string preparedCommand = @"SELECT TOP 1 [SomeColumn],[SomeColumn2]

                                  FROM [Database].[dbo].[Table]
                  WHERE [SomeOtherColumn] = @varValue";
        SqlCommand sqlQuery = new SqlCommand(preparedCommand, Locale.sqlDataConnection);
        sqlQuery.Prepare();
        sqlQuery.Parameters.AddWithValue("@varValue) ", varValue);

        SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader();
        if (sqlQueryResult != null) {
            while (sqlQueryResult.Read()) {
                string var1 = Locale.checkForNullReturnString(sqlQueryResult, 0);
            string var2 = Locale.checkForNullReturnString(sqlQueryResult, 1);
            }
            sqlQueryResult.Close();
        }
    }

Later on I found out it's possible thru column names (which seems easier to read with multiple columns and a lot of changing order etc):

    public static void exampleByColumnNames(string varValue) {

        string preparedCommand = @"SELECT TOP 1 [SomeColumn],[SomeColumn2]

                                  FROM [Database].[dbo].[Table]
                  WHERE [SomeOtherColumn] = @varValue";
        SqlCommand sqlQuery = new SqlCommand(preparedCommand, Locale.sqlDataConnection);
        sqlQuery.Prepare();
        sqlQuery.Parameters.AddWithValue("@varValue) ", varValue);

        SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader();
        if (sqlQueryResult != null) {
            while (sqlQueryResult.Read()) {
                string var1 = (string) sqlQueryResult["SomeColumn"];
            string var2 = (string) sqlQueryResult["SomeColumn2"];
            }
            sqlQueryResult.Close();
        }
    }

And 3rd example is by doing it by column names but using .ToString() to make sure it's not null value, or by doing If/else on the null check.

    public static void exampleByColumnNamesAgain(string varValue) {

        string preparedCommand = @"SELECT TOP 1 [SomeColumn],[SomeColumn2], [SomeColumn3]

                                  FROM [Database].[dbo].[Table]
                  WHERE [SomeOtherColumn] = @varValue";
        SqlCommand sqlQuery = new SqlCommand(preparedCommand, Locale.sqlDataConnection);
        sqlQuery.Prepare();
        sqlQuery.Parameters.AddWithValue("@varValue) ", varValue);

        SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader();
        if (sqlQueryResult != null) {
            while (sqlQueryResult.Read()) {
                string var1 = (string) sqlQueryResult["SomeColumn"].ToString();
            DateTime var2;
        DateTime.TryParse(sqlQueryResult["SomeColumn2"].ToString());

        int varInt = ((int) sqlQueryResult["SomeColumn3"] == null ? 0 : (int) sqlQueryResult["SomeColumn3"];

            }
            sqlQueryResult.Close();
        }
    }

Please bare in mind that I've just created this for sake of this example and there might be some typos or some slight syntax error, but the main question is which approach is best, which is the worst (I know first one is the one that I dislike the most).

I will soon have to start / rewriting some portion of my little 90k lines app which has at least those 3 examples used widely, so i would like to get best method for speed and preferably easiest to maintain (hopefully it will be same approach).

Probably there are some better options out there so please share?

Answer 1

It seems you may be looking at old books. If you're going to do it the "old fashioned way", then you should at least use using blocks. Summary:

using (var connection = new SqlConnection(connectionString))
{
    using (var command = new SqlCommand(commandString, connection))
    {
        using (var reader = command.ExecuteReader())
        {
             // Use the reader
        }
    }
}

Better still, look into Entity Framework.

Links: Data Developer Center

Answer 2

If it's easy you're looking for, you can't do any better than Linq-to-SQL:-

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

If your SQL database already exists, you can be up-and-running in seconds.

Otherwise, I agree with John.