In this article, we would continue to delve further and would see the implementation with LINQTOSQL. With the tool for LINQTOSQL, the method stub generated for stored procedure supports single result and hence it needs to be modified to support multiple results. This can be achieved through implementation of IMultipleResults.

Let’s look at the example for this.

Objective

To learn the multiple resultsets from database stored procedure and its implementation in C# with LINQTOSQL.

Description

Note: For these examples the sample database i.e. AdvntureWorks and SQL 2008 R2 is employed and a console project in VS2010 is used. For demonstration, the code for creating SQL connection, executing the inline SQL and stored procedure is also included in the class. Generally database constitutes a different component.

In this example, familiarity with LINQTOSQL, tool and DBML is assumed. Continuing the example with database AdventureWorks for earlier articles, we would stroll further.

The name “DataClassesDataContext” is given to auto-generated class for data context.

As seen above, the return type of this method is ISingleResult which in turn executes only one query. To get the multiple result sets, we need to modify the method. This can be done in other class with the same name. Of course such class needs to be partial.

Please do not forget to remove the original method once you program for new method in partial class.

Summary and Conclusion

The output clearly demonstrates the feature of multiple resultsets in SQL server stored procedure and its consequent handling in C# for LINQTOSQL. The code snippets for implementation of this and also for stored procedure are supplied above. With LINQTOSQL, the auto-generated code has method stubs with ISingleResult return type which would execute only one SQL statement/query in the stored procedure.

We saw how the method representing the stored procedure can be modified and decorated to support multiple results. This is little extra work needed to ensure the execution of multiple SQL statements as intended.

As stated earlier, the multiple resultsets can be really handy when database round trips can be avoided. This would often result into the performance gains. The number of multiple statements in embedded SQL and stored procedure better be kept minimal as having more number of SQL statements can be counterproductive.

Hope this article helps to understand the multiple resultsets and its handling in LINQTOSQL. This article ends the series on this topic and I hope all readers would have benefited out of this.