C# 如果数据库丢失，使 SQLite 连接失败？（删除/移动）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/807208/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Make SQLite connection fail if database is missing? (deleted/moved)
提问by CasperT
I have the following method inside class DBConnection. I call the method like this: SQLiteConnection conn = DBConnection.OpenDB();when I want to open an connection, so that I can execute my queries. I can call a similar method when I want to close the connection.
我里面有以下方法class DBConnection。我这样调用方法：SQLiteConnection conn = DBConnection.OpenDB();当我想打开一个连接时，以便我可以执行我的查询。当我想关闭连接时，我可以调用类似的方法。
The method:
方法：
public static SQLiteConnection OpenDB()
{
try
{
//Gets connectionstring from app.config
string myConnectString =
ConfigurationManager.ConnectionStrings[
"LegMedSQLLite.Properties.Settings.LegMedSQLLiteDBConnectionString"].ConnectionString;
var conn = new SQLiteConnection(myConnectString);
conn.Open();
return conn;
}
catch (SQLiteException e)
{
MessageBox.Show(e.ToString(), "TEST");
return null;
}
}
This all works fine and dandy. The problem is the try-catch though. Let us imagine the following scenario:
这一切都很好，花花公子。问题是try-catch。让我们想象一下下面的场景：
- The database file has been moved/delete.
- 数据库文件已被移动/删除。
The exception will never be thrown. Actually, the first catch I stumble upon is when I execute my first query - where it figures that there is no such table(s) and it throws its own exception. I was stunned by this weird phenomenon, but I soon found out that SQLite creates a new emptydatabase. By empty is mean no tables, nothing, just an SQLite database file with the same name as the old database which was supposed to be there.
永远不会抛出异常。实际上，我偶然发现的第一个问题是当我执行我的第一个查询时 - 它认为没有这样的表并抛出自己的异常。我被这个奇怪的现象惊呆了，但我很快发现 SQLite 创建了一个新的 空数据库。空是指没有表，什么都没有，只是一个与旧数据库同名的 SQLite 数据库文件，它应该在那里。
This is an issue, I want the application to know if there is something wrong (database not found, corrupted, being used by another process etc.) as soon as I try to call SQLiteConnection conn = DBConnection.OpenDB();.
这是一个问题，我希望应用程序在我尝试调用SQLiteConnection conn = DBConnection.OpenDB();.
Naturally, I could try call a File.Exists in my method, but that doesn't seem like a proper solution. Any help?
当然，我可以尝试在我的方法中调用 File.Exists，但这似乎不是一个合适的解决方案。有什么帮助吗？
采纳答案by Stephen Jennings
At least in System.Data.SQLite, you can add "FailIfMissing=True" to your connection string. SQLiteConnection.Open()will throw a SQLiteExceptionif the database file does not exist.
至少在 System.Data.SQLite 中，您可以FailIfMissing=True在连接字符串中添加“ ”。 如果数据库文件不存在，SQLiteConnection.Open()将抛出一个SQLiteException。
string ConnectString = "Data Source=file.sdb; FailIfMissing=True";
DbConnection db = new SQLiteConnection(ConnectString);
db.Open(); // Fails if file.sdb does not exist
See SQLite Connection String Samplesfor another example, look for "Disable create database behaviour".
有关另一个示例，请参阅SQLite 连接字符串示例，查找“禁用创建数据库行为”。
回答by Eoin Campbell
I haven't used SQLite but that is pretty bizarre behaviour to auto create a brand new database.
我没有使用过 SQLite，但是自动创建一个全新的数据库是非常奇怪的行为。
You could just adjust your try block to do a Select top 1 * From Tableimmediately after you open the connection, if it works, throw away the result and continue to return your connobject. If it fails, the exception handler should fire.
您可以Select top 1 * From Table在打开连接后立即调整您的 try 块，如果它有效，则丢弃结果并继续返回您的conn对象。如果失败，异常处理程序应该触发。
回答by Ed Schwehm
If there is no way to change the default SQLite behavior, then you might have to do a File.Exists. That would be better than connecting and creating a new file, checking to see if it's the database you want, then deleting the new file in the catch block.
如果无法更改默认的 SQLite 行为，那么您可能必须执行 File.Exists。这比连接并创建一个新文件，检查它是否是您想要的数据库，然后在 catch 块中删除新文件要好。
回答by Joel Coehoorn
Don't catch at that level. Instead, SQLiteConnectionshould implement IDisposable, meaning you should just return the open connection and allow calling code to handle any exceptions, as well as rely on the Dispose method to close the connection.
不要赶上那个级别。相反，SQLiteConnection应该实现 IDisposable，这意味着您应该只返回打开的连接并允许调用代码处理任何异常，以及依赖 Dispose 方法来关闭连接。
回答by eodonohoe
If you want to detect database corruption issues on start up , you can execute the command
如果要在启动时检测数据库损坏问题，可以执行命令
pragma integrity_check;
编译指示完整性_检查；
or
或者
pragma quick_check; ( which is faster, but less thorough )
pragma quick_check; （这更快，但不太彻底）
This returns a single row with the value "ok".
这将返回值为“ok”的单行。
Otherwise it will report errors that it encounters.
否则它会报告它遇到的错误。
回答by GGSoft
For sqlite use this: Suppose you have connection string in textbox txtConnSqlite
对于 sqlite 使用这个：假设你在文本框 txtConnSqlite 中有连接字符串
Using conn As New System.Data.SQLite.SQLiteConnection(txtConnSqlite.Text)
Dim FirstIndex As Int32 = txtConnSqlite.Text.IndexOf("Data Source=")
If FirstIndex = -1 Then MsgBox("ConnectionString is incorrect", MsgBoxStyle.Exclamation, "Sqlite") : Exit Sub
Dim SecondIndex As Int32 = txtConnSqlite.Text.IndexOf("Version=")
If SecondIndex = -1 Then MsgBox("ConnectionString is incorrect", MsgBoxStyle.Exclamation, "Sqlite") : Exit Sub
Dim FilePath As String = txtConnSqlite.Text.Substring(FirstIndex + 12, SecondIndex - FirstIndex - 13)
If Not IO.File.Exists(FilePath) Then MsgBox("Database file not found", MsgBoxStyle.Exclamation, "Sqlite") : Exit Sub
Try
conn.Open()
Dim cmd As New System.Data.SQLite.SQLiteCommand("SELECT * FROM sqlite_master WHERE type='table';", conn)
Dim reader As System.Data.SQLite.SQLiteDataReader
cmd.ExecuteReader()
MsgBox("Success", MsgBoxStyle.Information, "Sqlite")
Catch ex As Exception
MsgBox("Connection fail", MsgBoxStyle.Exclamation, "Sqlite")
End Try
End Using
I think you can easilly convert it to c# code
我认为您可以轻松地将其转换为 C# 代码
