asp.net mvc EntityFramework 操作数据库增删改查，带存储过程

引用

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

 

ADD添加数据

    #region ==============ADD添加数据===============

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <returns></returns>
        [Route("EFProduct/add/")]
        public string Add()
        {
            string status = string.Empty;

            //初始化（获取）数据
            Product product = new Product()
            {
                ProductName = "测试数据",
                Price = 1300,
                TypeId = 1
            };

            using (TAOBAODBEntities taobaoEntity = new TAOBAODBEntities())
            {
                //标记数据
                taobaoEntity.Product.Add(product);
                //执行数据事务 （类似执行操作）
                if (taobaoEntity.SaveChanges() > 0)
                {
                    status = "添加数据成功";
                }
                else
                {
                    status = "添加数据失败";
                }
            }
            return status;
        }
        #endregion

delete删除数据

#region ==============delete删除数据===============

        /// <summary>
        /// delete删除数据
        /// </summary>
        /// <returns></returns>
        [Route("EFProduct/Delete/{productID:int}")]
        public string Delete(int productID)
        {
            string status = string.Empty;

            using (TAOBAODBEntities taobaoEntity = new TAOBAODBEntities())
            {
                Product product = null;
                try
                {
                    //查询数据
                    product = taobaoEntity.Product.Single(m => m.ProductNo == productID);
                    //标记数据为删除
                    taobaoEntity.Product.Remove(product);
                    //执行数据事务 （类似执行操作）
                    if (taobaoEntity.SaveChanges() > 0)
                    {
                        status = "删除数据成功";
                    }
                    else
                    {
                        status = "删除数据失败";
                    }
                }
                catch (Exception)
                {
                    status = "数据不存在,无法删除";
                }
                return status;
            }

        }
        #endregion

Modify修改[单条]数据

 #region ==============Modify修改[单条]数据===============

        /// <summary>
        /// Modify修改[单条]数据
        /// </summary>
        /// <returns></returns>
        [Route("EFProduct/Modify/")]
        //[HttpPost] 
        public string Modify()
        {

            string status = string.Empty;

            //数据条件
            int productID = 32;

            using (TAOBAODBEntities taobaoEntity = new TAOBAODBEntities())
            {
                try
                {
                    //读取数据 
                    //Product product2 = taobaoEntity.Product.Single(m => m.ProductNo == productID);
                    Product product = taobaoEntity.Product.FirstOrDefault(m => m.ProductNo == productID);

                    //修改数据
                    product.ProductName = "测试修改数据Modify";

                    //执行数据事务 （类似执行操作）

                    if (taobaoEntity.SaveChanges() > 0)
                    {
                        status = "修改数据成功";
                    }
                    else
                    {
                        status = "修改数据失败;原因：源数据修改数据一致";
                    }

                }
                catch (Exception ex)
                {
                    status = "数据错误，修改失败;原因：" + ex.Message;
                }

            }
            return status;
        }
        #endregion

Modify修改[多条]数据

#region ==============Modify修改[多条]数据===============

        /// <summary>
        /// Modify修改[多条]数据
        /// </summary>
        /// <returns></returns>
        [Route("EFProduct/ModifyALL/")]
        //[HttpPost] 
        public string ModifyALL()
        {

            string status = string.Empty;


            //数据条件
            int typeid = 1;

            using (TAOBAODBEntities taobaoEntity = new TAOBAODBEntities())
            {
                try
                {
                    //读取数据 

                    List<Product> productList = taobaoEntity.Product.Where(m => m.TypeId == typeid).ToList<Product>();

                    //修改数据
                    int i = 0;
                    foreach (var item in productList)
                    {
                        item.Price = i * 1000;

                        i++;
                    }

                    //执行数据事务 （类似执行操作）
                    if (taobaoEntity.SaveChanges() > 0)
                    {
                        status = "修改数据成功";
                    }
                    else
                    {
                        status = "修改数据失败;原因：源数据修改数据一致";
                    }

                }
                catch (Exception ex)
                {
                    status = "数据错误，修改失败;原因：" + ex.Message;
                }

            }
            return status;
        }
        #endregion

