public LibraryDatabaseManager(String dbFolderPath)
{
DatabaseFile = String.Format(dbFolderPath + "{0}Library.db",
Path.DirectorySeparatorChar);
Connection = new SqliteConnection (
"Data Source = " + LibraryDatabaseManager.DatabaseFile +
"; Version = 3;");
bool exists = File.Exists (LibraryDatabaseManager.DatabaseFile);
if (!exists) {
SqliteConnection.CreateFile (LibraryDatabaseManager.DatabaseFile);
}
Connection.Open ();
if (!exists) {
using (SqliteCommand command = new SqliteCommand (Connection)) {
command.CommandText =
"CREATE TABLE Books (" +
"BookID INTEGER PRIMARY KEY NOT NULL, " +
"BookTitle TEXT, " +
"BookAuthor TEXT, " +
"BookGenre TEXT, " +
"BookPublishedYear INTEGER, " +
"BookPath TEXT);";
command.ExecuteNonQuery();
}
}
}
/**
* Constructor will try and open the database if none exsists it will create one
* In addition it will create new tables if non exsist.
*/
public SqlWrapper(GameObjectList objectList)
{
gameObjectList = objectList;
//try and open database, if failed make one!
Database = new sqliteConnection("Data Source=Dungeon" + ";Version=3;FailIfMissing=True");
try
{
Database.Open();
}
catch
{
Console.WriteLine("Open existing DB failed: So creating one");
sqliteConnection.CreateFile("Dungeon");
Database = new sqliteConnection("Data Source=Dungeon" + ";Version=3;FailIfMissing=True");
Database.Open();
}
//do the disable roll back thingy
//Not sure what this does, fixed a problem, found it on stack overflow
String disableRollback = "PRAGMA journal_mode = OFF";
sqliteCommand cmd = new sqliteCommand();
cmd.Connection = Database;
cmd.CommandText = disableRollback;
cmd.ExecuteNonQuery();
//create tables
new sqliteCommand(createTable + itemTableName + itemColumns, Database).ExecuteNonQuery();
new sqliteCommand(createTable + playerTableName + playerColumns, Database).ExecuteNonQuery();
}
/**
* Used if loading from an exsisitng dungeon. It will cycle through the dungeon table
* getting all the rooms and adding them to the new dungeons room list
* Having the dungeon as dynamic memory, allows for quicker sanity checks incase
* players try and break things. The databse is always written to if the player
* actuallty does anything.
*/
public Dungeon GetDungeon()
{
Dungeon d = new Dungeon();
try
{
var command = new sqliteCommand("select * from " + dungeonTableName, Database);
var reader = command.ExecuteReader();
while (reader.Read())
{
Room r = new Room();
r.name = reader["name"].ToString();
r.desc = reader["description"].ToString();
r.RoomIndex = Int32.Parse(reader["rIndex"].ToString());
r.north = Int32.Parse(reader["north"].ToString());
r.east = Int32.Parse(reader["east"].ToString());
r.south = Int32.Parse(reader["south"].ToString());
r.west = Int32.Parse(reader["west"].ToString());
d.GetRoomList().Add(r);
}
reader.Close();
Console.WriteLine("");
}
catch (Exception ex)
{
Console.WriteLine("Failed to display DB" + ex);
}
return(d);
}
/**
* Adds a new player to the player table
* @param tempPlayer the player so far that we will add.
* @param password the salted-hashes password the player used to login
* @param salt the salt used to hash the password and used for encryption
*/
public bool AddPlayer(Player tempPlayer, String password, String salt)
{
var command = new sqliteCommand("select * from " + playerTableName + " where name =:id", Database);
command.Parameters.Add("id", DbType.String).Value = tempPlayer.PlayerName;
var reader = command.ExecuteReader();
if (reader.HasRows == false && !U.HasBadChars(tempPlayer.PlayerName) && !U.HasBadChars(password))
{
try
{
command = new sqliteCommand("INSERT INTO " + playerTableName +
" (name, password, salt, rIndex) " +
"VALUES ($n, $p, $s, $i) ", Database);
command.Parameters.Add("$n", DbType.String).Value = tempPlayer.PlayerName;
command.Parameters.Add("$p", DbType.String).Value = password;
command.Parameters.Add("$s", DbType.String).Value = salt;
command.Parameters.Add("$i", DbType.Int32).Value = tempPlayer.RoomIndex;
command.ExecuteNonQuery();
ActivePlayers.Add(tempPlayer.PlayerName);
return(true);
}
catch (Exception ex)
{
Console.WriteLine("Failed Adding to DB: " + ex);
return(false);
}
}
else
{
return(false);
}
}
/**
* Get all items with where the owner field is equal to that of the players name
* @param owner the entity to whom the items belong.
*/
public String GetOwnedItems(String owner)
{
String rString = U.NL("");
var command = new sqliteCommand("select * from " + itemTableName + " where owner =:name ", Database);
command.Parameters.Add("name", DbType.String).Value = owner;
try
{
var reader = command.ExecuteReader();
while (reader.Read())
{
//make the description
String id = reader["itemID"].ToString();
rString += U.NL("Name: " + gameObjectList.GetItem(id).itemName);
rString += U.NL("Description: " + gameObjectList.GetItem(id).description);
U.NL("");
}
}
catch (Exception ex)
{
Console.Write("Failed to get inventory" + ex);
rString += "Could not find items";
}
return(rString);
}
/**
* Try and move an item from an oldowner to a new owner
* @param oldOwner the owner trying to move the item from
* @param newOwner the owner tyring to move the item too.
* @param item the name of the item to be moved.
*/
public String MoveItem(String oldOwner, String newOwner, String item)
{
var command = new sqliteCommand("select * from " + itemTableName + " where owner =:owner AND name =:item ", Database);
command.Parameters.Add("owner", DbType.String).Value = oldOwner;
command.Parameters.Add("item", DbType.String).Value = item;
try
{
var reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
if (ChangeItemOwner(reader["uniqueID"].ToString(), newOwner))
{
return("You ");
}
}
}
catch (Exception ex)
{
Console.Write("Item Not recognised: " + ex);
}
return("Failed To ");
}
private void apply_Click(object sender, EventArgs e)
{
SqliteCommand command = new SqliteCommand(connection);
String date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
command.CommandText = "INSERT OR REPLACE INTO comments (guid, comment, date) VALUES (@guid, @comment, @date)";
command.Parameters.Add(new SqliteParameter("@guid", guid));
command.Parameters.Add(new SqliteParameter("@comment", input.Text));
command.Parameters.Add(new SqliteParameter("@date", date));
command.ExecuteNonQuery();
command.Dispose();
if (mode == "players")
{
Thread thread = new Thread(new ThreadStart(gui.thread_Player));
thread.IsBackground = true;
thread.Start();
}
else if (mode == "bans")
{
Thread thread = new Thread(new ThreadStart(gui.thread_Bans));
thread.IsBackground = true;
thread.Start();
}
else if (mode == "player database")
{
Thread thread = new Thread(new ThreadStart(gui.thread_Database));
thread.IsBackground = true;
thread.Start();
}
this.Close();
}
public override void update(ActionTimer timer)
{
List <Account> _list = new List <Account>();
using (SQLiteCommand command = new SQLiteCommand(Sqlite.getSqlite().connection))
{
command.CommandText = "SELECT id, name FROM `account`";
using (SQLiteDataReader sdrITM = command.ExecuteReader())
{
if (sdrITM.HasRows)
{
while (sdrITM.Read())
{
int accID = Convert.ToInt32(sdrITM["id"]);
string name = Convert.ToString(sdrITM["name"]);
_list.Add(new Account()
{
accountID = accID, name = name
});
}
}
}
}
list = _list.ToArray();
isFinished = true;
timer.removeAction(this);
}
public bool register(String name, String password)
{
if (existsName(name))
{
return(false);
}
//Setting default parameter! Can be overwritten by other modules, after registration!
player.setSpawnInfos(@"NEWWORLD\NEWWORLD.ZEN", null, null);
player.HPMax = 10;
player.HP = 10;
using (SQLiteCommand command = new SQLiteCommand(Sqlite.getSqlite().connection)) {
command.CommandText = "INSERT INTO `account` (";
command.CommandText += " `id`, `name`, `password`, `posx`, `posy`, `posz`, `world`)";
command.CommandText += "VALUES( NULL, @name, @password, @posx, @posy, @posz, @world)";
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@password", password);
command.Parameters.AddWithValue("@posx", 0);
command.Parameters.AddWithValue("@posy", 0);
command.Parameters.AddWithValue("@posz", 0);
command.Parameters.AddWithValue("@world", player.Map);
command.ExecuteNonQuery();
command.CommandText = @"select last_insert_rowid()";
accountID = (long)command.ExecuteScalar();
}
state = State.LoggedIn;
return(true);
}
public static void log_CharacterStat(NPCProto proto, CharStat stat, int value)
{
if (proto is NPC)
{
return;
}
Player player = (Player)proto;
if (player.getAccount() == null || !player.IsSpawned())
{
return;
}
lock (connection)
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "INSERT INTO `logStats` (";
command.CommandText += " `id`, `accountID`, `type`, `value`, `time`)";
command.CommandText += "VALUES( NULL, @accID, @type, @value, @time)";
command.Parameters.AddWithValue("@accID", player.getAccount().accountID);
command.Parameters.AddWithValue("@type", (int)stat);
command.Parameters.AddWithValue("@value", value);
command.Parameters.AddWithValue("@time", DateTime.Now.Ticks);
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Clears all items from the database where their PublishDate is before the date provided.
/// </summary>
/// <param name="date"></param>
public void ClearItemsBeforeDate(DateTime date)
{
try
{
using (SqliteConnection connection = new SqliteConnection(ItemsConnectionString))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
string sql = @"DELETE FROM items WHERE DATETIME(publishdate) <= DATETIME(@date)";
command.CommandText = sql;
SqliteParameter parameter = new SqliteParameter("@date", DbType.String);
parameter.Value = date.ToString("yyyy-MM-dd HH:mm:ss");
command.Parameters.Add(parameter);
int rows = command.ExecuteNonQuery();
Logger.Info("ClearItemsBeforeDate before {0} cleared {1} rows.", date.ToString("yyyy-MM-dd HH:mm:ss"), rows);
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occured while clearing items before {0}: \n{1}", date, e);
}
}
private static void InitDefaultTables()
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "CREATE TABLE IF NOT EXISTS `logStats` (";
command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
command.CommandText += " `accountID` INTEGER NOT NULL,";
command.CommandText += " `type` INTEGER NOT NULL,";
command.CommandText += " `value` INTEGER NOT NULL,";
command.CommandText += " `time` INTEGER NOT NULL";
command.CommandText += ")";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS `logEvents` (";
command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
command.CommandText += " `accountID` INTEGER NOT NULL,";
command.CommandText += " `InteractAccountID` INTEGER NOT NULL,";
command.CommandText += " `type` INTEGER NOT NULL,";
command.CommandText += " `lastPing` INTEGER NOT NULL,";
command.CommandText += " `averagePing` INTEGER NOT NULL,";
command.CommandText += " `time` INTEGER NOT NULL,";
command.CommandText += " `message` TEXT NOT NULL";
command.CommandText += ")";
command.ExecuteNonQuery();
}
}
public bool ExcuteTransaction(string sql)
{
var cmds = sql.Split(';');
using (SqliteConnection conn = new SqliteConnection(this.SqlConfig.ConnectionString))
{
conn.Open();
SqliteCommand cmd = new SqliteCommand(conn);
SqliteTransaction tran = conn.BeginTransaction();
try
{
foreach (var cmdSql in cmds)
{
cmd.CommandText = cmdSql;
cmd.ExecuteNonQuery();
}
tran.Commit();
conn.Close();
return true;
}
catch (Exception e)
{
tran.Rollback();
conn.Close();
throw new Exception(e.Message + " sql:" + sql);
}
finally
{
conn.Close();
}
}
}
public static void Init()
{
try
{
_cards = new Dictionary<int, CardData>();
string currentPath = Assembly.GetExecutingAssembly().Location;
currentPath = Path.GetDirectoryName(currentPath) ?? "";
string absolutePath = Path.Combine(currentPath, "cards.cdb");
if (!File.Exists(absolutePath))
{
throw new Exception("Could not find the cards database.");
}
using (SqliteConnection connection = new SqliteConnection("Data Source=" + absolutePath))
{
connection.Open();
const string select =
"SELECT datas.id, alias, type, level, race, attribute, atk, def, name, desc " +
"FROM datas INNER JOIN texts ON datas.id = texts.id";
using (SqliteCommand command = new SqliteCommand(select, connection))
using (SqliteDataReader reader = command.ExecuteReader())
InitCards(reader);
}
}
catch (Exception ex)
{
throw new Exception("Could not initialize the cards database. Check the inner exception for more details.", ex);
}
}
protected int ExecuteNonQuery(SqliteCommand cmd)
{
try
{
lock (m_Connection)
{
var newConnection =
(SqliteConnection)((ICloneable)m_Connection).Clone();
if (newConnection.State != ConnectionState.Open)
newConnection.Open();
cmd.Connection = newConnection;
return cmd.ExecuteNonQuery();
}
}
catch (Mono.Data.Sqlite.SqliteException)
{
//m_log.Warn("[SQLiteDataManager]: Exception processing command: " + cmd.CommandText + ", Exception: " + ex);
//throw ex;
}
catch (Exception ex)
{
m_log.Warn("[SQLiteDataManager]: Exception processing command: " + cmd.CommandText + ", Exception: " + ex);
throw ex;
}
return 0;
}
public void DateTimeConvert ()
{
var dateTime = new DateTime (2016, 9, 15, 12, 1, 53);
var guid = Guid.NewGuid ();
using (var connection = new SqliteConnection ("Data Source=" + _databasePath)) {
connection.Open ();
var sqlCreate = "CREATE TABLE TestTable (ID uniqueidentifier PRIMARY KEY, Modified datetime)";
using (var cmd = new SqliteCommand (sqlCreate, connection)) {
cmd.ExecuteNonQuery ();
}
var sqlInsert = "INSERT INTO TestTable (ID, Modified) VALUES (@id, @mod)";
using (var cmd = new SqliteCommand (sqlInsert, connection)) {
cmd.Parameters.Add (new SqliteParameter ("@id", guid));
cmd.Parameters.Add (new SqliteParameter ("@mod", dateTime));
cmd.ExecuteNonQuery ();
}
}
using (var connection = new SqliteConnection ("Data Source=" + _databasePath)) {
connection.Open ();
var sqlSelect = "SELECT * from TestTable";
using (var cmd = new SqliteCommand (sqlSelect, connection))
using (var reader = cmd.ExecuteReader ()) {
while (reader.Read ()) {
Assert.AreEqual (guid, reader.GetGuid (0), "#1");
Assert.AreEqual (dateTime, reader.GetDateTime (1), "#2");
}
}
}
}
public void Create()
{
try {
if(File.Exists(dbPath)) {
cnn.Dispose();
// We want to start with a fresh db for each full run
// The database is created on the first open()
File.Delete(dbPath);
}
}
catch(Exception e) {
throw e;
}
try {
using (var createCommand = new SqliteCommand ("CREATE TABLE Company (RecordId int, Name text);", cnn))
using (var insertCommand = new SqliteCommand ("INSERT INTO Company VALUES (1, 'Test CO')", cnn)) {
cnn.Open();
createCommand.ExecuteNonQuery();
insertCommand.ExecuteNonQuery();
}
}
catch(Exception e) {
Console.WriteLine (e);
throw new AssertionException ("Create table failed", e);
}
finally {
cnn.Close();
}
}
private bool _CreateDB()
{
if (_Connection == null)
return false;
try
{
using (SQLiteCommand command = new SQLiteCommand(_Connection))
{
command.CommandText = "CREATE TABLE IF NOT EXISTS Version (Value INTEGER NOT NULL);";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Version (Value) VALUES(0)";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS Images ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Path TEXT NOT NULL, width INTEGER NOT NULL, height INTEGER NOT NULL);";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS ImageData ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"ImageID INTEGER NOT NULL, Data BLOB NOT NULL);";
command.ExecuteNonQuery();
}
}
catch (Exception e)
{
CLog.Error("Error creating Ressource DB " + e);
return false;
}
return true;
}
public void CloseSqlConnection()
{
if (dbCommand != null) {
dbCommand.Dispose ();
}
dbCommand = null;
if (reader != null) {
reader.Dispose ();
}
reader = null;
if (dbConnection != null) {
dbConnection.Close ();
}
dbConnection = null;
Debug.Log ("Disconnected from db.");
}
public DBManager(string DatabaseName)
{
if (!File.Exists(DatabaseName))
{
SqliteConnection.CreateFile(DatabaseName);
}
dbConnection = new SqliteConnection("Data Source=" + DatabaseName + ";Version=3;");
dbConnection.Open();
cmd = dbConnection.CreateCommand();
string checkForTable = "SELECT name FROM sqlite_master WHERE type='table' AND name='users';";
cmd.CommandText = checkForTable;
if (cmd.ExecuteNonQuery() == 0)
{
string create = "CREATE TABLE users (id INT PRIMARY KEY, name CHAR(50));";
cmd = dbConnection.CreateCommand();
cmd.CommandText = create;
try
{
cmd.ExecuteNonQuery();
}
catch
{
System.Diagnostics.Debug.WriteLine("Table already exists...");
}
}
cmd = null;
}
public static bool Init()
{
try
{
m_cards = new Dictionary <int, CardData>();
string currentPath = System.Reflection.Assembly.GetExecutingAssembly().Location;
currentPath = Path.GetDirectoryName(currentPath) ?? "";
string absolutePath = Path.Combine(currentPath, "Content/cards.cdb");
if (!File.Exists(absolutePath))
{
return(false);
}
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + absolutePath))
{
connection.Open();
const string select =
"SELECT datas.id, alias, type, level, race, attribute, atk, def, name, desc " +
"FROM datas INNER JOIN texts ON datas.id = texts.id";
SQLiteCommand command = new SQLiteCommand(select, connection);
using (SQLiteDataReader reader = command.ExecuteReader())
InitCards(reader);
command.Dispose();
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public void Open(string filePath)
{
if (_command != null)
_command.Dispose();
if (_conn != null)
_conn.Close();
try
{
ConnectionStringBuilder connstr = new ConnectionStringBuilder();
_conn = new Connection();
connstr.DataSource = (filePath.EndsWith("/") || filePath.EndsWith("\\")) ? filePath + "sys.db" : filePath + "/sys.db";
_conn.ConnectionString = connstr.ToString();
_conn.Open();
_command = new Command(_conn);
_command.CommandText = "SELECT Content FROM [Text] WHERE [Name]=:name AND [Language]=:language";
_command.Parameters.Add(new Parameter(":name", DbType.Binary));
_command.Parameters.Add(new Parameter(":language", DbType.Binary));
}
catch
{
if (_command != null)
_command.Dispose();
if (_conn != null)
_conn.Dispose();
_command = null;
_conn = null;
throw new DatabaseException("Cannot Open System Database");
}
}
/// <summary>
/// Returns all subscribed feeds.
/// </summary>
public IEnumerable<Feed> GetAllFeeds()
{
var feeds = new List<Feed>();
using (var connection = new SqliteConnection("Data Source=" + dbPath))
using (var query = new SqliteCommand("SELECT * FROM Feeds", connection))
{
connection.Open();
var reader = query.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
var feed = new Feed();
feed.Id = int.Parse(reader["id"].ToString());
feed.Name = reader ["name"].ToString();
feed.Url = reader ["url"].ToString();
feed.LastUpdated = DateTime.Parse (reader ["LastUpdated"].ToString ());
feed.CategoryId = int.Parse(reader["categoryId"].ToString());
feeds.Add(feed);
}
reader.Close();
}
return feeds;
}
public void Fill(int id)
{
ItemId = id;
NewItem = false;
MainClass.StatusMessage(String.Format ("Запрос выставки №{0}...", id));
string sql = "SELECT exhibition.* FROM exhibition WHERE exhibition.id = @id";
try
{
SqliteCommand cmd = new SqliteCommand(sql, (SqliteConnection) QSMain.ConnectionDB);
cmd.Parameters.AddWithValue("@id", id);
using(SqliteDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();
labelID.Text = rdr["id"].ToString();
entryName.Text = rdr["name"].ToString();
entryPhone1.Text = DBWorks.GetString(rdr, "phone", "");
entryPhone2.Text = DBWorks.GetString(rdr, "phone2", "");
textAddress.Buffer.Text = DBWorks.GetString(rdr, "address", "");
}
MainClass.StatusMessage("Ok");
this.Title = entryName.Text;
}
catch (Exception ex)
{
QSMain.ErrorMessageWithLog(this, "Ошибка получения информации о номенклатуре!", logger, ex);
}
TestCanSave();
}
/// <summary>
/// Returns all categories.
/// </summary>
public IEnumerable<Category> GetAllCategories()
{
var categories = new List<Category>();
using (var connection = new SqliteConnection("Data Source=" + dbPath))
using (var query = new SqliteCommand("SELECT * FROM Categories", connection))
{
connection.Open();
var reader = query.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
var category = new Category();
category.Id = int.Parse(reader["id"].ToString());
category.Name = reader ["name"].ToString();
categories.Add(category);
}
reader.Close();
}
return categories;
}
public IList<SyncItem> GetAll()
{
List<SyncItem> returnData = new List<SyncItem>();
using (var conn = _provider.GetConnection())
{
conn.Open();
using (SqliteCommand command =
new SqliteCommand(
string.Format(
@"SELECT Id, FileKey, CloudAssetId,
CloudLastUpdatedDate, CloudCreatedDate, IsDeleted,
Size FROM {0}", SyncItem.TableName), conn))
{
var reader = command.ExecuteReader();
while (reader.Read())
{
SyncItem item = new SyncItem();
item.Id = reader.GetInt32(0);
item.FileKey = reader.GetString(1);
item.CloudAssetId = reader.GetString(2);
item.CloudLastUpdated = new DateTime(reader.GetInt64(3));
item.CloudCreated = new DateTime(reader.GetInt64(4));
item.IsDeleted = reader.GetBoolean(5);
returnData.Add(item);
}
}
conn.Close();
}
return returnData;
}
public GUIhosts(GUImain gui, SqliteConnection connection, SqliteCommand command)
{
InitializeComponent();
InitializeList();
this.gui = gui;
_connection = connection;
_command = command;
using (_command = new SqliteCommand("SELECT host, port, password FROM hosts ORDER BY id DESC", _connection))
{
using (SqliteDataReader reader = _command.ExecuteReader())
{
while (reader.Read())
{
String host = gui.GetSafeString(reader, 0);
String port = gui.GetSafeString(reader, 1);
String password = gui.GetSafeString(reader, 2);
String[] items = { host, port, password };
ListViewItem item = new ListViewItem(items);
list.Items.Add(item);
}
}
}
}
void addRow(string sName, string sNote)
{
SqliteCommand cmd=new SqliteCommand(con);
cmd.CommandText=
string.Format("Insert into Cars (id,name,note) VALUES(NULL,'{0}','{1}');",
sName,sNote);
da.InsertCommand=cmd;
da.InsertCommand.ExecuteNonQuery();
DataTable dt = ds.Tables[0];
DataRow dr = dt.NewRow();
dr["name"]=sName;
dr["note"]=sNote;
dr["id"]=getNewID();
dt.Rows.Add(dr);
System.Console.WriteLine(da.InsertCommand.CommandText);
dt.AcceptChanges();
ds.AcceptChanges();
da.Update(dt);
doRefresh();
}
private bool _CreateCoverDB()
{
try
{
using (var command = new SQLiteCommand(_Connection))
{
command.CommandText = "CREATE TABLE IF NOT EXISTS Version (Value INTEGER NOT NULL);";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Version (Value) VALUES(@Value)";
command.Parameters.Add("@Value", DbType.Int32).Value = CSettings.DatabaseCoverVersion;
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS Cover ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Path TEXT NOT NULL, width INTEGER NOT NULL, height INTEGER NOT NULL);";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS CoverData ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"CoverID INTEGER NOT NULL, Data BLOB NOT NULL);";
command.ExecuteNonQuery();
}
}
catch (Exception e)
{
CLog.LogError("Error creating Cover DB " + e);
return(false);
}
return(true);
}
public void CommitTransaction()
{
var lst = new List<string>();
using (var cmd = new SqliteCommand(String.Format("SELECT DISTINCT [TableName] FROM {0}", TranStatusTable), ActiveConnection))
{
using (SqliteDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
lst.Add(r.GetString(0));
}
}
}
SqliteTransaction tran = ActiveConnection.BeginTransaction();
try
{
foreach (String tableName in lst)
{
using (var cmd = new SqliteCommand(String.Format("DELETE FROM __{0}", tableName), tran.Connection, tran))
cmd.ExecuteNonQuery();
}
using (var cmd = new SqliteCommand(String.Format("DELETE FROM {0}", TranStatusTable), tran.Connection, tran))
cmd.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
public bool Delete(Guid presentationUID)
{
bool ret = false;
if (presentationUID == Guid.Empty)
return false;
using (SqliteConnection conn = GetConnection())
{
using (SqliteCommand sqlCmd = new SqliteCommand(@"
BEGIN; " +
"DELETE FROM presentations " +
"WHERE PresentationUID=@PresentationUID;" +
"COMMIT;", conn))
{
sqlCmd.Parameters.AddWithValue("@PresentationUID", presentationUID.ToString());
conn.Open();
try
{
sqlCmd.ExecuteNonQuery();
ret = true;
}
catch (SqliteException ex)
{
Logging.Log(this, Logging.LoggingTypeError, "SQL cmd: " + sqlCmd, ex);
}
conn.Close();
}
}
return ret;
}
public void NonQuery( string cmdstr )
{
using (var cmd = new SqliteCommand( con )) {
cmd.CommandText = cmdstr;
cmd.ExecuteNonQuery();
}
}
public static List<string[]> ExecuteStringCommand(SqliteCommand command, int columncount)
{
try
{
var values = new List<string[]>();
SqliteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var row = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
row.Add(reader[i].ToString());
}
values.Add(row.ToArray());
}
reader.Close();
return values;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return new List<string[]>();
}
}
public static void Init(string databaseFullPath)
{
try
{
if (!File.Exists(databaseFullPath))
{
throw new Exception("Could not find the cards database.");
}
_cards = new Dictionary<int, NamedCard>();
using (SqliteConnection connection = new SqliteConnection("Data Source=" + databaseFullPath))
{
connection.Open();
using (IDbCommand command = new SqliteCommand(
"SELECT datas.id, ot, alias, setcode, type, level, race, attribute, atk, def, texts.name, texts.desc"
+ " FROM datas INNER JOIN texts ON datas.id = texts.id",
connection))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
LoadCard(reader);
}
}
}
}
}
catch (Exception ex)
{
throw new Exception("Could not initialize the cards database. Check the inner exception for more details.", ex);
}
}
public void UpdateOrders()
{
MainClass.StatusMessage("Получаем таблицу заказов...");
string sql = "SELECT orders.id, orders.customer, orders.contract, orders.address, orders.phone1, orders.phone2, orders.arrval, orders.deadline_s, orders.deadline_e FROM orders ";
SqliteCommand cmd = new SqliteCommand(sql, (SqliteConnection) QSMain.ConnectionDB);
using(SqliteDataReader rdr = cmd.ExecuteReader())
{
OrdersListStore.Clear();
while (rdr.Read())
{
OrdersListStore.AppendValues(rdr.GetInt32(rdr.GetOrdinal("id")),
rdr["customer"].ToString(),
rdr["contract"].ToString(),
rdr["phone1"].ToString() + rdr["phone2"].ToString(),
rdr["address"].ToString(),
String.Format("{0:d}", rdr["arrval"]),
DateWorks.GetDateRangeText(DBWorks.GetDateTime(rdr, "deadline_s", new DateTime()), DBWorks.GetDateTime(rdr, "deadline_e", new DateTime()))
);
}
}
MainClass.StatusMessage("Ok");
}
private void liqviscdata()
{
con.Open();
string stm = "SELECT * FROM windowsdata WHERE comp='"+comppicker.SelectedItem+"' ORDER BY comp ";
using (SqliteCommand cmd = new SqliteCommand(stm, con))
{
using (SqliteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
tcenti = double.Parse(temp.Text);
tk = tcenti + 273.15;
viscb =double.Parse( rdr["viscb"].ToString());
viscc = double.Parse(rdr["viscc"].ToString());
if (viscc != 0)
{
double visc1 = viscb * ((1 / tk) - (1 / viscc));
double viscocity = Math.Pow(10, visc1);
Liqvisc.Text = viscocity.ToString();
}
else
{
Liqvisc.Text = "0";
}
}
}
}
con.Close();
}
private void MakeSureTableExists()
{
using (SqliteCommand sqc = new SqliteCommand(connection)) {
sqc.CommandText = "CREATE TABLE IF NOT EXISTS `Files` (`id` INTEGER NOT NULL PRIMARY KEY, `path` TEXT NOT NULL, `changedate` INTEGER NOT NULL, `size` INTEGER NOT NULL, `hash` TEXT NOT NULL)";
sqc.ExecuteNonQuery();
}
}
private void _CreateHighscoreDBV1(string filePath)
{
using (var connection = new SQLiteConnection())
{
connection.ConnectionString = "Data Source=" + filePath;
try
{
connection.Open();
}
catch (Exception)
{
return;
}
using (var command = new SQLiteCommand(connection))
{
command.CommandText = "CREATE TABLE IF NOT EXISTS Version ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Value INTEGER NOT NULL);";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Version (id, Value) VALUES(NULL, 1 )";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS Songs ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Artist TEXT NOT NULL, Title TEXT NOT NULL, NumPlayed INTEGER);";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS Scores ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"SongID INTEGER NOT NULL, PlayerName TEXT NOT NULL, Score INTEGER NOT NULL, LineNr INTEGER NOT NULL, Date BIGINT NOT NULL, " +
"Medley INTEGER NOT NULL, Duet INTEGER NOT NULL, Difficulty INTEGER NOT NULL);";
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Write or update files and hashes.
/// </summary>
/// <param name="liste"></param>
public void WriteFiles(List<FileObject> liste)
{
using (SqliteTransaction sqt = connection.BeginTransaction()) {
using (SqliteCommand sqc = new SqliteCommand(connection)) {
// Alle vorhandenen Einträge löschen, da diese in jedem Fall überschrieben werden sollten
sqc.CommandText = "DELETE FROM Files WHERE path=:path";
sqc.Parameters.Add(new SqliteParameter("path"));
foreach (FileObject d in liste) {
sqc.Parameters["path"].Value = d.Filepath;
sqc.ExecuteNonQuery();
}
//sqc.Parameters.Clear(); // Nötig?
// Dateien eintragen
sqc.CommandText = "INSERT INTO Files (path, hash, size, changedate) VALUES (:path, :hash, :size, :changedate)";
sqc.Parameters.Add(new SqliteParameter("path"));
sqc.Parameters.Add(new SqliteParameter("hash"));
sqc.Parameters.Add(new SqliteParameter("size"));
sqc.Parameters.Add(new SqliteParameter("changedate"));
foreach (FileObject d in liste) {
sqc.Parameters["path"].Value = d.Filepath;
sqc.Parameters["hash"].Value = d.Hash;
sqc.Parameters["size"].Value = d.Size;
sqc.Parameters["changedate"].Value = d.ChangeDate;
sqc.ExecuteNonQuery();
}
}
sqt.Commit();
}
}
public static List<Dictionary<string, string>> QueryImpl(string handler, string statement, string[] param)
{
List<Dictionary<string,string>> result = new List<Dictionary<string,string>>();
var db = dbs[handler];
var dbcmd = new SqliteCommand(ConvertStatment(statement), db);
// Hard and ugly hack to use '?'
for (var i = 1; i <= param.Length; i++) {
dbcmd.Parameters.AddWithValue("@a" + i, param[i-1]);
}
IDataReader reader = dbcmd.ExecuteReader();
while(reader.Read())
{
Dictionary<string,string> row = new Dictionary<string,string>();
for (var i = 0; i < reader.FieldCount; i++) {
string val = reader.GetValue(i).ToString();
row.Add(reader.GetName(i),val);
}
result.Add(row);
}
reader.Dispose();
dbcmd.Dispose();
return result;
}
public override bool addWorker(string fname, string lname, string village, string hnr, int plz, string email, string mobile, string tel, string street)
{
try
{
sqlite_cmd = sqlite_conn.CreateCommand ();
sqlite_cmd.CommandText = "INSERT INTO tbl_worker (fname, lname, hnr, PLZ, village, email, mobile, telephone, street) VALUES ('"+fname+"', '"+lname+"','"+hnr+"','"+plz+"','"+village+"','"+email+"','"+mobile+"','"+tel+"','"+street+"')";
sqlite_conn.Open();
sqlite_cmd.ExecuteNonQuery();
sqlite_conn.Close();
int WorkerID = this.readWorkerID(fname,lname,village,hnr,email);
if(WorkerID != 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
sqlite_conn.Close ();
return false;
}
}
//Update from V0 (empty) to V1
private bool _UpdateV1()
{
if (_Connection == null || _Version >= 1)
return false;
using (SQLiteTransaction transaction = _Connection.BeginTransaction())
{
try
{
foreach (string file in _FilesV1)
{
string filePath = Path.Combine(CSettings.ProgramFolder, file);
if (!_AddImageToCreditsDB(filePath, transaction))
{
transaction.Rollback();
return false;
}
}
using (SQLiteCommand command = new SQLiteCommand(_Connection))
{
command.Transaction = transaction;
command.CommandText = "Update Version SET Value=@Value)";
command.Parameters.Add("@Value", DbType.Int32).Value = 1;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
return false;
}
}
return true;
}
public static int DoNonQuery (SqliteConnection connection, string command_text, string[] param_names, object[] param_args)
{
int ret = 0;
using (SqliteCommand command = new SqliteCommand ()) {
command.Connection = connection;
command.CommandText = command_text;
if (param_names != null) {
if (param_args == null || param_names.Length != param_args.Length)
throw new ArgumentException ("param_names, param_args", "param_names and param_args should have same number of items");
for (int i = 0; i < param_names.Length; ++i)
command.Parameters.AddWithValue (param_names [i], param_args [i]);
}
while (true) {
try {
ret = command.ExecuteNonQuery ();
break;
} catch (SqliteException e) {
if (e.ErrorCode == SQLiteErrorCode.Busy) {
Thread.Sleep (50);
} else {
throw;
}
} catch (Exception e) {
Log.Error (e, "SQL that caused the exception: {0}", command_text);
throw;
}
}
}
return ret;
}
public void DataCount(string table_name,out int count)
{
count=0;
DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = "SELECT COUNT(*) FROM " + table_name;
count = System.Convert.ToInt32(DbCommand.ExecuteScalar());
}
static List<Dictionary<string, object>> ExecuteQuery(string query, List<SqliteParameter> pars)
{
List<Dictionary<string, object>> res = new List<Dictionary<string, object>>();
using (var Conn = GetConnection())
{
Conn.Open();
using (SqliteCommand command = new SqliteCommand(query, Conn))
{
foreach (var par in pars)
command.Parameters.Add(par);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
res.Add(new Dictionary<string, object>());
for (int i = 0; i < reader.FieldCount; i++)
{
res[res.Count - 1][reader.GetName(i)] = reader[i];
}
}
}
}
}
}
return res;
}
public void RoomInfo(Socket UserSocket, SQLiteConnection connection, Dictionary <Socket, Character> clientDictonary)
{
ASCIIEncoding encoder = new ASCIIEncoding();
string returnMessage = "";
Character character = clientDictonary[UserSocket];
command = new sqliteCommand("select * from " + "table_characters" + " where name = " + "'" + character.name + "'", connection);
var characterSearch = command.ExecuteReader();
while (characterSearch.Read())
{
command = new sqliteCommand("select * from " + "table_dungeon" + " where name = " + "'" + characterSearch["room"] + "'", connection);
}
characterSearch.Close();
var dungeonSearch = command.ExecuteReader();
while (dungeonSearch.Read())
{
returnMessage += "-------------------------------";
returnMessage += "\nName: " + dungeonSearch["name"];
returnMessage += "\nDescription: " + dungeonSearch["description"];
returnMessage += "\nNorth: " + dungeonSearch["North"];
returnMessage += "\nSouth: " + dungeonSearch["South"];
returnMessage += "\nEast: " + dungeonSearch["East"];
returnMessage += "\nWest: " + dungeonSearch["West"];
returnMessage += "\nUp: " + dungeonSearch["Up"];
returnMessage += "\nDown: " + dungeonSearch["Down"];
returnMessage += "\n-------------------------------";
}
connection.Close();
byte[] sendbuffer = encoder.GetBytes(returnMessage);
int bytesSent = UserSocket.Send(sendbuffer);
//try
//{
// Console.WriteLine("");
// command = new sqliteCommand("select * from " + "table_dungeon" + " order by name asc", connection);
// var reader = command.ExecuteReader();
// while (reader.Read())
// {
// Console.WriteLine("Name: " + reader["name"]);
// }
// reader.Close();
// Console.WriteLine("");
//}
//catch (Exception ex)
//{
// Console.WriteLine("Failed to display DB" + ex);
//}
}
public void AddUser(string username)
{
string insertuser = "******"" + username + "\");";
//Console.WriteLine(insertuser);
cmd = dbConnection.CreateCommand();
cmd.CommandText = insertuser;
cmd.ExecuteNonQuery();
}
static void clientReceiveThread(object obj)
{
ReceiveThreadLaunchInfo receiveInfo = obj as ReceiveThreadLaunchInfo;
bool socketactive = true;
socketToCharacter.Add(receiveInfo.socket, receiveInfo.clientCharacter);
var sql = "insert into " + "table_characters" + " (name, room) values";
sql += "('" + receiveInfo.clientCharacter.name + "'";
sql += ",";
sql += "'" + receiveInfo.clientCharacter.playerRoom + "'";
sql += ")";
SQLiteCommand command = new sqliteCommand(sql, connection);
try
{
command.ExecuteNonQuery();
}
catch
{
Console.WriteLine("Failed to perform simple addition but still did it anyway.");
}
MudowRun.RoomInfo(receiveInfo.socket, connection, socketToCharacter);
while ((active == true) && (socketactive == true))
{
byte[] buffer = new byte[4094];
try
{
int result = receiveInfo.socket.Receive(buffer);
if (result > 0)
{
ASCIIEncoding encoder = new ASCIIEncoding();
lock (incommingMessages)
{
string message = encoder.GetString(buffer, 0, result);
if (receiveInfo.clientCharacter.PlayerLoginDetails(receiveInfo.userState, message, connection) == false)
{
MudowRun.Process(receiveInfo.clientCharacter, message, receiveInfo.socket, socketToCharacter, connection);
}
MudowRun.RoomInfo(receiveInfo.socket, connection, socketToCharacter);
}
}
}
catch (System.Exception ex)
{
socketactive = false;
}
}
}
//depending on what the client sent (1 or 2) add a username to databse or retreive a name from the database
static bool username(string Key, Socket client)
{
int bytesSent;
ASCIIEncoding encoder = new ASCIIEncoding();
sqliteCommand command;
var input = Key.Split(' ');
switch (input[0])
{
case "1":
for (int i = 1; i < input.Length; i++)
{
name += input[i];
}
try
{
var sql = "insert into " + "table_usernames" + " (name) values ";
sql += "('" + name + "'";
sql += ")";
command = new sqliteCommand(sql, conn);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Failed to add: " + name + " : to DB " + ex);
}
return(true);
case "2":
for (int i = 1; i < input.Length; i++)
{
name += input[i];
}
try
{
command = new sqliteCommand("select * from table_usernames where name == '" + name + "'", conn);
var reader = command.ExecuteReader();
if (reader.HasRows == true)
{
byte[] sendBuffer = encoder.GetBytes("signed in");
bytesSent = client.Send(sendBuffer);
}
}
catch (Exception ex)
{
Console.WriteLine("Failed to find: " + name + " : to DB " + ex);
}
return(true);
}
return(false);
}
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
DataTable dtArtists = new DataTable();
#region fetch data for artists
Mono.Data.Sqlite.SqliteConnection cn = new Mono.Data.Sqlite.SqliteConnection("library.sqlite");
Mono.Data.Sqlite.SqliteCommand comm = new Mono.Data.Sqlite.SqliteCommand(cn);
Mono.Data.Sqlite.SqliteDataAdapter adapter = new Mono.Data.Sqlite.SqliteDataAdapter(comm);
comm.CommandText = @"
SELECT name, id, fetched
FROM artists
";
adapter.Fill(dtArtists);
#endregion
if (dtArtists.Rows.Count == 0)
{
List <SubsonicItem> artists = Subsonic.GetIndexes();
foreach (SubsonicItem artist in artists)
{
DataRow dr = dtArtists.NewRow();
dr["name"] = artist.name;
dr["id"] = artist.id;
dr["feteched"] = DateTime.Now.ToString();
dtArtists.Rows.Add(dr);
comm = new Mono.Data.Sqlite.SqliteCommand(cn);
comm.CommandText = @"
INSERT INTO artists (name, id, fetched)
VALUES(@name, @id, @fetched);
";
comm.Parameters.AddWithValue("@name", artist.name);
comm.Parameters.AddWithValue("@id", artist.id);
comm.Parameters.AddWithValue("@fetched", DateTime.Now.ToString());
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
comm.ExecuteNonQuery();
}
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}
}
rptArtists.DataSource = dtArtists;
rptArtists.DataBind();
}
private bool _ConvertV2toV3(SQLiteConnection connection)
{
var command = new SQLiteCommand(connection)
{
CommandText = "ALTER TABLE Songs ADD DateAdded BIGINT"
};
command.ExecuteNonQuery();
command.CommandText = "UPDATE Songs SET [DateAdded] = @DateAdded";
command.Parameters.Add("@DateAdded", DbType.Int64, 0).Value = DateTime.Now.Ticks;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Version SET [Value] = @version";
command.Parameters.Add("@version", DbType.Int32, 0).Value = 3;
command.ExecuteNonQuery();
//Read NumPlayed from Scores and save to Songs
command.CommandText = "SELECT SongID, Date FROM Scores ORDER BY Date ASC";
SQLiteDataReader reader;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
return(false);
}
long lastDateAdded = -1;
int lastID = -1;
DateTime dt = new DateTime(1, 1, 1, 0, 0, 5);
long sec = dt.Ticks;
List <int> ids = new List <int>();
while (reader.Read())
{
int id = reader.GetInt32(0);
long dateAdded = reader.GetInt64(1);
if (id != lastID || dateAdded > lastDateAdded + sec)
{
ids.Add(id);
lastID = id;
lastDateAdded = dateAdded;
}
}
reader.Dispose();
foreach (int id in ids)
{
_IncreaseSongCounter(id, command);
}
command.Dispose();
return(true);
}
/*
* Sets the field with the int data for the entry where 'id' == id
*/
public void setFieldFromID(int id, int data, String field)
{
sqliteCommand command = new sqliteCommand("update " + m_TableName + " set " + field + " = @data where id = @id", m_Connection);
command.Parameters.Add("@id", System.Data.DbType.UInt32).Value = id;
command.Parameters.Add("@data", System.Data.DbType.UInt32).Value = data;
command.Parameters.Add("@field", System.Data.DbType.String).Value = field;
command.ExecuteNonQuery();
}
/*
* Sets the field with the String data for the entry where 'name' == name
*/
public void setFieldFromName(String name, String data, String field)
{
sqliteCommand command = new sqliteCommand("update " + m_TableName + " set " + field + " = @data where name = @name", m_Connection);
command.Parameters.Add("@data", System.Data.DbType.String).Value = data;
command.Parameters.Add("@name", System.Data.DbType.String).Value = name;
command.Parameters.Add("@field", System.Data.DbType.String).Value = field;
command.ExecuteNonQuery();
}
private bool _GetDataBaseSongInfos(int songID, out string artist, out string title, out int numPlayed, out DateTime dateAdded, string filePath)
{
artist = String.Empty;
title = String.Empty;
numPlayed = 0;
dateAdded = DateTime.Today;
using (var connection = new SQLiteConnection())
{
connection.ConnectionString = "Data Source=" + filePath;
try
{
connection.Open();
}
catch (Exception)
{
return(false);
}
using (var command = new SQLiteCommand(connection))
{
command.CommandText = "SELECT Artist, Title, NumPlayed, DateAdded FROM Songs WHERE [id] = @id";
command.Parameters.Add("@id", DbType.String, 0).Value = songID;
SQLiteDataReader reader;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
return(false);
}
if (reader != null && reader.HasRows)
{
reader.Read();
artist = reader.GetString(0);
title = reader.GetString(1);
numPlayed = reader.GetInt32(2);
dateAdded = new DateTime(reader.GetInt64(3));
reader.Dispose();
return(true);
}
if (reader != null)
{
reader.Dispose();
}
}
}
return(false);
}
public bool queryExists(String query, String field)
{
sqliteCommand command = new sqliteCommand("select * from " + m_TableName + " where " + field + " = @query", m_Connection);
command.Parameters.Add("@query", System.Data.DbType.String).Value = query;
command.Parameters.Add("@field", System.Data.DbType.String).Value = field;
sqliteDataReader reader = command.ExecuteReader();
return(reader.HasRows);
}
private int _GetDataBaseSongID(int songID, SQLiteCommand command)
{
CSong song = CSongs.GetSong(songID);
if (song == null)
{
return(-1);
}
return(_GetDataBaseSongID(song.Artist, song.Title, 0, command));
}
//sets players in room and where they can move to
public void SetClientInRoom(Socket client, String room)
{
if (socketToRoomLookup.ContainsKey(client) == false)
{
var command = new sqliteCommand("select * from table_rooms where name == '" + room + "'", conn);
var reader = command.ExecuteReader();
reader.Read();
Room currentRoom = new Room(reader["name"] as String, reader["desc"] as String);
socketToRoomLookup[client] = currentRoom;
}
}
public static bool SaveToDB(string dataSource, string completeQuery)
{
using (SqliteConnection con = new SqliteConnection())
{
con.ConnectionString = dataSource;
try { con.Open(); }
catch (Exception ex)
{
MakeLogErrorStatic(typeof(DBReader), ex);
if (con.State.ToString() == "Open")
{
con.Close();
con.Dispose();
}
return(false);
}
// security check and close connection if necessary
if (!DBSecurity.IsSecureSQLCommand(completeQuery))
{
MakeLogWarningStatic(typeof(DBReader),
"SaveToDB: Prevented forwarding of insecure sql-command: "
+ completeQuery);
return(false);
}
using (SQLiteCommand cmd = new SQLiteCommand(completeQuery, con))
{
cmd.CommandText = completeQuery;
SQLiteDataReader rdr = null;
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
throw new Exception("Could not execute SQLiteDataReader in SaveToDB: " + ex);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
}
}
return(true);
}
public override void update(ActionTimer timer)
{
using (SQLiteCommand command = new SQLiteCommand(Sqlite.getSqlite().connection))
{
command.CommandText = "SELECT name FROM `account` WHERE `id`=@id";
command.Parameters.AddWithValue("@id", this.accID);
using (SQLiteDataReader sdrITM = command.ExecuteReader())
{
if (sdrITM.HasRows)
{
sdrITM.Read();
this.name = Convert.ToString(sdrITM["name"]);
accExists = true;
}
}
}
if (accExists)
{
using (SQLiteCommand command = new SQLiteCommand(Accounts.Logs.SQLiteLogger.connection))
{
command.CommandText = "SELECT * FROM `logStats` WHERE `accountID`=@id";
command.Parameters.AddWithValue("@id", this.accID);
using (SQLiteDataReader sdrITM = command.ExecuteReader())
{
if (sdrITM.HasRows)
{
List <AccountLogChar> alcList = new List <AccountLogChar>();
while (sdrITM.Read())
{
AccountLogChar alc = new AccountLogChar();
alc.type = Convert.ToInt32(sdrITM["type"]);
alc.value = Convert.ToInt32(sdrITM["value"]);
alc.time = Convert.ToInt64(sdrITM["time"]);
alcList.Add(alc);
}
charStatList = alcList.ToArray();
}
}
}
}
isFinished = true;
timer.removeAction(this);
}
public void CreateStandardTables()
{
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = "CREATE TABLE IF NOT EXISTS `world_container` (";
command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
command.CommandText += " `name` text NOT NULL,";
command.CommandText += " `posx` REAL NOT NULL,";
command.CommandText += " `posy` REAL NOT NULL,";
command.CommandText += " `posz` REAL NOT NULL,";
command.CommandText += " `world` text NOT NULL,";
command.CommandText += " `opened` INTEGER NOT NULL";
command.CommandText += ")";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS `world_items` (";
command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
command.CommandText += " `iteminstance` text NOT NULL,";
command.CommandText += " `amount` INTEGER NOT NULL,";
command.CommandText += " `posx` REAL NOT NULL,";
command.CommandText += " `posy` REAL NOT NULL,";
command.CommandText += " `posz` REAL NOT NULL,";
command.CommandText += " `world` text NOT NULL";
command.CommandText += ")";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS `world_mobinteract` (";
command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
command.CommandText += " `vobType` INTEGER NOT NULL,";
command.CommandText += " `name` text NOT NULL,";
command.CommandText += " `posx` REAL NOT NULL,";
command.CommandText += " `posy` REAL NOT NULL,";
command.CommandText += " `posz` REAL NOT NULL,";
command.CommandText += " `world` text NOT NULL,";
command.CommandText += " `triggered` INTEGER NOT NULL";
command.CommandText += ")";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS `world_container_items` (";
command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
command.CommandText += " `containerid` INTEGER NOT NULL,";
command.CommandText += " `item` text NOT NULL,";
command.CommandText += " `amount` INTEGER NOT NULL";
command.CommandText += ")";
command.ExecuteNonQuery();
command.Dispose();
}
