在Entity Framework中执行存储过程，返回List<DataTable>或数据集 [英] Execute stored procedure in Entity Framework, return List<DataTable> or DataSet

问题描述

如何修改以下方法以返回列表

DataTable> 还是 DataSet?我想让它通用，以便它可以从数据库中返回多个结果集.

How do I modify the below method to return a List< DataTable> or a DataSet? I want to make it generic so it can return multiple resultsets from the database.

public static DataTable ExecuteStoredProcedure(ObjectContext db, string storedProcedureName, IEnumerable<SqlParameter> parameters)
    {
        var entityConnection = (EntityConnection) db.Connection;
        var conn = entityConnection.StoreConnection;
        var initialState = conn.State;
        var dt = new DataTable();

        try
        {
            if (initialState != ConnectionState.Open)
                conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = storedProcedureName;
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (var parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }

                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                    reader.Close();
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (initialState != ConnectionState.Open)
                conn.Close();
        }
        return dt;
    }

推荐答案

我最终是这样 -

public static DataSet ExecuteStoredProcedure(ObjectContext db, string storedProcedureName, IEnumerable<SqlParameter> parameters)
    {
        var connectionString = ((EntityConnection)db.Connection).StoreConnection.ConnectionString;
        var ds = new DataSet();

        using (var conn = new SqlConnection(connectionString))
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = storedProcedureName;
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (var parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }

                using (var adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(ds);
                }
            }
        }

        return ds;
    }

这篇关于在Entity Framework中执行存储过程，返回List&lt;DataTable&gt;或数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！