在C#中使用預存程序（帶輸入參數）

1首先在SQL Server中使用預存程序

CREATE procedure [dbo].[sp_dia] 
@age int, 
@tot int=0 output 
as 
select Sage,Sname 
from Students 
where Sage = @age; 
select @tot = COUNT(*) 
from Students 
where Sage = @age 
return @tot 

2然後建立一個控制台應用程式項目

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CallSp2
{
    class CallSp2
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection(@"
            server = ./WB_JAMES;
            integrated security = SSPI;
            database = master");
        try
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_dia";
            /////////
            SqlParameter inparm = cmd.Parameters.Add(
                "@age", SqlDbType.Int);
            inparm.Direction = ParameterDirection.Input;
            inparm.Value = 18;
            ///////////
            SqlParameter ouparm = cmd.Parameters.Add(
                "@tot", SqlDbType.Int);
            ouparm.Direction = ParameterDirection.Output;
            ///////////////
            SqlParameter retval = cmd.Parameters.Add(
                "return_value", SqlDbType.Int);
            retval.Direction = ParameterDirection.ReturnValue;
            ///////////
            SqlDataReader rdr=cmd.ExecuteReader();
            while(rdr.Read())
            {
                Console.WriteLine("{0} {1}"
                    , rdr[0].ToString().PadRight(5), rdr[1].ToString());
            }
            rdr.Close();

            /////////
            Console.WriteLine(
                "The output parameter value is {0}",
                cmd.Parameters["@age"].Value);
            ///////////
            Console.WriteLine(
                "The return value is {0}",
                cmd.Parameters["return_value"].Value);
        }
        catch (System.Exception e)
        {
            Console.WriteLine(e.ToString());
        }
        finally
        {
            conn.Close();
        }
        }
    }
}