获取存储过程返回数据

    public SqlConnection conn = new SqlConnection("...");
    public SqlCommand com = new SqlCommand();
    public SqlDataAdapter adpt = new SqlDataAdapter();
    public DataSet set = new DataSet();
    public DataTable dtb = new DataTable();

    //insert,update,delect语句
    public int pro0()
    {
        com.Connection = conn;
        com.CommandType = CommandType.Text;
        com.CommandText = "insert into t_zichan values(id)";
        int i = 0;
        try
        {
            com.Connection.Open();
            i = com.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
        }
        com.Connection.Close();
        //insert,update,delect执行ExecuteNonQuery()返回影响行数 >0表示成功
        //此影响行数为对数据的变化 select语句用ExecuteNonQuery()将返回-1
        return i;
    }
    //select语句
    public DataTable pro1()
    {
        com.Connection = conn;
        com.CommandType = CommandType.Text;
        com.CommandText = "select * from t_zichan";
        //写法1
        com.Connection.Open();
        SqlDataReader reader = com.ExecuteReader();
        dtb.Load(reader);
        com.Connection.Close();
        /*写法2使用SqlDataAdapter
        com.CommandText = "select * from t_zichan; select * from t_user;";
        //一次执行多句查询
        adpt.SelectCommand = com;
        adpt.SelectCommand.Connection.Open();
        adpt.Fill(set);
        //结果集中一张表也可以直接导入表中adpt.Fill(DataTable) 多结果导入adpt.Fill(DataSet)
        dtb = set.Tables[0];
        adpt.SelectCommand.Connection.Close();
        */
        return dtb;
    }
    //存储过程返回结果集
    public DataSet pro2()
    {
        com.Connection = conn;
        com.CommandType = CommandType.StoredProcedure;
        com.CommandText = "bbb";
        adpt.SelectCommand = com;
        adpt.SelectCommand.Connection.Open();
        adpt.Fill(set);
        adpt.SelectCommand.Connection.Close();
        return set;
    }
    //返回影响行数
    public int pro3()
    {
        com.Connection = conn;
        com.CommandType = CommandType.StoredProcedure;
        com.CommandText = "bbb";
        com.Connection.Open();
        int i;
        i = com.ExecuteNonQuery();
        com.Connection.Close();
        return i;
    }
    //返回结果集的一行一列  适用于查询结果一行一列 比如count(*)查询分页的总行数    
    public int pro4()
    {
        com.Connection = conn;
        com.CommandType = CommandType.StoredProcedure;
        com.CommandText = "ccc";
        com.Connection.Open();
        int i;
        i = (int)com.ExecuteScalar();
        com.Connection.Close();
        return i;
    }
    //返回存储过程的几种结果 out参数 return参数 影响行数 结果集
    public string pro5()
    {
        com.Connection = conn;
        com.CommandType = CommandType.StoredProcedure;
        com.CommandText = "ddd";
        com.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));
        com.Parameters["@a"].Value = 21;
        //DECLARE @a int
        com.Parameters.Add(new SqlParameter("@b", SqlDbType.VarChar,20));
        com.Parameters["@b"].Direction = ParameterDirection.Output;
        //DECLARE @b varchar(20)
        com.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
        com.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
        //DECLARE @return int
        int i = 0;
        try
        {
            com.Connection.Open();
            i = com.ExecuteNonQuery();
            //异步执行i = com.EndExecuteNonQuery(com.BeginExecuteNonQuery());
            //exec @return=ddd @a,@b output
            dtb.Load(com.ExecuteReader());
            //DataSet也可以.Load(reader)装入SqlDataReader
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (com.Connection.State == ConnectionState.Open)
                com.Connection.Close();
        }
        string s = "";
        s += "Return: " + com.Parameters["@return"].Value.ToString() + ", ";
        s += "Output: " + com.Parameters["@b"].Value.ToString() + ", ";
        s += "结果集: 1/" + dtb.Rows[0][0].ToString() + " 2/" + dtb.Rows[0][1].ToString() + ", ";
        s += "影响行数: " + i + ",";
        return s;
    }

 存储过程

CREATE procedure [dbo].[bbb]
as
    SELECT  * FROM t_zichan
    SELECT  * FROM t_users

GO


CREATE procedure [dbo].[ccc]
as
    SELECT 5

GO


CREATE procedure [dbo].[ddd]
    @a int,
    @b varchar(20) output
as
    select '一格',78
    set @b = 'output'
    exec Pro_Delete_Zichan @a --产生影响行数
    return @a+1
GO