RSSBus Articles

OLAP Reporting Directly from QuickBooks using ComponentOne OLAP for WinForms

Mon, 06 Dec 2010 00:00:00 GMT

By Rich Dudley, ComponentOne, December 6, 2010
This article was originally written and hosted on the ComponentOne Community Site

The RSSBus QuickBooks Connector abstracts the QuickBooks API into a familiar, database-like programming model. After installation, the QuickBooks connector is added as an ADO.NET Data Source, and QB now operates as a database of sorts. We can query "tables" containing customer, sales orders, invoice data and more. Since the data connection is two-way, we can also update data in QuickBooks from external sources. In this blog post, we'll go through the initial configuration and create a simple, interactive OLAP view from sales order data. This will be a zero-code sample, but future posts will detail more advanced features and will require some coding.

Installing RSSBus QuickBooks Connector

In order to program against QuickBooks, the development machine either needs to have QuickBooks installed, or QuickBooks needs to be available via a remote connection. QuickBooks Online is also supported.

Download the installer from http://www.rssbus.com/ado/quickbooks/, and run it. The installer will add the ADO.NET connection to our Data Source choices, and install a CHM reference file as well as sample projects.

There are three parts of the package - the System.Data.RSSBus.QuickBooks library, which we need to reference in our application; the QBConnector.exe, which is a web service that brokers the connections between our application and QuickBooks; and a series of schema files we don't need to adjust, but which may be updated to adjust for future releases of QuickBooks.

The first time we connect, we have to have QuickBooks running and the company file open to approve access to our application. Future connections don't require authorization.

Setting Up QuickBooks

Before we get started coding, we need to do a little setup with QuickBooks. I'm using the "retailer who tracks individual sales" sample database for this series. By default, these sample company files are created without a password. In order to have external connections to our company file, we have to set a password. Open the company file, then go to Company >> Change My Password to add a password to the file. The rest of this demo assumes you're leaving the User Name as "Admin" - if you change the User Name, make sure to adjust accordingly below. Keep track of this password--you'll need it to open the company file as well as connect with our application.

To allow external connections to our company file, we have to grant access to our application. The easiest way to grant these permissions (as well as test everything is installed correctly) is to run the sample application first.

Running the Sample RSSBus Project

The sample project installed with the RSSBus QuickBooks Connector is useful for testing whether the connector installation and QuickBooks configuration is correct, as well as being a stand-in application to set up application access permissions in our company file.

When using the RSSBus QuickBooks connector, connections to a QuickBooks company file are handled by an application called QBConnector.exe, which is installed with the RSSBus QuickBooks Connector. QBConnector.exe is a small web service that listens on port 2080 (by default). We provide some command line attributes to set the user name, password and application name. Note that this information is specific to a single application. If we want to run multiple applications against QuickBooks, we need to start multiple instances of the QuickBooks connector, setting different ports. We have to have QBConnector.exe running any time we want to connect to QuickBooks.

By default, QBConnector.exe is installed in %ProgramFiles%\RSSBus\RSSBus QuickBooks Data Provider\QBConnector. There is a readme file which explains the parameters we can use; the three main ones are the user name, password and application name. QBConnector.exe is a command-line application, and when we start QBConnector (seen below), we need to make sure to include at least these three parameters. QBConnector needs to be running on a machine with QuickBooks and our company file installed, so we either need to run it on a central machine or distribute it with out applications.

Now that QBConnector is running, QuickBooks is started and our company file is open, it's time to actually connect. The sample project can be opened from the Start menu, in the new RSSBus folder. Open the sample project, and adjust the connection string in web.config, setting the password and application name:

Hit F5 and the sample application will start. QuickBooks will display an alert. Choose the "Yes, always; ..." option and click Continue.

You'll be asked to confirm the application's access:

And the confirmation will be confirmed.

While we're approving access for our application, the sample app may time out. That's OK, stop debugging and rerun and the sample app should run:

If there's data in the grid, we're good to go.

Building the OLAP App

We're in the home stretch now. This part is going to seem long, but not really - there is no code, just a lot of configuration illustrations.

Open Visual Studio and start a new Windows Forms application. On the Data Sources tab, click Add New Data Source. Choose a Database type, and click Next.

Choose Dataset, and click Next.

Here, we want to create a New Connection.

Change the data source...

...select RSSBus QuickBooks Data Source and click OK.

Here's where we configure our data connection. The important parameters are"

Password = the password we set above in our company file
User = the administrative user for our company file (default = admin)

Application Name = the same application name we used above when we granted access.
Company File = the full path to the company file (QBW)

URL = The endpoint for QBConnector.exe. The default is http://localhost:2080, but this will need to be updated if running on a local machine or a different port.

The Schema Location is optional, this points to the RSD files which define the structure of the data in QuickBooks. Future changes in QuickBooks may mean future changes to these schema files, and perhaps these files may become version specific. At the current time, these schema files are accurate for all versions from 2006 to current.

Once we're done, we can choose to include sensitive data in the connection string, and we can confirm the connection string as well.

Save the connection string in the Application Configuration File.

Now, we select the database objects to include in the data set. Here we've selected all the available tables.

Click Finish. Visual Studio will import the schema and generate helper classes, and add Data Adapters to the Toolbox:

The default framework in Visual Studio is the Client Framework (a smaller version of .NET), but RSSBus currently needs the full framework. To change the framework, right-click on the project, go Properties >> Compile >> Advanced Compile Options, set Target Framework to .NET Framework 4.0

Almost done now! At this point, except for one small change, we're done with the QuickBooks connector configuration. We're now looking at the OLAP for WinForms functionality. From the toolbox, drag a C1OlapPanel onto the form.

Find the SmartTag in the upper right of the C1OlapPanel, and click it to extend the configuration tasks:

Make sure QuickBooks and the QBConnector are running and available, we need them in the next step.

Choose Data Source >> Other Sources >> Project Data Sources >> CarlsComputerShop >> Sales Orders (sorry no screenshot, can't seem to get one with the SmartTag open). The field selector will now populate with fields in Sales Orders "table".

The data bindings and adapters will automatically be added to the form

Hit F5 and watch the magic! Our application will start, and we can now slice-and-dice the Sales Order data.

Let's look at total spend, by item, by customer. Add Subtotal to the Values listbox, Items to the Row fields listbox, and CustomerName to the Column Fields listbox. Our grid will populate, but what we see is a count, not a dollar amount. Let's see if we can change that to a dollar summary.

Right-click Subtotal in the Values listbox, and choose Field Settings. We see the only option is Count. There is no option for Sum, so something is unexpected in how the schema is being interpreted by Visual Studio. Cancel out of this.

This means the data are being interpreted in a way that can only be counted - usually a string. Stop debugging, open CarlsComputerShop.xsd, scroll to find SalesOrders table schema, and highlight Subtotal.

Now, open the Properties tab. We see the DataType is set to System.String. Just as we suspected.

Change the DataType to System.Decimal, rebuild and start debugging again.

Using the same field choices, we now have dollar subtotals! But let's format the data for a better presentation.

To format, right-click Subtotal in the Values listbox, choose Field Settings >> Format, select Currency and OK.

Our data are now formatted as currency.

Another cool feature of the C1OlapPage is the ability to see the records which were included in the totals. Right-click any field with a value above $0, and a new window will open with the included data.

If we wanted to look at sales by month, rather than by customer, that's also very easy. Replace CustomerName with TransactionDate in the Column Fields box, and the grid will automatically rearrange itself.

By default, this is arranged by day. To arrange the data by month, right-click on TransactionDate in the Column Fields box, choose Field Settings, then the Format tab. We'll use a custom date format with has only the month and year, and click OK.

The grid is now grouped by month and year.

That's not a bad start without writing a line of code! To answer the obvious question, yes you can add a combobox so the end user can select the dataset, we have an example of that in the OLAP for WinForms documentation (see the product page at http://www.componentone.com/SuperProducts/OLAPWinForms/), and I'll have some more advanced tutorials here, too.

Summary

The RSSBus QuickBooks Connector make querying and updating QuickBooks data as simple as working with a database. When combined with the ComponentOne OLAP for WinForms, we have a powerful way to visualize data. This was just an introduction to the two components, in future posts we'll examine the more advanced features of both components.

Resources

RSSBus QuickBooks Connector: http://www.rssbus.com/ado/quickbooks/

ComponentOne OLAP for WinForms: http://www.componentone.com/SuperProducts/OLAPWinForms/

Charting Data from SalesForce

Tue, 17 Apr 2012 00:00:00 GMT

Charts can be essential when it comes to displaying critical information or making decisions based on the most recent information. Using the SalesForce ADO.NET Provider, you can easily chart your SalesForce Data in Visual Studio. This article demonstrates how easy it is to get your SalesForce data and databind it to a chart.

For this example, we will use the Microsoft Charting controls available in .NET 3.5. The exact same procedure outlined below can be used with any RSSBus ADO.NET Data Providers to chart data from the data source.

Step 1: Open Visual Studio and create a new Windows Forms project. For the purposes of the example, we will be using C#.
Step 2: Add a Chart control to the Form from the Toolbox.

Step 3: Add a reference to the SalesForce Data Provider.
Step 4: Create a method to retrieve the data using the SalesForce Data Provider. For example:

SalesForceConnection conn = new SalesForceConnection("User=username;Password=password;Access Token=Your_Access_Token;");
	SalesForceCommand comm = new SalesForceCommand("SELECT FirstName, AnnualRevenue FROM Lead " +
     "WHERE AnnualRevenue<>NULL AND FirstName<>NULL ORDER BY AnnualRevenue Desc LIMIT 10", conn);
	SalesForceDataAdapter da = new SalesForceDataAdapter(comm);
	DataSet dataset = new DataSet();
	da.Fill(dataset);

Step 5: DataBind the table of results to the chart.

chart1.DataSource = dataset;  
	chart1.Series[0].XValueMember = "FirstName";
	chart1.Series[0].YValueMembers = "AnnualRevenue";
	// Insert code for additional chart formatting here.
	chart1.DataBind();

Step 6: You can now run the project and chart your data. The completed chart may look something like this:

Chart Sample Project

To help you begin charting using the SalesForce Data Provider, download the fully functional sample project. You will also need the SalesForce ADO.NET Data Provider to make the connection. You can download a free trial here.

Accessing website data using ADO.NET

Mon, 24 Jan 2011 00:00:00 GMT

By i-programmer, January 24, 2011
This article was originally written and hosted on i-programmer

Introduction

Interacting with websites via the wide range of APIs that are on offer is attractive because it provides new possibilities but is slightly depressing because you have to find out how to do the job all over again for each API.

Now there is a better way. RSSBus has implemented a range of common APIs as ADO.NET data providers - Google, QuickBooks, Salesforce and SharePoint, with more planned.

What this means is that if you know how to work with databases using ADO.NET there is nothing more to learn. If you don't know ADO.NET then the good news is that it is easy and generalisable - and so worth learning.

In this article we are going to look at using the Google ADO.NET data provider but the general principles are the same for any ADO.NET data provider. We are are also going to implement a desktop Windows Forms application, but again the principles apply to any platform that can work with ADO.NET - WPF and ASP.NET. This means that you can use the same ideas to implement a desktop or web application.

Getting started with ADO.NET and the Google data provider is made even easier by the use of the designers that Visual Studio has - both the full version and the Express editions. Anything that you can do using one of the designers can be done using nothing but code and in many cases code is the more direct and efficient way to implement things - but when getting started a code generator is often simpler. In this case it also gives you a more powerful strongly typed set of classes to work with.

The Google data provider gives you access to a user's Google Calendar, Contacts, Docs, email, spreadsheets and more. To make it all work you need to download the Google Data Provider trial edition from RSSBus. Simply follow the instructions and install the data provider.

Connections

When you next start Visual Studio you can set up a new Google Data source using the Server Explorer. In a real application you would have to arrange to supply the user name and password programmatically, but the simplest way of trying things out is to supply your own Google user name and password - if you don't have one sign up for a new account, it's free after all.

Use the Add Connection command and select the RSSBus Google Data Source as the Data Source - if you can't find this then you haven't installed the data provider.

In the Add Connection dialog box you simply need to supply the password and user name for the Google account. These will be used to create a connection string which provides the details needed to make the connection to the Google account. Before moving on to work with the connection it is worth clicking the Test Connection button. This should come back with a "Test connection succeeded" message - if not you probably haven't entered the account details correctly or there is problem with the Internet connection.

If the connection tests OK then you can also run a query to examine the data. further. How to do this is well explained in the online help so let's move on to look at easy ways of creating some code.

Start a new Windows Forms Application C# application. You can create the same application in much the same way using Visual Basic if you want or and a WPF or ASP.NET project would be created using very similar steps.

The DataSet Designer

The next task is to use the DataSet designer to create a TableAdapter which is roughly speaking a DataSet complete with a DataAdapter. However the TableAdapter is more powerful in that it is strongly typed and has some additional methods. This is one time when using a designer is not only easier it actually generates better code.

Select the project, right click and select Add,New Item. When the Add New Item dialog box appears select DataSet - you can call it anything you like but to make clear how things work the default name is used DataSet1 in this example.

Once the DataSet has been created you can make use of the DataSet Designer to create the objects you need to work with the database.

Before we do that a quick reminder of how ADO.NET works.

There are a number of .NET classes that represent the data in memory the DataTable being the main one.

There is also a DataAdapter class that makes the connection between the in memory Table and the real database.

The DataAdapter uses a "connection string" to specify the details of the connection.

The new idea in ADO.NET 4 is the TableAdapter. This is a strongly typed DataTable plus a built in DataAdapter. It also has some other useful methods and properties that make is much easier to work with than the simple DataTable and the DataSet Designer is the simplest way of creating one.

If you now look at the RSSBus Google Data Provider in the Server Explorer you will see that there are Feeds, Views and Services. Feeds and Views are essentially data tables with feeds being read/write and views being read-only.

You can see that there are three data tables in the Feeds section - Calendar, Contact/Groups and Contacts.

We are going to connect to the Contacts table because the application is going to provide synchronisation between local and online contacts.

To create a DataAdapter for the Contacts table - simply drag and drop the Contacts table on the DataSet Designer. A default DataTable is created complete with all all of the columns that the Google data uses and a TableAdapter ready to be used to fill the DataTable. You can specify a SQL query as part of the DataAdapter to extract a subset of the records/columns but the default is to extract everything.

There is one small thing that we have to do to make the generated DataAdapter useful in this case. The connection string property has been set but the password has been left out as a security measure. In a real application we are probably going to have to manage the connection string in a more sophisticated way and its general form is:

Password=<password>;User=<user name>

Target Framework

Now we come to an interesting problem that is trivial but can be a puzzle if you don't know what is going on. If you try and run or build the the program as-is you will discover that you see an error message which says:

The type or namespace "RSSBus" does not exist in the namespace (are you missing an assembly reference)

No you are not missing an assembly reference and you might be surprised that as the project consists of nothing but generated code is generating an error message.

The reason for the error message is that the project by default is targeting the .NET Framework 4 Client Profile - which is a cut down project type designed to produce a very light weight application. Mostly it is exactly what you want but the ADO.NET application needs some additional assemblies and hence the error message.

To solve the problem and get rid of the error messages all you have to do is use Project Properties and change the Target Framework on the Application tab to ,NET Framework 4.

After this you can build or run the program without any problems and you don't have to make any manual changes as the warning message that pops up suggests.

Loading the data

Once again we have the choice of moving to code or continuing to work with Designers. This time switch to view the form in the Form Designer. At the top of the ToolBox you should see some new controls - DataSet1, ContactTableAdapter and TableAdapterManager.

If you don't see these controls build or run the project to create them before moving on.

Next simply drag-and-drop an instance of DataSet1 and ContactsTableAdapter onto the form. All this saves us is having to create instances of the default classes and it arguable that there is little to be gained from this way of working but - it can be done.

Now we do have to write some code but not much. As placing the two generated data controls on the form has created dataSet1 and contactsTableAdapter1 we can simply start using them in code.

Place a button on the form so that we can place some code in it's event handler.

To avoid having to give fully qualified names in the code add:

using System.Data.RSSBus.Google;

to the start of the program.

We need to set the connection string so that the DataAdapter can connect to the "database" i.e. the Google users name and password:

contactsTableAdapter1.Connection = new GoogleConnection(
	"Password=password;User=username");

We also need a DataTable of the right sort to store the records from the Google Contacts database. You can use a basic DataTable for this job but why do this when a strongly typed DataTable has been generated for you by the designer.

To create an instance of this strongly typed DataTable we have to use the DataSet:

DataSet1.ContactsDataTable MyTable= new DataSet1.ContactsDataTable();

Now we can fill the new table:

contactsTableAdapter1.Fill(MyTable);

The TableAdapter also has a GetData method which simply returns a DataTable of the correct type complete with data:

DataSet1.ContactsDataTable MyTable = contactsTableAdapter1.GetData();

At this point MyTable contains a set of record objects each record object has properties corresponding to the columns of the table and they are all of the correct type. This is the advantage of using a strongly typed DataTable.

So for example to get the FullName of the first record we could use:

string name=MyTable[0].FullName;

Notice that now we have a strongly typed table to get its advantages we have to use a strongly typed Row object.

For example to step thought the collection with a for each loop using a general DataRow type you would have to write something like:

foreach (DataRow row in MyTable) {
	name = (string) row["FullName"];
}

Notice tht you have use an index to retrieve the field and you have to cast it to the correct data type - string.

Now compare this to the strongly typed way of doing the same job:

foreach(DataSet1.ContactsRow row in MyTable) {
	name = row.FullName;
}

This works with a FullName property which is already a string so no cast is needed.

Once you have seen this sort of coding in action you should have no problem in understanding that it is a much better way to work.

Update

Now we come to the more difficult problem of updating a table. One the DataTable is loaded with data using the Fill or GetData methods you can work with the rows collection in the usual way. That is you can modify the data, delete rows etc. with out having to worry about what is happening to the data stored in the database or in the Google account in this case. However what if you want to update the external data with the changes you have made in memory?

This is where the TableAdapters Update method comes into play. In principle the Update method should be generated automatically when you create the TableAdapter but in most cases it finds it too difficult and leaves the method undefined. Fortunately it is fairly easy to create an Update method manually.

The key is to supply a SQL command that performs the update. To do this you have to supply a WHERE clause that matches up the data in the DataTable with the data in the database. In most cases the WHERE simply matches records on the key value. You also need a SET clause which stores the new data in the fields you want to change. So for example:

UPDATE table
SET field1=value1
WHERE (key=value2)

This will perform an update on table and will change only the record with key=value2 and it will change field1 to value1.

This easy enough but the TableAdapter will apply an UPDATE command to every record in the row collection clearly the UPDATE command has to use variables not constants to indicate what each field is to be updated to. What ADO.NET does is to introduce variables indicated by an @ at the front of the row property name. So for example, each ContactsRow object has a FullName property and to update the external record you would use something like

UPDATE Contacts
SET FullName=@FullName
WHERE (Editlink=@Editlink)

This will update the Contacts table matching up in memory records that have changed to table records that need to be changed by matching Edltlink to the Editlink property - which is the key for this table and so unique. Once the in memory and table records are matched the FullName field is updated by the contents of the FullName property of the row object.

To create the Update method open the DataSet editor and examint he properties of the ContactsTableAdapter. Click on Update command and select New. Next enter the SQL command as listed above and that's all you have to do.

You can now write code like:

MyTable[0].FullName = "I Programmer";
contactsTableAdapter1.Update(MyTable);

and the Google contacts data table will be updated. Of course there is always the possibility that the update will fail for one reason or another so use a Try-Catch.

Notice that only the FullName field is updated even if you change other fields in the in memory record. To change other fields simply add them to the SQL command.

Where next

You should be able to extend the ideas explained in this article to other tables in the Google provider and even to other providers. It is a neat approach to working with web APIs. The DataSet Designer also makes life easier and more reliable. You can use its strongly typed DataSets to work with both LINQ and the Entity Framework but in most cases simply getting, modifying and updating data is all you need and ADO.NET on its own does this very well. It really is only worth thinking of more complex approaches when joins and intertable relationships have to be handled.

Use PowerShell to List Google Docs

Thu, 03 Mar 2011 00:00:00 GMT

Are you looking for a quick and easy way to access your Google Docs from PowerShell? The Google Data Provider provides an easy-to-use ADO.NET interface that you can take advantage of with your PowerShell Scripts. Simply use the included SQL like .NET objects (GoogleConnection, GoogleCommand, GoogleDataAdapter, etc) in your PowerShell scripts to connect to your Google Apps accounts and synchronize, automate, download, and more!

Using the Google Data Provider in PowerShell to List Google Docs:

# Load the Google Data Provider assembly 
[Reflection.Assembly]::LoadFile("C:\Program Files\RSSBus\RSSBus Google Data Provider\lib\System.Data.RSSBus.Google.dll")

# Connect to Google 
$constr = "User=[username];Password=[password]"
$conn= New-Object System.Data.RSSBus.Google.GoogleConnection($constr)
$conn.Open()

$sql="SELECT Name, AuthorName, Type, Updated, Weblink from Documents"

$da= New-Object System.Data.RSSBus.Google.GoogleDataAdapter($sql, $conn)
$dt= New-Object System.Data.DataTable
$da.Fill($dt)

$dt.Rows | foreach {
	Write-Host $_.updated $_.name
}

Listing is only the first step. With full CRUD support, you can use the Google Data Provider to easily upload and download documents as well. The following bit of PowerShell code downloads one of the documents listed above:

Download a file from Google Docs:

$cmd= New-Object System.Data.RSSBus.Google.GoogleCommand("DownloadDocument", $conn)
$cmd.CommandType= [System.Data.CommandType]'StoredProcedure'
$cmd.Parameters.Add( (New-Object System.Data.RSSBus.Google.GoogleParameter("@Type", "TXT")) ) 
$cmd.Parameters.Add( (New-Object System.Data.RSSBus.Google.GoogleParameter("@Name", "myfile")) ) 
$cmd.Parameters.Add( (New-Object System.Data.RSSBus.Google.GoogleParameter("@LocalFile", "d:\myfile.txt")) ) 
$reader = $cmd.ExecuteReader()

Likewise, calling the UploadDocument stored procedure allows your scripts to upload documents directly to Google Docs.

As you can see, the Google Data Provider provides a hassle-free way to access the features of Google Apps directly from PowerShell script, and eliminates the headache involved with authentication, security, etc. Happy scripting!

Hooking into Google Apps With RSSBus Google Data Provider

Tue, 30 Nov 2010 00:00:00 GMT

By Mike Gold, November 30, 2010
This article was originally written and hosted on c-sharp corner

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 RSSBus Google Data Provider, which works like any other Data Provider, as you will soon find out.

Installing the Software

Installing the RSSBus 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, RSSBus 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, RSSBus has alternative licensing available that does not require registry access for deployment such as shared server support.

The Data Provider Schema

The RSSBus 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 RSSBus 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 RSSBus Data Source as shown in figure 1:

Figure 1 - Choosing the RSSBus 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 RSSBus 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 RSSBus 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 RSSBus 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 RSSBus Data Provider.

The Code

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

Listing 1 - Binding the RSSBus 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)\RSSBus\RSSBus Google Data Provider\db;User=mgold45@gmail.com;Password=csharpcornerrocks" providerName="System.Data.RSSBus.Google"/>

