Multiple Databases

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Sunday, June 16, 2013PrintSubscribe
Multiple Databases

Multiple database services are common in the modern business environment. Code On Time generator has the capability to tie this data together in a single web application.

In this example, DB1 holds the Categories and Products tables of an web-based order management system. DB2 holds the Suppliers table used by the purchasing department. You can see the database schemas in the picture below.

The schemas for databases DB1 and DB2.

Suppose that business requirements demand displaying vendor information next to the product name. We would like to add fields in Products to capture information from the Supplier, as well as allow access to information about Suppliers, all in the same application.

How do we overcome the physical separation of DB1 and DB2?

Take a look at the default web app generated from DB1 below. You will see that two pages have been created for Categories and Products, respectively. We will need to add Suppliers to this web app as well.

Categories and Products are available in the DB1 database.

Code On Time Generator does not support generation from multiple databases. Creating an application that handles different data sources will require a combined sample database that you can generate from. After the web app is created, you can change the connection strings to connect the remote data. In this example, it would be easiest to add the Suppliers table to DB1. Also, add SupplierID and SupplierCompanyName to the Products table. These fields, while not foreign keys in the database, will capture information from DB2 when we set them up as lookups.

Adding Suppliers Table to DB1

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / DB1 / Tables node, and press New Table.

Adding a new table to database DB1.

Assign the following values:

Column Name Data Type Allow Nulls
SupplierID int no
CompanyName nvarchar(50) no
ContactName nvarchar(50) yes
ContactTitle nvarchar(50) yes
Address nvarchar(50) yes
City nvarchar(50) yes
Region nvarchar(50) yes
PostalCode nvarchar(50) yes
Country nvarchar(50) yes
Phone nvarchar(50) yes
Fax nvarchar(50) yes
HomePage ntext yes

Press Ctrl+S to save the table. Assign the table a name of “Suppliers”.

Entering the name for 'Suppliers' table.

In the Object Explorer, right-click on Databases / DB1 / Tables / dbo.Products table node, and press Design.

Designing the Products table of database DB1.

Add the following columns:

Column Name Data Type Allow Nulls
SupplierID int yes
SupplierCompanyName nvarchar(50) yes

Save the table. Switch back to the app generator, click on the project name, and press Refresh.

Check the boxes next to Products controller and dbo.Suppliers table, and click Refresh.

Refreshing Products and Suppliers tables.

Proceed to regenerate the application.

When complete, notice that the Suppliers page has been added to the web app. However, there are no suppliers to be found. We will need to change the connection string for the Suppliers controller.

No suppliers are present in DB1 database.

Adding a Second Connection String

Switch back to the app generator. Click on the project name, and press Settings. Click on Web Server Configuration.

In the Web.Config modification instructions textbox, add the following:

AppendChild: /configuration/connectionStrings
<add name="DB2" connectionString="Data Source=.;Initial Catalog=DB2;Integrated Security=True;" providerName="System.Data.SqlClient" />

Press Finish to skip to the Summary page. Click Design to activate the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Suppliers controller node.

The Suppliers controller selected in the Project Explorer.

Change the Connection String Name property:

Property Value
Connection String Name DB2

Press OK to save.

Configuring Cross-Database Lookups

Double-click on Products / Fields / SupplierID node.

SupplierID field in the Products controller.

Make the following changes:

Property Value
Items Style Lookup
Items Data Controller Suppliers
Data Value Field SupplierID
Data Text Field CompanyName
New Data View createForm1
Copy SupplierCompanyName=CompanyName

Press OK to save. At the top of the Project Browser window, switch to the Data Fields tab. Assign an Alias of “SupplierCompanyName” to all SupplierID data fields.

Setting the Alias of SupplierID data fields to 'SupplierCompanyName'.

In the Project Explorer, double-click on Products / Fields / SupplierCompanyName (String(50)) node.

SupplierCompanyName field selected from the Products controller.

Switch to the Data Fields tab. Mark all data fields as hidden.

Marking the SupplierCompanyName data fields as hidden.

Viewing the Results

On the toolbar, press Browse. Navigate to the Products page, and start editing a record. Click on the SupplierCompanyName lookup – a list of suppliers will be displayed.

The lookup displays a list of suppliers.

Select a supplier. The ID will be inserted into the field, but the name will be displayed.

The Supplier lookup displays the Company Name.