# SmartQueryAdapter

## Overview

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

Uses the [Query](#query) string and [Schema](#schema) table to construct and execute an [SQL](#sql) statement, returning any data retrieved from the database.

{% code title="Default prompt" overflow="wrap" %}

````ini
#
# 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}}
```

````

{% endcode %}

## Configuration

```csharp
[WorksWith(typeof(SmartHttpEndpoint))]
```

## Properties

In addition to the properties inherited from the [SmartAdapter](https://docs.wisej.com/ai/components/adapters/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](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/common-schema-collections) 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.