As you can see, there is little difference between binding a Sql Server data source and a RSSBus 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.

Where	AuthorEmail	Description	EndTime	StartTime	Id	Title
Liberal Arts and Science Academy (LASA) HS at 7309 Lazy Creek Drive,. Austin, TX 78724	mgold45@gmail.com	Hi Bob, Just wanted to pass this on as an opportunity for the Robot Club here in Austin. Best, -Mike (an education based community service announcement) What : Judges Needed for FIRST LEGO League (FLL) Robotics Tournament When : Saturday, November 13th & Sunday, November 14th 7AM - 5PM (Volunteer one day or both days) Where : Liberal Arts and Science Academy (LASA) HS at 7309 Lazy Creek Drive,. Austin, TX 78724 On Saturday, November 13 and Sunday, November 14th, 2010, the Liberal Arts and Scienc... P	11/14/2010 12:00:00 AM	11/13/2010 12:00:00 AM	http://www.Google.	Judges Needed for FIRST LEGO League (FLL) Robotics Tournament

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=foo@gmail.com;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.

Publishing LightSwitch Applications

Wed, 23 Mar 2011 00:00:00 GMT

This article walks through the process of deploying a LightSwitch application that uses any of the RSSBus ADO.NET Data Providers to a new host.

There is a problem in deploying a LightSwitch application to another server or Web host. It does not automatically port some of the settings and dependencies needed for it to work on a machine different from the one it was developed on. If you are using a custom ADO.NET Data Provider such as our ADO.NET Data Providers, then your dlls and <system.data> config settings will not be added to the finished product. Here is what you need to do to get it to work on the new machine.

Modify the Web.config

On your local machine, there is a machine.config file where your Data Providers are registered. You will need to copy some settings from this file into your web.config file.

Step 1: Open a window and browse to C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config.
Step 2: Open machine.config with a text editor.
Step 3: Search this file for the <system.data> section. It should look like this:

<system.data>
	<DbProviderFactories>
		  ...
		<add name="RSSBus QuickBooks Data Provider" .../>
		  ...
    </DbProviderFactories>
</system.data>

Step 4: Add the full system.data section to the web.config file in your published LightSwitch application.
Step 5: Remove all ADO.NET Data Providers that your application does not use or that come installed with Visual Studio.

Add Missing DLLs

Step 1: In your published LightSwitch application, add any missing dll files to the bin directory. These must be .NET 4.0 dlls.
Step 2: For example, if you are using the RSSBus QuickBooks Data Provider, you would need to add the System.Data.RSSBus.QuickBooks and System.Data.RSSBus.QuickBooks.Entities dlls. These can be found in C:\Program Files (x86)\RSSBus\RSSBus QuickBooks Data Provider\lib\4.0

Connect to QuickBooks From LightSwitch Applications

Wed, 06 Apr 2011 00:00:00 GMT

The RSSBus Data Providers for ADO.NET include out-of-the-box support for Microsoft Visual Studio LightSwitch. In this demo, we will show you how to create a LightSwitch application that connects with QuickBooks and displays QuickBooks account data through the QuickBooks ADO.NET Data Provider. This exact same procedure outlined below can be used with any RSSBus ADO.NET Data Providers to integrate data with LightSwitch applications.

Before We Begin

QuickBooks provides no connectivity to its data over a network. RSSBus provides a utility called the Remote Connector that allows our QuickBooks ADO.NET Data Provider to communicate with QuickBooks across remotely. The Remote Connector must be installed and running on the host QuickBooks machine before completing this tutorial. Find more about setting up the Remote Connector at this help page on our website.

Step 1: Open Visual Studio and create a new LightSwitch Project.
Step 2: Add a new Data Source of type "Database".
Step 3: Change the data source to an RSSBus QuickBooks Data Source.
Step 4: Enter the connection details for your QuickBooks machine. In this example, we are using a separate machine to host QuickBooks on.

Step 5: Once you have your connection details set, select the tables and views you would like to add to the project.
Step 6: Now add a screen to the project. While adding the screen, select which table or view to associate with the screen. In our example we are using a Grid screen and the Accounts table.

Step 7: Run the LightSwitch application. The screen will automatically execute and populate with data.

LightSwitch Sample Project

To help you with getting started using the QuickBooks Data Provider within Visual Studio LightSwitch, download a sample project. You will also need the QuickBooks ADO.NET Data Provider to make the connection. You can download a free trial here.

Note: Before running the demo, you will need to change your connection details. Right-click on the QuickBooks Data Source and select Update Data Source. In the wizard window that pops up, click the Previous button, and enter the connection details for your QuickBooks host.

LINQ to QuickBooks

Thu, 07 Apr 2011 00:00:00 GMT

In this demo, we will demonstrate how to use LINQ to access tables in QuickBooks through the QuickBooks ADO.NET Data Provider. To do this we will LINQ to Entity Framework, which is used to generate the connection and can be used with any RSSBus ADO.NET Data Providers to access data via LINQ.

Step 1: Open Visual Studio and create a new project.
Step 2: Right click on the project and choose to add a new item. Select to add an ADO.NET Entity Data Model.

Step 3: Choose to generate from database and click next.
Step 4: Add a new Data Connection, and change your data source type to "RSSBus QuickBooks Data Source".
Step 5: Enter your data source connection information.

Step 6: If saving your entity connection to App.Config, set an entity name. In this example we are setting QuickBooksEntities as our entity connection to App.Config.
Step 7: Select any tables or views you would like to include in the model in addition to a model name.

Using the entity you created in Step 6, you can now perform Select, Update, Delete, and Insert commands. For example:

QuickBooksEntities qbeContext = new QuickBooksEntities();

var invoiceQuery = from invoice in qbeContext.Invoices
	orderby invoice.CustomerName
	select invoice;

foreach (var result in invoiceQuery) {
	Console.WriteLine("{0} {1} ", result.ID, result.CustomerName);
}

Demo: Migrate Data from Salesforce to SharePoint

Wed, 04 Jan 2012 00:00:00 GMT

Easy Data Migration from Salesforce to SharePoint

RSSBus Data Providers are development tools that simplify a large variety of tasks. In this demo we will show how you can use the RSSBus data providers to transfer data from one source to another.

Migrating data between sources is often tedious, but this is made simpler by using the RSSBus data providers which abstract out vendor-specific API's into a standard interface. Mapping columns from source to destination can in fact take as little as a dozen lines of code.

The Salesforce.com to SharePoint Migration Assistant demonstrates how RSSBus Data Providers convert data from source to destination in an orderly process: enter your credentials, select the tables you want to migrate, map the columns, and go. The code in the demo is also highly modifiable. Since all the Data Providers use a common interface, changing the source or destinations to something else (e.g., QuickBooks, Google) is a matter of replacing the providers.

This sample is just a simple demo where we copy every row in the source table to the destination. Several improvements can be made that make this more robust. As an example it is possible to update the destination with only the most recently modified data in the source. Implementing this is matter of altering the query the demo uses to pull down data from the source by adding to it some sort of condition.

Setting Up Pseudo Columns with LightSwitch.

Fri, 26 Aug 2011 00:00:00 GMT

There are a number of ways that you might want to refine your query results that are difficult to do with current table columns; pseudo columns can greatly simplify this. This example shows how to set up a connection using any of the RSSBus ADO.NET data providers and how to expose pseudo columns.

