Hooking into Google Apps With CData Google Data Provider

Introduction

The world is full of information and there are all kinds of ways to access it. The Microsoft .NET framework provides several convenient ways to access data from different data sources, whether it's an XML file, a web stream, or a database. For databases, Microsoft offers several techniques for retrieving the data. One of the simplest forms is through an ADO.NET Data Provider. The Data Provider architecture gives us an easy way to extract data from a table and put it in an in-memory data structure called a DataSet. ADO.NET also provides a way to perform other common database tasks such as querying the data or calling stored procedures. If you have used ADO.NET in the past, you are probably familiar with using a Data Provider to control data inside a database. But what if you want to access data that is not available in a database? What if you want to find data that is inside your Google account? Is there a way to access Google account data through the ADO.NET mechanism many of us are already using? Fortunately the answer is a resounding "yes!". That choice is available through the CData Google Data Provider, which works like any other Data Provider, as you will soon find out.

Installing the Software

Installing the CData Google DataProvider walks you through like most wizards these days. Just launch the executable and follow the steps. The only unusual part is that you are prompted for the registration keys twice: once from the web when downloading the installer, and again while running the executable. When I asked the company about the dual registration, CData has indicated that customers are provided with direct links and a customer would only have to enter the serial number and registration key once.

You are also required to run a registry file for desktop applications. Because this is a developer application, the registry file is not too inconvenient, but it should probably be made more transparent in the future. In the case of ASP.NET apps, CData has alternative licensing available that does not require registry access for deployment such as shared server support.

The Data Provider Schema

The CData Google Provider is laid out similarly to a SqlServer database with Tables, Views, and Stored Procedures. Tables consist of Calendar, ContactGroups, and Contacts which gives you access to Google's online calendar and contacts. Views consist of Google Docs which consist of Documents, MailMessages, SpreadSheets, and Worksheets. As you can see, the Views give you access to practically every other Google online application including GoogleDocs and Gmail. The stored procedures also supply a rich set of tools to use with Google applications enabling you to perform functions like a Google search, or to export GoogleDocs to Microsoft Word.

Using the Data Provider

Once the DataProvider is installed, it works just like a Sql Data Provider.This is great, because you can access the CData Google Provider directly from Server Explorer, giving you all the drag and drop magic you are used to with other Data Providers. For example, you can create a new connection to a Google account simply by choosing the CData Data Source as shown in figure 1:

Figure 1 - Choosing the CData Google Data Source in Server Explorer

Then you can create a connection as you would any other Sql Connection; you just use your Google account credentials instead:

Figure 2 - Filling in the Google Account Information

Once you hit Test Connection and it succeeds, you are all hooked up to Google and ready to go. You can immediately examine your data through Server Explorer and begin to imagine all the cool applications you can create for Google users. For example, the CData Google Provider shows all my contact information from Google if I right click on contacts and retrieve the data:

Figure 3 - Retrieving Contact Data Directly from a Google Account in Server Explorer

I could easily drag the Contacts table onto a Windows Form and bind it to a Data Grid, giving me an instant application that allowed me to organize my contact information from Google. Or I could create an ASP.NET web page that does the same thing. Although I don't know if I'd want to share all my Google contacts with the world, I could filter just those contacts I wanted to make available through a password protected site. Consider another scenario: Let's say I wanted to list all the people in my company's sales department on a page on the web. I could use the CData Google Data Provider to query just the contacts in my sales ContactGroup and put them in the sales page. Anytime I hired new salesman (or lost an existing salesman), I could just change my Google ContactGroups and the page would auto-magically update itself to reflect the new information. You might say, "Well I can just as well do that in a database". That's true, but if you are using the convenient web interface of Google to change contacts all the time, this is a way more convenient solution.

Query Limitations

Before delving further into an example of using the CData Google Provider, it's important to understand that there are limitations to what you can do with a non-database provider as opposed to a provider that internally calls the Google API. Here are some of them:

• Table joins are not supported.
• Table aliases are not supported.
• GROUP BY, HAVING, ORDER BY clauses and nested statements are not supported at the time of this article (though the developers assured me that support for these queries will be available soon).
• Scalar or aggregation functions are not supported.

The Data Provider does support Selects and Where clauses. Fortunately, this would be sufficient most of the time because I can manipulate the items above through LINQ once I have the requested data from the CData Data Provider.

The Code

If you are already familiar with using the Sql Data Provider, than using the CData Google Data Provider is fairly straightforward. Listing 1 is a snippet from CData's sample code for connecting Google Calendar Data to a Grid in your ASP.NET web page:

