posts - 36 , comments - 2 , trackbacks - 0

Monday, February 3, 2014

Import Salesforce Data into Google Spreadsheets

The Salesforce Connector offers standards-based web service endpoints what allow a variety of applications to get data from Salesforce. These endpoints expose data as OData, RSS, HTML, CSV, ATOM, SOAP, JSON, JSONP, and TSV. We will use the CSV format to import Salesforce data into a Google Spreadsheet. In this article, we will use the ImportData function of Google Spreadsheet to access the CSV data as shown below:

=ImportData('https://mydomain/sfconnector/services/odata.rsc/Account?@csv&@authtoken=3j5X0g5xxxxxxxxxxx')

This article assumes that you have already configured a working connection to Salesforce.

  1. First, configure the OData endpoint to include the table of interest. We will use the Accounts table. On the navigation bar on the connector's main page, click Settings and, clicking the Tables tab, add the Account table.
  2. Then find the access token for the administrator account in Admin -> Manage Users page. Optionally, you can create another user and use its access token. Each connector user is issued a unique security token that can be managed by the connector administrator.

  3. The authtoken parameter identifies who is accessing the data from the OData endpoint. Authenticate your query to the Salesforce data with your chosen authtoken, the last parameter in the URL.

  4. In a new Google sheet, use the =ImportData() formula to request the CSV file from the exposed data source. We use the default query to select the entire Account table. Google will periodically update the results of the formula, ensuring that the sheet contains up-to-date data. You can use a formula like this one:

    =ImportData("http://my.domain:34907/sfconnector/services/odata.rsc/Account?@csv&@authtoken=3j5X0g5xxxxxxxxxxx")

  5. Note: The URL to access the data is an OData URL and you can use filters, order-bys etc. to get just the portion of data you need.

Posted On Monday, February 3, 2014 5:44 AM | Comments (0) |

Tutorial: Access Salesforce Leads in SharePoint through an External List

You can expose a data source as an external list in Sharepoint using the RSSBus Connectors. This gives your users the capabilities to access and update live Salesforce data in SharePoint without browsing to Salesforce.com. For example, you can view and edit your Salesforce leads in SharePoint by creating an external list. Note that this tutorial applies to any of our data sources, not just Salesforce.

This guide is for SharePoint 2013 and assumes that Visual Studio 2012, Office Developer Tools for Visual Studio 2012, and the Salesforce Connector have been installed.

Contents

  1. Configuring the External List in SharePoint
  2. Pass-Through Authentication
  3. Credentials Authentication

Configuring the External List in SharePoint

First, determine if you need Pass-Through authentication or Credentials authentication.

Pass-Through authentication requires that you set up the RSSBus Integration Server to use Windows authentication. You can read more about configuring this option in the section Pass-Through Authentication.

Credentials authentication sets up one user name and password that is used to log on to the Salesforce Connector. The administrator then authorizes SharePoint users to be able to access the Salesforce Connector with this account. You can read more about configuring this option in the section Credentials Authentication.

Create the External Content Type

  1. Launch Visual Studio and create a new project. On the project type menu, select App for SharePoint 2013. To create the project for this app, select SharePoint-hosted and click Finish.
  2. In the Solution Explorer window, right-click the app project, point to the Add option, and choose Content Types for an External Data Source.
  3. In the resulting pop-up menu, supply the URL to the OData endpoint for the Salesforce Connector, and a name for the data source. For example, the Salesforce Connector's URL should look something like this:

    http://localhost:8181/sfconnector/odata.rsc

    In this example, the URL contains "localhost." This needs to be the IP address or server name if you have installed the Salesforce Connector on a different machine than the one running SharePoint.

  4. In the grid on the next page, choose which tables you want to expose as external content types, and click Finish.

  5. Browse to the newly created external content type in the Solution Explorer and locate the .ect file. In the screenshot below, this is Lead.ect. Double-clicking this file shows the column configuration editor, the filters for your content type, and the authentication methods. Notice that the default value is PassThrough: you will need to change this if you plan to use Credentials authentication instead, which is described in more detail later in this article.

  6. Next, import your schema file -- Lead.ect in this example. Point your browser to the SharePoint Central Administration portal and click Manage service Applications.

  7. In the resulting page, click Business Data Connectivity Service. Click Import and select the .ect file in the dialog.

Create the External List

Now that the external content type has been created, we are ready to create the external list in SharePoint.

  1. Open the browser and point it to your SharePoint site. Click Site Contents.
  2. Select Add an App.
  3. The resulting page shows the app types that can be added to your SharePoint site. Select the External List option. This opens a pop-up menu prompt for the name and the external content type of your external list. Click the Select External Content Type icon and choose the external content type that you created in the previous section.
  4. Once you select the content type, click Create.
 

Pass-Through Authentication

To use Pass-Through authentication for accessing your external content type, enable Windows authentication in the RSSBus Integration Server.

Kerberos Authentication

If your users are connecting to SharePoint using Kerberos authentication, you need to add these users to your Salesforce Connector.

Non-Kerberos Authentication

If you are not using Kerberos authentication, you are likely using another form of Windows authentication such as NTLM. When using pass-through authentication in the Business Connectivity Service (BCS) in SharePoint and users are authenticating via NTLM authentication, SharePoint connects to the external web service using the default IIS account, which is often the NT AUTHORITY\IUSR account, so you need to give the IUSR user access to the Salesforce Connector.

Back to Top

 

Credentials Authentication

Follow these steps to use Credentials authentication for your external content type:
  1. Create a Secure Store Target Application that authenticates the users who are allowed to access the Salesforce Connector:
    • Browse to the Central Administration area for your SharePoint site and click Manage Service Applications.
    • Click Secure Store Service link, and then press New.
    • When configuring the new Secure Store Target Application, supply Application ID, Display Name, and Contact E-mail. The Target Application Type should be set to Group.
  2. On the next page, add user name and password field names and field types.
  3. Next, choose administrators who are authorized to access connection settings for the external content type. The wizard prompts you for administrators who can manage these settings, and for users who are authorized to connect with the application's credentials.
  4. Having created the new Secure Store Target Application, click the link to the target application, and select Set Credentials from the drop-down menu that appears.
  5. In the resulting pop-up menu, enter the user name and password that SharePoint will use when connecting to the Salesforce Connector.
  6. When accessing the endpoint for your OData service, SharePoint uses an OData setting to map the external content type to the secure store credentials. You can register this setting with SharePoint by opening an instance of the SharePoint Management Shell and entering a command that will set several connection options. You can copy the command's options from the connection settings that you use to connect to the Salesforce data source. After defining the OData service URL, SharePoint site URL, connection name, and target application Id options, your command should resemble the sample below:
  7. New-SPODataConnectionSetting -AuthenticationMode Credentials 
    -ServiceAddressURL http://myserver/sfconnector/odata.rsc -ServiceContext
     http://myspsite/ -Name MyODataConnectionId 
    -SecureStoreTargetApplicationId SFConnector

  8. The final step in using Credentials authentication involves changing several properties in the external content type schema file, then importing it into SharePoint from Visual Studio. In the Solution Explorer in Visual Studio, browse to the .ect file. Set the ODataConnectionSettingsId property to the name of the OData setting you registered with SharePoint in the previous step, ODataServiceAuthenticationMode and ODataServiceMetadataAuthenticationMode to Credentials, and AllowInsecureTokenExchange to True.
  9. Once you save and rebuild the project, your .ect file is ready to import into SharePoint.

Back to Top

Posted On Monday, February 3, 2014 5:44 AM | Comments (0) |

Creating Master-Detail Screens in LightSwitch

LightSwitch provides support for using relationships between tables in a data source. However, QuickBooks does not natively expose relationships that can be used in LightSwitch. This article will walk you through the steps to set up a LightSwitch master-detail screen using simple queries.

  • Step 1: Open Visual Studio and make a new LightSwitch project or open an existing project.
  • Step 2: Add a new QuickBooks data source. For this demo, we will use Invoices and InvoiceLineItems but any transaction and line items tables will work.
  • Step 3: Right-click the InvoiceLineItems table under Data Sources and click Add Query. Add a parameter of type String to serve as a placeholder for the InvoiceId field. Add a filter condition to this query. We want to search for all line items with a certain invoice ID. The parameter will serve as a placeholder for this value.
  • Step 4: Add a new screen to your LightSwitch project. We will use an Editable Grid screen as the template. Select the Invoices table in the Screen Data list.
  • Step 5: On the new screen, click the Add Data Item button and choose the query we created in step 3. In the list of data items, scroll to the bottom of the query we just added until you see the Query Parameters node. Open the properties for the parameter you defined in step 3 and enter "Invoices.SelectedItem.ID" in the Parameter Binding box. This will bind the parameter to the ID of the currently selected invoice on the screen.
  • Step 6: Drag the query from the data item list onto the Screen Content Tree. We will just make a simple screen that displays the Invoices grid on top with the line items grid, Get Invoice Line Items, on the bottom. However, you can use layout items to customize the look of the screen however you need.
  • Step 7: Now all you need to do is run the application and the grids should be populated with the data from Invoices. Every time you select an invoice, the grid will be populated with the line items for that invoice.

