C# SDK对接向量数据库

本文介绍如何使用C#语言调用openGauss向量数据库。

环境要求

• 使用dotnet --version查看是否安装.NET开发工具，若无，则需安装dotnet-sdk
• 安装相关库

1. dotnet add package Pgvector 2. dotnet add package Npgsql

基本操作

1.连接数据库

1. public async Task<NpgsqlConnection> Connect(string connStr)
2. {
3. var dataSourceBuilder = new NpgsqlDataSourceBuilder(connStr);
4. dataSourceBuilder.UseVector();
5. await using var dataSource = dataSourceBuilder.Build();
6. var conn = dataSource.OpenConnection();
7. conn.ReloadTypes();
8. return conn;
9. }

2.创建表

1. public async Task CreateTableAsync(NpgsqlConnection conn)
2. {
3. const string create = "CREATE TABLE items (id serial PRIMARY KEY, embedding vector(3))";
4. await using var cmd = new NpgsqlCommand(create, conn);
5. await cmd.ExecuteNonQueryAsync();
6. }

3.创建索引

1. public async Task CreateIndexAsync(NpgsqlConnection conn)
2. {
3. const string createIndex = "CREATE INDEX ON items USING hnsw (embedding vector_l2_ops)";
4. await using var cmd = new NpgsqlCommand(createIndex, conn);
5. await cmd.ExecuteNonQueryAsync();
6. }

4.插入/删除/更新数据

• 批量插入

1. public async Task InsertDataAsync(NpgsqlConnection conn, Vector vector)
2. {
3. const string insert = "INSERT INTO items (embedding) VALUES ($1)";
4. await using var cmd = new NpgsqlCommand(insert, conn);
5. cmd.Parameters.AddWithValue(vector);
6. await cmd.ExecuteNonQueryAsync();
7. }

• 删除

1. public async Task DeleteDataAsync(NpgsqlConnection conn, int id)
2. {
3. const string delete = "DELETE FROM items WHERE id = $1";
4. await using var cmd = new NpgsqlCommand(delete, conn);
5. cmd.Parameters.AddWithValue(id);
6. await cmd.ExecuteNonQueryAsync();
7. }

• 更新

1. public async Task UpdateDataAsync(NpgsqlConnection conn, Vector vector, int id)
2. {
3. const string update = "UPDATE items SET embedding = $1 WHERE id = $2";
4. await using var cmd = new NpgsqlCommand(update, conn);
5. cmd.Parameters.AddWithValue(vector).DataTypeName = "vector";
6. cmd.Parameters.AddWithValue(id);
7. await cmd.ExecuteNonQueryAsync();
8. }

5.查询

1. public async Task<System.Collections.Generic.List<(int, Vector)>> QueryAsync(NpgsqlConnection conn, Vector vector, int limit)
2. {
3. const string query = "SELECT * FROM items ORDER BY embedding <-> $1 LIMIT $2"
4. await using var cmd = new NpgsqlCommand(query, conn);
5. cmd.Parameters.AddWithValue(vector).DataTypeName = "vector";
6. cmd.Parameters.AddWithValue(limit);

8. var results = new System.Collections.Generic.List<(int, Vector)>();
9. await using var reader = await cmd.ExecuteReaderAsync();
10. while (await reader.ReadAsync())
11. {
12. var id = reader.GetInt32(0);
13. var embedding = (Vector)reader.GetValue(1);
14. results.Add((id, embedding));
15. }
16. return results;
17. }

6.删除表

1. public async Task DropTableAsync(NpgsqlConnection conn)
2. {
3. const string drop = "DROP TABLE IF EXISTS items";
4. await using var cmd = new NpgsqlCommand(drop, conn);
5. await cmd.ExecuteNonQueryAsync();
6. }

7.关闭连接

1. public async Task CloseConnectionAsync(NpgsqlConnection conn)
2. {
3. if(conn != null)
4. {
5. await conn.CloseAsync();
6. await conn.DisposeAsync();
7. }
8. }

用例

1. using System;
2. using Pgvector;
3. using Npgsql;
4. namespace Demo
5. {
6. public class Program
7. {
8. public static async Task Main(string[] args)
9. {
10. var connstr = "Host=localhost;Database=Yourdb;Port=Yourport;Username=Yourname;Password=YourPassword";
11. var dbHandler = new Program();
12. var conn = await dbHandler.Connect(connstr);

14. await dbHandler.CreateTableAsync(conn);

16. await dbHandler.CreateIndexAsync(conn);

18. var vectorq = new Vector(new float[] {1, 1, 1});
19. await dbHandler.InsertDataAsync(conn, vectorq);

21. var results = await dbHandler.QueryAsync(conn, vectorq, 10);

23. await dbHandler.CloseConnectionAsync(conn);
24. }
25. }
26. }