As mentioned previously, the pubs
sample database for SQL Server is the basis of our SQL Server data. Before we
can add tables to the view, we need to set up a connection. To set up our
tables in Business Views we will first make a data connection to the SQL
Server. Simply open the Business Views application and connect to your server.
You will see a screen similar to the one in Figure 1. Now select Data
Connection and click on Create. This process is similar to setting up data
connections in Crystal Reports, so your knowledge there will help you here.
Figure 1
A wizard steps you through the steps of creating the
connection to SQL. In my case I selected OleDB (ADO) as the Data Source type
and then SQL Native Client. After selecting my server and the pubs database,
that first data connection is set up. Let us name this connection
DataConnection1.
Now let us quickly set up the connection to our web service
class. For this I use the ADO.NET data type. When it prompts you for a
filepath, point to the path containing the dll to the class we created. You
will need to select "File of Type" and ".NET DataSet
Provider." Once this is selected then check the box "Use Classes
from Project." From this there is a drop down box and you can select ERPReturnCustomers.Mocks.MfgLocationMock.
Finally, check the box "use dataset from class" and select
ReturnLocations. We will name this DataConnection2. Look at Figure 2 for a
sample of what this should look like.
Figure 2
Now that our data connections are set up let us get the
tables linked! To do that, we create a Data Foundation.
To create a data foundation, select Data Foundation from the
New File Menu option. You then step through a wizard to help define the
Foundation. In this sample we select DataConnection1 to help select the
tables. See Figure 3 for an example of how to set up that connection. Once the
pubs database is selected pick the following tables:
·
Authors
·
Publishers
·
Titleauthor
·
Titles
·
Sales
·
Stores
Figure 3
After selecting these tables they should display graphically
for you. The Insert Data Tables dialog box should still show. Now we can add
the web service table by clicking on add connection. Next, select
DataConnection2 from the available connections. After clicking OK (you may
need to click cancel after that), you should be back in the Insert Data Tables
dialog box, with DataConnection2 showing up (or whatever you named your data
connection for the web service). Click on the table MfgLocation and then click
"Add" to add this to the other tables in the Data Foundation.
You should be able to link using drag and drop or using the
option Smart Linking by Key. Using that option links all tables correctly,
except the mfgLocation table. You need to manually link this by dragging the
mfgid field from sales onto the ID field in the mfgLocation table. Now your
Data Foundation is ready!
