什么是执行SQL命令最DRY-适当的方式？ [英] What's the most DRY-appropriate way to execute an SQL command?

问题描述

我在找找出执行使用最少量的样板code数据库查询的最佳方式。该方法建议在的SqlCommand文档：

I'm looking to figure out the best way to execute a database query using the least amount of boilerplate code. The method suggested in the SqlCommand documentation:

private static void ReadOrderData(string connectionString)
{
    string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",     reader[0], reader[1]));
            }
        }
        finally
        {
            reader.Close();
        }
    }
}

主要由code，将有被用在了所有与数据库交互的方式。

mostly consists of code that would have to be repeated in every method that interacts with the database.

我已经分解出建立一个连接，这将产生code更喜欢下面的习惯。 （我也修改它，以便它返回的数据，为了使这个例子少一点微不足道的。）

I'm already in the habit of factoring out the establishment of a connection, which would yield code more like the following. (I'm also modifying it so that it returns data, in order to make the example a bit less trivial.)

private SQLConnection CreateConnection()
{
    var connection = new SqlConnection(_connectionString);
    connection.Open();
    return connection;
}

private List<int> ReadOrderData()
{
    using(var connection = CreateConnection())
    using(var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT OrderID FROM dbo.Orders;";

        using(var reader = command.ExecuteReader())
        {
            var results = new List<int>();
            while(reader.Read()) results.Add(reader.GetInt32(0));
            return results;
        }
    }
}

这是一个进步，但仍然有足够的样板唠叨我。这可以进一步减少？我特别想做点什么的前两行的过程。我不喜欢的方法应该是负责创建的SqlCommand 的。这是一小片的重复，因为它是在这个例子中，但似乎如果交易被手工管理或超时被改变或任何类似的增长。

That's an improvement, but there's still enough boilerplate to nag at me. Can this be reduced further? In particular, I'd like to do something about the first two lines of the procedure. I don't feel like the method should be in charge of creating the SqlCommand. It's a tiny piece of repetition as it is in the example, but it seems to grow if transactions are being managed manually or timeouts are being altered or anything like that.

修改的：假设，至少假设，有将必须返回一堆不同类型的数据。因此该解决方案不能只是一个一个尺寸适合所有人的方法，有将不得不一些不同的根据，至少，是否的ExecuteNonQuery ， 的ExecuteScalar ，的ExecuteReader ， ExecuteReaderAsync ，或任何其他人正在调用。我想减少在那些重复。

edit: Assume, at least hypothetically, there's going to have to be a bunch of different types of data being returned. And consequently the solution can't be just one one-size-fits-all method, there will have to be a few different ones depending, at minimum, on whether ExecuteNonQuery, ExecuteScalar, ExecuteReader, ExecuteReaderAsync, or any of the others are being called. I'd like to cut down on the repetition among those.

推荐答案

试过的小巧玲珑？

当然，这并不让你一个DataReader，但你可能只是preFER是这样，一旦你已经试过了。

Granted this doesn't get you a DataReader but you might just prefer it this way once you've tried it.

这是关于最轻重量的ORM可同时仍然被称为是一个ORM。没有更多的方法来DataReader的强类型之间的映射我。

It's about the lightest-weight an ORM can be while still being called an ORM. No more methods to map between DataReader and strong types for me.

用于这里的所有StackExchange网站。

Used right here on all the StackExchange sites.

using (var conn = new SqlConnection(cs))
{
    var dogs = connection.Query("select name, age from dogs");

    foreach (dynamic dog in dogs)
    {
        Console.WriteLine("{0} age {1}", dog.name, dog.age);
    }
}

或

using (var conn = new SqlConnection(cs))
{
    var dogs = connection.Query<Dog>("select Name, Age from dogs");

    foreach (Dog dog in dogs)
    {
        Console.WriteLine("{0} age {1}", dog.Name, dog.Age);
    }
}

class Dog
{
    public string Name { get; set; }
    public int Age { get; set; }
}

这篇关于什么是执行SQL命令最DRY-适当的方式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！