Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
Downloads > Code Samples & Utilities > Tutorial: Using the ADO.NET Entity Framework with ...

Tutorial: Using the ADO.NET Entity Framework with SQL Anywhere
 
RSS Feed
 
 
 

SQL Anywhere and the ADO.NET Entity Framework

Building on SQL Anywhere's rich tradition of supporting Microsoft data access standards, SQL Anywhere includes support for the ADO.NET Entity Framework, an object-relational mapping technology. SQL Anywhere support is also provided in Visual Studio 2008 and 2010, allowing programmers to remain within their Visual Studio environment when developing SQL Anywhere and UltraLite applications.

SQL Anywhere ADO.NET Entity Framework Tutorial

This tutorial demonstrates SQL Anywhere's capability to retrieve data from an ADO.NET Entity Data Model (EDM) using the following methods: binding controls to data source objects, EntityClient provider, object services, and LINQ to entities. The programming language used for this tutorial is C#; however, a VB.NET version is also provided in the source code download link below.

Requirements

SQL Anywhere .NET Integration Tools

The SQL Anywhere setup program automatically installs the .NET integration components to your Visual Studio 2010 installation. However, if you install Visual Studio 2010 after installing SQL Anywhere, you must install the SQL Anywhere integration tools:

  • Ensure Visual Studio is not running.
  • Open a Command Prompt and navigate to the following directory:
  • C:\Program Files\SQL Anywhere 12\Assembly\v2

  • Run the following command:

          SetupVSPackage.exe /i

If you want to un-install the integration tools:

  • Run the following command at the same directory:

          SetupVSPackage.exe /u

Create an Entity Data Model for the SQL Anywhere Demo Database

  1. Start Visual Studio 2008 and create a new Visual C# Windows Form Application. Name the project SQLAnyEDM.
  2. Add a new item of type ADO.NET Entity Data Model and name this file SADemo.edmx.

    New Item

  3. The Entity Data Model Wizard appears. Choose Generate from database and click Next.
  4. You will create a new data connection or SQL Anywhere. Click the New Connection button.
  5. In the Connection Properties dialog, click the Change button.
  6. Select SQL Anywhere from the list and click OK.

    Change Data Source

  7. Choose ODBC Data Source name and select SQL Anywhere 12 Demo from the list.

    Add the Sample Database

    Test the connection if desired. Click OK to close the dialog.
  8. Back at the EDM Wizard, notice the connection string uses the SQL Anywhere .NET data provider. Change the connection settings to SADemoEntities and click Next.

    Configure the Connection String

  9. Include all database objects in the model. Click Finish to complete the wizard.

          Note: If you are using Microsoft .NET Framework 4.0, clear the Pluralize or singularize generated object names checkbox and click Finish.

Add database objects

You now have an entity data model for the SQL Anywhere Demo database. You can view a visual representation of this model by double-clicking on the file SADemo.edmx.

Create a Data Source from the SADemo Entity Data Model

This tutorial uses a data grid to display customer information. The data grid is binded to a data source generated from the EDM.

  1. From the Data menu, select Show Data Sources.
  2. In the Data Sources window, click on Add New Data Source. The Data Source Configuration Wizard appears.
  3. Select Object and click Next.
  4. Expand the EDM and select Customers. Click Next.

    Customers

  5. Click Finish to complete the wizard.

Display Database Records in Grid View

One way to retrieve and view the data is to bind a data source object to a control, such as a grid view.

  1. Drag the Customers object from the Data Sources window and drop it into the main form, Form1. This creates DataGridView and BindingNavigator controls that will be binded to the Customers object.
  2. Let's add the necessary C# code to retrieve the customers' information from the database. Double-click on Form1 to open the code editor.
  3. In the Load event for the form, enter the following code:

    // Create an ObjectContext instance based on SADemoEntities
    var saEntities = new SQLAnyEDM.SADemoEntities();

    // Retrieve the list of customers from the entity
    var customers = saEntities.Customers;

    // Bind the customers object to the data grid
    customersBindingSource.DataSource = customers;

  4. Run the project by pressing F5. Notice that the form now contains the customers' information.

    Form Customers

  5. Close the application.
  6. Let's add a few more controls to display more information. Add command buttons and list box controls so that the form looks as follows:

