Tutorials

Tutorials

Driving a Search Form from a Database Table

InputDatabaseTest reads search terms from a SQL Server table with GPALDatabase, fills a search box with each row in turn using FillInFrom, and runs a full scrape-and-paginate workflow after every fill via CallAfterFillIn. It's the do-gpaldatabase pattern: let a table drive a repeated workflow instead of hardcoding values.

Complete Program

Here's the whole workflow, start to finish. Each piece is broken down and explained below.

using GenerallyPositive;

using GenerallyPositive.Browser;

using static GenerallyPositive.Enums;

public static IGPALGrid<string> retGrid;

int fileNameIdx = 0;

GPALDatabase database = (GPALDatabase)GPAL.Database

.WithServerName(@"MYSERVERSQLEXPRESS")

.WithDatabaseName("GPAL")

.WithDatabaseType(DatabaseType.SQLServer)

.WithTableName("TestInput")

.WithRowCount(4);

retGrid = GPAL.Grid.ToGPALObject();

Selector searchInput = (Selector)GPAL.Selector

.WithSelectorName("SearchInput")

.WithCSS("#gh-ac")

.WithXPath("//*[@id='gh-ac']")

.MatchPlaceholder("Search for anything");

IBrowser browser = GPAL.Browser

.WithBrowserType(BrowserType.FireFox)

.WithDriverLocation(@"c:drivers")

.GoTo("https://example.com")

.WithSelector(searchInput)

.CallAfterFillIn(CallAfterFillIn);

browser.FillInFrom(database);

browser.Close(true);

public static CallIfStatus CallAfterFillIn(IBrowser browser, IGPALGrid<string> tokens, int tokenIdx)

{

GPALFile fileList = $@"c: emp esults{fileNameIdx++}.txt";

Selector searchButton = (Selector)GPAL.Selector

.WithSelectorName("SearchButton")

.WithCSS("#gh-btn")

.WithXPath("//*[@id='gh-btn']")

.MatchText("Search");

Selector descriptionColumn = (Selector)GPAL.Selector

.WithSelectorName("Description")

.WithCSS("#srp-river-results > ul > li > div > div.s-item__info > a > h3");

Selector priceColumn = (Selector)GPAL.Selector

.WithSelectorName("Price")

.WithCSS("#srp-river-results > ul > li > div > div.s-item__info > div > div:nth-child(1) > span");

browser

.WithSelector(searchButton)

.LeftClick()

.WithSelector(descriptionColumn)

.WithSelector(priceColumn)

.WithAllThatMatch()

.GetGrid(out retGrid)

.SaveToTabbedText(fileList);

return CallIfStatus.Handled;

}

Describe the Source Table with GPALDatabase

GPAL.Database is a fluent factory just like GPAL.Browser. WithServerName, WithDatabaseName, and WithDatabaseType point it at a SQL Server instance, WithTableName picks the table to read from, and WithRowCount caps how many rows the workflow will process.

GPALDatabase database = (GPALDatabase)GPAL.Database

.WithServerName(@"MYSERVERSQLEXPRESS")

.WithDatabaseName("GPAL")

.WithDatabaseType(DatabaseType.SQLServer)

.WithTableName("TestInput")

.WithRowCount(4);

TIP

Like GPALUrl and GPALFile, GPALDatabase carries its configuration with it - build it once and pass it directly into FillInFrom rather than opening a connection yourself. See GPALDatabase: Querying and Writing SQL.

Target the Input and Register CallAfterFillIn

WithSelector points at the search box, and CallAfterFillIn registers a callback that GPAL will run after each value from the database is typed in. The callback receives the grid of values being iterated (tokens) and the current row index (tokenIdx).

Selector searchInput = (Selector)GPAL.Selector

.WithSelectorName("SearchInput")

.WithCSS("#gh-ac")

.WithXPath("//*[@id='gh-ac']")

.MatchPlaceholder("Search for anything");

IBrowser browser = GPAL.Browser

.WithBrowserType(BrowserType.FireFox)

.WithDriverLocation(@"c:drivers")

.GoTo("https://example.com")

.WithSelector(searchInput)

.CallAfterFillIn(CallAfterFillIn);

FillInFrom(database) Drives the Loop

Passing the GPALDatabase object to FillInFrom tells GPAL to query the table, then for each row, fill the targeted selector with that row's value and fire CallAfterFillIn. One call replaces a manual loop over query results plus a manual fill-and-search for each one.

browser.FillInFrom(database);

browser.Close(true);

WARNING

FillInFrom(database) blocks until every row has been processed and CallAfterFillIn has run for each one - Close(true) only fires once the entire database-driven loop finishes.

CallAfterFillIn: Search, Scrape, and Save Per Row

Inside the callback, the workflow clicks the search button, collects description and price columns from every matching result with WithAllThatMatch, pulls them into a grid with GetGrid, and writes each row's results to its own tabbed text file using a counter to keep filenames unique.

public static CallIfStatus CallAfterFillIn(IBrowser browser, IGPALGrid<string> tokens, int tokenIdx)

{

GPALFile fileList = $@"c: emp esults{fileNameIdx++}.txt";

browser

.WithSelector(searchButton)

.LeftClick()

.WithSelector(descriptionColumn)

.WithSelector(priceColumn)

.WithAllThatMatch()

.GetGrid(out retGrid)

.SaveToTabbedText(fileList);

return CallIfStatus.Handled;

}

TIP

CallAfterFillIn must return a CallIfStatus. Returning Handled tells GPAL the row was processed successfully and it's safe to move on to the next one from the database. See Data-Driven Forms: FillInFrom and CallAfterFillIn.

On this page

Tutorials
Driving a Search Form from a Database Table
  • Complete Program
  • Describe the Source Table with GPALDatabase
  • Target the Input and Register CallAfterFillIn
  • FillInFrom(database) Drives the Loop
  • CallAfterFillIn: Search, Scrape, and Save Per Row