存储过程的创建和使用

一、执行存储过程

1、执行insert存储过程

执行insert、delete、update存储过程与执行insert、delete、update语句一样，都是使用Execute()

CREATE PROCEDURE [dbo].[proc_AddSysUser01]
	-- Add the parameters for the stored procedure here
	@Name nvarchar(50),
	@Phone nvarchar(50)
AS
BEGIN
	--SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	insert into Sys_User values(@Name,@Phone,'耶路撒冷',GETDATE())
END
GO

执行的方法是：

[HttpPost]
public ActionResult InsertProcedure(FormCollection collection)
{
    try
    {
        #region 执行insert存储过程
        using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NHibernate"].ConnectionString))
        {
            int result = connection.Execute("proc_AddSysUser01", new
            {
                Name = collection["name"],
                Phone = collection["phone"]
            }, commandType: CommandType.StoredProcedure);
        }
        #endregion
 
        return RedirectToAction("Index");
    }
    catch (Exception ex)
    {
        return View();
    }
}

2、执行select存储过程

执行select存储过程与执行select语句一样，都是使用Query<Sys_User>()

CREATE PROCEDURE [dbo].[proc_GetSysUser] 
	-- Add the parameters for the stored procedure here
	@Id int,
	@Name nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	select * from Sys_User where Id<@Id and Name like '%'+@Name+'%'
END
GO

执行方法：

public ActionResult SelectProcedure(int id, string name)
{
    try
    {
        #region 执行select存储过程
        using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NHibernate"].ConnectionString))
        {
            List<Sys_User_bak> userList = connection.Query<Sys_User_bak>("proc_GetSysUser", new
            {
                Id = id,
                Name = name
            }, commandType: CommandType.StoredProcedure).AsList();
        }
        #endregion
 
        return RedirectToAction("Index");
    }
    catch (Exception ex)
    {
        return View();
    }
}

二、in操作
————————————————
版权声明：本文为CSDN博主「changuncle」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xiaouncle/article/details/82962776

以下是我的代码：
首选创建存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create  PROCEDURE [dbo].[VotePlayAdd]
	 @playerId nvarchar(10),
	 @voteNums int,
	 @ActivityId nvarchar(10),
	 @Ip nvarchar(20),
	 @CityName nvarchar(40),
	 @PersonId nvarchar(10), 
	 @OutSingle int output ---导出数据获取数据
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
     DECLARE @IpInCount int;--定义当前Ip数量
     select @IpInCount = COUNT(*) from    [OpreationLog] where Ip = @Ip and createtime > DATEADD(D,-1,GETDATE())
     if(@IpInCount>100)---如果投票一天大于100不让其投
     begin
     set @OutSingle = 300
     end
     else---如果没有超过100 即可投票
     begin
     update Player set VoteNum=VoteNum+@VoteNums where PlayerId=@playerId   ---更新投票表
     insert into  OpreationLog (VisitorId,ActivityId, PlayerId, CreateTime, VoteType, VoteNum, IP, CityName) 
      values(@PersonId,@ActivityId,@playerId,CONVERT(varchar(100), GETDATE(), 121),'普票',@voteNums,@Ip,@CityName)
     
     set @OutSingle = @IpInCount
     
     end  
END

执行方法：

 public void SetDB()
        {
            string Constr = "Data Source = .;Initial Catalog = VotingManagement;User Id = sa;Password = king81;";
            IDbConnection conn = new SqlConnection(Constr);
            try
            {
                conn.Open();
                DynamicParameters dp = new DynamicParameters();
                dp.Add("@playerId", "2");
                dp.Add("@VoteNums", 16);
                dp.Add("@ActivityId", "1");
                dp.Add("@Ip", "192.168.0.2");
                dp.Add("@CityName", "桌面测试");
                dp.Add("@PersonId", "1111122222233333");  
                dp.Add("@OutSingle", 0, DbType.Int32, ParameterDirection.Output);//导出参数
                int IpCountInDay = conn.ExecuteScalar<int>("VotePlayAdd", dp, null, null, CommandType.StoredProcedure);
                int OutSingle = dp.Get<int>("@OutSingle");
                if (OutSingle == 300)
                {
                    this.skinLabel2.Text = "300";
                    this.skinLabel1.Text = "本IP今天投票过多被禁止！";
                }
                else
                {
                    this.skinLabel2.Text = "200";
                    this.skinLabel1.Text = "成功了";
                }
            }
            catch (Exception ex)
            {
                this.skinLabel2.Text = "100";
                this.skinLabel1.Text = "错误！";
            }