public SqliteGisModelReader(string fileName)
{
mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3");
mDataConnection.Open();
SQLiteCommand getModelCommand = mDataConnection.CreateCommand();
getModelCommand.CommandText = "SELECT CorrectionConstant, CorrectionParameter FROM Model";
IDataReader reader = getModelCommand.ExecuteReader();
while (reader.Read())
{
mCorrectionConstant = reader.GetInt32(0);
mCorrectionParameter = reader.GetDouble(1);
}
reader.Close();
List<string> outcomeLabels = new List<string>();
getModelCommand.CommandText = "SELECT OutcomeLabel FROM Outcome ORDER BY OutcomeID";
reader = getModelCommand.ExecuteReader();
while (reader.Read())
{
outcomeLabels.Add(reader.GetString(0));
}
reader.Close();
mOutcomeLabels =outcomeLabels.ToArray();
mGetParameterCommand = mDataConnection.CreateCommand();
mGetParameterCommand.CommandText = "SELECT PredicateParameter.OutcomeID, PredicateParameter.Parameter FROM PredicateParameter INNER JOIN Predicate ON PredicateParameter.PredicateID = Predicate.PredicateID WHERE Predicate.PredicateLabel = ?";
mPredicateParameter = new SQLiteParameter();
mPredicateParameter.DbType = DbType.String;
mPredicateParameter.Size = 255;
mGetParameterCommand.Parameters.Add(mPredicateParameter);
}
public GamesRepository()
{
bool buildSchema = false;
if (!File.Exists(DatabaseFile))
{
Directory.CreateDirectory(DatabasePath);
SQLiteConnection.CreateFile(DatabaseFile);
buildSchema = true;
}
DatabaseConnection = new SQLiteConnection(ConString);
DatabaseConnection.Open();
using (SQLiteCommand com = DatabaseConnection.CreateCommand())
{
com.CommandText =
"PRAGMA automatic_index=FALSE; PRAGMA synchronous=OFF; PRAGMA auto_vacuum=INCREMENTAL; PRAGMA foreign_keys=ON; PRAGMA encoding='UTF-8';";
com.ExecuteScalar();
}
if (buildSchema)
{
using (SQLiteCommand com = DatabaseConnection.CreateCommand())
{
string md = Resource1.MakeDatabase;
com.CommandText = md;
com.ExecuteNonQuery();
}
}
using (SQLiteCommand com = DatabaseConnection.CreateCommand())
{
UpdateDatabase.Update(DatabaseConnection);
}
}
public void TestQuery()
{
var builder = new SQLiteConnectionStringBuilder();
builder.DataSource = "test.db";
using (DbConnection connection = new SQLiteConnection(builder.ToString()))
{
connection.Open();
using (var cmd1 = connection.CreateCommand())
{
cmd1.CommandText = @"SELECT name FROM sqlite_master WHERE type='table' AND name='table_test';";
var reader = cmd1.ExecuteReader();
if (reader.Read())
{
var tableName = reader.GetString(0);
System.Diagnostics.Trace.WriteLine(String.Format("table name={0}", tableName));
}
else
{
using (var cmd2 = connection.CreateCommand())
{
cmd2.CommandText = @"Create Table 'table_test' (num Integer, str)";
cmd2.ExecuteNonQuery();
}
}
}
}
}
public void Cleaup()
{
//drop all the tables from the db
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", DB_FILE_PATH)))
{
connection.Open();
using (SQLiteCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "DROP TABLE records";
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "DROP TABLE datasets";
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "DROP TABLE kvstore";
cmd.ExecuteNonQuery();
}
}
}
/// <summary>Write data with a given key to cache</summary>
/// <param name="key">Key of data to be stored</param>
/// <param name="data">Data to be stored</param>
public void Store(string key, string data)
{
SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File);
try
{
DateTime now = DateTime.Now;
connection.Open();
SQLiteCommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM [InetCache] WHERE [Keyword]=?";
command.Parameters.Add("kw", DbType.String).Value = key;
command.ExecuteNonQuery(); // out with the old
command = connection.CreateCommand();
command.CommandText = "INSERT INTO [InetCache] ([Timestamp], [Keyword], [Content]) VALUES (?, ?, ?)";
command.Parameters.Add("ts", DbType.Int32).Value = now.Year * 10000 + now.Month * 100 + now.Day;
command.Parameters.Add("kw", DbType.String).Value = key;
command.Parameters.Add("dt", DbType.String).Value = data;
command.ExecuteNonQuery(); // in with the new
}
finally
{
connection.Close();
}
}
public int updateItemTable(string key, string value)
{
SQLiteCommand sqliteCmd = sqlite.CreateCommand();
sqliteCmd.CommandText = "UPDATE ItemTable SET VALUE =:value WHERE KEY=:key";
SQLiteParameter parameter = new SQLiteParameter();
try
{
sqlite.Open();
byte[] bytes = System.Text.Encoding.Unicode.GetBytes(value);
sqliteCmd.Parameters.Add("value", DbType.Binary).Value = bytes;
sqliteCmd.Parameters.Add("key", DbType.String).Value = key;
return(sqliteCmd.ExecuteNonQuery());
}
catch (Exception ex)
{
throw new Exception("数据读取报错：" + ex.Message);
}
finally
{
if (sqlite != null)
{
sqliteCmd.Dispose();
sqlite.Close();
}
}
}
/// <summary>
/// コマンドを実行.
/// </summary>
/// <param name="inputCmd">inputed command</param>
/// <param name="conn">SQLite connection</param>
public static void executeCommand(String inputCmd, SQLiteConnection conn)
{
if (inputCmd.ToLower().StartsWith("select"))
{
try
{
SQLiteCommand command = conn.CreateCommand();
command = conn.CreateCommand();
command.CommandText = inputCmd;
SQLiteDataReader reader = command.ExecuteReader();
if (!reader.HasRows)
{
Console.WriteLine("no row selected");
}
int fc = reader.FieldCount;
while (reader.Read())
{
String[] row = new String[fc];
for (int i = 0; i < fc; i++)
{
row[i] = reader.GetValue(i).ToString();
}
Console.WriteLine(String.Join("\t", row));
}
Console.WriteLine();
Console.WriteLine(reader.StepCount + " row(s) selected");
}
catch (SQLiteException e)
{
Console.WriteLine(e.Message);
}
}
}
public MainWindow()
{
InitializeComponent();
if (!File.Exists("pix_database.sqlite"))
{
SQLiteConnection.CreateFile("pix_database.sqlite");
new_db = true;
}
db = new SQLiteConnection("Data Source=pix_database.sqlite;Version=3;");
db.Open();
SQLiteCommand createTables = db.CreateCommand();
String bookTable = "CREATE TABLE IF NOT EXISTS Book" +
"(ISBN TEXT PRIMARY KEY NOT NULL," +
"Title TEXT NOT NULL," +
"Published DATETIME NOT NULL," +
"Author TEXT NOT NULL," +
"Pages INT NOT NULL," +
"Publisher TEXT NOT NULL);";
String courseTable = "CREATE TABLE IF NOT EXISTS CourseBook" +
"(Course TEXT NOT NULL," +
"ISBN TEXT NOT NULL," +
"PRIMARY KEY (Course, ISBN)," +
"FOREIGN KEY (ISBN) REFERENCES Book(ISBN));";
createTables.CommandText = bookTable;
createTables.ExecuteNonQuery();
createTables.Parameters.Clear();
createTables.CommandText = courseTable;
createTables.ExecuteNonQuery();
createTables.Dispose();
if (new_db)
{
SQLiteCommand insertInitial = db.CreateCommand();
String book351 = "INSERT INTO Book VALUES ('0-672-33690-1', 'C# Unleashed', '2013-03-23', 'Bart de Smet', 1700, 'Sams Publishing');" +
"INSERT INTO Book VALUES ('978-0-672-33690-4', 'C# Unleashed', '2013-03-23', 'Bart de Smet', 1700, 'Sams Publishing');";
String course351 = "INSERT INTO CourseBook VALUES ('CSCI351', '978-0-672-33690-4');" +
"INSERT INTO CourseBook VALUES ('CSCI351', '0-672-33690-1');";
insertInitial.CommandText = book351;
insertInitial.ExecuteNonQuery();
insertInitial.Parameters.Clear();
insertInitial.CommandText = course351;
insertInitial.ExecuteNonQuery();
insertInitial.Dispose();
}
}
public static void CreateKaraokeDatabase(
FileInfo theDatabaseFileInfo)
{
SQLiteConnection.CreateFile(theDatabaseFileInfo.FullName);
using (SQLiteConnection theConnection = new SQLiteConnection(
DatabaseLayer.ToConnectionString(theDatabaseFileInfo)))
{
try
{
theConnection.Open();
using (SQLiteCommand theCommand = theConnection.CreateCommand())
{
StringBuilder theCommandText = new StringBuilder();
theCommandText.Append("CREATE TABLE [Settings] (");
theCommandText.Append("[ID] [integer] PRIMARY KEY AUTOINCREMENT,");
theCommandText.Append("[Key] [text],");
theCommandText.Append("[Value] [text])");
theCommand.CommandText = theCommandText.ToString();
theCommand.ExecuteNonQuery();
}
using (SQLiteCommand theCommand = theConnection.CreateCommand())
{
StringBuilder theCommandText = new StringBuilder();
theCommandText.Append("CREATE TABLE [Tracks] (");
theCommandText.Append("[ID] [integer] PRIMARY KEY AUTOINCREMENT,");
theCommandText.Append("[Path] [text],");
theCommandText.Append("[Details] [text],");
theCommandText.Append("[Extension] [text],");
theCommandText.Append("[Rating] [integer] DEFAULT 0,");
theCommandText.Append("[Checksum] [text],");
theCommandText.Append("[ToBeDeleted] [boolean] DEFAULT 0)");
theCommand.CommandText = theCommandText.ToString();
theCommand.ExecuteNonQuery();
}
}
finally
{
theConnection.Close();
}
}
}
internal static void DeleteHopsIngredient(int hopsIngredientId, SQLiteConnection connection)
{
using (SQLiteCommand deleteIngredientCommand = connection.CreateCommand())
{
deleteIngredientCommand.CommandText = "DELETE FROM HopsIngredients WHERE id = @id";
deleteIngredientCommand.Parameters.AddWithValue("id", hopsIngredientId);
deleteIngredientCommand.ExecuteNonQuery();
}
using (SQLiteCommand deleteJunctionCommand = connection.CreateCommand())
{
deleteJunctionCommand.CommandText = "DELETE FROM HopsInRecipe WHERE hopsIngredient = @id";
deleteJunctionCommand.Parameters.AddWithValue("id", hopsIngredientId);
deleteJunctionCommand.ExecuteNonQuery();
}
}
public DataManager(string dbFilePath)
{
if (string.IsNullOrEmpty(dbFilePath) == true)
{
throw new ArgumentNullException("dbFilePath");
}
_fileExists = File.Exists(CommonConst.DBFileName);
_dbFilePath = dbFilePath;
// DBとの接続を開始する。
_conn = new SQLiteConnection("Data Source=" + CommonConst.DBFileName);
// DBに接続する。
_conn.Open();
_command = _conn.CreateCommand();
// DBファイルが新規作成された場合
if (_fileExists == false)
{
// 空のテーブルを作成する。
this.CreateNewTables();
_fileExists = true;
}
// アプリケーション設定
_applicationSettings.amountSplitCharacter = this.GetAmountsSplitCharacter();
_applicationSettings.commentSplitCharacter = this.GetCommentSplitCharacter();
}
/// <summary>
/// Constructs the transaction object, binding it to the supplied connection
/// </summary>
/// <param name="connection">The connection to open a transaction on</param>
/// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param>
internal SQLiteTransaction(SQLiteConnection connection, bool deferredLock)
{
_cnn = connection;
_version = _cnn._version;
_level = (deferredLock == true) ? IsolationLevel.ReadCommitted : IsolationLevel.Serializable;
if (_cnn._transactionLevel++ == 0)
{
try
{
using (SQLiteCommand cmd = _cnn.CreateCommand())
{
if (!deferredLock)
cmd.CommandText = "BEGIN IMMEDIATE";
else
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException)
{
_cnn._transactionLevel--;
_cnn = null;
throw;
}
}
}
public void EnsureDatabaseFileExistsKeepsData()
{
string connectionString = "Data Source={0};New=False;UTF8Encoding=True;Version=3";
string path = Path.GetTempFileName();
File.Delete(path);
string fullConnectionString = string.Format(connectionString, path);
IDataBaseSetupImpl dataBaseSetup = new ProjectsRepository.DataBaseSetup(cfg);
dataBaseSetup.EnsureDatabaseFileExists(path);
using (SQLiteConnection sc = new SQLiteConnection(fullConnectionString))
{
sc.Open();
IDbCommand command = sc.CreateCommand();
command.CommandText = "INSERT INTO Queries (project_id, name,text) VALUES (100,'Test Queries','from Tests As Success')";
command.ExecuteNonQuery();
}
//Call it again to see it destroy data.
dataBaseSetup.EnsureDatabaseFileExists(path);
using (SQLiteConnection sc = new SQLiteConnection(fullConnectionString))
{
sc.Open();
IDbCommand command = sc.CreateCommand();
command.CommandText = "SELECT name,text FROM Queries WHERE project_id = 100";
IDataReader reader = command.ExecuteReader();
Assert.IsTrue(reader.Read(), "Data was no preserved when calling EnsureDatabaseFileExists twice");
Assert.AreEqual("Test Queries", reader.GetValue(0), "Bad data when calling ensure db twice");
Assert.AreEqual("from Tests As Success", reader.GetValue(1), "Bad data when calling ensure db twice");
}
File.Delete(path);
}
public Database()
{
string _dbPath = "MyDatabase.db3";
// Instanciation de notre connexion
SQLiteConnection sqlite_conn = new SQLiteConnection("Data Source=database.sqlite;Version=3;");
// Utilisation de l'API en mode synchrone
this.conn = sqlite_conn;
sqlite_conn.Open();
SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();
// Let the SQLiteCommand object know our SQL-Query:
sqlite_cmd.CommandText = "CREATE TABLE IF NOT EXISTS pass (id INT PRIMARY KEY, login varchar(100), privk varchar(100), pubk varchar(100)) ;";
// Now lets execute the SQL ;-)
sqlite_cmd.ExecuteNonQuery();
/* // Utilisation de l'API asynchrone
* SQLiteAsyncConnection connAsync = new SQLiteAsyncConnection("myDb.db3");
* connection.CreateTableAsync<Personne>();*/
}
private static void MakeTableForListOfFields([JetBrains.Annotations.NotNull][ItemNotNull]
List <FieldDefinition> fields,
[JetBrains.Annotations.NotNull] System.Data.SQLite.SQLiteConnection conn,
[JetBrains.Annotations.NotNull] string tableName)
{
if (fields.Count == 0)
{
throw new LPGException("No fields defined for database");
}
string sql = "CREATE TABLE " + tableName + "(";
foreach (var field in fields)
{
sql += field.Name + " " + field.Type + ",";
}
sql = sql.Substring(0, sql.Length - 1) + ");";
int result;
using (var command = conn.CreateCommand()) {
command.CommandText = sql;
result = command.ExecuteNonQuery();
}
if (result != 0)
{
throw new LPGException("Creating the table " + tableName + " failed.");
}
}
private bool CreateDatabase()
{
SQLiteConnection tempConnection = null;
try
{
SQLiteConnection.CreateFile(ConfigurationManager.AppSettings["dbPath"]);
tempConnection = new SQLiteConnection("Data Source=" + ConfigurationManager.AppSettings["dbPath"] + ";Version=3;");
tempConnection.Open();
SQLiteCommand command = tempConnection.CreateCommand();
command.CommandText = "CREATE TABLE pelaaja (id INTEGER PRIMARY KEY AUTOINCREMENT, etunimi TEXT NOT NULL, sukunimi TEXT NOT NULL, seura TEXT NOT NULL, hinta FLOAT NOT NULL, kuva_url TEXT NULL)";
command.ExecuteNonQuery();
tempConnection.Close();
}
catch(Exception e)
{
errors.Add("Tietokannan luominen epäonnistui");
return false;
}
finally
{
if(tempConnection != null) tempConnection.Close();
}
return true;
}
public void Clear(string name)
{
int procCount = Process.GetProcessesByName("firefox").Length;
if (procCount > 0)
throw new ApplicationException(string.Format("There are {0} instances of Firefox still running",
procCount));
try
{
using (var conn = new SQLiteConnection("Data Source=" + GetFirefoxCookiesFileName()))
{
conn.Open();
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "delete from moz_cookies where name='" + name + "'";
int count = command.ExecuteNonQuery();
}
}
catch (SQLiteException ex)
{
if (
!(ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Busy) ||
ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Locked)))
throw new ApplicationException("The Firefox cookies.sqlite file is locked");
}
}
//[JetBrains.Annotations.NotNull]
//private string MainFilename { get; set; }
public void MakeTableForListOfFields([JetBrains.Annotations.NotNull][ItemNotNull]
List <FieldDefinition> fields,
[JetBrains.Annotations.NotNull] HouseholdKey householdKey,
[JetBrains.Annotations.NotNull] string tableName)
{
if (fields.Count == 0)
{
throw new LPGException("No fields defined for database");
}
string dstFileName = GetFilenameForHouseholdKey(householdKey);
string sql = "CREATE TABLE " + tableName + "(";
foreach (var field in fields)
{
sql += field.Name + " " + field.Type + ",";
}
sql = sql.Substring(0, sql.Length - 1) + ");";
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + dstFileName + ";Version=3;")
) {
conn.Open();
var command = conn.CreateCommand();
command.CommandText = sql;
var result = command.ExecuteNonQuery();
if (result != 0)
{
throw new LPGException("Creating the table " + tableName + " failed.");
}
conn.Close();
}
}
/// <summary>
/// Takes a GIS model and a file and writes the model to that file.
/// </summary>
/// <param name="model">
/// The GisModel which is to be persisted.
/// </param>
/// <param name="fileName">
/// The name of the file in which the model is to be persisted.
/// </param>
public void Persist(GisModel model, string fileName)
{
Initialize(model);
PatternedPredicate[] predicates = GetPredicates();
if ( File.Exists(fileName))
{
File.Delete(fileName);
}
using (mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";New=True;Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3"))
{
mDataConnection.Open();
mDataCommand = mDataConnection.CreateCommand();
CreateDataStructures();
using (mDataTransaction = mDataConnection.BeginTransaction())
{
mDataCommand.Transaction = mDataTransaction;
CreateModel(model.CorrectionConstant, model.CorrectionParameter);
InsertOutcomes(model.GetOutcomeNames());
InsertPredicates(predicates);
InsertPredicateParameters(model.GetOutcomePatterns(), predicates);
mDataTransaction.Commit();
}
mDataConnection.Close();
}
}
private void loadHeroDiary()
{
// Load the latest Hero Diary entries
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + dbPath + @"\gv.db;Version=3;New=False;Compress=True;"))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
string commandText = "select Diary_ID as ID, Updated, EntryTime, Entry from Diary where HeroName=@HeroName order by Diary_ID desc limit 1000";
cmd.CommandText = commandText;
cmd.Parameters.AddWithValue("@HeroName", this.HeroName);
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataSet ds = new DataSet();
da = new SQLiteDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = ds.Tables[0];
grdDiary.DataSource = bindingSource;
grdDiary.AutoGenerateColumns = true;
grdDiary.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;
grdDiary.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
}
}
}
public static bool Add(dtoNhanVien nvsx)
{
var cnn = new SQLiteConnection(_connString);
cnn.Open();
using (var cmd = cnn.CreateCommand())
{
cmd.CommandText = "insert into NhanVien (ID,hoTen,ngaySinh,diaChi,tienLuong,ngheNghiep,thuocTinhKhac) values(@ID,@hoTen,@ngaySinh,@diaChi,@tienLuong,@ngheNghiep,@thuocTinhKhac)";
//Parameters
cmd.Parameters.Add(new SQLiteParameter("@ID"));
cmd.Parameters.Add(new SQLiteParameter("@hoTen"));
cmd.Parameters.Add(new SQLiteParameter("@ngaySinh"));
cmd.Parameters.Add(new SQLiteParameter("@diaChi"));
cmd.Parameters.Add(new SQLiteParameter("@tienLuong"));
cmd.Parameters.Add(new SQLiteParameter("@ngheNghiep"));
cmd.Parameters.Add(new SQLiteParameter("@imagePath"));
cmd.Parameters.Add(new SQLiteParameter("@thuocTinhKhac"));
cmd.Parameters["@ID"].Value = nvsx.Msnv;
cmd.Parameters["@hoTen"].Value = nvsx.HoTen;
cmd.Parameters["@ngaySinh"].Value = nvsx.NgaySinh;
cmd.Parameters["@diaChi"].Value = nvsx.DiaChi;
cmd.Parameters["@tienLuong"].Value = nvsx.TienLuong;
cmd.Parameters["@ngheNghiep"].Value = nvsx.NgheNghiep;
cmd.Parameters["@imagePath"].Value = nvsx.NgheNghiep;
cmd.Parameters["@thuocTinhKhac"].Value = nvsx.XmlThuocTinhRieng;
//Excute
cmd.ExecuteNonQuery();
}
return true;
}
public static Toimipiste Hae(int toimipisteId)
{
Toimipiste toimipiste = null;
DataSet ds = new DataSet();
SQLiteConnection conn = new SQLiteConnection(Properties.Settings.Default.Database);
conn.Open();
SQLiteCommand comm = conn.CreateCommand();
comm.CommandText = "SELECT id, nimi, lahiosoite, postitoimipaikka, postinro, email, puhelinnro FROM toimipiste WHERE id = $id";
comm.Parameters.AddWithValue("$id", toimipisteId);
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(comm.CommandText, conn))
{
adapter.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
toimipiste = new Toimipiste();
toimipiste.Id = int.Parse(row["id"].ToString());
toimipiste.Nimi = row["nimi"].ToString();
toimipiste.ParsiOsoite(row);
}
}
conn.Close();
return toimipiste;
}
public static dtoNhanVien Search(int msnv)
{
var cnn = new SQLiteConnection(_connString);
cnn.Open();
var cmd = cnn.CreateCommand();
cmd.CommandText = "select * from NhanVien where ID = @ID";
// Parameters
cmd.Parameters.Add(new SQLiteParameter("@ID"));
cmd.Parameters["@ID"].Value = msnv;
var reader = cmd.ExecuteReader();
dtoNhanVien dtoNhanVien = null;
if (reader.Read())
{
dtoNhanVien = new dtoNhanVien();
dtoNhanVien.Msnv = int.Parse(reader[0].ToString());
dtoNhanVien.HoTen = reader[1].ToString();
dtoNhanVien.NgaySinh = (DateTime)reader[2];
dtoNhanVien.DiaChi = reader[3].ToString();
dtoNhanVien.TienLuong = float.Parse(reader[4].ToString());
dtoNhanVien.NgheNghiep = reader[5].ToString();
//dtoNhanVien.ImagePath = reader[6].ToString();
dtoNhanVien.XmlThuocTinhRieng = reader[6].ToString();
}
cnn.Close();
return dtoNhanVien;
}
/// <summary>
/// delete specific cookie
/// </summary>
/// <param name="id"></param>
public static void DeleteCookie(object id)
{
if (!string.IsNullOrEmpty(GetFFCookiePath()))
{
using (var conn = new SQLiteConnection("Data Source=" + GetFFCookiePath()))
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = "delete from moz_cookies where id=" + id;
conn.Open();
int res = cmd.ExecuteNonQuery();
if (res != 1)
{ }
}
finally
{
cmd.Dispose();
conn.Close();
}
}
}
}
}
public int InstallDatabase()
{
int res = -1;
if (!File.Exists(DBParameter.data_source))
{
SQLiteConnection.CreateFile(DBParameter.data_source);
string connectionString = "Data Source=" + DBParameter.data_source;
connection = new SQLiteConnection(connectionString);
//Open connection
if (this.OpenConnection() == true)
{
try
{
using (connection)
{
//Create Command
using (SQLiteCommand comm = connection.CreateCommand())
{
comm.CommandText = DellContactsScript.Script;
res = comm.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
//
}
//close Connection
this.CloseConnection();
}
}
return res;
}
public static void ClearHistory()
{
if (!string.IsNullOrEmpty(GetFFHistoryPath()))
{
try
{
using (var conn = new SQLiteConnection("Data Source=" + GetFFHistoryPath()))
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = "delete from moz_places where hidden = 0; delete from moz_historyvisits where place_id not in (select id from moz_places where hidden = 0);";
conn.Open();
int res = cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
conn.Close();
}
}
}
}
catch
{ }
}
}
public void Execute(Action <SqliteConnection, SqliteCommand, SqliteTransaction> ExcuteQuery)
{
Lock = true;
using (var conn = new SqliteConnection()
{
ConnectionString = this.ConnectString
})
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
using (var trans = conn.BeginTransaction())
{
try
{
ExcuteQuery(conn, cmd, trans);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
}
}
conn.Close();
}
Lock = false;
}
/// <summary>
/// Initializes a new instance of the <b>DBCommand</b> class.
/// </summary>
/// <param name="con"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
private DBCommand( SQLiteConnection con, CommandType commandType, string commandText )
{
_con = con;
cmd = _con.CreateCommand();
cmd.CommandType = commandType;
cmd.CommandText = commandText;
}
public static SQLiteConnection CreateConnection()
{
// check and create database
// Tao db neu chua ton tai
var dataPath = AppDomain.CurrentDomain.BaseDirectory ;
var connString = @"Data Source=" + AppDomain.CurrentDomain.BaseDirectory + DatabaseName;
SQLiteConnection cnn;
if (!File.Exists(dataPath + DatabaseName))
{
// Create database
SQLiteConnection.CreateFile(dataPath + DatabaseName);
cnn = new SQLiteConnection(connString);
cnn.Open();
var cmd = cnn.CreateCommand();
cmd.CommandText = DataCreator;
cmd.ExecuteNonQuery();
cnn.Close();
}
else
{
cnn = new SQLiteConnection(connString);
}
return cnn;
}
/// <summary>Initialization ctor</summary>
/// <param name="file">Name of database file, will be created if not existing</param>
public WebCache(string file = "Cache.dat")
{
_File = file;
if (!System.IO.File.Exists(_File))
{
SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File);
try
{
connection.Open();
string[] inits = new string[]{
"CREATE TABLE [InetCache] ([Id] INTEGER PRIMARY KEY, [Timestamp] INTEGER, [Keyword] TEXT NOT NULL, [Content] TEXT NOT NULL)",
"CREATE INDEX [IDX_InetCache] ON [InetCache] ([Keyword])"
};
SQLiteCommand command = connection.CreateCommand();
foreach (string cmd in inits)
{
command.CommandText = cmd;
command.ExecuteNonQuery();
}
}
finally
{
connection.Close();
}
}
}
public override void update()
{
//Create a Dictionary to hold the receivers saved in the database
Dictionary<string, Receiver> receivers = new Dictionary<string, Receiver>();
//Create a new connection to the database
using (SQLiteConnection m_dbConnection = new SQLiteConnection(@"Data Source=database.sqlite;Version=3;"))
{
//Open database connection
m_dbConnection.Open();
using (SQLiteCommand command = m_dbConnection.CreateCommand())
{
//Select everything from the 'receivers' table
SQLiteCommand getReceivers = new SQLiteCommand("SELECT * FROM receivers", m_dbConnection);
SQLiteDataReader reader = getReceivers.ExecuteReader();
//Read every entry in the receivers table
while (reader.Read())
{
string name = (string)reader["name"];
Receiver battery = new Receiver((string)reader["name"], (double)reader["weight"], (int)reader["channelCount"], (int)reader["minVoltage"], (int)reader["maxVoltage"]);
//Add the battery into the dictionary using the name as the key and a new Receiver object as the value
receivers.Add(name, battery);
}
}
//Close the database connection
m_dbConnection.Close();
}
//Save the updated savedReceiver list
savedReceivers = receivers;
}
public static DataTable SearchMusicDatabase(
FileInfo theDatabaseFileInfo,
string theCriteria)
{
using (DataSet theDataSet = new DataSet())
{
using (SQLiteConnection theConnection = new SQLiteConnection(
DatabaseLayer.ToConnectionString(theDatabaseFileInfo)))
{
try
{
theConnection.Open();
using (SQLiteCommand theCommand = theConnection.CreateCommand())
{
StringBuilder theCommandBuilder = new StringBuilder(
"SELECT [ID], [Path], [Details], [Extension], 0, " +
"[Path] || '\\' || [Details] || [Extension] AS [FullPath] ");
theCommandBuilder.Append("FROM [Tracks] ");
theCommandBuilder.Append("WHERE ");
bool IsFirstParameter = true;
foreach (string thisCriteria in theCriteria.Split(' '))
{
if (!IsFirstParameter)
{
theCommandBuilder.Append(" AND ");
}
theCommandBuilder.Append("([Details] LIKE ? OR [Path] LIKE ?)");
SQLiteParameter thisCriteriaParameter = theCommand.CreateParameter();
theCommand.Parameters.Add(thisCriteriaParameter);
theCommand.Parameters.Add(thisCriteriaParameter);
thisCriteriaParameter.Value = String.Format(
CultureInfo.CurrentCulture,
"%{0}%",
thisCriteria.ToString());
IsFirstParameter = false;
}
theCommandBuilder.Append(" ORDER BY [Path], [Details]");
theCommand.CommandText = theCommandBuilder.ToString();
using (SQLiteDataAdapter theAdapter = new SQLiteDataAdapter(theCommand))
{
theAdapter.Fill(theDataSet);
}
}
}
finally
{
theConnection.Close();
}
}
return theDataSet.Tables[0];
}
}
public Notification GetNotification(ref string strError)
{
Notification notification = new Notification();
try
{
using (var conn = new SQLiteConnection(ConnectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "SELECT * FROM Notification WHERE NotificationPK = 1";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Notification n = new Notification()
{
EmailAddress = reader.GetString(reader.GetOrdinal("EmailAddress")),
PhoneNumber = reader.GetString(reader.GetOrdinal("PhoneNumber")),
NotificationType = reader.GetString(reader.GetOrdinal("NotificationType")),
CarrierGateway = reader.GetString(reader.GetOrdinal("CarrierGateway")),
Carrier = reader.GetString(reader.GetOrdinal("Carrier"))
};
notification = n;
}
}
}
}
catch (Exception ex)
{
strError = ex.Message;
}
return notification;
}
public void create(string file)
{
db_file = file;
conn = new SQLiteConnection("Data Source=" + db_file);
if (!File.Exists(db_file))
{
conn.Open();
using (SQLiteCommand command = conn.CreateCommand())
{
command.CommandText = @"CREATE TABLE `erogamescape` (
`id` INTEGER NOT NULL,
`title` TEXT,
`saleday` TEXT,
`brand` TEXT,
PRIMARY KEY(id));
CREATE TABLE `tableinfo` (
`tablename` TEXT NOT NULL,
`version` INTEGER,
PRIMARY KEY(tablename))";
command.ExecuteNonQuery();
command.CommandText = @"INSERT INTO tableinfo VALUES('erogamescape',0)";
command.ExecuteNonQuery();
}
conn.Close();
}
}
public EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None)
{
_databaseFullPath = databaseFullPath;
_destructorType = destructorType;
_connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath);
if (!File.Exists(_databaseFullPath))
{
SQLiteConnection.CreateFile(_databaseFullPath);
Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath);
}
else
{
Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath);
}
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
Log.Verbose("About to create or check for the Entities table in the EntityStore database.");
cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database.");
}
}
}
public void TrimSQLiteDatabase(string filename, string tablename, int MaxRows, string TestColumnName)
{
if (!Program.ENABLE_SQLITE)
{
return;
}
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", filename);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
int ret = 0; string olddate;
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
cmd.CommandText = @"SELECT COUNT(*) AS NumberOfRows FROM " + tablename;
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "NumberOfRows"
});
//int ret = cmd.ExecuteNonQuery();
SQLiteDataReader rdr = cmd.ExecuteReader();
rdr.Read();
ret = rdr.GetInt32(0); //get the number from return parameter position 0
rdr.Close();
if (ret > MaxRows)
{
//obtain the minimum value for the test column (usually a date field)
cmd.CommandText = @"SELECT MIN(" + TestColumnName + ") AS OldestDate FROM " + tablename;
cmd.Parameters.Clear();
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "OldestDate"
});
SQLiteDataReader rdr2 = cmd.ExecuteReader();
rdr2.Read();
olddate = rdr2.GetString(0);
rdr2.Close();
//delete all rows containing that minimum test value
cmd.CommandText = @"DELETE FROM " + tablename + " WHERE " + TestColumnName + " = '" + olddate + "'";
cmd.Parameters.Clear();
cmd.ExecuteNonQuery();
}
}
Program.logEvent("Local SQLite Database Table " + tablename + " Trimmed Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
}
/// <summary>
/// SQLを直接発行してDBにアクセス。
/// DB以外のパッケージからは使用しないこと
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public SQLiteDataReader ExecuteReader(string sql)
{
SQLiteCommand cmd = conn_.CreateCommand();
cmd.CommandText = sql;
var reader = cmd.ExecuteReader();
return(reader);
}
//CREATE TABLE cookies(creation_utc INTEGER NOT NULL UNIQUE PRIMARY KEY, host_key TEXT NOT NULL, name TEXT NOT NULL, value TEXT NOT NULL, path TEXT NOT NULL,
//expires_utc INTEGER NOT NULL, secure INTEGER NOT NULL, httponly INTEGER NOT NULL, last_access_utc INTEGER NOT NULL, has_expires INTEGER NOT NULL DEFAULT 1,
//persistent INTEGER NOT NULL DEFAULT 1, priority INTEGER NOT NULL DEFAULT 1, encrypted_value BLOB DEFAULT '', firstpartyonly INTEGER NOT NULL DEFAULT 0)
static public IEnumerable <Cookie> ReadCookies()
{
var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies";
if (!System.IO.File.Exists(dbPath))
{
throw new System.IO.FileNotFoundException("Can't find cookie store", dbPath);
}
var connectionString = "Data Source=" + dbPath;
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
var prm = cmd.CreateParameter();
cmd.Parameters.Add(prm);
cmd.CommandText = "SELECT * FROM cookies;Read Only=True";
//cmd.CommandText = "PRAGMA table_info(cookies)";
//cmd.CommandText = "SELECT sql FROM sqlite_master WHERE tbl_name = 'cookies' AND type = 'table'";
conn.Open();
try
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var decoded_value = System.Security.Cryptography.ProtectedData.Unprotect((byte[])reader["encrypted_value"], null, System.Security.Cryptography.DataProtectionScope.CurrentUser);
var ascii_value = Encoding.ASCII.GetString(decoded_value);
yield return(new Cookie()
{
creation_utc = (System.Int64)reader["creation_utc"],
host_key = (string)reader["host_key"],
name = (string)reader["name"],
value = (string)reader["value"],
path = (string)reader["path"],
expires_utc = (System.Int64)reader["expires_utc"],
secure = (System.Int64)reader["secure"],
httponly = (System.Int64)reader["httponly"],
last_access_utc = (System.Int64)reader["last_access_utc"],
has_expires = (System.Int64)reader["has_expires"],
persistent = (System.Int64)reader["persistent"],
priority = (System.Int64)reader["priority"],
AsciiDecryptedValue = ascii_value,
firstpartyonly = (System.Int64)reader["firstpartyonly"]
});
}
}
}
finally
{
conn.Close();
}
}
}
public List <CookieItem> ReadChromeCookies(string hostName)
{
if (hostName == null)
{
throw new ArgumentNullException("hostName");
}
List <CookieItem> ret = new List <CookieItem>();
var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies";
if (!System.IO.File.Exists(dbPath))
{
throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); // race condition, but i'll risk it
}
var connectionString = "Data Source=" + dbPath + ";pooling=false";
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
var prm = cmd.CreateParameter();
prm.ParameterName = "hostName";
prm.Value = hostName;
cmd.Parameters.Add(prm);
cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key like '%" + hostName + "%'";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var name = reader[0].ToString();
var encryptedData = (byte[])reader[1];
string encKey = File.ReadAllText(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Local State");
encKey = JObject.Parse(encKey)["os_crypt"]["encrypted_key"].ToString();
var decodedKey = System.Security.Cryptography.ProtectedData.Unprotect(Convert.FromBase64String(encKey).Skip(5).ToArray(), null, System.Security.Cryptography.DataProtectionScope.LocalMachine);
var _cookie = _decryptWithKey(encryptedData, decodedKey, 3);
if (ret.Find(x => x.Name == name) == null)
{
ret.Add(new CookieItem
{
Name = name,
Value = _cookie
});
}
}
}
conn.Close();
}
return(ret);
}
private static void GetItemsFromDatabase()
{
List <int> numberOfPeakMatchesList = new List <int>();
List <string> toDatabaseList = new List <string>();
var Binner = new Binner();
using (
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection("data source=" + LibraryDBPath + ".db3"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "ATTACH '" + QueryDBPath + ".db3' AS db2;";
cmd.ExecuteNonQuery();
cmd.CommandText = "ATTACH '" + QueryLibraryPairsDB + ".db3' AS dbPair;";
cmd.ExecuteNonQuery();
//This select statement is equivalent to if I had used inner joins between the tables. This version is just in an older style.
cmd.CommandText = "SELECT main.Spectrum.mzList, main.Spectrum.charge, main.Spectrum.precursor, main.Spectrum.peptide, main.Files.path, " +
"db2.QuerySpectrum.mzList, db2.QuerySpectrum.charge, db2.QuerySpectrum.precursor, db2.Files.path " +
"FROM main.Spectrum, main.Files, db2.Files, db2.QuerySpectrum, dbPair.QueryLibraryPair " +
"WHERE main.Spectrum.ID = dbPair.QueryLibraryPair.id_Library AND db2.QuerySpectrum.ID = dbPair.QueryLibraryPair.id_Query " +
"AND db2.QuerySpectrum.codex = db2.Files.codex AND main.Spectrum.codex = main.Files.codex " +
"AND dbPair.QueryLibraryPair.PeakMatchNumber<11;";
using (var reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult))
{
while (reader.Read())
{
Library oneLibrary = new Library(Convert.ToInt32(reader.GetString(1)), reader.GetString(3), reader.GetString(0).Split(',').Select(n => Convert.ToDouble(n)).ToArray(), reader.GetString(4), Convert.ToDouble(reader.GetString(2)));
Query oneQuery = new Query(Convert.ToInt32(reader.GetString(6)), reader.GetString(5).Split(',').Select(n => Convert.ToDouble(n)).ToArray(), reader.GetString(8), Convert.ToDouble(reader.GetString(7)));
listOfAllLibraries.Add(oneLibrary);
listOfAllQueries.Add(oneQuery);
if (listOfAllLibraries.Count % 50 == 0)
{
Console.WriteLine("processed: " + listOfAllLibraries.Count);
}
if (listOfAllLibraries.Count >= 500)
//if(listOfAllLibraries.Count %1000 ==0)
{
Binner.processor(listOfAllQueries, listOfAllLibraries);
listOfAllLibraries = new List <Library>();
listOfAllQueries = new List <Query>();
// break;
}
}
}
Binner.sendToFile(outputFilename);
}
conn.Close();
}
}
private void executeCommand(Action <SQLiteCommand> commandAction)
{
using (SQLiteConnection database = new System.Data.SQLite.SQLiteConnection("Data Source = quizard.db"))
{
database.Open();
using (SQLiteCommand command = database.CreateCommand())
{
commandAction.Invoke(command);
}
}
}
public void InsertValues(string table, IEnumerable <string> values, bool replace = false)
{
var sb = new StringBuilder();
sb.Append("INSERT OR ");
sb.Append(replace ? "REPLACE" : "IGNORE");
sb.Append(" INTO ");
sb.Append(table);
sb.Append(" VALUES(");
foreach (string value in values)
{
if (value == null)
{
sb.Append("NULL");
}
else
{
bool isNumber = value.Length > 0;
foreach (char c in value)
{
if (!Char.IsNumber(c))
{
isNumber = false;
break;
}
}
if (!isNumber)
{
sb.Append("'");
}
sb.Append(value.Replace("'", "''"));
if (!isNumber)
{
sb.Append("'");
}
}
sb.Append(",");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(");");
string sql = sb.ToString();
sb.Clear();
sb = null;
using (var command = m_Connection.CreateCommand())
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
public List <CookieItem> ReadCookies(string hostName)
{
if (hostName == null)
{
throw new ArgumentNullException("hostName");
}
List <CookieItem> ret = new List <CookieItem>();
var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies";
if (!System.IO.File.Exists(dbPath))
{
throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); // race condition, but i'll risk it
}
var connectionString = "Data Source=" + dbPath + ";pooling=false";
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
var prm = cmd.CreateParameter();
prm.ParameterName = "hostName";
prm.Value = hostName;
cmd.Parameters.Add(prm);
cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key like '%" + hostName + "%'";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var name = reader[0].ToString();
var encryptedData = (byte[])reader[1];
var decodedData = System.Security.Cryptography.ProtectedData.Unprotect(encryptedData, null, System.Security.Cryptography.DataProtectionScope.CurrentUser);
var plainText = Encoding.ASCII.GetString(decodedData); // Looks like ASCII
if (ret.Find(x => x.Name == name) == null)
{
ret.Add(new CookieItem
{
Name = name,
Value = plainText
});
}
}
}
conn.Close();
}
return(ret);
}
public void SaveDictionaryToDatabaseNewConnection([ItemNotNull][JetBrains.Annotations.NotNull]
List <Dictionary <string, object> > values,
[JetBrains.Annotations.NotNull] string tableName,
[JetBrains.Annotations.NotNull] HouseholdKey householdKey)
{
string sql = "Insert into " + tableName + "(";
string fields = "";
string parameters = "";
foreach (KeyValuePair <string, object> pair in values[0])
{
fields += pair.Key + ",";
parameters += "@" + pair.Key + ",";
}
fields = fields.Substring(0, fields.Length - 1);
parameters = parameters.Substring(0, parameters.Length - 1);
sql += fields + ") VALUES (" + parameters + ")";
string dstFileName = GetFilenameForHouseholdKey(householdKey);
using (System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection("Data Source=" + dstFileName + ";Version=3;Synchronous=OFF;Journal Mode=WAL;")) {
conn.Open();
using (var transaction = conn.BeginTransaction()) {
var command = conn.CreateCommand();
command.CommandText = sql;
foreach (Dictionary <string, object> row in values)
{
if (row.Count != values[0].Count)
{
throw new LPGException("Incorrect number of columns");
}
command.Parameters.Clear();
foreach (KeyValuePair <string, object> pair in row)
{
string parameter = "@" + pair.Key;
command.Parameters.AddWithValue(parameter, pair.Value);
}
command.ExecuteNonQuery();
}
transaction.Commit();
}
conn.Close();
}
}
//[JetBrains.Annotations.NotNull]
//public string ReturnMainSqlPath() => _filenameByHouseholdKey[Constants.GeneralHouseholdKey].Filename;
private static void SaveDictionaryToDatabase([NotNull][ItemNotNull] List <Dictionary <string, object> > values,
[NotNull] string tableName,
[NotNull] System.Data.SQLite.SQLiteConnection conn)
{
if (values.Count == 0)
{
return;
}
//figure out sql
var firstrow = values[0];
string sql = "Insert into " + tableName + "(";
string fields = "";
string parameters = "";
foreach (KeyValuePair <string, object> pair in firstrow)
{
fields += pair.Key + ",";
parameters += "@" + pair.Key + ",";
}
fields = fields.Substring(0, fields.Length - 1);
parameters = parameters.Substring(0, parameters.Length - 1);
sql += fields + ") VALUES (" + parameters + ")";
//execute the sql
using (var transaction = conn.BeginTransaction()) {
using (var command = conn.CreateCommand()) {
command.CommandText = sql;
foreach (Dictionary <string, object> row in values)
{
if (row.Count != firstrow.Count)
{
throw new LPGException("Incorrect number of columns");
}
command.Parameters.Clear();
foreach (KeyValuePair <string, object> pair in row)
{
string parameter = "@" + pair.Key;
command.Parameters.AddWithValue(parameter, pair.Value);
}
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
private static bool GetCookie_Chrome(string strHost, string strField, ref string Value)
{
Value = string.Empty;
bool fRtn = false;
string strPath, strDb;
// Check to see if Chrome Installed
strPath = GetChromeCookiePath();
if (string.Empty == strPath) // Nope, perhaps another browser
{
return(false);
}
try
{
strDb = "Data Source=" + strPath + ";pooling=false";
using (SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(strDb))
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT value FROM cookies WHERE host_key LIKE '%" + strHost + "%' AND name LIKE '%" + strField + "%';";
conn.Open();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Value = reader.GetString(0);
if (!Value.Equals(string.Empty))
{
fRtn = true;
break;
}
}
}
conn.Close();
}
}
}
catch (Exception)
{
Value = string.Empty;
fRtn = false;
}
return(fRtn);
}
/// <summary>
/// Opret forbindelse til databasen
/// </summary>
public static bool Connect(string connectionString, string prefix = "")
{
if (IsConnected)
{
return(false);
}
//Start connection
sql_conn = new SQLite_LIB.SQLiteConnection(connectionString);
sql_conn.Open();
sql_cmd = sql_conn.CreateCommand();
_prefix = prefix;
_isConnected = true;
return(true);
}
static public IEnumerable <Tuple <string, string> > ReadCookies(string hostName)
{
if (hostName == null)
{
throw new ArgumentNullException("hostName");
}
var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies";
if (!System.IO.File.Exists(dbPath))
{
throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); // race condition, but i'll risk it
}
var connectionString = "Data Source=" + dbPath + ";pooling=false";
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
var prm = cmd.CreateParameter();
prm.ParameterName = "hostName";
prm.Value = hostName;
cmd.Parameters.Add(prm);
//cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key = @hostName";
cmd.CommandText = "SELECT name,encrypted_value,host_key FROM cookies";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var encryptedData = (byte[])reader[1];
var decodedData = System.Security.Cryptography.ProtectedData.Unprotect(encryptedData, null, System.Security.Cryptography.DataProtectionScope.CurrentUser);
var plainText = Encoding.ASCII.GetString(decodedData); // Looks like ASCII
if (Regex.IsMatch(reader.GetString(2), hostName))
{
yield return(Tuple.Create(reader.GetString(2) + ":" + reader.GetString(0), plainText));
}
}
}
conn.Close();
}
}
private List <string> RetrieveLateIDNumbers()
{
///
///Retrieve unfinished transactions from LOCAL SQLite DATABASE
///
List <string> CardNumbers = new List <string>();
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
// -------------------------------
// Current KioskData.db data
// -------------------------------
cmd.CommandText = @"SELECT * FROM RES_KEY WHERE (ID_CARD_NO_IN is null or ID_CARD_NO_IN = '')";
SQLiteDataReader r = cmd.ExecuteReader();
while (r.Read())
{
DateTime Datetime = Convert.ToDateTime(r["OUT_DATETIME"]);
//Select Card records which have a Date of today, yesterday or two days ago. This way records which have been accidentally created will not continue to send reminders forever
if ((Datetime.Date == DateTime.Today) || (Datetime.Date == DateTime.Today - new TimeSpan(1, 0, 0, 0)) || (Datetime.Date == DateTime.Today - new TimeSpan(2, 0, 0, 0)))
{
CardNumbers.Add(Convert.ToString(r["ID_CARD_NO_OUT"]));
}
}
dbConn.Close();
}
Program.logEvent("Local SQLite Database Transaction Inserted Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
return(CardNumbers);
}
/// <summary>
/// Ctor
/// </summary>
public Bot()
{
SDB = new SQLiteConnection();
SDB.ConnectionString = BuildConString(@"D:\AIML\ConsoleBot\bin\Debug\AIML.aidb", 8192, 3, 8192, 8192, true, false);
SDB.Open();
using (SQLiteTransaction tr = SDB.BeginTransaction())
{
using (SQLiteCommand cmd = SDB.CreateCommand())
{
cmd.CommandText = "PRAGMA temp_store = 1";
cmd.ExecuteNonQuery();
cmd.CommandText = "PRAGMA count_changes = OFF";
cmd.ExecuteNonQuery();
cmd.CommandText = "PRAGMA page_size=8192";
cmd.ExecuteNonQuery();
}
}
this.setup();
}
public override List <WebVisit> RecentStackoverflow(string dbPath)
{
var list = new List <WebVisit>();
using (var connection = new System.Data.SQLite.SQLiteConnection("Data Source=" + dbPath + ";Version=3;Read Only=True"))
{
connection.Open();
var command =
@"SELECT urls.url, visits.visit_time, urls.title
FROM visits, urls
WHERE visits.url = urls.id AND urls.url LIKE '%stackoverflow%'
";
using (var c = connection.CreateCommand())
{
c.CommandText = command;
var reader = c.ExecuteReader();
var urls = new HashSet <string>();
while (reader.Read())
{
var url = reader.GetString(0);
var timeEpoch = reader.GetInt64(1) / 1000;
var visitTime = FromGoogleTime(timeEpoch);
var title = reader.GetString(2);
if (!urls.Contains(url))
{
list.Add(new WebVisit()
{
Url = url, Timestamp = visitTime, Title = title
});
}
urls.Add(url);
}
}
connection.Close();
}
return(list);
}
static void Main(string[] args)
{
string filter = "";
string path = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Login Data";
string db = "Data Source=" + path + ";pooling=false";
if (args.Length >= 1)
{
filter = args[0];
Console.WriteLine("Filter is {0}", filter);
}
Console.WriteLine("DS is located at {0}", path);
SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(db);
SQLiteCommand cmd = conn.CreateCommand();
if (filter.Equals(""))
{
cmd.CommandText = "SELECT action_url, username_value, password_value FROM logins";
}
else
{
cmd.CommandText = "SELECT action_url, username_value, password_value FROM logins WHERE action_url LIKE '%" + filter + "%'";
}
conn.Open();
SQLiteDataReader result = cmd.ExecuteReader();
while (result.Read())
{
byte[] data = (byte[])result[2];
byte[] plaintext = System.Security.Cryptography.ProtectedData.Unprotect(data, null, System.Security.Cryptography.DataProtectionScope.CurrentUser);
Console.WriteLine("{0}:{1}={2}", result.GetString(0), result.GetString(1), Encoding.ASCII.GetString(plaintext));
Console.Read();
}
conn.Close();
}
public void GetAllClassesTest()
{
QuizardDatabase db = null;
Assert.DoesNotThrow(delegate
{
db = new QuizardDatabase();
db.Open();
if (!File.Exists("quizard.db"))
{
int x = db.buildDB();
Assert.AreEqual(x, 0);
}
});
Console.WriteLine("Database created");
List <Class> allClasses = db.GetAllClasses();
Console.WriteLine("Query successful");
Console.WriteLine("Class count = " + allClasses.Count);
Assert.DoesNotThrow(delegate
{
using (SQLiteConnection database = new System.Data.SQLite.SQLiteConnection("Data Source = quizard.db"))
{
database.Open();
using (SQLiteCommand command = database.CreateCommand())
{
Console.WriteLine("SELECT COUNT(*) FROM classes;");
command.CommandText = "SELECT COUNT(*) FROM classes;";
Assert.AreEqual(command.ExecuteScalar(), allClasses.Count);
Console.WriteLine("Count was equal");
}
}
});
}
public bool ErrorDatabaseEntry(string Reservation, string Operator, string SQLFailTimeStamp, string KioskNum, int FailErrorFlag, string Generic1, string Generic2, int Generic3)
{
if (!Program.ENABLE_SQLITE)
{
return(true);
}
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO RES_KEY_FAIL
(
RESV_RESERV_NO,
OPERATOR_NO,
FAIL_DATETIME,
SQL_FAIL_TIME_STAMP,
KIOSK_FAIL_NO,
FAIL_ERROR_FLAG,
COL_GENERIC_1,
COL_GENERIC_2,
COL_GENERIC_3
)
VALUES
(
@Reservation,
@Operator,
@FailDateTime,
@SQLFailDateTime,
@KioskID,
@FailErrFlag,
@Generic1,
@Generic2,
@Generic3
)";
//parameterized update - more flexibility on parameter creation
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@Reservation",
Value = Reservation
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@Operator",
Value = Operator
});
// SQLite date format is: yyyy-MM-dd HH:mm:ss
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@FailDateTime",
Value = String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now)
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@SQLFailDateTime",
Value = SQLFailTimeStamp
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KioskID",
Value = Program.KIOSK_ID
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@FailErrFlag",
Value = FailErrorFlag
});
string gen1;
if (Generic1.Length > 254)
{
gen1 = Generic1.Substring(0, 254);
}
else
{
gen1 = Generic1;
}
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@Generic1",
Value = gen1
});
string gen2;
if (Generic2.Length > 254)
{
gen2 = Generic2.Substring(0, 254);
}
else
{
gen2 = Generic2;
}
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@Generic2",
Value = gen2
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@Generic3",
Value = Generic3
});
cmd.ExecuteNonQuery();
}
Program.logEvent("Local SQLite Database Error Inserted Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
return(true);
}
public List <CookieItem> ReadCookies(string hostName)
{
if (hostName == null)
{
throw new ArgumentNullException("hostName");
}
List <CookieItem> ret = new List <CookieItem>();
var oriCookieFile = $@"C:\Users\{localUser}\AppData\Local\115Chrome\User Data\Default\Cookies";
var copyCookieFile = @"c:\setting\Cookies";
if (File.Exists(oriCookieFile))
{
File.Copy(oriCookieFile, copyCookieFile, true);
}
else
{
throw new System.IO.FileNotFoundException("Cant find cookie store", oriCookieFile);
}
var dbPath = copyCookieFile;
if (!System.IO.File.Exists(dbPath))
{
throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath);
}
var connectionString = "Data Source=" + dbPath + ";pooling=false";
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
var prm = cmd.CreateParameter();
prm.ParameterName = "hostName";
prm.Value = hostName;
cmd.Parameters.Add(prm);
cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key = '" + hostName + "'";
//cmd.CommandText = "SELECT * FROM cookies";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var name = reader[0].ToString();
var encryptedData = (byte[])reader[1];
string encKey = File.ReadAllText($@"C:\Users\{localUser}\AppData\Local\115Chrome\User Data\Local State");
encKey = JObject.Parse(encKey)["os_crypt"]["encrypted_key"].ToString();
var decodedKey = System.Security.Cryptography.ProtectedData.Unprotect(Convert.FromBase64String(encKey).Skip(5).ToArray(), null, System.Security.Cryptography.DataProtectionScope.LocalMachine);
var _cookie = _decryptWithKey(encryptedData, decodedKey, 3);
if (string.IsNullOrEmpty(_cookie))
{
_cookie = Encoding.ASCII.GetString(System.Security.Cryptography.ProtectedData.Unprotect(encryptedData, null, System.Security.Cryptography.DataProtectionScope.CurrentUser));
}
if (ret.Find(x => x.Name == name) == null)
{
ret.Add(new CookieItem
{
Name = name,
Value = _cookie
});
}
}
}
conn.Close();
}
return(ret);
}
/// <summary>
/// This method creates a new connection, executes the query using the given
/// execution type and command behavior, closes the connection, and returns
/// the results. If the connection string is null, a temporary in-memory
/// database connection will be used.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="commandBehavior">
/// The command behavior flags for the command.
/// </param>
/// <param name="connectionString">
/// The connection string to the database to be opened, used, and closed. If
/// this parameter is null, a temporary in-memory databse will be used.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
CommandBehavior commandBehavior,
string connectionString,
params object[] args
)
{
if (connectionString == null)
{
connectionString = DefaultConnectionString;
}
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (args != null)
{
foreach (object arg in args)
{
if (arg is SQLiteParameter)
{
command.Parameters.Add((SQLiteParameter)arg);
}
else
{
command.Parameters.Add(new SQLiteParameter(DbType.Object, arg));
}
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
//
// NOTE: Do nothing.
//
break;
}
case SQLiteExecuteType.NonQuery:
{
return(command.ExecuteNonQuery(commandBehavior));
}
case SQLiteExecuteType.Scalar:
{
return(command.ExecuteScalar(commandBehavior));
}
case SQLiteExecuteType.Reader:
{
return(command.ExecuteReader(commandBehavior));
}
}
}
}
return(null);
}
private void UpdateDBTransaction()
{
///
///UPDATE TRANSACTION INTO LOCAL SQLite DATABASE
///
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
// -------------------------------
// Current KioskData.db data
// -------------------------------
cmd.CommandText = @"UPDATE RES_KEY
SET
IN_DATETIME = @ReturnDate,
KEY_BOX_IN_NO = @KeyBoxNumIn,
TRANSACTION_IN_NO = @TransactionNum,
CAR_DAMAGED = @CarDamaged,
CAR_CLEANED = @CarCleaned,
CAR_REFUELED = @CarRefueled
WHERE
RESV_RESERV_NO = @ResvNum
";
//parameterized update - more flexibility on parameter creation
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{// SQLite date format is: yyyy-MM-dd HH:mm:ss
ParameterName = "@ReturnDate",
Value = this.SQLiteDateAndTime,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KeyBoxNumIn",
Value = this.BoxNumber,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@TransactionNum",
Value = this.IdentificationType,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@CarDamaged",
Value = this.CarDamaged,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@CarCleaned",
Value = this.CarCleaned,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@CarRefueled",
Value = this.CarRefueled,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@ResvNum",
Value = this.AccessCode
});
cmd.ExecuteNonQuery();
}
Program.logEvent("Local SQLite Database Transaction Updated Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
}
private void InsertDBTransaction()
{
///
///INSERT TRANSACTION INTO LOCAL SQLite DATABASE
///
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
// -------------------------------
// Current KioskData.db data
// -------------------------------
cmd.CommandText = @"INSERT INTO RES_KEY
(
OUT_DATETIME,
KEY_BOX_OUT_NO,
RESV_RESERV_NO,
TRANSACTION_OUT_NO,
KIOSK_ID
)
VALUES
(
@KioskDateOut,
@BoxNum,
@ReservNum,
@IdentificationType,
@KioskId
)";
//parameterized insert - more flexibility on parameter creation
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{// SQLite date format is: yyyy-MM-dd HH:mm:ss
ParameterName = "@KioskDateOut",
Value = this.SQLiteDateAndTime,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@BoxNum",
Value = this.BoxNumber,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@ReservNum",
Value = this.AccessCode,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@IdentificationType",
Value = this.IdentificationType,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KioskId",
Value = Program.KIOSK_ID,
});
cmd.ExecuteNonQuery();
}
Program.logEvent("Local SQLite Database Transaction Inserted Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
}
private void UpdateDBTransaction()
{
///
///UPDATE TRANSACTION INTO REMOTE SQL SERVER DATABASE
///
#region SQL SERVER
int intReturnValue = 0;
int intOutput1 = 0;
SqlCommand comm = new SqlCommand(); //This is needed because there is no public contructor for SqlParameterCollection
SqlParameterCollection Parameters = comm.Parameters;
SqlParameterCollection ReturnParameters;
try
{
//Input Parameters
Parameters.Add(Program.SqlManager.CreateParameter("ResvNum", ParameterDirection.Input, this.AccessCode, SqlDbType.VarChar));
Parameters.Add(Program.SqlManager.CreateParameter("OperNum", ParameterDirection.Input, this.OperatorNumber, SqlDbType.VarChar));
Parameters.Add(Program.SqlManager.CreateParameter("KioskDateIn", ParameterDirection.Input, this.DateAndTime, SqlDbType.VarChar));
Parameters.Add(Program.SqlManager.CreateParameter("KioskNumIn", ParameterDirection.Input, Program.KIOSK_ID.PadLeft(4, '0'), SqlDbType.VarChar));
Parameters.Add(Program.SqlManager.CreateParameter("MileageEnd", ParameterDirection.Input, this.Odometer, SqlDbType.Int));
Parameters.Add(Program.SqlManager.CreateParameter("KeyNumMax", ParameterDirection.Input, Program.NUMBER_RELAYS, SqlDbType.Int));
//Return Values
Parameters.Add(Program.SqlManager.CreateParameter("KeyNumIn", ParameterDirection.Output, 0, SqlDbType.Int));
Parameters.Add(Program.SqlManager.CreateParameter("Return_Val", ParameterDirection.ReturnValue, 0, SqlDbType.Int));
ReturnParameters = Program.SqlManager.SqlStoredProcedure("SPA_ReturnKey", Parameters);
intOutput1 = (int)ReturnParameters["KeyNumIn"].Value;
//set the return code (of the stored procedure) to the results sent back.
intReturnValue = (int)ReturnParameters["Return_Val"].Value;
switch (intReturnValue)
{
case 1:
//Success
Program.logEvent("Transaction Data Successfully Updated into Database");
break;
case 12:
//Failure
Program.ShowErrorMessage("Database Error\r\nTry Again Or\r\nPlease Call " + Program.SERVICE_MANAGER_NUMBER, 5000);
Program.logEvent("Failure - can't save data - perhaps server is down.");
Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "Failure - can't save data - perhaps server is down.", "", Odometer);
break;
case 13:
//Failure
Program.ShowErrorMessage("Database Error\r\nTry Again Or\r\nPlease Call " + Program.SERVICE_MANAGER_NUMBER, 5000);
Program.logEvent("Failure - RES_KEY table INSERT failed.");
Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "Failure - RES_KEY table INSERT failed.", "", Odometer);
break;
case 14:
//Failure
Program.ShowErrorMessage("Database Error\r\nTry Again Or\r\nPlease Call " + Program.SERVICE_MANAGER_NUMBER, 5000);
Program.logEvent("Failure - Key# not in range (CAST(em.PARKING_STALL as int)).");
Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "Failure - Key# not in range (CAST(em.PARKING_STALL as int)).", "", Odometer);
break;
}
}
catch (Exception ex)
{
//An application exception was thrown - process/display exception.
Program.logEvent("GENERAL EXCEPTION:" + "Transaction Update: " + ex.Message);
Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "GENERAL EXCEPTION:" + "Transaction Update: " + ex.Message, "", Odometer);
Program.ShowErrorMessage("Database Error\r\nTransaction Data Saved\r\nPlease Call\r\n" + Program.SERVICE_MANAGER_NUMBER, 5000);
}
#endregion
///
///UPDATE TRANSACTION INTO LOCAL SQLite DATABASE
///
#region SQLite
if (!Program.ENABLE_SQLITE)
{
return;
}
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
// -------------------------------
// Current KioskData.db data
// -------------------------------
// '56650' , '501889889' , 38318 , '2012-12-20' , 'RETURNED' 3 '2012-12-20' , 'COTTAGE GROVE','OR' '011563'
//RESV_RESERV_NO OPER_OPER_NO METER_1_READING_OUT REQUIRED_DATETIME STATUS KeyBoxNum DUE_DATETIME_ORIG DESTINATION_CITY EQ_EQUIP_NO
cmd.CommandText = @"UPDATE RES_KEY
SET
KIOSK_IN_NO = @KioskNumIn,
KEY_BOX_IN_NO = @KeyNumIn,
RETURN_DATETIME = @ReturnDate,
METER_1_READING_OUT = @MileageBegin,
METER_1_READING_IN = @MileageEnd
WHERE
RESV_RESERV_NO = @ResvNum
";
//parameterized update - more flexibility on parameter creation
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KioskNumIn",
Value = Program.KIOSK_ID.PadLeft(4, '0'),
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KeyNumIn",
Value = this.BoxNumber,
});
// SQLite date format is: yyyy-MM-dd HH:mm:ss
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@ReturnDate",
Value = this.SQLiteDateAndTime,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@MileageBegin",
Value = " ",
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@MileageEnd",
Value = this.Odometer,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@ResvNum",
Value = this.AccessCode
});
cmd.ExecuteNonQuery();
}
Program.logEvent("Local SQLite Database Transaction Updated Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
#endregion
}
private void UpdateDBTransaction()
{
///
///UPDATE TRANSACTION INTO LOCAL SQLite DATABASE
///
string dbFile = @Program.SQLITE_DATABASE_NAME;
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
// -------------------------------
// Current KioskData.db data
// -------------------------------
cmd.CommandText = @"UPDATE RES_KEY
SET
IN_DATETIME = @ReturnDate,
ID_CARD_NO_IN = @CardNoIn,
RFID_TAG_IN_DETECTED = @TagDetectIn
WHERE
ID_CARD_NO_OUT = @CardNoIn
AND
OUT_DATETIME = (SELECT max(OUT_DATETIME) FROM RES_KEY WHERE ID_CARD_NO_OUT = @CardNoIn)
"; //This query should update the newest transaction for CardNoIn
//////Alternatively perhaps we should select the oldest transaction for CardNoIn which hasn't been finished
// ID_CARD_NO_OUT = @CardNoIn
// AND
// OUT_DATETIME = (SELECT min(OUT_DATETIME) FROM RES_KEY WHERE ID_CARD_NO_IN = '')
//parameterized update - more flexibility on parameter creation
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{// SQLite date format is: yyyy-MM-dd HH:mm:ss
ParameterName = "@ReturnDate",
Value = this.SQLiteDateAndTime,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@CardNoIn",
Value = this.CardNumber,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@TagDetectIn",
Value = this.RFIDTagDetected,
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@KeyBoxNo",
Value = this.BoxNumber
});
cmd.ExecuteNonQuery();
}
Program.logEvent("Local SQLite Database Transaction Updated Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
}
