3. Create a Custom DataContext Class
If you'll be querying the database directly using LINQ or Lambda expressions you can use the built-in System.Data.Linq.DataContext class directly to run your queries and skip this step. However, if you also need to query a stored procedure defined in a mapping file then you'll want to create a custom class that derives from the DataContext class. This is necessary since you'll need to access the protected ExecuteMethodCall() method found in DataContext which can be used to call stored procedures. Here's the signature for the ExecuteMethodCall() method:
// // Summary: // Executes the stored database procedure or scalar function // associated with the specified CLR method. // // Parameters: // instance: // The instance of the method invocation (the current object). // // methodInfo: // Identifies CLR method that corresponds to database method. // // parameters: // The array of parameters to be passed to the command. // // Returns: // Result (the return value and output parameters) of // executing the specified method. protected internal IExecuteResult ExecuteMethodCall(object instance, MethodInfo methodInfo, params object[] parameters);
Creating a custom DataContext class is a bit of extra work, but I normally create a custom class that inherits from DataContext or my LINQ to SQL designer class if I'm using the Visual Studio 2008 designer anyway so that I can control things like logging and connection strings in a more centralized place. Here's a simple example of a custom class named CustomDataContext that derives from DataContext and exposes a GetCustomerByLastName() method that is used to call the ap_GetCustomerByLastName stored procedure shown previously. Notice that the method calls the DataContext class's ExecuteMethodCall() method and passes in the name of the method to call (contained within the System.Reflection.MethodInfo object) as well as the parameter data to pass to the stored procedure.
using System; using System.Collections.Generic; using System.Data.Linq; using System.Data.Linq.Mapping; using System.Reflection; namespace ConsoleApplication1 { public class CustomDataContext : DataContext { public CustomDataContext(string connStr, XmlMappingSource xmlMap) : base(connStr, xmlMap) { } public IEnumerable<DAL.Customer> GetCustomerByLastName(string lastNameLetter) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), lastNameLetter); return result.ReturnValue as IEnumerable<DAL.Customer>; } } }