sqlite 操作和文件占用问题

小知识，大挑战！本文正在参与“程序员必备小知识”创作活动。

• nuget 安装 System.Data.SQLite

• 执行sql ExecuteNonQuery

public static int ExecuteNonQuery(string connectionString, string commandText, params object[] paramList)
{
    //连接
    SQLiteConnection cn = new SQLiteConnection(connectionString);
    //command
    SQLiteCommand cmd = cn.CreateCommand();
    cmd.CommandText = commandText;
    AttachParameters(cmd, commandText, paramList);
    if (cn.State == ConnectionState.Closed)
        cn.Open();
    int result = cmd.ExecuteNonQuery();
    cmd.Dispose();
    cn.Close();

    return result;
}

//参数传入处理函数，按参数顺序

 private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
{
    if (paramList == null || paramList.Length == 0) return null;

    SQLiteParameterCollection coll = cmd.Parameters;
    string parmString = commandText.Substring(commandText.IndexOf("@"));
    // pre-process the string so always at least 1 space after a comma.
    parmString = parmString.Replace(",", " ,");
    // get the named parameters into a match collection
    string pattern = @"(@)\S*(.*?)\b";
    Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
    MatchCollection mc = ex.Matches(parmString);
    string[] paramNames = new string[mc.Count];
    int i = 0;
    foreach (Match m in mc)
    {
        paramNames[i] = m.Value;
        i++;
    }

    // now let's type the parameters
    int j = 0;
    Type t = null;
    foreach (object o in paramList)
    {
        t = o.GetType();

        SQLiteParameter parm = new SQLiteParameter();
        switch (t.ToString())
        {

            case ("DBNull"):
            case ("Char"):
            case ("SByte"):
            case ("UInt16"):
            case ("UInt32"):
            case ("UInt64"):
                throw new SystemException("Invalid data type");


            case ("System.String"):
                parm.DbType = DbType.String;
                parm.ParameterName = paramNames[j];
                parm.Value = (string)paramList[j];
                coll.Add(parm);
                break;

            case ("System.Byte[]"):
                parm.DbType = DbType.Binary;
                parm.ParameterName = paramNames[j];
                parm.Value = (byte[])paramList[j];
                coll.Add(parm);
                break;

            case ("System.Int32"):
                parm.DbType = DbType.Int32;
                parm.ParameterName = paramNames[j];
                parm.Value = (int)paramList[j];
                coll.Add(parm);
                break;

            case ("System.Boolean"):
                parm.DbType = DbType.Boolean;
                parm.ParameterName = paramNames[j];
                parm.Value = (bool)paramList[j];
                coll.Add(parm);
                break;

            case ("System.DateTime"):
                parm.DbType = DbType.DateTime;
                parm.ParameterName = paramNames[j];
                parm.Value = Convert.ToDateTime(paramList[j]);
                coll.Add(parm);
                break;

            case ("System.Double"):
                parm.DbType = DbType.Double;
                parm.ParameterName = paramNames[j];
                parm.Value = Convert.ToDouble(paramList[j]);
                coll.Add(parm);
                break;

            case ("System.Decimal"):
                parm.DbType = DbType.Decimal;
                parm.ParameterName = paramNames[j];
                parm.Value = Convert.ToDecimal(paramList[j]);
                break;

            case ("System.Guid"):
                parm.DbType = DbType.Guid;
                parm.ParameterName = paramNames[j];
                parm.Value = (System.Guid)(paramList[j]);
                break;

            case ("System.Object"):

                parm.DbType = DbType.Object;
                parm.ParameterName = paramNames[j];
                parm.Value = paramList[j];
                coll.Add(parm);
                break;

            default:
                throw new SystemException("Value is of unknown data type");

        } // end switch

        j++;
    }
    return coll;
}

• 事务使用， ExecuteNonQuery

public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params object[] paramList)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                        please provide an open transaction.", "transaction");
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters((SQLiteCommand)cmd, cmd.CommandText, paramList);
            if (transaction.Connection.State == ConnectionState.Closed)
                transaction.Connection.Open();
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return result;
        }

• 查询 DataSet

 public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
{

    SQLiteCommand cmd = cn.CreateCommand();
    cmd.CommandText = commandText;
    if (paramList != null)
    {
        AttachParameters(cmd, commandText, paramList);
    }
    DataSet ds = new DataSet();
    if (cn.State == ConnectionState.Closed)
        cn.Open();
    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
    da.Fill(ds);
    da.Dispose();
    cmd.Dispose();
    cn.Close();
    return ds;
}

使用sqlite文件后，即使SQLiteConnection，SQLiteCommand 关闭释放后依然不能移动或拷贝文件，提示文件被另外一个进程占用，这时候我们要使用ClearAllPools清楚连接池后就能使用了。

System.Data.SQLite.SQLiteConnection.ClearAllPools();