C#存储过程的调用和数据库中编写

1 项目中调用存储过程

  /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string sqlconnectionString = ConfigurationManager.ConnectionStrings["sqlconn"].ToString();
        /// <summary>
        /// 数据库连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection SqlConnection()
        {
            var connection = new SqlConnection(sqlconnectionString);
            connection.Open();
            return connection;
        }

//方法
public int upOrderInfo(QX_ScoreOrderInfoModel OrdModel)
        {
            int res = 0;
            try
            {
                using (var conn = SqlHelper.SqlConnection())
                {
                    SqlCommand com = new SqlCommand();
                    com.CommandText = "upOrderInfo";
                    com.CommandType = CommandType.StoredProcedure;
                    com.Connection = conn;
                    com.Parameters.Add(new SqlParameter("@PayTypeID", OrdModel.PayTypeID));
                    com.Parameters.Add(new SqlParameter("@PayTypeName", OrdModel.PayTypeName));
                    com.Parameters.Add(new SqlParameter("@PayNO", OrdModel.PayNO));
                    com.Parameters.Add(new SqlParameter("@PayAmount", OrdModel.PayAmount));
                    com.Parameters.Add(new SqlParameter("@OrderNO", OrdModel.OrderNO));
                    //返回查询结果的第一行第一列的值
                    res = com.ExecuteScalar().ToInt();
                }

            }
            catch (Exception e)
            {
                res = 0;
            }
            return res;
        }

 2 数据库中存储过程的编写

USE [quanxi_tong]
GO
/****** Object:  StoredProcedure [dbo].[upOrderInfo]    Script Date: 2022/11/9 11:07:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[upOrderInfo] 
	-- Add the parameters for the stored procedure here
	@PayTypeID int,
	@PayTypeName varchar(30),
	@PayNO varchar(50),
	@PayAmount decimal(14, 2),
	@OrderNO varchar(30)
AS
BEGIN

--根据订单号查询订单信息
declare @OrderType int,@Score int,@UserType int,@UserId int,@PayScore int,@ProductId int,@UseDay int 

select @OrderType=OrderType,@Score=Score,@UserType=UserType,@UserId=UserId,@PayScore=PayScore,@ProductId=ProductId,@UseDay=UseDay from QX_OrderInfo  where OrderNO=@OrderNO;

--获取机构积分信息
declare @OrgScore int=0 ,@UserScore int=0
if	@UserType=1
  begin 
  select @OrgScore=Score  from QX_Base  where BaseId=@UserId;
  end	

--获取用户积分信息
if	@UserType=0
  begin 
  select @UserScore=Score  from QX_Member  where UserId=@UserId;
  end	

--开始执行修改
   begin tran  tran1
   --1 修改订单信息表数据
	update QX_OrderInfo set PayTypeID=@PayTypeID,
	PayTypeName=@PayTypeName,
	PayNO=@PayNO,
	PayAmount=@PayAmount,
	PayTime=GETDATE(),
	PayStatus=2,
	Status=1,
	SuccessTime=GETDATE()
	where OrderNO=@OrderNO

	
	--2 判断购买类型 
	--购买积分 OrderType=73  并且是机构会员
	if @@error =0
	  if (@OrderType=73 and @UserType=1)
	  begin 
	  --1 修改机构的积分信息
	  update QX_Base set Score=Score+@Score  where BaseId=@UserId;
	  --2 去修改机构积分流水表
	  insert into QX_ScoreOrg_TradeDetails (BaseId,BeforeScore,TradeScore,AfterScore,TradeType,TradeTime,TradeTitle,TradeDescription,SourceSystemCode,OrderNO) 
	   values (@UserId,@OrgScore,@Score,@OrgScore+@Score,0,GETDATE(),'购买积分','机构会员在机构中心上购买了积分套餐',1014,@OrderNO)
	  end

	--购买全媒体 OrderType=74 并且用户支付积分大于0
	if (@OrderType=74 and @PayScore>0)
	begin 
	  --1 修改用户的积分信息
	  update QX_Member set Score=Score-@PayScore  where UserId=@UserId;
	  --2 修改用户积分流水表
	  insert into QX_Score_TradeDetails (UserId,BeforeScore,TradeScore,AfterScore,TradeType,TradeTime,TradeTitle,TradeDescription,SourceSystemCode,OrderNO) 
	   values (@UserId,@UserScore,@PayScore,@UserScore-@PayScore,1,GETDATE(),'购买消费积分','用户在用户中心购买商品消耗积分',1013,@OrderNO)
	  end

	--购买会员 OrderType=75
	if @OrderType=75
	begin
	--1 根据产品id，获取角色id
	declare @RoleId int,@QX_UserRoleRelationId int=0,@EndTime datetime
	select @RoleId=RoleId from QX_Product  where ProductId=@ProductId; 

	if	@RoleId>0
	--2 去用户角色权限关系表中添加，之前存在就修改
	select @QX_UserRoleRelationId=QX_UserRoleRelationId,@EndTime=EndTime from QX_UserRoleRelation  where RoleId=@RoleId and  UserId=@UserId; 
	--没有记录，就新增数据
	if @QX_UserRoleRelationId =0
	insert into QX_UserRoleRelation  (UserId,EndTime,CreateTime,RoleId)  values (@UserId,dateadd(day,@UseDay,GETDATE()),GETDATE(),@RoleId)
	--存在就修改数据
	if @QX_UserRoleRelationId>0
	update QX_UserRoleRelation  set EndTime=dateadd(day,@UseDay,@EndTime)  where UserId=@UserId  and RoleId=@RoleId;
	
	end 
if( @@error=0)
		begin
			commit tran tran1
			select '1' as result
		end
else
		begin 
			select '0' as result
			rollback tran tran1		
			return;
		end
END

3 项目中执行存储过程，根据返回值，做后续处理。