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]
-
- for the stored procedure here
- @UWYear int
- AS
- BEGIN
-
-
- SET NOCOUNT ON;
-
- for procedure here
- SELECT *
- from ContractDetail
- where UWYear = @UWYear
-
- SELECT *
- from ContractDetail
- where UWYear = @UWYear - 1
- and ContractNumber not in
- (SELECT ContractNumber from ContractDetail where UWYear = @UWYear)
-
- 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 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:
- 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 will create three tables and that's not what I want. Merge method for the table will do the trick.