SmartQueryAdapter

Overview

The SmartQueryAdapter acts as an intermediary layer between an AI model and a BindingSource (specified by the DataSource property).

Uses the Query string and Schema table to construct and execute an SQL statement, returning any data retrieved from the database.

Default prompt

#
# SmartQueryAdapter
#
[SmartQueryAdapter.Prompt]
You are a skilled SQL programmer.
Your job is to generate a valid SQL statement for the database "{{database-name}}" from the user’s question.

## Instructions:
- Use only the tables defined in the Database Schema
- The generated SQL statement MUST be valid for "{{server-type}}"
- Define column aliases within quotes, with proper casing and with a space in between words
- Output only the SQL statement without any text before or after
- Enclose column names in []

```Database Schema
{{database-schema}}
```

Configuration

[WorksWith(typeof(SmartHttpEndpoint))]

Properties

In addition to the properties inherited from the SmartAdapter, the SmartQueryAdapter exposes the following additional properties.

Query

The Query property is used to specify the query string that will be sent to the AI model for generating an SQL statement. For example, if you set this.adapter.Query = "Total sales in Canada for the past 5 years", Wisej.AI sends this natural language query to the AI model, which then generates a valid SQL statement based on the request. The generated SQL statement is returned in the SQL property.

Additionally, the SQL statement is executed, and the resulting data is assigned to the DataSource property.

When the AutoRun property is set to true, assigning a value to the Query property automatically prompts the AI model to generate and execute the corresponding SQL statement.

ServerType

The ServerType property is a string that specifies the target database server for which valid SQL should be generated. By default, this property is set to "Microsoft SQL Server".

DataSource

he DataSource property refers to an instance of the Wisej.NET BindingSource class. When the adapter executes an SQL statement, it assigns the resulting DataSet to the DataSource property of the BindingSource.

You have the option to assign your own BindingSource instance to the adapter before executing the SQL statement, or you can use the default instance created by the adapter. If you have any Wisej.NET controls bound to this DataSource, they will be automatically populated with data when the adapter executes the SQL query.

Connection

The Connection property represents an ADO.NET connection to a database. You must assign a valid instance of an ADO.NET connection object (such as SqlConnection, OleDbConnection, or NpgsqlConnection). When required, the adapter will automatically invoke the Open() method on the provided connection to establish a connection to the database. Ensure the connection object is properly configured before assigning it to this property.

Schema

The Schema property is a DataTable that defines the structure of the tables and columns accessible to the AI model. This property follows the standard "Columns" schema collection format. Wisej.AI specifically utilizes the following columns from this schema: "TABLE_NAME", "COLUMN_NAME", and "DATA_TYPE".

It is important to note that Wisej.AI does not automatically detect foreign key relationships from the schema. Instead, it relies solely on column names to infer table relationships. For optimal results, it is recommended to create simplified database views with clear and consistent naming conventions. This approach helps the AI model better understand table associations and valid join conditions. Use meaningful and consistent column names to explicitly indicate relationships. For example:

• Invoice (InvoiceId, Date, Number)

• InvoiceLine (InvoiceId, LineNumber, Quantity, Description, Price, ItemId)

By using the same column name (such as InvoiceId) in related tables, you make the relationships clear and more easily recognizable to the model. This practice enhances the AI's ability to generate accurate queries and understand the data structure.

SchemaTables

An array of strings that identifies the tables to be included when constructing the schema context for the AI model.

Utilize this property to restrict the schema to only include tables or views specifically intended for use by the AI model. By doing so, you can optimize the data structure for the AI's needs, eliminating extraneous data and focusing solely on the tables or views that are purpose-built for the AI's functionality and analysis.

SQL

The SQL property is a read-only property that returns the SQL statement generated by the model.