因此，我开始使用SQLite本地存储数据，并希望确保以正确的方式进行存储。
处理此类操作的最佳实践是什么？我正在尝试创建一个可重复使用的连接，并在完成command.Dispose();后将其关闭。这是正确的方法吗？还是我应该以不同的方式创建连接？
using System.Data.SQLite;
public class DB
{
public static SQLiteConnection ConnectDB()
{
var m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();
return m_dbConnection;
}
public static void CreateTable()
{
string sql = "CREATE TABLE highscores (name VARCHAR(20), score INT)";
SQLiteCommand command = new SQLiteCommand(sql, ConnectDB());
command.ExecuteNonQuery();
MessageBox.Show("Table created");
command.Dispose();
}
}
作为对评论的回应，using语句是否用于CreateTable()，Dispose()是否不必使用，如下所示？
public static void CreateTable()
{
string sql = "CREATE TABLE highscores (name VARCHAR(20), score INT)";
using (SQLiteCommand command = new SQLiteCommand(sql, ConnectDB()))
{
command.ExecuteNonQuery();
MessageBox.Show("Table created");
}
}
看起来很合理，但应该使用close而不是dispose。更好的版本应该是类似的东西
public class DB
{
/// <summary>
/// Create a new database connection
/// </summary>
/// <param name="src">An optional connection string, if null or empty the default will be used</param>
/// <returns>A database connection</returns>
public static SqliteConnection ConnectDB(string src = null)
{
try
{
if (string.IsNullOrWhiteSpace(src))
src = "Data Source=MyDatabase.sqlite;Version=3;";
SqliteConnection dbConnection = new SqliteConnection("Data Source=MyDatabase.sqlite;Version=3;");
dbConnection.Open();
return dbConnection;
}
catch(System.Exception ex)
{
// Add logging here
throw ex;
// or just throw to re-throw the original error
}
}
/// <summary>
/// Close the database connection
/// </summary>
/// <param name="dbConnection"></param>
public static void CloseConnection(SqliteConnection dbConnection)
{
try
{
if (dbConnection != null)
dbConnection.Close();
}
catch (System.Exception ex)
{
// Add logging here, don't throw
}
}
/// <summary>
/// Execute a non query and returns the number of rows updated. Use this for create, drop, insert, delete and update commands
/// </summary>
/// <param name="sqlCmd">The SQL command to execute</param>
/// <param name="dbConnection">The database connection to use, if null an new connection will be opened and closed afterwards</param>
/// <param name="conString">An optional DB connection string</param>
/// <returns>The number of rows updated</returns>
public static async Task<int> ExecuteNonQuery(string sqlCmd, SqliteConnection dbConnection = null, string conString = null)
{
SqliteTransaction txn = null;
bool openedCon = false;
int result = 0;
try
{
if (dbConnection == null)
{
dbConnection = ConnectDB();
openedCon = true;
}
SqliteCommand command = new SqliteCommand(sqlCmd, dbConnection);
txn = command.Transaction;
result = await command.ExecuteNonQueryAsync();
await txn.CommitAsync();
}
catch (System.Exception ex)
{
if(txn != null)
{
await txn.RollbackAsync();
}
// Add logging here
throw ex;
// or just throw to re-throw the original error
}
finally
{
if (openedCon)
CloseConnection(dbConnection);
}
return result;
}
/// <summary>
/// Execute a command and return the result set in a data reader
/// </summary>
/// <param name="sqlCmd">The select to execute</param>
/// <param name="dbConnection">The database connection to use, if null an new connection will be opened and closed afterwards</param>
/// <param name="conString">An optional DB connection string</param>
/// <returns>SqliteDataReader with results</returns>
public static async Task<SqliteDataReader> ExecuteQuery(string sqlCmd, SqliteConnection dbConnection = null, string conString = null)
{
SqliteDataReader res = null;
bool openedCon = false;
try
{
if (dbConnection == null)
{
dbConnection = ConnectDB(conString);
openedCon = true;
}
SqliteCommand command = new SqliteCommand(sqlCmd, dbConnection);
res = await command.ExecuteReaderAsync();
}
catch (System.Exception ex)
{
res = null;
// Add logging here
throw ex;
// or just throw to re-throw the original error
}
finally
{
if (openedCon)
CloseConnection(dbConnection);
}
return res;
}
/// <summary>
/// Run a query and return the value in the first row, first column as an object
/// </summary>
/// <param name="sqlCmd">The select to execute</param>
/// <param name="dbConnection">The database connection to use, if null an new connection will be opened and closed afterwards</param>
/// <param name="conString">An optional DB connection string</param>
/// <returns>The value in the first row, first column as an object</returns>
public static async Task<object> ExecuteScalar(string sqlCmd, SqliteConnection dbConnection = null, string conString = null)
{
object res = null;
bool openedCon = false;
try
{
if (dbConnection == null)
{
dbConnection = ConnectDB(conString);
openedCon = true;
}
SqliteCommand command = new SqliteCommand(sqlCmd, dbConnection);
res = await command.ExecuteScalarAsync();
}
catch (System.Exception ex)
{
res = null;
// Add logging here
throw ex;
}
finally
{
if (openedCon)
CloseConnection(dbConnection);
}
return res;
}
}
你可以这样使用它；
static async Task Main(string[] args)
{
SqliteConnection con = null;
try
{
con = DB.ConnectDB();
int rowCount = await DB.ExecuteNonQuery("CREATE TABLE highscores (name VARCHAR(20), score INT)", con);
rowCount = await DB.ExecuteNonQuery("INSERT INTO highscores(name, score) VALUES ('first', 1)", con);
SqliteDataReader res = await DB.ExecuteQuery("SELECT * FROM highscores", con);
string maxName = (string)await DB.ExecuteScalar("SELECT max(name) FROM highscores", con);
}
catch (System.Exception ex)
{
// Resolve issue here
}
finally
{
if(con != null)
DB.CloseConnection(con);
}
}
您不需要专门调用dispose，当连接对象(本例中为con(超出范围时，会为您调用dispose。只需呼叫Close即可终止连接。
你可以在使用块中打开连接，这取决于你自己，但你仍然应该在使用结束前关闭连接。