ADO.NET 的几个示例三，事物和存储过程

如题，存储过程如下

 

                USE [Table]
                GO

                SET ANSI_NULLS ON
                GO
                SET QUOTED_IDENTIFIER ON
                GO
                -- =============================================
                -- Author:  <Author,,Name>
                -- Create date: <Create Date,,>
                -- Description: <Description,,>
                -- =============================================
                ALTER PROCEDURE [dbo].[myProcedure]
                 @param1 varchar(50),
                 @param2 varchar(50),
                 @paramReturn varchar(50) OUTPUT
                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 A.USER_ID,A.WORKER_ID FROM T_USER A WHERE A.USER_ID = @param1
                 SET @paramReturn = 'return string'
                 
                 --返回值,isnull如果返回值是NULL则返回空
                 RETURN  isnull(@paramReturn,'')
                END 

 

 

 

调用代码

/// <summary> /// 存储过程 及带参数的存储过程调用 /// </summary> void CreateSqlTransaction() { SqlConnection conn; SqlTransaction tran; conn = new SqlConnection("Data Source=localhost;Initial Catalog=Table;Integrated Security=SSPI;"); conn.Open(); tran = conn.BeginTransaction(); SqlCommand comm = conn.CreateCommand(); comm.Transaction = tran; comm.CommandType = CommandType.StoredProcedure; comm.CommandText = "myProcedure"; comm.Parameters.AddWithValue("@param1", "12345"); comm.Parameters.AddWithValue("@param2", ""); comm.Parameters.AddWithValue("@paramReturn", ""); comm.Parameters[2].Direction = ParameterDirection.Output; SqlDataReader reader = comm.ExecuteReader(); reader.Read(); MessageBox.Show("WORKERID ：" + reader[1]);// reader.GetString(1) MessageBox.Show("param2　：" + comm.Parameters[1].Value.ToString());//存储过程的参数必须定义，不论使用与否 MessageBox.Show("paramReturn　：" + comm.Parameters[2].Value.ToString()); reader.Close(); tran.Commit(); conn.Close(); }