Database Reference
In-Depth Information
To execute one or more SQL statements directly against the underlying Payment table, use the
ExecuteSqlCommand()
method available from the Database property from DbContext.class. Although we could query
the Payment entity in our model, the
ExecuteSqlCommand
enables us to query the underlying database table directly,
forgoing some Entity Framework features such as change tracking. We simply need a model object that inherently
contains a context object against which we execute ad hoc SQL commands.
Follow the pattern in Listing 3-4 to execute one or more SQL statements.
Listing 3-4.
Executing an Insert Statement
// delete previous test data
using (var context = new EFRecipesEntities())
{
context.Database.ExecuteSqlCommand("delete from chapter3.payment");
}
// insert two rows of data
using (var context = new EFRecipesEntities())
{
// note how using the following syntax with parameter place holders of @p0 and @p1
// automatically create the ADO.NET SqlParameters object for you
var sql = @"insert into Chapter3.Payment(Amount, Vendor) values (@p0, @p1)";
var rowCount = context.Database.ExecuteSqlCommand(sql, 99.97M, "Ace Plumbing");
rowCount += context.Database.ExecuteSqlCommand(sql, 43.83M, "Joe's Trash Service");
Console.WriteLine("{0} rows inserted", rowCount);
}
// retrieve and materialize data using (var context = new EFRecipesEntities())
{
Console.WriteLine("Payments");
Console.WriteLine("========");
foreach (var payment in context.Payments)
{
Console.WriteLine("Paid {0} to {1}", payment.Amount.ToString(),
payment.Vendor);
}
}
Following is the output of the code in Listing 3-4:
2 rows inserted
Payments
========
Paid $99.97 to Ace Plumbing
Paid $43.83 to Joe's Trash Service
How It Works
In Listing 3-4, we start by removing any previous test data. Notice how we use the
ExecuteSqlCommand
method from
the Database object from the DbContext object to execute this operation. Note how we feed a native SQL Delete
statement directly into the method.
