ProblemIn a recent tip on Querying SQL Server databases using LINQ to SQL, you have described how it can be used to query SQL Server databases. How do I use LINQ to SQL with stored procedures that retrieve database records?
SolutionWe use stored procedures to encapsulate business logic on the database server side. This gives us the advantage of modularity, performance, and security to name a few. We can use these stored procedures with LINQ to SQL. LINQ to SQL maps these database-defined abstractions to code-generated client objects, so that you can access them in a strongly typed manner from client code. This gives us the advantage of using Visual Studio's IntelliSense feature to expose them as methods together with the strongly-typed parameters which might be needed to call the stored procedures.
We'll start with the example project used in the LINQ to SQL tip to write LINQ query expressions that call stored procedures. Open the Northwind.dbml file by double-clicking it from the Solutions Explorer window.
This will open up the designer surface for you to drag-and-drop the stored procedures from the Server Explorerwindow. I'll use the SalesByCategory stored procedure from the Northwind database to retrieve a list of sales record based on the CategoryName and Year passed as parameters. If the stored procedure generates a list of classes based on what is already on the designer surface, you can drag-and-drop the stored procedure from the Server Explorerwindow to the class that will be generated. Let's say the stored procedure retrieves records from the Products table, you can drop the stored procedure on the Products class in the designer surface. This tells the designer that the resultset of the stored procedure is a list of Products classes. Otherwise, you can just drop it anywhere on the designer surface which will then be added to the DataContext class as a method.
Notice that the data types defined by the parameters accepted by the stored procedure are also strongly-typed. By default, the name of the method will be the same as the name of the stored procedure. If the stored procedure name contain spaces, the designer automatically converts them to the underscore ( _ ) character.
Calling the stored procedure using LINQ
Let's start writing some code inside the static void Main(string[] args).
//Obtaining the data source
var dbNorthwind =new NorthwindDataContext() ; // Create the query var query = dbNorthwind.SalesByCategory("Beverages","1997"); // Execute the query foreach (var c in query) { Console.WriteLine(c.ProductName + "," + c.TotalPurchase); } Console.ReadLine(); |
Visual Studio's IntelliSense feature now exposes the DataContext class' new method together with the parameters with their corresponding data types.
Even the results returned by the method are strongly typed
Running the application with logging will look like this when you run your project in Visual Studio. You can press F5 or click on Debug - Start Debugging in Visual Studio.
There is a limitation on calling stored procedures using LINQ to SQL. The designer does not generate code for stored procedures that use dynamic SQL statements to return a result set or those that depend on temporary tables to return a result. This is because LINQ to SQL cannot acquire the metadata for the resultset during design time. But it doesn't mean it is not possible. It just means that you will not be able to use the IntelliSense feature to retrieve the strongly-typed resultsets based on the metadata returned by the stored procedure.
Next Steps
You have seen how LINQ to SQL supports the ability to call SQL Server stored procedures and call them as methods of the DataContext class
- Start using stored procedures with LINQ to SQL.
- Learn more about LINQ to SQL
Chamundi Hills can be viewed from a distance of about 8 to 10 kms and from all the corners of Mysore City. Chamundeshwari temple is situated on the top of Chamundi hill which is about 3,489 ft. above sea level and located at a distance of 13 kms. from Mysore. The temple is dedicated to Sri Chamundeshwari, the titular deity of the Mysore Royal Family also described as ‘Mahishasura Mardini’for having killed the buffalo headed demon Mahishasura. The temple has a very beautiful idol of the goddess wearing a garland of skulls. Goddess Chamundeshwari, also spelled Chamundeswari is an incarnation of Goddess Durga, Goddess Kali is also referred to as the Goddess Chamundi.
The Chamundi temple has always been patronised by the rulers of Mysore. In 1659 Dodda Devaraja Wodeyar built 1,000 steps and huge Nandi, Lord Shiva's Bull. This colossal Nandi is one of the largest in India, 16 ft. (4.8 meters) tall at the front and 25 ft. (7.5 meters) in length. The magnificent pendent bells around its neck are exquisite. Nandi and the temple beside it are located at the 700th step of the Chamundi Hill. Krishnaraja Wodeyar III repaired the shrine in 1827 and built the present beautiful forty-meter, seven storied Gopura (tower at the entrance) with gold finials, and set up statues of his and his three queens. In 1827, Krishnaraja Wodeyar III made arrangements for festivals and processions and gifted Simhavahana in 1843.
