---

Download

AdventureWorks2008 .NET Samples for SQL Anywhere 11 and Visual Studio 2008 SP1

---

Introduction

Using the ‘AdventureWorks2008 Sample Database for SQL Anywhere’, and leveraging Microsoft .NET 3.5 technologies, the following samples are intended to demonstrate the key features of SQL Anywhere. Specifically, the points of .NET integration and support are highlighted. The samples were all developed in Microsoft’s Visual Studio, and are freely distributed in their source-code format. The data and information provided are fictitious, but are meant to simulate real-world business solutions.

Overview

Adventure Works Cycling is a medium-size company specializing in the manufacturing and resale of cycling products and parts. To support their business functions, their IT group has developed uniform solutions using SQL Anywhere. While the Human Resources, Production, Purchasing, and Sales groups are all distinct, there is vital communication between them as one group’s activities affect others. For example, a sales order may necessitate production to manufacture more parts or for purchasing to order a custom component.

Typically, a member of the sales team will visit with a client to come up with an appropriate sales order. In this case, Michael Blythe, a sales representative, is meeting with his client to enter a new order. Though Michael will be physically away from the corporate headquarters, he will have his laptop on-site onto which he can enter the order. After he finishes visiting one client, he checks his map viewer to locate the customers near his current position in order to make appropriate travel plans. Once the order is received by headquarters, Susan Eaton, a stocker from the production group, will need to check that the ordered in-house parts and products are in stock, and begin production for any missing items. To complicate matters, the production group is housed off-site. Meanwhile, Sheela Word, a purchasing manager, will need to check that the ordered custom parts and products are in stock, and order any missing items. For landing such a lucrative deal, Michael’s manager has approved a pay raise, though HR needs to look after this. Paula Barreto de Mattos, a human resources manager, will need to enter this information into their systems.

It is IT’s responsibility to support all of these business functions, and make the experience easy for non-technical staff. As with all IT groups, they don’t have huge resources or time to spend on the development of custom technologies. Furthermore, each group has their own demands on what the systems should look like. The sales group needs support for occasionally connected employees as well as operations using geographic data, HR needs to meet privacy standards, and the production group wants to have all data accessible via the internet since they are located off-site. Using SQL Anywhere 12 to leverage Microsoft’s .NET development platform, all of these business functions and needs can easily be supported without spending huge resources on development.

Adventure Works Business Logic (CLR External Environment)

As with most companies, much of Adventure Work’s rules of practice are embedded into their software in many different places. For IT, the question remains: how do we keep our business logic consistent in all places? An easy-to-maintain solution that still offers robust programming ability is required. Microsoft .NET makes business logic programming easy and adaptable. Logic can be written into a shared library in any .NET language, and then reused throughout all applications uniformly.

Thanks to SQL Anywhere’s CLR external environment, developers can embed their .NET logic into the database. Developers can define and implement validation or custom business procedures for handling data in .NET and use them in the database. Keeping the database up-to-date is as simple as updating the shared library that the database uses.

This sample uses a shared library (DLL) written in C# to calculate sales tax and shipping cost. The AdventureWorks2008 database is then instructed to use the library.

Please refer to the online documentation for more information about the SQL Anywhere CLR External Environment　(PDF P.719).

Sales: A Disconnected Application (ADO.NET, Mobile Link, DBMLSync .NET)

Michael Blythe visits with his clients on-site, and needs to be able to enter sales orders offline. This means he will need certain data from the sales database about products and the client with him, even when he can’t be connected to the corporate network. In order to avoid reduplicating effort, the sales group wants orders entered offline to be synched back up to headquarters easily. It would be a waste of time to have to re-enter the order when Michael gets back to the office.

Thanks to Mobile Link synchronization technology, Michael need only keep a subset of the sales database with him; just the list of products and some information about his clients. He can enter the order on his laptop, just like he would if he were in the office. When Michael re-connects to the network, he can synchronize his data store with the click of a button, and automatically alert the other groups of this new order.

After making the orders at one location, Michael has to get on the road again to visit the next customer. To minimize the traveling expense, Michael prefers taking the distances of customers in the surrounding area into consideration when he plans the traveling route. In order to accommodate this need, the AdventureWorks IT group has integrated a map viewer in the application using SQL Anywhere’s support for Spatial Data*. The viewer retrieves the geographic informaion that’s stored in the SQL Anywhere database, calculates the distances and maps the locations on a map. Michael can then simply check the map and decide which client he is going to visit next. Furthermore, any address changes made at the central office database will be synchronized through Mobile Link so that Michael can have the most up to date information with him.