edm_MainForm.PNG

The user interface for this tutorial application is now completed. The next steps is to add code to the command buttons to retrieve additional information from the SQL Anywhere database.

Retrieve Data Using EntityClient Provider

Another method to retrieve data is to use the EntityClient provider. The EDM is queried using Entity SQL.

  1. Double-click the Get Products button to open the code editor.
  2. In the Click event for the button, enter the following code:

    var saEntities = new SQLAnyEDM.SADemoEntities();

    // Query EDM using the EntityClient provider and Entity SQL
    var saConn = new System.Data.EntityClient.EntityConnection("Name=SADemoEntities");
    var saCmd = new System.Data.EntityClient.EntityCommand(
        @"select distinct p.Name
          from SADemoEntities.Products as p", saConn);

    saConn.Open();
    var saReader =
        saCmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

    // Loop through result set & add product name to the list box
    while (saReader.Read())
    {
        listBox1.Items.Add(saReader.GetString(0));
    } // while

  3. Notice the Entity SQL query. It returns a unique list of the products in the database.
  4. Run the product and click on the Get Products button to retrieve the information.

Get Products

  1. Close the application.

The EntityClient provider simply runs the query against the demo EDM. Without using the EntityFramework, the query must be executed against a SQL Anywhere connection object.

Retrieve Data Using Object Services

You can also retrieve data using Object Services. Again, this method uses Entity SQL.

  1. Double-click the Get Contacts button to open the code editor.
  2. In the Click event for the button, enter the following code:

    var saEntities = new SQLAnyEDM.SADemoEntities();

    // Query EDM using the Object Services
    var contacts = saEntities.CreateQuery<System.Data.Common.DbDataRecord>(
        @"select c.GivenName, c.Surname
          from SADemoEntities.Contacts as c");

    // Add the contact's first and last names to the list box
    foreach (var record in contacts)
    {
        listBox2.Items.Add(
            record.GetString(0) + " " + record.GetString(1));
    } // foreach

  3. Run the product and click on the Get Contacts button to retrieve the information.

    Get Contacts

  4. Close the application.

This time we use Object Services and Entity SQL to query the demo EDM and then loop through the result set to parse the returned data as appropriate.

Retrieve Data Using LINQ to Entities

The last method to retrieve data uses LINQ to entities.

  1. Double-click the Get Central Orders button to open the code editor.
  2. In the Click event for the button, enter the following code:

    var saEntities = new SQLAnyEDM.SADemoEntities();

    // Query EDM using LINQ to entities
    var orders = from o in saEntities.SalesOrders
                 where o.Region == "Central"
                 select o;

    // Add the sales order's number and date to the list box
    foreach (var so in orders)
    {
        listBox3.Items.Add(
            "Order #" + so.ID + " placed on " + so.OrderDate);
    } // foreach

  3. Run the product and click on the Get Central Orders button to retrieve the information.

    Get Central Orders

  4. Close the application.

The list of orders is retrieved using LINQ to entities, which simplifies the query building process because you can take advantage of Visual Studio's IntelliSense functionality to drill down and see the different properties of the demo EDM.

Conclusion

SQL Anywhere enables developers to leverage their .NET programming skills and build powerful database applications. By adding support for the .NET Entity Framework, developers can quickly generate entity data models that are based on new or existing SQL Anywhere databases. The information can be retrieved from the EDM using a number of methods, including data binding to controls, the EntityClient provider, Object Services, or LINQ to entities.


 

SQL Anywhere

The industry-leading mobile and embedded database that provides data management and synchronization technologies for applications outside the data center.
Related Links

DOCUMENT ATTRIBUTES
Last Revised: Jul 19, 2010
Product: SQL Anywhere
Technical Topics: Connectivity, SQL Anywhere, Microsoft.NET
  
Business or Technical: Technical
Content Id: 1056609
Infotype: Download
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics