说不得

最近做的项目由于之前的设计人员懒省事，不按照范式来，将一张表的扩展信息存到了一个“键-值”表中。如下图：

对于主表中的每一条信息，大约有60个“key”，也就是说主表中每插入1条记录，子表中必须要插入60条。

通过预估我们确定主表中最终的数据量大约是20万，也就是说，子表中会有20x60=1200万条记录。同样类型的“主-子”表我们一共有4对，且不说这些表的查询效率，单是每天一次的数据导入对于我们来说就是一项巨大的挑战。

在此我吐槽一下，本来一个十万级的数据库，就是让这种垃圾“设计师”生生给搞成了个千万级的。而且最初他提出的数据插入方案是将每一条数据都生成一条Insert语句，然后逐条调用ExecuteNoQuery执行，后果就是测试用的3000条主表记录，共生成3000x60x4=72万条数据，花费7小时执行完毕，性能30条/秒。后来他辞职了，换了个人，然后第二任也辞职了，第三任就是在下。

项目到我手里之后，在我的坚持下重新进行了软件结构设计，由于数据库系统已经被另一个子系统使用，所以没办法更改了，只好去寻找一种高效的插入方式。

最开始我使用多线程，开10个线程，使性能提升到300条/秒，测试用记录花费大约40分钟插入完毕，对于多60倍的正式数据来说，40小时执行完毕显然不能满足我们每天一次数据导入工作的要求。

通过Google大神，我找到了SqlBulkCopy。

经过测试，性能我很满意，4000条/秒，那就先用它吧，下一阶段的工作重点就是干掉“键-值”表。

在使用中，我也碰到了一些“莫名其妙”的问题，在此记下，以备查询。

1. SqlBulkCopy可以将一个DataTable对象插入到指定名称的数据表中，但是，这个DataTable的架构必须和数据库表一样，尤其要注意一点，DataTable中列的顺序必须和数据库表一样，而且不允许间隔。比如：数据库表中有A、B、C三列，其中B列有默认值，这时用于插入的DataTable不能只有A、C两列，如果只有这两列，就会把DataTable里C列的数据插入数据库表B列里，而把DBNull插入C列里。如果是C列有默认值，DataTable可以只有A、B两列。
2. SqlBulkCopu的默认超时时间是30秒，30x4000=1.2万，无法满足我一次性插入单张表的需要，改成3600秒。

改别人的程序时发现了这个问题。

一个WebApplication程序，在页面的设计模式下，所有服务器控件的位置出现一行红字“Error Creating Control - Object reference not set to an instance of an object”，无法进行控件拖放，只能在手工编辑代码，很是郁闷。

Google了一下，发现了这篇博客，里面描述的信息和我遇到的问题一样，原来是VS2010的Bug，照着修改一下就好了。

为了便于使用SQLite.Net，参照微软企业库中数据组件的写法写了这个类，无非就是为了便于使用。

下面是代码：

代码

using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;

namespace IdentifyCode.Data
{
    public class Database
    {
        /// <summary>
        ///   构造函数
        /// </summary>
        /// <param name="dataSource">数据文件</param>
        public Database(string dataSource)
            : this(dataSource, false, true)
        {
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dataSource">数据文件</param>
        /// <param name="readOnly">是否只读</param>
        /// <param name="pooling">是否使用连接池</param>
        public Database(string dataSource, bool readOnly, bool pooling)
        {
            if (!File.Exists(dataSource))
            {
                SQLiteConnection.CreateFile(dataSource);
            }
            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder
            {
                DataSource = dataSource,
                Version = 3,
                ReadOnly = readOnly,
                Pooling = pooling
            };
            ConnectionString = sb.ToString();
        }

        /// <summary>
        ///   连接字符串
        /// </summary>
        public static string ConnectionString { get; private set; }

        /// <summary>
        ///   获取Sql语句命令
        /// </summary>
        /// <param name = "query"></param>
        /// <returns></returns>
        public DbCommand GetSqlStringCommand(string query)
        {
            if (String.IsNullOrEmpty(query))
            {
                throw new ArgumentException("查询字符串不能为空。");
            }

            DbCommand command = new SQLiteCommand(query) { CommandType = CommandType.Text };
            return command;
        }

        public DbCommand GetSqlStringCommand(string query, DbTransaction transaction)
        {
            DbCommand command = GetSqlStringCommand(query);
            command.Transaction = transaction;
            return command;
        }

        public SQLiteTransaction GetTransaction()
        {
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            return conn.BeginTransaction();
        }

        /// <summary>
        ///   添加参数
        /// </summary>
        /// <param name = "command"></param>
        /// <param name = "dbType"></param>
        public void AddInParameter(DbCommand command, DbType dbType)
        {
            command.Parameters.Add(new SQLiteParameter(dbType));
        }

        /// <summary>
        ///   添加参数
        /// </summary>
        /// <param name = "command"></param>
        /// <param name = "dbType"></param>
        /// <param name = "value"></param>
        public void AddInParameter(DbCommand command, DbType dbType, object value)
        {
            if (value == null)
            {
                value = "null";
            }
            command.Parameters.Add(new SQLiteParameter(dbType) { Value = value });
        }

        /// <summary>
        ///   执行
        /// </summary>
        /// <param name = "command"></param>
        /// <returns></returns>
        public long ExecuteNonQuery(DbCommand command)
        {
            //如果包含事务，就执行返回，不管连接
            if (command.Transaction != null)
            {
                return command.ExecuteNonQuery();
            }

            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                command.Connection = conn;
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///   执行
        /// </summary>
        /// <param name = "command"></param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(DbCommand command)
        {
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                command.Connection = conn;
                return command.ExecuteReader();
            }
            catch
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                throw;
            }
        }

        public long ExecuteScalar(DbCommand command)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                command.Connection = conn;
                object obj = command.ExecuteScalar();
                long l = (long)obj;
                return l;
            }
        }
    }
}

使用方法

1.带事务：

代码

Database db = new Database(dataSource);
using (SQLiteTransaction tran = db.GetTransaction())
{
    using (DbCommand command = db.GetSqlStringCommand(sqlString, tran))
    {
        foreach (Entity entity in list)
        {
            db.AddInParameter(command, DbType.String, ...);
            ...

            db.ExecuteNonQuery(command);
            command.Parameters.Clear();
        }

        tran.Commit();
    }
}

2.不带事务：

代码

Database db = new Database(dataSource);
using (DbCommand command = db.GetSqlStringCommand(sqlString))
{
    foreach (Entity entity in list)
    {
        db.AddInParameter(command, DbType.String, ...);
        ...

        db.ExecuteNonQuery(command);
        command.Parameters.Clear();
    }
}

因为SQLite默认对每个操作打开一个事务，所以如果不声明事务的话，对于任何影响超过1行数据的操作都将会消耗非常长的时间。

万恶的UAC。

UAC造成问题不止这一个了，还有使用Process.GetProcesses()无法获取进程之类……