Listing 1 - Binding the CData DataSource to a GridView in ASP.NET

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="False"
	DataSourceID="GoogleDataSource" BorderStyle="Dotted" BorderWidth="2px"
	CellPadding="3" CellSpacing="3">
	<Columns>
		<asp:BoundField DataField="where" HeaderText="Where" SortExpression="where" />
		<asp:BoundField DataField="authoremail" HeaderText="AuthorEmail" SortExpression="authoremail" />
		<asp:BoundField DataField="description" HeaderText="Description" SortExpression="description" />
		<asp:BoundField DataField="endtime" HeaderText="EndTime" SortExpression="endtime" />
		<asp:BoundField DataField="starttime" HeaderText="StartTime" SortExpression="starttime" />
		<asp:BoundField DataField="id" HeaderText="Id" ReadOnly="True" SortExpression="id" />
		<asp:BoundField DataField="title" HeaderText="Title" SortExpression="title" />
	</Columns>
</asp:GridView>
<asp:SqlDataSource ID="GoogleDataSource" runat="server"
	ConnectionString="<%$ ConnectionStrings:GoogleConnectionString %>"
	ProviderName="<%$ ConnectionStrings:GoogleConnectionString.ProviderName %>"
	SelectCommand="SELECT * FROM [Calendar.rsd]">
</asp:SqlDataSource>

Remember: You will also need to list the connection string in the web.config file:

<add name="GoogleConnectionString" connectionString="Location=C:\Program Files (x86)\CData\CData Google Data Provider\db;User=myuser;Password=mypassword" providerName="System.Data.CData.Google"/>

As you can see, there is little difference between binding a Sql Server data source and a CData Data Source. The only detail that I didn't recognize in the sample code is that the table name has an rsd extension, but removing the extension did not seem to affect the application. Figure 4 shows part of the resulting page with the minimum coding shown in Listing 1.

Figure 4 - The Google Calendar Data Displayed in a GridView bound to the CData Google Data Provider

Let's look at a C# example in a Windows Form Application. Figure 5 is a sample Google Docs app supplied with the CData demo. This begins to show us the range of capabilities provided by CData for writing desktop apps.

Figure 5 - A Google Docs Windows Form App

Now let's examine the code behind this Google App. Again, if you understand how to use a Sql Provider in ADO.NET and the rich class set in ADO.NET, then the learning curve for access to Google Docs is not steep.

Listing 2 - ADO.NET code in a Windows Form to Display Google Doc List

private BindingSource GoogleDocsSource = new BindingSource();
private string connectionString;
private GoogleDataAdapter dataAdapter = new GoogleDataAdapter();
private string selectCommand = "SELECT Name,AuthorName,Kind,Updated,Weblink from Docs";
 
private void GetData() {
   
	try {
		Cursor.Current = Cursors.WaitCursor;
		dgvDocs.DataSource = GoogleDocsSource;
		GoogleConnection conn = new GoogleConnection(connectionString);
		dataAdapter = new GoogleDataAdapter(selectCommand, conn);
		GoogleCommandBuilder commandBuilder = new GoogleCommandBuilder(dataAdapter);
		dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
		DataTable table = new DataTable();
		dataAdapter.Fill(table);
		GoogleDocsSource.DataSource = table;
		dgvDocs.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
      
	} catch (Exception ex) {
		MessageBox.Show(ex.Message);
      
	} finally {
		Cursor.Current = Cursors.Default;
	}
}

Notice that we fill a DataTable just like we would in any other Sql Data Provider application. We create a connection with the GoogleConnection object, passing it a connection string. We then create an adapter that takes a query string and the connection object. We use the adapter to fill a data table and then we bind the data table to a data grid.

The Google CData Provider also has a set of stored procedures that provide additional features for extracting your online Google Docs. Listing 3 illustrates the use of a stored procedure that allows you to download a Google doc into other file formats. I was impressed by the number of formats the stored procedure supported through the underlying Google API. For example, for spreadsheets it supports exporting into txt, csv, and even xls format. For documents it supports exporting into pdf, doc, rtf, odt, and html format. Listing 4 illustrates how simple it is to call the ExportGoogleDoc stored procedure from the CData Google Data Provider.

Listing 4 - Exporting a Document from Google Docs to the Desktop

