C# 存储过程传参数

/// <summary>
    /// 执行存储过程,返回dataset
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">参数</param>
    /// <param name="tableName">DataSet结果中的表名</param>
    /// <returns>DataSet</returns>
    protected DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
    {
        using (SqlConnection connection = new SqlConnection(connStr))
        {
            DataSet dataSet = new DataSet();
            connection.Open();
            SqlDataAdapter sqlDa = new SqlDataAdapter();
            sqlDa.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
            sqlDa.SelectCommand.CommandTimeout = 30;
            sqlDa.Fill(dataSet, tableName);
            connection.Close();
            return dataSet;

        }
    }

    /// <summary>
    /// 执行存储过程,返回第一行第一个结果
    /// </summary>
    /// <param name="storedProcName">存储过程名字</param>
    /// <param name="parameters">参数</param>
    /// <returns></returns>
    protected string RunProcedureExecuteScalar(string storedProcName, IDataParameter[] parameters)
    {
        using (SqlConnection connection = new SqlConnection(connStr))
        {
            connection.Open();

            SqlCommand cmd = BuildQueryCommand(connection, storedProcName, parameters);

            string result = cmd.ExecuteScalar().ToString();

            return result;
        }
    }


    /// <summary>
    /// 构建sqlCommand对象
    /// </summary>
    /// <param name="connection">数据库链接字符串</param>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">SqlCommand</param>
    /// <returns></returns>
    protected SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
    {
        SqlCommand command = new SqlCommand(storedProcName, connection);
        command.CommandType = CommandType.StoredProcedure;
        if (parameters != null && parameters.Length > 0)
        {
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if (parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
        }
        return command;
    }
USE [SiteFactory]
GO
/****** Object:  StoredProcedure [dbo].[PE_CommonModel_Add]    Script Date: 2021/4/21 14:22:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PE_CommonModel_Add]
    (

	  @NodeID INT ,
	  @ModelID int,

	  @TableName nvarchar(255),
	  @Title nvarchar(255),
	  @Inputer nvarchar(255),
	  @Hits int,
	  @DayHits int,
	  @WeekHits int,
	  @MonthHits int,
	  @LinkType int,
	  @UpdateTime datetime,
	  @CreateTime datetime,
	  @TemplateFile nvarchar(255),
	  @Status int,
	  @EliteLevel int,
	  @Priority int,
	  @CommentAudited int,
	  @CommentUnAudited int,
	  @SigninType int,
	  @InputTime datetime,
	  @PassedTime datetime,
	  @Editor nvarchar(255),
	  @LastHitTime datetime,
	  @DefaultPicUrl nvarchar(255),
	  @PinyinTitle nvarchar(1000),
	  @TitleFontColor nvarchar(255),
	  @TitleFontType nvarchar(255),
	  @IncludePic nvarchar(255),
	  @ShowCommentLink bit,
	  @TitleHashKey decimal(32, 0),
	  @HtmlPageName nvarchar(255),
	  @SGType int,
	  @SGDataId int,


	  @TitleIntact nvarchar(255),
	  @Content   ntext,
	  @OldUrl    nvarchar(255)
	  
    )
AS 

declare @error int =0 --事务中操作的错误记录
declare @id int =0
--开启事务
begin transaction

DECLARE @strSQL nvarchar(500)

   set @error+=@@ERROR --记录有可能产生的错误号

   select @id = max(GeneralID) from PE_CommonModel;

   set @id = isnull(@id,0)

   set @error+=@@ERROR --记录有可能产生的错误号

   INSERT INTO dbo.PE_U_Article (id,TitleIntact, Content,oldurl) 
   VALUES(@id+1,ltrim(rtrim(@TitleIntact)),@Content,@OldUrl);
   
   set @error+=@@ERROR --记录有可能产生的错误号
   
    INSERT  INTO PE_CommonModel
            ( GeneralID ,
              NodeID ,
              ModelID ,
              ItemID ,
              TableName ,
              Title ,
              Inputer ,
              Hits ,
              DayHits ,
              WeekHits ,
              MonthHits ,
              LinkType ,
              UpdateTime ,
              CreateTime ,
              TemplateFile ,
              Status ,
              EliteLevel ,
              Priority ,
              CommentAudited ,
              CommentUnAudited ,
              SigninType ,
              InputTime ,
			  PassedTime ,
			  Editor ,
			  LastHitTime ,
			  DefaultPicUrl ,
			  PinyinTitle ,
			  TitleFontColor ,
			  TitleFontType ,
			  IncludePic ,
			  ShowCommentLink ,
			  TitleHashKey ,
			  HtmlPageName ,
			  SGType ,
			  SGDataId 
            )
    VALUES  ( @id+1,
              @NodeID ,
              @ModelID ,
              @id+1,
              @TableName ,
              @Title ,
              @Inputer ,
              @Hits ,
              @DayHits ,
              @WeekHits ,
              @MonthHits ,
              @LinkType ,
              @UpdateTime ,
              @CreateTime ,
              @TemplateFile ,
              @Status ,
              @EliteLevel ,
              @Priority ,
              @CommentAudited ,
              @CommentUnAudited ,
              @SigninType ,
              @InputTime ,
              @PassedTime ,
              @Editor ,
			  @LastHitTime ,
			  @DefaultPicUrl ,
			  @PinyinTitle ,
			  @TitleFontColor ,
			  @TitleFontType ,
			  @IncludePic ,
			  @ShowCommentLink ,
			  @TitleHashKey ,
			  @HtmlPageName ,
			  @SGType ,
			  @SGDataId 
		);
		set @error+=@@ERROR --记录有可能产生的错误号

--判断事务的提交或者回滚
if(@error<>0)
  begin
    rollback transaction
    return -1 --设置操作结果错误标识
  end
else
  begin
    commit transaction
    return 1 --操作成功的标识
  end

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值