Introduction

SQL Anywhere's CLR external environment　(PDF P.719) allows the implementation of user-defined functions and stored procedures written in any .NET language, such as C# or Visual Basic .NET. There are two main benefits of using the CLR external environment:

1. It provides an alternative to the SQL language, especially for operations where .NET is better suited than SQL (e.g.: string manipulation or mathematical computations).
2. It allows programmers to re-use existing .NET libraries.

In this demonstration, we take advantage of the publicly available YouTube API to obtain details of a particular account's subscriptions and store this information inside a SQL Anywhere database. Once this is done, the information can be retrieved by any database application.

Requirements

• SQL Anywhere - you can download a free Developer Edition
• Visual Studio 2008 SP1 or Visual Studio 2010
• .NET Framework 3.5 with SP1 or higher
• YouTube API (part of the Google Data API SDK - tested with version 1.4.0.2)
• Web browser capable of playing YouTube videos
• Source code - extract this file into 'My Documents\Visual Studio 2008\Projects', making sure to use folder names. All files are extracted into a folder named 'YouTube'.

This demonstration was written in the C# programming language.

For your convenience, the three required files from the YouTube API (Google.GData.Client.dll, Google.GData.Extensions.dll, and Google.GData.YouTube.dll) are included in the folder 'Redist'.

Setup

1. Open a Visual Studio 2008 Command Prompt: click Start > Programs > Microsoft Visual Studio 2008 > Visual Studio Tools > Visual Studio 2008 Command Prompt.
2. Change to the directory that contains the Google Data API SDK redistributables (The dlls are provided in the folder 'My Documents\Visual Studio 2008\Projects\YouTube\Redist').
3. Install the required Data API assemblies in the global assembly cache (GAC). Execute the following commands:

gacutil /i Google.GData.Client.dll
gacutil /i Google.GData.Extensions.dll
gacutil /i Google.GData.YouTube.dll

You will also need to install the YouTube access library in the GAC. This is explained below after you compile the assembly.

Application Architecture

There are four components in this demonstration:

1. A SQL Anywhere database that stores the YouTube account's subscriptions.
2. A YouTube access library that calls two Data API methods to obtain the subscription and video information.
3. A data access layer that saves, retrieves, or updates the subscription and video information from the database.
4. A custom viewer that displays subscription and video information.

The following diagram illustrates the application's architecture.

Demonstration Walkthrough

Compile YouTube Access Library

For your convenience, a compiled version of the YouTube access library (YouTube.dll) is included in the folder 'Redist'. You can use this assembly in case you do not want to compile the library.

1. In Visual Studio, open the solution "YouTube.sln".
2. Ensure that the references to the three Google Data API assemblies point to the correct path (The dlls are provided in the folder 'My Documents\Visual Studio 2008\Projects\YouTube\Redist' for your convenience). If the path is incorrect, simply remove and add them again.
3. Build the solution. The YouTube access library (YouTube.dll) will be compiled in the folder 'YouTube\YouTube\Output\x86\Debug'.

The code in YouTube.cs retrieves the subscriptions for a particular YouTube account, along with its associated video information. All these details will be stored into a SQL Anywhere database by the access layer assembly.

Add the YouTube Access Library to the GAC

1. Using the Visual Studio 2008 Command Prompt, change to the directory that contains the YouTube access library (YouTube.dll)
2. Install the YouTube access library in the GAC. Execute the following commands:

gacutil /i YouTube.dll

Compile the YouTube/SQL Anywhere Database Access Layer

1. In Visual Studio, open the solution "YouTube Subscriptions Viewer.sln". It includes two projects: the database access layer (YouTube.Database) and the viewer (Subscriptions).
2. Ensure that both references to the SQL Anywhere .NET data provider point to the correct path (default is C:\Program Files\SQL Anywhere12\Assembly\V2\iAnywhere.Data.SQLAnywhere.dll). If the path is incorrect, simply remove and add it again. Click here for detailed instructions on how to accomplish this (PDF P.116).
3. Ensure that the reference to the YouTube access library (YouTube.dll) points to the correct path. If the path is incorrect, simply remove and add it again.
4. Build the solution. The database access layer assembly (YouTube.Database.dll) will be compiled in the folder 'YouTube\YouTube.Database\Output\x86\Debug'.
5. Using Windows Explorer, copy the database access layer assembly to the 'Bin32' folder of your SQL Anywhere installation (default is C:\Program Files\SQL Anywhere 12\Bin32').

Run the YouTube Subscription Viewer

1. Open a Command Prompt, change to the 'YouTube' directory and start the SQL Anywhere database by executing this command:

dbeng12 YouTube.db

2. Back in Visual Studio, run the Subscription Viewer.

3. From the File menu, select Sign In.
4. Sign in to your YouTube account using valid credentials. Enter your account name and password. Click on Sign In.

5. The application retrieves a list of the videos for each of the user's subscriptions. The list of videos is stored inside the SQL Anywhere database and is automatically updated each time the user signs in.
6. Click on a video from the list. The information about the video is retrieved from the SQL Anywhere database and displayed in the lower right panel. You can view the video in the upper right panel.

7. Open a Web Browser, navigate to http://www.youtube.com and sign in to your account.
8. Open your subscriptions.
9. Let's subscribe to videos tagged as "SQLAnywhere". Enter that tag and click on Subscribe.

10. Go back to the Subscription Viewer and select Check For New Videos from the File menu.
11. Notice that a new list of videos appears that match the tag "SQLAnywhere".

12. You may now close the application. From the File menu, select Sign Out, then Exit.

Relevant Code Highlights

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

Database Schema

The SQL Anywhere database schema is fairly simple. It only contains three tables that store the YouTube account name, subscriptions, and video information; as well as two stored procedures to manage the subscriptions and videos.

The stored procedures do not execute SQL code, but rather make external calls to the .NET database access layer we created. Here's the defintion for one of the procedures:

ALTER PROCEDURE "YouTube"."SynchronizeYouTubeSubscriptions"(
  in UserID long varchar,
  in Username long varchar,
  in Password long varchar )
external name 'YouTube.Database.dll::YouTube.Database.SynchronizeSubscriptions( string, string, string )' language CLR

Notice the definition is just like the one for any other SQL stored procedure. This allows us to call the procedure in our code the same way you call a standard SQL stored procedure.

YouTube Access Library

The YouTube access library simply makes calls to the YouTube API. Its code is located in the file YouTube.cs in the solution "YouTube". Here's the code for the method used to obtain the list of subscriptions for a particular account:

YouTube/SQL Anywhere Database Access Layer

The database access layer code is located in the file Database.cs in the project "YouTube.Database". All the data access layer does is manage the subscription and video information. It obtains the list of subscriptions and videos from the YouTube access library (Google Data API ) and retrieves/inserts/updates/deletes that information from/into the SQL Anywhere database.

YouTube Subscription Viewer

The subscription viewer simply calls the CLR stored procedures defined in the database. Its code is found in the file Main.Data.cs in the project "Subscriptions". Here's an excerpt of the code to manage the account's subcriptions:

Clean Up

Perform the following steps to clean up your environment after the demonstration.

1. Exit Visual Studio.
2. Shut down the database. Execute the following using a Command Prompt:

dbstop -c "eng=YouTube;uid=dba;pwd=sql"

3. Remove the demonstration assemblies from the GAC. Execute the following using a Visual Studio Command Prompt:

gacutil /u Google.GData.Client
gacutil /u Google.GData.Extensions
gacutil /u Google.GData.YouTube
gacutil /u YouTube

4. Remove the file YouTube.Database.dll from your SQL Anywhere 'Bin32' directory.