学习sqlite，尝试写orm

学习sqlite，尝试写orm

我本身是一个Java程序员，因出差学了小半年的C#，第一次写一些稍微通用一点的方法，帮助我理解编程，这个代码是为了我的傅里叶轨迹而写的，后面看到有现成的库就直接弃用了。但是还是记录一下。

using MathNet.Numerics.Distributions;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using Robot.Enetity;

namespace Robot.utils
{
    [Obsolete("这个类是个测试类已过期，尽量不要使用，请使用sqlite-net-pcl",true)]
    public class SqliteUtils
    {
        public SqliteUtils() { }

        /// <summary>
        /// 连接sqlite数据库
        /// </summary>
        public static SQLiteConnection connect(string databasePath,string baseName)
        {
            // SQLite 连接字符串，指定数据库文件的路径
            string connectionString = "Data Source="+ databasePath+@"\" + baseName + ".db;Version=3;";
            //string databasePath = @"E:\your_path\your_database.db";
            // 使用 using 语句确保在使用完数据库连接后正确关闭连接
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            try
            {
                    // 打开数据库连接
                    connection.Open();
                connection.Close();


                    // 创建一个表（如果不存在的话）
                    //CreateSampleTable(connection);

                // 插入一些数据
                //InsertSampleData(connection);

                // 查询并显示数据
                //DisplayData(connection);
                  return connection;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
                return null;
            }
            
            
        }

        // 创建一个名为 "SampleTable" 的表
        static void CreateSampleTable(SQLiteConnection connection)
        {
            string createTableQuery = "CREATE TABLE IF NOT EXISTS SampleTable (Id INTEGER PRIMARY KEY, Name TEXT);";

            using (SQLiteCommand command = new SQLiteCommand(createTableQuery, connection))
            {
                command.ExecuteNonQuery();
            }
        }

        // 向表中插入一些样本数据
        static void InsertSampleData(SQLiteConnection connection)
        {
            string insertDataQuery = "INSERT INTO SampleTable (Name) VALUES ('John'), ('Alice'), ('Bob');";

            using (SQLiteCommand command = new SQLiteCommand(insertDataQuery, connection))
            {
                command.ExecuteNonQuery();
            }
        }

        // 查询并显示数据
        public static List<TrackPoint> selectData(SQLiteConnection connection, string tableName, params string[] columns)
        {

           // FieldInfo[] fields = type.GetFields(BindingFlags.NonPublic);
            string column = "*";
            if (columns is not null && columns.Length > 0)
            {
                column = string.Join(",", columns);
            }

            string selectDataQuery = $"SELECT {column} FROM {tableName};";
            List<TrackPoint> rows = new List<TrackPoint>();
            using (SQLiteCommand command = new SQLiteCommand(selectDataQuery, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    Console.WriteLine($"{tableName} Data:");
                    
                    while (reader.Read())
                    {
                        //for (int i = 0; i < fields.Length; i++)
                        //{
                        //    for (int j = 0; j < reader.FieldCount;j++)
                        //    {
                        //        if (fields[i].Name.Equals(reader)) { 
                        //        }
                        //    }
                        //}
                        rows.Add(new TrackPoint(reader.GetInt32(reader.GetOrdinal("id")),
                            reader.GetFloat(reader.GetOrdinal("x")),
                            reader.GetFloat(reader.GetOrdinal("y")),
                            reader.GetFloat(reader.GetOrdinal("z")),
                            reader.GetFloat(reader.GetOrdinal("acc_time"))));
                    }
                }
            }
            return rows;
        }

        /// <summary>
        /// 单条插入
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="trackPoint"></param>
        static void InsertTrackPoint(SQLiteConnection connection, TrackPoint trackPoint)
        {
            string insertQuery = "INSERT INTO TrackPoints (id, x, y, z, acc_time) VALUES (@Id, @X, @Y, @Z, @AccTime)";

            using (var command = new SQLiteCommand(insertQuery, connection))
            {
                command.Parameters.AddWithValue("@Id", trackPoint.Id);
                command.Parameters.AddWithValue("@X", trackPoint.X);
                command.Parameters.AddWithValue("@Y", trackPoint.Y);
                command.Parameters.AddWithValue("@Z", trackPoint.Z);
                command.Parameters.AddWithValue("@AccTime", trackPoint.AccTime);

                command.ExecuteNonQuery();
            }
        }


        /// <summary>
        /// 单条插入
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="trackPoint"></param>
        public static void Insert(SQLiteConnection connection, Type clazz,object obj)
        {
            PropertyInfo[] properties = clazz.GetProperties();
            string propertyNames = string.Join(",", properties.Select(property => property.Name));
            string propertyNamesAt = string.Join(",", properties.Select(property => "@" + property.Name));
            string insertQuery = "INSERT INTO " + clazz.Name + " (" +  propertyNames +  ") VALUES ("+ propertyNamesAt  + ")";

            using (var command = new SQLiteCommand(insertQuery, connection))
            {
                foreach (var property in properties)
                {
                    command.Parameters.AddWithValue("@"+property.Name, property.GetValue(obj));
                }
                command.ExecuteNonQuery();
            }
        }