Troubleshooting

If an error occurs when trying to load the data from these tables, you may see a red "X" where the data should appear. To troubleshoot the issue and see a more descriptive error message, follow these steps:

  • Step 1: Open your project properties and change the Application Type to Web.
  • Step 2: In Solution Explorer, select your project node and change from Logical View to File View.
  • Step 3: Expand the Server node and open the Web.config. Change the Mircosoft.LightSwitch.Trace.Enabled key to "true".
  • Step 4: Run your application again to the point that you receive the error. Change the URL to http://URL:port number/trace.axd
  • Step 5: Check this list for requests that have a status code other than 200. Errors with a 500 status code can correspond to an error coming from the QuickBooks ADO.NET driver.
If you continue to experience problems with your LightSwitch project, please contact support@rssbus.com.

Posted On Monday, February 3, 2014 5:44 AM | Comments (0) |

Connecting to Dynamics NAV using the RSSBus OData Provider

This article will demonstrate how to set up the Dynamics NAV OData services and how to connect to these services using the RSSBus ADO.NET Provider for OData. While this article uses the ADO.NET provider, you can also connect using any of our other OData drivers (ODBC, JDBC etc.). Note that some Dynamics NAV instances require secure authentication that is not available with the FREE Community Edition.

Setting up the Dynamics NAV Web Services

The first thing we will need to do is enable the OData Services in the Dynamics NAV administration tool. Open up the Administrator tool and expand the OData Services node. If the Enable OData Services box is not selected, make sure you edit the settings to enable this. Also note the port number, this is required to connect to Dynamics NAV.

Now open up Dynamics NAV and search for 'Web Services'. This will show a list of all the pages that are currently exposed through the OData Service. By default there won't be any listed here so click the New button to add a new service. First choose the Object Type of the data you want to retrieve and then choose the Object ID for the data you are looking for.

For example, to get a listing of Customers, we can choose to expose a page with an Object ID of 22. You can give the Service any name you like; this name will appear as the table name when you retrieve data from Dynamics NAV with our provider. With that done, you are ready to connect to Dynamics NAV via OData.

Connecting to Dynamics NAV

The provider requires several properties in order to connect to Dynamics NAV; this information can be found in the Dynamics NAV Administration tool. We will first need the URL property. The URL will be in the following form:
http://<Server>:<WebServicePort>/<ServerInstance>/OData

The User and Password are the same as you would use to log into NAV normally. The Authentication Scheme will depend on how your instance of NAV is configured but you will most likely need to use either NTML or NEGOTIATE authentication if you use Windows Authentication. Once all the credentials are set, you should be able to connect to NAV and see the list of services you set up earlier.

Connecting to different companies: If you issue queries directly against the table, Dynamics NAV will only display data from the currently active company. If you want to retrieve data from a company which is not currently active, you will need to use the DirectURL pseudo-column.

You can set this pseudo-column to the full URL of an OData Service. We can use this to connect to a different company by changing the default OData URL to include the Company parameter and giving it the company's name. For example:

http://<Server>:<WebServicePort>/<ServerInstance>/OData/Company('RSSBus')

Then it is just a matter of providing the path to the particular web service you are interested in. We will need to SQL-encode the apostrophe in our URL, which is as simple as replacing it with two single apostrophes:

SELECT * FROM Customers WHERE DirectURL='http://<Server>:<WebServicePort>/<ServerInstance>/OData/Company(''RSSBus'')/Customers'

If you have any trouble getting connected, please contact support@rssbus.com

Posted On Monday, February 3, 2014 5:44 AM | Comments (0) |

Using the RSSBus Data Providers with the DevExpress Data Grid

The RSSBus ADO.NET providers are standards-based libraries that can be used with various third-party components and tools. In this article we use the Salesforce provider to bind data to the DevExpress Windows Forms and Web controls.

Windows Forms Controls

