private uint _getBookId(uint authorId, uint titleId, uint formatId)
{
uint bookId = 0;
try
{
if (authorId > 0 && titleId > 0 && formatId > 0)
{
using (MySqlConnection conn = new MySqlConnection(_dbConnectionString))
{
string SqlQuery = "SELECT bookinfo.idBookInfo FROM BookInfo WHERE bookinfo.AuthorFKbi = @authoirid AND bookinfo.TitleFKbi = @titleid AND bookinfo.BookFormatFKbi = @formatid;";
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlQuery;
cmd.AddParameter("@authoirid", MySqlDbType.UInt32, authorId)
.AddParameter("@titleid", MySqlDbType.UInt32, titleId)
.AddParameter("@formatid", MySqlDbType.UInt32, formatId);
bookId = (uint)cmd.ExecuteScalar();
}
}
}
}
catch (Exception ex)
{
string errorMsg = "Database Error: " + ex.Message;
MessageBox.Show(errorMsg);
}
return(bookId);
}
public uint GetSeriesKey(AuthorModel author, string seriesTitle)
{
uint key = 0;
if (author != null && author.IsValid)
{
string SqlQuery = "SELECT series.idSeries FROM series WHERE series.SeriesName = @title AND series.AuthorOfSeries = @authorid;";
using (MySqlConnection conn = new MySqlConnection(_dbConnectionString))
{
int ResultCount = 0;
DataTable Dt = new DataTable();
try
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlQuery;
cmd.AddParameter("@title", MySqlDbType.String, seriesTitle)
.AddParameter("@authorid", MySqlDbType.UInt32, author.AuthorId);
cmd.ExecuteNonQuery();
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
ResultCount = sda.Fill(Dt);
if (ResultCount > 0)
{
key = Dt.Rows[0].Field <uint>(0);
}
}
}
catch (Exception ex)
{
string errorMsg = "Database Error: " + ex.Message;
MessageBox.Show(errorMsg);
key = 0;
}
}
}
return(key);
}
public string GetSeriesTitle(uint seriesId)
{
string title = string.Empty;
if (seriesId > 0)
{
string SqlQuery = "SELECT series.SeriesName FROM series WHERE series.idSeries = @seriesid;";
using (MySqlConnection conn = new MySqlConnection(_dbConnectionString))
{
int ResultCount = 0;
DataTable Dt = new DataTable();
try
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlQuery;
cmd.AddParameter("@seriesid", MySqlDbType.UInt32, seriesId);
cmd.ExecuteNonQuery();
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
ResultCount = sda.Fill(Dt);
if (ResultCount > 0)
{
title = Dt.Rows[0].Field <string>(0);
}
}
}
catch (Exception ex)
{
string errorMsg = "Database Error: " + ex.Message;
MessageBox.Show(errorMsg);
}
}
}
return(title);
}
private DataRow GetRawSeriesData(uint seriesId)
{
DataRow rawData = null;
if (seriesId > 0)
{
try
{
using (MySqlConnection conn = new MySqlConnection(_dbConnectionString))
{
string queryString = "SELECT * FROM series WHERE idSeries = @seriesid;";
int ResultCount = 0;
DataTable Dt = new DataTable();
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = queryString;
cmd.AddParameter("@seriesid", MySqlDbType.UInt32, seriesId);
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
ResultCount = sda.Fill(Dt);
if (ResultCount > 0)
{
rawData = Dt.Rows[0];
}
}
}
}
catch (Exception ex)
{
string errorMsg = "Database Error: " + ex.Message;
MessageBox.Show(errorMsg);
}
}
return(rawData);
}
public uint GetTitleKey(string title)
{
uint titleKey = 0;
string SqlQuery = "SELECT title.idTitle FROM title WHERE title.TitleStr = @title;";
using (MySqlConnection conn = new MySqlConnection(_dbConnectionString))
{
int ResultCount = 0;
DataTable Dt = new DataTable();
try
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlQuery;
cmd.AddParameter("@title", MySqlDbType.String, title);
cmd.ExecuteNonQuery();
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
ResultCount = sda.Fill(Dt);
if (ResultCount > 0)
{
titleKey = Dt.Rows[0].Field <uint>(0);
}
}
}
catch (Exception ex)
{
string errorMsg = "Database Error: " + ex.Message;
MessageBox.Show(errorMsg);
titleKey = 0;
}
}
return(titleKey);
}
public override void Execute()
{
if (TShock.DB.GetSqlType() == SqlType.Sqlite)
{
using (IDbConnection db = History.Database.CloneEx())
{
db.Open();
using (SqliteTransaction transaction = (SqliteTransaction)db.BeginTransaction())
{
using (SqliteCommand command = (SqliteCommand)db.CreateCommand())
{
command.CommandText = "INSERT INTO History (Time, Account, Action, XY, Data, Style, Paint, WorldID, Text) VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8)";
for (int i = 0; i < 9; i++)
{
command.AddParameter("@" + i, null);
}
command.Parameters[7].Value = Main.worldID;
foreach (Action a in actions)
{
command.Parameters[0].Value = a.time;
command.Parameters[1].Value = a.account;
command.Parameters[2].Value = a.action;
command.Parameters[3].Value = (a.x << 16) + a.y;
command.Parameters[4].Value = a.data;
command.Parameters[5].Value = a.style;
command.Parameters[6].Value = a.paint;
command.Parameters[8].Value = a.text;
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
}
else
{
using (IDbConnection db = History.Database.CloneEx())
{
db.Open();
using (MySqlTransaction transaction = (MySqlTransaction)db.BeginTransaction())
{
using (MySqlCommand command = (MySqlCommand)db.CreateCommand())
{
command.CommandText = "INSERT INTO History (Time, Account, Action, XY, Data, Style, Paint, WorldID, Text) VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8)";
for (int i = 0; i < 9; i++)
{
command.AddParameter("@" + i, null);
}
command.Parameters[7].Value = Main.worldID;
foreach (Action a in actions)
{
command.Parameters[0].Value = a.time;
command.Parameters[1].Value = a.account;
command.Parameters[2].Value = a.action;
command.Parameters[3].Value = (a.x << 16) + a.y;
command.Parameters[4].Value = a.data;
command.Parameters[5].Value = a.style;
command.Parameters[6].Value = a.paint;
command.Parameters[8].Value = a.text;
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
}
}