Merging data into single table from multiple tables (resultsets)

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:
  1. set ANSI_NULLS ON  
  2. set QUOTED_IDENTIFIER ON  
  3. go  
  4. ALTER PROCEDURE[dbo]. [usp_getContracts]  
  5.   --Add the parameters  
  6. for the stored procedure here  
  7. @UWYear int  
  8. AS  
  9. BEGIN  
  10. --SET NOCOUNT ON added to prevent extra result sets from  
  11. --interfering with SELECT statements.  
  12. SET NOCOUNT ON;  
  13. --Insert statements  
  14. for procedure here  
  15. SELECT *  
  16.   from ContractDetail  
  17. where UWYear = @UWYear  
  18.   --UWYear - 1 contracts  
  19. SELECT *  
  20.   from ContractDetail  
  21. where UWYear = @UWYear - 1  
  22. and ContractNumber not in  
  23.   (SELECT ContractNumber from ContractDetail where UWYear = @UWYear)  
  24.   --UWYear - 2 contracts  
  25. SELECT *  
  26.   from ContractDetail  
  27. where UWYear = @UWYear - 2  
  28. and ContractNumber not in  
  29.   (SELECT ContractNumber from ContractDetail where(UWYear = @UWYear or UWYear = @UWYear - 1))  
  30. END  
I am getting rows for three years and one way to do is to call the procedure three times but again, I want to improve performance and return three resultsets.
 
Well, let's 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 won't go into detail about the 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:
  1. public DataSet GetCurrentYearContracts(int UWYear) {  
  2.       DataSet dsContract = new DataSet();  
  3.       DataSet dsMergedContract = new DataSet();  
  4.       Database db = DatabaseFactory.CreateDatabase();  
  5.       string sqlCommand = "usp_getContracts";  
  6.       DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);  
  7.       db.AddInParameter(dbCommand, "UWYear", DbType.Int32, UWYear);  
  8.       db.LoadDataSet(dbCommand, dsContract, "ContractDetail");  
  9.       dsMergedContract.Tables.Add("ContractDetail");  
  10.       for (int i = 0; i < dsContract.Tables.Count; i++) {  
  11.             dsMergedContract.Tables["ContractDetail"].Merge(dsContract.Tables[i]);  
  12.       }  
  13.       return dsMergedContract;  
  14. }  
If you try to use DataSet.Merge method will create three tables and that's not what I want. Merge method for the table will do the trick.


Similar Articles