c# - 无法关闭/处置 SQLite 数据库 C# 上的文件锁

Question

您好，我目前正在开发一个生成模板 SQLite 数据库的应用程序，我遇到了一个问题，即在特定步骤后没有释放文件的过程。堆栈跟踪如下。

    {
  "ClassName": "System.AggregateException",
  "Message": "One or more errors occurred.",
  "Data": null,
  "InnerException": {
    "ClassName": "System.IO.IOException",
    "Message": "The process cannot access the file 'C:\Users\James\AppData\Local\Temp\AutoGeneratedDBTemplate-16-11-15-7f3bbbde-6513-4860-bf43-6847a61bb25c.db' because it is being used by another process.",
    "Data": null,
    "InnerException": null,
    "HelpURL": null,
    "StackTraceString": "   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)\r\n   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)\r\n   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.BeginUploadFromFile(String path, FileMode mode, AccessCondition accessCondition, BlobRequestOptions options, OperationContext operationContext, AsyncCallback callback, Object state) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 903\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.BeginUploadFromFile(String path, FileMode mode, AsyncCallback callback, Object state) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 884\r\n   at Microsoft.WindowsAzure.Storage.Core.Util.AsyncExtensions.TaskFromVoidApm[T1,T2](Func`5 beginMethod, Action`1 endMethod, T1 arg1, T2 arg2, CancellationToken cancellationToken) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Core\Util\AsyncExtensions.cs:line 192\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.UploadFromFileAsync(String path, FileMode mode, CancellationToken cancellationToken) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 988\r\n   at VMApi.Controllers.SqliteController.<UploadBlobedTemplate>d__4.MoveNext() in C:\path\to\project\VMApi\VMApi\Controllers\SqliteController.cs:line 87",
    "RemoteStackTraceString": null,
    "RemoteStackIndex": 0,
    "ExceptionMethod": "8\nWinIOError\nmscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.IO.__Error\nVoid WinIOError(Int32, System.String)",
    "HResult": -2147024864,
    "Source": "mscorlib",
    "WatsonBuckets": null
  },
  "HelpURL": null,
  "StackTraceString": "   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)\r\n   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)\r\n   at System.Threading.Tasks.Task`1.get_Result()\r\n   at VMApi.Controllers.ExportDBController.<>c__DisplayClass0_0.<Get>b__0(IAsyncResult r) in C:\path\to\project\VMApi\VMApi\Controllers\ExportDBController.cs:line 49",
  "RemoteStackTraceString": null,
  "RemoteStackIndex": 0,
  "ExceptionMethod": "8\nThrowIfExceptional\nmscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.Threading.Tasks.Task\nVoid ThrowIfExceptional(Boolean)",
  "HResult": -2146233088,
  "Source": "mscorlib",
  "WatsonBuckets": null,
  "InnerExceptions": [
    {
      "ClassName": "System.IO.IOException",
      "Message": "The process cannot access the file 'C:\Users\James\AppData\Local\Temp\AutoGeneratedDBTemplate-16-11-15-7f3bbbde-6513-4860-bf43-6847a61bb25c.db' because it is being used by another process.",
      "Data": null,
      "InnerException": null,
      "HelpURL": null,
      "StackTraceString": "   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)\r\n   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)\r\n   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.BeginUploadFromFile(String path, FileMode mode, AccessCondition accessCondition, BlobRequestOptions options, OperationContext operationContext, AsyncCallback callback, Object state) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 903\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.BeginUploadFromFile(String path, FileMode mode, AsyncCallback callback, Object state) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 884\r\n   at Microsoft.WindowsAzure.Storage.Core.Util.AsyncExtensions.TaskFromVoidApm[T1,T2](Func`5 beginMethod, Action`1 endMethod, T1 arg1, T2 arg2, CancellationToken cancellationToken) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Core\Util\AsyncExtensions.cs:line 192\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.UploadFromFileAsync(String path, FileMode mode, CancellationToken cancellationToken) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 988\r\n   at VMApi.Controllers.SqliteController.<UploadBlobedTemplate>d__4.MoveNext() in C:\path\to\project\VMApi\VMApi\Controllers\SqliteController.cs:line 87",
      "RemoteStackTraceString": null,
      "RemoteStackIndex": 0,
      "ExceptionMethod": "8\nWinIOError\nmscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.IO.__Error\nVoid WinIOError(Int32, System.String)",
      "HResult": -2147024864,
      "Source": "mscorlib",
      "WatsonBuckets": null
    }
  ]
}

和两个主要的 C# 类如下。

SqliteController.cs

using System.Web.Http;
using Microsoft.WindowsAzure.Storage.Blob;
using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.WindowsAzure.Storage;
using System.Collections;
using System.Diagnostics;
using System.Collections.Generic;
using System.Threading;


namespace VMApi.Controllers
{
    public class SqliteController : ApiController
    {
        public static string tempFile()
        {
            return Path.GetTempPath();
        }

        public static async Task<bool> TemplateSql()
        {
            bool Complete = false;
            CloudBlobContainer container = ConnectionProvider.GetUpviseBlobContainer();
            CloudBlockBlob blockBlob = container.GetBlockBlobReference("Tables.sql");
            using (FileStream fileStream = File.OpenWrite(tempFile() + "Tables.sql"))
            {

                IAsyncResult asyncResult = blockBlob.BeginDownloadToStream(fileStream, null, null);
                await Task.Factory.FromAsync(asyncResult, (r) =>
                {
                    try
                    {
                        blockBlob.EndDownloadToStream(r);
                        Complete = true;
                    }
                    catch (StorageException q)
                    {
                        Trace.TraceError("Download Error" + q.ToString());
                        Complete = false;
                    }
                });
                Trace.TraceInformation("Blob Download Status: " + Complete + " " + fileStream.SafeFileHandle.IsClosed);
            }
            return Complete;
        }

