Database Lookup

WebMap can use Local Knowledge to search on an address and then return results from a database table rather than a polygon.

1. To do this begin by creating a connection string for the database (or use an existing connection if you already have one set up.)

2. Next make these changes to the webMapLayersConfig.db (found in C:\inetpub\wwwroot\SISWebMap9.0\App_Data).

We recommend BACKING UP THE CONFIG DATABASE BEFORE MAKING ANY CHANGES.

Open the webMapLayersConfig.db database in a SQlite manager/editor.

Navigate to the ConnectionStrings table and note the ID of the connection string.

Add a new row in DBSearch.

Field	Description
DBSearch	'Cadcorp.NoticeBoard.DatabaseSearch.DataBaseConnector,Cadcorp.NoticeBoard.DatabaseSearch',
ConnectionStringID	Enter the connection string ID you noted earlier.
SEARCHCLASS	This value remains unchanged.
TableName	Name of the table to be searched.

Note the created DBSearchID.

For each column that is required as either a search column, orderby column or result column, add an entry into DBSearch_Columns and note the ColumnIDs.

Results

For each column required in the results, add a row to DBSearch_ResultColumns; ensure you have at least one result column.

INSERT INTO DBSearch_ResultColumns (DBSearchID,ColumnID) VALUES(<search ID>, <Column ID>);
Replace <search ID> with the id of the search from DBSearch
Replace <Column ID> with the id of a column from DBSearch_Columns

Order Search Results

To order search results, add entries into DBSearch_ResultsOrderColumns.

INSERT INTO DBSearch_ResultColumns (DBSearchID,ColumnID,[Index]) VALUES(<search ID>,<Column ID>,<index>);
Replace <search ID> with the id of the search from DBSearch
Replace <Column ID> with the id of a column from DBSearch_Columns
Replace <index> with a number representing the order the columns will be added to the order by clause.

Define columns to search

Now define the columns to search by adding rows to DBSearch_SearchColumns; ensure you add at least one column.

INSERT INTO DBSearch_SearchColumns (DBSearchID,ColumnID) VALUES(<search ID>, <Column ID>);
Replace <search ID> with the id of the search from DBSearch
Replace <Column ID> with the id of a column from DBSearch_Columns

Add Templates

Now add 2 templates to the Templates table. Remember to note the IDs of the templates.

1. The first template is used when nothing is found after running the search.
2. The second template is used for displaying the results.

Ensure the templates conform to a standard Local Knowledge template.

The template will be passed a JavaScript object that has an Items array; each item in the array is a row returned from the database.

To access the data, use the column name OR the column alias (if one has been defined.)

Link the database search

Finally to link the local knowledge to a database search, add a row to LocalKnowledge_DBSearch.

INSERT INTO LocalKnowledge_DBSearch (LocalKnowledgeID,DBSearchID,[Index],NothingFoundTemplateID,TemplateID) 
VALUES(<localKnowledgeID>, <searchID>, <index>, <nothingfoundtemplateid>, <templateid>);
Replace <localKnowledgeID> with the id of the local knowledge to link to in the table LocalKnowledge
Replace <searchID> with the id of the new search from DBSearch
Replace <index> with a number representing the order of the search
Replace <nothingfoundtemplateid> with the id of the template for when nothing is found
Replace <templateid> with the id of template for the results

Ensure the changes have been committed and restart the application pool.

---