AspNet 执行存储过程带参数 and Entity Framework 之存储过程篇

1：两个参数 

public static DataSet MethodName(DateTime DELIVERY_DATE, string WAREHOUSE_CODE)
{
     ArrayList ary = new ArrayList();
     ary.Add(DELIVERY_DATE);//这里的参数就是存储过程中的参数名，要一样一样的
     ary.Add(WAREHOUSE_CODE);
   return ExecuteStoreProcedure(procedureName, ary);
}

public static DataSet ExecuteStoreProcedure(string procedureName, ArrayList ary)
    {
        Database db = DatabaseFactory.CreateDatabase(connstring);
        DbConnection conn = db.CreateConnection();
        try
        {
            conn.Open();//数据库打开了，下面就是可以就行操作了。
            object[] paramsObject = new object[ary.Count];
            //把所有的参数转存到Parameters中
            for (int i = 0; i < ary.Count; i++)
            {
                paramsObject[i] = ary[i];
            }
            return db.ExecuteDataSet(procedureName, paramsObject);
        }
        finally
        {
            conn.Close();
            conn = null;
        }
    }

SqlParameter[] parameters =  {
                                  new SqlParameter("@sDate",dateGet1.ToShortDateString()),//存储过程的参数
                                  new SqlParameter("@eDate",dateGet2.ToShortDateString()),//                            
                              };


            DataTable Tables = CommonAPI.SpReturnTable("存储过程名字", parameters);

 public static DataTable SpReturnTable(string storedProcName, SqlParameter[] parameters)
    {
        try
        {
            using (SqlConnection conStr = new SqlConnection("数据不哭"))
            {
                DataTable dt = new DataTable();
                conStr.Open();
                SqlTransaction trans = null; //创建事务
                trans = conStr.BeginTransaction();
                try
                {
                    SqlCommand comStr = new SqlCommand(storedProcName, conStr);
                    comStr.Transaction = trans;
                    comStr.CommandType = CommandType.StoredProcedure; //设置执行类型为存储过程  
                    foreach (SqlParameter parameter in parameters)
                    {
                        comStr.Parameters.Add(parameter);
                    }
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        sda.SelectCommand = comStr;
                        sda.Fill(dt);
                    }
                    trans.Commit(); //提交事务
                    return dt;
                }
                catch (Exception ex)
                {
                    string a = ex.Message;
                    trans.Rollback(); //回滚事务
                    return null;
                }
                finally
                {
                    conStr.Close();
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception("出现异常：" + ex.Message + "");
        }
    }

 

Entity Framework 之存储过程篇

脚本如下，这是一个不带参数的存储过程

create procedure dbo.ProductsSel
    AS
        BEGIN
        SELECT * FROM Products
    END
GO    

以FromSql方式调用存储过程

[Produces("application/json")]
    [Route("api/Banner")]
    public class BannerController : Controller
    {
        [HttpGet]
        public IEnumerable<Products> GetProducts()
        {
            using (EFCoreContext efcore = new EFCoreContext())
            {
                var query = efcore.Products.FromSql("execute dbo.ProductsSel");
                return query.ToList();
            }
        }
    }

带参，只不过还是sql的问题，当然我们需要去修改一下我们的存储过程。修改后的脚本如下

create procedure dbo.ProductsSel
    @cid int
AS
BEGIN
    SELECT * FROM Products
        Where ProductId = @cid
END
GO

调用：

[HttpGet]
        public IEnumerable<Products> GetProductsById(int id)
        {
            var paramter = new SqlParameter
            {
                DbType = System.Data.DbType.Int32,
                ParameterName = "cid",
                Value = id
            };
            using (EFCoreContext efcore = new EFCoreContext())
            {
                var query = efcore.Products.FromSql("execute dbo.ProductsSel @cid",paramter);
                return query.ToList();
            }
        }

最简单是是这样

string sql ="select * from xxx"
efcore.Database.ExecuteSqlCommand(sql);