        public static List<object> select(SQLiteConnection connection, Type clazz, params string[] columns)
        {
            
            // FieldInfo[] fields = type.GetFields(BindingFlags.NonPublic);
            //Type clazz = t.GetType();
            string column = "*";
            if (columns is not null && columns.Length > 0)
            {
                column = string.Join(",", columns);
            }

            string selectDataQuery = $"SELECT {column} FROM {clazz.Name};";
            List<object> rows = new List<object>();
            using (SQLiteCommand command = new SQLiteCommand(selectDataQuery, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        object obj = Activator.CreateInstance(clazz);
                        //obj obj = new T();
                        PropertyInfo[] properties = clazz.GetProperties();
                        foreach (PropertyInfo property in properties)
                        {
                            property.SetValue(obj, reader.GetOrdinal(property.Name));
                        }

                        rows.Add(obj);
                    }
                }
            }
            return rows;
        }
        /// <summary>
        /// 单条批量插入，带事务
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="trackPoints"></param>
        static void InsertTrackPoints(SQLiteConnection connection, List<TrackPoint> trackPoints) {
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        foreach (var trackPoint in trackPoints)
                        {
                            InsertTrackPoint(connection, trackPoint);
                        }

                        transaction.Commit();
                        Console.WriteLine("批量插入数据成功！");
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine($"插入数据时发生错误: {ex.Message}");
                    }
                }
            }

        // 一次性插入多条 TrackPoint 数据的方法
        public static int InsertTrackPointsBatch(SQLiteConnection connection, List<TrackPoint> trackPoints)
        {
            string insertQuery = "INSERT INTO track_point ( x, y, z, acc_time) VALUES ";

            using (var command = new SQLiteCommand(connection))
            {
                List<string> valueStrings = new List<string>();

                for (int i = 0; i < trackPoints.Count; i++)
                {
                    string values = $"(@x{i}, @y{i}, @z{i}, @acc_time{i})";
                    valueStrings.Add(values);
                    /**
                     这行代码的作用是为 SQL 命令添加一个参数，参数的名称是 @Id{i}，
                    对应的数值是 trackPoints[i].Id。这样，在执行 SQL 命令时，数据库引擎会将这个参数替换到 SQL 语句中的相应位置，
                    从而完成一次数据插入。
                     */
                    //command.Parameters.AddWithValue($"@Id{i}", trackPoints[i].Id);
                    command.Parameters.AddWithValue($"@x{i}", trackPoints[i].X);
                    command.Parameters.AddWithValue($"@y{i}", trackPoints[i].Y);
                    command.Parameters.AddWithValue($"@z{i}", trackPoints[i].Z);
                    command.Parameters.AddWithValue($"@acc_time{i}", trackPoints[i].AccTime);
                }

                insertQuery += string.Join(", ", valueStrings);
                command.CommandText = insertQuery;
                return command.ExecuteNonQuery();
            }
            
        }


        // 获取类属性到数据库字段的映射关系,暂时不使用，以后若需要进行封装再次使用
        static Dictionary<string, string> GetFieldMappings<T>()
        {
            Dictionary<string, string> fieldMappings = new Dictionary<string, string>();

            PropertyInfo[] properties = typeof(T).GetProperties();
            foreach (PropertyInfo property in properties)
            {
                // 这里假设数据库字段与类属性名称一致，你可以根据实际情况修改映射逻辑
                fieldMappings[property.Name] = property.Name;
            }

            return fieldMappings;
        }

        // 插入数据到 SQLite 数据库  暂时不使用，以后若需要进行封装再次使用
        static void InsertData<T>(T data, Dictionary<string, string> fieldMappings)
        {
            string connectionString = "Data Source=your_database.db;Version=3;"; // 替换为你的数据库路径

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string tableName = typeof(T).Name; // 假设表名与类名一致，你可以根据实际情况修改
                string insertQuery = BuildInsertQuery(tableName, fieldMappings);

                using (var command = new SQLiteCommand(insertQuery, connection))
                {
                    foreach (var mapping in fieldMappings)
                    {
                        // 设置参数值，通过反射获取属性值
                        PropertyInfo property = typeof(T).GetProperty(mapping.Key);
                        command.Parameters.AddWithValue($"@{mapping.Value}", property.GetValue(data));
                    }

                    command.ExecuteNonQuery();
                }
            }
        }

        // 构建插入数据的 SQL 查询   暂时不使用，以后若需要进行封装再次使用
        static string BuildInsertQuery(string tableName, Dictionary<string, string> fieldMappings)
        {
            string columns = string.Join(", ", fieldMappings.Values);
            string values = string.Join(", ", fieldMappings.Values.Select(f => $"@{f}"));
            return $"INSERT INTO {tableName} ({columns}) VALUES ({values})";
        }




    }
}