EF执行T_sql语句存储过程helper封装及测试调用源码

刚开始接触EF也不知这么写会有什么问题，望大神指导、调教。

1.创建DbContextFatcay创建DbContext保证线程内唯一（这代码网上一堆，我也是从网上找的）

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Runtime.Remoting.Messaging;
using System.Text;

namespace EF_Model.DBSql
{
    public static class DBFactory
    {
        public static DbContext CreateDbContext { get { return GetcurrentDbContect(); } }
        private static DbContext GetcurrentDbContect()
        {
            DbContext db = (DbContext)CallContext.GetData("DbContext");
            if (db == null)
            {
                db = new HoganDBEntities();
                CallContext.SetData("DbContext", db);
            }
            return db;
        }
    }
}

2.创建t_sqlHelper【执行t_sql语句】我抽出了三个方法代码如下：

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Data;

namespace EF_Model.DBSql
{
    public class DBSQlHelper
    {
        HoganDBEntities db = (HoganDBEntities)DBFactory.CreateDbContext;

        /// <summary>
        /// 只可查询Entity类型的实体EF必须有此类型实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public IList<T> GetEntity<T>(string sql, params object[] pms) where T : class,new()
        {
            return db.Set<T>().SqlQuery(sql, pms).ToList();

        }

        /// <summary>
        /// 执行非EntityFormark实体类型数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public IList<T> GetNoEntity<T>(string sql, params object[] pms) where T : class,new()
        {
            return db.Database.SqlQuery<T>(sql, pms).ToList();
        }

        /// <summary>
        /// 执行新增、修改、删除时使用返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public int SqlCommand(string sql, params object[] pms)
        {
            return db.Database.ExecuteSqlCommand(sql, pms);
        }
    }
}

3.开始测试使用了

  3.1执行普通查询语句

DBSQlHelper helper = new DBSQlHelper();
        //
        // GET: /Home/

        public ActionResult Index()
        {
            #region 调用t_sql语句
            var t_sql = "select top 14 * from usergroup";
            IList<UserGroup> t_sqllist = helper.GetEntity<UserGroup>(t_sql);

            ////string s = "";
            ////foreach (var item in t_sqllist)
            ////{
            ////    s += item.ugCreateUnickName + ",";
            ////}
            #endregion
}

  3.2调用带参数的sql语句[表连接查询] 

     由于连接实体不需要两张表的所有字段，而EF实体类(数据库表字段不够)我创建了一个新的实体，为我少写字段我直接继承EF的实体代码如下:

 /// <summary>
    /// 与用户组表关联后查询结果实体
    /// </summary>
    public class UsersView : Users
    {
        public string ugCreateUnickName { get; set; }
        public string ugName { get; set; }
        public string ugInfo { get; set; }
    }

var t_Sql = @"
        select u.*,ug.ugCreateUnickName,ug.ugName,ug.ugInfo from UserGroup as ug
            right join users as u
            on ug.ugId=u.ugId
            where userid>@userid                
            order by userid desc
";
            //此方法 只能调用EF中有的类型[数据库中有实体表]
            //IList<UsersView> uvList= helper.GetEntity<UsersView>(t_Sql, new SqlParameter("@userid", 1));

            // IEnumerable<UsersView> uvList = helper.GetNoEntity<UsersView>(t_Sql, new SqlParameter("@userid", 1));

            SqlParameter p1 = new SqlParameter("@userid", 1);
            IEnumerable<UsersView> uvList = helper.GetNoEntity<UsersView>(t_Sql, p1);
            int count = uvList.Count();

  3.3调用存储过程【先来个错误的例子】

#region 错误的写法 调用存储过程

            //@prows_ int,  
            //@pages_ int,
            //@wheres_ varchar(1000),
            //@orders_ varchar(200)
            var query = "exec GetUserGroupList @prows_,@pages_,@wheres_,@orders_";
            IList<UserGroup> list = helper.GetEntity<UserGroup>(query, 50, 1, " ugid>3840517 ", " ugid ASC ");
            #endregion

  以下我是写了个分页的存储过程及调用代码

#region Ok调用存储过程
            var query = "exec GetUserGroupList @prows_,@pages_,@wheres_,@orders_";
            SqlParameter[] pms = new SqlParameter[] {
                new SqlParameter("@prows_",System.Data.SqlDbType.Int){ Value=50},
                new SqlParameter("@pages_",1),
                new SqlParameter("@wheres_"," ugid>3840517 "),
                new SqlParameter("@orders_"," ugid ASC ")
                };
            IList<UserGroup> list = helper.GetEntity<UserGroup>(query, pms);
#endregion

 

4.附问题一个【t_sqlHelper】问题代码如下：

/// <summary>
        /// 执行非EntityFormark实体类型数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public IEnumerable<T> GetNoEntity<T>(string sql, params object[] pms) where T : class,new()
        {
            return db.Database.SqlQuery<T>(sql, pms);
        }

原因是SqlParameter原有的参数没有Clear()，我也不知道在那里清空参数。就只有在t_sqlHelper中直接ToList()没有返回IEnumerable<T>了。

折腾了一晚上，如果转载请标明出处！谢谢，码了一晚上也不容易。