Execute Stored Procedure and Function from LINQ
By : Kasim Wirama, MCDBA, MVP SQL Server
In this article I would like to show you how to call stored procedure, scalar function, and table value function from LINQ.
For these three kinds database object, they are hosted under DataContext derived class with attribute Function, to differentiate between function and stored procedure, there is a property of the attribute, called IsComposable. If IsComposable is true, then the method will consider calling function in database, otherwise it will consider to call stored procedure.
For return type of them, returning ISingleResult<Classname> is intended for stored procedure, System.Nullable<type> return type is intended for scalar function and IQueryable<Classname> return type is intended for table value function.
Stored procedure
Sample method :
[Function(Name="[dbo].[Customers By City]")]
public ISingleResult<Customer> CustomerByCity(string param1)
{
return (ISingleResult<Customer>)(this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), param1).ReturnValue);
}
Scalar function
Sample method :
[Function(Name="dbo.MinUnitPriceByCategory",IsComposable=true)]
public System.Nullable<decimal> MinUnitPriceByCategory(int categoryID)
{
System.Nullable<decimal> result = (System.Nullable<decimal>)this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), categoryID).ReturnValue;
return result;
}
Table value function
Sample method :
[Function(Name = "[dbo].[GetCustomerInfo]",IsComposable=true)]
public IQueryable<Customer> GetCustomerInfo(string param1)
{
return (IQueryable<Customer>)(this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), param1).ReturnValue);
}
You can also see generated code from Visual Studio by add new item, then select LINQ to SQL classes, then in the new designer window you drag stored procedure or function into. You can see generated code in file <filename>.designer.cs, or you can also generate csharp file from SQLMetal command line utility.
In main program, you can call all these methods by code below :
class Class1{ public static void Main() { SampleDb sampleDB = new SampleDb(new SqlConnection(your connection string)); foreach (var row in sampleDB.CustomerByCity("Seattle")) { Console.WriteLine(row.CustomerID + " - " + row.CompanyName); } Console.WriteLine(); var query = sampleDB.MinUnitPriceByCategory(1); Console.WriteLine(query); Console.WriteLine(); foreach(var row in sampleDB.GetCustomerInfo("ALFKI")) { Console.WriteLine(row.CompanyName + " - " + row.City); } Console.ReadLine(); }}
SampleDB class is derived from DataContext base class.