public void openDatabase(string options)
{
string[] optVal = getOptVal(options);
if (optVal == null)
{
DispatchCommandResult(new PluginResult(PluginResult.Status.JSON_EXCEPTION));
}
else
{
try
{
string name = optVal[0];
string version = optVal[1];
string display_name = optVal[2];
string size = optVal[3];
db = new SQLiteConnection(name);
db.Open();
DispatchCommandResult(new PluginResult(PluginResult.Status.OK));
}
catch (Exception e)
{
DispatchCommandResult(new PluginResult(PluginResult.Status.ERROR, e.StackTrace));
}
}
}
private SQLiteConnection CreateSQLiteConnection(string nativeDbPath, CreationCollisionOption creationCollisionOption)
{
if (string.IsNullOrEmpty(nativeDbPath) || databaseNameProvider == null || fileService == null || sqlitePlatformProvider == null) return null;
try
{
createSqlConnectionResetEvent.WaitOne();
if (creationCollisionOption == CreationCollisionOption.OpenIfExists && SqliteDbConnection != null)
{
return SqliteDbConnection;
}
SqliteDbConnection = new SQLiteConnection(sqlitePlatformProvider.SqLitePlatform, nativeDbPath, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite);
return SqliteDbConnection;
}
catch (Exception e)
{
Debug.WriteLine(e);
}
finally
{
createSqlConnectionResetEvent.Set();
}
return null;
}
private void loadButton_Click(object sender, EventArgs e)
{
SQLiteConnection db = new SQLiteConnection(new SQLite.Net.Platform.Win32.SQLitePlatformWin32(), @"d:\dnd\dndspells.sqlite");
spells = db.Table<Spell>().ToList<Spell>();
populateList();
}
private PreparedSqlLiteInsertCommand CreateCommand(SQLiteConnection conn, string extra)
{
var cols = _tableMapping.InsertColumns;
string insertSql;
if (!cols.Any() && _tableMapping.Columns.Count() == 1 && _tableMapping.Columns[0].IsAutoInc)
{
insertSql = string.Format("insert {1} into \"{0}\" default values", _tableMapping.TableName, extra);
}
else
{
var replacing = string.Compare(extra, "OR REPLACE", StringComparison.OrdinalIgnoreCase) == 0;
if (replacing)
{
cols = _tableMapping.Columns;
}
insertSql = string.Format("insert {3} into \"{0}\"({1}) values ({2})", _tableMapping.TableName,
string.Join(",", (from c in cols
select "\"" + c.Name + "\"").ToArray()),
string.Join(",", (from c in cols
select "?").ToArray()), extra);
}
return new PreparedSqlLiteInsertCommand(conn)
{
CommandText = insertSql
};
}
private static void ListAllBooks(string connectionString)
{
SQLiteConnection databaseConnection = new SQLiteConnection(connectionString);
databaseConnection.Open();
using (databaseConnection)
{
string sqlStringCommand = "SELECT * FROM Books";
SQLiteCommand allBooks = new SQLiteCommand(sqlStringCommand, databaseConnection);
SQLiteDataReader reader = allBooks.ExecuteReader();
using (reader)
{
while (reader.Read())
{
Console.WriteLine(
"Title: {0}, \nAuthor: {1}, \nPublishDate: {2}, \nISBN: {3}",
reader["Title"],
reader["Author"],
(DateTime) reader["PublishDate"],
reader["ISBN"]);
Console.WriteLine();
}
}
}
}
public ECOdatabase ()
{
database = DependencyService.Get<ISqlite> ().GetConnection ();
// database.DeleteAll<CartItem> ();
//Create a CartItem table if there is no such table
if (database.TableMappings.All(t => t.MappedType.Name != typeof(CartItem).Name)) {
//Create cartitem table
database.CreateTable<CartItem> ();
database.Commit ();
}
if (database.TableMappings.All(t => t.MappedType.Name != typeof(BuyList).Name)) {
//Create cartitem table
database.CreateTable<BuyList> ();
database.Commit ();
}
if (database.TableMappings.All(t => t.MappedType.Name != typeof(WoolworthsItem).Name)) {
//Create cartitem table
database.CreateTable<WoolworthsItem> ();
database.Commit ();
}
// database.DeleteAll<BuyList>();
if (GetWoolWorthsItemAll ().Count == 0) {
WoolworthsItem item1 = new WoolworthsItem ("50375264", "Kleenex Tissues", 2.50);
InsertItemToWoolWorthsItem (item1);
}
}
public static Options GetOptions()
{
using (SQLiteConnection conn = new SQLiteConnection(dbLocation))
{
return conn.Get<Options>(1);
}
}
public static int SaveOptions(Options options)
{
using (SQLiteConnection conn = new SQLiteConnection(dbLocation))
{
return conn.Update(options);
}
}
public static void SetSqlConnection(SQLiteConnection newConnection)
{
conn = newConnection;
var db = Container.Resolve<ICurrencyDatabase>();
db.Connection = conn;
db.RegisterTables();
}
static OptionsRepository()
{
// Figure out where the SQLite database will be.
var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
dbLocation = Path.Combine(documents, "timer_sqlite-net.db");
using (SQLiteConnection conn = new SQLiteConnection(dbLocation))
{
conn.CreateTable<Options>();
Options o = null;
try
{
o = conn.Get<Options>(1);
}
catch (InvalidOperationException)
{
// Doesn't exist...
}
if (o == null)
{
// Create options... a bit contrived perhaps :)
o = new Options();
o.Seconds = 5;
int rows = conn.Insert(o);
if (rows == 0)
{
throw new InvalidOperationException("Can't create options!");
}
}
}
}
public static void Install(SQLiteConnection connection, string schema)
{
if (connection == null) throw new ArgumentNullException("connection");
Log.Info("Start installing Hangfire SQL objects...");
var script = GetStringResource(
typeof(SQLiteObjectsInstaller).Assembly,
"Hangfire.SQLite.Install.sql");
script = script.Replace("SET @TARGET_SCHEMA_VERSION = 5;", "SET @TARGET_SCHEMA_VERSION = " + RequiredSchemaVersion + ";");
script = script.Replace("$(HangFireSchema)", !string.IsNullOrWhiteSpace(schema) ? schema : Constants.DefaultSchema);
for (var i = 0; i < RetryAttempts; i++)
{
try
{
connection.Execute(script);
break;
}
catch (SQLiteException ex)
{
throw;
}
}
Log.Info("Hangfire SQL objects installed.");
}
public void Invoke(ServerMessage message, IIrcClient ircClient, ILogger logger)
{
if (message.Command.Equals("376") && !_isInitialized) //We use this to initialize the plugin. This should only happen once.
{
_ircClient = ircClient;
_logger = logger;
//Subscribe to when the stream startes/ends events
_ircClient.TwitchService.OnStreamOnline += TwitchService_OnStreamOnline;
_ircClient.TwitchService.OnStreamOffline += TwitchService_OnStreamOffline;
_timer.AutoReset = true;
_timer.Interval = 60000;
_timer.Elapsed += _timer_Elapsed;
_timer.Enabled = true;
_dataFolder = PluginHelper.GetPluginDataFolder(this);
_database = new SQLiteConnection(new SQLitePlatformWin32(), Path.Combine(_dataFolder, "CurrencyPlugin.db"));
_database.CreateTable<CurrencyUserModel>();
//This is for debugging
//_watchedChannelsList.Add("#unseriouspid");
//_onlineChannelsList.Add("#unseriouspid");
_logger.Write($"{GetType().Name} Initialized.");
_isInitialized = true;
}
else if (message.Command.Equals("PRIVMSG") &&
_isInitialized &&
_watchedChannelsList.Contains(message.GetChannelName()))
{
var userCommand = message.Trailing.Split(' ')[0].ToLower().TrimStart();
var nickName = message.GetSender();
var channelName = message.GetChannelName();
switch (userCommand)
{
case "!points":
if (IsUserCommandOnCooldown(userCommand, nickName, channelName)) return;
DisplayPoints(nickName, channelName);
CooldownUserCommand(userCommand, nickName, channelName);
break;
case "!gamble":
if (IsUserCommandOnCooldown(userCommand, nickName, channelName)) return;
DoGamble(message);
CooldownUserCommand(userCommand, nickName, channelName);
break;
case "!bonus":
DoAddBonus(message);
break;
case "!bonusall":
DoAddBonusAll(message);
break;
}
}
}
public string DeleteItem(int itemId)
{
string result = string.Empty;
using (var dbConn = new SQLiteConnection(App.SQLITE_PLATFORM, App.DB_PATH))
{
var existingItem = dbConn.Query<Media>("select * from Media where Id =" + itemId).FirstOrDefault();
if (existingItem != null)
{
dbConn.RunInTransaction(() =>
{
dbConn.Delete(existingItem);
if (dbConn.Delete(existingItem) > 0)
{
result = "Success";
}
else
{
result = "This item was not removed";
}
});
}
return result;
}
}
public static SQLiteConnection GetDbxsqConnection()
{
var conn = new SQLiteConnection(new SQLitePlatformWinRT(), DBPath);
conn.CreateTable<XYXS>();
return conn;
}
public static List<ModelDB> LoadData(SaveInfo save = null)
{
if (save != null) {
SetCurrentSave(save);
}
var _conn = new SQLiteConnection(Conf.savePath + currentSave.Filename, SQLiteOpenFlags.ReadWrite);
var loadedData = new List<ModelDB>();
var subclasses = GameState.GetGeneratedModelTypes();
foreach(var c in subclasses) {
var instance = Activator.CreateInstance(c);
try {
_conn.Table(instance).ToList();
var query = _conn.GetType().GetMethod("Table", new Type[] { })
.MakeGenericMethod(c)
.Invoke(_conn, null) as IEnumerable;
foreach (var item in query) {
loadedData.Add((ModelDB)item);
}
} catch (SQLiteException) {
// it's gonna complain about missing tables. ignore them
}
}
return loadedData;
}
private static void ListBooks(SQLiteConnection dbCon)
{
using (dbCon)
{
SQLiteCommand cmdSelect = new SQLiteCommand(
"SELECT * FROM Books", dbCon);
Console.WriteLine("Books:");
Console.WriteLine("ID\tTitle\t\tAuthor\t\tPublish Date\tISBN");
SQLiteDataReader reader = cmdSelect.ExecuteReader();
using (reader)
{
while (reader.Read())
{
int id = (int)reader["BookId"];
string title = (string)reader["Title"];
string author = (string)reader["Author"];
DateTime publishDate = (DateTime)reader["PublishDate"];
string isbn = (string)reader["ISBN"];
Console.WriteLine("{0}\t{1}\t{2}\t{3:dd.MM.yyyy}\t{4}",
id, title.Trim(), author.Trim(), publishDate, isbn.Trim());
}
}
}
}
private static SQLiteConnection CreateTableConnection()
{
// 创建 News 模型对应的表,如果已存在,则忽略该操作。
var connection = new SQLiteConnection(new SQLitePlatformWinRT(), DataBase.DbPath);
connection.CreateTable<News>();
return connection;
}
private void Proceed_Click(object sender, RoutedEventArgs e)
{
Model.Userdata userData = new Model.Userdata();
userData.addData(name.Text, (int)AgeList.SelectedItem, 0);
// Create a Database.
// Add to database.
try
{
var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "App.db");
var db = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT() ,path);
db.CreateTable<Model.Userdata>();
db.CreateTable<Model.Subject>();
db.Insert(userData);
this.Frame.Navigate(typeof(World2));
}
catch (Exception)
{
throw;
}
// redirecting to
}
public LibraryService(
SQLiteConnection sqLiteConnection,
IDispatcherUtility dispatcherUtility)
{
_sqLiteConnection = sqLiteConnection;
_dispatcherUtility = dispatcherUtility;
}
public void ByteArrays()
{
//Byte Arrays for comparisson
ByteArrayClass[] byteArrays = new ByteArrayClass[] {
new ByteArrayClass() { bytes = new byte[] { 1, 2, 3, 4, 250, 252, 253, 254, 255 } }, //Range check
new ByteArrayClass() { bytes = new byte[] { 0 } }, //null bytes need to be handled correctly
new ByteArrayClass() { bytes = new byte[] { 0, 0 } },
new ByteArrayClass() { bytes = new byte[] { 0, 1, 0 } },
new ByteArrayClass() { bytes = new byte[] { 1, 0, 1 } },
new ByteArrayClass() { bytes = new byte[] { } }, //Empty byte array should stay empty (and not become null)
new ByteArrayClass() { bytes = null } //Null should be supported
};
SQLiteConnection database = new SQLiteConnection(TestPath.GetTempFileName());
database.CreateTable<ByteArrayClass>();
//Insert all of the ByteArrayClass
foreach (ByteArrayClass b in byteArrays)
database.Insert(b);
//Get them back out
ByteArrayClass[] fetchedByteArrays = database.Table<ByteArrayClass>().OrderBy(x => x.ID).ToArray();
Assert.AreEqual(fetchedByteArrays.Length, byteArrays.Length);
//Check they are the same
for (int i = 0; i < byteArrays.Length; i++)
{
byteArrays[i].AssertEquals(fetchedByteArrays[i]);
}
}
public void ByteArrayWhere()
{
//Byte Arrays for comparisson
ByteArrayClass[] byteArrays = new ByteArrayClass[] {
new ByteArrayClass() { bytes = new byte[] { 1, 2, 3, 4, 250, 252, 253, 254, 255 } }, //Range check
new ByteArrayClass() { bytes = new byte[] { 0 } }, //null bytes need to be handled correctly
new ByteArrayClass() { bytes = new byte[] { 0, 0 } },
new ByteArrayClass() { bytes = new byte[] { 0, 1, 0 } },
new ByteArrayClass() { bytes = new byte[] { 1, 0, 1 } },
new ByteArrayClass() { bytes = new byte[] { } }, //Empty byte array should stay empty (and not become null)
new ByteArrayClass() { bytes = null } //Null should be supported
};
SQLiteConnection database = new SQLiteConnection(TestPath.GetTempFileName());
database.CreateTable<ByteArrayClass>();
byte[] criterion = new byte[] { 1, 0, 1 };
//Insert all of the ByteArrayClass
int id = 0;
foreach (ByteArrayClass b in byteArrays)
{
database.Insert(b);
if (b.bytes != null && criterion.SequenceEqual<byte>(b.bytes))
id = b.ID;
}
Assert.AreNotEqual(0, id, "An ID wasn't set");
//Get it back out
ByteArrayClass fetchedByteArray = database.Table<ByteArrayClass>().Where(x => x.bytes == criterion).First();
Assert.IsNotNull(fetchedByteArray);
//Check they are the same
Assert.AreEqual(id, fetchedByteArray.ID);
}
public UserService(SQLiteConnection database, ILogger logger, string botOwner)
{
_database = database;
_database.CreateTable<UserDatabaseEntity>();
_logger = logger;
_botOwner = botOwner.ToLower();
}
public void AutoGuid_HasGuid()
{
var db = new SQLiteConnection(new SQLitePlatformTest(), TestPath.GetTempFileName());
db.CreateTable<TestObj>(CreateFlags.AutoIncPK);
var guid1 = new Guid("36473164-C9E4-4CDF-B266-A0B287C85623");
var guid2 = new Guid("BC5C4C4A-CA57-4B61-8B53-9FD4673528B6");
var obj1 = new TestObj
{
Id = guid1,
Text = "First Guid Object"
};
var obj2 = new TestObj
{
Id = guid2,
Text = "Second Guid Object"
};
int numIn1 = db.Insert(obj1);
int numIn2 = db.Insert(obj2);
Assert.AreEqual(guid1, obj1.Id);
Assert.AreEqual(guid2, obj2.Id);
db.Close();
}
public static void Startup()
{
SQLiteConnection connection = null;
string dbLocation = "expensesDB.db3";
#if __ANDROID__
var library = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
dbLocation = Path.Combine(library, dbLocation);
var platform = new SQLitePlatformAndroid();
connection = new SQLiteConnection(platform, dbLocation);
#elif __IOS__
CurrentPlatform.Init();
var docsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
var libraryPath = Path.Combine(docsPath, "../Library/");
dbLocation = Path.Combine(libraryPath, dbLocation);
var platform = new SQLitePlatformIOS();
connection = new SQLiteConnection(platform, dbLocation);
#elif WINDOWS_PHONE
var platform = new SQLitePlatformWP8();
dbLocation = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbLocation);
connection = new SQLiteConnection(platform, dbLocation);
#endif
ServiceContainer.Register<IMessageDialog>(() => new MessageDialog());
ServiceContainer.Register<ICloudService>(AzureService.Instance);
ServiceContainer.Register<IExpenseService>(() => new ExpenseService(connection));
ServiceContainer.Register<ExpensesViewModel>();
ServiceContainer.Register<ExpenseViewModel>();
}
public void MultipleResultSets()
{
using (SQLiteConnection conn = new SQLiteConnection(m_csb.ConnectionString))
{
conn.Open();
conn.ExecuteNonQuery(@"create table Test(Id integer not null primary key autoincrement, Value text not null);");
conn.ExecuteNonQuery(@"insert into Test(Id, Value) values(1, 'one'), (2, 'two');");
using (var cmd = (SQLiteCommand) conn.CreateCommand())
{
cmd.CommandText = @"select Value from Test where Id = @First; select Value from Test where Id = @Second;";
cmd.Parameters.Add("First", DbType.Int64).Value = 1L;
cmd.Parameters.Add("Second", DbType.Int64).Value = 2L;
using (var reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
Assert.AreEqual("one", reader.GetString(0));
Assert.IsFalse(reader.Read());
Assert.IsTrue(reader.NextResult());
Assert.IsTrue(reader.Read());
Assert.AreEqual("two", reader.GetString(0));
Assert.IsFalse(reader.Read());
Assert.IsFalse(reader.NextResult());
}
}
}
}
public Sqlite()
{
DbName = "db.txt";
_pathToDatabase = "";
WriteConn = null;
_isEnabled = false;
}
public void Clear()
{
using (var c = new SQLiteConnection(platform, DbPath))
{
c.Execute("DELETE FROM BackgroundTrackItem");
}
}
public SQLiteConnection GetSqlConnection(string fileName)
{
var path = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
var plat=new SQLite.Net.Platform.WindowsPhone8.SQLitePlatformWP8();
var conn=new SQLiteConnection(plat, path);
return conn;
}
private void BtnReadAdditionFile_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Title = "请选择附加路径文件";
dialog.Filter = "txt文件(*.txt)|*.txt";
if (dialog.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
OpenFileDialog dialog2 = new OpenFileDialog();
dialog2.Title = "请选择文件";
dialog2.Filter = "db文件(*.db)|*.db";
if (dialog2.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
string dbPath = dialog2.FileName;
string conStr = @"Data Source =" + dbPath;
m_dbConnection = new SQLiteConnection(conStr);
m_dbConnection.Open();
string txtPath = dialog.FileName;
FileStream txtFS = new FileStream(txtPath, FileMode.Open);
var utf8WithoutBom = new System.Text.UTF8Encoding(false);
StreamReader txtSR = new StreamReader(txtFS, utf8WithoutBom);
StreamWriter txtSW = new StreamWriter(txtFS, utf8WithoutBom);
string aryLine = null;
myRoadStruct tempStruct = new myRoadStruct();
string[] tempStrarray = null;
string sql = null;
DataTable dtTemp = null;
FileStream csvFS = null;
StreamWriter csvSW = null;
string csvName = null;
DialogResult dr = MessageBox.Show("是否是百度模式的数据！", "重要选择", MessageBoxButtons.YesNoCancel);
while ((aryLine = txtSR.ReadLine()) != null)
{
//if (aryLine == " ")
// continue;
tempStrarray = aryLine.Split(',');
tempStruct.id = int.Parse(tempStrarray[0]);
//if(tempStruct.id < 56953)
// continue;
tempStruct.origin_city = tempStrarray[1 + 4];
tempStruct.origin_region = tempStrarray[3 + 4];
tempStruct.dest_city = tempStrarray[2 + 4];
tempStruct.dest_region = tempStrarray[4 + 4];
if (tempStruct.origin_city != tempStruct.origin_region)
{
csvName = tempStruct.origin_city + '+' + tempStruct.origin_region;
}
else
{
csvName = tempStruct.origin_city;
}
if (File.Exists(System.IO.Path.GetDirectoryName(dbPath) + "\\" + csvName + ".csv") == false)
{
continue;
}
SQLiteCommand cmd = m_dbConnection.CreateCommand();
sql = "select * from path_data where id = " + tempStruct.id.ToString();
cmd.CommandText = sql;
SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
dtTemp = new DataTable();
dao.Fill(dtTemp);
if (dtTemp.Rows.Count == 0)
{
continue;
}
csvFS = new FileStream(System.IO.Path.GetDirectoryName(dbPath) + "\\" + csvName + ".csv", FileMode.Open);
csvSW = new StreamWriter(csvFS, utf8WithoutBom);
csvSW.BaseStream.Seek(0, SeekOrigin.End);
if (dr == DialogResult.OK)
{
for (int k = 0; k < 11; k++)
{
csvSW.Write(dtTemp.Rows[0][k] + ",");
}
csvSW.WriteLine("\"" + dtTemp.Rows[0][11] + "\"");
}
else
{
for (int k = 0; k < 5; k++)
{
csvSW.Write(dtTemp.Rows[0][k] + ",");
}
csvSW.Write(tempStruct.origin_city + ",");
csvSW.Write(tempStruct.dest_city + ",");
csvSW.Write(tempStruct.origin_region + ",");
csvSW.Write(tempStruct.dest_region + ",");
csvSW.Write(dtTemp.Rows[0][5] + ",");
csvSW.Write(dtTemp.Rows[0][6] + ",");
if (dtTemp.Rows[0][7].ToString().Length > 100)
{
csvSW.WriteLine("\"" + dtTemp.Rows[0][7] + "\"");
}
else
{
csvSW.WriteLine("\"" + dtTemp.Rows[0][8] + "\"");
}
csvSW.Flush();
csvSW.Close();
csvFS.Close();
}
}
txtSW.Close();
txtSR.Close();
txtFS.Close();
MessageBox.Show("补充完毕");
}
private void ReadBbBtn_Click(object sender, EventArgs e)
{
ProgressFm = new ProgressBar(2, 100);
if (Mode == -1)
{
MessageBox.Show("未选择数据模式");
return;
}
Stopwatch sw = new Stopwatch();
sw.Start();
OpenFileDialog dialog = new OpenFileDialog();
dialog.Title = "请选择文件";
dialog.Filter = "db文件(*.db)|*.db";
if (dialog.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
string dbPath = dialog.FileName;
string conStr = @"Data Source =" + dbPath;
m_dbConnection = new SQLiteConnection(conStr);
m_dbConnection.Open();
string mySql = "SELECT origin_lat,origin_lng FROM path_data group by origin_lat,origin_lng";
SQLiteCommand cmd = m_dbConnection.CreateCommand();
cmd.CommandText = mySql;
SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
dt = new DataTable();
dao.Fill(dt);
FileStream Origin_ArrayTxtFS = new FileStream(System.IO.Path.GetDirectoryName(dbPath) + "\\" + System.IO.Path.GetFileName(dbPath).Substring(0, System.IO.Path.GetFileName(dbPath).Length - 3) + "_" + "Origin-Array.txt", FileMode.OpenOrCreate);
var utf8WithoutBom = new System.Text.UTF8Encoding(false);
StreamWriter Origin_ArrayTxtSW = new StreamWriter(Origin_ArrayTxtFS, utf8WithoutBom);
for (int i = 0; i < dt.Rows.Count; i++)
{
Origin_ArrayTxtSW.WriteLine(dt.Rows[i].ItemArray[0].ToString() + "+" + dt.Rows[i].ItemArray[1].ToString());
}
Origin_ArrayTxtSW.Close();
Origin_ArrayTxtFS.Close();
m_dbConnection.Close();
string txtPath = System.IO.Path.GetDirectoryName(dbPath) + "\\" + System.IO.Path.GetFileName(dbPath).Substring(0, System.IO.Path.GetFileName(dbPath).Length - 3) + "_" + "Origin-Array.txt";
FileStream fileS = new FileStream(txtPath, FileMode.Open);
StreamReader sr = new StreamReader(fileS, utf8WithoutBom);
string srLine;
ProgressFm.Show(this);
int progress = 1;
while ((srLine = sr.ReadLine()) != null)
{
numOfgroup++;
}
sr.BaseStream.Seek(0, SeekOrigin.Begin);
if (Mode == 1)
{
while ((srLine = sr.ReadLine()) != null)
{
ProgressFm.setPos(((progress++) / numOfgroup) * 100);//设置进度条位置
Car_ModeMakeroutes(dbPath, srLine);
}
}
else if (Mode == 2)
{
while ((srLine = sr.ReadLine()) != null)
{
ProgressFm.setPos(((progress++) / numOfgroup) * 100);//设置进度条位置
PublicTrans_Make_Routes(dbPath, srLine);
}
}
else if (Mode == 3)
{
while ((srLine = sr.ReadLine()) != null)
{
ProgressFm.setPos(((progress++) / numOfgroup) * 100);//设置进度条位置
Walking_Riding_Make_Routes(dbPath, srLine);
}
}
else if (Mode == 4)
{
while ((srLine = sr.ReadLine()) != null)
{
ProgressFm.setPos(((progress++) / numOfgroup) * 100);//设置进度条位置
Walking_Riding_Make_Routes(dbPath, srLine);
}
}
ProgressFm.Close();//关闭窗体
sw.Stop();
TimeSpan ts2 = sw.Elapsed;
MessageBox.Show((ts2.TotalMilliseconds / 1000).ToString("0.0") + "秒转换完毕");
}
private void Car_ModeMakeroutes(string dbPath, string csvPath)
{
List <string> idArray = new List <string>();
if (csvPath.IndexOf('+') == 0)
{
string conStr = @"Data Source =" + dbPath;
m_dbConnection = new SQLiteConnection(conStr);
m_dbConnection.Open();
string csvName = null;
csvName = csvPath;
string sql = "SELECT count(*) FROM path_data WHERE origin_city = '" + csvName + "'";
SQLiteCommand cmd = m_dbConnection.CreateCommand();
cmd.CommandText = sql;
SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
dt = new DataTable();
dao.Fill(dt);
SQLiteCommand cmd2 = m_dbConnection.CreateCommand();
string sql2 = "SELECT origin_city ,origin_region FROM path_data WHERE origin_city = '" + csvName + "' limit 0,1";
cmd2.CommandText = sql2;
SQLiteDataReader reader = cmd2.ExecuteReader();
reader.Read();
if (reader["origin_city"].ToString() != reader["origin_region"].ToString())
{
csvName = reader[1].ToString() + reader[0].ToString();
}
FileStream csvFile = null;
StreamWriter csvSw = null;
if (File.Exists(System.IO.Path.GetDirectoryName(dbPath) + "\\" + csvName + ".csv"))
{
csvFile = new FileStream(System.IO.Path.GetDirectoryName(dbPath) + "\\" + csvName + ".csv", FileMode.Open);
var utf8WithoutBom = new System.Text.UTF8Encoding(false);
csvSw = new StreamWriter(csvFile, utf8WithoutBom);
csvSw.BaseStream.Seek(0, SeekOrigin.End);
}
else
{
csvFile = new FileStream(System.IO.Path.GetDirectoryName(dbPath) + "\\" + csvName + ".csv", FileMode.Create);
var utf8WithoutBom = new System.Text.UTF8Encoding(false);
csvSw = new StreamWriter(csvFile, utf8WithoutBom);
}
int count = int.Parse(dt.Rows[0][0].ToString());
int arrayNum = count / 500 + 1;
DataTable dtArray = null;
for (int i = 0; i < arrayNum; i++)
{
dtArray = new DataTable();
string sqlStr = "SELECT * FROM path_data WHERE origin_city = '" + csvName + "' limit " + (i * 500).ToString() + "," + 500.ToString();
SQLiteCommand command = m_dbConnection.CreateCommand();
command.CommandText = sqlStr;
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
dataAdapter.Fill(dtArray);
for (int j = 0; j < dtArray.Rows.Count; j++)
{
for (int k = 0; k < 7; k++)
{
csvSw.Write(dtArray.Rows[j][k] + ",");
}
csvSw.WriteLine("\"" + dtArray.Rows[j][7] + "\"");
}
}
csvSw.Close();
csvFile.Close();
}
else
{
string conStr = @"Data Source =" + dbPath;
m_dbConnection = new SQLiteConnection(conStr);
m_dbConnection.Open();
int index = csvPath.IndexOf('+');
string origin_lng = csvPath.Substring(index + 1, csvPath.Length - index - 1);
string origin_lat = csvPath.Substring(0, index);
string CalculateNumSql = "SELECT count(*) FROM path_data WHERE origin_lat = '" + origin_lat + "' AND origin_lng = '" + origin_lng + "'";
SQLiteCommand cmd1 = m_dbConnection.CreateCommand();
cmd1.CommandText = CalculateNumSql;
SQLiteDataReader reader = cmd1.ExecuteReader();
reader.Read();
FileStream csvFile = null;
StreamWriter csvSw = null;
if (File.Exists(System.IO.Path.GetDirectoryName(dbPath) + "\\" + csvPath + ".csv"))
{
//csvFile = new FileStream(System.IO.Path.GetDirectoryName(dbPath) + "\\" + csvPath + "routes"+ ".csv", FileMode.Open);
//var utf8WithoutBom = new System.Text.UTF8Encoding(false);
//csvSw = new StreamWriter(csvFile, utf8WithoutBom);
//csvSw.BaseStream.Seek(0, SeekOrigin.End);
File.Delete(System.IO.Path.GetDirectoryName(dbPath) + "\\" + csvPath + ".csv");
}
csvFile = new FileStream(System.IO.Path.GetDirectoryName(dbPath) + "\\" + csvPath + "_routes" + ".csv", FileMode.Create);
var utf8WithoutBom = new System.Text.UTF8Encoding(false);
csvSw = new StreamWriter(csvFile, utf8WithoutBom);
int count = int.Parse(reader[0].ToString());
int arrayNum = count / 500 + 1;
DataTable dtArray = null;
for (int i = 0; i < arrayNum; i++)
{
dtArray = new DataTable();
string sqlStr = "SELECT * FROM path_data WHERE origin_lat = '" + origin_lat + "' AND origin_lng = '" + origin_lng + "'" + " limit " + (i * 500).ToString() + "," + 500.ToString();
SQLiteCommand command = m_dbConnection.CreateCommand();
command.CommandText = sqlStr;
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
dataAdapter.Fill(dtArray);
for (int j = 0; j < dtArray.Rows.Count; j++)
{
for (int k = 0; k < dtArray.Columns.Count; k++)
{
csvSw.Write(dtArray.Rows[j][k] + ",");
}
csvSw.WriteLine();
idArray.Add(dtArray.Rows[j][0].ToString());
}
}
csvSw.Close();
csvFile.Close();
CarModeReadsubPaths(dbPath, csvPath, idArray);
idArray.Clear();
}
}
public static IEnumerable<Registro> DeleteMethod(SQLiteConnection db, int id)
{
return db.Query<Registro>("DELETE FROM Registro where Id=?", id);
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
Process process = new Process();
process.StartInfo.FileName = "/bin/bash";
process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'";
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.Start();
using (StreamReader reader = process.StandardOutput) {
tainted_2 = reader.ReadToEnd();
process.WaitForExit();
process.Close();
}
tainted_3 = tainted_2;
if ((4 + 2 >= 42))
{
{}
}
else
{
StringBuilder escape = new StringBuilder();
for (int i = 0; i < tainted_2.Length; ++i)
{
char current = tainted_2[i];
switch (current)
{
case '\\':
escape.Append(@"\5c");
break;
case '*':
escape.Append(@"\2a");
break;
case '(':
escape.Append(@"\28");
break;
case ')':
escape.Append(@"\29");
break;
case '\u0000':
escape.Append(@"\00");
break;
case '/':
escape.Append(@"\2f");
break;
default:
escape.Append(current);
break;
}
}
tainted_3 = escape.ToString();
}
//flaw
string query = "SELECT * FROM Articles WHERE id=" + tainted_3;
SQLiteConnection dbConnection = null;
try{
dbConnection = new SQLiteConnection("data source=C:\\data");
SQLiteCommand command = new SQLiteCommand(query, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
private void ExecuteSql(string sql)
{
switch (DbType)
{
case Type.SqlServer:
using (var conn = new SqlConnection(ConnectionString))
{
var comm = new SqlCommand(sql, conn);
ExecSql(conn, comm);
}
break;
case Type.Access:
using (var conn = new OleDbConnection(ConnectionString))
{
var comm = new OleDbCommand(sql, conn);
ExecSql(conn, comm);
}
break;
case Type.Oracle:
using (var conn = new OracleConnection(ConnectionString))
{
var comm = new OracleCommand(sql, conn);
ExecSql(conn, comm);
}
break;
case Type.MySql:
using (var conn = new MySqlConnection(ConnectionString))
{
var comm = new MySqlCommand(sql, conn);
ExecSql(conn, comm);
}
break;
case Type.Sqlite:
using (var conn = new SQLiteConnection(ConnectionString))
{
var comm = new SQLiteCommand(sql, conn);
ExecSql(conn, comm);
}
break;
case Type.PostGreSql:
using (var conn = new NpgsqlConnection(ConnectionString))
{
var comm = new NpgsqlCommand(sql, conn);
ExecSql(conn, comm);
}
break;
case Type.Teradata:
using (var conn = new TdConnection(ConnectionString))
{
var comm = new TdCommand(sql, conn);
ExecSql(conn, comm);
}
break;
case Type.Odbc:
using (var conn = new OdbcConnection(ConnectionString))
{
var comm = new OdbcCommand(sql, conn);
ExecSql(conn, comm);
}
break;
}
}
private void Calc_Nhour_Pop_GDP_Btn_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
sw.Start();
int Allhour = 10;
OpenFileDialog dialog2 = new OpenFileDialog();
dialog2.Title = "请选择文件";
dialog2.Filter = "db文件(*.db)|*.db";
if (dialog2.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
string dbPath = dialog2.FileName;
string conStr = @"Data Source =" + dbPath;
m_dbConnection = new SQLiteConnection(conStr);
m_dbConnection.Open();
for (int i = 1; i <= Allhour; i++)
{
SQLiteCommand commandTemp = m_dbConnection.CreateCommand();
string sql_AddPop = "ALTER TABLE county_info ADD '" + i.ToString() + "hourPop' DOUBLE";
commandTemp.CommandText = sql_AddPop;
try
{
commandTemp.ExecuteNonQuery();
}
catch (System.Exception ex)
{
continue;
}
string sql_AddGDP = "ALTER TABLE county_info ADD '" + i.ToString() + "hourGDP' DOUBLE";
commandTemp.CommandText = sql_AddGDP;
try
{
commandTemp.ExecuteNonQuery();
}
catch (System.Exception ex)
{
continue;
}
string sql_AddCounty_Array = "ALTER TABLE county_info ADD '" + i.ToString() + "County_Array' STRING";
commandTemp.CommandText = sql_AddCounty_Array;
try
{
commandTemp.ExecuteNonQuery();
}
catch (System.Exception ex)
{
continue;
}
}
DataTable dTable = new DataTable();
string sql = "SELECT * FROM county_info";
//开启事务
var transaction = m_dbConnection.BeginTransaction();
SQLiteCommand command = m_dbConnection.CreateCommand();
command.CommandText = sql;
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
dataAdapter.Fill(dTable);
//提交事务
transaction.Commit();
Double[] AllPop = new Double[Allhour];
Double[] AllGDP = new Double[Allhour];
string[] AllCounty_Array = new string[Allhour];
ProgressBar ProgressFm2 = new ProgressBar(2, 100);
ProgressFm2.Show(this);
ProgressFm2.setPos(0);//设置进度条位置
int numOrder = dTable.Rows.Count;
for (int i = 0; i < numOrder; i++)
{
string origin = dTable.Rows[i][2].ToString();
string origin_Region = dTable.Rows[i][3].ToString();
string sql_SelectCountyPath = "SELECT destination,destination_region,duration_s FROM path_info WHERE origin = '" + origin + "' AND origin_region = '" + origin_Region + "'";
var transaction2 = m_dbConnection.BeginTransaction();
SQLiteCommand cmd_SelectCountyPath = m_dbConnection.CreateCommand();
cmd_SelectCountyPath.CommandText = sql_SelectCountyPath;
SQLiteDataAdapter dataAdapter2 = new SQLiteDataAdapter(cmd_SelectCountyPath);
DataTable dT_CountyPath = new DataTable();
dataAdapter2.Fill(dT_CountyPath);
transaction2.Commit();
var transaction3 = m_dbConnection.BeginTransaction();
for (int l = 0; l < Allhour; l++)
{
if (l == 0)
{
try
{
AllPop[l] = double.Parse(dTable.Rows[i][4].ToString());
AllGDP[l] = double.Parse(dTable.Rows[i][5].ToString());
AllCounty_Array[l] = dTable.Rows[i][1].ToString() + ";";
}
catch (System.Exception ex)
{
AllPop[l] = 0;
AllGDP[l] = 0;
AllCounty_Array[l] = dTable.Rows[i][1].ToString() + ";";
}
continue;
}
try
{
AllPop[l] = 0;
AllGDP[l] = 0;
AllCounty_Array[l] = "";
}
catch (System.Exception ex)
{
}
}
for (int j = 0; j < dT_CountyPath.Rows.Count; j++)
{
string cityFullName = dT_CountyPath.Rows[j][1].ToString() + dT_CountyPath.Rows[j][0].ToString();
int time = 0;
if (double.Parse(dT_CountyPath.Rows[j][2].ToString()) % 3600 == 0)
{
time = (int)(double.Parse(dT_CountyPath.Rows[j][2].ToString()) / 3600);
}
else
{
time = (int)(double.Parse(dT_CountyPath.Rows[j][2].ToString()) / 3600) + 1;
}
if (time > 10)
{
continue;
}
string sql_Select_Pop_Gdp = "SELECT population,GDP FROM county_info WHERE City_FullName = '" + cityFullName + "'";
SQLiteCommand cmd_Select_Pop_Gdp = m_dbConnection.CreateCommand();
cmd_Select_Pop_Gdp.CommandText = sql_Select_Pop_Gdp;
SQLiteDataReader reader = cmd_Select_Pop_Gdp.ExecuteReader();
reader.Read();
try
{
double poplation = double.Parse(reader[0].ToString());
double GDP = double.Parse(reader[1].ToString());
AllPop[time - 1] += poplation;
AllGDP[time - 1] += GDP;
AllCounty_Array[time - 1] += cityFullName + ";";
}
catch (System.Exception ex)
{
continue;
}
}
for (int k = 1; k < Allhour; k++)
{
AllPop[k] += AllPop[k - 1];
AllGDP[k] += AllPop[k - 1];
AllCounty_Array[k] = AllCounty_Array[k - 1] + AllCounty_Array[k];
}
for (int k = 0; k < Allhour; k++)
{
string sql_Update_Pop_Gdp = "UPDATE county_info SET '" + (k + 1).ToString() + "hourPop' = '" + AllPop[k].ToString() + "','" + (k + 1).ToString() + "hourGDP' = '" + AllGDP[k].ToString()
+ "','" + (k + 1).ToString() + "County_Array' = '" + AllCounty_Array[k] + "' WHERE City_FullName = '" + origin_Region + origin + "'"; //加Where条件
SQLiteCommand cmd_Update_Pop_Gdp = m_dbConnection.CreateCommand();
cmd_Update_Pop_Gdp.CommandText = sql_Update_Pop_Gdp;
cmd_Update_Pop_Gdp.ExecuteNonQuery();
}
transaction3.Commit();
ProgressFm2.setPos((int)((double)(i + 1) / (double)numOrder * 100));//设置进度条位置
}
ProgressFm2.Close();
sw.Stop();
TimeSpan ts2 = sw.Elapsed;
MessageBox.Show((ts2.TotalMilliseconds / 1000).ToString("0.0") + "Finished");
}
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
try
{
string dpPath = System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "user.db3");
var db = new SQLiteConnection(dpPath);
db.CreateTable <Mozos>();
Mozos tblogin = new Mozos();
tblogin.Nombre = "Maria";
tblogin.Apellido = "Paz";
tblogin.Email = "*****@*****.**";
tblogin.Direccion = "Almafuerte 2030, San Justo";
tblogin.Contraseña = "Pass";
db.Insert(tblogin);
db.CreateTable <Propinas>();
Propinas propina1 = new Propinas()
{
fecha = DateTime.Now,
monto = 80,
idCliente = 1,
idEmpleado = 1
};
db.Insert(propina1);
// iv = (ImageView)findViewById(R.id.myimage);
}
catch
{
}
SetContentView(Propishare.Resource.Layout.MozoLogin);
// Create your application here
// Get our button from the layout resource,
// and attach an event to it
btnLogin = FindViewById <Button>(Resource.Id.btnLogin);
// btncreate = FindViewById<Button>(Resource.Id.btnregister);
txtEmail = FindViewById <EditText>(Resource.Id.Email);
txtContraseña = FindViewById <EditText>(Resource.Id.txtContraseñaMozo);
btnLogin.Click += Btnsign_Click;
// btncreate.Click += Btncreate_Click;
CreateDB();
//private void Btncreate_Click(object sender, EventArgs e)
//{
// StartActivity(typeof(RegisterActivity));
//}
//
}
public DataStore(string databaseName)
{
DatabaseName = databaseName;
_connection = SQLite.GetConnection(DatabaseName);
}
public LocationDB(string dbPath)
{
database = new SQLiteConnection(dbPath);
database.CreateTable <SavedLocation>();
}
public AchievementRepository(string dbPath)
{
_db = new SQLiteConnection(dbPath);
_db.CreateTable <AchievementModel>();
_db.CreateTable <AchievementStep>();
}
//Search
public static List <SearchModel> LoadSearch(SearchModel user)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
var output = cnn.Query <SearchModel>("", new DynamicParameters());
if (user.Value == 1)
{
Console.WriteLine("value = 1");
if (user.SearchField != null)
{
output = cnn.Query <SearchModel>("select fname, lname, eventid, jumpnr, finalscore " +
"from jump JOIN (competitor JOIN user ON user.userID = competitor.userID) ON jump.competitorID = competitor.competitorID " +
"where fname = '" + user.SearchField + "'", new DynamicParameters());
}
else
{
output = cnn.Query <SearchModel>("select fname, lname, eventid, jumpnr, finalscore " +
"from jump JOIN (competitor JOIN user ON user.userID = competitor.userID) " +
"ON jump.competitorID = competitor.competitorID ", new DynamicParameters());
}
return(output.ToList());
}
else if (user.Value == 2)
{
Console.WriteLine("value = 2");
if (user.SearchField != null)
{
output = cnn.Query <SearchModel>("select fname, lname, event.eventid, startdate " +
"from user JOIN (judge JOIN (eventjudge JOIN event ON event.eventID = eventjudge.eventID) " +
"ON judge.judgeID = eventjudge.judgeID) ON user.userID = judge.userID " +
"where fname = '" + user.SearchField + "'", new DynamicParameters());
}
else
{
output = cnn.Query <SearchModel>("select fname, lname, event.eventid, startdate " +
"from user JOIN (judge JOIN (eventjudge JOIN event ON event.eventid = eventjudge.eventid) " +
"ON judge.judgeid = eventjudge.judgeid) ON user.userid = judge.userid ", new DynamicParameters());
}
return(output.ToList());
}
else if (user.Value == 3)
{
Console.WriteLine("value = 3");
if (user.SearchField != null)
{
output = cnn.Query <SearchModel>("SELECT event.eventid, startdate, competitorid, jumpnr, finalscore " +
"FROM Jump JOIN event ON jump.eventid = event.eventid " +
"WHERE event.eventid ='" + user.SearchField + "'", new DynamicParameters());
}
else
{
output = cnn.Query <SearchModel>("SELECT event.eventid, startdate, competitorid, jumpnr, finalscore " +
"FROM Jump JOIN event ON jump.eventid = event.eventid ", new DynamicParameters());
}
return(output.ToList());
}
else
{
Console.WriteLine("Error");
return(null);
}
}
}
public frmSearchProductStockin(frmStockIn flist)
{
InitializeComponent();
slist = flist;
cn = new SQLiteConnection(dbcon.MyConnection());
}
private void extract_Routes_City_Info_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
sw.Start();
OpenFileDialog dialog = new OpenFileDialog();
dialog.Title = "请选择附加路径文件";
dialog.Filter = "txt文件(*.txt)|*.txt";
if (dialog.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
string txtPath = dialog.FileName;
FileStream txtFS = new FileStream(txtPath, FileMode.Open);
var utf8WithoutBom = new System.Text.UTF8Encoding(false);
StreamReader txtSR = new StreamReader(txtFS, utf8WithoutBom);
StreamWriter txtSW = new StreamWriter(txtFS, utf8WithoutBom);
string aryLine = null;
OpenFileDialog dialog2 = new OpenFileDialog();
dialog2.Title = "请选择文件";
dialog2.Filter = "db文件(*.db)|*.db";
if (dialog2.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
string dbPath = dialog2.FileName;
//string dbPath = @"C:\Users\i\Desktop\最新版本程序\CountySeat_info.db";
string conStr = @"Data Source =" + dbPath;
m_dbConnection = new SQLiteConnection(conStr);
m_dbConnection.Open();
double index = 0;
ProgressBar ProgressFm2 = new ProgressBar(2, 100);
ProgressFm2.Show(this);
ProgressFm2.setPos(0);//设置进度条位置
int num = 0;
while ((aryLine = txtSR.ReadLine()) != null)
{
num++;
}
//txtSR.BaseStream.Seek(0, SeekOrigin.Begin);
txtFS.Close();
txtSR.Close();
FileStream txtFS2 = new FileStream(txtPath, FileMode.Open);
StreamReader txtSR2 = new StreamReader(txtFS2, utf8WithoutBom);
while ((aryLine = txtSR2.ReadLine()) != null)
{
FileStream csvFS = new FileStream(System.IO.Path.GetDirectoryName(txtPath) + "\\" + aryLine + ".csv", FileMode.Open);
StreamReader csvSR = new StreamReader(csvFS, utf8WithoutBom);
SQLiteCommand cmd = m_dbConnection.CreateCommand();
int flag = 0;
string pathInfo = "";
string[] pathInfoArray;
var transaction = m_dbConnection.BeginTransaction();
while ((pathInfo = csvSR.ReadLine()) != null)
{
pathInfoArray = pathInfo.Split(',');
if (flag == 0)
{
string sql = "INSERT INTO county_info VALUES('" + pathInfoArray[7] + pathInfoArray[5] + "','" + pathInfoArray[5] + "','" + pathInfoArray[7] + "','" + pathInfoArray[2] + "','" + pathInfoArray[1] + "')";
cmd.CommandText = sql;
try
{
cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
continue;
}
flag++;
}
SQLiteCommand command = m_dbConnection.CreateCommand();
string sqlStr = "INSERT INTO path_info VALUES(";
sqlStr += pathInfoArray[0] + ",'";
for (int i = 1; i < 10; i++)
{
sqlStr += pathInfoArray[i] + "','";
}
sqlStr += pathInfoArray[10] + "')";
command.CommandText = sqlStr;
try
{
command.ExecuteNonQuery();
}
catch (System.Exception ex)
{
continue;
}
}
transaction.Commit();
csvSR.Close();
csvFS.Close();
index++;
ProgressFm2.setPos((int)(((index + 1) / num) * 100));//设置进度条位置
}
txtSR2.Close();
txtFS2.Close();
m_dbConnection.Close();
sw.Stop();
TimeSpan ts2 = sw.Elapsed;
ProgressFm2.Close();
MessageBox.Show((ts2.TotalMilliseconds / 1000).ToString("0.0") + "Finished");
//MessageBox.Show("Finished!");
}
private void Add_Pop_Gdp_Btn_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
sw.Start();
OpenFileDialog dialog = new OpenFileDialog();
dialog.Title = "请选择人口和GDP文件";
dialog.Filter = "csv文件(*.csv)|*.csv";
if (dialog.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
string csvPath = dialog.FileName;
FileStream csvFS = new FileStream(csvPath, FileMode.Open);
var utf8WithoutBom = new System.Text.UTF8Encoding(false);
StreamReader csvSR = new StreamReader(csvFS, utf8WithoutBom);
int numOrder = 0;
string aryLine;
while ((aryLine = csvSR.ReadLine()) != null)
{
numOrder++;
}
csvSR.BaseStream.Seek(0, SeekOrigin.Begin);
OpenFileDialog dialog2 = new OpenFileDialog();
dialog2.Title = "请选择文件";
dialog2.Filter = "db文件(*.db)|*.db";
if (dialog2.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
string dbPath = dialog2.FileName;
string conStr = @"Data Source =" + dbPath;
m_dbConnection = new SQLiteConnection(conStr);
m_dbConnection.Open();
double index = -1;
ProgressBar ProgressFm2 = new ProgressBar(2, 100);
ProgressFm2.Show(this);
ProgressFm2.setPos(0);//设置进度条位置
var transaction = m_dbConnection.BeginTransaction();
while ((aryLine = csvSR.ReadLine()) != null)
{
if (index == -1)
{
index++;
continue;
}
string[] info = aryLine.Split(',');
string cityFullname = info[2] + info[1];
string sql = "UPDATE county_info SET id = " + info[0] + ",population = " + info[5] + ",GDP = " + info[6] + " WHERE City_FullName = '" + cityFullname + "'";
SQLiteCommand cmd = m_dbConnection.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
index++;
ProgressFm2.setPos((int)(((index + 1) / numOrder) * 100));//设置进度条位置
}
transaction.Commit();
ProgressFm2.Close();
sw.Stop();
TimeSpan ts2 = sw.Elapsed;
MessageBox.Show((ts2.TotalMilliseconds / 1000).ToString("0.0") + "Finished");
}
private void FindAverages()
{
// This is lazy, should fix
// Define variables
var thisMonth = DateTime.Today;
var lastMonth = DateTime.Today.AddMonths(-1);
var thirdMonth = DateTime.Today.AddMonths(-2);
var fourthMonth = DateTime.Today.AddMonths(-3);
var fifthMonth = DateTime.Today.AddMonths(-4);
var sixthMonth = DateTime.Today.AddMonths(-5);
var seventhMonth = DateTime.Today.AddMonths(-6);
var thisYear = DateTime.Today.Year.ToString("yyyy", CultureInfo.InvariantCulture);
var lastYear = DateTime.Today.AddYears(-1);
Int64 threeAvg = 0;
Int64 sixAvg = 0;
Int64 weekAvg = 0;
// Create a connection to the DB
SQLiteConnection m_dbConnection;
m_dbConnection = new SQLiteConnection("Data Source=GuardDB.sqlite;Version=3");
m_dbConnection.Open();
// Check for special cases! (switch case pitched a fit so if/else it is for now
if (thisMonth.ToString("MMMM", CultureInfo.InvariantCulture) == "January")
{
string currMonth = thisMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string prevMonth = lastMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string thrdMonth = thirdMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string frthMonth = fourthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string ffthMonth = fifthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string sxthMonth = sixthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string svnhMonth = seventhMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string currYear = thisMonth.ToString("yyyy", CultureInfo.InvariantCulture);
string prevYear = lastYear.ToString("yyyy", CultureInfo.InvariantCulture);
SQLiteCommand FindThreeMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + prevYear + ")", m_dbConnection);
SQLiteCommand FindSixMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + ffthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + sxthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + svnhMonth + "'" + "AND year=" + prevYear + ")", m_dbConnection);
SQLiteDataReader threeMonth = FindThreeMonth.ExecuteReader();
while (threeMonth.Read())
{
threeAvg = threeMonth.GetInt64(1);
}
SQLiteDataReader sixMonth = FindSixMonth.ExecuteReader();
while (sixMonth.Read())
{
sixAvg = sixMonth.GetInt64(1);
}
//weekAvg = threeAvg / 15;
threeAvg = threeAvg / 3;
sixAvg = sixAvg / 6;
sixMonthAvgLabel.Text = sixAvg.ToString();
threeMonthAvgLabel.Text = threeAvg.ToString();
//fiveWeekAvgLabel.Text = weekAvg.ToString();
}
if (thisMonth.ToString("MMMM", CultureInfo.InvariantCulture) == "February")
{
string currMonth = thisMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string prevMonth = lastMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string thrdMonth = thirdMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string frthMonth = fourthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string ffthMonth = fifthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string sxthMonth = sixthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string svnhMonth = seventhMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string currYear = thisMonth.ToString("yyyy", CultureInfo.InvariantCulture);
string prevYear = lastYear.ToString("yyyy", CultureInfo.InvariantCulture);
SQLiteCommand FindThreeMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + prevYear + ")"
+ " GROUP BY month", m_dbConnection);
SQLiteCommand FindSixMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + ffthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + sxthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + svnhMonth + "'" + "AND year=" + prevYear + ")", m_dbConnection);
SQLiteDataReader threeMonth = FindThreeMonth.ExecuteReader();
while (threeMonth.Read())
{
threeAvg = threeMonth.GetInt64(1);
}
SQLiteDataReader sixMonth = FindSixMonth.ExecuteReader();
while (sixMonth.Read())
{
sixAvg = sixMonth.GetInt64(1);
}
//weekAvg = threeAvg / 15;
threeAvg = threeAvg / 3;
sixAvg = sixAvg / 6;
threeMonthAvgLabel.Text = threeAvg.ToString();
sixMonthAvgLabel.Text = sixAvg.ToString();
//fiveWeekAvgLabel.Text = weekAvg.ToString();
}
if (thisMonth.ToString("MMMM", CultureInfo.InvariantCulture) == "March")
{
string currMonth = thisMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string prevMonth = lastMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string thrdMonth = thirdMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string frthMonth = fourthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string ffthMonth = fifthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string sxthMonth = sixthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string svnhMonth = seventhMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string currYear = thisMonth.ToString("yyyy", CultureInfo.InvariantCulture);
string prevYear = lastYear.ToString("yyyy", CultureInfo.InvariantCulture);
SQLiteCommand FindThreeMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + prevYear + ")"
+ " GROUP BY month", m_dbConnection);
SQLiteCommand FindSixMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + ffthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + sxthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + svnhMonth + "'" + "AND year=" + prevYear + ")", m_dbConnection);
SQLiteDataReader threeMonth = FindThreeMonth.ExecuteReader();
while (threeMonth.Read())
{
threeAvg = threeMonth.GetInt64(1);
}
SQLiteDataReader sixMonth = FindSixMonth.ExecuteReader();
while (sixMonth.Read())
{
sixAvg = sixMonth.GetInt64(1);
}
//weekAvg = threeAvg / 15;
threeAvg = threeAvg / 3;
sixAvg = sixAvg / 6;
threeMonthAvgLabel.Text = threeAvg.ToString();
sixMonthAvgLabel.Text = sixAvg.ToString();
//fiveWeekAvgLabel.Text = weekAvg.ToString();
}
if (thisMonth.ToString("MMMM", CultureInfo.InvariantCulture) == "April")
{
string currMonth = thisMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string prevMonth = lastMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string thrdMonth = thirdMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string frthMonth = fourthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string ffthMonth = fifthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string sxthMonth = sixthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string svnhMonth = seventhMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string currYear = thisMonth.ToString("yyyy", CultureInfo.InvariantCulture);
string prevYear = lastYear.ToString("yyyy", CultureInfo.InvariantCulture);
SQLiteCommand FindThreeMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + currYear + ")"
+ " GROUP BY month", m_dbConnection);
SQLiteCommand FindSixMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + ffthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + sxthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + svnhMonth + "'" + "AND year=" + prevYear + ")", m_dbConnection);
SQLiteDataReader threeMonth = FindThreeMonth.ExecuteReader();
while (threeMonth.Read())
{
threeAvg = threeMonth.GetInt64(1);
}
SQLiteDataReader sixMonth = FindSixMonth.ExecuteReader();
while (sixMonth.Read())
{
sixAvg = sixMonth.GetInt64(1);
}
//weekAvg = threeAvg / 15;
threeAvg = threeAvg / 3;
sixAvg = sixAvg / 6;
threeMonthAvgLabel.Text = threeAvg.ToString();
sixMonthAvgLabel.Text = sixAvg.ToString();
//fiveWeekAvgLabel.Text = weekAvg.ToString();
}
if (thisMonth.ToString("MMMM", CultureInfo.InvariantCulture) == "May")
{
string currMonth = thisMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string prevMonth = lastMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string thrdMonth = thirdMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string frthMonth = fourthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string ffthMonth = fifthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string sxthMonth = sixthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string svnhMonth = seventhMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string currYear = thisMonth.ToString("yyyy", CultureInfo.InvariantCulture);
string prevYear = lastYear.ToString("yyyy", CultureInfo.InvariantCulture);
SQLiteCommand FindThreeMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + currYear + ")"
+ " GROUP BY month", m_dbConnection);
SQLiteCommand FindSixMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + ffthMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + sxthMonth + "'" + "AND year=" + prevYear + ")"
+ "OR (month = '" + svnhMonth + "'" + "AND year=" + prevYear + ")", m_dbConnection);
SQLiteDataReader threeMonth = FindThreeMonth.ExecuteReader();
while (threeMonth.Read())
{
threeAvg = threeMonth.GetInt64(1);
}
SQLiteDataReader sixMonth = FindSixMonth.ExecuteReader();
while (sixMonth.Read())
{
sixAvg = sixMonth.GetInt64(1);
}
//weekAvg = threeAvg / 15;
threeAvg = threeAvg / 3;
sixAvg = sixAvg / 6;
threeMonthAvgLabel.Text = threeAvg.ToString();
sixMonthAvgLabel.Text = sixAvg.ToString();
//fiveWeekAvgLabel.Text = weekAvg.ToString();
}
if (thisMonth.ToString("MMMM", CultureInfo.InvariantCulture) == "June")
{
string currMonth = thisMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string prevMonth = lastMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string thrdMonth = thirdMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string frthMonth = fourthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string ffthMonth = fifthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string sxthMonth = sixthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string svnhMonth = seventhMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string currYear = thisMonth.ToString("yyyy", CultureInfo.InvariantCulture);
string prevYear = lastYear.ToString("yyyy", CultureInfo.InvariantCulture);
SQLiteCommand FindThreeMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + currYear + ")"
+ " GROUP BY month", m_dbConnection);
SQLiteCommand FindSixMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE (month = '" + prevMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + thrdMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + frthMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + ffthMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + sxthMonth + "'" + "AND year=" + currYear + ")"
+ "OR (month = '" + svnhMonth + "'" + "AND year=" + prevYear + ")", m_dbConnection);
SQLiteDataReader threeMonth = FindThreeMonth.ExecuteReader();
while (threeMonth.Read())
{
threeAvg = threeMonth.GetInt64(1);
}
SQLiteDataReader sixMonth = FindSixMonth.ExecuteReader();
while (sixMonth.Read())
{
sixAvg = sixMonth.GetInt64(1);
}
//weekAvg = threeAvg / 15;
threeAvg = threeAvg / 3;
sixAvg = sixAvg / 6;
threeMonthAvgLabel.Text = threeAvg.ToString();
sixMonthAvgLabel.Text = sixAvg.ToString();
//fiveWeekAvgLabel.Text = weekAvg.ToString();
}
else
{
string currMonth = thisMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string prevMonth = lastMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string thrdMonth = thirdMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string frthMonth = fourthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string ffthMonth = fifthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string sxthMonth = sixthMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string svnhMonth = seventhMonth.ToString("MMMM", CultureInfo.InvariantCulture);
string currYear = thisMonth.ToString("yyyy", CultureInfo.InvariantCulture);
string prevYear = lastYear.ToString("yyyy", CultureInfo.InvariantCulture);
SQLiteCommand FindThreeMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE month IN ('" + prevMonth + "'" + ","
+ "'" + thrdMonth + "'" + ","
+ "'" + svnhMonth + "'"
+ ") AND year = " + currYear + "", m_dbConnection);
SQLiteCommand FindSixMonth = new SQLiteCommand("SELECT month, COUNT(timestamp) AS total FROM Gregorian WHERE month IN ('" + prevMonth + "'" + ","
+ "'" + thrdMonth + "'" + ","
+ "'" + frthMonth + "'" + ","
+ "'" + ffthMonth + "'" + ","
+ "'" + sxthMonth + "'" + ","
+ "'" + svnhMonth + "'"
+ ") AND year = " + currYear + "", m_dbConnection);
SQLiteDataReader threeMonth = FindThreeMonth.ExecuteReader();
while (threeMonth.Read())
{
threeAvg = threeMonth.GetInt64(1);
}
SQLiteDataReader sixMonth = FindSixMonth.ExecuteReader();
while (sixMonth.Read())
{
sixAvg = sixMonth.GetInt64(1);
}
//weekAvg = threeAvg / 15;
threeAvg = threeAvg / 3;
sixAvg = sixAvg / 6;
threeMonthAvgLabel.Text = threeAvg.ToString();
sixMonthAvgLabel.Text = sixAvg.ToString();
//fiveWeekAvgLabel.Text = weekAvg.ToString();
}
m_dbConnection.Close();
}
public FormComments(SQLiteConnection db)
{
this.db = db;
InitializeComponent();
}
public Program()
{
md5 = MD5.Create();
if (!File.Exists("hashtable.db"))
{
SQLiteConnection.CreateFile("hashtable.db");
}
sql_conn = new SQLiteConnection("Data source=hashtable.db");
sql_conn.Open();
sql_cmd = sql_conn.CreateCommand();
sql_cmd.CommandText = @"CREATE TABLE IF NOT EXISTS md5 (num INTEGER PRIMARY KEY AUTOINCREMENT, hash TEXT, file TEXT, user TEXT, time TEXT)";
sql_cmd.ExecuteNonQuery();
try
{
FileStream htmlfs = new FileStream("user.html", FileMode.Open);
StreamReader htmlsr = new StreamReader(htmlfs);
userSiteTemplate = htmlsr.ReadToEnd();
htmlsr.Close();
htmlfs.Close();
}
catch
{
}
if (!Directory.Exists("images"))
{
Directory.CreateDirectory("images");
}
if (File.Exists("imageIndex"))
{
try
{
FileStream readIndexfs = new FileStream("imageIndex", FileMode.Open);
readIndexfs.Seek(0, SeekOrigin.Begin);
StreamReader sr = new StreamReader(readIndexfs);
nextIndex = Int64.Parse(sr.ReadLine());
sr.Close();
readIndexfs.Close();
}
catch
{
FileStream readIndexfs = new FileStream("imageIndex", FileMode.Create);
readIndexfs.Seek(0, SeekOrigin.Begin);
StreamWriter sw = new StreamWriter(readIndexfs);
sw.WriteLine(0);
sw.Close();
readIndexfs.Close();
}
}
else
{
FileStream readIndexfs = new FileStream("imageIndex", FileMode.Create);
readIndexfs.Seek(0, SeekOrigin.Begin);
StreamWriter sw = new StreamWriter(readIndexfs);
sw.WriteLine(0);
sw.Close();
readIndexfs.Close();
}
listener = new TcpListener(System.Net.IPAddress.Any, 8888);
listener.Start();
httpThread = new Thread(httpListen);
httpThread.Start();
while (true)
{
TcpClient client = listener.AcceptTcpClient();
Thread thread = new Thread(new ParameterizedThreadStart(client_handler));
thread.Start(client);
}
}
private SQLiteConnection Conn; // The XML connection
/// <summary>
/// Constructor per default
/// </summary>
public DBManager()
{
Conn = DBConnection.GetInstance().GetDataBase();
}
private void GenTrend()
{
// Get Current Month
string currMonth = DateTime.Today.ToString("MMMM", CultureInfo.InvariantCulture);
// Get Previous Month
string prevMonth = DateTime.Today.AddMonths(-1).ToString("MMMM", CultureInfo.InvariantCulture);
// Get Current Year
string currYear = DateTime.Today.ToString("yyyy", CultureInfo.InvariantCulture);
// Set Monthly Total
Int64 monthlyTotal = 0;
// Set Previous Monthly Total
Int64 prevTotal = 0;
SQLiteConnection m_dbConnection;
m_dbConnection = new SQLiteConnection("Data Source=GuardDB.sqlite;Version=3");
m_dbConnection.Open();
// Grab value from 3 month trend
string threeMonthAVG_str = threeMonthAvgLabel.Text;
Int64 threeMonthAVG = Convert.ToInt64(threeMonthAVG_str);
//Int64 fiveWeekAVG = Convert.ToInt64(fiveWeekAvgLabel.Text);
// Get months count
SQLiteCommand currentMonthTotal = new SQLiteCommand("SELECT month, COUNT(*) AS total FROM Gregorian WHERE month = '" + currMonth + "' AND year = " + currYear + " LIMIT 1", m_dbConnection);
// If January
if (currMonth == "January")
{
string prevYear = DateTime.Today.AddYears(-1).ToString("yyyy", CultureInfo.InvariantCulture);
SQLiteCommand prevMonthTotal = new SQLiteCommand("SELECT month, COUNT(*) AS total FROM Gregorian WHERE month = '" + prevMonth + "' AND year = " + prevYear + " LIMIT 1", m_dbConnection);
SQLiteDataReader getPrevTotal = prevMonthTotal.ExecuteReader();
while (getPrevTotal.Read())
{
prevTotal = getPrevTotal.GetInt64(1);
}
}
// If not January
else
{
SQLiteCommand prevMonthTotal = new SQLiteCommand("SELECT month, COUNT(*) AS total FROM Gregorian WHERE month = '" + prevMonth + "' AND year = " + currYear + " LIMIT 1", m_dbConnection);
SQLiteDataReader getPrevTotal = prevMonthTotal.ExecuteReader();
while (getPrevTotal.Read())
{
prevTotal = getPrevTotal.GetInt64(1);
}
}
SQLiteDataReader getMonthlyTotal = currentMonthTotal.ExecuteReader();
while (getMonthlyTotal.Read())
{
monthlyTotal = getMonthlyTotal.GetInt64(1);
}
// Check if current total is approaching previous month
double warningThresh = prevTotal - 5; // five times below the previous months total
double criticalThresh = prevTotal - 2; // two times below the previous months total
double thresh = threeMonthAVG - 1; // genuinely useless
double currWeekAvg = (monthlyTotal / 5);
//double weeklySuggestion = Math.Round(currWeekAvg - fiveWeekAVG, 0);
// If current monthly total is less than the previous monthly total
if (monthlyTotal < warningThresh)
{
trendOutLabel.Text = "Trending Down by " + (prevTotal - monthlyTotal);
adviceTextBox.Text = "You are on track to continue trending downwards. You have " + (threeMonthAVG - monthlyTotal) + " times left before you exceed your 3 month average and " + (prevTotal - monthlyTotal) + " times left this month before you endanger your trend.";
}
else if ((monthlyTotal >= warningThresh) & (monthlyTotal < criticalThresh))
{
trendOutLabel.Text = "Trending Slightly Down by " + (prevTotal - monthlyTotal);
adviceTextBox.Text = "You are approaching your three month average. You have " + (threeMonthAVG - monthlyTotal) + " times left before you exceed your 3 month average and " + (prevTotal - monthlyTotal) + " times left this month before you endanger your trend.";
}
else if ((monthlyTotal >= criticalThresh) & (monthlyTotal < prevTotal))
{
trendOutLabel.Text = "Trend Warning";
adviceTextBox.Text = "You are close to breaking your trend. You have " + (threeMonthAVG - monthlyTotal) + " times left before you exceed your 3 month average and " + (prevTotal - monthlyTotal) + " times left this month before you trend upwards.";
}
else if (monthlyTotal == prevTotal)
{
trendOutLabel.Text = "Trending Steady";
adviceTextBox.Text = "Your current 3 month average is " + threeMonthAVG + " and your current monthly total is " + monthlyTotal + ". You have hit last months total and will trend upwards on next occurrence.";
}
else
{
trendOutLabel.Text = "Trending Up by " + (monthlyTotal - prevTotal);
if (monthlyTotal >= prevTotal)
{
adviceTextBox.Text = "Your current 3 month average is " + threeMonthAVG + " and your current monthly total is " + monthlyTotal + ". You have exceeded last months total and are trending upwards at a rate of +" + (monthlyTotal - prevTotal) + " times this month";
}
else
{
adviceTextBox.Text = "money machine broke - contact developer if you see this message";
}
}
//bool test1 = (monthlyTotal > warningThresh);
}
public SQLManager(string dbPath)
{
connection = new SQLiteConnection(dbPath);
connection.CreateTable <T>();
}
public DataBaseService()
{
connection = new SQLiteConnection($"Data Source={Path.GetFullPath(@"..\..\")}AmicsDeLaMusica.db;New=False;");
}
public StockRepository() : base()
{
conn = DependencyService.Get <ISQLite>().GetConnection();
conn.CreateTable <Stock>();
}
public static void ExtractCredentials(string loginsJsonPath, string keyDBPath, string masterPassword)
{
if (!File.Exists(loginsJsonPath))
{
return;
}
if (!File.Exists(keyDBPath))
{
return;
}
// Prep ASN parser
Asn1Der asn = new Asn1Der();
bool someResults = false;
SQLiteConnection database = null;
try
{
database = new SQLiteConnection(keyDBPath, SQLiteOpenFlags.ReadOnly, false);
}
catch (Exception e)
{
Console.WriteLine("[X] {0}", e.InnerException.Message);
return;
}
string query = "SELECT item1,item2 FROM metadata WHERE id = 'password';";
List <SQLiteQueryRow> results = database.Query2(query, false);
foreach (SQLiteQueryRow row in results)
{
// Global salt - item1
var globalSalt = (byte[])row.column[0].Value;
// Parse ASN from item2
var item2Byte = (byte[])row.column[1].Value;
Asn1DerObject item2 = asn.Parse(item2Byte);
string asnString = item2.ToString();
// Password check
// Check for pbeWithSha1AndTripleDES-CBC algorithm OID in ASN (1.2.840.113549.1.12.5.1.3)
// Use to decrypt password-check if found
if (asnString.Contains("2A864886F70D010C050103"))
{
// Get Entry Salt (password-check)
var entrySalt = item2.objects[0].objects[0].objects[1].objects[0].Data;
// Get ciphertext (password-check)
var cipherText = item2.objects[0].objects[1].Data;
// Decrypt password-check ciphertext & check if master password is correct
decryptMoz3DES CheckPwd = new decryptMoz3DES(cipherText, globalSalt, Encoding.ASCII.GetBytes(masterPassword), entrySalt);
var passwordCheck = CheckPwd.Compute();
string decryptedPwdChk = Encoding.GetEncoding("ISO-8859-1").GetString(passwordCheck);
if (!decryptedPwdChk.StartsWith("password-check"))
{
Console.WriteLine("\n[X] Master password is wrong; cannot decrypt credentials.\n");
return;
}
}
// Check for pkcs5 pbes2 algorithm OID in ASN (1.2.840.113549.1.5.13)
// Use to decrypt password-check if found
else if (asnString.Contains("2A864886F70D01050D"))
{
// Get Entry Salt (password-check)
var entrySalt = item2.objects[0].objects[0].objects[1].objects[0].objects[1].objects[0].Data;
// Get IV part2 (password-check)
var partIV = item2.objects[0].objects[0].objects[1].objects[2].objects[1].Data;
// Get ciphertext (password-check)
var cipherText = item2.objects[0].objects[0].objects[1].objects[3].Data;
// Decrypt password-check ciphertext & check if master password is correct
MozillaPBE CheckPwd = new MozillaPBE(cipherText, globalSalt, Encoding.ASCII.GetBytes(masterPassword), entrySalt, partIV);
var passwordCheck = CheckPwd.Compute();
string decryptedPwdChk = Encoding.GetEncoding("ISO-8859-1").GetString(passwordCheck);
if (!decryptedPwdChk.StartsWith("password-check"))
{
Console.WriteLine("\n[X] Master password is wrong; cannot decrypt credentials.\n");
return;
}
}
else if (!asnString.Contains("2A864886F70D010C050103") && !asnString.Contains("2A864886F70D01050D"))
{
Console.WriteLine("\n[X] Unrecognized encryption algorithm.\n");
return;
}
database.Close();
// If master password is correct, proceed to get private key
try
{
SQLiteConnection keyDatabase = null;
keyDatabase = new SQLiteConnection(keyDBPath, SQLiteOpenFlags.ReadOnly, false);
// Parse ASN from a11
string keyQuery = "SELECT a11,a102 FROM nssPrivate;";
List <SQLiteQueryRow> keyResults = keyDatabase.Query2(keyQuery, false);
foreach (SQLiteQueryRow keyRow in keyResults)
{
// Read ASN Value from a11 in nssPrivate
var a11Byte = (byte[])keyRow.column[0].Value;
Asn1DerObject a11ASNValue = asn.Parse(a11Byte);
// Get Entry Salt (privateKey)
var keyEntrySalt = a11ASNValue.objects[0].objects[0].objects[1].objects[0].objects[1].objects[0].Data;
// Get IV part2 (privateKey)
var keyPartIV = a11ASNValue.objects[0].objects[0].objects[1].objects[2].objects[1].Data;
// Get cipherText (privateKey)
var keyCipherText = a11ASNValue.objects[0].objects[0].objects[1].objects[3].Data;
// Decrypt private key ciphertext
MozillaPBE PrivKey = new MozillaPBE(keyCipherText, globalSalt, Encoding.ASCII.GetBytes(masterPassword), keyEntrySalt, keyPartIV);
var fullprivateKey = PrivKey.Compute();
// Trim private key - we only need the first 24 bytes
byte[] privateKey = new byte[24];
Array.Copy(fullprivateKey, privateKey, privateKey.Length);
// Decrypt & print logins
decryptLogins(loginsJsonPath, privateKey);
keyDatabase.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
Console.WriteLine("\n[*] Done.\n");
}
}
public SQLiteController()
{
this._sqLiteConnection = DependencyService.Get <ISQLite>().GetConnection();
this._sqLiteConnection.CreateTable <UserLocalInfo>();
}
public RoleIdDbService(string dbPath)
{
db = new SQLiteConnection(dbPath);
db.CreateTable<UserProfile>();
}
void IObjectExplorer.SetConnection(string connectionString, IDbConnection connection)
{
_connection = (SQLiteConnection)connection;
}
public BudgetModel()
{
this.conn = new SQLiteConnection(DbHandler.GetLocalFilePath("JustMovedDb.sqlite"));
}
//CREATE
public LoginDataBase()
{
conn = DependencyService.Get <ConectionSQLite>().GetConnection();
conn.CreateTable <LoginModel>();
}
public static IEnumerable<Registro> UpdateMethod(SQLiteConnection db, string nombre, string usuario, string password, int id)
{
return db.Query<Registro>("UPDATE Registro SET Nombre=?, Usuario=?, Password=? where Id=?",
nombre, usuario, password, id);
}
public void Dispose()
{
WriteConn.Dispose();
WriteConn.Close();
PrintSqlDatabaseMessage("Disconnected from local database.");
WriteConn = null;
}
private static SQLiteConnection GetDatabse()
{
var connection = new SQLiteConnection(new SQLitePlatformWinRT(), DbPath);
// 创建 Person 模型对应的表,如果已存在,则忽略该操作。
connection.CreateTable<PostDetail>();
return connection;
}
