使用 C# 语言实现 SQLite 数据库表列检测功能，当目标表中未包含指定列时，执行列新增操作。

核心代码

using System;
using System.Data.SQLite;

namespace KHQP.Utility
{
    /// <summary>
    /// SQLite表字段扩展工具类
    /// </summary>
    public class SQLiteTableColumnManager
    {
        // 数据库连接字符串（请替换为你的实际连接字符串）
        private readonly string _connectionString = "Data Source= data.db;Pooling=true;FailIfMissing=false";


        /// <summary>
        ///使用demo
        /// 检测并新增SignalChannelTestHandler表的TestId2和TestType列
        /// </summary>
        /// <returns>是否成功执行（无异常即返回true）</returns>
        public bool AddMissingColumnsToSignalChannelTestHandler()
        {
            // 定义需要检测/新增的列信息
            var columnsToCheck = new[]
            {
            new { ColumnName = "TestId2", SqlType = "VARCHAR(100)", DefaultValue = "''" },
            new { ColumnName = "TestType", SqlType = "INTEGER", DefaultValue = "0" }
            };

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

                    foreach (var column in columnsToCheck)
                    {
                        // 1. 检测列是否存在
                        bool columnExists = CheckColumnExists(connection, "SignalChannelTestHandler", column.ColumnName);

                        if (!columnExists)
                        {
                            // 2. 列不存在则新增
                            AddColumnToTable(connection, "SignalChannelTestHandler",
                                column.ColumnName, column.SqlType, column.DefaultValue);
                            Console.WriteLine($"成功为表SignalChannelTestHandler新增列：{column.ColumnName}");
                        }
                        else
                        {
                            Console.WriteLine($"表SignalChannelTestHandler已存在列：{column.ColumnName}，无需新增");
                        }
                    }

                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"新增列失败：{ex.Message}");
                // 建议替换为项目实际的日志组件（如log4net/NLog）
                // LogHelper.Error("新增SignalChannelTestHandler表列失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 检测SQLite表中指定列是否存在
        /// </summary>
        /// <param name="connection">已打开的数据库连接</param>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns>列是否存在</returns>
        public bool CheckColumnExists(SQLiteConnection connection, string tableName, string columnName)
        {
            // SQLite通过sqlite_master和PRAGMA table_info查询表结构
            string checkSql = @"
            SELECT COUNT(*) 
            FROM pragma_table_info(@TableName) 
            WHERE name = @ColumnName";

            using (var command = new SQLiteCommand(checkSql, connection))
            {
                command.Parameters.AddWithValue("@TableName", tableName);
                command.Parameters.AddWithValue("@ColumnName", columnName);

                int count = Convert.ToInt32(command.ExecuteScalar());
                return count > 0;
            }
        }

        /// <summary>
        /// 为SQLite表新增列
        /// </summary>
        /// <param name="connection">已打开的数据库连接</param>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="sqlType">列数据类型（如VARCHAR(100)、INTEGER）</param>
        /// <param name="defaultValue">默认值（可选）</param>
        public void AddColumnToTable(SQLiteConnection connection, string tableName,
            string columnName, string sqlType, string defaultValue = null)
        {
            // 拼接ALTER TABLE语句（SQLite仅支持ADD COLUMN，且有语法限制）
            string alterSql = $"ALTER TABLE {tableName} ADD COLUMN {columnName} {sqlType}";
            if (!string.IsNullOrEmpty(defaultValue))
            {
                alterSql += $" DEFAULT {defaultValue}";
            }

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

本文来自www.luofenming.com