try {
	SaveFileDialog saveDialog = new SaveFileDialog();
	saveDialog.Filter = "All Files (*.*)|*.*|CSV (*.csv)|*.csv|DOCX (*.docx)|*.docx|HTM (*.htm)|*.htm|HTML (*.html)|*.html|PDF (*.pdf)|*.pdf|PPT (*.ppt)|*.ppt|TXT (*.txt)|*.txt|XLSX (*.xlsx)|*.xlsx";
	saveDialog.Title = "Select the location to download.";
	saveDialog.FileName = dgvDocs.Rows[dgvDocs.SelectedCells[0].RowIndex].Cells["Name"].Value.ToString();
	saveDialog.RestoreDirectory = true;
	if (saveDialog.ShowDialog() == DialogResult.OK) {
		Cursor.Current = Cursors.WaitCursor;
		GoogleConnection conn = new GoogleConnection(connectionString);
		GoogleCommand cmd = new GoogleCommand("ExportGoogleDoc", conn);
		cmd.CommandType = CommandType.StoredProcedure;
		cmd.Parameters.Add(new GoogleParameter("Type", new System.IO.FileInfo(saveDialog.FileName).Extension.Substring(1)));
		cmd.Parameters.Add(new GoogleParameter("Name", dgvDocs.Rows[dgvDocs.SelectedCells[0].RowIndex].Cells["Name"].Value.ToString()));
		cmd.Parameters.Add(new GoogleParameter("LocalFile", saveDialog.FileName));
		cmd.ExecuteNonQuery();
		MessageBox.Show("File Downloaded.");
	}    
} catch (Exception ex) {
	MessageBox.Show(ex.Message);
    
} finally {
	Cursor.Current = Cursors.Default;
}

As you may have deduced from figure 5, you can also use the provider to upload documents from your desktop to Google. For example, you could probably create a Word Add-In that seamlessly exports your word documents to your Google account. The import stored procedure code to call UploadGoogleDoc is implemented in much the same way as the export procedure code since it is using the same techniques to call a stored procedure using ADO.NET. The upload procedure just requires a path to the local file you are uploading as a parameter.

Listing 3 - Importing a Document into Google

try {
	OpenFileDialog openDialog = new OpenFileDialog();
	openDialog.Filter = "All Files (*.*)|*.*";
	openDialog.Title = "Select a file to upload.";
	openDialog.RestoreDirectory = true;
	if (openDialog.ShowDialog() == DialogResult.OK) {
		Cursor.Current = Cursors.WaitCursor;
		GoogleConnection conn = new GoogleConnection(connectionString);
		GoogleCommand cmd = new GoogleCommand("UploadGoogleDoc", conn);
		cmd.CommandType = CommandType.StoredProcedure;
		cmd.Parameters.Add(new GoogleParameter("LocalFile", openDialog.FileName));
		cmd.ExecuteNonQuery();
		GetData();
	}
	
} catch (Exception ex) {
	MessageBox.Show(ex.Message);
	
} finally {
	Cursor.Current = Cursors.Default;
}

Caching

You'll notice when using the provider that it can sometimes be a little slow due to the inherent latency of the underlying Google API. The CData Provider comes with some caching functionality to help speed up performance. The cache allows the Data Provider to query against a local database on the user's machine. The CData Data Provider allows you to explicitly cache data by use of the CACHE statement. Alternatively, you can set the CData Data Provider to auto-cache by setting AutoCache to true in your connection string.The CData Data Provider also provides a third caching option called offline. In offline caching, the user can execute all CRUD statements against the user's local cached database. To use offline mode, just set offline to true in your connection string:

GoogleConnection myConnection = new GoogleConnection("Location=/mydb;Offline=true;User=user@gmail.com;Password=password");

The QuickBooks CData Data Provider

The Google Data Provider is not the only software that CData provides. There is also a CData QuickBooks Provider. The QuickBooks provider gives us the same convenient ADO.NET rich class set for querying and manipulating the data inside of QuickBooks. With the Provider the user has access to the all of the following QuickBooks Schema Components: Accounts, Bills, Checks, CreditMemos, Customers, Invoices, JournalEntries, PurchaseOrders, SalesOrders, SalesReceipts, and Vendors. The stored procedures in the CData for QuickBooks allow you to search for particular transactions. This is useful if you want to find transactions for a particular account or find transactions that fall within a particular date range. Like the CData Data Provider for Google, the QuickBooks has an offline and caching option as well. In the QuickBooks Data Provider, you can also provide a URL in the connection string if you want to communicate with QuickBooks remotely.

Conclusion

If you are a .NET programmer, you often find yourself looking for solutions to problems that allow you to leverage the .NET framework. If you have a project that requires you to manipulate data in a Google account or a group of Google accounts, the Google CData Data Provider gives you an easy way to use what you already know. With CData you can use ADO.NET and SQL to manipulate all of the applications available on Google. Whether it's for the desktop or the web, you can use this convenient mechanism to create Google-enhanced applications. If you are curious to experiment with the CData for either Google or QuickBooks, you can download a trial version and give it a try from the CData website. The cost of the Google CData Data Provider is $299 as of this writing. The QuickBooks CData Data Provider is $399. Once you purchase the components, they are royalty free to distribute with your application. If you are looking for a well-organized component for exercising the rich online applications provided by Google, your search is over: CData gives you everything you need to mine the Google landscape.