Sqlite数据库文件无法删除

问题描述：

VS2010 Setup工程卸载安装程序时，发生本地sqlite数据库文件无法删除。该卸载程序在完成卸载前读取了本地数据库的信息，代码大致如下：

private DataTable GetDataTableBySql(string databaseFilePath,string sql)
{
            SQLiteConnection conn = new SQLiteConnection("Data Source=" + databaseFilePath+ ";Version=3");
            DataTable datatable = new DataTable();
            try
            {
                conn.Open();
                SQLiteCommand comm = new SQLiteCommand(sql, conn);
                SQLiteDataReader reader = comm.ExecuteReader();
               
                DataColumn column = new DataColumn();
                for (int i = 0; i < columns.Count(); i++)
                {
                    if (columns[i].Length > 0)
                    {
                        datatable.Columns.Add(columns[i].ToString());
                    }
                }

                while (reader.Read())
                {
                    DataRow dr = datatable.NewRow();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        dr[i] = reader[i].ToString();
                    }
                    datatable.Rows.Add(dr);
                    dr = null;
                }
                reader.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                conn.Close();
                return datatable;
            }       
          
            return datatable;
}

数据库信息读取完毕后，也调用close()方法关闭了连接，但是无法完成程序的卸载过程，报“另一个应用程序已经以独占模式访问文件...”。

问题解决：

上述代码中，虽然已经调用close()方法，但是C#的垃圾回收机制并没有马上回收数据库连接对象。有两种办法可以解决上述问题。

其一，调用Dispose方法，让垃圾回收机制立即回收数据库连接对象。

private DataTable GetDataTableBySql(string databaseFilePath,string sql)
{
            SQLiteConnection conn = new SQLiteConnection("Data Source=" + databaseFilePath+ ";Version=3");
            DataTable datatable = new DataTable();
            try
            {
                conn.Open();
                SQLiteCommand comm = new SQLiteCommand(sql, conn);
                SQLiteDataReader reader = comm.ExecuteReader();
               
                DataColumn column = new DataColumn();
                for (int i = 0; i < columns.Count(); i++)
                {
                    if (columns[i].Length > 0)
                    {
                        datatable.Columns.Add(columns[i].ToString());
                    }
                }

                while (reader.Read())
                {
                    DataRow dr = datatable.NewRow();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        dr[i] = reader[i].ToString();
                    }
                    datatable.Rows.Add(dr);
                    dr = null;
                }
                reader.Close();
                
                //立即释放comm
                comm.Dispose();

                conn.Close();

                //立即释放conn
                conn.Dispose();
            }
            catch (Exception ex)
            {
                conn.Close();
                return datatable;
            }       
          
            return datatable;
}

其二，使用using(){}，using语句块中的对象，在语句块结束后会由马上被回收。

private DataTable GetDataTableBySql1(string databaseFilePath,string sql)
        {
            DataTable datatable = new DataTable();
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + databaseFilePath+ ";Version=3"))
                {
                    conn.Open();
                    using (SQLiteCommand comm = new SQLiteCommand(sql, conn))
                    {
                        using (SQLiteDataReader reader = comm.ExecuteReader())
                        {
                            DataColumn column = new DataColumn();
                            for (int i = 0; i < columns.Count(); i++)
                            {
                                if (columns[i].Length > 0)
                                {
                                    datatable.Columns.Add(columns[i].ToString());
                                }
                            }

                            while (reader.Read())
                            {
                                DataRow dr = datatable.NewRow();

                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    dr[i] = reader[i].ToString();
                                }
                                datatable.Rows.Add(dr);
                                dr = null;
                            }
                        }
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                return datatable;
            }

            return datatable;
        }