First install the RSSBus product(s) you have purchased. After running the setup, open an instance of Visual Studio LightSwitch. In this tutorial we will make a C# LightSwitch application with the Salesforce Data Provider.

What are pseudo columns?

Pseudo columns are used in WHERE clauses just like regular columns but are not present in any table. As an example you could create a SELECT statement with a psuedo column like this:

SELECT * FROM Accounts WHERE maxresults='10'

Passing the pseudo columns to the data provider allows for more granular control over the rows that get returned. Since they are special entities for Tables and Views they do not exist in the data table returned and cannot be read. For a list of the pseudo columns available with a particular Table or View confer with the help file supplied with the RSSBus Data Provider. The pseudo columns are not available by default however so the rest of this tutorial will show you how to add them into your project.

Setting Up a LightSwitch Project

Step 1: Go to File -> New -> Project (or Ctrl+Shft+N) and select a LightSwitch application as shown in the photo.

Step 2: Right Click on the "Data Source" folder of the project and select to add a new source

Then select a Database application:
Step 3: In the Connection Properties Wizard enter your credentials; in this case since we are connecting to Salesforce you will need to put in your user name, password, and access token. To expose the pseudo columns all you need to do after this is enter *=* in the Pseudo Columns tabs found under Misc:

Step 4:

Accounts

After this if you need more help setting up a LightSwitch Screen, we have another Knowledge Base entry that shows you how to do this here. Once you have created a Screen you can now query from the available columns and pseudo columns.

Using the Pseudo Columns

Step 1: Once you have created the screen you will see an Edit Query link on the designer page; click on this link.
Step 2: Select Add Filter from the Filter section and select from the available column and pseudo columns you can query against:

That's it!

That is all it takes to set up a project that uses pseudo columns. This procedure common among all RSSBus Data Providers and additionally can be used to expose pseudo columns with Entity Framework projects.

If you have any further questions about this you can email our support team.

Faster Access to QuickBooks using the QuickBooks Data Provider

Wed, 04 May 2011 00:00:00 GMT

The RSSBus QuickBooks Data Provider for ADO.NET makes it easy to get data from QuickBooks. But what if you need to return tens of thousands of records? How can you get all this information without there being a huge delay in the application? The answer to this problem is simple - use the built-in caching capabilities of the QuickBooks ADO.NET Data Provider.

Creating the Cache

Accessing the local cache is much faster than accessing data directly from QuickBooks. To do so, you will first need to populate your cache, then change your connection string to access the cache instead of the live data source.

Step 1: Open Visual Studio and create a new C# project.
Step 2: Add a reference to System.Data.RSSBus.QuickBooks to the project and import it to your main class.
Step 3: Add the following code sample to your project:

string connectionString = "URL=http://myquickbooksserver:2080;"
	"Password=password;" +
	"User=user;" +
	"Application Name=My QuickBooks Application;" +
	"Auto Cache=True;" +
	"Offline=False;" +
	"Cache Location=C:\\myfolder\\mycache.db;"

QuickBooksConnection conn = new QuickBooksConnection(connectionString);
QuickBooksDataAdapter dataAdapter = new QuickBooksDataAdapter("Select * From Invoices", conn);
DataTable table = new DataTable();
dataAdapter.Fill(table);

Step 4: Modify the connection string so that it fits your QuickBooks connection settings. Remember that you will not require a URL, Password, and User if QuickBooks is installed and running on the same machine.
Step 5: Run the code sample without debugging to get it to execute faster.
Step 6: If you are connecting to multiple company files, you will need to create an individual cache for each company file.

Now that you have a cache, you can make queries to it. Not only that, but because the internal cache is not limited by the QuickBooks API. This allows you to make more advanced SQLite SELECTS to it that are not available when accessing directly from the Data Source. Here's how to access the cache:

Step 1: Modify the Offline=False portion of the connection string to Offline=True.
Step 2: Modify Query Passthrough=False to Query Passthrough=True, or add it to the connection string if it did not exist.

Congratulations! You can now get data directly from the cache. Try comparing the performance between getting data from the cache and directly from QuickBooks. You will find that it is much faster to get cached data than data directly from QuickBooks.

Keeping the Cache up to Date

Once you have a cache, you do not need to get everything from QuickBooks again to update the cache. Instead, you can use the TimeModified column available on every table in the RSSBus Data Provider. However, since > and < syntax handling is not available in QuickBooks, you will need to use the StartModifiedDate and EndModifiedDate pseudo-columns. Here's a modified version of the previous code sample illustrating how:

string connectionString = "URL=http://myquickbooksserver:2080;"
// ...
connectionString += "Cache Location=C:\\myfolder\\mycache.db;"

// First, get the most recent modified time from the cache. 
// Set Query Passthrough to true to run directly against SQLite.
string connstr = connectionString.Replace("Offline=False", "Offline=True");
connstr = connstr.Replace("Query Passthrough=False", "Query Passthrough=True");
QuickBooksDataAdapter dataAdapter = new QuickBooksDataAdapter("SELECT max(TimeModified) as MaxTime FROM Invoices", connstr);
dataAdapter.Fill(table);

// Now set that to be our most recent date. If there are no 
// rows, set date = 1970-01-01.
string date;
try {
	date = (String)table.Rows[0]["MaxTime"];
} catch (Exception ex) {
	date = "1970-01-01";
}
table.Rows.Clear();
table.Columns.Clear();

// Now search the live data source for only modified dates 
// as new or newer than the one we found. Filling a table 
// will cause the cache to be updated with the results when 
// Auto Cache=True in the connection string.
connstr = connstr.Replace("Offline=True", "Offline=False");
connstr = connstr.Replace("Query Passthrough=True", "Query Passthrough=False");
QuickBooksConnection conn = new QuickBooksConnection(connstr);
dataAdapter = new QuickBooksDataAdapter("SELECT * FROM Invoices WHERE StartModifiedDate = '" + date + "'", conn);
dataAdapter.Fill(table);

Automating the Caching Process

There is just one more step to keeping your cache up to date. You will simply need to make an automatic process that updates the cache on a regular basis. We are including a sample project that uses the Timer class to regularly update the cache based on the table names passed to it. Click here to download the sample project. You will also need to install the QuickBooks ADO.NET Data Provider to run the demo. You can download a free trial here.

Faster Access to Salesforce.com using the Salesforce Data Provider

Mon, 11 Jul 2011 00:00:00 GMT

The RSSBus Salesforce Data Provider for ADO.NET makes retrieving data from Salesforce a breeze. Sometimes you may need to return tens of thousands of records. How do you get all this information without huge delays? The caching features of the Salesforce ADO.NET Data Provider make this problem a cinch.

Creating the Cache

Queries from a local cache bring data back much faster than directly from Salesforce.com. Populating and accessing the cache requires setting just two values, Offline and Auto Cache, in a connection string.

Step 1: Open Visual Studio and create a new C# project.
Step 2: Add a reference to System.Data.RSSBus.SalesForce to the project and import it to your main class.
Step 3: Add the following code sample to your project:

string connectionString = 
	"User=user;" +
	"Password=password;" +
	"Access Token=token" +
	"Offline=False;" +
	"Auto Cache=True;" +
	"Cache Location=C:\\mycache.db;";

SalesForceConnection conn = new SalesForceConnection(connectionString);
SalesForceDataAdapter dataAdapter = new SalesForceDataAdapter("Select * From Accounts", conn);
DataTable table = new DataTable();
dataAdapter.Fill(table);

"Offline=False;"

"Auto Cache=True;"

Step 4: Modify the connection string so that it fits your Salesforce connection settings.
Step 5: Run the code sample without debugging to get it to execute faster.

As an optimization measure, it is possible Salesforce has returned only some of the rows matching your query, with additional rows waiting to be fetched. Salesforce appends query results with a column entitled "QueryLocator". The value in the "QueryLocator" column for all rows is the same, provided more results need fetched. To fetch more results via the QueryLocator insert the following code:

String queryLocator = null;

do {
	String query = "SELECT * FROM Accounts";
		
	if (!string.IsNullOrWhiteSpace(queryLocator)) {
		query += string.Format(" WHERE QueryLocator='{0}'", queryLocator);
	}
	
	SalesForceConnection conn = new SalesForceConnection(connectionString);
	SalesForceDataAdapter dataAdapter = new SalesForceDataAdapter(query.ToString(), conn);
	dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
	dataAdapter.Fill(table);

	if (table.Rows.Count > 0) {
		int lastRow = table.Rows.Count - 1;
		queryLocator = table.Rows[lastRow]["QueryLocator"].ToString();
	}
} while (!string.IsNullOrEmpty(queryLocator));

Now that you have a cache, you can make queries to it. Not only that, but the internal cache is not limited by the Salesforce API. This allows you to make more advanced SQLite SELECT calls that are not available when accessing directly from the Data Source. Here's how to access the cache:

Step 1: Set the Offline connection value to "True" to direct the adapter to the cache.
Step 2: To disable the SQL Parser from performing error checking, set the Query Passthrough connection value to "True", or add it to the connection string if it did not previously exist.

Congratulations! You can now get data directly from the cache. Try comparing the performance between getting data from the cache and directly from Salesforce. You will find that it is much faster to get cached data than data directly from Salesforce. In addition, unlike SalesForce, the cache can always return all rows of data.

Keeping the Cache up to Date

Once you have a cache, you do not need to get everything from Salesforce again to update the cache. Instead, you can use the LastModifiedDate column available on every table in the RSSBus Data Provider. However, since comparative operators (aside from equals) are not available in Salesforce, you will need to use a SOQLWHERE condition in your SELECT statement.

Here's a modified version of the previous code sample illustrating how:

// First, get the most recent modified time from the cache. 
// Set Query Passthrough to true to run directly against SQLite.
connectionString = connectionString.Replace("Offline=False", "Offline=True");
connectionString = connectionString.Replace("Query Passthrough=False", "Query Passthrough=True");
String query = "SELECT max(LastModifiedDate) as MaxTime FROM Accounts";
SalesForceDataAdapter dataAdapter = new SalesForceDataAdapter(query, connectionString);
dataAdapter.Fill(table);

// Now set that to be our most recent date. 
// If there are no rows, set date to beginning of epoch to retrieve all rows.
string date;
if (datatable.Rows.Count > 0)
	date = (string)datatable.Rows[0]["MaxTime"];
else
	date = "1970-01-01 00:00:00.000";

DateTime dateTime = DateTime.ParseExact(date, "yyyy-MM-dd HH:mm:ss.fff", null);
date = string.Format("{0:s}Z", dateTime); //SOQL-specific date formatting

// Now search the live data source for only modified dates as new or newer than the one we 
// found. Filling a table will cause the cache to be updated with the results when 
// "Auto Cache=True" in the connection string.
connectionString = connectionString.Replace("Offline=True", "Offline=False");
connectionString = connectionString.Replace("Query Passthrough=True", "Query Passthrough=False");
SalesForceConnection conn = new SalesForceConnection(connectionString);
String query = String.Format("SELECT * FROM Accounts WHERE (SOQLWHERE = 'LastModifiedDate>={1}')", date);
dataAdapter = new SalesForceDataAdapter(query, conn);
dataAdapter.Fill(table);

Automating the Caching Process

There is just one more step to keeping your cache up to date. You will simply need to make an automatic process that updates the cache on a regular basis. We are including a sample project that uses the Timer object to regularly update the cache based on the table names passed to it. Click here to download the sample project. You will also need to install the Salesforce ADO.NET Data Provider to run the demo. You can download a free trial here.

Accessing SharePoint Online with the RSSBus SharePoint Data Provider.

Wed, 03 Aug 2011 00:00:00 GMT

This article walks through the process of using the SharePoint Data Provider and authenticating with SharePoint Online / Office 365. This tutorial covers how to perform this programmatically and with the Visual Studio Server Explorer.

In addition to accessing lists from local installations of SharePoint server, the SharePoint Data Provider also supports accessing lists from SharePoint Online. SharePoint Online and Office 365 authentication requires a cookie based authentication. To connect to SharePoint Online please follow the steps below.

Authenticating to SharePoint Online in your program.

Step 1: Point Internet Explorer to 'http://YOUR_DOMAIN.sharepoint.com/TeamSite' which will redirect you to an authentication page. Be Sure to check the 'Keep-Me-Signed-In' option at login.

Step 2: Now that you have authenticated to SharePoint Online through your web browser, the cookie that was retrieved must be passed to the Auth Cookie connection setting. If you used Internet Explorer to log into SharePoint Online then you set Auth Cookie to '*' and the Data Provider will pick up the cookie automatically from IE. Here is an example of how to set this in your application:
DataTable table = new DataTable(); String command = "SELECT * FROM Calendar"; SharePoinConnection conn = new SharePointConnection( "URL=http://YOUR_DOMAIN.sharepoint.com/TeamSite; Auth Cookie='*'"); SharePointDataAdapter adapter = new SharePointDataAdapter(command, conn); dataAdapter.Fill(table);

Authenticating with Server Explorer.

To set the 'Auth Cookie' with the Server Explorer in Visual Studio you will have to have completed Step 1 from above first. Then after the cookie has been retrieved you can add new connection:

Now you need to add the URL and wildcard charater, '*', and the explorer will construct the connection string for you:

After following the steps above, you can use the provider just like you would when connected to a local SharePoint server. This procedure can also be applied to Entity Framework and Microsoft LightSwitch projects.

If you have any further questions about this you can email our support team.

Connect SalesForce to SQL Server through SSIS

Tue, 12 Apr 2011 00:00:00 GMT

Using SQL Server as a backup for critical business data provides an essential safety net against loss. In addition, backing up data to SQL Server enables business users to more easily connect that data with features like reporting, full-text search, analytics, and more.

This example demonstrates how to use the SalesForce ADO.NET Data Provider inside of a SQL Server SSIS workflow to transfer data directly from SalesForce account into a Microsoft SQL Server database.

Step 1: Open Visual Studio and create a new Integration Services Project.
Step 2: Add a new Data Flow Task from the Toolbox onto the Control Flow screen.
Step 3: In the Data Flow screen, add an ADO NET Source and an OLE DB Destination from the Toolbox.

Step 4: Add a new Data Connection, and select your provider as .NET Providers\RSSBus SalesForce Data Provider.
Step 5: Enter the connection details for your SalesForce account. In this example, we are using a SalesForce Developer Account for this example. Remember to obtain the Access Token for the account.

Step 6: Once you have selected the new connection, specify the query to use for the data extraction. In this case, the query is going to extract the information for the SalesForce accounts.

Step 7: Close the ADO NET Source wizard. Now drag the arrow below the source to connect it to the OLE DB Destination.
Step 8: Open the OLE DB Destination and add a New Connection. Enter your server and database information here. In this example, we are using a separate machine running SQLExpress.
Step 9: Set your Data access mode to "table or view" and select the table or view to populate in your database.
Step 10: Configure any properties you wish to on the Mappings screen.

Step 11: Close the OLE DB Destination Editor and run the project. After the SSIS Task has finished executing, your database will be populated with data obtained from SalesForce.

SSIS Sample Project

To help you with getting started using the SalesForce Data Provider within SQL Server SSIS, download the fully functional sample project. You will also need the SalesForce ADO.NET Data Provider to make the connection. You can download a free trial here.

Note: Before running the demo, you will need to change your connection details to fit your environment as detailed in Step 5 and Step 8.

Connect SharePoint to SQL Server through SSIS

