public void UpdateDatabaseWithArteProps( string ConnectionString )
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection( ConnectionString );
conn.Open();
System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction();
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand( conn );
command.Transaction = transaction;
foreach ( var a in ArteList ) {
string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateNames );
command.CommandText = UpdateNames;
command.ExecuteNonQuery();
string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateDescs );
command.CommandText = UpdateDescs;
command.ExecuteNonQuery();
if ( a.Type == Arte.ArteType.Generic ) {
string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + Util.DateTimeToUnixTime( DateTime.UtcNow ) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateStatus );
command.CommandText = UpdateStatus;
command.ExecuteNonQuery();
}
}
command.Dispose();
transaction.Commit();
conn.Close();
conn.Dispose();
}
/// <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 static int ExecuteNonQueryWithTransaction(string sql, params System.Data.SQLite.SQLiteParameter[] arrayOfParameters)
{
if (string.IsNullOrEmpty(sql))
{
return(0);
}
CreateDatabaseFileIfNotExist();
using (System.Data.SQLite.SQLiteConnection cn = new System.Data.SQLite.SQLiteConnection(GetConnectionString()))
{
cn.Open();
int iReturn = -2;
cn.Open();
System.Data.SQLite.SQLiteTransaction trans = cn.BeginTransaction();
try
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(sql, cn, trans))
{
if ((arrayOfParameters?.Length ?? 0) > 0)
{
com.Parameters.AddRange(arrayOfParameters);
}
iReturn = com.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
trans.Rollback();
iReturn = -2;
throw;
}
return(iReturn);
}
}
public void UpdateDatabaseWithArteProps(string ConnectionString)
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(ConnectionString);
conn.Open();
System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction();
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(conn);
command.Transaction = transaction;
foreach (var a in ArteList)
{
string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString("X6") + "]\"";
Console.WriteLine(UpdateNames);
command.CommandText = UpdateNames;
command.ExecuteNonQuery();
string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString("X6") + "]\"";
Console.WriteLine(UpdateDescs);
command.CommandText = UpdateDescs;
command.ExecuteNonQuery();
if (a.Type == Arte.ArteType.Generic)
{
string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + Util.DateTimeToUnixTime(DateTime.UtcNow) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString("X6") + "]\"";
Console.WriteLine(UpdateStatus);
command.CommandText = UpdateStatus;
command.ExecuteNonQuery();
}
}
command.Dispose();
transaction.Commit();
conn.Close();
conn.Dispose();
}
public static int Execute( List<string> args )
{
// 0xCB20
if ( args.Count != 2 ) {
Console.WriteLine( "Generates a scenario db for use in Tales.Vesperia.Website from a MAPLIST.DAT." );
Console.WriteLine( "Usage: maplist.dat scenario.db" );
return -1;
}
String maplistFilename = args[0];
string connStr = "Data Source=" + args[1];
using ( var conn = new System.Data.SQLite.SQLiteConnection( connStr ) ) {
conn.Open();
using ( var ta = conn.BeginTransaction() ) {
SqliteUtil.Update( ta, "CREATE TABLE descriptions( filename TEXT PRIMARY KEY, shortdesc TEXT, desc TEXT )" );
int i = 0;
foreach ( MapName m in new MapList( System.IO.File.ReadAllBytes( maplistFilename ) ).MapNames ) {
Console.WriteLine( i + " = " + m.ToString() );
List<string> p = new List<string>();
p.Add( "VScenario" + i );
p.Add( m.Name1 != "dummy" ? m.Name1 : m.Name3 );
p.Add( m.Name1 != "dummy" ? m.Name1 : m.Name3 );
SqliteUtil.Update( ta, "INSERT INTO descriptions ( filename, shortdesc, desc ) VALUES ( ?, ?, ? )", p );
++i;
}
ta.Commit();
}
conn.Close();
}
return 0;
}
private void importBt_Click(object sender, EventArgs e)
{
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Filter = "*.XLS,*.XLSX|*.XLS;*.XLSX";
if (fdlg.ShowDialog() != DialogResult.OK)
{
return;
}
FileStream fs = File.OpenRead(fdlg.FileName);
string ext = Path.GetExtension(fdlg.FileName);
IWorkbook wk = null;
if (ext.ToLower().Equals(".xls"))
{
wk = new HSSFWorkbook(fs);
}
else
{
wk = new XSSFWorkbook(fs);
}
fs.Close();
persons.Clear();
ISheet sheet = wk.GetSheetAt(0);
System.Data.SQLite.SQLiteConnection cn = persons.OpenTran();
System.Data.Common.DbTransaction trans = cn.BeginTransaction();
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row.GetCell(0) == null)
{
break;
}
string name = row.GetCell(0).ToString();
string text = "";
if (row.GetCell(1) != null)
{
text = row.GetCell(1).ToString();
}
persons.Add(new Person(name, text), cn);
}
trans.Commit();
persons.CloseTran(cn);
MessageBox.Show("导入完毕");
this.personsLv.VirtualListSize = persons.Count;
personsLv.Invalidate();
}
/// <summary>
/// 保存设备信息
/// </summary>
/// <returns></returns>
public bool SaveDeviceInfomation(Device device, PointInfo points, LogConfig log, List <AlarmConfig> alarm, List <DigitalSignature> ds)
{
using (System.Data.SQLite.SQLiteConnection conn = SQLiteHelper.SQLiteHelper.CreateConn())
{
PointTempBLL _point = new PointTempBLL();
LogConfigBLL _log = new LogConfigBLL();
AlarmConfigBLL _alarm = new AlarmConfigBLL();
ReportEditorBLL _report = new ReportEditorBLL();
DigitalSignatureBLL _digital = new DigitalSignatureBLL();
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
System.Data.Common.DbTransaction tran = conn.BeginTransaction();
try
{
//保存设备信息
if (InsertDevice(device, tran) &&
//保存温度点信息
_point.InsertPoint(points, tran) &&
//保存log信息
_log.InsertLogConfig(log, tran) &&
//保存alarm信息
_alarm.InsertAlarmConfig(alarm, tran) &&
_digital.InsertDigitalSignature(ds, tran)
)
{
tran.Commit();
}
else
{
tran.Rollback();
}
}
catch (Exception ex)
{
tran.Rollback();
conn.Close();
_tracing.Error(ex, "save data to db failed!");
return(false);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
return(true);
}
public void InsertSpeedSQLite(bool prepare, bool useTransaction)
{
var connectionString = "Data Source=:memory:";
using (var connection = new System.Data.SQLite.SQLiteConnection(connectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "DROP TABLE IF EXISTS Numbers";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE `Numbers` (Key INTEGER, Value REAL, PRIMARY KEY(Key));";
command.ExecuteNonQuery();
if (prepare)
{
command.CommandText = "INSERT INTO Numbers VALUES (@Key, @Value);";
command.Prepare();
command.Parameters.AddWithValue("@Key", 0);
command.Parameters.AddWithValue("@Value", 0);
}
System.Data.SQLite.SQLiteTransaction txn = null;
if (useTransaction)
{
txn = connection.BeginTransaction();
command.Transaction = txn;
}
for (var i = 0; i < NumberOfInserts; i++)
{
if (prepare)
{
command.Parameters["@Key"].Value = i;
command.Parameters["@Value"].Value = i;
}
else
{
command.CommandText = $"INSERT INTO Numbers VALUES ({i}, {i});";
}
command.ExecuteNonQuery();
}
if (useTransaction)
{
txn.Commit();
}
}
}
/// <summary>
/// 用户向导部分插入四个表
/// </summary>
/// <param name="user"></param>
/// <param name="list"></param>
/// <returns></returns>
public bool InsertUserWizard(UserInfo user, Policy policy, List <string> right, List <Dictionary <string, object> > list)
{
MeaningsBLL bll = new MeaningsBLL();
using (System.Data.SQLite.SQLiteConnection conn = SQLiteHelper.SQLiteHelper.CreateConn())
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
System.Data.Common.DbTransaction tran = conn.BeginTransaction();
try
{
/*插入userinfo*/
this.InsertUser(user, tran);
/*修改策略*/
processor.Update <Policy>(policy, tran);
/*修改权限*/
if (right != null)
{
Dictionary <string, List <string> > r = new Dictionary <string, List <string> >();
r.Add(user.UserName, right);
new UserRightBLL().SummitUserRight(r, tran);
}
/*修改meaning*/
if (list != null && list.Count > 0)
{
bll.InsertMeanRel(user.UserName, list, tran);
}
tran.Commit();
}
catch
{
tran.Rollback();
conn.Close();
return(false);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
return(true);
}
public bool DeleteDeviceInformation(List <Device> device, List <PointInfo> points, List <LogConfig> log, List <AlarmConfig> alarm, List <DigitalSignature> digital)
{
//if(_digital.IsExist()
using (System.Data.SQLite.SQLiteConnection conn = SQLiteHelper.SQLiteHelper.CreateConn())
{
PointTempBLL _point = new PointTempBLL();
LogConfigBLL _log = new LogConfigBLL();
AlarmConfigBLL _alarm = new AlarmConfigBLL();
DigitalSignatureBLL _digital = new DigitalSignatureBLL();
ReportEditorBLL _reportBll = new ReportEditorBLL();
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
System.Data.Common.DbTransaction tran = conn.BeginTransaction();
try
{
//删除设备信息
DeleteDevice(device, tran);
////删除温度点信息
_point.DeletePointInfo(points, tran);
////删除log信息
_log.DeleteLogConfig(log, tran);
////删除alarm信息
_alarm.DeleteAlarmConfig(alarm, tran);
device.ForEach(p => _reportBll.DeleteReportEditorBySnTn(p.SerialNum, p.TripNum, tran));
_digital.DeleteDigitalSignature(digital, tran);
tran.Commit();
}
catch (Exception ex)
{
_tracing.Error(ex, "delete the device info failed");
tran.Rollback();
conn.Close();
return(false);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
return(true);
}
/// <summary>
/// 用户向导部分插入四个表
/// </summary>
/// <param name="user"></param>
/// <param name="list"></param>
/// <returns></returns>
public bool InsertUserWizard(UserInfo user, List <Dictionary <string, object> > list)
{
MeaningsBLL bll = new MeaningsBLL();
using (System.Data.SQLite.SQLiteConnection conn = SQLiteHelper.SQLiteHelper.CreateConn())
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
System.Data.Common.DbTransaction tran = conn.BeginTransaction();
try
{
/*插入userinfo*/
this.InsertUser(user, tran);
//先获取当前meaning及relation的最大id
int mId = bll.GetMeaningPKValue();
int rId = bll.GetRelationPKValue();
Dictionary <string, object> rDic, mDic;
foreach (Dictionary <string, object> dic in list)
{
rDic = new Dictionary <string, object>(dic);
mDic = new Dictionary <string, object>(dic);
mDic.Add("ID", ++mId);
rDic.Add("ID", ++rId);
bll.InsertOrUpdateMeaning(mDic, tran);
bll.InsertMeanRel(user.UserName, rDic, tran);
}
tran.Commit();
}
catch
{
tran.Rollback();
conn.Close();
return(false);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
return(true);
}
/// Return value = Number of rows affected.
/// sConnectionString = Connection string.
/// arrayOfSqlItems = Array of SQL items.
/// KeyValuePair<string, SQLiteParameter[]> o1 = new KeyValuePair<string, SQLiteParameter[]>
/// (
/// "INSERT INTO SpeedStage (SpeedStageValue, Description) VALUES (@SpeedStageValue, @Description)",
/// new SQLiteParameter[]
/// {
/// new SQLiteParameter("@SpeedStageValue", SqlDbType.Int) { Value = 1001 },
/// new SQLiteParameter("@Description", SqlDbType.VarChar) { Value = "test1" }
/// }
/// );
/// KeyValuePair<string, SQLiteParameter[]> o2 = new KeyValuePair<string, SQLiteParameter[]>
/// (
/// "INSERT INTO SpeedStage (SpeedStageValue, Description) VALUES (@SpeedStageValue, @Description)",
/// new SQLiteParameter[]
/// {
/// new SQLiteParameter("@SpeedStageValue", SqlDbType.Int) { Value = 1002 },
/// new SQLiteParameter("@Description", SqlDbType.VarChar) { Value = "test2" }
/// }
/// );
/// KeyValuePair<string, SQLiteParameter[]> o3 = new KeyValuePair<string, SQLiteParameter[]>
/// (
/// "INSERT INTO SpeedStage (SpeedStageValue, Description) VALUES (@SpeedStageValue, @Description)",
/// new SQLiteParameter[]
/// {
/// new SQLiteParameter("@SpeedStageValue", SqlDbType.Int) { Value = 1003 },
/// new SQLiteParameter("@Description", SqlDbType.VarChar) { Value = "test3" }
/// }
/// );
/// Example 1:
/// int i = ExecuteNonQuery(ConnectionString, o1, o2);
/// Example 2:
/// KeyValuePair<string, SQLiteParameter[]>[] arrayOfSqlItems = new KeyValuePair<string, SQLiteParameter[]>[] { o1, o2, o3 };
/// int i = ExecuteNonQuery(ConnectionString, arrayOfSqlItems);
public static int ExecuteNonQuery(params KeyValuePair <string, System.Data.SQLite.SQLiteParameter[]>[] arrayOfSqlItems)
{
if (arrayOfSqlItems == null)
{
return(0);
}
CreateDatabaseFileIfNotExist();
using (System.Data.SQLite.SQLiteConnection cn = new System.Data.SQLite.SQLiteConnection(GetConnectionString()))
{
cn.Open();
int iReturn = 0;
System.Data.SQLite.SQLiteTransaction trans = cn.BeginTransaction();
try
{
foreach (KeyValuePair <string, System.Data.SQLite.SQLiteParameter[]> o in arrayOfSqlItems)
{
if (string.IsNullOrEmpty(o.Key) == false)/// o.Key is SQL.
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(o.Key, cn, trans))
{
/// o.Value is array of parameters.
if ((o.Value?.Length ?? 0) > 0)
{
com.Parameters.AddRange(o.Value);
}
iReturn += com.ExecuteNonQuery();
}
}
}
trans.Commit();
}
catch
{
trans.Rollback();
iReturn = -2;
throw;
}
return(iReturn);
}
}
public void UpdateColumn(string ColumnName, Object[] data)
{
bool wasOpen = _isOpen;
if (!_isOpen)
{
Open();
}
if (data.Count() == 0)
{
return;
}
//check if column exists...
string cmdstring = "UPDATE [" + _tableName + "] SET [" + ColumnName + "]='";
using (System.Data.SQLite.SQLiteTransaction trans = _connection.BeginTransaction())
{
try
{
using (System.Data.SQLite.SQLiteCommand cmd = _connection.CreateCommand())
{
cmd.Transaction = trans;
for (int i = 0; i < data.Count(); i++)
{
cmd.CommandText = cmdstring + data[i] + "' WHERE rowid=" + _rowIds[i];
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}catch (Exception ex)
{
trans.Rollback();
}
}
if (!wasOpen)
{
Close();
}
}
public bool StartTransaction()
{
try
{
cnn = new System.Data.SQLite.SQLiteConnection(ConnectionString, true);
cnn.Open();
transaction = cnn.BeginTransaction();
}
catch
{
transaction = null;
if (cnn != null)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
cnn = null;
}
return(false);
}
return(true);
}
public static int Execute(List <string> args)
{
// 0xCB20
if (args.Count != 2)
{
Console.WriteLine("Generates a scenario db for use in Tales.Vesperia.Website from a MAPLIST.DAT.");
Console.WriteLine("Usage: maplist.dat scenario.db");
return(-1);
}
String maplistFilename = args[0];
string connStr = "Data Source=" + args[1];
using (var conn = new System.Data.SQLite.SQLiteConnection(connStr)) {
conn.Open();
using (var ta = conn.BeginTransaction()) {
SqliteUtil.Update(ta, "CREATE TABLE descriptions( filename TEXT PRIMARY KEY, shortdesc TEXT, desc TEXT )");
int i = 0;
foreach (MapName m in new MapList(System.IO.File.ReadAllBytes(maplistFilename)).MapNames)
{
Console.WriteLine(i + " = " + m.ToString());
List <string> p = new List <string>();
p.Add("VScenario" + i);
p.Add(m.Name1 != "dummy" ? m.Name1 : m.Name3);
p.Add(m.Name1 != "dummy" ? m.Name1 : m.Name3);
SqliteUtil.Update(ta, "INSERT INTO descriptions ( filename, shortdesc, desc ) VALUES ( ?, ?, ? )", p);
++i;
}
ta.Commit();
}
conn.Close();
}
return(0);
}
public void BeginTran()
{
tran = conn.BeginTransaction();
}
public void ParseUserHandHistoryFile(FileInfo file)
{
this.CurrentHand = null;
Logger.Info("Importing Hand History File {0} in directory {1}", file.Name, file.DirectoryName);
Data.HandHistoryFile currentFile = this.Store.GetHandHistoryFile(file.FullName);
if (currentFile == null)
{
string name = System.IO.Path.GetFileNameWithoutExtension(file.Name);
string path = file.FullName;
currentFile = new Data.HandHistoryFile();
currentFile.Path = path;
currentFile.Name = name;
currentFile.SiteId = this._siteId;
currentFile.DateAdded = UnixTimestamp.UtcNow;
currentFile.MachineName = Environment.MachineName;
currentFile.User = Environment.UserName;
currentFile.DateCreated = new UnixTimestamp(file.CreationTimeUtc);
using (this.Store.Lock())
{
currentFile.Id = this.Store.InsertHandHistoryFile(_siteId,
name, path, 0, currentFile.MachineName,
currentFile.User, currentFile.DateCreated.UnixTime);
}
}
using (StreamReader sr = new StreamReader(File.Open(file.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
{
string s = sr.ReadToEnd();
string[] lines = s.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
if (lines == null || lines.Length == 0)
{
Logger.Warn("No lines found in file {0}", file);
return;
}
// last is the last line number parsed;
// if the file hasn't been parsed yet, last will be zero
long last = currentFile.LineNumber;
for (long i = last; i < lines.Length; i++)
{
string line = lines[i];
ParseUserHandHistoryLine(file, line, i);
}
this.Store.UpdateLastLine(currentFile.Id, lines.Length);
}
using (this.Store.Lock())
{
using (System.Data.SQLite.SQLiteConnection connection = this.Store.CreateConnection())
{
connection.Open();
using (System.Data.SQLite.SQLiteTransaction transaction = connection.BeginTransaction())
{
foreach (Data.Hand hand in this._hands)
{
bool saved = hand.Save(this.Store, connection);
}
transaction.Commit();
}
this._stats.TotalPlayers = this.Store.GetPlayerCount();
this._stats.TotalHands = this.Store.GetHandCount();
}
}
this.CurrentHand = null;
_hands.Clear();
Logger.Info("Importing Hand History File {0} complete", file.Name);
}
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();
}
}
}
}
private void UpdateSqLiteDB(List<long> docGroupIds)
{
string connectionString = "data source=" + Arguments.SQLiteDataFile;
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(connectionString))
{
con.Open();
using (System.Data.SQLite.SQLiteTransaction tran = con.BeginTransaction())
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
foreach (int id in docGroupIds)
{
com.CommandText = " UPDATE DocumentGroups SET Operation=3 WHERE DocumentGroupId = " + id;
com.ExecuteNonQuery();
com.CommandText = " UPDATE Documents SET Operation=3 WHERE DocumentGroupId = " + id;
com.ExecuteNonQuery();
}
}
tran.Commit();
}
}
}
public static JSONArray ExecuteQuerySync(string sql, Dictionary <string, object> parameters, bool useTransaction)
{
// Force transaction if it is an update, insert or delete.
if (GAUtilities.StringMatch(sql.ToUpperInvariant(), "^(UPDATE|INSERT|DELETE)"))
{
useTransaction = true;
}
// Get database connection from singelton sharedInstance
SqliteConnection sqlDatabasePtr = Instance.SqlDatabase;
// Create mutable array for results
JSONArray results = new JSONArray();
SqliteTransaction transaction = null;
SqliteCommand command = null;
try
{
if (useTransaction)
{
transaction = sqlDatabasePtr.BeginTransaction();
}
command = sqlDatabasePtr.CreateCommand();
if (useTransaction)
{
command.Transaction = transaction;
}
command.CommandText = sql;
command.Prepare();
// Bind parameters
if (parameters.Count != 0)
{
foreach (KeyValuePair <string, object> pair in parameters)
{
command.Parameters.AddWithValue(pair.Key, pair.Value);
}
}
using (SqliteDataReader reader = command.ExecuteReader())
{
// Loop through results
while (reader.Read())
{
// get columns count
int columnCount = reader.FieldCount;
JSONObject row = new JSONObject();
for (int i = 0; i < columnCount; i++)
{
string column = reader.GetName(i);
if (string.IsNullOrEmpty(column))
{
continue;
}
row[column] = reader.GetValue(i).ToString();
}
results.Add(row);
}
}
if (useTransaction)
{
transaction.Commit();
}
}
catch (SqliteException e)
{
// TODO(nikolaj): Should we do a db validation to see if the db is corrupt here?
GALogger.E("SQLITE3 ERROR: " + e);
results = null;
if (useTransaction)
{
if (transaction != null)
{
try
{
transaction.Rollback();
}
catch (SqliteException ex)
{
GALogger.E("SQLITE3 ROLLBACK ERROR: " + ex);
}
finally
{
transaction.Dispose();
}
}
}
}
finally
{
if (command != null)
{
command.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
}
}
// Return results
return(results);
}
public static JSONArray ExecuteQuerySync(string sql, Dictionary <string, object> parameters, bool useTransaction)
{
#if !UNITY_EDITOR
// Force transaction if it is an update, insert or delete.
if (GAUtilities.StringMatch(sql.ToUpperInvariant(), "^(UPDATE|INSERT|DELETE)"))
{
useTransaction = true;
}
// Get database connection from singelton sharedInstance
SqliteConnection sqlDatabasePtr = Instance.SqlDatabase;
// Create mutable array for results
JSONArray results = new JSONArray();
SqliteCommand command = null;
try
{
if (useTransaction)
{
sqlDatabasePtr.BeginTransaction();
}
command = sqlDatabasePtr.CreateCommand();
command.CommandText = sql;
// Bind parameters
if (parameters.Count != 0)
{
foreach (KeyValuePair <string, object> pair in parameters)
{
command.Bind(pair.Key, pair.Value);
}
}
// Loop through results
foreach (List <Tuple <string, string> > reader in command.ExecuteQueryMY())
{
// get columns count
int columnCount = reader.Count;
JSONClass row = new JSONClass();
for (int i = 0; i < columnCount; i++)
{
string column = reader[i].Item1;
if (string.IsNullOrEmpty(column))
{
continue;
}
row[column] = reader[i].Item2.ToString();
}
results.Add(row);
}
if (useTransaction)
{
//transaction.Commit();
sqlDatabasePtr.Commit();
}
}
catch (SqliteException e)
{
// TODO(nikolaj): Should we do a db validation to see if the db is corrupt here?
GALogger.E("SQLITE3 ERROR: " + e);
results = null;
}
finally
{
}
// Return results
return(results);
#else
return(null);
#endif
}
//디비에 넣자
private void mfnInsertToDb()
{
mliStock.Clear();
string strCode = "";
string strQuery = "";
if (txtDbList.Text.Contains(","))
{
for (int i = 0; i < txtDbList.Text.Split(',').Length; i++)
{
mliStock.Add(txtDbList.Text.Split(',')[i].ToUpper());
}
}
else if (txtDbList.Text.Replace(" ", "") == "")
{
return;
}
else
{
mliStock.Add(txtDbList.Text.ToUpper());
}
for (int i = 0; i < mliStock.Count; i++)
{
strCode = mfnSearchCode(mliStock[i]);
if (strCode == "")
{
return;
}
else
{
mDetailInfo = Parser.GetDetailInfo(strCode, Convert.ToInt32(txtNum.Text) + 60);
strQuery = "DELETE FROM List WHERE Name = '" + mliStock[i] + "'";
Network.ExecDB(strQuery, mDbSource);
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(mDbSource))
{
conn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
using (System.Data.SQLite.SQLiteTransaction tran = conn.BeginTransaction())
{
for (int j = 0; j < mDetailInfo.Length; j++)
{
strQuery = "";
strQuery += "INSERT INTO List (Name, Date, Price, Volumn, HighPrice, LowPrice, StartPrice)" + "\r\n";
strQuery += "VALUES( " + "\r\n";
strQuery += "'" + mliStock[i] + "',";
strQuery += "'" + mDetailInfo[j].date + "',";
strQuery += "'" + mDetailInfo[j].price + "',";
strQuery += "'" + mDetailInfo[j].volumn + "',";
strQuery += "'" + mDetailInfo[j].highPrice + "',";
strQuery += "'" + mDetailInfo[j].lowPrice + "',";
strQuery += "'" + mDetailInfo[j].startPrice + "');" + "\r\n";
cmd.CommandText = strQuery;
cmd.ExecuteNonQuery();
}
tran.Commit();
}
}
conn.Close();
}
/*
* if (Network.ExecDB(strQuery, mDbSource) == 0)
* {
* MessageBox.Show("InsertError");
* return;
* }
*/
}
}
}
