sqlite导入导出的C#程序代码

//先创建文件路径
string dtexport = AppDomain.CurrentDomain.BaseDirectory + "log\\export.sql";
RecordLog(dtexport);
if (!File.Exists(dtexport)) File.Create(dtexport);

//导出数据库表.sql
private void export() {

    string str = AppDomain.CurrentDomain.BaseDirectory;

    // 创建一个 SQLiteConnection 对象
    using (var connection = new SQLiteConnection("Data Source=" + str + "DataBase\\Data.db;"))
    {
        // 打开数据库连接
        connection.Open();

        // 查询所有表名
        var tableNames = new List<string>();
        var tablesCommand = new SQLiteCommand("SELECT name FROM sqlite_master WHERE type='table'", connection);
        using (var tablesReader = tablesCommand.ExecuteReader())
        {
            while (tablesReader.Read())
            {
                tableNames.Add(tablesReader.GetString(0));
            }
        }

        // 遍历所有表，生成导出 SQL
        var exportSql = new StringWriter();
        foreach (var tableName in tableNames)
        {
            // 查询表结构
            var schemaCommand = new SQLiteCommand($"PRAGMA table_info({tableName})", connection);
            var schemaReader = schemaCommand.ExecuteReader();

            // 生成 CREATE TABLE 语句
            exportSql.WriteLine($"-- Table: {tableName}");
            exportSql.Write("CREATE TABLE ");
            exportSql.Write(tableName);
            exportSql.Write(" (");
            bool firstColumn = true;
            while (schemaReader.Read())
            {
                string columnName = schemaReader.GetString(1);
                string columnType = schemaReader.GetString(2);
                bool notNull = !schemaReader.IsDBNull(3) && schemaReader.GetInt32(3) != 0;
                bool primaryKey = !schemaReader.IsDBNull(5) && schemaReader.GetInt32(5) != 0;

                if (!firstColumn)
                {
                    exportSql.Write(",");
                }
                exportSql.Write(columnName);
                exportSql.Write(" ");
                exportSql.Write(columnType);
                if (notNull)
                {
                    exportSql.Write(" NOT NULL");
                }
                if (primaryKey)
                {
                    exportSql.Write(" PRIMARY KEY");
                }
                firstColumn = false;
            }
            exportSql.Write(");");
            exportSql.WriteLine();

            // 查询表数据
            var dataCommand = new SQLiteCommand($"SELECT * FROM {tableName}", connection);
            using (var dataReader = dataCommand.ExecuteReader())
            {
                // 生成 INSERT 语句
                while (dataReader.Read())
                {
                    exportSql.Write($"INSERT INTO {tableName} VALUES (");
                    bool firstValue = true;
                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        if (!firstValue)
                        {
                            exportSql.Write(",");
                        }
                        object value = dataReader.GetValue(i);
                        if (value == null)
                        {
                            exportSql.Write("NULL");
                        }
                        else if (value is string)
                        {
                            exportSql.Write($"'{value.ToString().Replace("'", "''")}'");
                        }
                        else if (value is DateTime)
                        {
                            exportSql.Write($"'{((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss.fff")}'");
                        }
                        else
                        {
                            exportSql.Write(value.ToString());
                        }
                        firstValue = false;
                    }
                    exportSql.Write(");");
                    exportSql.WriteLine();
                }
            }
            exportSql.WriteLine();
        }
        string dtexport = AppDomain.CurrentDomain.BaseDirectory + "log\\export.sql";
        // 将导出 SQL 写入到文件
        File.WriteAllText(dtexport, exportSql.ToString());
    }

}

//将.sql导入新建数据库
//private void import() {
    
//    // 读取 SQL 文件内容
//    string sql = File.ReadAllText(sqlFilePath);

//    // 创建一个新的 SQLiteConnection 对象
//    using (var connection = new SQLiteConnection($"Data Source={databaseFilePath}"))
//    {
//        // 打开数据库连接
//        connection.Open();

//        // 创建一个 SQLiteCommand 对象，并执行 SQL 文件中的 SQL 语句
//        using (var command = new SQLiteCommand(sql, connection))
//        {
//            command.ExecuteNonQuery();
//        }
//    }

//    Console.WriteLine("SQL 文件导入成功！");
    

//}