问题描述:

Below defined is the stored procedure which has two insert statement.

 USE [RHK_HIS]

GO

/****** Object: StoredProcedure [dbo].[createuser] Script Date: 01/23/2014 11:50:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[createuser]

-- Add the parameters for the stored procedure here

( @User_Id varchar(15),

@Password varchar(15),

@Name varchar(30),

@Role_Id varchar(10),

@Department_Id varchar(10),

@Active varchar(1)

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

insert into User_M (User_Id,Password,Name,Role_Id,Department_Id,Active) values (@User_Id,@Password,@Name,@Role_Id,@Department_Id,@Active)

insert into User_Dept_Map(User_Id,Dept_Id) values (@User_Id,@Department_Id)

END

Stored Procedure is been called in Connection.cs by a function specified below

 public int create_user(UserMaster ua)

{

SqlConnection Con = new SqlConnection(str);

Con.Open();

SqlCommand Cmd = new SqlCommand("createuser", Con);

Cmd.CommandType = CommandType.StoredProcedure;

try

{

log.Debug("Inside Create user");

Cmd.Parameters.AddWithValue("@User_Id", ua.UserName);

Cmd.Parameters.AddWithValue("@Password", ua.Password);

Cmd.Parameters.AddWithValue("@Name", ua.Name);

Cmd.Parameters.AddWithValue("@Role_Id", ua.Role);

Cmd.Parameters.AddWithValue("@Department_Id", ua.Department);

Cmd.Parameters.AddWithValue("@Active", ua.Active);

log.Debug("Inside Create_User: New User created having ID: " + ua.UserName);

log.Info("user created");

return Cmd.ExecuteNonQuery();

}

catch (Exception e)

{

log.Debug("Error: Inside catch block of Create User");

log.Error("Error msg:" + e);

log.Error("Stack trace:" + e.StackTrace);

throw e;

}

finally

{

Cmd.Dispose();

Con.Close();

Con.Dispose();

}

}

This code is working properly which will insert the values to two different table,Now my question is if I have two select statement of two different table in one stored procedure how to return the values in the function?? Should I use the object to return?? If so how to create an object.? Any help appreciated

网友答案:

You can get both select statement resultset in form of DataTable combined in Dataset.

Below code snippet is done using SqlDataAdapter & DataSet, change values wherever applicable

C# code

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("<SQL Server Connection string>");
System.Data.SqlClient.SqlDataAdapter adptr = new System.Data.SqlClient.SqlDataAdapter("exec <Stored Proc name>", conn);
System.Data.DataSet dataset = new System.Data.DataSet();
adptr.Fill(dataset);

System.Data.DataTable table = new System.Data.DataTable();
table = dataset.Tables[0];
//do something for table-1

table = dataset.Tables[1];
//do something for table-2
相关阅读:
Top