Tue, 12 Apr 2011 00:00:00 GMT

This example demonstrates how to use the SharePoint ADO.NET Data Provider inside of a SQL Server SSIS workflow to transfer data directly from a SharePoint server into a Microsoft SQL Server database.

Step 1: Open Visual Studio and create a new Integration Services Project.
Step 2: Add a new Data Flow Task from the Toolbox onto the Control Flow screen.
Step 3: In the Data Flow screen, add an ADO NET Source and an OLE DB Destination from the Toolbox.

Step 4: Add a new Data Connection, and select your provider as .NET Providers\RSSBus SharePoint Data Provider.
Step 5: Enter the connection details for your SharePoint machine. In this example, we are using a SharePoint blog page.

Step 6: Once you have selected the new connection, specify the query to use for the data extraction. In this case, the query is going to extract the complete information for the SalesForce accounts.

Step 7: Close the ADO NET Source wizard. Now drag the arrow below the source to connect it to the OLE DB Destination.
Step 8: Open the OLE DB Destination and add a New Connection. Enter your server and database information here. In this example, we are using a separate machine running SQLExpress.
Step 9: Set your Data access mode to "table or view" and select the table or view to populate in your database.
Step 10: Configure any properties you wish to on the Mappings screen.

Step 11: Close the OLE DB Destination Editor and run the project. After the SSIS Task has finished executing, your database will be populated with data obtained from SharePoint.

SSIS Sample Project

To help you with getting started using the SharePoint Data Provider within SQL Server SSIS, download the fully functional sample project. You will also need the SharePoint ADO.NET Data Provider to make the connection. You can download a free trial here.

Note: Before running the demo, you will need to change your connection details to fit your environment as detailed in Step 5 and Step 8.

Connect QuickBooks to SQL Server through SSIS

Tue, 12 Apr 2011 00:00:00 GMT

This example demonstrates how to use the QuickBooks ADO.NET Data Provider inside of a SQL Server SSIS workflow to transfer data directly from QuickBooks and/or QuickBooks Online into a Microsoft SQL Server database. The exact same procedure outlined below can be used with any RSSBus ADO.NET Data Providers to connect SQL Server directly with remote data via SSIS.

Step 1: Open Visual Studio and create a new Integration Services Project.
Step 2: Add a new Data Flow Task from the Toolbox onto the Control Flow screen.
Step 3: In the Data Flow screen, add a DataReader Source and an OLE DB Destination from the Toolbox.

Step 4: Add a new Data Connection, and select your provider as .NET Providers\RSSBus QuickBooks Data Provider.
Step 5: Enter the connection details for your QuickBooks machine. In this example, we are using a separate machine to host QuickBooks on.

Step 6: Open the DataReader editor. Go to the Connection Managers tab and select the Data Connection you just created.
Step 7: Keeping the DataReader Source open, go to the Component Properties tab and enter your SQL select command.

Step 8: Close the DataReader editor. Now drag the arrow below the DataReader Source to connect it to the OLE DB Destination.
Step 9: Open the OLE DB Destination and add a New Connection. Enter your server and database information here. In this example, we are using a separate machine running SQLExpress.
Step 10: Set your Data access mode to "table or view" and select the table or view to populate in your database
Step 11: Configure any properties you wish to on the Mappings screen.

Step 12: Close the OLE DB Destination Editor and run the project. After the SSIS Task has finished executing, your database will be populated with data obtained from QuickBooks.

SSIS Sample Project

To help you with getting started using the QuickBooks Data Provider within SQL Server SSIS, download the fully functional sample project. You will also need the QuickBooks ADO.NET Data Provider to make the connection. You can download a free trial here.

Note: Before running the demo, you will need to change your connection details to fit your environment as detailed in Step 5 and Step 9.

RSSBus in the Cloud: Deploying AS2 Connector to Windows Azure

Fri, 02 Sep 2011 00:00:00 GMT

Chances are, the idea of opening up access to your AS2 services outside your secure, load-balanced network inspires cringe-worthy visions of hours wading through configuration esoterica with nothing but the most brutally terse of man pages to guide you. To the rescue, RSSBus' web-ready AS2 Connector makes nightmares like these a thing of the past.

It gets better: pairing AS2 Connector with Microsoft's Windows Azure cloud service gives you the levity to forget about having to build, host and scale your AS2 services ever again.

Follow this tutorial and learn the few short steps you need to deploy AS2 Connector on Windows Azure.

A Base Assumption

For the purposes of this article, we assume you already have an active Windows Azure subscription. You can find more information on Microsoft's subscription offers on the Windows Azure website.

Deploying AS2 Connector to Windows Azure

Deploying services to Windows Azure requires three files: a signed certificate (.pfx file), a Cloud Service Package (.cspkg), and a Cloud Service Configuration (.cscfg). First we'll create a self-signed certificate and then create a hosted service on Windows Azure with a pre-packaged version of AS2 Connector.

Creating a Certificate

Windows Azure accepts a variety of certificates. For this article, we'll create a self-signed certificate, since they're relatively easy to create and sufficiently secure for basic uses.

In Windows, open the IIS Manager.
Under the IIS heading in the middle pane, double-click the Server Certificates icon.
Under the Actions panel on the right hand side, select Create Self-Signed Certificate...
A dialog will appear asking you to name your certificate. When you're done, the new certificate will appear in the Server Certificates pane.
Right-click on the newly created certificate and select Export. Complete the export dialog that pops up.

Creating and Deploying a Hosted Service

Download the RSSBus AS2 Connector package and configuration files for Windows Azure.
Log into Windows Azure. You will be presented with the Management Portal.
On the ribbon up top, select New Hosted Service.
A configuration dialog will pop up. Fill in the relevant details. There are several details of note to be aware of:
- Under Deployment options, select Deploy to stage environment.
- For Package location and Configuration file, you'll want to select the files downloaded in the first step of this section.
- When you click Add Certificate, you will want to provide the self-signed certificate you created in the previous section.
When you are finished, click OK. Windows Azure will upload, initialize, and start AS2 Connector. This process may take several minutes.
Select the deployment after its status indicates that it's ready. In the right hand panel, you will see a property entitled DNS name. Click on the link below that.
A new tab appears presenting you with the RSSBus AppServer login page. Enter the following default credentials:
- User: admin
- Password: test

Now AS2 Connector is fully deployed. All you have to do after this is simply configure your AS2 Connector Profile and add your AS2 partners and you're in business.

Upgrading to AS2 Connector V3

Tue, 06 Dec 2011 00:00:00 GMT

RSSBus AS2 Connector V3 offers a quick 3-step way to import your old trading partners from previous versions of the app.

Navigate to RSSBus AS2 Connector on your server. Click on the Services tab at the top right.
In the left-hand panel listing your available services, select ImportProfile.rsb.
In the Directory field of the service, specify the path to your old as2data directory and click the Call Service button.

AS2 Connector should now have successfully imported your old profile information and trading partners. Because importing copies over your old data, changes in one application do not appear in the other if they are running side by side.

Tutorial: RSSBus AppServer Events

Wed, 30 Nov 2011 00:00:00 GMT

RSSBus apps can raise events when a particular condition is met. In the RSSBus AS2 Connector app, for instance, sending a file raises two events: BeforeSend and AfterSend. The power of these events lies in the ability to respond to them with, say, running a batch file or shell script. This tutorial shows you how to automate actions in your operatings system after an RSSBus app raises an event.

Learning what events an application offers

To find out what events an app makes available, log in to that app and navigate to the Services page. On the left-hand panel, scroll down to the Events section. You will see listed the events available for a given app.

If you click on one of the events, it lists some information, including the event parameters. These are bits of data you can use within the script that executes in tandem with that event. Below that, in the Configuration section, you will see a description of events, including the path to the selected event.

A Simple Example: Running a Batch File After Sending a File in AS2 Connect

AS2 Connector sends and receives files to and from trading partners. Often times it is useful to execute a batch when a file is sent from AS2 Connector, so for this example, we'll be working with the AfterSend event. Go to the AfterSend event listed in the Events section of the left-hand column on the Services page. Under the Configuration section, note the path to the events directory and navigate to it in your file system.

In the events directory, you will see several files. Open the AfterSend.rsb file in your flavor of text editor. You will see several blocks of XML, including a few commented blocks. The commented blocks are prebaked scripts that perform common and useful tasks. The first two comment blocks respectively pertain to Windows batch files and UNIX shells scripts. The directions for either option are identical in this tutorial, but we'll use Windows batch files here for simplicity's sake.

First, uncomment the batch file code block so it looks like this:

<rsb:set item="batchInput" attr="name" value="C:\myfile.bat" />
<rsb:set item="batchInput" attr="arguments" value="[AS2From] [File]" />
<rsb:call op="sysExecute" input="batchInput" />

Let's unpack this brief triplet of code. The prefix rsb indicates the XML tag is an RSBScript command. RSBScript is the scripting language of RSSBus.

You see there are two rsb:set tags. These tags set up the inputs to the rsb:call tag on the third line. Notice the rsb:set tags have three attributes: item, attr, and value. For the first rsb:set, we set the location of our batch file to the name attribute of the batchInput item. You will want to edit the value to point the location of your own batch file.

We do something similar in the next rsb:set tag. This time, we are setting the arguments of the batch file itself. AS2From and File are inputs to the AfterSend event. All of the inputs are listed in the rsb:info block near the top of the script.

Also notice the square brackets around the input names: this resolves the value of those inputs. Say for instance the values of AS2From and File were "MyPartner" and "C:\test.xml", respectively. Then, according to our rsb:set command, the arguments attribute of the batchInput will equal MyPartner C:\test.xml. Again, you will need to edit this value to match the inputs of your batch script.

Finally, the rsb:call tag calls the sysExecute operation using our batchInput item. This tells the operating system to execute a process as specified by our batchInput.

Taking Control of Events

After you've successfully enabled your batch file or shell script to execute when an event fires, the actions you can perform are limited by the capabilities of your system. This is a powerful tool for triggering processes on your system or even services within an RSSBus app.

Tutorial: Hosting RSSBus Apps in IIS

Thu, 10 Nov 2011 00:00:00 GMT

RSSBus Apps by default runs hosted in the RSSBus AppServer. In some versions of Windows, this requires a user to be logged in to run an instance of RSSBus AppServer. If this poses a problem to you, you may alternatively host RSSBus Apps in Microsoft Internet Information Services (IIS).

This guide will show you how to host RSSBus Apps using IIS. It gives instructions for several versions of IIS and Windows. If your version is not covered in this tutorial or you have other questions, please contact our support team for further assistance.

Hosting RSSBus Apps in IIS

Windows 7 or Windows Server 2008 R2 (IIS 7.5)
Windows Vista or Windows Server 2008 (IIS 7)
Windows Server 2003 (IIS 6)
Windows XP (IIS 5)

Troubleshooting

Visiting http://localhost/apps results in a "Page Cannot Be Displayed" error (HTTP 404 error).
Apps stop responding if I stay logged out after a while.

Windows 7 or Windows Server 2008 R2 (IIS 7.5)

Create a Web Application

Open IIS Manager.
In the "Connections" panel, expand the node for your local machine. Right-click Default Web Site (or your custom website) and choose Add Application...
You will be prompted for several bits of info.
- Alias: Pick an alias for the application. For our purposes, we'll go ahead and name it apps.
- Application pool: You can assign a new application pool if you'd like. For the tutorial we'll stick with the DefaultAppPool. Whichever pool you decide, note its name for the next step in this tutorial.
- Physical path: You'll need to point the physical path to the www directory of the RSSBus Apps installation. Assuming you've installed it to the default installation directory, you should specify the physical path to be C:\Program Files (x86)\RSSBus\RSSBus Apps\www.

Configure Permissions

We'll now need to allow the application pool identity to access the RSSBus Apps application directory. First we'll need to determine the identity.

In the left-hand Connections panel of IIS, click on the Application Pools node. Right-click on the application pool we assigned RSSBus Apps in the previous step, and select Advanced Settings...
In the dialog, under the Process Model section, note the name in the Identity field. This value is typically NetworkService, which is what we'll use here.

Now, we need to assign access permissions to the application pool identity.

Using ApplicationPoolIdentity
If you are using ApplicationPoolIdentity or another managed service account identity as your application pool identity, you will need to follow the alternate configuration instructions below.

There are two directories that we will need to give the identity access:

C:\Program Files (x86)\RSSBus\RSSBus Apps\www
C:\Program Files (x86)\RSSBus\RSSBus Apps\data

For the steps below, we will cover how to assign these permissions for the www directory. You can repeat these steps for the data directory as well.

In Windows Explorer, navigate to C:\Program Files (x86)\RSSBus\RSSBus Apps\. Right-click on the www directory and select Properties.
Select the Security tab and click the Edit button. Then click on Add...
In the Select Users or Groups dialog that pops up, enter the application pool identity that you noted in the previous sequence of instructions. Click OK.
Then, in the Permissions for www dialog, ensure the application pool identity has the following permissions:
- Modify
- Read & Execute
- List Folder Contents
- Read
- Write
Go ahead and confirm the settings.

At this point we should be good to go. Head on over to http://localhost/apps.

Alternate: Configure Permissions For A Managed Service Account Identity

