C# EF CodeFirst的仓储

参考：https://www.cnblogs.com/hblc/p/12331424.html

仓储(Respository)是存在于工作单元和数据库之间单独分离出来的一层，是对数据访问的封装。其优点：

　　　　1）业务层不需要知道它的具体实现，达到了分离关注点。

　　　　2）提高了对数据库访问的维护，对于仓储的改变并不会改变业务的逻辑，数据库可以用Sql Server，MySql等。

第一步，先创建数据库模型。

 public class User
    {
        //主键
        [Key]
        //自增
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        //列索引
        [Column(Order = 1)]
        //不为空
        [Required]
        //数值长度
        [StringLength(50)]
        public string 名称 { get; set; }
    }

 

 

 第二步：添加EF框架

 

 

public class CreateDataBase : DbContext
    {
        //您的上下文已配置为从您的应用程序的配置文件(App.config 或 Web.config)
        //使用“CreateDataBase”连接字符串。默认情况下，此连接字符串针对您的 LocalDb 实例上的
        //“EditForm.Model.CreateDataBase”数据库。
        // 
        //如果您想要针对其他数据库和/或数据库提供程序，请在应用程序配置文件中修改“CreateDataBase”
        //连接字符串。
        //使用自定义连接串
        public static string GetConstr()
        {
            string connString = ConfigurationManager.ConnectionStrings["CreateDataBase"].ToString();
            return connString;
        }
        public CreateDataBase()
            : base(GetConstr())
        {
            //模型更改时重新创建数据库
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<CreateDataBase, ReportingDbMigrationsConfiguration>());
            this.Configuration.AutoDetectChangesEnabled = false;
            this.Configuration.ValidateOnSaveEnabled = false;
            this.Configuration.LazyLoadingEnabled = false;
            this.Configuration.ProxyCreationEnabled = false;
        }
        internal sealed class ReportingDbMigrationsConfiguration : DbMigrationsConfiguration<CreateDataBase>
        {
            public ReportingDbMigrationsConfiguration()
            {
                AutomaticMigrationsEnabled = true;//任何Model Class的修改將会直接更新DB
                AutomaticMigrationDataLossAllowed = true;
            }
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            //modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();//这句是不要将EF生成的sql表名不要被复数 就是表名后面不要多加个S
        }
        public virtual DbSet<Model> Model { get; set; } 

}

  第三步创建仓储接口：

  public interface IRepositoryBase : IDisposable
    {
        IRepositoryBase BeginTrans();
        int Commit();
        int Insert<TEntity>(TEntity entity) where TEntity : class;
        int Insert<TEntity>(List<TEntity> entitys) where TEntity : class;
        int Update<TEntity>(TEntity entity) where TEntity : class;
        int Delete<TEntity>(TEntity entity) where TEntity : class;
        int Delete<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class;
        TEntity FindEntity<TEntity>(object keyValue) where TEntity : class;
        TEntity FindEntity<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class;
        IQueryable<TEntity> IQueryable<TEntity>() where TEntity : class;
        IQueryable<TEntity> IQueryable<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class;
        List<TEntity> FindList<TEntity>(string strSql) where TEntity : class;
        List<TEntity> FindList<TEntity>(string strSql, DbParameter[] dbParameter) where TEntity : class;
        List<TEntity> FindList<TEntity>(Pagination pagination) where TEntity : class, new();
        List<TEntity> FindList<TEntity>(Expression<Func<TEntity, bool>> predicate, Pagination pagination) where TEntity : class, new();
        DataTable FindDataTable(string strSql, SqlParameter[] dbParameters);
        DataTable FindDataTable(string strSql);
        DataSet FindDataSet(string strSql, SqlParameter[] dbParameters);
        DataSet FindDataSet(string strSql);
        bool CheckDatabaseExists();
        int DataBaseBackUp(string FilePath, string DataBaseName);
        int DataBaseRestor(string path, string DataBaseName);

    }

