What are the disadvantages of using a method that invokes a delegate for each row in SqlDataReader?

Question

When I find a new idea, I always stick to it and do not see any weaknesses. Bad things happen when I start using a new idea in a large project and later discover that the idea was very bad and I should not use it in any project.

That is why, having a new idea and being ready to use it in a new large project, I need your opinion on this, especially negative .

---

For a long time, I was bored with typing again and again or copy-paste the following blocks into projects that need to be directly accessed by the database:

string connectionString = Settings.RetrieveConnectionString(Database.MainSqlDatabase);
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
    sqlConnection.Open();

    using (SqlCommand getProductQuantities = new SqlCommand("select ProductId, AvailableQuantity from Shop.Product where ShopId = @shopId", sqlConnection))
    {
        getProductQuantities.Parameters.AddWithValue("@shopId", this.Shop.Id);
        using (SqlDataReader dataReader = getProductQuantities.ExecuteReader())
        {
            while (dataReader.Read())
            {
                yield return new Tuple<int, int>((int)dataReader["ProductId"], Convert.ToInt32(dataReader["AvailableQuantity"]));
            }
        }
    }
}

, , - , , :

IEnumerable<Tuple<int, int>> quantities = DataAccess<Tuple<int, int>>.ReadManyRows(
    "select ProductId, AvailableQuantity from Shop.Product where ShopId = @shopId",
    new Dictionary<string, object> { { "@shopId", this.Shop.Id } },
    new DataAccess<string>.Yield(
        dataReader =>
        {
            return new Tuple<int, int>(
                (int)dataReader["ProductId"],
                Convert.ToInt32(dataReader["AvailableQuantity"]);
        }));

:

• ,

• ( , , ),

• (, while ..),

• Intellisense,

• , .

:

IEnumerable<string> productNames = DataAccess<string>.ReadManyRows(
    "select distinct ProductName from Shop.Product",
    new DataAccess<string>.Yield(dataReader => { return (string)dataReader["ProductName"]; }));

ExecuteNonQuery, ExecuteScalar ReadManyRows DataAccess<T>.ReadManyRows , .

:

• . , , SqlCommand. , SqlCommand .

• SqlCommand s. , , DataAccess , , SqlCommand ExecuteReader(CommandBehavior.SingleRow).

• ( ).

, DataAccess<T>.ReadManyRows?

Answer 1

, , , , , . , API.

, , - , , , . , , . , .

( , ):

, , , , new Dictionary<string, object>.

Tuple.Create

, .

DataReader

- , DataReader ?

.
Kudos to .

var tuples = new DataAccess ("select ProductId, AvailableQuantity from Shop.Product where ShopId = @shopId")
    .With (new { shopId = this.Shop.Id }) // map parameters to values
    .ReadMany (row =>
         Tuple.Create (row.Value<int> ("ProductId"), row.Value<int> ("AvailableQuantity"))); 

var strings = new DataAccess ("select distinct ProductName from Shop.Product")
    .ReadMany (row => row.Value<string> ("ProductName")); 

, :

var productName = new DataAccess ("select ProductName from Shop.Product where ProductId = @productId")
    .With (new { productId = this.SelectedProductId }) // whatever
    .ReadOne (row => row.Value<string> ("ProductName")); 

Row:

class Row {
    DataReader reader;

    public Row (DataReader reader)
    {
        this.reader = reader;
    }

    public T Value<T> (string column)
    {
        return (T) Convert.ChangeType (reader [column], typeof (T));
    }
}

ReadOne ReadMany ( ) DataReader selector lambdas.

Answer 2

: SQL , ( LINQ, , , , DBML EDMX ). SQL , ( - ) SQL , . SQL , - ; , , , , , - .

Answer 3

. , , , . , . .

Spring Framework Java , JdbcTemplate HibernateTemplate, . , .

Answer 4

, .

, , , - , SqlConnection , .

IEnumerable<T> , , . , , .

ToList(), . , .

, , - .

Answer 5

/ - , .

, . , :

new DataAccess<string>.Yield(
    dataReader =>
    {
        return new Tuple<int, int>(
            (int)dataReader["ProductId"],
            Convert.ToInt32(dataReader["AvailableQuantity"]);
    }));

, / ( try catch?), .