The code below shows how to populate a DevExpress chart, ChartControl1, with Salesforce data. The SalesforceDataAdapter binds to the Series property of ChartControl1; the Diagram property of WebChartControl1 defines the x- and y-axes as the column names.
using (SalesforceConnection connection = new SalesforceConnection("user=user;password=pass;"))
{
  SalesforceDataAdapter dataAdapter = new SalesforceDataAdapter("Select A.BillingState, SUM(O.Amount) AS SUM_O_Amount From  Opportunity O, Account A Group By A.BillingState", connection);
  DataTable table = new DataTable();
  salesforceDataAdapter1.Fill(table);
  Series series = new Series("Series1", ViewType.Bar);
  ChartControl1.Series.Add(series);
  series.DataSource = table;
  series.ValueDataMembers.AddRange(new string[] { "SUM_O_Amount" });
  series.ArgumentScaleType = ScaleType.Qualitative;	
  series.ArgumentDataMember = "BillingState";
  series.ValueScaleType = ScaleType.Numerical;
  ((SideBySideBarSeriesView)series.View).ColorEach = true;
  ((XYDiagram)ChartControl1.Diagram).AxisY.Visible = true;
  ChartControl1.Legend.Visible = false;
  ChartControl1.Dock = DockStyle.Fill;
}
You can use the DevExpress controls with any of the RSSBus providers to visualize data from a wide variety of sources.

Web Controls

