c# - 使用 SQLite 处理数十亿条记录

Question

在我的 WPF 应用程序中，我将客户数据插入 SQLite 数据库。我有 2 张 table ( zip 和客户)。数据可以增长到数十亿。我需要从数据库中快速插入和检索。

我正在使用 SQLite，因为该应用程序将仅在单个系统上使用。根据我的研究，SQLite 可以处理 TB 的数据，所以我想知道它是否有问题？

以前我使用 MySQL 和 EF，但插入和检索非常慢。

这是两张表。从 ZipCode 获取客户信息时，将被添加到记录中。

CREATE TABLE ZIPCODE(
                    Zip NUMERIC PRIMARY KEY ,
                    City VARCHAR,
                    County VARCHAR,
                    State VARCHAR
                );
CREATE TABLE CUSTOMER(
                Phone NUMERIC PRIMARY KEY,
                FirstName VARCHAR,
                LastName VARCHAR,
                Address VARCHAR,
                Zip NUMERIC,
                Status CHAR(5),
                Category CHAR(10),
                Options CHAR(5),
                Email VARCHAR,
                Seconds NUMERIC,
                FOREIGN KEY(Zip) REFERENCES ZIPCODE(Zip)
            );

下面是我用来插入数据的 C# 函数。

public int AddCustomersToDB(List<Customer> customers)
        {
            int invalidCount = 0;
            try
            {
                _dbConnection.Open();
                using (var cmd = new SQLiteCommand(_dbConnection))
                {
                    using (var transaction = _dbConnection.BeginTransaction())
                    {
                        foreach (Customer c in customers)
                        {
                            string values = String.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\"", c.Phone, c.FirstName, c.LastName, c.Address, c.Zip, c.Status, c.Category, c.Options, c.Email, c.Seconds);
                            cmd.CommandText = String.Format(@"INSERT INTO CUSTOMER(Phone,FirstName,LastName,Address,Zip,Status,Campaign,Options,Email,Seconds) VALUES ({0});", values);
                            try
                            {
                                cmd.ExecuteNonQuery();
                            }
                            catch (SQLiteException e)
                            {
                                invalidCount++;
                                continue;
                            }
                        }
                        transaction.Commit();
                    }
                }
                return invalidCount;
            }
            catch (SQLiteException e)
            {
                throw new Exception("SQLiteException:" + e.Message);
            }
            finally { _dbConnection.Close(); }
        }

它需要一些关于这种方法的建议，我在 GitHub 上有代码，所以如果有人想查看完整的代码，我也可以分享。

Answer 1

我会改变一些事情。我会使用 sql 命令参数，因为它可以防止 sql 注入(inject)攻击并清理您的代码。 try/catch/finally block 使您的代码更容易出错，因为您必须手动处理连接。将代码包装在 using block 中即使在 using block 中发生异常，也将确保调用 Dispose。

关于您的 SQLite 问题 - maximum database size for a SQLite数据库大约 140 TB。所以它可以处理大量数据，但是你想使用那个大小的 SQLite 数据库 ?

我在几个项目中使用过 SQLite，但从未用于大型数据集。将它用于大型数据集的缺点是它将整个数据库存储在单个磁盘文件中，并且许多文件系统将文件的最大大小限制为小于 SQLite 的限制。

对于一般使用指南，我会查看以下内容:Appropriate Uses For SQLite .

显示参数使用和使用 block 的示例代码:

        var customers = new List<Customer>(); // get your customers

        using (var conn = new SQLiteConnection("your connection string"))
        {
            await conn.OpenAsync();

            using (var cmd = new SQLiteCommand(conn))
            {
                cmd.CommandText = cmd.CommandText = "INSERT INTO CUSTOMER (Phone, FirstName) VALUES(@param1, @param2)";
                cmd.CommandType = CommandType.Text;

                using (var trans = conn.BeginTransaction())
                {
                    foreach (var c in customers)
                    {
                        cmd.Parameters.AddWithValue("@param1", c.Phone);
                        cmd.Parameters.AddWithValue("@param2", c.FirstName);

                        await cmd.ExecuteNonQueryAsync();
                    }
                }
            }
        }