C#でSQLiteの自動採番を使い特定フォルダ内のファイルを番号管理するテーブルを作る。

namespace FilesDB01;
class Program
{
    static void Main()
    {
        using var obj = new FilesDB();

        Console.WriteLine("a");

        DirectoryInfo dirInfo = new DirectoryInfo(@".\");

        foreach(FileInfo fi in dirInfo.EnumerateFiles())
        {
            string lastUpdate = fi.LastWriteTime.ToString("yyyy/MM/dd HH:mm:ss");

            long id = obj.GetID(fi.FullName, lastUpdate, fi.Length);

            if (id > -1)
            {
                Console.WriteLine("取得:{0} {1} {2} {3}",
                    id,
                    fi.FullName,
                    lastUpdate,
                    fi.Length);
            }
            else
            {
                id = obj.AddID(fi.FullName, lastUpdate, fi.Length);
                Console.WriteLine("追加:{0} {1} {2} {3}",
                    id,
                    fi.FullName,
                    lastUpdate,
                    fi.Length);
            }
            // id:1を削除
            //obj.DeleteID(1);

        }
    }
}

using System.Data.SQLite;
using System.Diagnostics;
namespace FilesDB01;

class FilesDB : IDisposable
{
    readonly static string _dbFileName = @"./FilesDB.db";

    SQLiteConnection _conn;
    SQLiteCommand _cmd;
    /// <summary>
    /// コンストラクタ
    /// </summary>
    public FilesDB()
    {
        Debug.Print("FilesDB.FilesDB()");
        // コネクション
        _conn = new SQLiteConnection();
        _conn.ConnectionString = string.Format("Data Source = {0}", _dbFileName);

        // データベースを開く
        _conn.Open();
        // コマンド
        _cmd = new SQLiteCommand(_conn);

        // テーブルを作成
        const string sql = "CREATE TABLE IF NOT EXISTS files (id integer primary key autoincrement, path text, last_update text, len integer)";
        _cmd.CommandText = sql;
        var _ = _cmd.ExecuteNonQuery(); // 戻り値どうしよう

        // インデックスを作成
        const string sql2 = "CREATE INDEX IF NOT EXISTS files_index ON files (path, last_update, len)";
        _cmd.CommandText = sql2;
        _ = _cmd.ExecuteNonQuery(); // 戻り値どうしよう
    }
    public long GetID(string path, string lastUpdate, long length)
    {
        int result = -1;

        _cmd.CommandText = string.Format(
            "SELECT id FROM files WHERE path='{0}' and last_update='{1}' and len={2} ",
            path, lastUpdate, length);
        
        using (var rec = _cmd.ExecuteReader())
        {
            if(rec.Read())
            {
                return (long)rec["id"];
            }
        }

        return result;
    }
    public long AddID(string path, string lastUpdate, long length)
    {
        // レコードの追加
        _cmd.CommandText = string.Format(
            "INSERT INTO files (path, last_update, len) values ('{0}','{1}', {2})",
            path,
            lastUpdate,
            length
        );
        var _ = _cmd.ExecuteNonQuery();

        return GetID(path, lastUpdate, length);
    }
    public void DeleteID(int id)
    {
        // レコードの削除
        _cmd.CommandText = string.Format(
            "DELETE FROM files WHERE id = {0}",
            id);
        var _ = _cmd.ExecuteNonQuery();
    }
    /// <summary>
    /// Dispose
    /// </summary>
    public void Dispose()
    {
        Debug.Print("FilesDB.Dispose()");
        _cmd?.Dispose();
        _conn?.Dispose();
    }
}

a
取得:9 H:\csharp\dotnet8\console\FilesDB01\FilesDB.cs 2024/09/28 15:58:47 2516
追加:12 H:\csharp\dotnet8\console\FilesDB01\FilesDB.db 2024/09/28 15:59:44 16384
取得:3 H:\csharp\dotnet8\console\FilesDB01\FilesDB01.csproj 2024/09/28 14:44:59 358        
取得:4 H:\csharp\dotnet8\console\FilesDB01\FilesDB01.sln 2024/09/28 14:45:18 1119
取得:11 H:\csharp\dotnet8\console\FilesDB01\Program.cs 2024/09/28 15:59:38 1066