The code below shows how to populate a DevExpress web control with Salesforce data. The SalesforceDataAdapter binds to the Series property of the chart, WebChartControl1; the Diagram property of WebChartControl1 defines the x- and y-axes as the column names.
using (SalesforceConnection connection = new SalesforceConnection(
"user=support@nsoftware.com;password=!rssbus;"))
{
SalesforceDataAdapter salesforceDataAdapter1 = new SalesforceDataAdapter(
"Select A.BillingState, SUM(O.Amount) AS SUM_O_Amount From  Opportunity O, Account A Group By A.BillingState", connection);

DataTable table = new DataTable();
salesforceDataAdapter1.Fill(table);
Series series = new Series("Series1", ViewType.Bar);
WebChartControl1.Series.Add(series);
series.DataSource = table;
series.ValueDataMembers.AddRange(new string[] { "SUM_O_Amount" });
series.ArgumentScaleType = ScaleType.Qualitative;
series.ArgumentDataMember = "BillingState";
series.ValueScaleType = ScaleType.Numerical;
((SideBySideBarSeriesView)series.View).ColorEach = true;
((XYDiagram)WebChartControl1.Diagram).AxisY.Visible = true;
WebChartControl1.Legend.Visible = false;
You can use the DevExpress controls with any of the RSSBus providers to visualize data from a wide variety of sources.

Posted On Monday, February 3, 2014 5:44 AM | Comments (0) |

Friday, May 31, 2013

Access QuickBooks from PHP

This article will explain how to connect to any of the RSSBus SQLBrokers from PHP. While the example will use the SQLBroker for QuickBooks, the same process can be followed for any of the RSSBus SQLBrokers.

  • Step 1: Download and install the SQLBroker for QuickBooks from RSSBus.
  • Step 2: Next configure the SQLBroker for QuickBooks to connect to your QuickBooks company file or online account. If you browse to the Help file in the Windows start menu, there is a link to the Getting Started Guidewhich walks you through setting up the SQLBroker for QuickBooks. Once you have installed the SQLBroker for QuickBooks and configured the connection to QuickBooks, you are ready to begin querying data from PHP.
  • Step 3: Now open the connection to the SQLBroker from PHP. Invoking the mysql_connect method will connect with the RSSBus SQLBroker server in PHP. As parameters to the mysql_connect method, you will need to supply the connection info to your RSSBus SQLBroker instance. This will contain the following:
    • The remote host location and port where the server is running. In this case "localhost" is used for the remote host setting since it is running on the local machine and the port is 3307.
    • The username in this case is root.
    • The password for the specified user.
    <?php
    $con mysql_connect("localhost:3307","root","my_password");
    ?>
  • Step 4: We are now ready to query our QuickBooks data. In this example, we will query the data from the Customers table in our QuickBooks database. The following steps will walk you through the example:
    • First, we must tell the RSSBus SQLBroker which database we want to connect to. In our example, we have configured the database name to be "QuickBooksSqlBroker".
    • Now we will use the mysql_query method to query the table. The results will be stored in the $result object.
    • Finally, we can walk through each row and column, printing the values to display in our PHP page.
    <?php
    mysql_select_db("QuickBooksSqlBroker", $con);
     
    $result mysql_query("SELECT * FROM Customers");
     
    while($row mysql_fetch_assoc($result)) {
    foreach ($row as $k=>$v) {
    echo "$k : $v";
    echo "<br />";
    }
    }
    ?>
  • Step 5: Finally close the connection to RSSBus SQLBroker when we are finished querying our data source.
    <?php
    mysql_close($con);
    ?>

Posted On Friday, May 31, 2013 8:11 AM | Comments (0) |

Wednesday, February 13, 2013

Analyze Salesforce data with PowerPivot

The ODBC protocol is used by a wide variety of Business Intelligence (BI) and reporting tools to get access to data from different databases. The RSSBus ODBC Drivers bring the same power and ease of use to non-traditional data sources such as Salesforce, Microsoft CRM, QuickBooks etc. Here we will use the RSSBus Salesforce Driver to import data into PowerPivot.

Importing Table Data

  • Step 1: Download and install both the RSSBus ODBC Driver for Salesforce and PowerPivot.
  • Step 2: Open Excel and go to the PowerPivot tab, and click on the 'PowerPivot Window' button, this will open PowerPivot.
  • Step 3: Click the 'External Data from Other Sources' button, this will open the 'Table Import Wizard'.
  • Step 4: Now select the data source type, in our case this will be the OLEDB/ODBC source.
  • Step 5: Here you can give a name for this connection and set the connection string. The connection string is usually of the form: "Provider=MSDASQL.1;Persist Security Info=False;DSN=RSSBus Salesforce Source". If you don't know your DSN name (the only piece of information that changes in this connection string), you can also use the "Build..." button to generate the same.

    Note: If you use the 'Build...' button to generate the connection string, make sure you select the 'Microsoft OLEDB Provider for ODBC Drivers' in the Provider tab. The Connection tab will then allow you to select a DSN available on your machine. If you don't see RSSBus Salesforce Source here, make sure the RSSBus Salesforce Driver is installed and a DSN has been configured.
  • Step 6: On continuing with the wizard, you will be prompted to choose how to import the data. Select the option to list the tables and you will see the list of tables available from Salesforce. You can then select the tables that you want to import.
  • Step 7: After closing the wizard, the data from your chosen tables will be available in PowerPivot.

Custom Query Import

Besides choosing the table to be imported, you can also specify a query to import specific columns from a table, you can even use 'WHERE' clauses to import just the relevant piece of information. To do this, instead of continuing in the wizard to the table list, choose to specify a query. This choice was mentioned in Step 6 above.

  • Step 1: Choose to specify a query.
  • Step 2: Here you can type any query you want, for example 'SELECT BillingStreet, Phone FROM Account'. You can validate the query and import data based on it. You can also start with a query like 'SELECT * from Account' and design the rest of it using the wizard from the Design button. This wizard allows you to modify the query and see the results it will produce.
  • Step 3: Click 'Finish' to import the data for your chosen query.

Posted On Wednesday, February 13, 2013 9:40 AM | Comments (0) |

Using RSSBus JDBC Drivers in ColdFusion

The RSSBus JDBC Drivers can be used in any environment that supports loading a JDBC Driver. In this tutorial we will explore using the RSSBus Salesforce JDBC Driver from within ColdFusion.

To begin, this tutorial will assume that you have already installed the RSSBus Salesforce JDBC Driver as well as Adobe ColdFusion v10, and that you already have a Salesforce account and access token (See our forum for more details about getting an access token).

  1. Add the RSSBus JDBC Driver to ColdFusion's lib directory.

    Copy the Salesforce JDBC Driver and lic file from "C:\Program Files\RSSBus\RSSBus JDBC Driver for Salesforce V3\lib" to "C:\ColdFusion10\cfusion\wwwroot\WEB-INF\lib".

    rssbus.jdbc.salesforce.jar
    rssbus.jdbc.salesforce.lic

    Note: If you do not copy the .lic file with the jar, you will see a licensing error that indicates you do not have a valid license installed. This is true for both the trial and full versions.

  2. Add the RSSBus Salesforce JDBC Driver as a data source.

    From the ColdFusion administrator interface, choose "Data Sources" from "Data & Services". Here you will want to "Add New Data Source". The data source name can be any name, provided it conforms to the ColdFusion variable naming conventions. For driver, choose "other", then click the "Add" button.

  3. Populate the driver properties.

    JDBC URL will need to be in the format: jdbc:salesforce:<connectionString>. For Salesforce, the connection string must include User, Password, and AccessToken, for example:

    jdbc:salesforce:User=username@test.com;Password=password;Access Token=AbCxYz

    Driver Class is: rssbus.jdbc.salesforce.SalesforceDriver

    The driver name is used to recognize the data source in the ColdFusion administration console.

  4. Test the connection to the data source

    You can now test the connection by pressing the check mark to the left of the RSSBusSalesforce data source you just created.

    The data source should report an "OK" status, and should now be ready for use.
  5. Creating the ColdFusion Markup File.

    Next, create a new ColdFusion Markup file (.cfm) and place it in the wwwroot ("C:\ColdFusion10\cfusion\wwwroot") directory for ColdFusion.

    The following code can be used to query the data source:

    <cfquery name="SalesforceQuery" dataSource="RSSBusSalesforce">
      SELECT * FROM Account
    </cfquery>

    And a CFTable can be used to quickly output the table in HTML:

    <cftable 
      query = "SalesforceQuery"
      border = "1"
      colHeaders
      colSpacing = "2"
      headerLines = "2"
      HTMLTable
      maxRows = "500"
      startRow = "1"/>
      
      <cfcol header="<b>ID</b>" align="Left" width=2 text="#Id#"/>
      <cfcol header="<b>Name</b>" align="Left" width=15 text="#Name#"/>
      <cfcol header="<b>Phone</b>" align="Center" width=12 text="#Phone#"/>
      <cfcol header="<b>Billing Street</b>" align="Center" width=25 text="#Billingstreet#"/>
      <cfcol header="<b>Billing City</b>" align="Center" width=15 text="#Billingcity#"/>
      <cfcol header="<b>Billing State</b>" align="Center" width=5 text="#Billingstate#"/>
    </cftable>

    Full code, including the HTML portion is available below:

    <html>
    <head><title>Hello World</title></head>
    <body>
    <cfoutput>#ucase("hello world")#</cfoutput>
    
    <cfquery name="SalesforceQuery" dataSource="RSSBusSalesforce">
      SELECT * FROM Account
    </cfquery>
    <cftable 
      query = "SalesforceQuery"
      border = "1"
      colHeaders
      colSpacing = "2"
      headerLines = "2"
      HTMLTable
      maxRows = "500"
      startRow = "1">
      
      <cfcol header="<b>ID</b>" align="Left" width=2 text="#Id#"/>
      <cfcol header="<b>Name</b>" align="Left" width=15 text="#Name#"/>
      <cfcol header="<b>Phone</b>" align="Center" width=12 text="#Phone#"/>
      <cfcol header="<b>Billing Street</b>" align="Center" width=25 text="#Billingstreet#"/>
      <cfcol header="<b>Billing City</b>" align="Center" width=15 text="#Billingcity#"/>
      <cfcol header="<b>Billing State</b>" align="Center" width=5 text="#Billingstate#"/>
    </cftable>
    </body>
    </html>
  6. Run the code!

    Running this code in the browser should produce the following output:

If you have any questions, comments, or feedback regarding this tutorial, please contact us at support@rssbus.com.

Posted On Wednesday, February 13, 2013 9:40 AM | Comments (0) |

Thursday, December 20, 2012

Integrate Apps with Gmail (.NET or Java).

Use the RSSBus Google ADO.NET Provider or JDBC Driver to read and search email messages on your Gmail accounts.

The RSSBus Google Data Provider for ADO.NET allows you to use the search capabilities in IMAP to query your Gmail account. This article will explore how to execute your own custom queries from your application. We will use the RSSBus Google Data Provider along with the Google Email demo to execute custom queries on a Gmail account.

  • Step 1: Navigate to the \demos - winform\googlemail\ folder in your installation directory and open either the C# or VB version of the demo. This article will use the C# version.
  • Step 2: Build and run the demo. Enter your credentials and click the Connect button.
  • Step 3: After connecting, the left-hand panel directly below the credentials will be populated with the available mailboxes associated with your Gmail account. Selecting a mailbox will retrieve the individual messages contained.
  • Step 4: The Advanced Search Options panel contains fields that allow you to search based on a number of different simple search criteria. You can search using as many of these criteria at one time as you like.
  • Step 5: Now, we will show off the powerful capabilities of the RSSBus Google Data Provider. The Data Provider contains a number of columns that let you refine your search to exactly fit what you need. More importantly, you can query based on multiple search criteria allowing you to easily create complex queries. To see this in action, we'll edit the demo code to update the query.
  • Step 6: For the purposes of demostration, we will hard-code the query we want to run. In the listSelectedMailbox() function, modify the query before you pass it to the GoogleDataReader object like so:
    using (GoogleConnection conn = new GoogleConnection(buildConnectionString())) {
    Query = "SELECT Id,From,Subject,Date,Size,Attachments FROM MailMessages WHERE () AND Mailbox=Inbox";
    GoogleDataReader data = executeCommand(conn, Query, nullnull);
    enableMailBoxSelected(true);
    refreshMailList(data);
    }
    Now we can add our search terms to the WHERE clause.
  • Step 7: For simple searches, such as seraching for all emails from a particular person, you can use the standard Columns in your WHERE clause. For example, searching for all emails from Twitter in our Inbox from a certain date, we can use the query: 
    SELECT * FROM MailMessages
    WHERE (FROM='Twitter' AND Date > '11-25-2012')
    AND Mailbox=Inbox

  • Step 8: To run the query, simply start the demo again and click on your Inbox. Only emails that match the criteria you specified will be returned.

  • For a look at more advanced queries see: Using SQL to query Gmail

Posted On Thursday, December 20, 2012 2:08 AM | Comments (0) |

Using SQL to Query Gmail

The Google Data Provider makes it easy to search email from a Gmail account. Instead of learning the details of the IMAP search command simply use the simple SQL syntax. You can also search based on multiple criteria at the same time. This article will demonstrate some advanced queries that are possible with the Data Provider.

  • For simple searches, such as searching for all emails from a particular person, you can use the standard Columns in your WHERE clause. For example, searching for all emails from Twitter in our Inbox from a certain date, we can use the query:
    SELECT * FROM MailMessages 
    WHERE (FROM='Twitter' AND Date > '11-25-2012') 
          AND Mailbox=Inbox
  • To find all emails with subject 'RSSBus Data Provider', we would use:
    SELECT * FROM MailMessages
    WHERE (Subject = 'RSSBus ADO.NET Data Provider') 
          AND (Mailbox = Inbox)
  • To find all sent emails between September 23rd, 2011 and September 23rd, 2012, we would use:
    SELECT * FROM MailMessages 
    WHERE (Date > '9-23-2011' AND Date < '9-23-2012') 
          AND Mailbox='[Gmail]/Sent Mail'
  • To find all the mail messages in our Inbox from either Twitter or anyone with 'Microsoft' in the name, we would use:
    SELECT * FROM MailMessages 
    WHERE (From='Twitter' OR From LIKE '%Microsoft%') 
          AND Mailbox=Inbox
  • To find the first 30 mail messages in our Inbox with Cc of "Microsoft" and Bcc of "Twitter", we would use:
    SELECT * FROM MailMessages 
    WHERE (CC='Microsoft' AND BCC ='Twitter') 
          AND Mailbox=Inbox 
    LIMIT 30
  • To find all unseen emails since October 1st, 2012 and everything from Twitter, we would use:
    SELECT * FROM MailMessages 
    WHERE (Flags LIKE '%UNSEEN%' AND Date > '10-1-2012' OR From='Twitter') 
          AND Mailbox=Inbox
  • The Data Provider contains a number of Pseudo-Columns that let you refine your search even further. Note that the SearchCriteria Pseudo-Column allows you to search directly using the IMAP Search command.

  • To directly use IMAP to find all the mail messages in our Inbox that haven't been seen and are flagged as important that were delivered before October 1st, 2012, we would use:
    SELECT * FROM MailMessages 
    WHERE (SearchCriteria='UNSEEN FLAGGED BEFORE 1-Oct-2012') 
          AND Mailbox=Inbox
    Note: The SearchCriteria takes the search terms in the syntax defined by the IMAP Search command.
  • As you have seen, any query you want to run on your Gmail account is possible with any of the RSSBus Google Data Tools. RSSBus offers Google integration as an ADO.NET Provider, JDBC Driver, SSIS Task, and an Excel Add-In.

Posted On Thursday, December 20, 2012 1:56 AM | Comments (0) |

Powered by: