public void AddPageVector(string link, Dictionary <string, double> dictionary)
{
object LID;
lock (LinkTableRead)
{
GetLinkIdCommand.Parameters.AddWithValue("link", link);
LID = GetLinkIdCommand.ExecuteScalar();
}
foreach (var word in dictionary)
{
lock (VectorTable)
{
if (Controller.OperationCancelled)
{
return;
}
//sql = "INSERT INTO VECTOR SELECT ID,'" + word.Key + "'," + word.Value + " FROM URL WHERE URL='" + link + "';";
//database.ExecuteNonQuery(sql);
AddPageVectorCommand.Parameters.AddWithValue("keyword", word.Key);
AddPageVectorCommand.Parameters.AddWithValue("keywordrank", word.Value);
AddPageVectorCommand.Parameters.AddWithValue("linkid", LID);
AddPageVectorCommand.ExecuteNonQuery();
}
}
}
private static void player_leaderboard(ChatMessage c)
{
long chan_id = get_channel_id(c.Channel);
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = @"SELECT nickname, points
FROM players
WHERE chan_id = @chanid
ORDER BY points DESC LIMIT 5";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@chanid", chan_id);
long pos = 1;
string list = "Leaderboard for #" + c.Channel + ": ";
using (System.Data.SQLite.SQLiteDataReader r = com.ExecuteReader())
{
while (r.Read())
{
list = list + " (" + pos + ") " + ((string)r["nickname"]).Trim() + " - " + r["points"] + ", ";
pos++;
}
}
// then tell the player their position
com.CommandText = @"SELECT count(*) AS rank
FROM players
WHERE chan_id = @chanid AND points > (SELECT points from players where nickname = @nickname)
ORDER BY points DESC";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@nickname", c.Username);
com.Parameters.AddWithValue("@chanid", chan_id);
object res = com.ExecuteScalar();
long rank = 0;
if (res != null)
{
rank = (long)res;
}
com.CommandText = @"SELECT count(*) from players where chan_id = @chanid";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@chanid", chan_id);
res = com.ExecuteScalar();
long total = 0;
if (res != null)
{
total = (long)res;
}
con.Close();
cl.SendWhisper(c.Username, list + " you are ranked " + (rank != 0?rank:total) + "/" + total);
}
verb("Leaderboard req from " + c.Username);
}
/// <summary>
/// Excludes the MXD.
/// </summary>
/// <param name="mxdfilepath">The mxdfilepath.</param>
/// <returns></returns>
private static bool ExcludeMXD(string mxdfilepath)
{
bool exclude = false;
if (!string.IsNullOrEmpty(ExcludeDBPath))
{
using (System.Data.SQLite.SQLiteConnection connection = new System.Data.SQLite.SQLiteConnection())
{
connection.ConnectionString = "Data Source=" + ExcludeDBPath + ";Version=3;";
connection.Open();
string sql = "select count(*) from exclude where excludeitem = '{0}'";
string path = mxdfilepath.Replace("'", "''");
using (System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(string.Format(sql, path), connection))
{
object result = command.ExecuteScalar();
if (result != null)
{
int recordcount = Convert.ToInt32(result);
exclude = recordcount > 0;
}
}
connection.Close();
}
}
return(exclude);
}
public bool CreateCurrency(string CurrencyName, decimal CurrencyFactor)
{
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(BaseDbContext.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = string.Format("Select 1 from Currencies where CurrencyName='{0}'", CurrencyName); // Add the first entry into our database
var exists = com.ExecuteScalar();
if (exists == null)
{
com.CommandText = string.Format("INSERT INTO Currencies(CurrencyName,CurrencyFactor) Values ('{0}','{1}')", CurrencyName, CurrencyFactor); // Add the first entry into our database
com.ExecuteNonQuery();
}
else
{
return(false);
}
return(true);
}
}
}
catch (Exception ex)
{
return(false);
}
}
public bool CreateParticularsSubType(string SubTypeName, int ParticularID)
{
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(BaseDbContext.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = string.Format("Select 1 from ParticularsSubType where SubTypeName='{0}' and ParticularID='{1}'", SubTypeName, ParticularID); // Add the first entry into our database
var exists = com.ExecuteScalar();
if (exists == null)
{
com.CommandText = string.Format("INSERT INTO ParticularsSubType (SubTypeName,ParticularID) Values ('{0}','{1}')", SubTypeName, ParticularID); // Add the first entry into our database
com.ExecuteNonQuery();
}
else
{
return(false);
}
return(true);
}
}
}
catch (Exception ex)
{
return(false);
}
}
public int MaxQuestionOrder(Category category)
{
int result = 0;
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
command.CommandText = @"SELECT MAX([order]) from questions WHERE categoryid=@categoryid";
SqliteParameter parameter = new SqliteParameter("@categoryid", DbType.Int32);
parameter.Value = category.Id;
command.Parameters.Add(parameter);
object val = command.ExecuteScalar();
if (val != DBNull.Value)
{
result = Convert.ToInt32(val);
}
}
}
}
catch (SqliteException ex)
{
Logger.Fatal("Unable to perform MaxQuestionOrder: \n{0}", ex);
}
return(result);
}
void DB.IDB.Insert(object obj)
{
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandTimeout = 8000;
//
string sql = "";
string fields = "";
string values = "";
foreach (System.Reflection.PropertyInfo p in obj.GetType().GetProperties())
{
if (fields == "")
{
fields += p.Name;
values += "@" + p.Name;
}
else
{
fields += "," + p.Name;
values += "," + "@" + p.Name;
}
}
sql = "insert into " + DB.ReflectionHelper.GetDataTableNameByModel(obj) + "(" + fields + ")values(" + values + ")";
cmd.CommandText = sql;
cmd.Parameters.AddRange(ModelToSqlParameters(obj));
//
cmd.ExecuteScalar();
}
public int SaveCategory(Category category)
{
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
bool updating = (category.Id > 0);
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
SqliteParameter parameter;
string sql = @"INSERT INTO categories (name,active) VALUES (@name,@active);SELECT last_insert_rowid();";
if (updating)
{
sql = @"UPDATE categories SET name=@name,active=@active WHERE id=@id";
parameter = new SqliteParameter("@id", DbType.Int32);
parameter.Value = category.Id;
command.Parameters.Add(parameter);
}
parameter = new SqliteParameter("@name", DbType.String);
parameter.Value = category.Name;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@active", DbType.Boolean);
parameter.Value = category.Active;
command.Parameters.Add(parameter);
command.CommandText = sql;
int result;
if (updating)
{
command.ExecuteNonQuery();
result = category.Id;
// If result isn't the categoryid, then ignore it
}
else
{
Int64 newId = (Int64)command.ExecuteScalar();
result = Convert.ToInt32(newId);
category.Id = result;
}
return(result);
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred with SaveCategory({0}): \n{1}", category.Id, e);
}
return(0);
}
private static bool IsExistingTable()
{
string sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'highscores'";
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(sql, m_dbConnection);
object obj = command.ExecuteScalar();
return(obj != null);
}
public bool CreateBU(string BUName, string BUDescription, List <Account> accounts)
{
try
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(BaseDbContext.databasestring))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = string.Format("Select 1 from BU where BUName='{0}'", BUName); // Add the first entry into our database
var exists = com.ExecuteScalar();
if (exists == null)
{
com.CommandText = string.Format("INSERT INTO BU (BUName,BUDescription) Values ('{0}','{1}')", BUName, BUDescription); // Add the first entry into our database
com.ExecuteNonQuery();
com.CommandText = string.Format("SELECT BUID FROM BU WHERE BUNAME='{0}'", BUName); // Add the first entry into our database
string buid = Convert.ToString(com.ExecuteScalar());
foreach (Account acc in accounts)
{
com.CommandText = string.Format("INSERT INTO BUAccountMapping (BUID,AccountID) Values ('{0}','{1}')", buid, acc.AccountID); // Add the first entry into our database
com.ExecuteNonQuery();
}
}
else
{
return(false);
}
return(true);
}
}
}
catch (Exception ex)
{
return(false);
}
}
private static long[] stats()
{
long playerCount = 0;
long roundCount = 0;
long guessCount = 0;
long channelCount = 0;
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select Count(*) from players";
playerCount = (long)com.ExecuteScalar();
com.CommandText = "Select Count(*) from rounds";
roundCount = (long)com.ExecuteScalar();
com.CommandText = "Select Count(*) from guesses";
guessCount = (long)com.ExecuteScalar();
com.CommandText = "Select Count(*) from channels";
channelCount = (long)com.ExecuteScalar();
con.Close();
}
return(new long[] { playerCount, roundCount, guessCount, channelCount });
}
static void Main(string[] args)
{
using (System.Data.SQLite.SQLiteConnection cn = new System.Data.SQLite.SQLiteConnection(connectionString))
{
cn.Open();
using (System.Data.SQLite.SQLiteCommand cm = new System.Data.SQLite.SQLiteCommand(cn))
{
cm.CommandText = "select count(*) from person where height<160";
cm.CommandType = System.Data.CommandType.Text;
object resultat = cm.ExecuteScalar();
Console.WriteLine($"Der er {resultat} under 160 i tabellen");
}
}
}
private void loadDB()
{
string connectionString = $@"Data Source={DBFileName}";
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connectionString);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand("select * from test limit 1");
cmd.Connection = conn;
conn.Open();
cmd.ExecuteScalar();
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds);
var table = ds.Tables[0];
minColWidth = new int[table.Columns.Count];
for (var colIndex = 0; colIndex < minColWidth.Length; colIndex++)
{
minColWidth[colIndex] = minWidth;
}
foreach (DataRow row in table.Rows)
{
for (var colIndex = 0; colIndex < minColWidth.Length; colIndex++)
{
minColWidth[colIndex] = Math.Max(minColWidth[colIndex], TextRenderer.MeasureText(row[colIndex].ToString(), gridControl1.Font).Width);
}
}
ReflectionDictionary dict = new ReflectionDictionary();
XpoDefault.Dictionary = dict;
XPClassInfo classInfo = new XPDataObjectClassInfo(dict, "Test", new Attribute[] { new OptimisticLockingAttribute(false), new DeferredDeletionAttribute(false) });
List <string> colNames = new List <string>();
foreach (DataColumn col in table.Columns)
{
colNames.Add(col.ColumnName);
classInfo.CreateMember(col.ColumnName, col.DataType);
}
classInfo.GetMember("id").AddAttribute(new KeyAttribute());
XPInstantFeedbackSource instantDS = new XPInstantFeedbackSource(classInfo);
instantDS.ResolveSession += instantDS_ResolveSession;
instantDS.DismissSession += instantDS_DismissSession;
gridView.Columns.Clear();
gridControl1.DataSource = instantDS;
gridControl1.Refresh();
btnBestFirColumns.PerformClick();
}
object DB.IDB.ExecuteScalar(string sql, System.Data.IDbDataParameter[] pars)
{
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandTimeout = 8000;
cmd.CommandText = sql;
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
//
return(cmd.ExecuteScalar());
}
public bool LinkExists(string link)
{
lock (LinkTable)
{
CheckLinkCountCommand.Parameters.AddWithValue("link", link);
object count = CheckLinkCountCommand.ExecuteScalar();
if (Convert.ToInt32(count) == 0)
{
return(false);
}
return(true);
}
}
/// Get a single value from database.
public static object ExecuteScalar(string sql, params System.Data.SQLite.SQLiteParameter[] arrayOfParameters)
{
CreateDatabaseFileIfNotExist();
using (System.Data.SQLite.SQLiteConnection cn = new System.Data.SQLite.SQLiteConnection(GetConnectionString()))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(sql, cn))
{
if ((arrayOfParameters?.Length ?? 0) > 0)
{
com.Parameters.AddRange(arrayOfParameters);
}
com.Connection.Open();
return(com.ExecuteScalar());
}
}
}
void DB.IDB.Update(object obj, string key_fields)
{
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandTimeout = 8000;
//
string sql = "";
string fields = "";
foreach (System.Reflection.PropertyInfo p in obj.GetType().GetProperties())
{
if (fields == "")
{
fields += p.Name + "=" + "@" + p.Name;
}
else
{
fields += "," + p.Name + "=" + "@" + p.Name;
}
}
string filter = "";
if (key_fields.Contains(",") == false)
{
filter = key_fields + "=" + "@" + key_fields;
}
else
{
foreach (string field in key_fields.Split(','))
{
if (filter == "")
{
filter += field + "=" + "@" + field;
}
else
{
filter += " and " + field + "=" + "@" + field;
}
}
}
sql = "update " + DB.ReflectionHelper.GetDataTableNameByModel(obj) + " set " + fields + " where " + filter;
cmd.CommandText = sql;
cmd.Parameters.AddRange(ModelToSqlParameters(obj));
//
cmd.ExecuteScalar();
}
/// <summary>
/// Returns a new valid ID value for the table.
/// </summary>
/// <param name="Table_Name">The name of the table to get the max ID from</param>
/// <returns>A new valid ID for the table</returns>
public int Get_New_Id(string Table_Name)
{
string ID_Name = Table_Name.Substring(0, Table_Name.Length - 1);
System.Data.SQLite.SQLiteCommand cmd = SQL_Connection.CreateCommand();
cmd.CommandText = "SELECT max(Ref" + ID_Name + ") FROM " + Table_Name;
object Id = cmd.ExecuteScalar();
if (Id != null && Id.ToString() != "")
{
return(int.Parse(Id.ToString()) + 1);
}
else
{
return(0);
}
}
/// <summary>
/// 执行结果集中的第一个数据的查询
/// </summary>
/// <param name="connection">数据库连接字符串</param>
/// <param name="sql">查询语句</param>
/// <returns>受影响的结果集中的第一个数据</returns>
public static object GetExecuteScalar(string connection, string sql)
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connection);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(sql, conn);
try
{
conn.Open();
return(cmd.ExecuteScalar());
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
}
}
private static long get_channel_id(string name)
{
long chan_id = -1;
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "SELECT ID FROM channels WHERE channel_name=@channel_name";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@channel_name", name);
Object res = com.ExecuteScalar();
if (res != null)
{
chan_id = (long)res;
}
con.Close();
}
return(chan_id);
}
public Int64 RowCount()
{
bool wasOpen = _isOpen;
if (!_isOpen)
{
Open();
}
Int64 result;
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand("SELECT Count(*) FROM '" + _tableName + "'", _connection))
{
result = (Int64)cmd.ExecuteScalar();
}
if (!wasOpen)
{
Close();
}
return(result);
}
/// <summary>
/// Returns the family id from the family name
/// </summary>
/// <param name="Familly_Name"></param>
/// <returns>the family id</returns>
public int Get_Familly_ID(string Familly_Name)
{
// TODO : check for typos
System.Data.SQLite.SQLiteCommand cmd = SQL_Connection.CreateCommand();
cmd.CommandText = "SELECT RefFamille FROM Familles WHERE Nom = ?";
System.Data.SQLite.SQLiteParameter Familly_Name_Parameter = new System.Data.SQLite.SQLiteParameter();
Familly_Name_Parameter.Value = Familly_Name;
cmd.Parameters.Add(Familly_Name_Parameter);
object Id = cmd.ExecuteScalar();
if (Id != null && Id.ToString() != "")
{
return(int.Parse(Id.ToString()));
}
else
{
return(-1);
}
}
/// <summary>
/// Returns the quantity from the article with the given id
/// </summary>
/// <param name="Article_Id"></param>
/// <returns>the quantity of the article</returns>
public int Count_Articles_Id(string Article_Id)
{
// TODO : check for typos
System.Data.SQLite.SQLiteCommand cmd = SQL_Connection.CreateCommand();
cmd.CommandText = "SELECT Quantite FROM Articles WHERE RefArticle = ?";
System.Data.SQLite.SQLiteParameter Article_Id_Parameter = new System.Data.SQLite.SQLiteParameter();
Article_Id_Parameter.Value = Article_Id;
cmd.Parameters.Add(Article_Id_Parameter);
object Id = cmd.ExecuteScalar();
if (Id != null && Id.ToString() != "")
{
return(int.Parse(Id.ToString()));
}
else
{
return(-1);
}
}
public string QuartzDbCheck()
{
string location = $"{SanitizePath()}databases/jobDb.db";
if (!File.Exists(location))
{
string query;
using (WebClient client = new WebClient()) {
query = client.DownloadString(
"https://raw.githubusercontent.com/vigetious/quartznet/main/database/tables/tables_sqlite.sql");
}
using (var connection = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + location)) {
connection.Open();
using (var command = new System.Data.SQLite.SQLiteCommand(query, connection)) {
command.ExecuteScalar();
}
}
}
return(location);
}
private static void player_points(ChatMessage c)
{
long playerPoints = 0;
long chan_id = get_channel_id(c.Channel);
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "SELECT points FROM players WHERE nickname = @nickname AND chan_id = @chanid";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@nickname", c.Username);
com.Parameters.AddWithValue("@chanid", chan_id);
object res = com.ExecuteScalar();
if (res != null)
{
playerPoints = (long)res;
}
con.Close();
}
cl.SendWhisper(c.Username, "You have " + playerPoints + " points in #" + c.Channel.Trim() + ".");
verb("Points req from " + c.Username);
}
//查询首行首列
public void QueryObject()
{
int id = 1;
var __QueryObject_Common_con__ = new System.Data.SQLite.SQLiteConnection(Models.ConnectionStrings.Common);
__QueryObject_Common_con__.Open();
#region var count; 选取首行首列的值
var __QueryObject_count_command__ = new System.Data.SQLite.SQLiteCommand("select count(*) from users where id=@id", __QueryObject_Common_con__);
var __QueryObject_count_parameters__ = new System.Data.SQLite.SQLiteParameter[1];
__QueryObject_count_parameters__[1 - 1] = new System.Data.SQLite.SQLiteParameter("@id", System.Data.DbType.Int64, 8);
__QueryObject_count_parameters__[1 - 1].Value = id;
__QueryObject_count_command__.Parameters.AddRange(__QueryObject_count_parameters__);
var count = new NFinal.DB.SqlObject(__QueryObject_count_command__.ExecuteScalar()).ToInt();
#endregion
__QueryObject_Common_con__.Close();
Write(count);
}
public int MaxQuestionOrder(Category category)
{
int result = 0;
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
command.CommandText = @"SELECT MAX([order]) from questions WHERE categoryid=@categoryid";
SqliteParameter parameter = new SqliteParameter("@categoryid", DbType.Int32);
parameter.Value = category.Id;
command.Parameters.Add(parameter);
object val = command.ExecuteScalar();
if (val != DBNull.Value)
result = Convert.ToInt32(val);
}
}
}
catch (SqliteException ex)
{
Logger.Fatal("Unable to perform MaxQuestionOrder: \n{0}", ex);
}
return result;
}
/// <summary>
/// writes the current database information in DBInfo object
/// </summary>
/// <returns>a DbInfo object</returns>
private DBInfo getDBInfo()
{
DBInfo dbInfo = new DBInfo();
byte dbPercentage = 0;
try
{
CLogger.WriteLog(ELogLevel.DEBUG, "getDBUsage Reading Info from Registry");
Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(regDir);
if (key != null)
{
if (key.GetValue(regMaxDBSize) != null)
{
maxDBSize = Convert.ToInt64(key.GetValue(regMaxDBSize).ToString());
}
key.Close();
}
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(dbPath);
conn.Open();
int page_count = 0, page_size = 0;
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand("PRAGMA page_count;", conn))
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
page_count = Convert.ToInt32(obj.ToString());
}
cmd.CommandText = "PRAGMA page_size;";
obj = cmd.ExecuteScalar();
if (obj != null)
{
page_size = Convert.ToInt32(obj.ToString());
}
}
long temp = (page_count * page_size)/((maxDBSize / 100));
dbInfo.dbPercentage = (byte)temp;
dbInfo.getDBSpaceLeft=maxDBSize-(page_count * page_size);
CLogger.WriteLog(ELogLevel.DEBUG, "getDBUsage() THe page count is " + page_count + " and the page size is " + page_size
+ "\n DB Size:" + (page_count * page_size));
}
catch (Exception e){
CLogger.WriteLog(ELogLevel.DEBUG, "getDBUsage Exception while reading from reg " + e.Message);
}
// return dbPercentage;
return dbInfo;
}
public int SaveQuestion(Question question)
{
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
bool updating = (question.Id > 0);
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
SqliteParameter parameter;
string sql = @"INSERT INTO questions (answer,askcount,categoryid,easinessfactor,interval,lastasked,nextaskon,[order],previousinterval,responsequality,title) ";
sql += "VALUES (@answer,@askcount,@categoryid,@easinessfactor,@interval,@lastasked,@nextaskon,@order,@previousinterval,@responsequality,@title);SELECT last_insert_rowid();";
if (updating)
{
sql = @"UPDATE questions SET answer=@answer,askcount=@askcount,categoryid=@categoryid,easinessfactor=@easinessfactor,interval=@interval,";
sql += "lastasked=@lastasked,nextaskon=@nextaskon,[order]=@order,previousinterval=@previousinterval,responsequality=@responsequality,title=@title ";
sql += "WHERE id=@id";
parameter = new SqliteParameter("@id", DbType.Int32);
parameter.Value = question.Id;
command.Parameters.Add(parameter);
}
parameter = new SqliteParameter("@title", DbType.String);
parameter.Value = question.Title;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@answer", DbType.String);
parameter.Value = question.Answer;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@askcount", DbType.Int32);
parameter.Value = question.AskCount;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@categoryid", DbType.Int32);
parameter.Value = question.Category.Id;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@easinessfactor", DbType.Double);
parameter.Value = question.EasinessFactor;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@interval", DbType.Int32);
parameter.Value = question.Interval;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@lastasked", DbType.Int64);
parameter.Value = question.LastAsked.Ticks;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@nextaskon", DbType.Int64);
parameter.Value = question.NextAskOn.Ticks;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@order", DbType.Int32);
if (updating)
{
parameter.Value = question.Order;
}
else
{
parameter.Value = MaxQuestionOrder(question.Category) +1;
}
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@previousinterval", DbType.Int32);
parameter.Value = question.PreviousInterval;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@responsequality", DbType.Int32);
parameter.Value = question.ResponseQuality;
command.Parameters.Add(parameter);
command.CommandText = sql;
int result;
if (updating)
{
command.ExecuteNonQuery();
result = question.Id;
}
else
{
Int64 newId = (Int64)command.ExecuteScalar();
result = Convert.ToInt32(newId);
}
return result;
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred with SaveQuestion({0}): \n{1}", question.Id, e);
}
return 0;
}
public int SaveCategory(Category category)
{
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
bool updating = (category.Id > 0);
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
SqliteParameter parameter;
string sql = @"INSERT INTO categories (name,active) VALUES (@name,@active);SELECT last_insert_rowid();";
if (updating)
{
sql = @"UPDATE categories SET name=@name,active=@active WHERE id=@id";
parameter = new SqliteParameter("@id", DbType.Int32);
parameter.Value = category.Id;
command.Parameters.Add(parameter);
}
parameter = new SqliteParameter("@name", DbType.String);
parameter.Value = category.Name;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@active", DbType.Boolean);
parameter.Value = category.Active;
command.Parameters.Add(parameter);
command.CommandText = sql;
int result;
if (updating)
{
command.ExecuteNonQuery();
result = category.Id;
// If result isn't the categoryid, then ignore it
}
else
{
Int64 newId = (Int64)command.ExecuteScalar();
result = Convert.ToInt32(newId);
category.Id = result;
}
return result;
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred with SaveCategory({0}): \n{1}", category.Id, e);
}
return 0;
}
public int SaveQuestion(Question question)
{
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
bool updating = (question.Id > 0);
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
SqliteParameter parameter;
string sql = @"INSERT INTO questions (answer,askcount,categoryid,easinessfactor,interval,lastasked,nextaskon,[order],previousinterval,responsequality,title) ";
sql += "VALUES (@answer,@askcount,@categoryid,@easinessfactor,@interval,@lastasked,@nextaskon,@order,@previousinterval,@responsequality,@title);SELECT last_insert_rowid();";
if (updating)
{
sql = @"UPDATE questions SET answer=@answer,askcount=@askcount,categoryid=@categoryid,easinessfactor=@easinessfactor,interval=@interval,";
sql += "lastasked=@lastasked,nextaskon=@nextaskon,[order]=@order,previousinterval=@previousinterval,responsequality=@responsequality,title=@title ";
sql += "WHERE id=@id";
parameter = new SqliteParameter("@id", DbType.Int32);
parameter.Value = question.Id;
command.Parameters.Add(parameter);
}
parameter = new SqliteParameter("@title", DbType.String);
parameter.Value = question.Title;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@answer", DbType.String);
parameter.Value = question.Answer;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@askcount", DbType.Int32);
parameter.Value = question.AskCount;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@categoryid", DbType.Int32);
parameter.Value = question.Category.Id;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@easinessfactor", DbType.Double);
parameter.Value = question.EasinessFactor;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@interval", DbType.Int32);
parameter.Value = question.Interval;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@lastasked", DbType.Int64);
parameter.Value = question.LastAsked.Ticks;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@nextaskon", DbType.Int64);
parameter.Value = question.NextAskOn.Ticks;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@order", DbType.Int32);
if (updating)
{
parameter.Value = question.Order;
}
else
{
parameter.Value = MaxQuestionOrder(question.Category) + 1;
}
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@previousinterval", DbType.Int32);
parameter.Value = question.PreviousInterval;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@responsequality", DbType.Int32);
parameter.Value = question.ResponseQuality;
command.Parameters.Add(parameter);
command.CommandText = sql;
int result;
if (updating)
{
command.ExecuteNonQuery();
result = question.Id;
}
else
{
Int64 newId = (Int64)command.ExecuteScalar();
result = Convert.ToInt32(newId);
}
return(result);
}
}
}
catch (SqliteException e)
{
Logger.Warn("SqliteException occurred with SaveQuestion({0}): \n{1}", question.Id, e);
}
return(0);
}
private void btnCreate_Click(object sender, EventArgs e)
{
if (hapDataView1.SelectedItem == null)
return;
string output = GenerateSQLCommand(tblPanelService);
using (var cmd = new System.Data.SQLite.SQLiteCommand(DataBase.Instance.sqlConnection))
{
cmd.CommandText = "insert into DebitNotes(OwnerId,Category,Date,Services,Advances,Tax,Total) values(@OwnerId,@Category,DateTime('now','localtime'),@Services,@Advances,@Tax,@Total); Select last_insert_rowid()";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[6];
parameters[0] = new System.Data.SQLite.SQLiteParameter("OwnerId", ((Company)hapDataView1.SelectedItem).Id);
parameters[1] = new System.Data.SQLite.SQLiteParameter("Category", cmb_Category.Text);
parameters[2] = new System.Data.SQLite.SQLiteParameter("Services", GenerateSQLCommand(tblPanelService));
parameters[3] = new System.Data.SQLite.SQLiteParameter("Advances", GenerateSQLCommand(tblPanelAdvance));
parameters[4] = new System.Data.SQLite.SQLiteParameter("Tax", tax);
parameters[5] = new System.Data.SQLite.SQLiteParameter("Total", total);
cmd.Parameters.AddRange(parameters);
long insertedId = (long)cmd.ExecuteScalar();
}
Form_Main.Instance.ReloadDebitNotes();
}
private static void award_points(string user_id, long guess, string endtime, long place)
{
long new_points = 0;
round_awarded++;
switch (place)
{
case 0:
new_points = 500; // exact guess
break;
case 1:
new_points = 50; // first
break;
case 2:
new_points = 15; // second
break;
case 3:
new_points = 5; // third
break;
}
string player_name = "<unknown>";
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
// add points to the user
com.CommandText = "UPDATE players SET points = points + @new_points WHERE id = @id";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@id", user_id);
com.Parameters.AddWithValue("@new_points", new_points);
com.ExecuteNonQuery();
// and get their name
com.CommandText = "SELECT nickname FROM players WHERE id = @id";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@id", user_id);
object res = com.ExecuteScalar();
if (res != null)
{
player_name = (string)res;
}
}
// notify the channel
switch (place)
{
case 0:
cl.SendMessage(player_name + " guessed exactly, and wins " + new_points + " points!");
break;
case 1:
cl.SendMessage(player_name + " was the closest, and wins " + new_points + " points!");
break;
case 2:
cl.SendMessage(player_name + " came in second and earns " + new_points + " points.");
break;
case 3:
cl.SendMessage(player_name + " had the third best guess, earning " + new_points + " points.");
break;
}
}
private static void round_guess(ChatMessage c)
{
verb("guess from " + c.Username);
// check to make sure the game hasn't been running too long
if (Math.Abs(DateTime.Now.ToFileTimeUtc() - round_started_time) > 45 * 10000000L)
{
cl.SendWhisper(c.Username, "Sorry, it's been more than 45 seconds. Try next round!");
if (round_more_guesses)
{
round_more_guesses = false;
cl.SendMessage("Guessing is now over, please wait until the next round.");
}
return;
}
round_more_guesses = true;
string guess = new string(c.Message.Where(Char.IsDigit).ToArray()); // linq magic to extract any leading/trailing chars
string user = c.Username;
long chan_id = get_channel_id(c.Channel);
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
// we track players based on their first guess
com.CommandText = "INSERT OR IGNORE INTO players (nickname, chan_id) VALUES (@nickname, @chanid)";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@nickname", c.Username);
com.Parameters.AddWithValue("@chanid", chan_id);
com.ExecuteNonQuery();
con.Close();
con.Open();
// get the userid for this nickname
com.CommandText = "SELECT id FROM players WHERE nickname = @nickname AND chan_id = @chanid";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@nickname", c.Username);
com.Parameters.AddWithValue("@chanid", chan_id);
Object res = com.ExecuteScalar();
long userId = -1;
if (res != null)
{
userId = (long)com.ExecuteScalar();
}
else
{
verb("Problem with guess from " + c.Username + ". Couldn't find id?");
con.Close();
return;
}
// This is a goofy sqlite upsert
com.CommandText = @"UPDATE OR IGNORE guesses
SET time=@guess, t=CURRENT_TIMESTAMP
WHERE user_id=@user_id AND round_id=@round_id AND chan_id=@chanid";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@guess", guess);
com.Parameters.AddWithValue("@user_id", userId);
com.Parameters.AddWithValue("@round_id", round_id);
com.Parameters.AddWithValue("@chanid", chan_id);
com.ExecuteNonQuery();
com.CommandText = "INSERT OR IGNORE INTO guesses (time, user_id, round_id, chan_id) VALUES (@guess, @user_id, @round_id, @chanid)";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@guess", guess);
com.Parameters.AddWithValue("@user_id", userId);
com.Parameters.AddWithValue("@round_id", round_id);
com.Parameters.AddWithValue("@chanid", chan_id);
com.ExecuteNonQuery();
con.Close();
}
}
private static void KML2SQLDO(string filename)
{
Console.WriteLine("Importing file \"{0}\"...", Path.GetFileName(filename));
string filePrefix = Transliteration.Front(Path.GetFileName(filename).ToUpper().Substring(0, 2));
System.Data.SQLite.SQLiteConnection sqlc = new System.Data.SQLite.SQLiteConnection(String.Format("Data Source={0};Version=3;", OruxPalsServerConfig.GetCurrentDir() + @"\StaticObjects.db"));
sqlc.Open();
{
System.Data.SQLite.SQLiteCommand sc = new System.Data.SQLite.SQLiteCommand("SELECT MAX(IMPORTNO) FROM OBJECTS", sqlc);
int import = 1;
try { string a = sc.ExecuteScalar().ToString(); int.TryParse(a, out import); import++; }
catch { };
string importTemplate = "insert into OBJECTS (LAT,LON,SYMBOL,[NAME],COMMENT,IMPORTNO,SOURCE) VALUES ({0},{1},'{2}','{3}','{4}',{5},'{6}')";
XmlDocument xd = new XmlDocument();
using (XmlTextReader tr = new XmlTextReader(filename))
{
tr.Namespaces = false;
xd.Load(tr);
};
string defSymbol = "\\C";
XmlNode NodeSymbol = xd.SelectSingleNode("/kml/symbol");
if (NodeSymbol != null)
{
defSymbol = NodeSymbol.ChildNodes[0].Value;
}
string defFormat = "R{0:000}-{1}"; // {0} - id; {1} - file prefix; {2} - Placemark Name without spaces
XmlNode NodeFormat = xd.SelectSingleNode("/kml/format");
if (NodeFormat != null)
{
defFormat = NodeFormat.ChildNodes[0].Value;
}
XmlNodeList nl = xd.GetElementsByTagName("Placemark");
List <PreloadedObject> fromKML = new List <PreloadedObject>();
if (nl.Count > 0)
{
for (int i = 0; i < nl.Count; i++)
{
string pName = System.Security.SecurityElement.Escape(Transliteration.Front(nl[i].SelectSingleNode("name").ChildNodes[0].Value));
pName = Regex.Replace(pName, "[\r\n\\(\\)\\[\\]\\{\\}\\^\\$\\&\']+", "");
string pName2 = Regex.Replace(pName.ToUpper(), "[^A-Z0-9\\-]+", "");
string symbol = defSymbol;
if (nl[i].SelectSingleNode("symbol") != null)
{
symbol = nl[i].SelectSingleNode("symbol").ChildNodes[0].Value.Trim();
}
if (nl[i].SelectSingleNode("Point/coordinates") != null)
{
string pPos = nl[i].SelectSingleNode("Point/coordinates").ChildNodes[0].Value.Trim();
string[] xyz = pPos.Split(new char[] { ',' }, 3);
sc.CommandText = String.Format(importTemplate, new object[] {
xyz[1], xyz[0], symbol.Replace("'", @"''"),
String.Format(defFormat, i + 1, filePrefix, pName2),
pName, import, Path.GetFileName(filename)
});
sc.ExecuteScalar();
}
;
Console.Write("Import Placemark {0}/{1}", i + 1, nl.Count);
Console.SetCursorPosition(0, Console.CursorTop);
}
}
;
Console.WriteLine();
Console.WriteLine("Done");
};
sqlc.Close();
}
private void btnConfirm_Click(object sender, EventArgs e)
{
if (txtCompanyName.Text == string.Empty)
{
txtCompanyName.Focus();
return;
}
//Submit all changes
if (currentState == AddressBookState.Add)
{
using (var cmd = new System.Data.SQLite.SQLiteCommand(DataBase.Instance.sqlConnection))
{
cmd.CommandText = "insert into AddressBook(CompanyName,President,Contact,Tel,Fax,Cellphone,Address1,Address2,SerialNumber,State,PhoneFormatId) values(@CompanyName,@President,@Contact,@Tel,@Fax,@Cellphone,@Address1,@Address2,@SerialNumber,@State,@PhoneFormatId); Select last_insert_rowid()";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[11];
parameters[0] = new System.Data.SQLite.SQLiteParameter("CompanyName", txtCompanyName.Text);
parameters[1] = new System.Data.SQLite.SQLiteParameter("President", txtPresident.Text);
parameters[2] = new System.Data.SQLite.SQLiteParameter("Contact", txtContact.Text);
parameters[3] = new System.Data.SQLite.SQLiteParameter("Tel", mtxtTel.Text);
parameters[4] = new System.Data.SQLite.SQLiteParameter("Fax", mtxtFax.Text);
parameters[5] = new System.Data.SQLite.SQLiteParameter("Cellphone", mtxtCellphone.Text);
parameters[6] = new System.Data.SQLite.SQLiteParameter("Address1", txtAddress1.Text);
parameters[7] = new System.Data.SQLite.SQLiteParameter("Address2", txtAddress2.Text);
parameters[8] = new System.Data.SQLite.SQLiteParameter("SerialNumber", mtxtSerialNumber.Text);
parameters[9] = new System.Data.SQLite.SQLiteParameter("State", cbbState.SelectedText);
parameters[10] = new System.Data.SQLite.SQLiteParameter("PhoneFormatId", cbbPhoneFormat.SelectedIndex);
cmd.Parameters.AddRange(parameters);
long insertedId = (long)cmd.ExecuteScalar();
CurrentCompany.Id = (int)insertedId;
}
}
else if (currentState == AddressBookState.Modify)
{
using (var cmd = new System.Data.SQLite.SQLiteCommand(DataBase.Instance.sqlConnection))
{
cmd.Parameters.Add(cmd.CreateParameter());
cmd.CommandText = "update AddressBook set CompanyName = @CompanyName,President = @President,Contact = @Contact,Tel = @Tel,Fax = @Fax,Cellphone = @Cellphone,Address1 = @Address1,Address2 = @Address2, SerialNumber = @SerialNumber, State = @State, PhoneFormatId = @PhoneFormatId where Id = @Id";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[12];
parameters[0] = new System.Data.SQLite.SQLiteParameter("CompanyName", txtCompanyName.Text);
parameters[1] = new System.Data.SQLite.SQLiteParameter("President", txtPresident.Text);
parameters[2] = new System.Data.SQLite.SQLiteParameter("Contact", txtContact.Text);
parameters[3] = new System.Data.SQLite.SQLiteParameter("Tel", mtxtTel.Text);
parameters[4] = new System.Data.SQLite.SQLiteParameter("Fax", mtxtFax.Text);
parameters[5] = new System.Data.SQLite.SQLiteParameter("Cellphone", mtxtCellphone.Text);
parameters[6] = new System.Data.SQLite.SQLiteParameter("Address1", txtAddress1.Text);
parameters[7] = new System.Data.SQLite.SQLiteParameter("Address2", txtAddress2.Text);
parameters[8] = new System.Data.SQLite.SQLiteParameter("Id", CurrentCompany.Id);
parameters[9] = new System.Data.SQLite.SQLiteParameter("SerialNumber", mtxtSerialNumber.Text);
parameters[10] = new System.Data.SQLite.SQLiteParameter("State", cbbState.Text);
parameters[11] = new System.Data.SQLite.SQLiteParameter("PhoneFormatId", cbbPhoneFormat.SelectedIndex);
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
}
}
currentState = AddressBookState.View;
companies.ResetItem(customControl1.SelectedIndex);
UpdateControls();
}