Hooking into your Google Apps With the RSSBus Google Data Provider

Figure 4 - The Google Calendar Data Displayed in a GridView bound to the RSSBus 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 RSSBus demo. This begins to show us the range of capabilities provided by RSSBus 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 RSSBus 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 RSSBus 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 RSSBus 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 RSSBus Data Provider allows you to explicitly cache data by use of the CACHE statement. Alternatively, you can set the RSSBus Data Provider to auto-cache by setting AutoCache to true in your connection string.The RSSBus 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=mgold45;Password=password");

The QuickBooks RSSBus Data Provider

The Google Data Provider is not the only software that RSSBus provides. There is also an RSSBus 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 RSSBus 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 RSSBus 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 RSSBus Data Provider gives you an easy way to use what you already know. With RSSBus 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 RSSBus for either Google or QuickBooks, you can download a trial version and give it a try from the RSSBus website. The cost of the Google RSSBus Data Provider is $299 as of this writing. The QuickBooks RSSBus 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: RSSBus gives you everything you need to mine the Google landscape.