This sample uses a custom-defined Mobile Link synchronization model that is deployed to the AdventureWorks2008 database, as well as a custom-created remote database that mimics a subset of AdventureWorks2008 features. An ADO.NET 3.5 application accesses the data in the remote (offline) database using both SqlCommand style objects and SqlTableAdapter style objects, demonstrating both methods of database interaction and .NET support. The customer location is stored as a ST_Geometry type using the WGS 84 standard reference system to mimic the longtitude/latitude information used by a GPS. The Mobile Link synchronization client (DBMLSync) is invoked using the .NET API to demonstrate incorporation of sync from within a .NET application.

Please refer to the online documentation for more information about SQL Anywhere .NET support (PDF P4), Mobile Link synchronization (PDF P.2), and the Mobile Link Client API for .NET (PDF P.335).

*Note that the spatial component is not included in the sample application for SQL Anywhere 11.

Production: A Web Service Consumer (.NET Web Services)

Susan Eaton needs to regularly check the product inventory for items that produced in-house. Unfortunately, her group is located off-site from the central headquarters and their databases. Rather than replicate the database unnecessarily, the production group simply wants the data to be available over the internet.

Thanks to SQL Anywhere’s integrated HTTP server, the data can easily be exposed via a secure web service using standard protocols such as SOAP and DISH (WSDL).

This sample first defines a web service. Then, it uses a .NET application to consume data from a .NET Web Service hosted from inside of the AdventureWorks2008 database.

Please refer to the online documentation for more information about SQL Anywhere web services (PDF P.881).

Purchasing: A Business Intelligence Application (Entity Framework, LINQ)

The purchasing group generates a lot of reports, and needs a lot of data to do this. More importantly, they want to have their report designers have data that is easy to use. Their reports are developed in Microsoft .NET C# because of its robustness. Their report designers are not very proficient with SQL. Rather than just be treated like rows of data, the purchasing group wants to work with objects that carry some business meaning. That way, they can generate reports quickly and easily. Because of this need, they are requesting that their software incorporate LINQ and the Entity Framework.

Thanks to SQL Anywhere’s Entity Framework support, data entities can be created automatically with a few clicks of the mouse. The resultant entities can be queried using Microsoft .NET’s LINQ (Language INtegrated Querying).

This sample uses ADO.NET to connect to the database and then uses Visual Studio 2010’s entity model designer to create programming objects that represent data, rather than just pull rows of data. The entities are queried using LINQ from a .NET application.

Please refer to the online documentation for more information about SQL Anywhere .NET support (PDF P.113).

Human Resources: A Web Application (ASP.NET Providers)

As with most human resources groups, Adventure Works Cycling's HR is concerned about privacy acts and needs to secure their data access. At the same time, they follow industry trends currently pointing towards we-based applicatinos for managing employee information. The IT group must deliver a solution that integrates trusted security into a web application and so have chosen ASP.NET technology due to its quick implementation time and broad range of security options.

Thanks to SQL Anywhere's ASP.NET providers support, the web application can leverage the SQL Anywhere database for storing confidential login information and application security details.

This sample uses the Visual Studio ASP.NET Setup Tool to configure the AdventureWorks 2008 database for use with an ASP.NET web application. It then defines security roles and privileges within the web application, thus highlighting the seamless integration.

Please refer to the online documentation for more information about the SQL Anywhere ASP.NET Providers (PDF P.161).

Requirements

• SQL Anywhere
  
• Microsoft Visual Studio 2008 SP1 or 2010
• Microsoft .NET Framework 3.5 SP1
• AdventureWorks2008 Sample Database for SQL Anywhere - Please note that there is a separate version of the sample database for SQL Anywhere 11 and SQL Anywhere 12.

Installing the Samples

1. Install the ‘AdventureWorks2008 Sample Database’.
2. Run the installer for the ‘AdventureWorks2008 .NET Samples’.

Running the Samples

1. Run the AdventureWorks2008 database engine, by selecting ‘Start Sample Database’ from the ‘AdventureWorks2008’ program group, located under ‘SQL Anywhere 12’. Click here to view the screenshot.
2. Run the AdventureWorks2008 MobiLink Server, by selecting ‘Start MobiLink Server’ from the ‘AdventureWorks2008’ program group, located under ‘SQL Anywhere 12’.
3. Run the AdventureWorks2008Remote database engine, by selecting ‘Start Sample Remote Database’ from the ‘AdventureWorks2008’ program group, located under ‘SQL Anywhere 12’.
4. Open the Visual Studio 2010 solution file in Microsoft Visual Studio 2010, by either selecting ‘Open .NET Samples Visual Studio 2010 Solution’ from the ‘AdventureWorks2008’ program group, or manually browsing to, and opening, the ‘AdventureWorks2008.sln’ file in the samples directory.
5. You can build and run any of the projects from the ‘Solution Explorer’.