第四步：仓储接口实现

  public class RepositoryBase : IRepositoryBase, IDisposable
    {
        Contxt dbcontext = new Contxt();
        private DbTransaction dbTransaction { get; set; }
        public IRepositoryBase BeginTrans()
        {
            DbConnection dbConnection = ((IObjectContextAdapter)dbcontext).ObjectContext.Connection;
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }
            dbTransaction = dbConnection.BeginTransaction();
            return this;
        }
        public int Commit()
        {
            try
            {
                var returnValue = dbcontext.SaveChanges();
                if (dbTransaction != null)
                {
                    dbTransaction.Commit();
                }
                return returnValue;
            }
            catch (Exception)
            {
                if (dbTransaction != null)
                {
                    this.dbTransaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.Dispose();
            }
        }
        public void Dispose()
        {
            if (dbTransaction != null)
            {
                this.dbTransaction.Dispose();
            }
            this.dbcontext.Dispose();
        }
        public int Insert<TEntity>(TEntity entity) where TEntity : class
        {
            dbcontext.Entry<TEntity>(entity).State = EntityState.Added;
            return dbTransaction == null ? this.Commit() : 0;
        }
        public int Insert<TEntity>(List<TEntity> entitys) where TEntity : class
        {
            foreach (var entity in entitys)
            {
                dbcontext.Entry<TEntity>(entity).State = EntityState.Added;
            }
            return dbTransaction == null ? this.Commit() : 0;
        }
        public int Update<TEntity>(TEntity entity) where TEntity : class
        {
            dbcontext.Set<TEntity>().Attach(entity);
            PropertyInfo[] props = entity.GetType().GetProperties();
            foreach (PropertyInfo prop in props)
            {
                if (prop.GetValue(entity, null) != null)
                {
                    if (prop.GetValue(entity, null).ToString() == "&nbsp;")
                        dbcontext.Entry(entity).Property(prop.Name).CurrentValue = null;
                    dbcontext.Entry(entity).Property(prop.Name).IsModified = true;
                }
            }
            return dbTransaction == null ? this.Commit() : 0;
        }
        public int Delete<TEntity>(TEntity entity) where TEntity : class
        {
            //dbcontext.Set<TEntity>().AsNoTracking();
            dbcontext.Set<TEntity>().Attach(entity);
            dbcontext.Entry<TEntity>(entity).State = EntityState.Deleted;
            return dbTransaction == null ? this.Commit() : 0;
        }
        public int Delete<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class
        {
            var entitys = dbcontext.Set<TEntity>().Where(predicate).ToList();
            entitys.ForEach(m => dbcontext.Entry<TEntity>(m).State = EntityState.Deleted);
            return dbTransaction == null ? this.Commit() : 0;
        }
        public TEntity FindEntity<TEntity>(object keyValue) where TEntity : class
        {
            return dbcontext.Set<TEntity>().Find(keyValue);
        }
        public TEntity FindEntity<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class
        {
            return dbcontext.Set<TEntity>().FirstOrDefault(predicate);
        }
        public IQueryable<TEntity> IQueryable<TEntity>() where TEntity : class
        {
            return dbcontext.Set<TEntity>();
        }
        public IQueryable<TEntity> IQueryable<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class
        {
            return dbcontext.Set<TEntity>().Where(predicate);
        }
        public List<TEntity> FindList<TEntity>(string strSql) where TEntity : class
        {
            return dbcontext.Database.SqlQuery<TEntity>(strSql).ToList<TEntity>();
        }
        public List<TEntity> FindList<TEntity>(string strSql, DbParameter[] dbParameter) where TEntity : class
        {
            return dbcontext.Database.SqlQuery<TEntity>(strSql, dbParameter).ToList<TEntity>();
        }
        public List<TEntity> FindList<TEntity>(Pagination pagination) where TEntity : class, new()
        {
            bool isAsc = pagination.sord.ToLower() == "asc" ? true : false;
            string[] _order = pagination.sidx.Split(',');
            MethodCallExpression resultExp = null;
            var tempData = dbcontext.Set<TEntity>().AsQueryable();
            foreach (string item in _order)
            {
                string _orderPart = item;
                _orderPart = Regex.Replace(_orderPart, @"\s+", " ");
                string[] _orderArry = _orderPart.Split(' ');
                string _orderField = _orderArry[0];
                bool sort = isAsc;
                if (_orderArry.Length == 2)
                {
                    isAsc = _orderArry[1].ToUpper() == "ASC" ? true : false;
                }
                var parameter = Expression.Parameter(typeof(TEntity), "t");
                var property = typeof(TEntity).GetProperty(_orderField);
                var propertyAccess = Expression.MakeMemberAccess(parameter, property);
                var orderByExp = Expression.Lambda(propertyAccess, parameter);
                resultExp = Expression.Call(typeof(Queryable), isAsc ? "OrderBy" : "OrderByDescending", new Type[] { typeof(TEntity), property.PropertyType }, tempData.Expression, Expression.Quote(orderByExp));
            }
            tempData = tempData.Provider.CreateQuery<TEntity>(resultExp);
            pagination.records = tempData.Count();
            tempData = tempData.Skip<TEntity>(pagination.rows * (pagination.page - 1)).Take<TEntity>(pagination.rows).AsQueryable();
            return tempData.ToList();
        }
        public List<TEntity> FindList<TEntity>(Expression<Func<TEntity, bool>> predicate, Pagination pagination) where TEntity : class, new()
        {
            bool isAsc = pagination.sord.ToLower() == "asc" ? true : false;
            string[] _order = pagination.sidx.Split(',');
            MethodCallExpression resultExp = null;
            var tempData = dbcontext.Set<TEntity>().Where(predicate);
            foreach (string item in _order)
            {
                string _orderPart = item;
                _orderPart = Regex.Replace(_orderPart, @"\s+", " ");
                string[] _orderArry = _orderPart.Split(' ');
                string _orderField = _orderArry[0];
                bool sort = isAsc;
                if (_orderArry.Length == 2)
                {
                    isAsc = _orderArry[1].ToUpper() == "ASC" ? true : false;
                }
                var parameter = Expression.Parameter(typeof(TEntity), "t");
                var property = typeof(TEntity).GetProperty(_orderField);
                var propertyAccess = Expression.MakeMemberAccess(parameter, property);
                var orderByExp = Expression.Lambda(propertyAccess, parameter);
                resultExp = Expression.Call(typeof(Queryable), isAsc ? "OrderBy" : "OrderByDescending", new Type[] { typeof(TEntity), property.PropertyType }, tempData.Expression, Expression.Quote(orderByExp));
            }
            tempData = tempData.Provider.CreateQuery<TEntity>(resultExp);
            pagination.records = tempData.Count();
            tempData = tempData.Skip<TEntity>(pagination.rows * (pagination.page - 1)).Take<TEntity>(pagination.rows).AsQueryable();
            return tempData.ToList();
        }
        public DataTable FindDataTable(string strSql, SqlParameter[] dbParameters)
        {
            return dbcontext.Database.ExecuteDataTable(strSql, dbParameters);
        }
        public DataTable FindDataTable(string strSql)
        {
            return dbcontext.Database.ExecuteDataTable(strSql);
        }
        public DataSet FindDataSet(string strSql, SqlParameter[] dbParameters)
        {
            return dbcontext.Database.ExecuteDataSet(strSql, dbParameters);
        }
        public DataSet FindDataSet(string strSql)
        {
            return dbcontext.Database.ExecuteDataSet(strSql);
        }

        public bool CheckDatabaseExists()
        {

            string sqlCreateDBQuery;
            bool result = false;
            try
            {
                string databaseName = Regex.Match( dbcontext.connStringAll, @"Database=([^;]+)").Groups[1].Value;
                var databaseSource = dbcontext.connStringAll.Replace(databaseName, "master");
                SqlConnection tmpConn = new SqlConnection(databaseSource);

                sqlCreateDBQuery = string.Format("SELECT database_id from sys.databases WHERE Name  = '{0}'", databaseName);
                using (tmpConn)
                {
                    using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
                    {
                        tmpConn.Open();
                        object resultObj = sqlCmd.ExecuteScalar();
                        int databaseID = 0;
                        if (resultObj != null)
                        {
                            int.TryParse(resultObj.ToString(), out databaseID);
                        }
                        tmpConn.Close();
                        result = (databaseID > 0);
                    }
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;

        }

        /// <summary>
        /// 数据库备份
        /// </summary>
        /// <param name="FilePath"></param>
        /// <param name="DataBaseName"></param>
        /// <returns></returns>
        public int DataBaseBackUp(string FilePath, string DataBaseName)
        {
            string databaseName = Regex.Match(dbcontext.connStringAll, @"Database=([^;]+)").Groups[1].Value;
            var databaseSource = dbcontext.connStringAll.Replace(databaseName, "master");
            using (SqlConnection conn = new SqlConnection(databaseSource))
            {
                StringBuilder builder = new StringBuilder();
                builder.Append(" backup database " + DataBaseName + " to disk='" + FilePath + "' with init");

                SqlCommand cmdBK = new SqlCommand();
                cmdBK.CommandType = CommandType.Text;
                cmdBK.Connection = conn;
                cmdBK.CommandText = builder.ToString();
                try
                {
                    conn.Open();
                    int Count = cmdBK.ExecuteNonQuery();
                    return Count;
                }
                catch (Exception ex)
                {
                    return 0;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

 


        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="dataBaseName">数据库名称</param>
        /// <param name="path">还原的路径bak文件</param>
        /// <param name="UserId">用户名</param>
        /// <param name="PassWord">密码</param>
        /// <param name="DataSource">IP或者当前服务（.）</param>
        public int   DataBaseRestor(string path, string dataBaseName)
        {
            try
            {
                string databaseName = Regex.Match(dbcontext.connStringAll, @"Database=([^;]+)").Groups[1].Value;
                var strconn = dbcontext.connStringAll.Replace(databaseName, "master");
                DataTable DBNameTable = new DataTable();
                SqlDataAdapter Adapter = new SqlDataAdapter("select name from master..sysdatabases", strconn);
                lock (Adapter)
                {
                    Adapter.Fill(DBNameTable);
                }
                foreach (DataRow row in DBNameTable.Rows)
                {
                    if (row["name"].ToString() == dataBaseName)
                    {
                        return 0;
                    }
                }
                //检测真正当前bak文件真正的log mdf的名字
                var strsql = " restore  filelistonly from disk = '" + path + "'";
                SqlDataAdapter Adapter2 = new SqlDataAdapter(strsql, strconn);
                var dt = new DataTable();
                lock (Adapter2)
                {
                    Adapter2.Fill(dt);
                }
                var mdf = dt.Rows[0][0].ToString();
                var log = dt.Rows[1][0].ToString();
                string restore = string.Format(@"restore database {0} from disk = '{1}'
                                                with REPLACE
                                                , move '{2}' to 'D:\{3}.mdf'
                                                ,move '{4}' to 'D:\{5}.ldf'", dataBaseName, path, mdf, dataBaseName, log, dataBaseName);
                SqlConnection conn = new SqlConnection(strconn);
                SqlCommand cmd1 = new SqlCommand(restore, conn);
                conn.Open();//k
                var res = cmd1.ExecuteNonQuery();
                conn.Close();//g
                return res;
            }
            catch (Exception ex)
            {

                return 0;
            }

        }

    }