SQLite 检查表、字段是否存在，不存在则新增

有些时候我们可能需要在代码中检测某个表或某个表中某个字段是否存在，不存在的情况下通过代码去添加它。

判断表是否存在：

Sqlite会自动维护一个系统表sqlite_master，该表存储了我们所创建的各个table, view, trigger等信息。

使用在线操作数据库工具https://sqliteonline.com/

select * from sqlite_master where type='table' order by name;
--select count(*) from sqlite_master where type='table' and name='表名'

判断字段是否存在：

这里有两种方式

第一种：可以清晰的查出表中的字段，在代码中用list接收然后判断list中有没有某个字段

PRAGMA  table_info(表名)

第二种：可以通过查询表时的sql语句中是否包含某个字段

select * from sqlite_master where type = 'table' and name = '表名' and sql like '%字段名%'

新增字段

其中的 column 可以省略，同时也可以不给 default 默认值。

alter table 表名 add column '字段名' INTEGER default 1
--等同于alter table 表名 add '字段名' INTEGER

c# 代码操作示例

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Data.Sqlite;
using DairyStar.CalfFeeding.Core.Util;
using System.Text.Json;
using Dapper.Contrib.Extensions;
using System.Reflection; 

  //封装的Dapper公共类
  public class DbService : IDbService
  {
    public DbService()
    {
    }   
    /// <summary>
    /// 获取连接对象
    /// </summary>
    /// <returns></returns>
    private IDbConnection GetDbConnection()
    {
      //方式一
      //AppSettingOptions AppSettingOptions = ConfigManager.Get<AppSettingOptions>();
      //string connStr = AppSettingOptions.ConnectionStrings.DBConnection;
      //方式二
      //ConnectionStrings ConnectionStrings = ConfigManager.GetSection<ConnectionStrings>("ConnectionStrings");
      //string connStr = ConnectionStrings.DBConnection;
      //方式三
      var connStr = $"Data Source={Path.GetDirectoryName(Assembly.GetEntryAssembly()!.Location)}\\Iot_CalfFeed.db;";
      IDbConnection connection = new SqliteConnection(connStr);
      return connection;
    }

    /// <summary>
    /// Sqlite检查表、字段是否存在，不存在则新增
    /// </summary>
    /// <param name="TableName"></param>
    /// <param name="Field"></param>
    /// <param name="FieldType"></param>
    /// <param name="FieldDefault"></param>
    /// <returns></returns>
    public (bool, string) CheckFieldOrNot(string TableName, string[] Field, string[] FieldType, string[] FieldDefault)
    {
      using (IDbConnection conn = GetDbConnection())
      {
        try
        {
          string TableSql = $"select count(*) from sqlite_master where type='table' and name='{TableName}'";
          int ExistTable = conn.ExecuteScalar<int>(TableSql);
          if (ExistTable < 1)
          {
            //表不存在则新增表
            List<string> NeedAddField = new List<string>();
            for (int i = 0; i < Field.Length; i++)
            {
              string AddField = $"{Field[i]} {FieldType[i]}";
              if (FieldDefault[i] != "")
              {
                AddField += $" DEFAULT {FieldDefault[i]}";
              }
              NeedAddField.Add(AddField);
            }
            string FieldStr = string.Join(",", NeedAddField);
            string AddTableSql = $"create table {TableName}({FieldStr})";
            conn.Execute(AddTableSql);
            bool TableAddResult = conn.ExecuteScalar<int>(TableSql) > 0;
            if (!TableAddResult) 
              return (false, $"添加本地库{TableName}表时失败");
            else
              return (true, "");
          }

          string Sql = $"PRAGMA  table_info({TableName})";
          List<dynamic> TableStructure = conn.Query<dynamic>(Sql).ToList();
          for (int i = 0; i < Field.Length; i++)
          {
            bool Exist = TableStructure.Where(a => a.name == Field[i]).ToList().Count > 0;
            if (!Exist)
            {
              //如果不存在则新增字段
              string AddField = $"alter table {TableName} add column {Field[i]} {FieldType[i]}";
              if (FieldDefault[i] != "")
              {
                AddField += $" DEFAULT {FieldDefault[i]}";
              }
              bool FieldAddResult = conn.Execute(AddField) > 0;
              if (!FieldAddResult)
                return (false, $"添加本地库{TableName}表字段{Field[i]}类型{FieldType[i]}时失败");
              else
                return (true, "");
            }
          }
          return (true, "");
        }
        catch (Exception ex)
        {
          string Message = $"检查新增本地数据表或库字段时失败:{ex.Message + ex.StackTrace + ex.InnerException}";
          return (false, Message);
        }
      }
    }
}

//业务层调用方法
public class FeedingService : IFeedingService
{
    IDbService _dbService;  

    public FeedingService(IDbService dbService)
    {
      _dbService = dbService;    
    }

    /// <summary>
    /// 检查表、字段是否存在，不存在则新增
    /// </summary>
    /// <returns></returns>
    public (bool, string) CheckFieldOrNot()
    {
      string TableName = "FeedCowInfo";
      string[] Field = { "IsAbnormal" };
      string[] FieldType = { "INTEGER" }; 
      string[] FieldDefault = { "1" };
      return _dbService.CheckFieldOrNot(TableName, Field, FieldType, FieldDefault);
    }
}