LINQ To SQL Samples - Stored Procedures |
This sample uses a stored procedure to return the number of Customers in the 'WA' Region.
Public Sub LinqToSqlStoredProc01()
Dim count = db.Customers_Count_By_Region("WA")
Console.WriteLine(count)
End Sub
Result:
3
This sample uses a method mapped to the 'Customers By City' stored procedure in Northwind database to return customers from 'London'. Methods can be created by dragging stored procedures from the Server Explorer onto the O/R Designer which can be accessed by double-clicking on .DBML file in the Solution Explorer.
Public Sub LinqToSqlStoredProc02()
Dim q As ISingleResult(Of Customers_By_City) = db.Customers_By_City("London")
ObjectDumper.Write(q, 0)
End Sub
Result:
CustomerID=AROUT ContactName=Thomas Hardy CompanyName=Around the Horn City=London
CustomerID=BSBEV ContactName=Victoria Ashworth CompanyName=B's Beverages City=London
CustomerID=CONSH ContactName=Elizabeth Brown CompanyName=Consolidated Holdings City=London
CustomerID=EASTC ContactName=Ann Devon CompanyName=Eastern Connection City=London
CustomerID=NORTS ContactName=Simon Crowther CompanyName=North/South City=London
CustomerID=SEVES ContactName=Hari Kumar CompanyName=Seven Seas Imports City=London
This sample uses a stored procedure to return the Customer 'SEVES' and all its Orders.
Public Sub LinqToSqlStoredProc04()
Dim result = db.Get_Customer_And_Orders("SEVES")
Console.WriteLine("********** Customer Result-set ***********")
Dim customer As IEnumerable(Of Get_Customer_And_OrdersResult) = result
ObjectDumper.Write(customer)
Console.WriteLine()
End Sub
Result:
********** Customer Result-set ***********
CustomerID=SEVES CompanyName=Seven Seas Imports ContactName=Hari Kumar ContactTitle=Sales Manager Address=90 Wadhurst Rd. City=London Region=null PostalCode=OX15 4NB Country=UK Phone=(171) 555-1717 Fax=(171) 555-5646
This sample uses a stored procedure that returns an out parameter.
Public Sub LinqToSqlStoredProc05()
Dim totalSales? = 0@
Dim customerID = "ALFKI"
' Out parameters are passed by ByRef, to support scenarios where
' the parameter is In or Out. In this case, the parameter is only
' out.
db.CustOrderTotal(customerID, totalSales)
Console.WriteLine("Total Sales for Customer '{0}' = {1:C}", customerID, totalSales)
End Sub
Result:
Total Sales for Customer 'ALFKI' = $4,273.00
This sample uses a method mapped to the 'ProductsUnderThisUnitPrice' function in Northwind database to return products with unit price less than $10.00. Methods can be created by dragging database functions from the Server Explorer onto the O/R Designer which can be accessed by double-clicking on .DBML file in the Solution Explorer.
Public Sub LinqToSqlStoredProc06()
Dim q = db.ProductsUnderThisUnitPrice(10.0)
ObjectDumper.Write(q, 0)
End Sub
Result:
ProductID=13 ProductName=Konbu SupplierID=6 CategoryID=8 QuantityPerUnit=2 kg box UnitPrice=7.0000 UnitsInStock=24 UnitsOnOrder=0 ReorderLevel=5 Discontinued=False
ProductID=24 ProductName=Guaraná Fantástica SupplierID=10 CategoryID=1 QuantityPerUnit=12 - 355 ml cans UnitPrice=5.5000 UnitsInStock=20 UnitsOnOrder=0 ReorderLevel=0 Discontinued=True
ProductID=33 ProductName=Geitost SupplierID=15 CategoryID=4 QuantityPerUnit=500 g UnitPrice=3.5000 UnitsInStock=112 UnitsOnOrder=0 ReorderLevel=20 Discontinued=False
ProductID=52 ProductName=Filo Mix SupplierID=24 CategoryID=5 QuantityPerUnit=16 - 2 kg boxes UnitPrice=8.0000 UnitsInStock=38 UnitsOnOrder=0 ReorderLevel=25 Discontinued=False
ProductID=54 ProductName=Tourtière SupplierID=25 CategoryID=6 QuantityPerUnit=16 pies UnitPrice=8.4500 UnitsInStock=21 UnitsOnOrder=0 ReorderLevel=10 Discontinued=False
ProductID=75 ProductName=Rhönbräu Klosterbier SupplierID=12 CategoryID=1 QuantityPerUnit=24 - 0.5 l bottles UnitPrice=8.7500 UnitsInStock=125 UnitsOnOrder=0 ReorderLevel=25 Discontinued=False
This sample queries against a collection of products returned by 'ProductsUnderThisUnitPrice' method. The method was created from the database function 'ProductsUnderThisUnitPrice' in Northwind database.
Public Sub LinqToSqlStoredProc07()
Dim q = From p In db.ProductsUnderThisUnitPrice(10.0) _
Where p.Discontinued = True _
Select p
ObjectDumper.Write(q, 0)
End Sub
Result:
ProductID=24 ProductName=Guaraná Fantástica SupplierID=10 CategoryID=1 QuantityPerUnit=12 - 355 ml cans UnitPrice=5.5000 UnitsInStock=20 UnitsOnOrder=0 ReorderLevel=0 Discontinued=True
