Код: Выделить всё
using Microsoft.SemanticKernel.ChatCompletion;
using Microsoft.SemanticKernel;
using MySql.Data.MySqlClient;
namespace AI_SQL_Integration
class Program
{
// Define the connection string for MySQL
private static readonly string connectionString = ""; // MySQL Database Connection
static async Task Main(string[] args)
{
//var completionConfig = new OpenAIChatCompletionConfig
//{
// Temperature = 0.5 // Set the temperature here
//};
// Configure the Semantic Kernel
var kernelBuilder = Kernel.CreateBuilder();
#pragma warning disable SKEXP0010
var kernel = kernelBuilder
.AddOpenAIChatCompletion(
modelId: "llama2",
apiKey: "",
endpoint: new Uri("") //endpoint
)
.Build();
// Create the chat service
var aiModel = kernel.GetRequiredService();
using var connection = new MySqlConnection(connectionString);
try
{
await connection.OpenAsync();
Console.WriteLine("Connection to the database was successful.");
}
catch (Exception ex)
{
Console.WriteLine($"Error connecting to the database: {ex.Message}");
return;
}
// Conversation loop
while (true)
{
Console.Write("Your question: ");
var question = Console.ReadLine();
try
{
// Get the schema of the database
var schema = await GetDatabaseSchemaAsync(connection);
// Generate the SQL query based on the user's question
var sqlQuery = await GenerateSQLQueryAsync(aiModel, question, schema);
// Execute the SQL query and get the results
var sqlResult = await ExecuteSQLQueryAsync(connection, sqlQuery);
// Generate a natural language response based on the SQL result
var naturalResponse = await GenerateNaturalResponseAsync(aiModel, question, schema, sqlQuery, sqlResult);
Console.WriteLine(naturalResponse);
}
catch (Exception ex)
{
Console.WriteLine($"Sorry, it seems there was an issue with my previous response. {ex.Message}");
Console.WriteLine("To answer your question accurately, I would need to see the actual SQL query you are using and the structure of your database.");
Console.WriteLine("Could you please provide me with that information so I can assist you better?");
}
}
}
private static async Task GetDatabaseSchemaAsync(MySqlConnection connection)
{
var schemaQuery = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'new_fegime'";
var schemaInfo = string.Empty;
using (var command = new MySqlCommand(schemaQuery, connection))
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
schemaInfo += $"Table: {reader["TABLE_NAME"]}, Column: {reader["COLUMN_NAME"]}, Type: {reader["DATA_TYPE"]}\n";
}
}
return schemaInfo;
}
private static async Task GenerateSQLQueryAsync(IChatCompletionService aiModel, string question, string schema)
{
var prompt = $@"
Based on the table schema below, write a SQL query that would answer the user's question:
{schema}
Question: {question}
SQL Query:";
var response = string.Empty;
await foreach (var message in aiModel.GetStreamingChatMessageContentsAsync(prompt))
{
response += message;
}
return response.Trim();
}
private static async Task ExecuteSQLQueryAsync(MySqlConnection connection, string sqlQuery)
{
var result = string.Empty;
try
{
var command = new MySqlCommand(sqlQuery, connection);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
for (int i = 0; i < reader.FieldCount; i++)
{
result += $"{reader.GetName(i)}: {reader.GetValue(i)}\t";
}
result += "\n";
}
}
catch (Exception ex)
{
result = $"Error executing query: {ex.Message}";
}
return result;
}
private static async Task GenerateNaturalResponseAsync(IChatCompletionService aiModel, string question, string schema, string sqlQuery, string sqlResult)
{
var prompt = $@"
Based on the table schema below, question, sql query, and sql response, write a natural language response:
{schema}
Question: {question}
SQL Query: {sqlQuery}
SQL Response: {sqlResult}
Response:";
var response = string.Empty;
await foreach (var message in aiModel.GetStreamingChatMessageContentsAsync(prompt))
{
response += message;
}
return response.Trim();
}
}
}
Подробнее здесь: https://stackoverflow.com/questions/787 ... llicunated
