Browse DevX
Sign up for e-mail newsletters from DevX


Take a Leap Forward with ADO.NET vNext (Part 1) : Page 3

ADO.NET vNext is a leap forward in database programming, using mapping files to isolate your applications from relational database schema changes, and letting you choose whether to deal with data directly as objects or as tabular data.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Programming Against the EDM
To be able to communicate with the EDM schema and execute queries against that schema, the ADO.NET team has introduced two new features:

  1. Mapping Provider. This is similar to the .NET SQL Client Provider except that it lets you talk to an EDM schema rather than directly to a database. The important classes in this layer are MapConnection and MapCommand. Based on the EDM schema and the mapping information, the mapping provider internally uses the mapping infrastructure to translate between the entity data model and the physical relational data model. By using the EDM model and the mapping provider your application no longer needs to use or understand database-specific constructs; the entire application operates in terms of the higher-level EDM model.
  2. Entity SQL. This is a data query/command language similar to SQL, except that it's oriented towards executing queries against the EDM schema rather than directly against a database schema. Entity SQL is designed to fully leverage the expressivity of EDM, letting you formulate queries statically formulated at design time or construct them dynamically at runtime. Because Entity SQL queries the EDM schema which is closely aligned with the objects and concepts in your application, queries often become much easier to write and understand as opposed to queries against a normalized database schema, which quickly become complicated in standard SQL—particularly when you have to write queries with multiple joins.
If you're familiar with traditional ADO.NET 2.0 data access code, you can easily migrate those applications to use the new mapping provider with couple of minor changes. Here's a skeleton implementation that shows the steps involved in communicating with the database using the familiar ADO.NET 2.0. The original code might look like this:

using (SqlConnection connection = new SqlConnection(...)) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "..."; using (IDataReader reader = command.ExecuteReader()) { // ... Process the records } } }

To modify the preceding code to use the new mapping provider, all you need to do is to make two changes: Change the SqlConnection to a MapConnection object and change the SqlCommand objects to MapCommand objects. Here's the altered code:

using (MapConnection connection = new MapConnection(...)) { using (MapCommand command = connection.CreateCommand()) { command.CommandText = "..."; using (IDataReader reader = command.ExecuteReader()) { // ... Process the records } } }

Now that you have a basic understanding of the mapping provider and entity SQL, here's a simple example that leverages both against the AdventureWorksModel EDM created earlier.

Executing Entity SQL Through the Mapping Provider
This first example executes a simple Entity SQL query against the AdventureWorksModel schema to retrieve the ProductSubcategory data. Add a new Button control named btnGetCategories and a ListBox (named lstCategories) to your project's default form, and modify the Click event code of the button as follows:

private void btnGetCategories_Click (object sender, EventArgs e) { using (MapConnection connection = new MapConnection("name=AdventureWorksConnectionString")) { connection.Open(); MapCommand command = connection.CreateCommand(); command.CommandText = "SELECT VALUE p FROM " + "AdventureWorksModel." + "AdventureWorks" + ".ProductSubcategory AS p "; DbDataReader reader = command.ExecuteReader( CommandBehavior.SequentialAccess); lstCategories.Items.Clear(); while (reader.Read()) { lstCategories.Items.Add (reader["ProductSubcategoryID"] .ToString() + " - " + reader["Name"]); } } }

The above code is simple and straightforward if you are familiar with ADO.NET programming model. First, you open the connection to the database, set the properties of the command object and finally invoke the ExecuteReader() method of the command object to return the results. Notice that the code uses the CommandBehavior.SequentialAccess enumeration value when it invokes the ExecuteReader() method. The enumeration value is required for executing queries through the mapping provider. Passing the CommandBehavior.SequentialAccess enumeration value tells the ADO.NET runtime to load the data sequentially as it is received in the form of a stream. The preceding code retrieves the connection string from the app.config file, where it is stored as follows:

<connectionStrings> <add name="AdventureWorksConnectionString" connectionString="metadata=.; provider=System.Data.SqlClient; provider connection string=&quot; Data Source=localhost; Initial Catalog=AdventureWorks; Integrated Security=True&quot;" providerName="System.Data.Mapping" /> </connectionStrings>

Now look at the Entity SQL used to query the EDM. The VALUE keyword allows you to retrieve all the values in a specific entity. In this example, it retrieves all the elements contained in the ProductSubcategory entity.

Figure 9. Form Output: Clicking the "Get Categories" button, retrieves all the categories and displays them in the list box.

command.CommandText = "SELECT VALUE p FROM " + "AdventureWorksModel.AdventureWorks.ProductSubcategory" + "AS p ";

Note that the FROM clause of the query is made up of three components arranged in the following sequence:

<Name of the Schema>.<Container>.<EntitySet>

After adding the code, run the form by pressing F5 and click on the "Get Categories" button and you should see output similar to Figure 9.

Passing Parameters to Entity SQL
Here's a slightly more complex example that demonstrates how to pass a parameter to an Entity SQL query to obtain the details of a specific product. To build this example, create a new form and place controls similar to those shown in Figure 10: a single-line Text control, a Button, and a ListBox. The button is named btnGetProductDetails; here's the code for its Click event:

Figure 10. Parameterized Query Output Results: When you specify a product ID and click on the "Get Product Details" button, you retrieve the details of the specific product and display them in the list box.

private void btnGetProductDetails_Click (object sender, EventArgs e) { using (MapConnection connection = new MapConnection("name=AdventureWorksConnectionString")) { connection.Open(); MapCommand command = connection.CreateCommand(); command.CommandText = "SELECT p.ProductID, p.Name, " + "p.ProductNumber FROM " + "AdventureWorksModel." + "AdventureWorks.Product AS p" + "WHERE p.ProductID = @ProductID"; command.Parameters.AddWithValue ("ProductID",Convert.ToInt32 (txtProductID.Text)); DbDataReader reader = command.ExecuteReader( CommandBehavior.SequentialAccess); lstProductDetails.Items.Clear(); while (reader.Read()) { lstProductDetails.Items.Add ("ID = " + reader["ProductID"]. ToString()); lstProductDetails.Items.Add ("Name = " + reader["Name"]); lstProductDetails.Items.Add ("Product Number = " + reader["ProductNumber"]); } } }

The preceding example passes the parameter to the Entity SQL by invoking the AddWithValue() method of the Parameters collection.

command.Parameters.AddWithValue ("ProductID",Convert.ToInt32 (txtProductID.Text));

Figure 10 shows the results.

Thanks for your registration, follow us on our social networks to keep up-to-date