It's very common to reduce the number of trips to improve performance, so I wrote the procedure to return multiple resultsets. The complete procedure code is as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_getContracts]
-- Add the parameters for the stored procedure here
@UWYear int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT *
from ContractDetail
where UWYear = @UWYear
--UWYear-1 contracts
SELECT *
from ContractDetail
where UWYear = @UWYear - 1
and ContractNumber not in
(SELECT ContractNumber
from ContractDetail
where UWYear = @UWYear)
--UWYear-2 contracts
SELECT *
from ContractDetail
where UWYear = @UWYear - 2
and ContractNumber not in
(SELECT ContractNumber
from ContractDetail
where (UWYear = @UWYear or UWYear = @UWYear-1))
END
I am getting rows for three years and one way to do is to call procedure three times but again, I want to improve performance and return three resultsets.
Well, lets get them in .NET code. I am using Enterprise Library 2.0 for data access and you can see, it can help writing cleaner code along with other benefits. I wont go in details of Enterprise Library in this article.
Once I get dataset filled with data, multiple tables (3 in this case) and I want one table with all the data combined.
So I created dsMergedContract as new dataset for merged data, add the table into it and them merge rows from all the tables into the table added to merged dataset. The code looks like:
public DataSet GetCurrentYearContracts(int UWYear)
{
DataSet dsContract = new DataSet();
DataSet dsMergedContract = new DataSet();
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "usp_getContracts";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "UWYear", DbType.Int32, UWYear);
db.LoadDataSet(dbCommand, dsContract, "ContractDetail");
dsMergedContract.Tables.Add("ContractDetail");
for (int i = 0; i < dsContract.Tables.Count; i++)
{
dsMergedContract.Tables["ContractDetail"].Merge(dsContract.Tables[i]);
}
return dsMergedContract;
}
If you try to use DataSet.Merge method, it will create three tables and that's not what I want. Merge method for table will do the trick.