public void UpdateDatabaseWithArteProps( string ConnectionString )
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection( ConnectionString );
conn.Open();
System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction();
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand( conn );
command.Transaction = transaction;
foreach ( var a in ArteList ) {
string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateNames );
command.CommandText = UpdateNames;
command.ExecuteNonQuery();
string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateDescs );
command.CommandText = UpdateDescs;
command.ExecuteNonQuery();
if ( a.Type == Arte.ArteType.Generic ) {
string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + Util.DateTimeToUnixTime( DateTime.UtcNow ) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateStatus );
command.CommandText = UpdateStatus;
command.ExecuteNonQuery();
}
}
command.Dispose();
transaction.Commit();
conn.Close();
conn.Dispose();
}
static void Main1(string[] args)
{
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
Console.WriteLine(connstr.ToString());
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES('dotnetthink','mypassword')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n").Append("password:").Append(reader.GetString(1));
}
Console.WriteLine(sb.ToString());
}
public DatabaseManager()
{
string createTableQuery = @"CREATE TABLE IF NOT EXISTS LASTPROJECTS (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[address] VARCHAR(2048) NULL
)";
string path = Path.
GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
path = path + @"\bin\Debug\databaseFile.db3";
if(!File.Exists (path))
System.Data.SQLite.SQLiteConnection.CreateFile("databaseFile.db3");
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = createTableQuery;
com.ExecuteNonQuery();
con.Close();
}
}
Console.WriteLine("ALLO");
}
private Connection()
{
if (!System.IO.File.Exists(DB_FILE))
{
System.Data.SQLite.SQLiteConnection.CreateFile(DB_FILE);
}
sqliteConnection = new System.Data.SQLite.SQLiteConnection($"Data Source={DB_FILE};Version=3;Password={PASSWORD};");
sqliteConnection.Open();
var sql = "CREATE TABLE torrents (tag VARCHAR(20), url VARCHAR(255), name VARCHAR(255), added DATETIME)";
var command = new System.Data.SQLite.SQLiteCommand(sql, sqliteConnection);
command.ExecuteNonQuery();
}
static void Main(string[] args)
{
// get remote records
//GetUnclaimedBugs();
// compare datatime with DB
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connStr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connStr.DataSource = datasource;
conn.ConnectionString = connStr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "Create table test(username varchar(20), password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = "Insert into test values('dontnetthiink', 'passwd')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "select * from test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while(reader.Read())
{
sb.Append("username: "******"\n")
.Append("passwd:").Append(reader.GetString(1));
}
Console.WriteLine(sb.ToString());
// insert latest records
}
static void ExecuteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "sample.sqlite");
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Memberではなく、Members)
var sql = "CREATE TABLE Members (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Address TEXT, TelNo TEXT); ";
sql += "CREATE TABLE Items (Id INTEGER PRIMARY KEY AUTOINCREMENT, Price INTEGER, MemberId INTEGER, Name TEXT, SoldAt datetime, FOREIGN KEY(MemberId) REFERENCES Members(Id))";
var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
protected int ExecSQL(string SQL, System.Data.SQLite.SQLiteTransaction odTrans, System.Data.DbType[] ParaType, object[] ParaValue)
{
int re = -1;
ConnectionState OldConnState = odc.State;
try
{
if (odc.State == ConnectionState.Closed) odc.Open();
odCommExec = new System.Data.SQLite.SQLiteCommand(SQL, odc);
odCommExec.CommandTimeout = 60 * 60 * 24;
odCommExec.Transaction = odTrans;
if (ParaType != null)
{
for (int i = 0; i < ParaType.Length; i++)
{
odCommExec.Parameters.Add("Para" + i, ParaType[i]).Value = ParaValue[i];
}
}
re = odCommExec.ExecuteNonQuery();
}
catch (Exception ex)
{
CCLog.WriteLog(ex);
throw ex;
}
finally
{
if (OldConnState == ConnectionState.Closed) odc.Close();
}
return re;
}
private void button1_Click(object sender, EventArgs e)
{
//创建一个数据库文件
//’dotnetthink','mypassword'
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES('dotnetthink', 'mypassword')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
private void button1_Click_1(object sender, EventArgs e)
{
bool edadp = textBox2.Text.All(Char.IsNumber);
float pesov;
bool pesop = float.TryParse(textBox6.Text, out pesov);
if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && textBox6.Text != "" && textBox7.Text != "" && textBox8.Text != "" && textBox9.Text != "" && textBox10.Text != "" && textBox11.Text != "" && textBox12.Text != "" && textBox13.Text != "" && textBox14.Text != "" && textBox15.Text != "" && textBox16.Text != "" && textBox17.Text != "" && textBox18.Text != "" && textBox19.Text != "" && textBox20.Text != "" && textBox21.Text != "" && textBox22.Text != "")
{
if (edadp == true)
{
if (pesop == true)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "UPDATE Expediente Set Nombre = '" + textBox1.Text + "' , Sexo = '" + comboBox1.Text + "', Edad = '" + textBox2.Text + "', Ocupacion = '" + textBox4.Text + "', Estadocivil = '" + comboBox2.Text + "', Religion = '" + textBox3.Text + "', TA = '" + textBox5.Text + "', Peso = '" + textBox6.Text + "', Tema = '" + textBox7.Text + "',FC = '" + textBox8.Text + "', FR = '" + textBox9.Text + "', EnfermedadesFamiliares = '" + textBox10.Text + "', AreaAfectada = '" + comboBox3.Text + "', Antecedentes = '" + textBox11.Text + "', Habitos = '"+textBox13.Text+"', GPAC = '"+comboBox4.Text+"', FUMFUP = '"+comboBox5.Text+"', Motivo = '"+textBox14.Text+"', CuadroClinico = '"+textBox15.Text+"', ID = '"+textBox16.Text+"', EstudiosSolicitados = '"+textBox17.Text+"', TX = '"+textBox18.Text+"', PX = '"+textBox19.Text+"', Doctor = '"+textBox20.Text+"', CP = '"+textBox21.Text+"', SSA = '"+textBox22.Text+"' Where Folio =" + foliom + "";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
this.Close();
}
else
{
MessageBox.Show("Solo introduzca numeros en el peso", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Solo introduzca numeros en la edad", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Ha dejado campos en blanco", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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();
}
public static bool AddCardexTokens(string uri, string method, int status, string statusMsg, string ip, string token)
{
bool bolR;
try
{
Create_db();
bolR = false;
var lastaccess = DateTime.UtcNow;
var tk = new Token_Extracted_Data();
tk = ExtractTokenData(token);
string jti = tk.jti;
string userid = tk.userid.ToString();
string username = tk.username;
string role = tk.role;
string email = tk.email;
ulong nbf = Conversions.ToULong(tk.nbf);
ulong iat = Conversions.ToULong(tk.iat);
ulong exp = Conversions.ToULong(tk.exp);
string iss = tk.iss;
string aud = tk.aud;
string nbf_date = tk.nbf_date;
string iat_date = tk.iat_date;
string exp_date = tk.exp_date;
string strSQLQuery = @"INSERT INTO validations
(requestUri, method, status, statusMsg, ipaddr, userid, username, role, email, nbf_date, iat_date, exp_date, iss, aud, nbf, iat, exp, jti, token)
VALUES
(@requestUri, @method, @status, @statusMsg, @ipaddr, @userid, @username, @role, @email, @nbf_date, @iat_date, @exp_date, @iss, @aud, @nbf, @iat, @exp, @jti, @token);
UPDATE users SET lastaccess = @lastaccess, laststatus = @status, lastipaddr = @ipaddr WHERE id = @userid;";
using (var connection = new System.Data.SQLite.SQLiteConnection(mStrSQLiteConnString))
{
using (var command = new System.Data.SQLite.SQLiteCommand(strSQLQuery, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add("@requestUri", DbType.String);
command.Parameters["@requestUri"].Value = uri;
command.Parameters.Add("@method", DbType.String);
command.Parameters["@method"].Value = method;
command.Parameters.Add("@status", DbType.Int32);
command.Parameters["@status"].Value = status;
command.Parameters.Add("@statusMsg", DbType.String);
command.Parameters["@statusMsg"].Value = statusMsg;
command.Parameters.Add("@ipaddr", DbType.String);
command.Parameters["@ipaddr"].Value = ip;
command.Parameters.Add("@userid", DbType.String);
command.Parameters["@userid"].Value = userid;
command.Parameters.Add("@username", DbType.String);
command.Parameters["@username"].Value = username;
command.Parameters.Add("@role", DbType.String);
command.Parameters["@role"].Value = role;
command.Parameters.Add("@email", DbType.String);
command.Parameters["@email"].Value = email;
command.Parameters.Add("@nbf_date", DbType.String);
command.Parameters["@nbf_date"].Value = nbf_date;
command.Parameters.Add("@iat_date", DbType.String);
command.Parameters["@iat_date"].Value = iat_date;
command.Parameters.Add("@exp_date", DbType.String);
command.Parameters["@exp_date"].Value = exp_date;
command.Parameters.Add("@iss", DbType.String);
command.Parameters["@iss"].Value = iss;
command.Parameters.Add("@aud", DbType.String);
command.Parameters["@aud"].Value = aud;
command.Parameters.Add("@nbf", DbType.UInt64);
command.Parameters["@nbf"].Value = nbf;
command.Parameters.Add("@iat", DbType.UInt64);
command.Parameters["@iat"].Value = iat;
command.Parameters.Add("@exp", DbType.UInt64);
command.Parameters["@exp"].Value = exp;
command.Parameters.Add("@jti", DbType.String);
command.Parameters["@jti"].Value = jti;
command.Parameters.Add("@token", DbType.String);
command.Parameters["@token"].Value = token;
command.Parameters.Add("@lastaccess", DbType.DateTime);
command.Parameters["@lastaccess"].Value = lastaccess;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
bolR = true;
}
catch (Exception ex)
{
WriteActivityLog(ex.Message, 2);
bolR = false;
}
return(bolR);
}
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 void button1_Click(object sender, EventArgs e)
{
//创建一个数据库文件
//’dotnetthink','mypassword'
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES('dotnetthink', 'mypassword')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
/// <summary>
/// Function that export historical price (high, low, close) for the pair
/// <paramref name="ccyBase"/> VS <paramref name="ccyPair"/>.
/// It export it in a SQLite database, located in the the Project Folder,
/// in the ../static/databases folder
/// </summary>
/// <param name="ccyBase">the name of the base cryptocurrency.</param>
/// <param name="ccyPair">the name of the paire cryptocurrency.</param>
/// <param name="nbDays">the length of the history.</param>
/// <returns>
/// A tuple containing, in this order, the binary sucess (true or false),
/// and eventually a message explaining why the order failed
/// </returns>
/// <example>
/// in BTC-EUR price :
/// BTC is the base currency
/// EUR is the paire currency
/// </example>
public (string, int) ExportHistoricalData(string ccyBase, string ccyPair, int nbDays)
{
System.Data.SQLite.SQLiteConnection mainConnexion = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteCommand mainRequest;
string goodDate = DateTime.Now.ToString("dd-MM-yyy__HHmmss");
string dbName = "DataBase_" + ccyBase + "_" + ccyPair + "_" + nbDays + "_Days_" + goodDate + ".sqlite";
string dbPath = Tools.GetDirectory() + "Static/Databases/";
dbPath += dbName;
string tableName = ccyBase + "-" + ccyPair + "-" + nbDays.ToString() + "Days";
string request = "";
int response;
Dictionary <string, Dictionary <string, string> > BTCPrice;
int i;
// Création de la DB Sqlite3
System.Data.SQLite.SQLiteConnection.CreateFile(dbPath);
// Connection à la BD nouvellement crée
mainConnexion = new System.Data.SQLite.SQLiteConnection("Data Source=" + dbPath + ";Version=3");
mainConnexion.Open();
// Création d'un nouvelle table
request = "CREATE TABLE [" + tableName + "] (";
request += "id INTEGER PRIMARY KEY AUTOINCREMENT,";
request += "date VARCHAR(100),";
request += "open DOUBLE,";
request += "high DOUBLE,";
request += "low DOUBLE,";
request += "close DOUBLE,";
request += "volume INT";
request += ");";
mainRequest = new System.Data.SQLite.SQLiteCommand(request, mainConnexion);
response = mainRequest.ExecuteNonQuery();
// Historique des prix
BTCPrice = GetHistoricalData(ccyBase, ccyPair, nbDays);
// Export des prix historiques vers la base de données
i = 1;
foreach (var price in BTCPrice)
{
request = "INSERT INTO [" + tableName + "] VALUES (";
request += i.ToString() + ",";
request += "'" + price.Key.ToString() + "',";
request += price.Value["open"].ToString() + ",";
request += price.Value["high"].ToString() + ",";
request += price.Value["low"].ToString() + ",";
request += price.Value["close"].ToString() + ",";
request += (Double.Parse(price.Value["volumefrom"], System.Globalization.CultureInfo.InvariantCulture) + Double.Parse(price.Value["volumeto"], System.Globalization.CultureInfo.InvariantCulture)).ToString();
request += ");";
mainRequest = new System.Data.SQLite.SQLiteCommand(request, mainConnexion);
response = mainRequest.ExecuteNonQuery();
i++;
}
mainConnexion.Close();
return(dbName, response);
}
private void UpgradeLocalDB()
{
string path = System.IO.Path.Combine(Application.StartupPath, "DbUpdate_Sqlite.sql");
if (File.Exists(path))
{
List<string> commands = (new LJH.GeneralLibrary.SQLHelper.SQLStringExtractor()).ExtractFromFile(path);
if (commands != null && commands.Count > 0)
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("Data Source=" + _DBPath))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
foreach (string command in commands)
{
try
{
cmd.CommandText = command;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//LJH.GeneralLibrary.ExceptionHandling.ExceptionPolicy.HandleException(ex);
}
}
}
}
}
}
}
private async void OnCompleteButtonClickAsync(object sender, RoutedEventArgs e)
{
Quest quest = (Quest)(sender as FrameworkElement).DataContext;
if (!quest.Status.Equals("Complete"))
{
string sqlQuery = "SELECT * FROM Character WHERE Id = " + quest.ArcId;
string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "QuestArc.db3");
using (var dbConnection = new System.Data.SQLite.SQLiteConnection("Data Source = " + path))
{
using (var dbcmd = new System.Data.SQLite.SQLiteCommand())
{
dbConnection.Open();
dbcmd.Connection = dbConnection;
dbcmd.CommandText = sqlQuery;
IDataReader reader = dbcmd.ExecuteReader();
string characterId = null;
int points = 0;
while (reader.Read())
{
characterId = reader["Id"].ToString();
points = (int)(long)reader["UnallocatedPoints"];
}
reader.Close();
string difficulty = quest.Difficulty;
int numPoints = 0;
switch (difficulty)
{
case "Easy":
numPoints = 10 + points;
break;
case "Normal":
numPoints = 20 + points;
break;
case "Hard":
numPoints = 30 + points;
break;
}
dbcmd.Parameters.AddWithValue("@id", quest.Id);
dbcmd.Parameters.AddWithValue("@status", "Complete");
dbcmd.CommandText = @"UPDATE Quest SET Status = @status WHERE Id = @id";
dbcmd.ExecuteNonQuery();
dbcmd.Parameters.AddWithValue("@id", characterId);
dbcmd.Parameters.AddWithValue("@points", numPoints);
dbcmd.CommandText = @"UPDATE Character SET UnallocatedPoints = @points WHERE Id = @id";
dbcmd.ExecuteNonQuery();
dbConnection.Close();
}
this.Bindings.Update();
}
ContentDialog dialog = new ContentDialog();
dialog.Title = "Quest Complete!";
dialog.CloseButtonText = "Ok";
await dialog.ShowAsync();
}
else
{
ContentDialog dialog = new ContentDialog();
dialog.Title = "Quest is already Complete";
dialog.CloseButtonText = "Ok";
await dialog.ShowAsync();
}
}
private void Save()
{
// --------------------------------------------------
// Deadline
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("INSERT INTO deadlines(deadline) VALUES(@deadline); SELECT last_insert_rowid()", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@deadline", DeadlineDatePicker.SelectedDate));
var reader = command.ExecuteReader();
if (reader.Read())
{
row["deadline_id"] = reader.GetInt64(0);
}
else
{
row["deadline_id"] = System.DBNull.Value;
}
}
taskDataAdapter.Update(dataSet, "event");
// --------------------------------------------------
// Tags
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
var currentTags = new System.Collections.Generic.List<string>(Util.SplitTags(TagsTextBox.Text));
var whereInTuple = Util.SqlParametersList(currentTags);
var rows = new System.Collections.Generic.List<System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>>();
{
var command = new System.Data.SQLite.SQLiteCommand(@"
SELECT events_tags.ID, events_tags.event_id, events_tags.tag_id, tags.name
FROM events_tags LEFT JOIN tags ON tags.ID = events_tags.tag_id
WHERE events_tags.event_id=@id AND tags.name IN(" + whereInTuple.Item1 + @")
UNION ALL
SELECT NULL, NULL, ID, name
FROM tags
WHERE ID NOT IN(SELECT tag_id FROM events_tags WHERE event_id=@id) AND name IN(" + whereInTuple.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple.Item2)
command.Parameters.Add(parameter);
var reader = command.ExecuteReader();
while (reader.Read())
{
var tuple = new System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>(null, null, null, "");
System.Nullable<long> tasksTagsID = null;
System.Nullable<long> taskID = null;
System.Nullable<long> tagID = null;
string name = "";
if (!reader.IsDBNull(0))
tasksTagsID = reader.GetInt64(0);
if (!reader.IsDBNull(1))
taskID = reader.GetInt64(1);
if (!reader.IsDBNull(2))
tagID = reader.GetInt64(2);
if (!reader.IsDBNull(3))
name = reader.GetString(3);
rows.Add(System.Tuple.Create(tasksTagsID, taskID, tagID, name));
}
}
// delete all old tasks_tags not need for new tags
{
var oldTasksTagsIDs = new System.Collections.Generic.List<long>();
foreach (var tuple in rows)
{
if (tuple.Item1.HasValue)
oldTasksTagsIDs.Add(tuple.Item1.Value);
}
var whereInTuple2 = Util.SqlParametersList(oldTasksTagsIDs);
var command = new System.Data.SQLite.SQLiteCommand("DELETE FROM events_tags WHERE event_id=@id AND ID NOT IN(" + whereInTuple2.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple2.Item2)
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
// link existing new tags
foreach (var tuple in rows)
{
if (!tuple.Item1.HasValue && tuple.Item3.HasValue)
{
var tagID = tuple.Item3.Value;
long newTasksTagsID = Util.InsertInto(connection, "events_tags", System.Tuple.Create("event_id", id), System.Tuple.Create("tag_id", tagID));
}
}
// create and link new tags
{
var newTags = new System.Collections.Generic.List<string>();
foreach (var tagName in currentTags)
{
bool found = false;
foreach (var row in rows)
{
if (row.Item4 == tagName)
{
found = true;
break;
}
}
if (!found)
{
long newTagID = Util.InsertInto(connection, "tags", System.Tuple.Create("name", tagName));
long newTasksTagsID = Util.InsertInto(connection, "events_tags", System.Tuple.Create("event_id", id), System.Tuple.Create("tag_id", newTagID));
}
}
}
// Alerts
foreach (System.Data.DataRow row in dataSet.Tables["alerts"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["event_id"] = id;
}
alertsDataAdapter.Update(dataSet, "alerts");
dataSet.Tables["alerts"].Clear();
alertsDataAdapter.Fill(dataSet, "alerts");
}
// --------------------------------------------------
// Clean state
IsDirty = false;
try
{
this.parent.MassReloadEvents();
}
catch
{
}
}
private void newAccountButton_Click(object sender, EventArgs e)
{
Directory.CreateDirectory(Variables.databaseFolder);
string newAccountName = "";
DialogResult dr = new DialogResult();
NameAccount nameAccount = new NameAccount();
dr = nameAccount.ShowDialog();
if (Variables.accountName != "")
{
newAccountName = Variables.accountName + ".db";
// This is the query which will create a new table in our database file with three columns. An auto increment column called "ID", and two NVARCHAR type columns with the names "Key" and "Value"
string createMonthlyTableQuery = @"CREATE TABLE IF NOT EXISTS [Monthly] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
string createYearlyTableQuery = @"CREATE TABLE IF NOT EXISTS [Yearly] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
string createWantedTableQuery = @"CREATE TABLE IF NOT EXISTS [Wanted] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
string createAssestTableQuery = @"CREATE TABLE IF NOT EXISTS [Assests] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
System.Data.SQLite.SQLiteConnection.CreateFile(Path.Combine(Variables.databaseFolder, newAccountName)); // Create the file which will be hosting our database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + Path.Combine(Variables.databaseFolder, newAccountName)))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = createMonthlyTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createYearlyTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createWantedTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createAssestTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
con.Close(); // Close the connection to the database
}
}
Variables.dataPath = Variables.connectionString + Variables.databaseFolder + @"\" + Variables.accountName + ".db";
string fullDbPath = Variables.databaseFolder + @"\" + Variables.accountName + ".db";
if (File.Exists(fullDbPath))
{
this.Close();
}
else
{
MessageBox.Show("I cannot find the database captain. I must abort!", "Database Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
}
}
}
private void btn_saveMod_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Sei sicuro di voler salvare le modifiche? Tutte le vecchie informazioni verranno sovrascritte",
"Attenzione", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
if (checkValidationCert() && cashControl() && checkValidationDate())
{
//sql update
int tipoAbb = 0;
if (!tb_ingressi.Text.ToString().Equals("0") || !tb_ingressi.Text.ToString().Equals(""))
{
tipoAbb = 1; //abbonameno a ingressi
}
else
{
tipoAbb = 0;
} //abonameno std
if (checkUsefulFields() && checkCorsi())
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
//controllo costo rispettato e date inserite bene
//-------------------------------------------------------------------QUERY INSERT ISCRITTI
string command = "UPDATE Iscritto SET Nome='" + tb_nome.Text + "', Cognome='" + tb_cognome.Text + "', DataN='" + tb_dataN.Text + "', " +
"Residenza='" + tb_residenza.Text + "', Via='" + tb_via.Text + "', Recapito='" + tb_recapito.Text + "', Ntessera='" + tb_nTessera.Text +
"', DataIn='" + tb_dataIn.Text + "', " + "DataFine='" + tb_dataFine.Text + "', NIngressi='" + tb_ingressi.Text + "', TipoAbb='" + tipoAbb
+ "', Costo='" + tb_costo.Text + "' " + "WHERE CodIscritto='" + nTessera + "'";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
//-------------------------------------------------------------------QUERY INSERT FREQUENTA (eliminare tutte quelle esistenti e rifarla)
command = "DELETE FROM Frequenta WHERE CodIscritto='" + nTessera + "'";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
int IdCorso = 0;
foreach (DataGridViewRow row in dgv_corsi.Rows)
{
command = "SELECT Id FROM Corso WHERE Nome='" + row.Cells[0].Value + "'";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
IdCorso = reader.GetInt32(0);
}
command = "INSERT INTO Frequenta values ('" + nTessera + "','" + IdCorso + "')";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
}
//-------------------------------------------------------------------QUERY INSERT RATE
command = "DELETE FROM Rata WHERE CodIscritto='" + nTessera + "'";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
foreach (DataGridViewRow row in dgv_rate.Rows)
{
command = "INSERT INTO Rata values ('" + nTessera + "','" + row.Cells[0].Value + "','" + row.Cells[2].Value + "','" + row.Cells[1].Value + "')";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
}
//-------------------------------------------------------------------QUERY INSERT CERTIFICATO
if (tb_pres.Text.ToUpper().Equals("SI"))
{
command = "UPDATE Certificato SET Presente='SI', DataScadenza='" + tb_dataScad.Text + "' WHERE CodIscritto='" + nTessera + "'";
}
else
{
command = "UPDATE Certificato SET Presente='NO', DataScadenza='" + tb_dataScad.Text + "' WHERE CodIscritto='" + nTessera + "'";
}
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
conn.Close();
}
}
MessageBox.Show("Inserimento avvenuto con successo!", "Inserimento");
this.Close();
}
}
}
}
static void ExecuteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
CommonFunction common = new CommonFunction();
common.setDbPath(path);
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " WORD_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , RONRI_NAME1 TEXT";
sql += " , RONRI_NAME2 TEXT";
sql += " , BUTSURI_NAME TEXT";
sql += " , USER_ID INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += " , FOREIGN KEY (USER_ID) REFERENCES USER_MST(USER_ID)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " SHINSEI_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , RONRI_NAME1 TEXT";
sql += " , RONRI_NAME2 TEXT";
sql += " , BUTSURI_NAME TEXT";
sql += " , WORD_ID INTEGER";
sql += " , STATUS INTEGER";
sql += " , USER_ID INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += " , FOREIGN KEY (USER_ID) REFERENCES USER_MST(USER_ID)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " USER_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , EMP_ID INTEGER UNIQUE ";
sql += " , USER_NAME TEXT";
sql += " , KENGEN INTEGER";
sql += " , MAIL_ID TEXT";
sql += " , PASSWORD TEXT";
sql += " , MAIL_ADDRESS TEXT";
sql += " , SANKA_KAHI INTEGER";
sql += " , DELETE_FLG INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += "); ";
sql += "insert into USER_MST(USER_ID,EMP_ID,USER_NAME,KENGEN,MAIL_ID,PASSWORD,MAIL_ADDRESS,SANKA_KAHI,DELETE_FLG,VERSION) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
protected override void OnStart(string[] args)
{
//Create db
WindowsServiceMentorshipTest.Database.InitializeDB.CreateDBTable();
//Read from db
string selectQuery = @"SELECT * FROM MyTable";
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|/databaseFile1.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); // Open the connection to the database
com.CommandText = selectQuery; // Set CommandText to our query that will select all rows from the table
com.ExecuteNonQuery(); // Execute the query
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
//string postJSON;
DataClass001 dataClass001 = new DataClass001();
while (reader.Read())
{
dataClass001.Key = reader["Key"].ToString();
dataClass001.Value = reader["Value"].ToString();
//postJSON = JsonSerializer.Serialize(dataClass001);
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result_Post_Keys.txt", dataClass001.Key);
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result_Post_Values.txt", dataClass001.Value);
using (var client = new HttpClient())
{
client.BaseAddress = new Uri("http://webapi.local/api/");
//HTTP POST
//var postTask = client.PostAsJsonAsync<DataClass001>("values", dataClass001);
var postTask = client.PostAsJsonAsync <string>("values", @"{" + "\"key\":\"" + dataClass001.Key + "\"," + "\"value\":\"" + dataClass001.Value + "\"}");
postTask.Wait();
var result = postTask.Result;
if (result.IsSuccessStatusCode)
{
//return RedirectToAction("Index");
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result_Post.txt", "Success" + result.StatusCode);
}
else
{
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result_Post.txt", "Fail" + result.StatusCode);
}
}
}
using (var client = new HttpClient())
{
client.BaseAddress = new Uri("http://webapi.local/api/");
//HTTP GET
var responseTask = client.GetAsync("values");
responseTask.Wait();
var result = responseTask.Result;
if (result.IsSuccessStatusCode)
{
System.IO.File.WriteAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result.txt", "Success");
var readTask = result.Content.ReadAsAsync <IList <string> >();
readTask.Wait();
IList <string> results = readTask.Result;
foreach (var s in results)
{
//Console.WriteLine(d);
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\ResultBody.txt", s);
}
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\ResultBody.txt", Environment.NewLine);
}
else
{
System.IO.File.WriteAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result.txt", "FAIL");
}
}
conn.Close(); // Close the connection to the database
}
}
WriteToFile("Service is started at " + DateTime.Now);
timer.Elapsed += new ElapsedEventHandler(OnElapsedTime);
timer.Interval = 5000; //number in milisecinds
timer.Enabled = true;
}
}
public int ExecuteNonQuery(string sql)
{
var sqlCmd = new System.Data.SQLite.SQLiteCommand(sql, sqliteConnection);
return(sqlCmd.ExecuteNonQuery());
}
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 UpdateSqLiteDB(List<long> docGroupIds)
{
string connectionString = "data source=" + Arguments.SQLiteDataFile;
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(connectionString))
{
con.Open();
using (System.Data.SQLite.SQLiteTransaction tran = con.BeginTransaction())
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
foreach (int id in docGroupIds)
{
com.CommandText = " UPDATE DocumentGroups SET Operation=3 WHERE DocumentGroupId = " + id;
com.ExecuteNonQuery();
com.CommandText = " UPDATE Documents SET Operation=3 WHERE DocumentGroupId = " + id;
com.ExecuteNonQuery();
}
}
tran.Commit();
}
}
}
/// <summary>
/// This is run each time the app runs, but won't do anything after the first run.
/// </summary>
public void CreateDatabase()
{
try
{
if (!File.Exists(Settings.DatabaseFile))
{
string categoriesSql = "CREATE TABLE \"categories\" ("+
"\"id\" INTEGER PRIMARY KEY AUTOINCREMENT," +
"\"name\" TEXT NOT NULL," +
"\"active\" INTEGER DEFAULT 0,"+
"\"inbuilt\" INTEGER DEFAULT 0)";
string questionsSql = "CREATE TABLE questions (" +
"\"id\" INTEGER PRIMARY KEY AUTOINCREMENT," +
"\"categoryid\" INTEGER," +
"\"title\" TEXT," +
"\"answer\" TEXT," +
"\"order\" INTEGER DEFAULT 0," +
"\"lastasked\" INTEGER DEFAULT 0," +
"\"nextaskon\" INTEGER DEFAULT 0," +
"\"previousinterval\" INTEGER DEFAULT 0," +
"\"interval\" INTEGER DEFAULT 0," +
"\"askcount\" INTEGER DEFAULT 0," +
"\"responsequality\" INTEGER DEFAULT 0," +
"\"easinessfactor\" REAL DEFAULT 0)";
// Create the file
SqliteConnection.CreateFile(Settings.DatabaseFile);
// And the schema
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
command.CommandText = categoriesSql;
command.ExecuteNonQuery();
command.CommandText = questionsSql;
command.ExecuteNonQuery();
// Default category
command.CommandText = Default.Sql();
command.ExecuteNonQuery();
#if GERMAN
command.CommandText = German.Sql();
command.ExecuteNonQuery();
#elif SPANISH
command.CommandText = Spanish.Sql();
command.ExecuteNonQuery();
#elif FRENCH
command.CommandText = French.Sql();
command.ExecuteNonQuery();
#endif
}
}
}
}
catch (IOException ex)
{
Logger.Fatal("Unable to delete the database file {0}: \n{1}", Settings.DatabaseFile, ex);
throw;
}
catch (SqliteException ex)
{
Logger.Fatal("Unable to create the database: \n{0}", ex);
throw;
}
}
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();
}
}
/*
static void UpdateMySql(string connstr, string inputfile, string outputfile)
{
Console.WriteLine("Updating mysql database.");
using(MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connstr))
{
conn.Open();
string cmdtext = "update Recording set RecordingFileName = @NewFilename where RecordingFileName = @OldFilename;";
using(MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(cmdtext, conn))
{
cmd.Parameters.AddWithValue("@NewFilename", outputfile);
cmd.Parameters.AddWithValue("@OldFilename", inputfile);
int rows = cmd.ExecuteNonQuery();
if(rows > 0)
Console.WriteLine("Successfully updated database.");
else
Console.WriteLine("Failed to update database.");
}
conn.Close();
}
}
static void UpdateSqlServer(string connstr, string inputfile, string outputfile)
{
Console.WriteLine("Updating SQL Server database.");
using(SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
string cmdtext = "update Recording set RecordingFileName = @NewFilename where RecordingFileName=@OldFilename";
using(SqlCommand cmd = new SqlCommand(cmdtext, conn))
{
cmd.Parameters.AddWithValue("@NewFilename", outputfile);
cmd.Parameters.AddWithValue("@OldFilename", inputfile);
int rows = cmd.ExecuteNonQuery();
if(rows > 0)
Console.WriteLine("Successfully updated database.");
else
Console.WriteLine("Failed to update database.");
}
conn.Close();
}
}
*/
static void UpdateSqlLite(string connstr, string inputfile, string outputfile)
{
Console.WriteLine("Update Sql Lite database.");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connstr))
{
conn.Open();
string cmdtext = "update scheduled_recording set filename = @NewFilename where lower(filename) = @OldFilename";
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(cmdtext, conn))
{
cmd.Parameters.AddWithValue("@NewFilename", outputfile);
cmd.Parameters.AddWithValue("@OldFilename", inputfile.ToLower());
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
Console.WriteLine("Sucessfully updated database.");
else
Console.WriteLine("Failed to update database.");
}
conn.Close();
}
}
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 void btnDelete_Click(object sender, EventArgs e)
{
var messageBoxResult = MessageBox.Show("你確定要刪除這筆資料嗎?", "刪除", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
if (messageBoxResult == System.Windows.Forms.DialogResult.No)
return;
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(DataBase.Instance.sqlConnection))
{
cmd.CommandText = "Delete From AddressBook where Id=@Id";
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter("Id", CurrentCompany.Id));
cmd.ExecuteNonQuery();
}
companies.Remove(CurrentCompany);
}
static void Main(string[] args)
{
if (File.Exists(config_fn))
{
StreamReader file = new StreamReader(config_fn);
user = file.ReadLine(); // instructions
user = file.ReadLine();
oauth = file.ReadLine();
channel = file.ReadLine();
}
Thread mainThread = new Thread(Program.MainThread);
System.Console.WriteLine("ShickenBot starting up!");
/* prep database */
bool first = false;
if (!File.Exists(database_fn))
{
System.Data.SQLite.SQLiteConnection.CreateFile(database_fn);
first = true;
System.Console.WriteLine("Initializing empty statistics database!");
}
con = new System.Data.SQLite.SQLiteConnection("data source=" + database_fn);
if (first)
{
/* create the table */
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = @"CREATE TABLE IF NOT EXISTS [channels] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[channel_name] TEXT UNIQUE NOT NULL
)";
com.ExecuteNonQuery();
com.CommandText = @"CREATE TABLE IF NOT EXISTS [rounds] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[chan_id] INTEGER NOT NULL,
[began] TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
[time] INTEGER DEFAULT 0,
FOREIGN KEY (chan_id) REFERENCES channels(ID)
)";
com.ExecuteNonQuery();
com.CommandText = @"CREATE TABLE IF NOT EXISTS [players] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[chan_id] INTEGER NOT NULL,
[nickname] TEXT NOT NULL,
[points] INTEGER DEFAULT 0,
FOREIGN KEY (chan_id) REFERENCES channels(ID),
UNIQUE (chan_id, nickname) ON CONFLICT REPLACE
)";
com.ExecuteNonQuery();
com.CommandText = @"CREATE TABLE IF NOT EXISTS [guesses] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[round_id] INTEGER NOT NULL,
[user_id] TEXT NOT NULL,
[chan_id] INTEGER NOT NULL,
[t] TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
[time] INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES players(ID),
FOREIGN KEY (round_id) REFERENCES rounds(ID),
FOREIGN KEY (chan_id) REFERENCES channels(ID)
)";
com.ExecuteNonQuery();
con.Close();
}
}
else
{
long[] stat = stats();
System.Console.WriteLine("Loaded statistics database. " + stat[0] + " viewers, " + stat[1] + " rounds, " + stat[2] + " guesses tracked across " + stat[3] + " channels.");
}
/* launch chat */
mainThread.Start();
while (Console.Read() != 13)
{
;
}
}
static void ExecuteSqliteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter_v2.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder()
{
DataSource = path
};
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " word_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " shinsei_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , word_id INTEGER";
sql += " , status INTEGER";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " user_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , emp_id INTEGER UNIQUE ";
sql += " , user_name TEXT";
sql += " , kengen INTEGER";
sql += " , mail_id TEXT";
sql += " , password TEXT";
sql += " , mail_address TEXT";
sql += " , sanka_kahi INTEGER";
sql += " , delete_flg INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += "); ";
sql += "insert into USER_MST(user_id,emp_id,user_name,kengen,mail_id,password,mail_address,sanka_kahi,delete_flg,version) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
string sqliteDdlText = sql;
var cmd = new System.Data.SQLite.SQLiteCommand(sqliteDdlText, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
private static void round_end(ChatMessage c)
{
string endtime = new string(c.Message.Where(Char.IsDigit).ToArray()); // linq magic to extract any leading/trailing chars
if (endtime.Length != 4)
{
verb("Invalid endtime (" + endtime + ")");
return;
}
long chan_id = get_channel_id(c.Channel);
verb("round ended by " + c.Username + ", with time of " + endtime);
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
// first, all the perfect guesses
com.CommandText = @"SELECT user_id, time
FROM guesses
WHERE round_id = @round_id
AND chan_id = @chanid
AND time = @end_time";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@round_id", round_id);
com.Parameters.AddWithValue("@chanid", chan_id);
com.Parameters.AddWithValue("@end_time", endtime);
using (System.Data.SQLite.SQLiteDataReader r = com.ExecuteReader())
{
while (r.Read())
{
award_points((string)r["user_id"], (long)r["time"], endtime, 0);
}
}
// then, all the users who weren't exactly right
com.CommandText = @"SELECT user_id, time
FROM guesses
WHERE round_id = @round_id
AND time != @end_time
AND chan_id = @chanid
ORDER BY ABS(time - @end_time) ASC LIMIT 3";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@round_id", round_id);
com.Parameters.AddWithValue("@chanid", chan_id);
com.Parameters.AddWithValue("@end_time", endtime);
using (System.Data.SQLite.SQLiteDataReader r = com.ExecuteReader())
{
long place = 1;
while (r.Read())
{
award_points((string)r["user_id"], (long)r["time"], endtime, place);
place++;
}
}
// then update the round with the final time, for stats
com.CommandText = @"UPDATE rounds SET time = @end_time WHERE id = @id AND chan_id = @chanid";
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@id", round_id);
com.Parameters.AddWithValue("@chanid", chan_id);
com.Parameters.AddWithValue("@end_time", endtime);
com.ExecuteNonQuery();
con.Close();
}
if (round_awarded == 0)
{
cl.SendMessage("Round #" + round_id + " ended without anyone playing :(");
}
// end the round
round_started = false;
}
private bool Save()
{
System.Data.SQLite.SQLiteTransaction transaction = null;
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
try
{
transaction = connection.BeginTransaction();
// --------------------------------------------------
// Deadline
if (DeadlineIsSelectedDateRadioButton.IsChecked.Value)
{
var command = new System.Data.SQLite.SQLiteCommand("INSERT INTO deadlines(deadline) VALUES(@deadline); SELECT last_insert_rowid()", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@deadline", DeadlineDatePicker.SelectedDate));
var reader = command.ExecuteReader();
if (reader.Read())
{
row["deadline_id"] = reader.GetInt64(0);
}
}
else if (DeadlineIsEventRadioButton.IsChecked.Value)
{
row["deadline_id"] = (EventsComboBox.SelectedValue != null) ? EventsComboBox.SelectedValue : System.DBNull.Value;
}
else
{
row["deadline_id"] = System.DBNull.Value;
}
taskDataAdapter.Update(dataSet, "task");
// --------------------------------------------------
// Tags
var currentTags = new System.Collections.Generic.List<string>(Util.SplitTags(TagsTextBox.Text));
var whereInTuple = Util.SqlParametersList(currentTags);
var rows = new System.Collections.Generic.List<System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>>();
{
var command = new System.Data.SQLite.SQLiteCommand(@"
SELECT tasks_tags.ID, tasks_tags.task_id, tasks_tags.tag_id, tags.name
FROM tasks_tags LEFT JOIN tags ON tags.ID = tasks_tags.tag_id
WHERE tasks_tags.task_id=@id AND tags.name IN(" + whereInTuple.Item1 + @")
UNION ALL
SELECT NULL, NULL, ID, name
FROM tags
WHERE ID NOT IN(SELECT tag_id FROM tasks_tags WHERE task_id=@id) AND name IN(" + whereInTuple.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple.Item2)
command.Parameters.Add(parameter);
var reader = command.ExecuteReader();
while (reader.Read())
{
var tuple = new System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>(null, null, null, "");
System.Nullable<long> tasksTagsID = null;
System.Nullable<long> taskID = null;
System.Nullable<long> tagID = null;
string name = "";
if (!reader.IsDBNull(0))
tasksTagsID = reader.GetInt64(0);
if (!reader.IsDBNull(1))
taskID = reader.GetInt64(1);
if (!reader.IsDBNull(2))
tagID = reader.GetInt64(2);
if (!reader.IsDBNull(3))
name = reader.GetString(3);
rows.Add(System.Tuple.Create(tasksTagsID, taskID, tagID, name));
}
}
// delete all old tasks_tags not need for new tags
{
var oldTasksTagsIDs = new System.Collections.Generic.List<long>();
foreach (var tuple in rows)
{
if (tuple.Item1.HasValue)
oldTasksTagsIDs.Add(tuple.Item1.Value);
}
var whereInTuple2 = Util.SqlParametersList(oldTasksTagsIDs);
var command = new System.Data.SQLite.SQLiteCommand("DELETE FROM tasks_tags WHERE task_id=@id AND ID NOT IN(" + whereInTuple2.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple2.Item2)
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
// link existing new tags
foreach (var tuple in rows)
{
if (!tuple.Item1.HasValue && tuple.Item3.HasValue)
{
var tagID = tuple.Item3.Value;
long newTasksTagsID = Util.InsertInto(connection, "tasks_tags", System.Tuple.Create("task_id", id), System.Tuple.Create("tag_id", tagID));
}
}
// create and link new tags
{
var newTags = new System.Collections.Generic.List<string>();
foreach (var tagName in currentTags)
{
bool found = false;
foreach (var row in rows)
{
if (row.Item4 == tagName)
{
found = true;
break;
}
}
if (!found)
{
long newTagID = Util.InsertInto(connection, "tags", System.Tuple.Create("name", tagName));
long newTasksTagsID = Util.InsertInto(connection, "tasks_tags", System.Tuple.Create("task_id", id), System.Tuple.Create("tag_id", newTagID));
}
}
}
// --------------------------------------------------
// Alerts
foreach (System.Data.DataRow row in dataSet.Tables["alerts"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["task_id"] = id;
}
alertsDataAdapter.Update(dataSet, "alerts");
dataSet.Tables["alerts"].Clear();
alertsDataAdapter.Fill(dataSet, "alerts");
// --------------------------------------------------
// Sub-Tasks
foreach (System.Data.DataRow row in dataSet.Tables["sub_tasks"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["child_of"] = id;
}
subTasksDataAdapter.Update(dataSet, "sub_tasks");
dataSet.Tables["sub_tasks"].Clear();
subTasksDataAdapter.Fill(dataSet, "sub_tasks");
// --------------------------------------------------
// Clean state
IsDirty = false;
transaction.Commit();
}
catch (System.Data.SQLite.SQLiteException e)
{
if (transaction != null)
transaction.Rollback();
switch (e.ErrorCode)
{
case System.Data.SQLite.SQLiteErrorCode.Constraint:
Util.ShowFieldMustBeUniqueMessage(this, Util.ExtractColumnName(e.Message));
break;
}
}
}
try
{
this.parent.MassReloadTasks();
}
catch
{
}
return !IsDirty;
}
private void SaveBadgeForItem(String itemPath, String badgePath, Boolean isClear = false) {
var m_dbConnection = new System.Data.SQLite.SQLiteConnection("Data Source=" + this._DBPath + ";Version=3;");
m_dbConnection.Open();
if (isClear) {
var command3 = new System.Data.SQLite.SQLiteCommand("DELETE FROM badges WHERE Path=@Path", m_dbConnection);
command3.Parameters.AddWithValue("Path", itemPath);
command3.ExecuteNonQuery();
} else {
var command1 = new System.Data.SQLite.SQLiteCommand("SELECT * FROM badges WHERE Path=@Path", m_dbConnection);
command1.Parameters.AddWithValue("Path", itemPath);
var Reader = command1.ExecuteReader();
var sql = Reader.Read()
? @"UPDATE badges SET Collection = @Collection, Badge = @Badge WHERE Path = @Path"
: @"INSERT INTO badges (Path, Collection, Badge) VALUES (@Path, @Collection, @Badge)";
var command2 = new System.Data.SQLite.SQLiteCommand(sql, m_dbConnection);
command2.Parameters.AddWithValue("Path", itemPath);
command2.Parameters.AddWithValue("Collection", Path.GetFileName(Path.GetDirectoryName(badgePath)));
command2.Parameters.AddWithValue("Badge", Path.GetFileName(badgePath));
command2.ExecuteNonQuery();
Reader.Close();
}
m_dbConnection.Close();
}
static void ExecuteSqliteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter_v2.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " word_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " shinsei_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , word_id INTEGER";
sql += " , status INTEGER";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " user_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , emp_id INTEGER UNIQUE ";
sql += " , user_name TEXT";
sql += " , kengen INTEGER";
sql += " , mail_id TEXT";
sql += " , password TEXT";
sql += " , mail_address TEXT";
sql += " , sanka_kahi INTEGER";
sql += " , delete_flg INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += "); ";
sql += "insert into USER_MST(user_id,emp_id,user_name,kengen,mail_id,password,mail_address,sanka_kahi,delete_flg,version) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
string sqliteDdlText = sql;
var cmd = new System.Data.SQLite.SQLiteCommand(sqliteDdlText, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
public int ExecuteNonQuery(string sql)
{
var sqlCmd = new System.Data.SQLite.SQLiteCommand(sql, sqliteConnection);
return sqlCmd.ExecuteNonQuery();
}
private void btn_ok_Click(object sender, EventArgs e)
{
if (!tb_costo.Text.Equals(""))
{
int lastNTess;
addNewRata.setCosto(tb_costo.Text);
int tipoAbb = 0;
if (nud_Ningressi.Value != 0)
{
tipoAbb = 1; //abbonameno a ingressi
}
else
{
tipoAbb = 0;
} //abbonamento std
//if (checkUsefulFields() && checkCorsi() && DateTime.Compare(dtp_dataIn.Value, dtp_dataFin.Value) != -1 && DateTime.Compare(dtp_dataIn.Value, dtp_dataFin.Value) != 0)
if (checkUsefulFields() && checkCorsi())
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
//-------------------------------------------------------------------QUERY INSERT ISCRITTI
string command = @"INSERT into Iscritto(Nome,Cognome,DataN,CodFisc,Residenza,Via,Recapito,Email,Ntessera,DataIn,DataFine,NIngressi,TipoAbb,Costo)
values ('" + tb_nome.Text.ToString() + "','" + tb_cognome.Text.ToString() + "','" + dtp_dataN.Value.ToString("yyyy-MM-dd")
+ "','" + tb_codFisc.Text.ToString() + "','" + tb_residenza.Text.ToString() + "','" + tb_via.Text.ToString() + "','" + tb_recapito.Text.ToString() + "','"
+ tb_mail.Text.ToString() + "','" + tb_nTessera.Text.ToString() + "','" + dtp_dataIn.Value.ToString("yyyy-MM-dd") + "','" + dtp_dataFin.Value.ToString("yyyy-MM-dd") + "','" + nud_Ningressi.Value
+ "','" + tipoAbb + "','" + sqliteRealTypeconversion(tb_costo.Text.ToString()) + "');";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
//-------------------------------------------------------------------QUERY INSERT FREQUENTA
cmd.CommandText = "SELECT CodIscritto FROM Iscritto WHERE CodIscritto = (SELECT MAX(CodIscritto) FROM Iscritto);";
cmd.ExecuteNonQuery();
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
lastNTess = reader.GetInt32(0);
}
foreach (DataGridViewRow row in dgv_selCorsi.Rows)
{
if (Convert.ToBoolean(row.Cells[0].Value) == true)
{
command = "INSERT INTO Frequenta values ('" + lastNTess + "','" + row.Cells[2].Value + "')";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
}
}
//-------------------------------------------------------------------QUERY INSERT RATE
cmd.CommandText = addNewRata.getRateQuery();
cmd.ExecuteNonQuery();
//-------------------------------------------------------------------QUERY INSERT CERTIFICATO
if (cb_certificato.Checked)
{
command = "INSERT INTO Certificato values('" + lastNTess + "','SI','" + dtp_scadenzaCert.Value.ToString("yyyy-MM-dd") + "')";
}
else
{
command = "INSERT INTO Certificato(CodIscritto,Presente) values('" + lastNTess + "','NO')";
}
cmd.CommandText = command;
cmd.ExecuteNonQuery();
conn.Close();
}
}
MessageBox.Show("Inserimento avvenuto con successo!", "Inserimento");
this.Close();
addNewRata.Close();
}
else
{
MessageBox.Show("Alcuni campi necessari non sono stati compilati oppure per favore ricontrolla se hai inserito correttamente i corsi."
+ "Ricorda che tutti devono essere iscritti a GENERALE!",
"Errore Inserimento");
}
}
else
{
MessageBox.Show("Alcuni campi necessari non sono stati compilati, per favore inserisci un costo abbonameno non nullo",
"Errore Inserimento");
}
}
public void CreateUserDatabase()
{
// This is the query which will create a new table in our database file with three columns. An auto increment column called "ID", and two NVARCHAR type columns with the names "Key" and "Value"
var createTableQueries = new[] {@"CREATE TABLE IF NOT EXISTS [AspNetRoles] (
[Id] NVARCHAR (128) NOT NULL PRIMARY KEY,
[Name] NVARCHAR (256) NOT NULL
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUsers] (
[Id] NVARCHAR (128) NOT NULL PRIMARY KEY,
[Email] NVARCHAR (256) NULL,
[EmailConfirmed] BIT NOT NULL,
[PasswordHash] NVARCHAR (4000) NULL,
[SecurityStamp] NVARCHAR (4000) NULL,
[PhoneNumber] NVARCHAR (4000) NULL,
[PhoneNumberConfirmed] BIT NOT NULL,
[TwoFactorEnabled] BIT NOT NULL,
[LockoutEndDateUtc] DATETIME NULL,
[LockoutEnabled] BIT NOT NULL,
[AccessFailedCount] INT NOT NULL,
[UserName] NVARCHAR (256) NOT NULL
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserRoles] (
[UserId] NVARCHAR (128) NOT NULL,
[RoleId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([UserId], [RoleId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE,
FOREIGN KEY(RoleId) REFERENCES AspNetRoles(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserLogins] (
[LoginProvider] NVARCHAR (128) NOT NULL,
[ProviderKey] NVARCHAR (128) NOT NULL,
[UserId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([LoginProvider], [ProviderKey], [UserId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserClaims] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[UserId] NVARCHAR (128) NOT NULL,
[ClaimType] NVARCHAR (4000) NULL,
[ClaimValue] NVARCHAR (4000) NULL,
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [__MigrationHistory] (
[MigrationId] NVARCHAR (150) NOT NULL,
[ContextKey] NVARCHAR (300) NOT NULL,
[Model] VARBINARY (4000) NOT NULL,
[ProductVersion] NVARCHAR (32) NOT NULL,
PRIMARY KEY ([MigrationId], [ContextKey])
);"};
System.Data.SQLite.SQLiteConnection.CreateFile(@"d:\work\Ricettario\Ricettario\App_Data ricettario.db3"); // Create the file which will be hosting our database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(@"data source=d:\work\Ricettario\Ricettario\App_Data\ricettario.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
foreach (var createTableQuery in createTableQueries)
{
com.CommandText = createTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
}
//com.CommandText = "Select * FROM MyTable"; // Select all rows from our database table
//using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
//{
// while (reader.Read())
// {
// Console.WriteLine(reader["Key"] + " : " + reader["Value"]); // Display the value of the key and value column for every row
// }
//}
con.Close(); // Close the connection to the database
}
}
}
public void MoveQuestion(Question question, int newIndex)
{
int currentIndex = 0; // not the order, the list index.
// All questions, find the question's index
IList <Question> questions = ListQuestions().OrderBy(q => q.Order).ToList();
for (int i = 0; i < questions.Count; i++)
{
if (questions[i].Id == question.Id)
{
currentIndex = i;
break;
}
}
// Move all items after the new index up one
var after = questions.Where(q => q.Order >= newIndex).ToList();
for (int i = 0; i < after.Count; i++)
{
after[i].Order += 1;
}
questions[currentIndex].Order = newIndex;
// Re-order
var ordered = questions.OrderBy(q => q.Order).ToList();
for (int i = 0; i < ordered.Count; i++)
{
ordered[i].Order = i;
}
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
// TODO: put inside a transaction (not WinSQLite friendly)
command.CommandText = @"UPDATE questions SET [order]=@order WHERE id=@id";
foreach (Question questionItem in ordered)
{
SqliteParameter parameter = new SqliteParameter("@id", DbType.Int32);
parameter.Value = questionItem.Id;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@order", DbType.Int32);
parameter.Value = questionItem.Order;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
}
}
catch (SqliteException ex)
{
Logger.Fatal("Unable to MoveQuestion: \n{0}", ex);
throw;
}
}
//디비에 넣자
private void mfnInsertToDb()
{
mliStock.Clear();
string strCode = "";
string strQuery = "";
if (txtDbList.Text.Contains(","))
{
for (int i = 0; i < txtDbList.Text.Split(',').Length; i++)
{
mliStock.Add(txtDbList.Text.Split(',')[i].ToUpper());
}
}
else if (txtDbList.Text.Replace(" ", "") == "")
{
return;
}
else
{
mliStock.Add(txtDbList.Text.ToUpper());
}
for (int i = 0; i < mliStock.Count; i++)
{
strCode = mfnSearchCode(mliStock[i]);
if (strCode == "")
{
return;
}
else
{
mDetailInfo = Parser.GetDetailInfo(strCode, Convert.ToInt32(txtNum.Text) + 60);
strQuery = "DELETE FROM List WHERE Name = '" + mliStock[i] + "'";
Network.ExecDB(strQuery, mDbSource);
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(mDbSource))
{
conn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
using (System.Data.SQLite.SQLiteTransaction tran = conn.BeginTransaction())
{
for (int j = 0; j < mDetailInfo.Length; j++)
{
strQuery = "";
strQuery += "INSERT INTO List (Name, Date, Price, Volumn, HighPrice, LowPrice, StartPrice)" + "\r\n";
strQuery += "VALUES( " + "\r\n";
strQuery += "'" + mliStock[i] + "',";
strQuery += "'" + mDetailInfo[j].date + "',";
strQuery += "'" + mDetailInfo[j].price + "',";
strQuery += "'" + mDetailInfo[j].volumn + "',";
strQuery += "'" + mDetailInfo[j].highPrice + "',";
strQuery += "'" + mDetailInfo[j].lowPrice + "',";
strQuery += "'" + mDetailInfo[j].startPrice + "');" + "\r\n";
cmd.CommandText = strQuery;
cmd.ExecuteNonQuery();
}
tran.Commit();
}
}
conn.Close();
}
/*
* if (Network.ExecDB(strQuery, mDbSource) == 0)
* {
* MessageBox.Show("InsertError");
* return;
* }
*/
}
}
}
public void CreateDatabase()
{
var createTableQueries = new[] {
@"CREATE TABLE IF NOT EXISTS [Recipes] (
[Id] INTEGER PRIMARY KEY,
[Email] NVARCHAR (256) NULL,
[EmailConfirmed] BIT NOT NULL,
[PasswordHash] NVARCHAR (4000) NULL,
[SecurityStamp] NVARCHAR (4000) NULL,
[PhoneNumber] NVARCHAR (4000) NULL,
[PhoneNumberConfirmed] BIT NOT NULL,
[TwoFactorEnabled] BIT NOT NULL,
[LockoutEndDateUtc] DATETIME NULL,
[LockoutEnabled] BIT NOT NULL,
[AccessFailedCount] INT NOT NULL,
[UserName] NVARCHAR (256) NOT NULL
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserRoles] (
[UserId] NVARCHAR (128) NOT NULL,
[RoleId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([UserId], [RoleId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE,
FOREIGN KEY(RoleId) REFERENCES AspNetRoles(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserLogins] (
[LoginProvider] NVARCHAR (128) NOT NULL,
[ProviderKey] NVARCHAR (128) NOT NULL,
[UserId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([LoginProvider], [ProviderKey], [UserId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserClaims] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[UserId] NVARCHAR (128) NOT NULL,
[ClaimType] NVARCHAR (4000) NULL,
[ClaimValue] NVARCHAR (4000) NULL,
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [__MigrationHistory] (
[MigrationId] NVARCHAR (150) NOT NULL,
[ContextKey] NVARCHAR (300) NOT NULL,
[Model] VARBINARY (4000) NOT NULL,
[ProductVersion] NVARCHAR (32) NOT NULL,
PRIMARY KEY ([MigrationId], [ContextKey])
);"};
var dbFile = @"d:\work\Ricettario\Ricettario\App_Data\ricettario_shared.db3";
System.Data.SQLite.SQLiteConnection.CreateFile(dbFile);
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(@"data source=" + dbFile))
{
con.Open();
foreach (var createTableQuery in createTableQueries)
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
com.CommandText = createTableQuery;
com.ExecuteNonQuery();
}
}
con.Close();
}
}
/// <summary>
/// Create database and fill with mock data.
///
/// If it exists, it will overwrite.
/// </summary>
public bool CreateDb()
{
try
{
DropDb();
}
catch (Exception ex) { }
// CreateFile either creates a file or overwrites it with an empty one if existent and deletes everything
try
{
System.Data.SQLite.SQLiteConnection.CreateFile(@"C:\sqlite\something.db");
}
catch (Exception ex) { }
using (System.Data.SQLite.SQLiteConnection connectDb = new System.Data.SQLite.SQLiteConnection("Data Source=C:\\sqlite\\something.db"))
{
using (System.Data.SQLite.SQLiteCommand cmdCreate = new System.Data.SQLite.SQLiteCommand(connectDb))
{
// 1. open connection
connectDb.Open();
// 2. execute table creation commands
cmdCreate.CommandText = "create table if not exists activities(" +
"id integer primary key autoincrement," +
"activity text not null);";
cmdCreate.ExecuteNonQuery();
cmdCreate.CommandText = "create table if not exists intensities(" +
"id integer primary key autoincrement," +
"intensity text not null);";
cmdCreate.ExecuteNonQuery();
cmdCreate.CommandText = "create table if not exists entries(" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"date text not null, " +
"activityid integer not null references activities(id), " +
"duration real not null, " +
"intensityid integer not null references intensities(id), " +
"exclude boolean, " +
"notes text);";
cmdCreate.ExecuteNonQuery();
// 3. execute data insertion commands into the already existing tables
// 3.1 intensity levels
try
{
cmdCreate.CommandText = "insert into intensities (" +
"id, intensity) values (" +
"1, 'Low');";
cmdCreate.ExecuteNonQuery();
cmdCreate.CommandText = "insert into intensities (" +
"id, intensity) values (" +
"2, 'Medium');";
cmdCreate.ExecuteNonQuery();
cmdCreate.CommandText = "insert into intensities (" +
"id, intensity) values (" +
"3, 'High');";
cmdCreate.ExecuteNonQuery();
}
catch (Exception ex) { }
// 3.2 activities
try {
foreach (Activity a in Data.Activities)
{
cmdCreate.CommandText = "insert into activities (" +
"id, activity) values (" +
a.Id.ToString() + ", '" +
a.Name + "');";
cmdCreate.ExecuteNonQuery();
}
}
catch (Exception ex) { }
// 3.3 entries
foreach (Entry e in Data.Entries)
{
cmdCreate.CommandText = "insert into entries (" +
"id, date, activityid, duration, intensityid, exclude, notes) values (" +
e.Id.ToString() + ", '" +
e.Date.ToString() + "', " +
e.ActivityId.ToString() + ", " +
e.Duration.ToString() + ", " +
"2, null, null);";
cmdCreate.ExecuteNonQuery();
}
// 4. closing connection
connectDb.Close();
}
return(true); // if success
}
}
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 static void InitializeDatabase(string connectionStr)
{
try {
//create database tables
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionStr)) {
//create Tasks table
using (var command = new System.Data.SQLite.SQLiteCommand()) {
command.Connection = conn;
command.CommandText =
"CREATE TABLE IF NOT EXISTS \"Tasks\"" +
"( " +
"\"Id\" TEXT NOT NULL UNIQUE, " +
"\"Title\" TEXT NOT NULL, " +
"\"Description\" TEXT NOT NULL, " +
"\"StartTime\" TEXT NOT NULL, " +
"\"LastNotificationTime\" TEXT NOT NULL, " +
"\"FrequencyType\" INTEGER NOT NULL, " +
"\"R\" INTEGER NOT NULL, " +
"\"G\" INTEGER NOT NULL, " +
"\"B\" INTEGER NOT NULL, " +
"PRIMARY KEY(\"Id\")" +
") ";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
//create Frequencies table
using (var command = new System.Data.SQLite.SQLiteCommand()) {
command.Connection = conn;
command.CommandText =
"CREATE TABLE IF NOT EXISTS \"Frequencies\"" +
"( " +
"\"TaskId\" TEXT NOT NULL UNIQUE, " +
"\"Time\" TEXT NOT NULL, " +
"FOREIGN KEY(\"TaskId\") REFERENCES \"Tasks\"(\"Id\"), " +
"PRIMARY KEY(\"TaskId\") " +
") ";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
//create Notifications table
using (var command = new System.Data.SQLite.SQLiteCommand()) {
command.Connection = conn;
command.CommandText =
"CREATE TABLE IF NOT EXISTS \"Notifications\"( " +
"\"TaskId\" TEXT NOT NULL, " +
"\"Time\" TEXT NOT NULL, " +
"FOREIGN KEY(\"TaskId\") REFERENCES \"Tasks\"(\"Id\"), " +
"PRIMARY KEY(\"TaskId\", \"Time\") " +
")";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
}
}
catch {
}
}
public void removeProject(List<String> paths)
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
for (int i = 0; i < paths.Count; i++)
{
com.CommandText = "DELETE FROM LASTPROJECTS WHERE address = '" + paths[i] + "'";
com.ExecuteNonQuery();
}
con.Close();
}
}
}
public static void Delete(PaymentNote target)
{
using (var cmd = new System.Data.SQLite.SQLiteCommand(DataBase.Instance.sqlConnection))
{
cmd.Parameters.Add(cmd.CreateParameter());
cmd.CommandText = "delete from DebitNotes where Id = " + target.Id;
cmd.ExecuteNonQuery();
}
}
public void insertFile(String file)
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "INSERT INTO LASTPROJECTS (address) Values ('"+ file +"')";
com.ExecuteNonQuery();
con.Close();
}
}
}
public static void WriteOff(PaymentNote target)
{
using (var cmd = new System.Data.SQLite.SQLiteCommand(DataBase.Instance.sqlConnection))
{
cmd.Parameters.Add(cmd.CreateParameter());
cmd.CommandText = "update DebitNotes set WroteOff = DateTime('now','localtime') where Id = " + target.Id;
cmd.ExecuteNonQuery();
}
}
public static bool Create_db()
{
bool bolR;
var con = new System.Data.SQLite.SQLiteConnection();
var cmd = new System.Data.SQLite.SQLiteCommand();
string str_sql;
bolR = true;
Directory.CreateDirectory(mPathWEBAPI + "Data");
if (!File.Exists(mStrSQLiteDBFile))
{
try
{
System.Data.SQLite.SQLiteConnection.CreateFile(mStrSQLiteDBFile);
con = new System.Data.SQLite.SQLiteConnection()
{
ConnectionString = mStrSQLiteConnString
};
con.Open();
// con.ChangePassword(mStrDBPassword)
cmd.Connection = con;
str_sql = Conversions.ToString(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(@"
CREATE TABLE IF NOT EXISTS [users] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[name] VARCHAR(512) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[email] VARCHAR(512) DEFAULT (null),
[role] VARCHAR(512) DEFAULT (null),
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'users';
CREATE UNIQUE INDEX [id]
ON [users] (
[id] ASC
);
INSERT INTO users (username, name, password, role) VALUES ('admin', 'Administrator', '", PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), @"', 'Administrators');
INSERT INTO users (username, name, password, email, role) VALUES ('robs', 'Roberto Gaxiola', '"), PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), @"', '*****@*****.**', 'Administrators');
CREATE TABLE IF NOT EXISTS [tokens] (
[id] INTEGER NOT NULL DEFAULT 1 PRIMARY KEY AUTOINCREMENT,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[userid] INTEGER NOT NULL,
[refresh_token] VARCHAR(1024) NOT NULL,
[status] INTEGER NOT NULL DEFAULT(1),
[ipaddr] VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS [swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'swagger';
INSERT INTO swagger (username, password) VALUES ('admin', '"), PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), "');"));
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
WriteActivityLog(ex.Message, 2);
return(false);
}
finally
{
con.Close();
}
}
try
{
con = new System.Data.SQLite.SQLiteConnection()
{
ConnectionString = mStrSQLiteConnString
};
con.Open();
cmd.Connection = con;
var dtB = con.GetSchema("Columns");
if (dtB.Select("COLUMN_NAME = 'ipaddr' AND TABLE_NAME = 'tokens'").Length == 0)
{
str_sql = "ALTER TABLE tokens ADD COLUMN [ipaddr] VARCHAR(20);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("COLUMN_NAME = 'name' AND TABLE_NAME = 'users'").Length == 0)
{
str_sql = "ALTER TABLE users ADD COLUMN [name] VARCHAR(512);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'validations'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [validations] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[method] VARCHAR(20),
[status] INTEGER,
[statusMsg] TEXT,
[ipaddr] VARCHAR(20),
[userid] INTEGER,
[username] VARCHAR(50),
[role] VARCHAR(512),
[email] VARCHAR(512),
[nbf_date] VARCHAR(256),
[iat_date] VARCHAR(256),
[exp_date] VARCHAR(256),
[nbf] INTEGER,
[iat] INTEGER,
[exp] INTEGER,
[iss] VARCHAR(256),
[aud] VARCHAR(256),
[jti] VARCHAR(1024),
[token] TEXT
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
// con.Close()
}
if (dtB.Select("COLUMN_NAME = 'method' AND TABLE_NAME = 'validations'").Length == 0)
{
str_sql = "ALTER TABLE validations ADD COLUMN [method] VARCHAR(20);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"CREATE TABLE IF NOT EXISTS [validationsbk] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[method] VARCHAR(20),
[status] INTEGER,
[statusMsg] TEXT,
[ipaddr] VARCHAR(20),
[userid] INTEGER,
[username] VARCHAR(50),
[role] VARCHAR(512),
[email] VARCHAR(512),
[nbf_date] VARCHAR(256),
[iat_date] VARCHAR(256),
[exp_date] VARCHAR(256),
[nbf] INTEGER,
[iat] INTEGER,
[exp] INTEGER,
[iss] VARCHAR(256),
[aud] VARCHAR(256),
[jti] VARCHAR(1024),
[token] TEXT
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"INSERT INTO validationsbk
SELECT id,date,requestUri,method,status,statusMsg,ipaddr,userid,username,role,email,nbf_date,iat_date,exp_date,nbf,iat,exp,iss,aud,jti,token
FROM validations;";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"DROP table validations;
ALTER TABLE validationsbk RENAME TO validations;";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'swagger'").Length == 0)
{
str_sql = Conversions.ToString(Operators.ConcatenateObject(Operators.ConcatenateObject(@"CREATE TABLE IF NOT EXISTS [swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'swagger';
INSERT INTO swagger (username, password) VALUES ('admin', '", PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), "');"));
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
// con.Close()
}
if (dtB.Select("TABLE_NAME = 'cardex_swagger'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [cardex_swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[status] INTEGER,
[statusMsg] TEXT,
[username] VARCHAR(50),
[ipaddr] VARCHAR(20)
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'params'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [params] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[swagAuth] INTEGER DEFAULT (0)
);
INSERT INTO params (swagAuth) VALUES (0);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
con.Close();
}
catch (Exception)
{
}
// If mBolAuto = False Then MsgBox("Error durante actualizacion de tablas" & vbCrLf & str_sql & vbCrLf & ex.Message)
finally
{
con.Close();
}
return(bolR);
}
public void MoveQuestion(Question question, int newIndex)
{
int currentIndex = 0; // not the order, the list index.
// All questions, find the question's index
IList<Question> questions = ListQuestions().OrderBy(q => q.Order).ToList();
for (int i = 0; i < questions.Count; i++)
{
if (questions[i].Id == question.Id)
{
currentIndex = i;
break;
}
}
// Move all items after the new index up one
var after = questions.Where(q => q.Order >= newIndex).ToList();
for (int i = 0; i < after.Count; i++)
{
after[i].Order += 1;
}
questions[currentIndex].Order = newIndex;
// Re-order
var ordered = questions.OrderBy(q => q.Order).ToList();
for (int i = 0; i < ordered.Count; i++)
{
ordered[i].Order = i;
}
try
{
using (SqliteConnection connection = new SqliteConnection(Settings.DatabaseConnection))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand(connection))
{
// TODO: put inside a transaction (not WinSQLite friendly)
command.CommandText = @"UPDATE questions SET [order]=@order WHERE id=@id";
foreach (Question questionItem in ordered)
{
SqliteParameter parameter = new SqliteParameter("@id", DbType.Int32);
parameter.Value = questionItem.Id;
command.Parameters.Add(parameter);
parameter = new SqliteParameter("@order", DbType.Int32);
parameter.Value = questionItem.Order;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
}
}
catch (SqliteException ex)
{
Logger.Fatal("Unable to MoveQuestion: \n{0}", ex);
throw;
}
}