using System;
using System.Data;
using System.Data.SQLite;
using System.Collections.Generic;
using System.Reflection;

public class SqliteHelper
{
    /// <summary>
    /// 數(shù)據(jù)庫(kù)連接字符串
    /// </summary>
    public static string ConnectionString { get; set; } = "Data Source=work.db;Version=3;Pooling=True;Journal Mode=WAL;Cache=Shared;Synchronous=NORMAL;BusyTimeout=5000;";

    /// <summary>
    /// 執(zhí)行 插入、更新、刪除 操作
    /// </summary>
    /// <param name="sql">要執(zhí)行的SQL語(yǔ)句（INSERT/UPDATE/DELETE）</param>
    /// <param name="parameters">SQL參數(shù)數(shù)組，防止SQL注入</param>
    /// <returns>p1.是否成功，p2.錯(cuò)誤信息</returns>
    public static (bool, string) ExecuteSql(string sql, params SQLiteParameter[] parameters)
    {
        if (ConnectionString == null)
            return (false, "請(qǐng)先設(shè)置 ConnectionString");

        try
        {
            int line = 0;
            using (var conn = new SQLiteConnection(ConnectionString))
            {
                conn.Open();
                using (var cmd = new SQLiteCommand(sql, conn))
                {
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);
                    // ExecuteNonQuery 返回受影響的行數(shù)
                    line = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            return (true, null);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            return (false, ex.Message);
        }
    }

    /// <summary>
    /// 執(zhí)行查詢(xún)SQL
    /// </summary>
    /// <param name="sql">SQL語(yǔ)句</param>
    /// <param name="parameters">SQL參數(shù)數(shù)組，防止SQL注入</param>
    /// <returns>p1.是否成功，p2.查詢(xún)的數(shù)據(jù)，p3.錯(cuò)誤信息</returns>
    public static (bool, DataTable, string) Query(string sql, params SQLiteParameter[] parameters)
    {
        if (ConnectionString == null)
            return (false, null, "請(qǐng)先設(shè)置 ConnectionString");

        try
        {
            var dataTable = new DataTable();
            using (var conn = new SQLiteConnection(ConnectionString))
            {
                conn.Open();
                using (var cmd = new SQLiteCommand(sql, conn))
                {
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);
                    //使用數(shù)據(jù)適配器填充 DataTable
                    using (var adapter = new SQLiteDataAdapter(cmd))
                        adapter.Fill(dataTable);
                }
                conn.Close();
            }
            return (true, dataTable, null);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            return (false, null, ex.Message);
        }
    }

    /// <summary>
    /// 執(zhí)行查詢(xún)SQL
    /// </summary>
    /// <typeparam name="T">結(jié)果集列表中對(duì)象的類(lèi)型，需提供無(wú)參構(gòu)造函數(shù)</typeparam>
    /// <param name="sql">SQL語(yǔ)句</param>
    /// <param name="parameters">SQL參數(shù)數(shù)組，防止SQL注入</param>
    /// <returns>p1.是否成功，p2.查詢(xún)的數(shù)據(jù)，p3.錯(cuò)誤信息</returns>
    public static (bool, List<T>, string) Query<T>(string sql, params SQLiteParameter[] parameters) where T : new()
    {
        if (ConnectionString == null)
            return (false, null, "請(qǐng)先設(shè)置 ConnectionString");

        List<T> list = new List<T>();
        try
        {
            using (var conn = new SQLiteConnection(ConnectionString))
            {
                conn.Open();
                using (var cmd = new SQLiteCommand(sql, conn))
                {
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);
                    
                    using (var reader = cmd.ExecuteReader())
                    {
                        // 獲取一次性獲取列的元數(shù)據(jù)，用于反射匹配屬性
                        var schemaTable = reader.GetSchemaTable();
                        // 建立列名到序號(hào)的映射
                        var ordinals = new Dictionary<string, int>();
                        foreach (DataRow col in schemaTable.Rows)
                        {
                            string colName = col["ColumnName"].ToString();
                            ordinals[colName] = (int)col["ColumnOrdinal"];
                        }
                        // 獲取泛型類(lèi)型的所有可寫(xiě)屬性
                        Type objType = typeof(T);
                        PropertyInfo[] properties = objType.GetProperties();
                        while (reader.Read())
                        {
                            T obj = new T();
                            foreach (PropertyInfo prop in properties)
                            {
                                if (!prop.CanWrite) continue;
                                if (ordinals.ContainsKey(prop.Name))
                                {
                                    object value = reader.GetValue(ordinals[prop.Name]);
                                    if (value != DBNull.Value)
                                    {
                                        // 轉(zhuǎn)換為屬性的實(shí)際類(lèi)型后賦值
                                        Type targetType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                                        object safeValue = Convert.ChangeType(value, targetType);
                                        prop.SetValue(obj, safeValue);
                                    }
                                }
                            }
                            list.Add(obj);
                        }
                        reader.Close();
                    }
                }
                conn.Close();
            }
            return (true, list, null);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            return (false, null, ex.Message);
        }
    }
}