Let's continue this series of posts on using LINQ To SQL for ADO.NET with a simple way to abstract LINQ To SQL behind a Database Gateway. Previous posts include:
You can read other LINQ To SQL Tutorials, too.
LINQ To SQL Database Class and Interface
Let's create a simple IDatabase Interface to abstract the various queries, commands, and stored procedures we need to access in the database:
public interface IDatabase
{
IEnumerable<T> ExecuteQuery<T>(string query,
params object[] parameters);
int ExecuteCommand(string command,
params object[] parameters);
IEnumerable<T> ExecuteStoredProcedure<T>(
string storedProcedureName,
params object[] parameters);
}
The Database Gateway for executing ADO.NET with LINQ To SQL is as follows:
public class Database : IDatabase
{
private readonly NorthwindDataContext _dc;
public Database() : this(new NorthwindDataContext()) {}
public Database(NorthwindDataContext dc)
{
_dc = dc;
}
public IEnumerable<T> ExecuteQuery<T>(string query,
params object[] parameters)
{
return _dc.ExecuteQuery<T>(query, parameters);
}
public int ExecuteCommand(string command,
params object[] parameters)
{
return _dc.ExecuteCommand(command, parameters);
}
public IEnumerable<T> ExecuteStoredProcedure<T>(
string storedProcedureName,
params object[] parameters)
{
var methodInfo = _dc.GetType().GetMethod
(storedProcedureName);
return (IEnumerable<T>)
methodInfo.Invoke(_dc, parameters);
}
}
You can use this LINQ To SQL Database Class to execute a stored procedure tossed on the LINQ To SQL Visual Designer as simple as:
var db = new Database();
var results = db.ExecuteStoredProcedure<SalesByCategoryResult>
("SalesByCategory", "Beverages", "1998");
foreach (var result in results)
Console.WriteLine("Product: {0}, Sales:{1:c}",
result.ProductName, result.TotalPurchase);
Conclusion
LINQ To SQL can be used for ADO.NET as well as an O/R Mapper. You can also abstract it pretty easily behind a Database Gateway to abstract out the use of LINQ To SQL.
David Hayden