Note You may need to resolve the path for the SQL Anywhere assemblies before building the projects. In the Visual Studio Solution Explorer, remove and readd the references to the following: Sales Disconnected Application: iAnywhere.Data.SQLAnywhere and iAnywhere.MobiLink.Client. Human Resources Web Application: iAnywhere.Data.SQLAnywhere and iAnywhere.Web.Security You may ignore and warning messages that appear when you readd the reference. For more information on adding references, please refer to Adding a reference to the Data Provider DLL in your project (PDF P.116).

Samples Walkthrough

1. First, make sure the AdventureWorks2008 and the AdventureWorks2008Remote database engines are running. See the Running the Samples section above for details.

   Part I: Michael Blythe

   In this part of the walkthrough, Michael Blythe, a sales representative, enters a new order while offline. By having a local SQL Anywhere database in his laptop that stores all the information required to place an order, Michael benefits from improved application response time as there is no need to retrieve product and customer information across the network. Once the order is entered, Michael synchronizes it with the main corporate database at Adventure Works Cycling headquarters.

2. Run the ‘Sales Disconnected Application’ project from the Visual Studio 2010 solution.
3. It will automatically load all previous orders. You can double-click on any order to view its details as an example. Click here to view the screenshot.
4. From the ‘File’ menu, select ‘New Order’ to enter a new order.
5. Choose ‘AW00000053’ as the Customer.
6. Choose ‘99 Edgewater Drive, Norwood. 02062’ as billing and shipping addresses.
7. Choose ‘CARGO TRANSPORT 5’ as the ship method.
8. Check the ‘Sales Territory’ option, and choose ‘Northeast’.
9. Choose ‘In process’ as the order status. Click here to view the screenshot.
10. Add the following items:
    • 2x BK-M18B-40, each at 149.99
    • 4x BK-R19B-44, each at 99.99
    • 4x BC-R205, each at 9.99
    • 6x HL-U509, each at 24.99
11. Click the ‘Apply’ button. The new order is saved in the local database and is ready to be replicated to the main corporate database.
12. From the main window, choose ‘Synchronize with Corporate Database’ from the ‘File’ menu, and wait for the synchronization to complete. Click here to view the screenshot.
13. You will notice the message "Synchronization completed" in the SQL Anywhere MobiLink client window. Click here to view the screenshot.
14. Select '‘Refresh’ from the ‘View’ menu and notice the new order. Its status is "In process".
15. Skip step 15-16 if you are using 'Adventure Works 2008 .NET Samples for SQL Anywhere 11'. From the ‘View’ menu, choose ‘View Customer Location’ to view the customers in the area. 
16. Check the ‘300 KM’ radio button to retrieve the list of customers within a range of 300 KM. The locations are plotted on the map and the detailed information is listed in the datagrid. Click on the customer with ID '29543' in the list, the corresponding point in the map is highlighted with a tooltip showing the relevant information. Click here to view the screenshot.
17. Close the application.

    Please refer to the online documentation for more information about the SQL Anywhere CLR external environment (PDF P.719), SQL Anywhere .NET support (PDF P.4), MobiLink synchronization (PDF P.3), and the Mobile Link Client API for .NET (PDF P.335).

    Part II: Susan Eaton

    In this part of the walkthrough, Susan Eaton, a stocker, will verify that the parts are in stock. The data she sees is consumed by a .NET application using a web service hosted from inside the SQL Anywhere database.

18. Run the ‘Production Web Service Consumer’ project from the Visual Studio 2010 solution.
19. It will automatically show all products and their inventory.
20. Scroll through the list and verify the following: 2x BK-M18B-40 ordered, 4x BK-R19B-44 ordered, and 4x BC-R205 ordered. Click here to view the screenshot.
21. Also verify that there is sufficient inventory to satisfy the orders.
22. Close the application.

    Please refer to the online documentation for more information about SQL Anywhere web services (PDF P.881).

    Part III: Sheela Word

    In this part of the walkthrough, Sheela Word, a purchasing manager, will verify that the parts are on order. The application uses LINQ and the Entity Framework to query the SQL Anywhere database.

