【学习笔记】C#执行存储过程传入参数并返回output参数和return_value的方法

1.存储过程

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		fangpan
-- Create date: 2012-2-1
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ECSS30_Stat]
@BeginTime DATETIME,
@EndTime DATETIME,
@Count int OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	
	--1.创建临时表
	IF OBJECT_ID('TEMPDB..#TEMP_FANGPAN')IS NOT NULL
	DROP TABLE #TEMP_FANGPAN
	CREATE TABLE #TEMP_FANGPAN(
	[UserContent] [varchar](256),
	[RecTime] [datetime],
	[RecResult] [char](1),
	[RecResultCode] [varchar](32),
	[RecResultDesc] [varchar](256),
	[SubRecType] [varchar](16),
	[RecBizName] [varchar](32),
	[RecOperationName] [varchar](16)
	)
	
	--2.筛选识别失败数据插入临时表
	INSERT INTO #TEMP_FANGPAN(
	[UserContent],
	[RecTime],
	[RecResult],
	[RecResultCode],
	[RecResultDesc],
	[SubRecType],
	[RecBizName],
	[RecOperationName]
	)
	select a.[UserContent],
	a.[RecTime],
	a.[RecResult],
	a.[RecResultCode],
	a.[RecResultDesc],
	a.[SubRecType],
	a.[RecBizName],
	a.[RecOperationName]
	from dbo.LogRec_Route a
	where [RecResult]='0'

	--3.统计
	select @Count=COUNT(*) FROM #TEMP_FANGPAN
	where [RecTime]>=@BeginTime
	and [RecTime]<=@EndTime
	and [RecResultCode]='RouteResult_Items_IsNull'
	IF @Count!=0
	RETURN 1
	ELSE
	RETURN 0
END

2.执行存储过程

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
namespace READDATA
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection("User ID=sa;Initial Catalog=ECSS20_logtest_AHMobile;Data Source=192.168.85.76;Password=ctitest");
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "ECSS30_Stat";
            cmd.CommandType = CommandType.StoredProcedure;
            IDataParameter[] parameters={
                new SqlParameter("@BeginTime",SqlDbType.DateTime),
                new SqlParameter("@EndTime",SqlDbType.DateTime),
                new SqlParameter("@Count",SqlDbType.Int),
                new SqlParameter("return_value",SqlDbType.Int)
            };
            parameters[0].Value = "2011-01-01";
            parameters[1].Value = "2012-02-01";
            parameters[2].Direction = ParameterDirection.Output;
            parameters[3].Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.AddRange(parameters);
            cmd.ExecuteNonQuery();
            conn.Close();
            Console.WriteLine("@Count:" + parameters[2].Value);
            Console.WriteLine("return_value:" + parameters[3].Value);
            Console.ReadKey();
        }
    }
}