Database Reference
In-Depth Information
Then we create a string containing a SQL Insert statement. This statement contains two parameters:
@Amount
and
@Vendor
. These are placeholders that will be replaced by values when the statement is executed.
Next we create two DbParameter parameter objects, which bind the placeholder names to specific values. For the
first insert, we bind the value 99.97 to the Amount placeholder along with “Ace Plumbing” to the Vendor placeholder.
We then create another vendor record. Notice how both vendors are assigned to an array of type DbParameter. To
execute the SQL statement, we pass both the string containing the SQL statement and the array of DbParameter
objects to the
ExecuteSqlCommand()
method.
ExecuteSqlCommand()
returns the count of rows affected by the
statement. In our case, one row is inserted each time we call
ExecuteSqlCommand()
.
If you don't have any parameters for a SQL statement, there is an overload of the
ExecuteSqlCommand()
method
with a single parameters that expects only a SQL statement.
•
The pattern in Listing 3-4 is similar to how we would query data leveraging the Microsoft ADO.
NET framework with the SqlClient object. The difference is that we don't need to construct a
connection string and explicitly open a connection. The underlying Entity Framework context
object automatically performs this work. Note that there are two versions of the context object
in Entity Framework: The DbContext object in Entity Framework versions 5, 6, and the 4.x
Code-First approach.
•
The ObjectContext in earlier versions of Entity Framework.
Keep in mind as well that the DbContext is simply a wrapper, or “Façade,” which wraps the legacy ObjectContext,
making the context object significantly more intuitive and easy to use. All functionality from the underlying
ObjectContext is still available.
The way we express the command text and the parameters is also different. With the ADO.NET
ExecuteNonQuery() method
, the command text and parameters are set on the underlying Command object. Here,
these are passed into the
ExecuteSqlCommand()
method as simple arguments.
Of course, the observant reader will notice here (and this is important) that we're really not querying the model.
In fact, as we mentioned, you don't need to have the Payment entity shown in Figure
3-3
. The
ExecuteSqlCommand()
method simply uses the object's DbContext for its connection to the underlying data store.
Best Practice
To parameterize or not to parameterize, that is the question . . . Okay, Shakespeare aside, should you use parameters
for SQL statements or just create the SQL statement strings dynamically? The best practice is to use parameters
whenever possible. Here are some reasons why:
•
Parameterized SQL statements help prevent SQL Injection attacks. If you construct a complete
SQL statement as a string by dynamically appending together strings that you get from a
user interface, such as an ASP.NET TextBox control, you may end up inadvertently exposing
yourself to injected SQL statements that can significantly damage your database and reveal
sensitive information. When you use parameterized SQL statements, the parameters are
handled in a way that prevents this.
•
Parameterized SQL statements, as we have shown in this recipe, allow you to reuse parts of the
statement. This reuse can make your code more simple and easier to read.
•
Following the re-use idea, most enterprise-class databases like Oracle Database, IBM DB2,
and even Microsoft SQL Server in some circumstances, can take advantage of parameterized
queries by reusing the parsed version of the query even if the parameters have changed. This
boosts performance and lowers the processing overhead for SQL statement re-use.
•
Parameterized SQL statements make your code more maintainable and configurable. For
example, the statements could come from a configuration file. This would allow you to make
some changes to the application without changing the code.