GetProduct查询单表数据[升序]

#region ==============GetProduct查询单表数据[升序]===============

        /// <summary>
        /// GetProduct查询单表数据[升序]
        /// </summary>
        /// <returns></returns>
        [Route("EFProduct/GetProductOrderBy/")]
        //[HttpPost] 
        public string GetProductOrderBy()
        {

            string status = string.Empty;


            //数据条件
            int typeid = 1;

            using (TAOBAODBEntities taobaoEntity = new TAOBAODBEntities())
            {
                try
                {
                    //读取数据 

                    List<Product> productList = taobaoEntity.Product.Where(p => p.TypeId == typeid).OrderBy(p => p.Price).ToList<Product>();

                    //逐条渲染数据
                    int i = 0;
                    foreach (var item in productList)
                    {
                        status += $"{item.ProductName}--{item.Price}<br/>";
                    }
                }
                catch (Exception ex)
                {
                    status = "数据错误;原因：" + ex.Message;
                }

            }
            return status;
        }
        #endregion

GetProduct查询单表数据[降序]

#region ==============GetProduct查询单表数据[降序]===============

        /// <summary>
        /// GetProduct查询单表数据[降序]
        /// </summary>
        /// <returns></returns>
        [Route("EFProduct/GetProductOrderByDescending/")]
        //[HttpPost] 
        public string GetProductOrderByDescending()
        {

            string status = string.Empty;


            //数据条件
            int typeid = 1;

            using (TAOBAODBEntities taobaoEntity = new TAOBAODBEntities())
            {
                try
                {
                    //读取数据 

                    List<Product> productList = taobaoEntity.Product.Where(p => p.TypeId == typeid).OrderByDescending(p => p.Price).ToList<Product>();

                    //逐条渲染数据
                    int i = 0;
                    foreach (var item in productList)
                    {
                        status += $"{item.ProductName}--{item.Price}<br/>";
                    }
                }
                catch (Exception ex)
                {
                    status = "数据错误;原因：" + ex.Message;
                }

            }
            return status;
        }
        #endregion

Get多表联查数据

#region ==============Get多表联查数据===============

        /// <summary>
        /// Get多表联查数据
        /// </summary>
        /// <returns></returns>
        [Route("EFProduct/GetProductJoin/")]
        //[HttpPost] 
        public string GetProductJoin()
        {
            string status = string.Empty;

            using (TAOBAODBEntities taobaoEntity = new TAOBAODBEntities())
            {
                try
                {
                    //读取数据 

                    var productList = from a in taobaoEntity.Product
                                      join b in taobaoEntity.ProductType
                                      on a.TypeId equals b.typeid
                                      select new { a.ProductName, b.typename };

                    //逐条渲染数据
                    int i = 0;
                    foreach (var item in productList)
                    {
                        status += $"{item.ProductName}--{item.typename}<br/>";
                    }

                }
                catch (Exception ex)
                {
                    status = "数据错误;原因：" + ex.Message;
                }

            }
            return status;
        }
        #endregion

EF用原生SQL语句增、删、改 通用方法

#region ==============EF用原生SQL语句增、删、改 通用方法===============

        /// <summary>
        /// EF用原生SQL语句增、删、改 通用方法
        /// </summary>
        /// <returns></returns>
        [Route("EFProduct/ExecuteNonQuery/")]
        //[HttpPost] 
        public string ExecuteNonQuery()
        {
            string status = string.Empty;

            using (TAOBAODBEntities taobaoEntity = new TAOBAODBEntities())
            {
                try
                {
                    SqlParameter[] paras ={
                        new SqlParameter("@name","测试")
                        };

                    //可以写增、删、改的SQL 语句
                    int result = taobaoEntity.Database.ExecuteSqlCommand(@"insert into ProductType(typename) values(@name)", paras);

                    if (result > 0)
                    {
                        status = "成功";
                    }

                }
                catch (Exception ex)
                {
                    status = "数据错误，;原因：" + ex.Message;
                }

            }
            return status;
        }
        #endregion