23. Run the ‘Purchasing Business Intelligence Application’ project from the Visual Studio 2010 solution.
24. It will automatically show all products and their order status.
25. Scroll through the list and verify that there are at least 6x HL-U509 on order from a vendor. Click here to view the screenshot.
26. Close the application.

    Please refer to the online documentation for more information about SQL Anywhere .NET support (PDF P.4).

    Part IV: Paula Barreto de Mattos

    In this part of the walkthrough, Paula Barreto de Mattos, a human resources manager, increases Michael Blythe's salary. Paula's login credentials are stored in the corporate database and the web application uses the SQL Anywhere ASP.NET Provider for membership and role authentication.

27. Run the ‘Human Resources Web Application ’ project from the Visual Studio 2010 solution.
28. At the login screen, enter ‘paula0’ as the user name and ‘sqlanywhere’ as the password.
29. After authenticating, click the ‘Company Employee Management ’ link. Click here to see the screenshot.
30. On page #14, edit Michael Blythe's employee information. Click the Edit link on the left side and enter the new pay rate of 30.00. Click the Update link on the left side. Click here to see the screenshot.
31. Close the application.

    Please refer to the online documentation for more information about the SQL Anywhere ASP.NET Providers (PDF P.161).

Conclusion

All of the company’s business tasks were completed easily with seamless integration.

Relevant Code Highlights

The following section highlights the code that is used to interface with SQL Anywhere.

Spatial Data Type and Operations*

The customer location information is stored in a column of type ST_GEOMETRY with SRID 4326 (the default spatial reference system used by GPS) in the table ‘Address’. A stored procedure is then used to retrieve the list of customers within a given range by using the spatial operator ST_WithinDistance. The selected points are then passed to a function that projects the points using Mercator Projection and calculates the corresponding X/Y coordinates to be plotted on the map:

--Pass in the range as a parameter
CREATE PROCEDURE "Sales"."ufnGetCustomerLocation"(IN radius INTEGER)
RESULT(CustomerID INTEGER, AddressID INT, addr VARCHAR(100), PostalCode VARCHAR (10),
          dist DOUBLE, xCoord FLOAT, yCoord FLOAT, pointLocation VARCHAR (100))
BEGIN

SELECT DISTINCT Sales.Customer.CustomerID, Person.Address.AddressID,
                       STRING(AddressLine1, ', ', City) AS addr, PostalCode,
                       ROUND( SpatialLocation.ST_Distance ( NEW ST_Point (-80, 40, 4326)), 8 ) AS dist,
                       --Convert longitude/latitude to distance on the map "Sales".ufnCalculateDistance(SpatialLocation, 'x') AS xCoord,
                       "Sales".ufnCalculateDistance(SpatialLocation, 'y') AS yCoord,
                       SpatialLocation AS pointLocation

     FROM Person.Address, Sales.Customer
     JOIN sales.SalesOrderHeader
     ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID
          AND Sales.SalesOrderHeader.BillToAddressID = person.address.AddressID

           --Filter the points using the given range, assuming Point(-80, 40) is the center
      AND SpatialLocation.ST_WithinDistance( NEW ST_Point(-80, 40,4326), radius) = 1
     ORDER BY dist;
END;

*Note that the spatial component is not included in the sample application for SQL Anywhere 11.

CLR External Environment

Any static function from a .NET 2.0 class library can be used as a stored procedure or function. It only needs to be registered as follows:

CREATE FUNCTION MyFunction( IN InputParameter DOUBLE )
RETURNS DOUBLE
EXTERNAL NAME 'My.dll::MyClass.MyStaticFunction( double ) double'
LANGUAGE CLR;

ADO.NET Connectivity (SACommand)

One method of ADO.NET data provision is using SQL Anywhere client objects, similar to SqlClient provided by the base class. The following code demonstrates the basics of retrieving data using the ADO.NET Data Provider.

// Connect to the database.
SAConnection conn = new SAConnection("DSN='DataSource';UID='dba';PWD='sql'");
conn.Open();
SACommand cmd = conn.CreateCommand();

// Retrieve the data
cmd.CommandText = "SELECT Column FROM Table";
SADataReader data = cmd.ExecuteReader();
while (data.Read())
{
    myString = data.GetString(1);
}
data.Close();

// Close the connection.
conn.Close();

ADO.NET Connectivity (SATableAdapter)