Open a Command Prompt as an administrator (Right click the Command Prompt link from Start → Accessories and choose Run as Administrator.

Use the icacls command to grant permissions to the www directory:

icacls "C:\Program Files (x86)\RSSBus\RSSBus Apps\www" /grant "IIS APPPOOL\DefaultAppPool":(OI)(M)

Prevent Application Processes from Unloading

IIS unloads processes that idle too long. This may cause inactivity in RSSBus Connector apps that need to perform background tasks.

Note the Application Pool that you associated with your virtual directory: right-click on your virtual directory, and select Properties. Under the Virtual Directory tab, note the Application Pool. Close the dialog.
Open the configuration file located at C:\Windows\System32\inetsrv\config\applicationHost.config.

In the appropriate applicationPools entry, add startMode="AlwaysRunning" so it appears as such:

<applicationPools>
  <add name="DefaultAppPool" managedRuntimeVersion="v4.0" startMode="AlwaysRunning" />
</applicationPools>

At this point we should be good to go. Head on over to http://localhost/apps.

Windows Vista or Windows Server 2008 (IIS 7)

Create a Web Application

Open IIS Manager.
In the Connections panel, expand the node for your local machine. Right-click Default Web Site (or your custom website) and choose Add Application...
You will be prompted for several bits of info.
- Alias: Pick an alias for the application. For our purposes, we'll go ahead and name it apps.
- Application pool: You can assign a new application pool if you'd like. For the tutorial we'll stick with the DefaultAppPool. Whichever pool you decide, note its name for the next step in this tutorial.
- Physical path: You'll need to point the physical path to the www directory of the RSSBus Apps installation. Assuming you've installed it to the default installation directory, you should specify the physical path as C:\Program Files (x86)\RSSBus\RSSBus Apps\www.

Configure Permissions

We'll now need to allow the application pool identity to access the RSSBus Apps application directory. First we'll need to determine the identity.

In the Connections panel of IIS, click on the Application Pools node. Right-click on the application pool we've assigned RSSBus Apps, and select Advanced Settings...
In the dialog, under the Process Model section, note the name in the Identity field. This value is typically NetworkService, which is what we'll use here.

Now, we need to assign access permissions to the application pool identity. There are two directories that we will need to give the identity access:

C:\Program Files (x86)\RSSBus\RSSBus Apps\www
C:\Program Files (x86)\RSSBus\RSSBus Apps\data

For the steps below, we will cover how to assign these permissions for the www directory. You can repeat these steps for the data directory as well.

In Windows Explorer, navigate to C:\Program Files (x86)\RSSBus\RSSBus Apps\. Right-click on the www directory and select Properties.
Select the Security tab and click the Edit button. Then click on Add...
In the Select Users or Groups dialog that pops up, enter the application pool identity that you noted in the previous sequence of instructions. Click OK.
Then, in the Permissions for www dialog, ensure the application pool identity has the following permissions:
- Modify
- Read & Execute
- List Folder Contents
- Read
- Write
Go ahead and confirm the settings.

At this point we should be good to go. Head on over to http://localhost/apps.

If you've run into any trouble during this guide, please see the Troubleshooting section.

Windows Server 2003 (IIS 6)

Create A Virtual Directory

Open IIS Manager.
In IIS Manager, in the left-hand panel, navigate to the Default Web Site node at Console Root → Local Computer → Default Web Site.
Right-click on Default Web Site and select New → Virtual Directory.
IIS Manager will now take you through the Virtual Directory Creation Wizard. Here are the values you should provide for each prompt.
1. Alias: Whichever name you prefer. For this article, we'll specify apps.
2. Directory: C:\Program Files\RSSBus\RSSBus Apps\www.
3. Access Permissions:
  - Read
  - Run
  - Execute

Map Application File Types

Now that you've completed the Virtual Directory Creation Wizard, you will need to associate RSSBus file types with IIS.

Right-click on the virtual directory you just created and select Properties.
In the Properties popup, under the Virtual Directory tab, click the Configure button.
You will need to add two application extension mappings: .rst and .rsb. You will also need to specify a path to the executable that handles these files. This path is dependent on the version of ASP.NET you are using. Assuming you are using v2.0, the full path will look like C:\WINDOWS\Microsoft.NET\Framework\v2.0\aspnet_isapi.dll. You can adjust the path according to the correct version. Additionally, note that if your system runs on a 64-bit CPU, "Framework" instead should appear as "Framework64".

How To Determine Your Version of ASP.NET
Go to the Properties popup for the RSSBus Apps virtual directory you created. Select the ASP.NET tab. Note the ASP.NET version. Be aware that RSSBus Apps requires a minimum ASP.NET version of 2.0.

Configure Permissions

We'll now need to allow the application pool identity to access the RSSBus Apps application directory. First we'll need to determine the identity.

Right-click on the virtual directory representing our AppServer and select Properties.
In the Virtual Directory tab, note the value in the Application pool field. This value is typically DefaultAppPool.
Now cancel out of this dialog, and expand the Application Pools node in the left-hand navigation panel. Right-click on the application pool you identified in the previous step and select Properties.
Note the Application pool identity.

Now, we need to assign access permissions to the application pool identity. There are two directories that we will need to give the identity access:

C:\Program Files (x86)\RSSBus\RSSBus Apps\www
C:\Program Files (x86)\RSSBus\RSSBus Apps\data

For the steps below, we will cover how to assign these permissions for the www directory. You can repeat these steps for the data directory as well.

In Windows Explorer, navigate to C:\Program Files (x86)\RSSBus\RSSBus Apps\. Right-click on the www directory and select Properties.
Select the Security tab and click the Edit button. Then click on Add...
In the "Select Users or Groups" dialog that pops up, enter the application pool identity that you noted in the previous sequence of instructions. Click OK.
Then, in the Permissions for www dialog, ensure the application pool identity has the following permissions:
- Modify
- Read & Execute
- List Folder Contents
- Read
- Write
Go ahead and confirm the settings.

At this point we should be good to go. Head on over to http://localhost/apps.

You're Done!

Head on over to http://localhost/apps and check out your handiwork.

If you've run into any trouble during this guide, please see the Troubleshooting section.

Windows XP (IIS 5)

Create A Virtual Directory

Open IIS Manager.
In IIS Manager, in the left-hand panel, navigate to the Default Web Site node at Console Root → Local Computer → Default Web Site.
Right-click on Default Web Site and select New → Virtual Directory.
IIS Manager will now take you through the Virtual Directory Creation Wizard. Here are the values you should provide for each prompt.
1. Alias: Whichever name you prefer. For this article, we'll specify apps.
2. Directory: C:\Program Files\RSSBus\RSSBus Apps\www.
3. Access Permissions:
  - Read
  - Run
  - Execute

Map Application File Types

Now that you've completed the Virtual Directory Creation Wizard, you will need to associate RSSBus file types with IIS.

Right-click on the virtual directory you just created and select Properties.
In the Properties popup, under the Virtual Directory tab, click the Configure button.
You will need to add two application extension mappings: .rst and .rsb. You will also need to specify a path to the executable that handles these files. This path is dependent on the version of ASP.NET you are using. Assuming you are using v2.0, the full path will look like C:\WINDOWS\Microsoft.NET\Framework\v2.0\aspnet_isapi.dll. You can adjust the path according to the correct version. Additionally, note that if your system runs on a 64-bit CPU, "Framework" instead should appear as "Framework64".

How To Determine Your Version of ASP.NET
Go to the Properties popup for the RSSBus Apps virtual directory you created. Select the ASP.NET tab. Note the ASP.NET version. Be aware that RSSBus Apps requires a minimum ASP.NET version of 2.0.

You're Done!

Head on over to http://localhost/apps and check out your handiwork.

If you've run into any trouble during this guide, please see the Troubleshooting section.

Troubleshooting

Visiting http://localhost/apps results in a Page Cannot Be Displayed error (HTTP 404 error).

This can happen if ASP.NET is not enabled correctly. To confirm this, visit http://localhost/apps/favicon.ico. If this page loads successfully, then ASP.NET is not properly enabled.

If you are using Windows Server 2003 open the Internet Information Services manager via the steps mentioned in the setup and check the Web Service Extensions listed under the local computer. Make sure ASP.NET v2.0 is enabled. You may also need to take the step below.

For other environments, running IIS6 or lower, open the windows command prompt and navigate to the .NET Framework version installation C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 and run the command aspnet_regiis -i. You may have to change Framework to Framework64 if you are using a 64-bit processor. You should see something like this:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis -i
Start installing ASP.NET (2.0.50727).
.......
Finished installing ASP.NET (2.0.50727).

Then restart IIS by issuing the iisreset command. You should see something like this:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>iisreset

Attempting stop...
Internet services successfully stopped
Attempting start...
Internet services successfully restarted

For environments, running IIS7 or higher, follow the steps below to enable ASP.NET:

Open Control Panel
Go to Programs (or Programs and Features)
Choose Turn Windows features on or off
Expand Internet Information Services
Expand World Wide Web Services
Expand Application Development Features
Check the box next to ASP.NET
Click OK, wait for Windows to install the necessary features

Apps stop responding some time after I log out.

IIS is possibly unloading your application after it times out from inactivity. To prevent this you will need to run IIS 7.5 or higher and make a configuration change. Follow these instructions here.

Discussion: Security and RSSBus AppServer

Wed, 30 Nov 2011 00:00:00 GMT

Security is a serious concern for any system administrator. RSSBus apps typically store, modify, and share usernames, passwords, and a whole host of other information that should remain under lock and key. Precautions clearly must be taken before deploying RSSBus AppServer. This document discusses the provisions RSSBus AppServer offers to secure connections with its host environment.

Enable SSL

SSL (or Secure Sockets Layer) encrypts connections between a client and your server. This protects connections from network listeners and man-in-the-middle attacks. It also allows users to confirm the identity of your server. To enable SSL on your server, choose the instructions below that match your environment.

Windows

- Enabling SSL in RSSBus AppServer

Go to the RSSBus AppServer Options dialog by right-clicking the AppServer icon in the Windows taskbar icon tray and selecting Server Options...

For a completely secure sever, you may want to consider disabling Plain Text access in the dialog. Enable the Use SSL checkbox, and then click the button next to the SSL Server Certificate field to select a certificate.

A dialog will pop up listing the certificates in your system store. You also have the option to create your own self-signed certificate with the Create Certificate button at the bottom.

After you choose a certificate, click Apply to save your changes. You are now running RSSBus AppServer over HTTPS. Do note that users will now have to prepend URLS with HTTPS instead of HTTP to now access your server.

- Enabling SSL in IIS

The following steps assume you already have a certificate you can use to enable SSL on your server.

Open IIS Manager.
Select your server's node from the left-hand panel, and then click the Bindings... link on the right-hand side.
In the dialog that pops up, click the Add... button. Select https from the drop-down menu, and then select a server certificate. Click OK to save the changes and then close the popup.
Now, with your server's node still selected, double-click the SSL Settings icon under the IIS heading. Enable the Require SSL checkbox, and click Apply in the right-hand panel.

Java

- Enabling SSL in Jetty

To enable SSL in Jetty, you must generate a certificate and modify the server configuration. For instructions, see this guide from the Jetty website.

Authtokens

Authtokens are unique identifiers that authenticate authorized users to run services without logging in. This is useful for automating or integrating with other processes.

To authorize a user to run service using an authtoken, log in as an administrator, and then navigate to the desired service. Scroll down to the Security section and select a user from the Add User drop-down.

To run the service, add the HTTP header x-rssbus-authtoken with the desired authtoken as part of the HTTP request. Alternatively, you may specify the authtoken as @authtoken as part of HHTP form-post data or a query parameter.

For more information about services in RSSBus Apps, please see this knowledge base article.

How To Use Services With RSSBus Service-Enabled Solutions

Mon, 28 Nov 2011 00:00:00 GMT

RSSBus Solutions based on the RSSBus AppServer framework allow you to securely access your business data from anywhere on the web. Exposed services give you programmatic access to your apps with powerful suite of commands. In this tutorial, we will use AS2 Connector, one of the solutions built on the RSSBus AppServer platform, and list the available logs with the List Logs service.

Listing Logs Using AS2 ConnectorSE

In your browser, navigate to your RSSBus AppServer host. Enter your credentials if necessary. Then open the AS2 Connector app from the homepage.

Click on the "Services" link in the blue navigation bar up top to go to the Services page. From here you are presented with a brief explanation of this page plus a list of services available to you on the left hand side.

Since we're adding a listing the available logs, go ahead and click on the "List Logs" service. The service appears in full, plus a description, its required parameters, and optional parameters below that. At minimum we need to provide the name of a partner in the Name input.

When you�re ready, click the "Call Service" button. A results page will appear listing the various logs that AS2 Connector writes to.

Congratulations! You have now listed the logs of AS2 Connector using a service. Every service in all apps of the RSSBus AppServer platform is accessible via HTTP get requests. Even better, you can retrieve the output of these services in a variety of formats, including:

RSS
HTML
CSV
ATOM
SOAP
JSON
Excel
TSV

To change the output format, you can either select the format from the dropdown menu that appears under the Run Service button on the service page, or you can append &@rss (if RSS is the desired format) to the end of URL that locates the service.

RSSBus AppServer: User Management FAQ

Mon, 28 Nov 2011 00:00:00 GMT

RSSBus AppServer offers a robust suite of user managements tools. Administrators can add and remove users, control what apps users use, and choose the specific areas of apps users can access. This FAQ covers the general concepts of user administration for RSSBus AppServer.

User Management FAQ

What kinds of user roles does RSSBus AppServer provide?
What are services?
How can a user execute an app's service while logged out?
What is an "authtoken"?
Do authorizations for an app and its services work independent of each other?
How do I change a user's authtoken?
How do I authorize a user to run an app?
How do I grant or revoke administrative access for a user?
What kinds of users can modify an app's settings?
Do users need passwords?
What information can a user change on his or her own profile?
Are users renamable?

What kinds of user roles does RSSBus AppServer provide?

RSSBus AppServer offers three levels of user roles: regular, administrator, and root. Regular users can execute app and app services, provided they are authorized to do so.

Administrators possess the ability to modify both app and user settings. Administrators can authorize users to run apps and individual app services. Adminstrators may also create and delete users, and grant or revoke administrator access to other users (and themselves).

There is only one root user, the admin. The root user behaves and functions exactly like administrative users, with a two exceptions. First, the root user cannot be deleted. Second, the password for the root user is set during the installation process for AS2 Connector.

What are services?

Services allow users to perform a greater set of functionality in an app than what is exposed through an app's user interface. Services execute either through the RSSBus AppServer interface or through HTTP requests. Each service has its own unique URL, plus services may take parameters of various sorts. For more information about services in RSSBus Apps, please see this knowledge base article.

How can a user execute an app's service while logged out?

Regular users can execute an app's services with authtokens. An administrator must first authorize a user to access a service. To do so, log in as an administrator, and then navigate to the desired service. Scroll down to the Security section and select a user from the Add User drop-down.

For more information about services in RSSBus Apps, please see this knowledge base article.

What is an "authtoken"?

Authtokens are unique identifiers that allow users to execute app services, even while they are logged out. To authorize a user to run services, see this section.

Do authorizations for an app and its services work independent of each other?

Yes. If authorized, users may use an app's front-end interface, without access to any of that app's services. Conversely, users may execute services of an app without authorization to run an app itself. Controlling which services a user may execute allows administrators to create highly granular models of user behavior.

How do I change a user's authtoken?

In the Manage Users page of the RSSBus AppServer, click on the user whose authtoken you wish to change. In the modal dialog that appears, click New button that appears next to the Authtoken field, and save the change.

How do I authorize a user to run an app?

In the Manage Users page of the RSSBus AppServer, click on the user whose access you wish to authorize. In the modal dialog that appears, click checkbox that appears next to the app you wish to grant access to, and save the change.

How do I grant or revoke administrative access for a user?

In the Manage Users page of the RSSBus AppServer, click on the user whose administrative access you wish to modify. In the modal dialog that appears, click the checkbox that appears in the Administrator field, and save the change.

What kinds of users can modify an app's settings?

Only administrators can modify an app's settings.

Do users need passwords?

For security purposes, all users are required to have passwords.

What information can a user change on his or her own profile?

Regular users may only change their password. Administrators may additionally alter email and authtoken values, grant administrative access, and grant access to apps and app services.

Are users renamable?

User names are permanent and not renamable.

Using Object-relational mapping (ORM) with Salesforce Entities

Wed, 25 Apr 2012 00:00:00 GMT

Object-relational mapping (ORM) technique can bridge your logical business model with your physical storage model. Hibernate is a popular object-relational mapping library for Java, providing a framework for mapping object-oriented domain models to a traditional relational database.

Note: Plese see the article Using LINQ with QuickBooks to see the ORM approach using Microsoft .NET.

The RSSBus Salesforce JDBC Driver supports full integration with Hibernate.In this tutorial, we will generate an object-relational mapping of your Salesforce repository using the RSSBus JDBC Driver for Salesforce with Hibernate. A similar approach is applicable to all our JDBC Drivers.

Though Eclipse is the IDE of choice for this article, RSSBus drivers work in any product that supports the Java Runtime Environment. If you prefer NetBeans as your IDE, see our article here. If you prefer IntelliJ IDEA, see here.

STEP 1: Download the Prerequisite Software
You will need three pieces of software to follow this tutorial: Eclipse, the RSSBus Salesforce Driver, and Hibernate. Here are their corresponding download links:

Note: These instructions are intended to work specifically with Hibernate version 4.1.2. Our Salesforce driver works with other versions, but these instructions may vary release to release.

STEP 2: Install the Hibernate Plug-In for Eclipse

In Eclipse, navigate to Help -> Install New Software.
Click the Add button, enter "Hibernate" in the Name field, and enter "http://download.jboss.org/jbosstools/updates/stable/" in the Location field. Click OK.
After all the content loads, expand the All JBoss Tools 3.2.x option, choose Hibernate Tools, and click Next.

STEP 3: Start A New Project

Go to the File menu and create new project. Select Java Project as your project type and click Next. Enter a project name and click Finish.
Right-click on your project and select Properties. Select Java Build Path and then select the Libraries tab.
Using the Add External JARs... command, you need to add several libraries to your project.

From your RSSBus Salesforce download, the rssbus.salesforce.jar library.
From your Hibernate download, all of the .jar files in the /dist/lib/required and /dist/lib/provided directories.

STEP 4: Add a Hibernate Configuration File

Right-click on our new project and select New -> Other -> Hibernate.
Select Hibernate Configuration File (cfg.xml) and hit Next.
Select src as the parent folder and click Next.
Input the following values for each text field:

Database dialect: SQL Server
Driver class: RSSBus.SalesForce.SalesForceDriver
Connection URL: jdbc:salesforce:User=Your User name; Password=Your password; Access Token=Your Access Token

STEP 5: Connecting Hibernate to Salesforce We must now connect our Hibernate instance to Salesforce.

Switch to the Hibernate Configurations perspective: Window -> Open Perspective -> Hibernate.
Right-click on the Hibernate Configurations panel and select Add Configuration...
Click the Browse... button and choose the project we're working with.
For the Configuration file field, click Setup, click Use Existing..., and select the location of the hibernate.cfg.xml file (inside src folder in this demo).
Click OK to dismiss the Edit Configuration dialog
Expand the Database node of our new created Hibernate Configurations file. If you can see the tables of the database, it means the connection is successfully established.

STEP 6: Reverse Engineer Salesforce

Switch back to the Package Explorer.
Right-click on our project, select New -> Other, and select Hibernate -> Hibernate Reverse Engineering File (reveng.xml). Click Next.
Select src as the parent folder and click Next.
In the Console configuration dropdown menu, select the Hibernate configuration file we created in the previous step and click Refresh. Please note that it may take a few minutes to load all the meta data of more than a hundred tables in Salesforce. Alternatively, you can use the cache of metadata. See how to do that at here.
Expand the node and choose the tables you want to reverse engineer. Click Finish when you are done.

STEP 7: Configure Hibernate to Run

From the menu bar, select Run -> Hibernate Code Generation -> Hibernate Code Generation Configurations.
In the Console configuration dropdown menu, select our Hibernate configuration file we created in Step 5. Click Browse... by Output directory and select src. Use existing reveng.xml from src.
Enable the Reverse engineer from JDBC Connection checkbox. Click the Setup button, click Use Existing..., and select the location of the hibernate.cfg.xml file (inside src folder in this demo).
In the Exporters tab, check Domain code (.java) and Hibernate XML Mappings (hbm.xml).
Click Run.

One or more plain old Java objects (POJO) are created based on the reverse engineering setting in the previous step.

STEP 8: Insert Mapping Tags For each mapping we have generated, we will need to create a mapping tag in hibernate.cfg.xml to point Hibernate to our mapping resource. Open hibernate.cfg.xml and insert the mapping tags like below the comment as so:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
  <session-factory name="">
    <property name="hibernate.connection.driver_class">
      RSSBus.SalesForce.SalesForceDriver
    </property>
    <property name="hibernate.connection.url">
      jdbc:salesforce:User=Your User Name; Password=Your password; Access Token= Your Access Token;
    </property>
    <property name="hibernate.dialect">
      org.hibernate.dialect.SQLServerDialect
    </property>
    <!--Add the XML Mapping tags here-->
    <mapping resource="Account.hbm.xml" /> 
    </session-factory>
</hibernate-configuration>

STEP 9: Et Voila! Now the setup of Hibernate is done. Using the entity you created from last step, you can now perform Select, Update, Delete, and Insert commands. For example:

import java.util.*;
import org.hibernate.*;
import org.hibernate.cfg.Configuration;

public class App {
	public static void main(final String[] args) {
    Session session =  new
    Configuration().configure().buildSessionFactory().openSession();
        String SELECT = "FROM Account";
        Query q = session.createQuery(SELECT);
        List<Account> resultList = (List<Account>) q.list();

        for(Account s: resultList){
          System.out.println(s.getAccountNumber());
          System.out.println(s.getName());
        }
	}
}

Using RSSBus JDBC Drivers from IntelliJ IDEA

Wed, 25 Apr 2012 00:00:00 GMT

In this article, we will demonstrate how to integrate RSSBus JDBC Driver for Salesforce with IntelliJ. The same approach is applicable to all our JDBC drivers.

STEP 1: In the Data Sources window, right click and select "Add Data Source -> DB Data Source".

STEP 2: In the Data Source Properties dialog add the JDBC Driver file "rssbus.salesforce.jar". It is in the lib directory of our product. Select "RSSBus.SalesForce.SalesForceDriver" as the JDBC Driver Class from the drop-down list.

The JDBC URL allows you to specify the connection URL. The connection URL is as below:
jdbc:salesforce:AccessToken=YourAccessToken
The URL must start with "jdbc:salesforce:...", and can include any of the connection properties separated with a semi-colon. Click "Finish". See connection string options here
Configure the user name, password and Click "OK" to finish.
STEP 3: : Right click the data source we just created, and select "Refresh Tables". After the connection is established, you can expand it to see the meta-data which will include Tables, Views and Procedures.

The Tables are similar to database tables and support two-way data communication. The Views are similar to traditional database views and are read-only. And the Procedures are similar to database stored procedures; they allow you to manipulate the data source in ways that is not possible with Tables or Views.

Data can be queried for tables and views by right clicking on any of them and selecting "Open Tables Editor". You can also insert, update or delete records from the data view window.

Using RSSBus JDBC Drivers from NetBeans

Wed, 25 Apr 2012 00:00:00 GMT

In this article, we will show how to use the RSSBus JDBC Driver for Salesforce with NetBeans. The same approach is applicable to all our JDBC drivers.

STEP 1: Expand the Database node in the Service window, and right click on the Drivers node, select New Driver.

STEP 2: In the New JDBC Driver wizard, add the rssbus.salesforce.jar file by clicking on the "Add" button. It's inside the lib directory of our product. After adding the driver file, click "Find" button to search the driver class inside the jar. Select "RSSBus.SalesForce.SalesForceDriver" from the drop-down list. Enter the name for this driver in the Name text field. Click "OK".
STEP 3: Right click the database node and select "New Connection". In the New Connection Wizard, choose the driver we just added from the drop-down list, click "Next". Enter your user name and password. The JDBC URL allows you to specify the connection URL. The connection URL is as below:

jdbc:salesforce:AccessToken=YourAccessToken

The URL must start with "jdbc:salesforce:...", and can include any of the connection properties separated with a semi-colon. Click "Finish". See connection string options here

STEP 4: Right-click the connection we just created, select "Connect". After the connection is established, you can expand it to see the meta-data which will include Tables, Views and Procedures.

Data can be queried for tables and views by right clicking on any of them and selecting "View Data". You can also insert, update or delete records in the data view window.

Note: There is a bug in some versions of NetBeans that leads to a null pointer exception when using a JDBC Driver with JDK 1.7. If you encounter this exception you can still use the JDBC driver in you code only integration with NetBeans is broken.

List Active Directory Groups and Users in SharePoint

Wed, 13 Jan 2010 00:00:00 GMT

This web part will list each user group and its members, as defined in your Active Directory (or other LDAP server) installation.

Step 1: Ensure the RSSBus Web Part is installed.
Step 2: Install the LDAP Connector. Add the RSSBus Web Part to your page and paste the following template into its source editor, but do not click �Apply� yet.

<rsb:import lib="RSSBus.LdapOps" />
<rsb:info>
  <input name="showemptygroups" values="false,true" />
</rsb:info>

<!-- general ldap connection properties -->
<rsb:set attr="server" value="testman"/>
<rsb:set attr="authdn" value="NS2\\Lance"/>
<rsb:set attr="password" value="*****"/>

<!-- group search settings -->
<rsb:set attr="dn" value="dc=NS2"/>
<rsb:set attr="filter" value="objectClass=group"/>
<rsb:set attr="scope" value="WHOLESUBTREE"/>

<!-- group search -->
<rsb:call op="ldapSearch" save="groups" />
<rsb:call op="feedGet?feed=[_feeds.groups | urlencode]">
  <rsb:set attr="showgroup" value="true" />
  <rsb:equals attr="showemptygroups" value="false">
    <rsb:set attr="showgroup" value="[ldap:member# | def('0') | equals('0', 'false', 'true')]" />
  </rsb:equals>
  <rsb:equals attr="showgroup" value="true">
   <a href="javascript:;" onmousedown="toggleDiv('[ldap:name]-content');">[ldap:name]</a> ([ldap:member# | def('0')])
   <div id="[ldap:name]-content" style="display:none">
     <rsb:set attr="dn" value="[ldap:dn]"/>
     <rsb:set attr="scope" value="BASEOBJECT"/>
     <rsb:call op="ldapSearch" output="out">
       <table width="100%">
         <tr style="background-color:LightGrey"><td>samAccountName:</td><td>[ldap:samaccountname]</td></tr>
         <tr style="background-color:LightGrey"><td>Full Group DN:</td><td>[ldap:dn]</td></tr>
         <tr></tr>       
         <rsb:enum attr="ldap:member">
           <tr><td colspan="2">[_value]</td></tr>
        </rsb:enum>
       </table>
     </rsb:call>
   </div>
   <hr />
 </rsb:equals>
</rsb:call>

<script> function toggleDiv(div) {
  var mydiv=document.getElementById(div);
  if(mydiv.style.display == 'none') { 
    mydiv.style.display = 'block'; 
  }
  else { 
    mydiv.style.display = 'none';
  }
}
</script>

Step 3: Modify this template so that it works for your Active Directory server. There are several item to change:
1. On line 8-10 change the values of the server, authentication DN, and authentication password settings to match your AD installation. See the advanced tip at the bottom of this post to automatically use the logged in user as the authentication DN.
2. On line 13 modify the value of the DN setting to match your domain.
You can customize the HTML in the template or leave it as is.
Step 4: Click "Apply". When the page reloads, you will be presented with a list of the groups on your Active Directory domain. Click on a group to expand it into its list of user members.

Advanced Tip: Use RSSBus� special _spuser item in order to get information specific to the currently authenticated SharePoint user. This is useful for listing documents modified by a specific user, authenticating to an Exchange server, and (in this example) for authenticating to AD. You can set the DN to "[_spuser.LoginName]", and prompt the user to enter their own password.

Quickly Integrate Custom SQL Queries With SharePoint

Wed, 13 Jan 2010 00:00:00 GMT

This Web Part will perform a custom SQL query with a custom display. Unlike previous examples, this will require you to provide your SQL connection string and query and make replacements inside the template itself to make it match the results of the query.

Step 1: Ensure the RSSBus Web Part is installed.
Step 2: Make sure you have a valid SQL connection string and query. If you are using another type of database, that will work as well � there are many different database connectors in the RSSBus Connector library.

Step 3: Add the RSSBus Web Part to your page and paste the following template into its source editor, but do not click "Apply" yet.

<rsb:info>
   <input name="conn" default="server=***;database=***;User ID=***;Password=***" />
   <input name="query" default="SELECT TOP 20 * YourTable ORDER BY YourDateColumn" />
</rsb:info>

<rsb:import lib="RSSBus.SqlOps"/>

<table width="100%">
  <tr> 
    <th style="background-color:LightGrey; white-space:nowrap">Name</th> 
    <th style="background-color:LightGrey; white-space:nowrap">Company</th> 
    <th style="background-color:LightGrey; white-space:nowrap">Product</th> 
    <th style="background-color:LightGrey; white-space:nowrap">Date/Time</th> 
  </tr> 
  <rsb:call op="sqlQuery">  
    <tr> 
      <td><a href="mailto:[sql:customeremail]">[sql:customername]</a></td>  
      <td>[sql:company]</td> 
      <td>[sql:productcode]</td> 
      <td>[sql:time]</td> 
    </tr> 
  </rsb:call>
</table>

Step 4: Modify this template so that it works for your SQL server. There are a few things to change:
1. On line 2 and 3, conn and query. Provide the connection string and query for your SQL query.
2. On lines 10�13, modify the column header names to match the values coming back from your query. You can add others as well.
3. On lines 18-21, change the outputs to match the outputs coming out of your query � these will match the column names. In this example the name, company, part, and time column values have been outputted for each query result.

As always, you can customize the HTML in the template or leave it as is.

Advanced Tip: Did you know that the RSSBus Web Part has YUI formatting capabilities built-in? Try the example below to format the output of the rsb:call as a YUI Table (modify the YUI script to match your own connection string, query, and column names).

<rsb:info> <input name="conn" default="server=***;database=***;User ID=***;Password=***" /> <input name="query" default="SELECT TOP 20 * FROM YourTable ORDER BY YourDateColumn DESC" /> </rsb:info> <rsb:import lib="RSSBus.SqlOps"/> <rsb:set attr='_meta.YUIDataColDefs' value='DataDefsDown'/> <rsb:set attr='_meta.YUIDataFields' value='DataFieldsDown'/> <rsb:call op="sqlQuery" format=ToYuiTable/> <script> var DataDefsDown = [ {key:"sql:customername",label:"Name",sortable:false,resizeable:true}, {key:"sql:productcode",label:"Part#",sortable:false,resizeable:true}, {key:"sql:time",label:"Date",sortable:false,resizeable:true}, {key:"sql:company",label:"Company",sortable:false,resizeable:true}, {key:"sql:title",label:"JobTitle",sortable:false,resizeable:true} ]; var DataFieldsDown = [ {key:"sql:customername"}, {key:"sql:productcode"}, {key:"sql:time"}, {key:"sql:company"}, {key:"sql:title"} ]; </script>

Microsoft Dynamics CRM Integration with SharePoint

Wed, 13 Jan 2010 00:00:00 GMT

In the past integrating Microsoft CRM with SharePoint required a large investment of time and money, often resulting in unnecessary rigidness and complexity. This particular example alleviates that complexity using Microsoft CRM Services (although its use is not a requirement).

Step 1: Install RSSBus for SharePoint if it is not already installed on your server. See here for instructions.

Step 2: Ensure the MscrmOps Connector is installed. Retrieve the service URL for the installation of Microsoft Dynamics CRM (e.g, https://crmserver:5555/mscrmservices/2006/crmservice.asmx) as well as a user name and password needed for accessing the service; CRM uses integrated authentication by default, so this will normally be your domain account and password.

Step 3: Add the RSSBus Web Part to your page and paste the following template into its source editor. Next, edit the service URL, user name, and password settings to correspond to your own MS CRM instance. Finally, click �Apply�. You will see the Web Part rendered with a list of contacts from Microsoft CRM.

<rsb:import lib="RSSBus.MscrmOps"/> <rsb:set attr="serviceurl" value="http://10.0.1.197:5555/mscrmservices/2006/crmservice.asmx"/> <rsb:set attr="username" value="YOURUSERNAME"/> <rsb:set attr="password" value="YOURPASSWORD"/> <rsb:set attr="maxresults" value="300" /> <rsb:set attr="_meta.YUITableAttrs" value="mscrm:fullname, mscrm:telephone1, mscrm:address1_city"/> <rsb:set attr="_meta.YUITableLabels" value="Full Name, Phone #, Country"/> <rsb:call op="mscrmContact" format="YuiTable" />
This particular template renders a grid using the Yahoo UI Table. You can do your own HTML rendering by inserting HTML inside the scope of the <rsb:call>. Other CRM entities such as appointments, leads, contracts, etc. can also be listed. For example, by modifying a single line of code, the previous template can be changed to list CRM appointments. Simply replace op=�mscrmContact� with op=�mscrmAppointment�. Also, by changing the operation call (the op= part) and the HTML formatting of the data, you can create pie charts of CRM invoices, bar charts of orders, etc.

Search Email History Web Part

Wed, 13 Jan 2010 00:00:00 GMT

The following template will allow you to quickly pull a list of sent and received emails for a particular address directly from SharePoint pages.

Step 1: Ensure the RSSBus Web Part is installed.

Step 2: Install the IAMP Connector.
Step 3: Find the host name and IP address of your IMAP mail serve. Also, make sure you know the user name and password to authenticate the server (This is the same information entered into Outlook when configuring your mail server).

Step 4: Add the RSSBus WebPart to your page by pasting the following template into its source editor. Click "Apply". <rsb:check attr="_request.email"> <rsb:import lib="RSSBus.ImapOps" /> <div id="myemails" style="height:15em;overflow:auto">  <rsb:set attr="sentsince" value="[sentsince | date() | dateadd('Month', '[months]') | todate('dd-MMM-yyyy') ]" />  <rsb:set attr="searchcriteria" value="SENTSINCE [sentsince] OR FROM [query] TO [query]" />  <rsb:set attr="uidmode" value="TRUE" /> <rsb:call op="imapSearch"> <rsb:first> <div style="overflow:auto"> <table width="100%"> <tr> <th style="background-color:LightGrey; white-space:nowrap">From</th> <th style="background-color:LightGrey; white-space:nowrap">Subject</th> <th style="background-color:LightGrey; white-space:nowrap">Date</th> <th style="background-color:LightGrey; white-space:nowrap">Size</th> </tr> </rsb:first>  <rsb:set attr="part.id" value="1" />  <rsb:notnull attr="part:contenttype"> <rsb:enum attr="part:contenttype"> <rsb:set attr="contenttype" value="part:contenttype#[_index]" /> <rsb:equals attr="contenttype" value="text/html"> <rsb:set attr="part.id" value="[_index]" /> </rsb:equals> </rsb:enum> </rsb:notnull> <tr> <td style="white-space:nowrap">[imap:fromname]</td> <td style="white-space:nowrap"> <a href="?msgnumber=[imap:id]&partid=[part.id]&email=[query]">[imap:subject | truncate('55')]</a> </td> <td style="white-space:nowrap">[imap:date]</td> <td style="white-space:nowrap">[imap:size]</td> </tr> <rsb:last> </table> </div> </rsb:last> </rsb:call> </div>  <rsb:check attr="_request.msgnumber"> <div id="myemailsnippet" style="height:30em;overflow:auto"> <rsb:call op="imapGetPart?id=[_request.msgnumber]&partid=[_request.partid]"> <div id="headers" style="height:50px"> <table width="100%" style="background-color:LightGrey"> <tr> <td width="75px">From:</td> <td><a href="mailto:[imap:fromemail | htmlencode]">[imap:fromname]</a></td> </tr> <tr><td width="75px">Date:</td><td>[imap:date]</td></tr> <tr><td width="75px">Subject:</td><td>[imap:subject]</td></tr> <tr /> </table> </div> <div id="body" style="overflow:auto"> [imap:content | Replace('0A','<br>','true')] </div> </rsb:call> </div> </rsb:check> </rsb:check> <script> function clearDiv(div) { var mydiv = document.getElementById(div); mydiv.innerHTML = ""; } </script>

Step 5: Specify your IMAP server and authentication credentials. If you clicked "Apply" in the source editor, you should now see a new property appear in the properties editor of the Web Part in your browser. Under "Web Part Inputs", you will see "server", "email", "password", and "mailbox" inputs. These inputs need to be populated with the values corresponding to your IMAP server, mailbox name (e.g. "Inbox"), and account credentials. Optionally, you can change the "months" and "sortcriteria" inputs to alter the output generated. By default, this Web Part will display messages to and from a specified email address in the past 6 months (the "months" input property) and then sort them by reverse-date (most recent first � the "sortcriteria" input).

After you have set these inputs appropriately, click "Apply" in the properties window and the page will reload. Enter an email address in the search box and click "Search". The emails sent to or from that email address will be displayed inside the Web Part. Click on any one of those messages to load a plain text or HTMML view of the email.

As always, you can customize the HTML in the template, or leave it as is.

Advanced Tip: Note the "sortcriteria" input expects a sort order formatted according to the IMAP Sort RFC (RFC 5256). Other common sort criteria are ARRIVAL, DATE, CC, FROM, SUBJECT, TO, SIZE. Any of those criteria can be preceded with REVERSE as shown in the example. Similarly, the search criteria specified inside the template itself can also be modified in the format specified in the IMAPv4 RFC (Section 6.4.4 in RFC 3501).

Keep Your SharePoint Calendar Synced with Google

Wed, 13 Jan 2010 00:00:00 GMT

The following is one method to keep your SharePoint Calendar(s) synced with your Google Calendar. This particular example only syncs in one direction: from Google to SharePoint. However, it could be set to sync in the opposite direction, or in both directions. This solution requires that you have the RSSBus Web Part installed (see here for instructions). You will also need the GoogleOps Connector, which is automatically included with the RSSBus Web Part.

Step 1: Log on to SharePoint and browse to your calendar. Under Site Actions click 'Edit Page'.

Step 2: Click "Add a Web Part" and add the RSSBus Web Part to the page. Many users prefer to drag the Calendar to the top and place RSSBus Web Part directly below it.
Step 3: Within the edit menu, click 'Modify Shared Web Part' and open the Source Editor.

Step 4: Paste the following RSSBus Web Part template and click 'Apply'.

<rsb:info title="Google Calendar Sync" description="This template Syncs your SharePoint Calendar events with those in a Google Calendar account."> <input name="email" description="The email used to authenticate with Google." required="true"/> <input name="password" description="The password used to authenticate with Google." required="true" style="password"/> <input name="calendar" description="The SharePoint calendar to sync to." default="Calendar"/> <input name="days" description="The number of days to import events for." default="30"/> </rsb:info> <rsb:import lib="RSSBus.GoogleOps"/>  <rsb:set attr="list" value="[calendar]"/> <rsb:call op="spListItems" out="myitemout" save="myspitems" > <rsb:call op="spGetItem?itemid=[sp:id]" > <rsb:set attr="myitemout.title" value="[sp:title]"/> <rsb:set attr="myitemout.location" value="[sp:location | def('')]"/> <rsb:set attr="myitemout.start_time" value="[sp:start_time]"/> <rsb:set attr="myitemout.end_time" value="[sp:end_time]"/> <rsb:set attr="myitemout.description" value="[sp:description | def('')]" /> </rsb:call> </rsb:call>  <rsb:set attr="fromdate" value="[null | date('d')]"/> <rsb:set attr="todate" value="[fromdate | dateadd(day, [days]) | todate('d') ]"/> <rsb:call op="googleCalSearch" out="mygcalout" save="mygitems"> <rsb:set attr="mygcalout.title" value="[ggl:title]"/> <rsb:set attr="mygcalout.location" value="[ggl:where | def('')]"/> <rsb:set attr="mygcalout.start_time" value="[ggl:starttime | todate]"/> <rsb:set attr="mygcalout.end_time" value="[ggl:endtime | todate]"/> <rsb:set attr="mygcalout.description" value="[ggl:description | def('')]"/> </rsb:call>  <rsb:set attr="diff.base" value="[_feeds.myspitems]"/> <rsb:set attr="diff.feed" value="[_feeds.mygitems]"/> <rsb:set attr="diff.filter" value="title,start_time"/> <rsb:set attr="diff.list" value="[list]"/> <rsb:call op="feedDiff" in="diff">  <rsb:set attr="name#" value="Title"/> <rsb:set attr="name#" value="Location"/> <rsb:set attr="name#" value="Start Time"/> <rsb:set attr="name#" value="End Time"/> <rsb:set attr="name#" value="Description"/> <rsb:set attr="value#" value="[title]"/> <rsb:set attr="value#" value="[location | def('')]"/> <rsb:set attr="value#" value="[start_time]"/> <rsb:set attr="value#" value="[end_time]"/> <rsb:set attr="value#" value="[description | def('')]"/> <rsb:call op="spAddItem"/> <b>[title]</b> Successfully imported from Google Calendar.<br> </rsb:call>

Step 5: After you click Apply, specify your Google Calendar email and password. Next, specify the name of the calendar to sync with (i.e. "Calendar"), and the number of days you want it to keep synced at a time. Optionally, you can embed inputs into the script, instead of leaving them as Web Part properties. To embed the inputs, change the rsb:info section of code to the following:

<rsb:set attr="email" value="YOUREMAIL" /> <rsb:set attr="password" value="YOURPASSWORD" /> <rsb:set attr="calendar" value="YOURCALENDARNAME" /> <rsb:set attr="days" value="30" />
Now, the Calendar page is automatically be updated with the latest items from Google Calendar.

Simple SharePoint Image Rotator

Wed, 13 Jan 2010 00:00:00 GMT

This example shows how to connect a dynamic image rotator Web Part with the SharePoint picture library.

Step 1: Ensure the RSSBus Web Part is installed.

Step 2: Choose your images and upload them to a Picture Library. The Picture Library should be located within the same site you plan to add the RSSBus Web Part to.

Step 3: Add the RSSBus Web Part to your page and paste the following template into its source editor.

 <rsb:set attr="list" value="Picture Library" />  <rsb:call op="spListItems" save="pictures" />  <rsb:call op="feedRandom?feed=[_feeds.pictures | urlencode]" pagesize="1">  <img src="[sp:url]" width=�150px� border="0" align="right" /> </rsb:call>
Paste the template into the source editor and change "Picture Library� to the name of your library. For best results size the pictures before uploading them to your library and delete the width="150px" setting from the image tag in the HTML.

List Document Libraries and Documents

Wed, 13 Jan 2010 00:00:00 GMT

SharePoint sites often contain multiple libraries, with each including dozens or even hundreds of documents. This example will detail how to create a Web Part that displays a list of the most recently modified document libraries/documents, along with how to simply view a quick list of all libraries.

Step 1: Ensure the RSSBus Web Part is installed.

Step 2: Make sure you have access to a few document libraries with documents inside.

Step 3: Add the RSSBus Web Part to your page and paste the following template into its source editor. Click "Apply" in the template editor and the template will go into effect, giving you a list of recently modified libraries and documents.

<rsb:info> <input name="maxlibraries" default="5" /> <input name="maxdocuments" default="5" /> </rsb:info> <rsb:call op="spListLists?listtype=DocumentLibrary" save="lists" /> <table width="100%"> <rsb:call op="feedSort?type=date&sort=-sp:item_modified&feed=[_feeds.lists | urlencode]" pagesize="[maxlibraries]" > <tr> <th style="background-color:LightGrey; white-space:nowrap" colspan="2"> <a href="[sp:url]">[rss:title]</a> </th> </tr> <rsb:call op="spListDocuments?library=[rss:title]" save="mydocs" /> <rsb:call op="feedSort?type=date&sort=-sp:modified&feed=[_feeds.mydocs | urlencode]" pagesize="[maxdocuments]" > <tr> <td><a href="[sp:url]">[rss:title]</a></td> <td> <rsb:set attr="today" value="[sp:modified | todate('MM/dd/yy')]" /> <rsb:equals attr="today" value="[null | date('MM/dd/yy')]"> [sp:modified | todate('hh:mm:ss')] <rsb:else> [sp:modified | todate('MM/dd/yy')] </rsb:else> </rsb:equals> </td> </tr> </rsb:call> </rsb:call> </table>
This template lists all of your document libraries and sorts them by the last modified date. Also, for each libraries, it lists the documents within and sorts by the last modified dates. The result is a view of the most recently modified documents on the site.

After you paste this template into the source editor, click 'Apply'. You can customize the HTML in the template, or leave it as is. At this point you can change maximum number of libraries and documents that will be listed in the view by setting those properties in the property tab of the designer.

As always, you can customize the html in the template, or leave it as is.

Multi-Source Search in SharePoint

Wed, 13 Jan 2010 00:00:00 GMT

Use the RSSBus WebPart to create a multi-source search in SharePoint, allowing you to enter one search term and see results from a variety of different search services. The RSSBus WebPart template is simple due to its built-in feed manipulation capabilities.
<div id="search"> <label for="search"><b>Search:</b></label> <input id="search" type="text" name="search" size="25" value="[_request.form:search| def('')]" /> <input type=submit value="Search" /> </div> <rsb:check attr="_request.form:search"> <rsb:set item="input" attr="feed#1" value="http://search.msn.com/results.aspx?q=[_request.form:search]&format=rss&FORM=ZZRE" /> <rsb:set item="input" attr="feed#2" value="http://news.search.yahoo.com/news/rss?ei=UTF-8&p=[_request.form:search]" /> <rsb:set item="input" attr="feed#3" value="http://www.flickr.com/services/feeds/photos_public.gne?tags=[_request.form:search]&format=rss_200" /> <rsb:set item="input" attr="feed#4" value="http://news.google.com/news?hl=en&q=[_request.form:search]&ie=UTF-8&output=rss" /> <rsb:set item="input" attr="feed#5" value="http://search.twitter.com/search.rss?q=[_request.form:search]" /> <rsb:call op="feedUnion" in="input"> <a href="[rss:link]">[rss:title]</a><br /> <div>[rss:description]</div><p /> <hr /> </rsb:call> </rsb:check>
The first five lines are HTML to create a search query text box and submit button. The form tag was not used, instead you can piggy back on the existing form available in every SharePoint page. Next, check to see if the search input variable exists in the form of the incoming HTTP request. If it is, then set source feeds, call for the union of the feeds (duplicates are automatically discarded), and output HTML for each result.

Usenet Newsgroup Browser in SharePoint

Wed, 13 Jan 2010 00:00:00 GMT

The following will show you how to add a Usenet Newsgroup browser to your SharePoint site with a short RSSBus SharePoint WebPart template. The template works by importing the RSSBus NntpOps Connector. The template calls the nntpListArticles operation to list the articles of a particular Newsgroup and displays them in a table. Then, when an article is clicked on, it calls the nntpGetArticle operation to retrieve the actual article (only the first 1000 lines of the article). If the thread is larger than 1000 lines, it will provide a link to view the message in Google Groups. Copy and paste the WebPart template below into your own RSSBus SharePoint WebPart (with the NntpOps connector installed) and it will work right away. This template browses the microsoft.public.sharepoint.windowsservices group (you can set your own news server, group, etc.).
<rsb:import lib="RSSBus.NntpOps" /> <div style="height:100;overflow:auto"> <table width="98%"> <tr> <th style="background-color:LightGrey; white-space:nowrap">Subject</th> <th style="background-color:LightGrey; white-space:nowrap">Author</th> <th style="background-color:LightGrey; white-space:nowrap">Size</th> <th style="background-color:LightGrey; white-space:nowrap">Date</th> </tr> <rsb:set attr="group" value="microsoft.public.sharepoint.windowsservices" /> <rsb:set attr="range" value="25" /> <rsb:set attr="server" value="msnews.microsoft.com" /> <rsb:call op="nntpListArticles"> <tr> <td style="white-space:nowrap"><a href="?msgnumber=[nntp:number]">[nntp:subject | truncate('60')]</a></td> <td style="white-space:nowrap">[nntp:author]</td> <td style="white-space:nowrap">[nntp:size]</td> <td style="white-space:nowrap">[nntp:date]</td> </tr> </rsb:call> </table> </div> <div> <rsb:check attr="_request.msgnumber"> <hr /> <rsb:call op="nntpGetArticle?maxlines=1000&article=[_request.msgnumber]"> <div id="headers"> <table width="98%" style="background-color:LightGrey"> <tr> <td width="75px">From:</td> <td><a href="mailto:[nntp:author | htmlencode]">[nntp:author | htmlencode]</a></td> </tr> <tr> <td width="75px">Date:</td> <td>[nntp:date]</td> </tr> <tr> <td width="75px">Subject:</td> <td>[nntp:subject]</td> </tr> <tr /> <tr> <td colspan="2"><a href="http://groups.google.com/groups?threadm=[nntp:id | Replace('<', '') | Replace('>', '') | urlencode]">View in Google Groups</a></td> </tr> </table> </div> <div id="body" style="height:400;overflow:auto"> <hr /> [nntp:body | Replace('0A','<br>','true')] </div> <p /> <a href="http://groups.google.com/groups?threadm=[nntp:id | Replace('<', '') | Replace('>', '') | urlencode]">View in Google Groups</a> </rsb:call> </rsb:check> </div>
If you want to make the news server and group editable via the "Modified Shared WebPart" property settings, simply delete the rsb:set lines where they are hard-coded in the template, and add the following at the top:
<rsb:info title="RSSBus SharePoint WebPart: USENET Newsgroup Browser"> <input name="Server" default="msnews.microsoft.com" required="true" /> <input name="Group" default="microsoft.public.sharepoint.windowsservices" required="true" /> <input name="Range" default="25" /> </rsb:info>
The above tells the WebPart to create three inputs: Server, Group, and Range. The NntpOps Connector will be able to see the inputs and work with them.

Customize SharePoint RSS Feeds

Wed, 13 Jan 2010 00:00:00 GMT

The default RSS feeds in SharePoint are basic and can be customized with extended RSS meta-information.

First, install the RSSBus SharePoint WebPart. After running the setup, go to the Site Collection Features page to activate the Web Part. In order to create custom RSS feeds, activate the "RSSBus Service Handler" feature.

In this example a special document library called "Really Simple Services" was created for the feeds. For the first feed, a new document was added to the library and named "sp-rss.rst" with the following contents:
<rsb:info title="My Custom RSS Feed" description="This is my custom RSS feed description" />  <rsb:set attr="list" value="Shared Documents"/> <rsb:set item="_meta" attr="rss:title" value="Custom SharePoint RSS: [list]" /> <rsb:call op="spListItems" output="out"> <rsb:push title="[rss:title | def('untitled item')]"> [out.*] </rsb:push> </rsb:call>
Next, call this custom RSS Feed as would be expected (i.e. http://myserver/Really Simple Services/sp-rss.rsb). Except, when you hit the URL instead of downloading a document, you will get an RSS Feed. This example generates an RSS Feed out of a specific list, but it can be changed to generate an RSS feed out of any list by getting the list from the URL. For example:
<rsb:info> <input name="list" default="Shared Documents" /> </rsb:info> <rsb:set item="_meta" attr="rss:title" value="Custom SharePoint RSS: [list]" /> <rsb:call op="spListItems" output="out"> <rsb:push title="[rss:title | def('untitled item')]"> [out.*] </rsb:push> </rsb:call>
Now, if you call the same URL, the Shared Documents will be returned. But, if a specific list is added to the URL, that list will be returned instead For example: http://myserver/Really Simple Services/sp-rss.rsb?list=Other Documents. The difference in the script itself is that the "list" name has been moved from an rsb:set (hard-coded) to the rsb:info input.

A SharePoint Web Part for Shipment Tracking

Wed, 13 Jan 2010 00:00:00 GMT

This sample allows the user to input a tracking number for UPS, USPS, or Fedex and then display tracking events in an easy-to-read table.

Step 1: Ensure the RSSBus Web Part is installed.

Step 2: Install the UPS, USPS, and/or FedEx Connectors. You don�t need them all if you don�t want them all, you only need the ones you want to be able to track packages for. Make sure that you have service credentials for the shipping companies you want to use. UPS, USPS, and Fedex all require user/password/etc in order to reach their package tracking web services.
Step 3: Add the RSSBus WebPart to your page and paste the following template into its source editor, and click "Apply".

<rsb:info> <input name="ups-accesskey" required="true" /> <input name="ups-accountnumber" required="true" /> <input name="ups-user" required="true" /> <input name="ups-password" required="true" /> <input name="usps-user" required="true" /> <input name="usps-password" required="true" /> <input name="fedex-accountnumber" required="true" /> <input name="fedex-meternumber" required="true" /> <input name="fedex-developerkey" required="true" /> <input name="fedex-password" required="true" /> </rsb:info>  <div id="trackinginput"> <table cellspacing="5px"> <tr> <td>Tracking Number:</td> <td> <input type="text" name="trackingnumber" value="" size="25"/> </td> <td align="right" colspan="2"> <input type="radio" name="shipper" value="UPS">UPS <input type="radio" name="shipper" value="USPS">USPS <input type="radio" name="shipper" value="FedEx" checked>FedEx </td> <td align="right"> <input type="submit" value="Track" /> </td> </tr> </table> </div>  <rsb:check attr="_request.form:trackingnumber"> <div style="height:250;overflow:auto"> <rsb:select value="[_request.form:shipper | tolower]"> <rsb:case match="exact" value="ups"> <rsb:import lib="RSSBus.UpsOps" /> <rsb:set attr="ups.user" value="[ups-user]" /> <rsb:set attr="ups.password" value="[ups-password]" /> <rsb:set attr="ups.accesskey" value="[ups-accesskey]" /> <rsb:set attr="ups.accountnumber" value="[ups-accountnumber]" /> <rsb:set attr="ups.url" value="https://wwwcie.ups.com/ups.app/xml/Track" /> <rsb:set attr="ups.trackingid" value="[_request.form:trackingnumber]" /> <rsb:set attr="global.operation" value="upsTrack" /> <rsb:set attr="global.input" value="ups" /> </rsb:case> <rsb:case match="exact" value="usps"> <rsb:import lib="RSSBus.UspsOps" /> <rsb:set attr="usps.user" value="[usps-user]" /> <rsb:set attr="usps.password" value="[usps-password]" /> <rsb:set attr="usps.url" value="http://production.shippingapis.com/ShippingAPI.dll" /> <rsb:set attr="usps.trackingid" value="[_request.form:trackingnumber]" /> <rsb:set attr="global.operation" value="uspsTrack" /> <rsb:set attr="global.input" value="usps" /> </rsb:case> <rsb:case match="exact" value="fedex"> <rsb:import lib="RSSBus.FedexOps" /> <rsb:set attr="fedex.password" value="[fedex-password]" /> <rsb:set attr="fedex.accountnumber" value="[fedex-accountnumber]" /> <rsb:set attr="fedex.meternumber" value="[fedex-meternumber]" /> <rsb:set attr="fedex.developerkey" value="[fedex-developerkey]" /> <rsb:set attr="fedex.trackingid" value="[_request.form:trackingnumber]" /> <rsb:set attr="fedex.trackingidtype" value="TRACKINGNUMBERORDOORTAG" /> <rsb:set attr="fedex.url" value="https://gatewaybeta.fedex.com:443/xml" /> <rsb:set attr="global.operation" value="fedexTrack" /> <rsb:set attr="global.input" value="fedex" /> </rsb:case> </rsb:select> <rsb:call op="[global.operation]" in="[global.input]"> <rsb:first> <table width="100%"> <tr> <th style="background-color:LightGrey; white-space:nowrap">Status</th> <th style="background-color:LightGrey; white-space:nowrap">Date/Time</th> <th style="background-color:LightGrey; white-space:nowrap">Location</th> </tr> </rsb:first> <tr> <td>[scandesc]</td> <td>[scandate] [scantime]</td> <td>[scancity], [scanstate] [scancountry]</td> <tr> <rsb:last> </table> </rsb:last> </rsb:call> </div> </rsb:check>

Step 4: Specify your account credentials. If you clicked "Apply" in the source editor of the Web Part, you should now see a new property appear in the properties editor of the Web Part in your browser. Under "Web Part Inputs", you'll see "ups-accesskey", "ups-accountnumber", and several other credential inputs. These inputs will need to be populated with the values corresponding to your web service account credentials obtained from UPS, USPS, and/or FedEx. If you�re not using one or more of these shipping companies, just insert a dummy value or modify the template so that input is not required.

After you have set these inputs appropriately, click "Apply" in the properties window and the page will reload. Enter a tracking number, select the shipping company, and click "Track". The tracking events will be displayed inside the web part!
As always, you can customize the HTML in the template, or leave it as is.

Twitter Search Web Part

Wed, 13 Jan 2010 00:00:00 GMT

The following will guide you through including Twitter searches and streams inside SharePoint pages. With Twitter API and RSS feeds, the process is simplified. Also, by using a different RSS or Atom Feed to start from, you may change from showing searches, mentions, direct messages, or other Twitter streams.

Step 1: Ensure the RSSBus Web Part is installed.

Step 2: Find the RSS or Atom Feed URL for the Twitter stream you want to display. For this example, Twitter search results are shown for the keywords "SharePoint" , "PowerShell", and �RSSBus� (this step can be skipped if you only want to see it work). When you are ready to try it with your own search or another Twitter stream, simply provide your URL. If you are unsure how to get this RSS feed, see the instructions are below:

Twitter Search: Visit search.twitter.com, perform your search, and look for the "Feed for this query" icon on the top right of the page.

Public Timeline: Visit your profile and look for the �RSS feed of user name's tweets� icon on the right side of the page.

Twitter Friends Timeline: Visit your profile and look for the "RSS feed" icon on the bottom right of the page.

Twitter Favorites: Visit your favorites page on Twitter and look for the "RSS feed for favorites" icon on the bottom right of the page.

Friends Timelines: Visit your friend�s page and look for the "RSS feed of friend's name tweets" icon on the right side of the page.

Advanced Tip: Custom Twitter feeds can be made out of @replies, direct messages, or combined feeds by using the TwitterOps Connector. With the TwitterOps Connector, you have complete control enabling you to send direct messages, send replies, get following and follower lists, or combine two or more of the previous options into one feed. The easiest way to test TwitterOps before importing it into an actual Web Part is by using the RSSBus Server Admin Console.

Step 3: Add the RSSBus WebPart to your page and paste the following template into its source editor. Next, click "Apply". The Web Part will be rendered with the results of the Twitter search results for SharePoint, PowerShell, and RSSBus.

<rsb:info title="Twitter Search" > <input name="SearchPhrase" description="The search phrase to use." def="SharePoint OR PowerShell OR RSSBus"/> </rsb:info> <div style="height:60em;width:100%;overflow:auto;padding:10px"> <rsb:call op="http://search.twitter.com/search.rss?q=[SearchPhrase]" pagesize="10"> <div style="float:left;background-color:whiteSmoke;margin-bottom:20px"> <div style="float:left;width:40px;margin-bottom:5px"> <a href="[rss:link]"><img align="left" src="[google:image_link]" border="0" width="33px"/></a> </div> <div><a href="[rss:link]">[rss:author]</a></div> <div style="color:gray">[rss:pubDate]</div> <div style="float:left;width:100%">[rss:description]</div> </div> </rsb:call> </div>

Step 4: Customize the template for your specific needs. If you clicked "Apply" in the source editor of the Web Part, a new property appeared in the properties editor of the Web Part in your browser. Under "Web Part Inputs", you will find a new "SearchPhrase" input. Put your own search criteria here. Optionally, if you want to only show a stream such as your friend's timeline, replace the following line:
<rsb:call op="http://search.twitter.com/search.rss?q=[SearchPhrase]" pagesize="10">
with this:
<rsb:call op="ENTER_YOUR_URL_HERE" pagesize="10">

Note the pagesize="10". This tells the Web Part to only show 10 of the items in the feed. This option is customizable and can be removed to list all feeds.

RSSBus Articles

OLAP Reporting Directly from QuickBooks using ComponentOne OLAP for WinForms

Installing RSSBus QuickBooks Connector

Setting Up QuickBooks

Running the Sample RSSBus Project

Building the OLAP App

Summary

Resources

Charting Data from SalesForce

Chart Sample Project

Accessing website data using ADO.NET

Introduction

Connections

The DataSet Designer

Target Framework

Loading the data

Update

Where next

Use PowerShell to List Google Docs

Using the Google Data Provider in PowerShell to List Google Docs:

Download a file from Google Docs:

Hooking into Google Apps With RSSBus Google Data Provider

Introduction

Installing the Software

The Data Provider Schema

Using the Data Provider

Query Limitations

The Code

Caching

Conclusion

Publishing LightSwitch Applications

Modify the Web.config

Add Missing DLLs

Connect to QuickBooks From LightSwitch Applications

Before We Begin

LightSwitch Sample Project

LINQ to QuickBooks

Demo: Migrate Data from Salesforce to SharePoint

Easy Data Migration from Salesforce to SharePoint

Setting Up Pseudo Columns with LightSwitch.

What are pseudo columns?

Setting Up a LightSwitch Project

Using the Pseudo Columns

That's it!

Faster Access to QuickBooks using the QuickBooks Data Provider

Creating the Cache

Keeping the Cache up to Date

Automating the Caching Process

Faster Access to Salesforce.com using the Salesforce Data Provider

Creating the Cache

Keeping the Cache up to Date

Automating the Caching Process

Accessing SharePoint Online with the RSSBus SharePoint Data Provider.

Authenticating to SharePoint Online in your program.

Authenticating with Server Explorer.

Connect SalesForce to SQL Server through SSIS

SSIS Sample Project

Connect SharePoint to SQL Server through SSIS

SSIS Sample Project

Connect QuickBooks to SQL Server through SSIS

SSIS Sample Project

RSSBus in the Cloud: Deploying AS2 Connector to Windows Azure

A Base Assumption

Deploying AS2 Connector to Windows Azure

Creating a Certificate

Creating and Deploying a Hosted Service

Upgrading to AS2 Connector V3

Tutorial: RSSBus AppServer Events

Learning what events an application offers

A Simple Example: Running a Batch File After Sending a File in AS2 Connect

Taking Control of Events

Tutorial: Hosting RSSBus Apps in IIS

Table of Contents

Hosting RSSBus Apps in IIS

Troubleshooting

Windows 7 or Windows Server 2008 R2 (IIS 7.5)

Create a Web Application

Configure Permissions

Using ApplicationPoolIdentity

Alternate: Configure Permissions For A Managed Service Account Identity