Skip to main content
 首页 » 编程设计

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

2024年02月05日34youxin

您好，我目前正在开发一个生成模板 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% 确定这是这里的问题任何帮助将不胜感激。

请您参考如下方法:

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

最后，我通过组合 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();

再次感谢所有的帮助:)


  文章来源：
  https://www.windname.com/post/dddba2b4.html
阅读延展