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
 
 
 
 
Support > Technical Documents > Document Types > White Paper-Technical > SQL Anywhere and the ADO.NET Entity Framework

SQL Anywhere and the ADO.NET Entity Framework
 
RSS Feed
 
 
 

Introduction

This whitepaper describes how database application developers can use SQL Anywhere 12 and the ADO.NET Entity Framework to build database-driven applications. This whitepaper also describes how the Language Integrated Query (LINQ) to Entities, Object Services, and the EntityClient Provider methodologies can be used to access data stored inside a SQL Anywhere database.

The ADO.NET Entity Framework is a new technology from Microsoft that simplifies the development of data aware applications and reduces the amount of code database application developers must write. Typically, databases are designed by database administrators who ensure that all information is stored in schemas optimized for data retrieval. Although these schemas provide efficient access to data, they are not the best method of representing the business objects required by applications. Often, programmers must design and implement methods for transforming the data stored in relational systems into robust and reusable objects that are integral parts of enterprise solutions. The ADO.NET Entity Framework provides an intuitive approach to designing business logic based on relational data.

The ADO.NET Entity Framework introduces the Entity Data Model (EDM) which is a conceptual view of the data used in the application. Often, existing database schemas contain large amounts of data, and only a subset of this data is required by client programs. For example, a Microsoft Windows client application that processes online orders may only require information about customers, products, and orders. Instead of including all the database tables in the EDM, the developer provides only the tables that are relevant to the application.

Download paper

Download PDF Button -   41x45px

Required Software

SQL Anywhere .NET Integration Tools

The SQL Anywhere setup program automatically installs the .NET integration components to your Visual Studio 2008 installation. However, if you install Visual Studio 2008 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 from a SQL Anywhere Database

A SQL Anywhere database can also be used to create a new entity data model defined in Visual Studio 2008. Follow the steps below to add the SQL Anywhere 12 Demo database as a EDM to your project.

  1. Right-click your project,  click Add New Item > ADO.NET Entity Data Model.
       
         ** If the ADO.NET Entity Data Model does not show up, verify Visual Studio 2008 Service Pack 1 and Microsoft .NET Framework 3.5 with Service Pack 1 are installed properly.

  2. In the Name field, type SADemo.edmx. Click Add

                        Add a new EDM

  1. Select Generate from database and click Next.
  2. Skip step 4 - 6 if the default connection is SQL Anywhere.demo12. Otherwise, click New Connection.
  3. In the Data source list, click SQL Anywhere. Click Continue.

        ** If SQL Anywhere does not appear in the Data source list, verify the SQL Anywhere integration components are installed properly.

  4. Click ODBC Data Source name and select SQL Anywhere 12 Demo. Click OK.
  5. Click Next.
  6. Include all database objects in the model and click Finish.

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

View the Entity Data Model in the Entity Designer

When you create an entity data model, a visual representation of the model appears in the Entity Designer. In the diagram below, the generated properties and associations match the database schema.

View the Entity Designer

 

This diagram illustrates that the entity is properly mapped to the database schema.

View table schema mapping.PNG

Bind Data to Controls

Use the following procedure to add a single DataGridView control to display the information stored in the SQL Anywhere database.