        public static string CreateFileName()
        {
            DateTime Date = DateTime.Now;
            String dateF = string.Format("{0:dd-MM-yy}", Date);
            string Filename = string.Format("{0}\\AutoGeneratedDBTemplate-{1}-{2}.db", tempFile(), dateF, Guid.NewGuid().ToString());

            return Filename;
        }

        public static async Task<string> UploadBlobedTemplate()
        {
            string FileName = CreateFileName();
            await DBBUildController.BuildDB(FileName);

            string Complete = "Error.";
            string F = FileName;
            CloudBlobContainer container = ConnectionProvider.GetUpviseBlobContainer();
            CloudBlockBlob blockBlob = container.GetBlockBlobReference(F);

            CancellationToken ca = new CancellationToken();
            Task ado = blockBlob.UploadFromFileAsync(F, FileMode.Open, ca);
            await ado.ContinueWith(t =>
            {
                Complete = "All is well in the Database World";
            });
            return Complete;
        }
    }
}

DBBUild.Controller.cs

using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Data.SQLite;
    using System.Threading.Tasks;
    using System.Collections;
    using System.Diagnostics;

    namespace VMApi.Controllers
    {
        class DBBUildController
        {
            public static async Task BuildDB(string ParseFileName)
            {
                string templateSql = SqliteController.tempFile() + "Tables.sql";
                IEnumerable Lines;
                List<string> LineUnformatted = new List<string>();
                Task<bool> returnedTaskTResult = SqliteController.TemplateSql();
                bool result = await returnedTaskTResult;

                if (result)
                {
                    Lines = File.ReadLines(templateSql);
                    foreach (string v in Lines)
                    {
                        LineUnformatted.Add(v);
                    }
                }

                List<string> LineResults = new List<string>();
                foreach (string q in LineUnformatted)
                {
                    LineResults.Add(q);
                }
                string FileName = ParseFileName;

                SQLiteConnection.CreateFile(FileName);
                using (SQLiteConnection con = new SQLiteConnection("data source=" + FileName + ";Version=3;Pooling=False;"))
                {
                    await con.OpenAsync();

                    SQLiteCommand cmd = new SQLiteCommand(con);

                    using (var transaction = con.BeginTransaction())
                    {

                        Trace.TraceInformation("Starting the SQLCMD Builder");

                        foreach (string liner in LineResults)
                        {
                            cmd.CommandText = liner;
                            Trace.TraceInformation(liner);

                            try
                            {
                                await cmd.ExecuteNonQueryAsync();
                            }
                            catch (SQLiteException)
                            {
                                Trace.TraceError("An Error Occoured Executing the Query");
                            }
                        }
                        Trace.TraceInformation("Transaction Commited");
                        transaction.Commit();
                        transaction.Dispose();
                    }
                    Trace.TraceInformation("DB Connection Closed.");

                    Trace.TraceInformation("Sqlite Data Connection Close File Generation Complete.");
                }
            }
        }
    }

调试输出:

https://gist.github.com/anonymous/b0f74be1057b8e834e16

我已经尝试了几种关闭数据库的方法，但老实说，我不是 100% 确定这是这里的问题任何帮助将不胜感激。

Answer 1

非常感谢提供帮助的建议和链接，

最后，我通过组合 GC.Collect() 和 cmd.Dispose() 函数解决了这个问题。

这也可以通过对 SQLiteCommand 方法执行 using 来解决，但在我的特定代码段中会导致 cmd 出现未定义错误。

整个函数最终看起来像这样:

public static async Task BuildDB(string ParseFileName)
        {
            string templateSql = SqliteController.tempFile() + "Tables.sql";
            IEnumerable Lines;
            List<string> LineUnformatted = new List<string>();
            Task<bool> returnedTaskTResult = SqliteController.TemplateSql();
            bool result = await returnedTaskTResult;

            if (result)
            {
                Lines = File.ReadLines(templateSql);
                foreach (string v in Lines)
                {
                    LineUnformatted.Add(v);
                }
            }

            List<string> LineResults = new List<string>();
            foreach (string q in LineUnformatted)
            {
                LineResults.Add(q);
            }
            string FileName = ParseFileName;

            SQLiteConnection.CreateFile(FileName);
            using (SQLiteConnection con = new SQLiteConnection("data source=" + FileName + ";Version=3;Pooling=False;"))
            {
                await con.OpenAsync();

                SQLiteCommand cmd = new SQLiteCommand(con);

                using (var transaction = con.BeginTransaction())
                {

                    Trace.TraceInformation("Starting the SQLCMD Builder");

                    foreach (string liner in LineResults)
                    {
                        cmd.CommandText = liner;

                        try
                        {
                            await cmd.ExecuteNonQueryAsync();
                        }
                        catch (SQLiteException)
                        {
                            Trace.TraceError("An Error Occoured Executing the Query");
                        }
                    }
                    Trace.TraceInformation("Transaction Commited");
                    transaction.Commit();
                    cmd.Dispose();
                    transaction.Dispose();
                }

                GC.Collect();
                GC.WaitForPendingFinalizers();

                Trace.TraceInformation("Sqlite Data Connection Close File Generation Complete.");
            }
        }

为了清楚起见，我解决了这个问题:

            transaction.Commit();
            cmd.Dispose();
            transaction.Dispose();
            GC.Collect();
            GC.WaitForPendingFinalizers();

再次感谢所有的帮助:)