mssql server 返回多表结果集

 

mssqlserver 代码

create PROCEDURE [dbo].[gd]ASBEGIN    SELECT 1,12    SELECT 21,22END

 

 

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]ASBEGIN    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 outputASBEGIN   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();            }