EF用原生SQL语句查 多条数据

#region ==============EF用原生SQL语句查 多条数据===============

        /// <summary>
        /// EF用原生SQL语句查 多条数据
        /// </summary>
        /// <returns></returns>
        [Route("EFProduct/ExecuteReader/")]
        //[HttpPost] 
        public string ExecuteReader()
        {
            string status = string.Empty;

            using (TAOBAODBEntities taobaoEntity = new TAOBAODBEntities())
            {
                try
                {
                    List<Product> productList = taobaoEntity.Database.SqlQuery<Product>("select * from Product").ToList();//所有查询、存储过程调用

                    foreach (var item in productList)
                    {
                        status = status + item.ProductName + "<br/>";
                    }

                }
                catch (Exception ex)
                {
                    status = "数据错误，;原因：" + ex.Message;
                }

            }
            return status;
        }
        #endregion

EF调用数据库存储过程 多条数据--【分页】-首页参数可空

#region ==============EF调用数据库存储过程 多条数据--【分页】-首页参数可空===============
 
        /// <summary>
        /// EF调用数据库存储过程 多条数据--【分页】-首页参数可空
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <returns></returns> 
        [Route("EFProduct/ExecuteProcReader/{pageIndex?}")]
        //[HttpPost] 
        public string ExecuteProcReader(int pageIndex=1)
        {
            string status = string.Empty; 
            int pageSize = 10;//每页显示条数 
            int pageCount=0;//总页数 
            int start=(pageIndex-1)*pageSize+1;
            int end=pageIndex*pageSize;
            /*
             * 假如当前是第一页
             * start=（1-1）*10+1
             * start=1
             * end=1*10
             * end=10
             * 也就是第1条到第10条是第一页的数据
             * ****************************************
             * 假如当前是第二页
             * start=（2-1）*10+1
             * start=11
             * end=2*10
             * end=20
             * 也就是第11条到第20条是第二页的数据
             */
            int minnum = start;
            int maxnum = end;


            using (TAOBAODBEntities taobaoEntity = new TAOBAODBEntities())
            {
                try
                {
                    SqlParameter[] paras ={
                       new SqlParameter("@minnum",minnum),
                       new SqlParameter("@maxnum",maxnum),
                       new SqlParameter("@ProductCount",System.Data.SqlDbType.Int)//指定输出参数类型
                        };
                    paras[2].Direction = ParameterDirection.Output;//指明第3个参数为输出参数
                    List<Product> productList = taobaoEntity.Database.SqlQuery<Product>("exec proc_product_page @minnum,@maxnum,@ProductCount output", paras).ToList();//所有查询、存储过程调用
                    int ProductCount=(int)paras[2].Value;

                    //计算显示页数
                    pageCount=ProductCount/pageSize;
                    if (ProductCount%pageSize!=0)
                    {
                        pageCount++;
                    }
             
                    foreach (var item in productList)
                    {
                        status = status +$"[{item.ProductNo}]"+ item.ProductName + "[分页数据]<br/>";
                    }
                    status = status + "<br/><hr/>";
                    status = status + "当前页[" +pageIndex+ "]页&nbsp;&nbsp;";
                    status = status + "总页数[" +pageCount+ "]页&nbsp;&nbsp;";
                    status = status + "总条数[" +ProductCount+ "]条&nbsp;&nbsp;";
                   
                }
                catch (Exception ex)
                {
                    status = "数据错误;原因：" + ex.Message;
                }

            }
            return status;
        }
        #endregion

下面这个是存储过程的写法

SQL Server数据库存储过程分页，带总条数

https://www.cnblogs.com/cplvfx/articles/11573488.html