无法关闭/处置SQLite数据库C#上的文件锁
本文关键字：数据库 文件锁 SQLite 处置 | 更新日期: 2023-09-27 18:22:05
嗨，我目前正在开发一个生成模板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方法上使用来解决，但在我的特定代码中，这会导致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();
再次感谢您的帮助：）