ADO调用存储过程实例讲解

1：直接有返回值的存储过程：
public string GetProjectCode() 
        {
            string strProc = "CreateProjectNumber";
	    //这个@ProjectNumber输出参数的设置，必须数据类型与长度与存储过程完全一致
            SqlParameter sp = new SqlParameter("@ProjectNumber",SqlDbType.VarChar,50);
            sp.Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(SqlHelper.ITODBConnectionStr, CommandType.StoredProcedure, strProc, sp);
            return sp.Value.ToString(); ;

        }
或者直接以SQL的形式调用：
 public string GetProjectCode() 
        {
            string strSql = "Declare @ProjectNumber varchar(50) EXEC CreateProjectNumber @ProjectNumber OUTPUT SELECT @ProjectNumber ";
            string a = SqlHelper.ExecuteScalar(SqlHelper.ITODBConnectionStr, CommandType.Text, strSql).ToString();
            return a;
        }
    /// <summary>
    /// 调用无参数的存储过程，直接调用存储过程的返回值
    /// </summary>
    /// <returns></returns>
    public static DataTable Pro_Categorys_Products() 
    {
        string procedureName = "pro_Categorys_Products";
        SqlParameter sp = new SqlParameter();
        //获取存储过程的返回值，在这里设置
        sp.Direction = ParameterDirection.ReturnValue;
        DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.StoredProcedure, procedureName, sp).Tables[0];
        //当存储过程执行完毕后，返回存储过程的返回结果
        string spValue = sp.Value.ToString();
        return dt;
    }
2：调用SQLFUNCTION函数返回值：
public string GetTaskNumber() 
        {
            string strFunction = "CreateTaskNumber";
            SqlParameter[] sp = {
                new SqlParameter("@Result", SqlDbType.VarChar)                    
            };
            sp[0].Direction = ParameterDirection.ReturnValue;
            SqlHelper.ExecuteNonQuery(SqlHelper.ITODBConnectionStr, CommandType.StoredProcedure, strFunction, sp);
            return sp[0].Value.ToString();
         
        }


3：调用无返回值的存储过程：

//这种调用方式，参数不需要显示设置与存储过程中的输出参数长度一致
 public static DataTable Pro_Categorys_ProductsByCount(int beginNumber,int endNumber) 
    {
        string proceduceName = "pro_Categorys_ProductsByCount";
        //定义输入参数的时候，需要与存储过程中定义的参数名称一致，数据类型也一致
        SqlParameter[] sp = { 
            new SqlParameter("@ProductNumberBegin",SqlDbType.Int),
            new SqlParameter("@ProductNumberEnd",SqlDbType.Int)
        };
        sp[0].Value = beginNumber;
        sp[1].Value = endNumber;

        DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.StoredProcedure, proceduceName, sp).Tables[0];
        return dt;
    }

4：调用返回结果集与返回值的存储过程

存储过程：

ALTER PROC TEST123
(
	@TNAME VARCHAR(20)
)
AS
BEGIN
	DECLARE @ReturnCount INT,@StrSql varchar(300)
	SELECT @ReturnCount = COUNT(*) FROM TEST 
	
	SELECT @StrSql  = 'SELECT * FROM TEST WHERE TNAME = '''+@TNAME+''' '
	
	PRINT @StrSql
	
	EXEC(@StrSql)
	
	RETURN @ReturnCount
	
END

GO

应用程序：

public static DataTable ProTest( out int returnValue)
        {
            string procedureName = "TEST123";
            SqlParameter[] sp = {   
            new SqlParameter("@TNAME",SqlDbType.VarChar),  
            new SqlParameter("@ReturnCount",SqlDbType.Int)  
            };
            sp[0].Value = "A";
            sp[1].Direction = ParameterDirection.ReturnValue;
            DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.DBConnectString, CommandType.StoredProcedure, procedureName, sp).Tables[0];
            //当存储过程执行完毕后，返回存储过程的返回结果   
            returnValue = Convert.ToInt32(sp[1].Value);
            return dt;
        }  

调用端：

 	   int a;
            DataTable dt =  ProTest(out a);

            int b = a;