LINQ to Entities are used in step 10 of this procedure to allow the use of the IntelliSense (auto completion) feature of Visual Studio. Through the use of tool tips and IntelliSense, Visual Studio can help programmers detect and avoid application runtime errors.

  1. On the Data menu, click Show Data Sources.
  2. Click Add New Data Source.
  3. On the Data Source Type list, click Object. Click Next.
  4. Expand the Model list and click Customers. Click Next.
  5. Click Finish.
  6. In the Data Sources window, drag the Customer object to your main form. The DataGridView and BindingControlNavigator controls that display the records stored in the database are generated.
  7. In the Load event pane of the main form, add the following code to bind the Customer object to the data grid:

    [C#]

    var saEntities = new SA12EntityFramework.Entities();
    // Retrieve the list of customers from the EDM
    var customers = saEntities.Customers;
    // Bind the customers object to the data grid
    customersBindingSource.DataSource = customers;


    [VB]

    Dim saEntities As New SA12EntityFrameworkVB.Entities()
    // Retrieve the list of customers from the EDM
    Dim customers = saEntities.Customers
    // Bind the customers object to the data grid
    CustomersDataGridView.DataSource = customers

  8. Run the project to view the list of customers in the data grid.
  9. Close the sample application.
  10. In the Load event pane of the main form, add the following code to display only Canadian customers in the data grid:

    [C#]

    var saEntities = new SA12EntityFramework.Entities();
    // Retrieve the list of customers from the EDM
    var customers = from c in saEntities.Customers
                           where c.Country == "Canada" select c;
    // Bind the customers object to the data grid
    customersBindingSource.DataSource = customers;


    [VB]

    Dim saEntities As New SA12EntityFrameworkVB.Entities()
    // Retrieve the list of customers from the EDM
    Dim customers = From c in saEntities.Customers _
                             Where c.Country Is "Canada" _
                             Select c
    // Bind the customers object to the data grid
    CustomersDataGridView.DataSource = customers

  11. Run the project and verify that only Canadian customers appear.
  12. Close the sample application.

Extend the Entity Data Model

The code generated for the Entity Data Model indicates that it is a partial class entity. Database application developers can extend a partial class entity to include properties and methods that complete business objects.

Use the following procedure to add new class files with two methods of retrieving information to the sample application. This procedure assumes that the developer cannot change the database schema. So, the logic is added by extending the Entity Data Model.

  1. Add a new class file to the project and change the code to the following:

    [C#]

    namespace SA12EntityFramework
    {
         public partial class Entities
         {
             public List<string> AvailableColours()
             {
                 var colours = (from Products p in this.Products
                                    orderby p.Color
                                    select p.Color).Distinct();
                 return colours.ToList();
             }
             public List<Products> GetColourProducts(string colour)
            {
                 var prods = from Products p in this.Products
                                  where p.Color == colour
                                  select p;
                 return prods.ToList();
             }
         }
    }

    [VB]

    Partial Public Class Entities

         Public Function AvailableColours() As List(Of String)
             Dim colours As IQueryable(Of String)
             colours = (From Products In Me.Products _
                           Order By Products.Color _
                           Select Products.Color).Distinct()
             Return colours.ToList()
         End Function

         Public Function GetColourProducts(ByVal colour As String) _
         As List(Of Products)
             Dim prods As IQueryable(Of Products)
             prods = From Products In Me.Products _
                         Where Products.Color Is colour _
                         Select Products
             Return prods.ToList()
         End Function

    End Class

  2. In the pane below the data grid, add a combo box, a list box, and a Get Products button to the main form. These controls display the results of the two methods added in step 1.
  3. In the Load event pane of the main form, add the following code:

    [C#]

    var colours = saEntities.AvailableColours();
    // Add each colour to combo box
    foreach (var c in colours)
    {
         comboBox1.Items.Add(c.ToString());
    } // foreach


    [VB]

    Dim colours = saEntities.AvailableColours()
    'Add each colour to combo box
    For Each (Dim c In colours)
         ComboBox1.Items.Add(c.ToString())
    Next

  4. Add the following code to the button click event:

    [C#]

    listBox1.Items.Clear();
    string colour = comboBox1.SelectedItem.ToString();
    var saEntities = new SA12EntityFramework.Entities();
    var available = saEntities.GetColourProducts(colour);
    // Add product to list box
    foreach (var a in available)
    {
         listBox1.Items.Add(a.Name.ToString());
    } // foreach


    [VB#]

    ListBox1.Items.Clear()
    Dim colour = ComboBox1.SelectedItem.ToString()
    Dim saEntities As New SA12EntityFrameworkVB.Entities
    Dim available = saEntities.GetColourProducts(colour)
    ' Add product to list box
    For Each a In available
         ListBox1.Items.Add(a.Name.ToString())
    Next

  5. Run the project. The AvailableColours method retrieves a unique list of the colours in the Products table and populates the combo box. The GetColourProducts method retrieves a list of the Products entity type that matches the LINQ query.

Other Data Access Methods

In addition to LINQ to Entities, developers can use Object Services and the EntityClient Provider to access SQL Anywhere data.

Object Services:

For this method, Entity SQL is used to query the Entity Data Model. Using Object Services minimizes the amount of code required to write data access modules, and results in an object whose contents can easily be retrieved using a "for each" loop. For example, drag another Listbox to the form and replace the lines in the From1_Load event with the following:

[C#]

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

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

[VB]

     Dim saEntities As New SA12EntityFrameworkVB.Entities

     'Query EDM using Object Services

     Dim contacts = saEntities.CreateQuery(Of System.Data.Common.DbDataRecord) ("select c.GivenName, c.Surname from Entities.Contacts as c")

   'Add the contact's first name and last name to the list box
     For Each record In contacts
          ListBox2.Items.Add(record.GetString(0) + " " + record.GetString(1))
     Next

EntityClient Provider

For this method, Entity SQL is used in a manner similar to the method used to access data with the ADO.NET provider. Before the development of the Entity Framework, programmers developed SQL Anywhere applications with ADO.NET with connection, command, and data reader objects. The EntityClient Provider offers a similar way of accessing data, but the Entity Data Model is queried instead of the database. When using the EntityClient Provider, programmers can use connections and commands in the form of "EntityConnections" and "EntityCommands". For example, drag another Listbox to the form and replace the lines in the From1_Load event with the following:

[C#]

     var saEntities = new SA12EntityFramework.Entities();

    // Query EDM using the EntityClient provider and Entiry SQL
     var saConn = new System.Data.EntityClient.EntityConnection("Name=Entities");
     var saCmd = new System.Data.EntityClient.EntityCommand(@"select distinct p.Name from Entities.Products as p", saConn);
     saConn.Open();
     var saReader = saCmd.ExecuteReader( System.Data.CommandBehavior.SequentialAccess);
     // Loop through result set and add product name to the list box
     while (saReader.Read())
     {
         listBox3.Items.Add(saReader.GetString(0));
     } // while


[VB]

     Dim saEntities As New SA12EntityFrameworkVB.Entities

     'Query EDM using the EntityClient provider and Entity SQL
     Dim saConn As New System.Data.EntityClient.EntityConnection("Name=Entities")
     Dim saCmd = New System.Data.EntityClient.EntityCommand( _
                         "select distinct p.Name from Entities.Products as p", saConn)
     saConn.Open()
     Dim saReader = saCmd.ExecuteReader (System.Data.CommandBehavior.SequentialAccess)
     ' Loop through result set and add product name to the list box
     While saReader.Read()
         ListBox3.Items.Add(saReader.GetString(0))
     End While

Summary

Database application developers can use SQL Anywhere 12 to take advantage of the ADO.NET Entity Framework technology. The SQL Anywhere integration components for Visual Studio can be used to generate Entity Data Models from a SQL Anywhere database and manipulate records. Records can be manipulated with control data binding, Object Services, the EntityClient provider, and LINQ to Entities. Database application developers can extend the generated Entity Data Model to implement the robust business logic required by today's enterprise solutions.

 


 

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: Nov 03, 2008
Product: SQL Anywhere
Technical Topics: Connectivity, SQL Anywhere, Microsoft.NET, SQL ISV Applications, SQL Server Applications
  
Business or Technical: Technical
Content Id: 1060541
Infotype: White Paper-Technical
 
 
 

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