By creating an ADO.NET Data Set, Visual Studio 2010 will automatically use SQL Anywhere objects to connect to the database. It will create a table adapter for use in data grids. You can see all of the generated code associated with ‘SalesOrdersView.xsd’, as well as in the form designer for ‘SalesOrdersList.cs’. Consequently, only one line of code need be written for the ‘RefreshList’ method.

public void RefreshList()
{
    // Tell the ADO.NET Table Adapter to refresh itself.
    this.salesOrdersTableAdapter.Fill(this.salesOrdersView.vSalesOrders);
}

.NET Web Service Consumption

By adding a service reference in Visual Studio 2010 to the .NET Web Service hosted within the SQL Anywhere database, code will automatically generated for connecting to and querying the web-service. It can then be accessed in a similar manner to the ADO.NET Data Provider.

// Connect to the web service.
DISHSoapPortClient client = new DISHSoapPortClient();

// Query the data.
int sqlCode = 0;
DataTableReader data = client.Service(out sqlCode).CreateDataReader();

// Read the data.
while (data.Read())
{
    myString = data.GetString(0);
}
data.close();

LINQ to Entity Framework

By creating an ADO.NET Data Entity Model, Visual Studio 2010 will automatically use SQL Anywhere objects to connect to the database. It will create objects that can represent the data stored in the database. You can see all of the generated code associated with ‘PuchasingEntities.emd’. Consequently, only a few lines of code need be written for the ‘RefreshData’ method.

private void RefreshData()
{
    // Use LINQ to query the generated entities.

    PurchasingEntities entities = new PurchasingEntities();

    var items =
        from pv in entities.ProductVendor
        join p in entities.Product on pv.ProductID equals p.ProductID
        join v in entities.Vendor on pv.BusinessEntityID equals v.BusinessEntityID

        select new {
            ProductNumber = p.ProductNumber,
            ProductName = p.Name,
            LeadTime = pv.AverageLeadTime,
            Ordered = pv.OnOrderQty.HasValue ? pv.OnOrderQty.Value : 0,
            Vendor = v.Name };

    // Add each item to the list.

    foreach (var item in items)
    {
        list.Items.Add(new ListViewItem(new string[] {
            item.ProductNumber,
            item.ProductName,
            item.LeadTime.ToString(),
            item.Ordered.ToString(),
            item.Vendor }));
    }
}

ASP.NET Data Providers (web.config)

Once the database is setup properly, you can use SQL Anywhere as the data store for your ASP.NET Provider data by inserting the following lines into your application's web.config file:

<?xml version="1.0"?>

<configuration>

    <connectionStrings>
        <add name="MyCS"
            connectionString="..."
            providerName="iAnywhere.Data.SQLAnywhere"/>
    </connectionStrings>

    <system.web>

        <authorization>
            <allow roles="..."/>
            <deny users="*"/>
        </authorization>

        <roleManager enabled="true" defaultProvider="SARoleProvider">
            <providers>
                <add connectionStringName="MyCS"
                    applicationName="/"
                    commandTimeout="30"
                    name="SARoleProvider"
                    type="iAnywhere.Web.Security.SARoleProvider"/>
            </providers>
        </roleManager>

        <membership defaultProvider="SAMembershipProvider">
            <providers>
                <add connectionStringName="MyCS"
                    applicationName="/"
                    commandTimeout="30"
                    enablePasswordReset="true"
                    enablePasswordRetrieval="false"
                    maxInvalidPasswordAttempts="5"
                    minRequiredNonalphanumericCharacters="0"
                    minRequiredPasswordLength="4"
                    passwordAttemptWindow="10"
                    passwordFormat="Hashed"
                    requiresQuestionAndAnswer="false"
                    requiresUniqueEmail="true"
                    passwordStrengthRegularExpression=""
                    name="SAMembershipProvider"
                    type="iAnywhere.Web.Security.SAMembershipProvider"/>
            </providers>
        </membership>

        <profile defaultProvider="SAProfileProvider">
            <providers>
                <add name="SAProfileProvider"
                    type="iAnywhere.Web.Security.SAProfileProvider"
                    connectionStringName="MyCS"
                    applicationName="/"
                    commandTimeout="30"/>
            </providers>
            <properties>
                <add name="UserString" type="string" serializeAs="Xml"/>
                <add name="UserObject" type="object" serializeAs="Binary"/>
            </properties>
        </profile>

        <authentication mode="Forms"/>

    </system.web>

</configuration>

When you finish setting up the SQL Anywhere ASP.NET providers, you can use the Visual Studio ASP.NET Web Site Administration Tool to create and manage users and roles for your applications, as depicted in the following screenshot: