存储过程的通用数据访问代码，以返回Enumerable< >

本文关键字：返回 Enumerable 代码 数据 访问 存储过程 | 更新日期: 2023-09-27 18:05:09

与其用不同的Enumerable重复相同的ADO.net代码，我想让它更通用和可重用。

我有以下ADO。Net代码返回一个对象集合:

public static IEnumerable<TasCriteria> GetTasCriterias()
    {
        using (var conn = new SqlConnection(_connectionString))
        {
            var com = new SqlCommand();
            com.Connection = conn;
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "IVOOARINVENTORY_GET_TASCRITERIA";
            var adapt = new SqlDataAdapter();
            adapt.SelectCommand = com;
            var dataset = new DataSet();
            adapt.Fill(dataset);
            var types = (from c in dataset.Tables[0].AsEnumerable()
                         select new TasCriteria()
                         {
                              TasCriteriaId = Convert.ToInt32(c["TasCriteriaId"]),
                              TasCriteriaDesc= c["CriteriaDesc"].ToString()
                         }).ToList<TasCriteria>();
            return types;
        }
    }

模型:

 public class TasCriteria
    {
        public int TasCriteriaId { get; set; }
        public string TasCriteriaDesc { get; set; }
    }

存储过程的通用数据访问代码，以返回Enumerable< >

如果存储过程返回的列名与类中的属性完全匹配，则可以使用如下代码所示的反射。我将把向存储过程添加参数的练习留给其他人。(顺便说一下，这是未经测试的代码，我是凭脑子写的)

    public static IEnumerable<T> GetStoredProcedure<T>(string procedure) where T : new()
    {
        var data = new List<T>();
        using (var conn = new SqlConnection(_connectionString))
        {
            var com = new SqlCommand();
            com.Connection = conn;
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = procedure;
            var adapt = new SqlDataAdapter();
            adapt.SelectCommand = com;
            var dataset = new DataSet();
            adapt.Fill(dataset);
            //Get each row in the datatable
            foreach (DataRow row in dataset.Tables[0].Rows)
            {
                //Create a new instance of the specified class
                var newT = new T();
                //Iterate each column
                foreach (DataColumn col in dataset.Tables[0].Columns)
                {
                    //Get the property to set
                    var property = newT.GetType().GetProperty(col.ColumnName);
                    //Set the value
                    property.SetValue(newT, row[col.ColumnName]);
                }
                //Add it to the list
                data.Add(newT);
            }
            return data;
        }
    }

假设你有一个这样的类:

public class TasCriteria
{
    public int TasCriteriaId { get; set; }
    public string TasCriteriaDesc { get; set; }
}

你可以这样调用这个函数:

IEnumerable<TasCriteria> criteria = GetStoredProcedure<TasCriteria>("IVOOARINVENTORY_GET_TASCRITERIA");

可以选择从主方法中提取特定的代码。

在例子:

public static IEnumerable<T> GetCriterias(
                            string storedProcedureName,
                            Func<IEnumerable<T>> enumerateMethod)
{
    using (var conn = new SqlConnection(_connectionString))
    {
        var com = new SqlCommand();
        com.Connection = conn;
        com.CommandType = CommandType.StoredProcedure;
        com.CommandText = storedProcedureName;
        var adapt = new SqlDataAdapter();
        adapt.SelectCommand = com;
        var dataset = new DataSet();
        adapt.Fill(dataset);
        return enumerateMethod(dataset.Tables[0]);
    }
}

如果你的存储过程需要参数，你可以重载你的函数:

public static IEnumerable<T> GetCriterias(
                        string storedProcedureName,
                        Func<IEnumerable<T>> enumerateMethod,
                        SqlParameter[] parameters)

使用此代码，您可以定义与de Func<>签名匹配的方法。

public static IEnumerable<TasCriteria> EnumerateTasCriteria(DataTable table)
{
    return 
        (from c in table.AsEnumerable()
        select new TasCriteria()
        {
            TasCriteriaId = Convert.ToInt32(c["TasCriteriaId"]),
            TasCriteriaDesc= c["CriteriaDesc"].ToString()
        }).ToList<TasCriteria>();
}
public static IEnumerable<DetailCriteria> EnumerateDetailCriteriaCriteria(
                                            DataTable table)
{
    return 
        (from c in table.AsEnumerable()
         select new DetailCriteria()
        {
        DetailCriteriaId = Convert.ToInt32(c["DetailCriteriaId"]),
        DetailCriteriaDesc = c["CriteriaDesc"].ToString()
        }).ToList<TasCriteria>();
}
然后，你可以这样调用你的代码: 
IEnumerable<TasCriteria> task = 
         GetCriterias<TasCriteria>("IVOOARINVENTORY_GET_TASCRITERIA", EnumerateTasCriteria);
IEnumerable<DetailCriteria> details =
         GetCriterias<DetailCriteria>("IVOOARINVENTORY_GET_DETAILSCRITERIA", EnumerateDetailCriteriaCriteria);
相关文章：