asp.net mssqlserver 存储过程

mssql server 返回多表结果集

 

mssqlserver 代码

create PROCEDURE [dbo].[gd]

AS
BEGIN


    SELECT 1,12

    SELECT 21,22
END

 

 

C#代码

    using (SqlConnection conn = new SqlConnection("Data Source=服务器;Initial Catalog=数据库;Persist Security Info=True;User ID=用户名;Password=密码"))
            {
                //查询少量数据时，用适配器（内部就是用 读取器 读取数据然后装入 数据集/数据表 返回）
                SqlDataAdapter da = new SqlDataAdapter("gd", conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                //创建数据表
                DataTable dt = new DataTable();
                DataSet ds = new DataSet();
                //执行查询并填充数据
                da.Fill(ds);
                conn.Close();
                da.SelectCommand.Dispose();
                da.SelectCommand.Parameters.Clear();

                
            }

 

访问形式

                string tab1row1col1 = ds.Tables[0].Rows[0][0].ToString();

                string tab1row1col2 = ds.Tables[0].Rows[0][1].ToString();

                string tab2row1col1 = ds.Tables[1].Rows[0][0].ToString();

                string tab2row1col2 = ds.Tables[1].Rows[0][1].ToString();

 

mssql server 返回单表结果集

 

C#代码

 

  using (SqlConnection conn = new SqlConnection("Data Source=服务器;Initial Catalog=数据库;Persist Security Info=True;User ID=用户名;Password=密码"))
            {
                SqlDataAdapter da = new SqlDataAdapter("gd", conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                //创建数据表
                DataTable dt = new DataTable();
                //执行查询并填充数据
                da.Fill(dt);
                conn.Close();
                da.SelectCommand.Dispose();
                da.SelectCommand.Parameters.Clear();

            }

 

mssql server return返回

 

mssqlserver 代码

create PROCEDURE [dbo].[gd_return]

AS
BEGIN


    return '123'

    
END

 

C#代码

  using (SqlConnection conn = new SqlConnection("数据库连接字符串"))
            {
                SqlParameter[] cmdParms = {new SqlParameter("@return",SqlDbType.VarChar)};

                cmdParms[0].Direction= ParameterDirection.ReturnValue;

                SqlCommand sqlCommand = new SqlCommand("gd_return", conn);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.AddRange(cmdParms);
                conn.Open();
                sqlCommand.ExecuteNonQuery();
                object bj = cmdParms[0].Value;
                string result = bj.ToString();
            }

 

mssql server output返回

mssqlserver 代码

create PROCEDURE [dbo].[gd_output]
   @test1 int output,
   @test2 int output
AS
BEGIN

   set @test1=1;

   set @test2=2;

    
END

C#代码

using (SqlConnection conn = new SqlConnection("数据库连接字符串"))
            {
                SqlParameter[] cmdParms = {new SqlParameter("@test1",SqlDbType.Int),
                                          new SqlParameter("@test2",SqlDbType.Int)
                                          };

                cmdParms[0].Direction= ParameterDirection.Output;
                cmdParms[1].Direction = ParameterDirection.Output;

                SqlCommand sqlCommand = new SqlCommand("gd_output", conn);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.AddRange(cmdParms);
                conn.Open();
                sqlCommand.ExecuteNonQuery();
                object ob1 = cmdParms[0].Value;
                object ob2 = cmdParms[0].Value;
                string result1 = ob1.ToString();
                string result2 = ob2.ToString();

            }