我已经开始涉猎C＃，并且正在编写一段代码来处理将两个表插入到SQLite数据库中的情况，如果所涉及的平台尚不存在，则仅为“平台”插入行。
该代码按预期工作，但是我正在寻求有关是否正确处理事务的验证。
还要感谢任何指向时髦或可以改进的指针。
谢谢
private static string databaseFilePath = @"Data\";
private static string databaseFileName = "myDB.db";
private static string databaseFullPath = String.Concat(databaseFilePath, databaseFileName);
private static string platformTableName = "Platforms";
private static string gameTableName = "Games";
// Create our tables
private async void CreateTables(SQLiteConnection connection, SQLiteTransaction transaction)
{
// SQLite query string to create the Platform table
string createPlatformTableQuery = @"CREATE TABLE IF NOT EXISTS [" + platformTableName + @"] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[Name] TEXT NOT NULL,
[ShortCode] TEXT NOT NULL,
[Description] TEXT NOT NULL,
[ReleaseDate] TEXT NOT NULL,
[Images] TEXT NOT NULL,
[Video] TEXT NOT NULL,
[RomPaths] TEXT NOT NULL
)";
// SQLite query string to create the Games table
string createGamesTableQuery = @"CREATE TABLE IF NOT EXISTS [" + gameTableName + @"] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[Platform] TEXT NOT NULL,
[Name] TEXT NOT NULL,
[Crc] TEXT NOT NULL,
[Screenshots] TEXT NOT NULL,
[FanArt] TEXT NOT NULL,
[BoxArt] TEXT NOT NULL,
[CartArt] TEXT NOT NULL,
[DiscArt] TEXT NOT NULL,
[Music] TEXT NOT NULL,
[Video] TEXT NOT NULL,
[Players] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[ReleaseDate] TEXT NOT NULL,
[Publisher] TEXT NOT NULL,
[Developer] TEXT NOT NULL,
[Rating] TEXT NOT NULL,
[PlayCount] INTEGER NOT NULL,
[PlayTime] REAL NOT NULL,
[FilePath] TEXT NOT NULL
)";
// Create an SQLite command
using (SQLiteCommand command = new SQLiteCommand(connection))
{
try
{
// Set the command to create our platform table
command.CommandText = createPlatformTableQuery;
// Execute the query
await command.ExecuteNonQueryAsync();
// Set the command to create our games table
command.CommandText = createGamesTableQuery;
// Execute the query
await command.ExecuteNonQueryAsync();
}
// We encountered an exception
catch (SQLiteException e)
{
// Rollback the transaction
transaction.Rollback();
// Throw the exception
throw e;
}
}
}
// Insert Platforms (defined in PlatformList.cs) into the platforms table
private async void InsertPlatforms(SQLiteConnection connection, SQLiteTransaction transaction, PlatformList platformList)
{
// Is this a fresh population of the Platforms table data?
bool freshPopulation = false;
// Create an SQLite command
using (SQLiteCommand command = new SQLiteCommand(connection))
{
try
{
command.CommandText = "SELECT COUNT(*) from " + platformTableName;
var count = await command.ExecuteScalarAsync();
freshPopulation = Convert.ToInt16(count) <= 0;
}
// We encountered an exception
catch (SQLiteException e)
{
// Rollback the transaction
transaction.Rollback();
// Throw the exception
throw e;
}
}
// Loop through the platform list
foreach (var item in platformList.list)
{
// Populate an array from all items in each platformList entry
string[] values = new string[]
{
item.name, item.shortCode, item.description, item.releaseDate, item.images, item.video
};
// Comma quote the values
string commaQuotedValues = "'" + String.Join("','", values) + "'";
string commandText = String.Concat("INSERT INTO ", platformTableName, " (Name, ShortCode, Description, ReleaseDate, Images, Video) Values (", commaQuotedValues, ")");
// Create an SQLite command
using (SQLiteCommand command = new SQLiteCommand(connection))
{
try
{
// If this is the first time we are inserting data into the platforms table
if (freshPopulation)
{
// Set the command text
command.CommandText = commandText;
// Execute the query
await command.ExecuteNonQueryAsync();
}
// There is already data in the platforms table.. Let's ensure that it's up to date
else
{
// Set the command to select an existing row from the platforms table (if it exists)
command.CommandText = @"SELECT ShortCode FROM " + platformTableName + " WHERE ShortCode='" + item.shortCode + "'";
// Start the data reader
using (SQLiteDataReader reader = command.ExecuteReader())
{
// If this row isn't already inserted into the database
if (!reader.HasRows)
{
// Insert any rows not already inserted into the platforms table
using (SQLiteCommand insertCommand = new SQLiteCommand(connection))
{
try
{
// Set the command text
insertCommand.CommandText = commandText;
// Execute the query
await insertCommand.ExecuteNonQueryAsync();
}
// We encountered an exception
catch (SQLiteException e)
{
// Rollback the transaction
transaction.Rollback();
// Throw the exception
throw e;
}
}
}
}
}
}
// We encountered an exception
catch (SQLiteException e)
{
// Rollback the transaction
transaction.Rollback();
// Throw the exception
throw e;
}
}
}
}
// Init
public async void Init()
{
// Create an instance of the platform list
PlatformList platformList = new PlatformList();
// If the database doesn't exist
if (!File.Exists(databaseFullPath))
{
// Create the database
SQLiteConnection.CreateFile(databaseFullPath);
}
// Create an SQLite connection to the database
using (SQLiteConnection connection = new SQLiteConnection(@"data source=" + databaseFullPath))
{
// Open the connection to the database
await connection.OpenAsync();
// Start a transaction
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
// Create the required tables
CreateTables(connection, transaction);
// Insert platforms into the Platforms table
InsertPlatforms(connection, transaction, platformList);
// Commit the transaction
transaction.Commit();
}
}
}
参考方案
如果代码由于任何异常异常中止，则事务未完成；您不应将回滚限制为SQLiteException。
要重新抛出当前异常，应使用throw;而不指定异常对象。否则，异常信息（堆栈跟踪等）将被重置。
在SQLite中，一个连接只能有一个事务，因此您不需要告诉命令对象要使用哪个事务（其他数据库可能会有所不同）。
要回滚事务，您只需要一个try / catch，应该在创建事务时完成（删除所有其他catch es）：
using (var transaction = connection.BeginTransaction())
{
try
{
CreateTables(connection);
InsertPlatforms(connection, platformList);
}
catch (Exception e)
{
transaction.Rollback();
throw;
}
transaction.Commit();
}
我正在使用Retrofit来获取JSON答复。这是我实施的一部分-@GET("/api/report/list") Observable<Bills> listBill(@Query("employee_id") String employeeID); 而条例草案类是-public static class…
改造正在返回一个空的响应主体 - java我正在尝试使用Retrofit和Gson解析一些JSON。但是，我得到的响应机构是空的。当我尝试从对象中打印信息时，出现NullPointerException。我确保URL正确，并且我也确保POJO也正确。我正在使用jsonschema2pojo来帮助创建POJO类。这是我要解析的JSON{ "?xml": { "@versi…
每个文件合并后添加换行 - python我有很多类似以下内容的JSON文件：例如。1.json{"name": "one", "description": "testDescription...", "comment": ""} test.json{"name"…
使用python将信息从Linux文件解析到Windows - python我正在尝试从Linux环境中解析一些内容，然后使用python将它们转储到Windows环境中的excel文件中。eg: foo/bar/myfile.txt我要在Windows env中保留一些Excel文件的内容C:foo\bar\myfile.txt我知道如何提取所需的信息，但找不到从python的Linux env在Windows操作系统中创建文件的…
Div单击与单选按钮相同吗？ - php有没有一种方法可以使div上的click事件与表单环境中的单选按钮相同？我只希望下面的div提交值，单选按钮很丑代码输出如下：<input id="radio-2011-06-08" value="2011-06-08" type="radio" name="radio_date&#…