public int ExecuteNonQuery(string cmdtext, IDataParameter[] param)
{
SQLiteConnection conn = new SQLiteConnection(ConnectionStr);
SQLiteCommand comm = new SQLiteCommand(cmdtext, conn);
if (param != null)
{
comm.Parameters.AddRange(param);
}
int count;
conn.Open();
SQLiteTransaction tra = null;
try
{
if (cmdtext.Contains("Insert"))
{
tra = conn.BeginTransaction();
}
count = comm.ExecuteNonQuery();
}
finally
{
tra?.Commit();
conn.Close();
}
return(count);
}
/// <summary>
/// Execute Insert Query - Async
/// </summary>
/// <param name="query">Query</param>
/// <param name="parameters">Optional. Parameters</param>
/// <param name="isNeedTransactionBlock">Optional. IsNeedTransactionBlock default false</param>
/// <returns>Inseted Id</returns>
public static async Task <int> ExecuteInsertQueryAsync(string query, IEnumerable <KeyValuePair <string, object> > parameters = null, bool isNeedTransactionBlock = false)
{
int insertedId = -1;
await Task.Run(() =>
{
using (SQLiteConnection conn = GetConnection)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = query;
if (parameters != null && parameters.Count() > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
cmd.Prepare();
lock (readWriteLock)
{
conn.Open();
SQLiteTransaction transaction = null;
try
{
if (isNeedTransactionBlock)
{
transaction = conn.BeginTransaction();
}
int changesCount = cmd.ExecuteNonQuery();
if (changesCount > 0)
{
cmd.CommandText = LastInsertedIdQuery;
cmd.Prepare();
insertedId = int.Parse(cmd.ExecuteScalar().ToString());
}
transaction?.Commit();
}
catch
{
transaction?.Rollback();
applicationErrorLog.ErrorLog("Database", "DB Transaction", "DB Transaction Main queries Error");
throw;
}
finally
{
conn.Close();
}
}
}
}
});
return(insertedId);
}
/// <summary>
/// Execute Query Async
/// </summary>
/// <typeparam name="T">Type of list need to returned</typeparam>
/// <param name="query">Query</param>
/// <param name="readerFunc">Reader function</param>
/// <param name="parameters">Optional. Parameters</param>
/// <param name="isNeedTransactionBlock">Optional. IsNeedTransactionBlock default false</param>
/// <returns>List of T</returns>
public static async Task <IEnumerable <T> > ExecuteQueryAsync <T>(string query, Func <SQLiteDataReader, T> readerFunc, IEnumerable <KeyValuePair <string, object> > parameters = null, bool isNeedTransactionBlock = false)
{
IList <T> items = new List <T>();
await Task.Run(() =>
{
using (SQLiteConnection conn = GetConnection)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = query;
if (parameters != null && parameters.Count() > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
cmd.Prepare();
lock (readWriteLock)
{
conn.Open();
SQLiteTransaction transaction = null;
try
{
if (isNeedTransactionBlock)
{
transaction = conn.BeginTransaction();
}
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
items.Add(readerFunc(reader));
}
}
transaction?.Commit();
}
catch
{
transaction?.Rollback();
applicationErrorLog.ErrorLog("Database", "DB Transaction", "DB Transaction Main queries Error");
throw;
}
finally
{
conn.Close();
}
}
}
}
});
return(items);
}
private void Dispose(bool disposing)
{
if (disposing)
{
_transaction?.Commit();
_transaction?.Dispose();
_connection?.Dispose();
}
}
/// <summary>
/// Execute Query Single Or Default Async
/// </summary>
/// <typeparam name="T">Type of list need to returned</typeparam>
/// <param name="query">Query</param>
/// <param name="readerFunc">Reader function</param>
/// <param name="parameters">Optional. Parameters</param>
/// <param name="isNeedTransactionBlock">Optional. IsNeedTransactionBlock default false</param>
/// <returns>List of T</returns>
public static async Task <T> ExecuteQuerySingleOrDefaultAsync <T>(string query, Func <SQLiteDataReader, T> readerFunc, IEnumerable <KeyValuePair <string, object> > parameters = null, bool isNeedTransactionBlock = false)
{
T item = default;
await Task.Run(() =>
{
using (SQLiteConnection conn = GetConnection)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = query;
if (parameters != null && parameters.Count() > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
cmd.Prepare();
lock (readWriteLock)
{
conn.Open();
SQLiteTransaction transaction = null;
try
{
if (isNeedTransactionBlock)
{
transaction = conn.BeginTransaction();
}
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
item = readerFunc(reader);
}
}
transaction?.Commit();
}
catch
{
transaction?.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
});
return(item);
}
public void Commit()
{
lock (_lock)
{
_transaction?.Commit();
_transaction?.Dispose();
_transaction = null;
}
}
/// <summary>
/// Execute Scalar - Async
/// </summary>
/// <typeparam name="T">Type need to returned</typeparam>
/// <param name="query">Query</param>
/// <param name="parameters">Optional. Parameters</param>
/// <param name="isNeedTransactionBlock">Optional. IsNeedTransactionBlock default false</param>
/// <returns>T</returns>
public static async Task <T> ExecuteScalarAsync <T>(string query, Func <string, T> stringToTypeCast, IEnumerable <KeyValuePair <string, object> > parameters = null, bool isNeedTransactionBlock = false)
{
T result = default;
await Task.Run(() =>
{
using (SQLiteConnection conn = GetConnection)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = query;
if (parameters != null && parameters.Count() > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
cmd.Prepare();
lock (readWriteLock)
{
conn.Open();
SQLiteTransaction transaction = null;
try
{
if (isNeedTransactionBlock)
{
transaction = conn.BeginTransaction();
}
result = stringToTypeCast(cmd.ExecuteScalar().ToString());
transaction?.Commit();
}
catch
{
transaction?.Rollback();
applicationErrorLog.ErrorLog("Database", "DB Transaction", "DB Transaction Main queries Error");
throw;
}
finally
{
conn.Close();
}
}
}
}
});
return(result);
}
/// <summary>
/// Execute Non Query - Async
/// </summary>
/// <param name="query">Query</param>
/// <param name="parameters">Optional. Parameters</param>
/// <param name="isNeedTransactionBlock">Optional. IsNeedTransactionBlock default false</param>
/// <returns>Changes count</returns>
public static async Task <int> ExecuteNonQueryAsync(string query, IEnumerable <KeyValuePair <string, object> > parameters = null, bool isNeedTransactionBlock = false)
{
int changesCount = -1;
await Task.Run(() =>
{
using (SQLiteConnection conn = GetConnection)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = query;
if (parameters != null && parameters.Count() > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
cmd.Prepare();
lock (readWriteLock)
{
conn.Open();
SQLiteTransaction transaction = null;
try
{
if (isNeedTransactionBlock)
{
transaction = conn.BeginTransaction();
}
changesCount = cmd.ExecuteNonQuery();
transaction?.Commit();
}
catch
{
transaction?.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
});
return(changesCount);
}
public override void BulkInsert(ITableData data, string tableName)
{
var sourceColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.SourceColumn).ToList();
var sourceColumnValues = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => "?").ToList();
var destColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.DataSetColumn).ToList();
SQLiteTransaction existingTransaction = Transaction as SQLiteTransaction;
SQLiteTransaction bulkTransaction = null;
if (existingTransaction == null)
{
bulkTransaction = this.DbConnection.BeginTransaction();
}
using (bulkTransaction)
using (var command = this.DbConnection.CreateCommand())
{
command.Transaction = existingTransaction ?? bulkTransaction;
command.CommandText =
$@"INSERT INTO {tableName}
({String.Join(",", sourceColumnNames)})
VALUES ({String.Join(",", sourceColumnValues)})
";
command.Prepare();
while (data.Read())
{
foreach (var mapping in destColumnNames)
{
SQLiteParameter par = new SQLiteParameter();
par.Value = data.GetValue(data.GetOrdinal(mapping));
command.Parameters.Add(par);
}
command.ExecuteNonQuery();
command.Parameters.Clear();
}
bulkTransaction?.Commit();
}
}
/// 执行多条SQL列表语句，实现数据库事务。
/// </summary>
/// <param name="sqlStringList">多条SQL语句</param>
/// <param name="commandParametersList">对应SQL语句列表中每一条语句的参数集合列表</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
public static void ExecuteBatchSql(IList <string> sqlStringList, IList <SQLiteParameter[]> commandParametersList)
{
if (sqlStringList.Count == 0 || commandParametersList.Count == 0 || sqlStringList.Count != commandParametersList.Count)
{
throw new Exception("传入参数错误");
}
//创建SQLiteCommand执行命令语句对象
using (SQLiteCommand cmd = new SQLiteCommand())
{
//使用using语句，方便using语句中声明的对象自动被Dispose
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
//打开数据库连接
connection.Open();
//SQLiteCommand执行命令语句对象添加数据库连接对象
cmd.Connection = connection;
//创建事务
SQLiteTransaction tx = connection.BeginTransaction();
//SQLiteCommand执行命令语句对象添加事务对象
cmd.Transaction = tx;
try
{
//遍历SQL语句，依次执行
for (int n = 0; n < sqlStringList.Count; n++)
{
try
{
//执行命令前预处理
PrepareCommand(cmd, connection, null, sqlStringList[n], commandParametersList[n]);
//执行非查询数据库操作
cmd.ExecuteNonQuery();
//清空传入的参数
cmd.Parameters.Clear();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
/*
* string strsql = SQLStringList[n].ToString();
* //执行数据库相应操作
* if (strsql.Trim().Length > 1)
* {
* cmd.CommandText = strsql;
* cmd.ExecuteNonQuery();
* }
*/
/*
* //每执行500条SQL语句就做一次事务提交
* if (n > 0 && (n % 500 == 0 || n == SQLStringList.Count - 1))
* {
* //事务提交
* tx.Commit();
* //重新开启事务
* tx = conn.BeginTransaction();
* }
*/
}
//一次性提交事务
tx.Commit();
}
catch (System.Data.SqlClient.SqlException e)
{
tx.Rollback();
Console.WriteLine(e.GetType() + ":" + e.Message);
throw new Exception(e.Message);
}
}
}
}
/// <summary>
/// Creates or upgrades the database
/// </summary>
void InitializeTables(Func <Guid> userIdProvider)
{
using (SQLiteTransaction transaction = this.connection.BeginTransaction()) {
using (SQLiteCommand cmd = this.connection.CreateCommand()) {
cmd.CommandText = @"
CREATE TABLE IF NOT EXISTS Properties (
name TEXT NOT NULL PRIMARY KEY,
value TEXT NOT NULL
);
INSERT OR IGNORE INTO Properties (name, value) VALUES ('dbVersion', '" + expectedDBVersion.ToString() + @"');
" ;
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = this.connection.CreateCommand()) {
cmd.CommandText = "SELECT value FROM Properties WHERE name = 'dbVersion';";
string version = (string)cmd.ExecuteScalar();
if (version == null)
{
throw new InvalidOperationException("Error retrieving database version");
}
Version actualDBVersion = new Version(version);
if (actualDBVersion != expectedDBVersion)
{
throw new IncompatibleDatabaseException(expectedDBVersion, actualDBVersion);
}
}
if (RetrieveUserId(this.connection) == null)
{
using (SQLiteCommand cmd = this.connection.CreateCommand()) {
cmd.CommandText = @"INSERT OR IGNORE INTO Properties (name, value) VALUES ('userID', ?);";
cmd.Parameters.Add(new SQLiteParameter {
Value = userIdProvider().ToString()
});
cmd.ExecuteNonQuery();
}
}
using (SQLiteCommand cmd = this.connection.CreateCommand()) {
cmd.CommandText = @"
CREATE TABLE IF NOT EXISTS Sessions (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
startTime TEXT NOT NULL,
endTime TEXT
);
CREATE TABLE IF NOT EXISTS Environment (
session INTEGER NOT NULL,
name TEXT NOT NULL,
value TEXT
);
CREATE TABLE IF NOT EXISTS FeatureUses (
id INTEGER NOT NULL PRIMARY KEY,
session INTEGER NOT NULL,
time TEXT NOT NULL,
endTime TEXT,
feature TEXT NOT NULL,
activationMethod TEXT
);
CREATE TABLE IF NOT EXISTS Exceptions (
session INTEGER NOT NULL,
time TEXT NOT NULL,
type TEXT NOT NULL,
stackTrace TEXT
);
" ;
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
public void EndMassStoring()
{
_transation.Commit();
_transation.Dispose();
_transation = null;
}
public static int UpdateSPD(Spd s, List <Pengikut> pengikut, Biaya b)
{
int r = -1;
try {
using (SQLiteConnection conn = new SQLiteConnection(connectionString)) {
conn.Open();
using (SQLiteTransaction tr = conn.BeginTransaction()) {
using (SQLiteCommand cmd = new SQLiteCommand(conn)) {
cmd.Transaction = tr;
cmd.CommandText = "UPDATE data SET kode=@kode, pegawai=@pegawai, tb=@tb, maksud=@maksud, transport=@transport, t_berangkat=@t_berangkat, t_tujuan=@t_tujuan, penjabat=@penjabat, jabatan=@jabatan, lama=@lama, tgl_berangkat=@tgl_berangkat, tgl_kembali=@tgl_kembali, no_surat=@no_surat, tgl_tugas=@tgl_tugas, akun=@akun WHERE id=@id";
cmd.Prepare();
cmd.Parameters.AddWithValue("@id", s.id);
cmd.Parameters.AddWithValue("@kode", s.kode);
cmd.Parameters.AddWithValue("@pegawai", s.pegawai_id);
cmd.Parameters.AddWithValue("@tb", s.tb);
cmd.Parameters.AddWithValue("@maksud", s.maksud);
cmd.Parameters.AddWithValue("@transport", s.trasport_id);
cmd.Parameters.AddWithValue("@t_berangkat", s.t_berangkat_id);
cmd.Parameters.AddWithValue("@t_tujuan", s.t_tujuan_id);
cmd.Parameters.AddWithValue("@penjabat", s.penjabat);
cmd.Parameters.AddWithValue("@jabatan", s.t_jabatan);
cmd.Parameters.AddWithValue("@lama", s.lama);
cmd.Parameters.AddWithValue("@tgl_berangkat", s.tgl_berangkat);
cmd.Parameters.AddWithValue("@tgl_kembali", s.tgl_kembali);
cmd.Parameters.AddWithValue("@no_surat", s.no_surat);
cmd.Parameters.AddWithValue("@tgl_tugas", s.tgl_tugas);
cmd.Parameters.AddWithValue("@akun", s.akun);
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE biaya SET harian=@harian, h_lama=@h_lama, h_total=@h_total, penginapan=@penginapan, p_lama=@p_lama, p_total=@p_total, transport_pp=@transport_pp, transport_loak=@transport_loak, damri=@damri, lain=@lain WHERE id=@id";
cmd.Prepare();
cmd.Parameters.AddWithValue("@id", b.id);
cmd.Parameters.AddWithValue("@harian", b.harian);
cmd.Parameters.AddWithValue("@h_lama", b.h_lama);
cmd.Parameters.AddWithValue("@h_total", b.h_total);
cmd.Parameters.AddWithValue("@penginapan", b.penginapan);
cmd.Parameters.AddWithValue("@p_lama", b.p_lama);
cmd.Parameters.AddWithValue("@p_total", b.p_total);
cmd.Parameters.AddWithValue("@transport_pp", b.transport_pp);
cmd.Parameters.AddWithValue("@transport_loak", b.transport_loak);
cmd.Parameters.AddWithValue("@damri", b.damri);
cmd.Parameters.AddWithValue("@lain", b.lain_lain);
cmd.ExecuteNonQuery();
foreach (var p in pengikut)
{
cmd.CommandText = "INSERT INTO pengikut (pegawai, ket, data, tgl_lahir) VALUES (@pegawai, @ket, @data, @tgl_lahir)";
if (p.id != -1)
{
cmd.CommandText = "UPDATE pengikut SET pegawai=@pegawai, ket=@ket, data=@data, t_lahir=@t_lahir WHERE id=@id";
cmd.Parameters.AddWithValue("@id", p.id);
}
cmd.Parameters.AddWithValue("@pegawai", p.pegawai);
cmd.Parameters.AddWithValue("@ket", p.ket);
cmd.Parameters.AddWithValue("@data", s.id);
cmd.Parameters.AddWithValue("@t_lahir", p.t_lahir);
cmd.ExecuteNonQuery();
}
}
tr.Commit();
r = 1;
}
conn.Close();
}
} catch (SQLiteException e) {
throw;
}
return(r);
}
public void Insert(object[] paramValues)
{
try
{
if (paramValues.Length != m_parameters.Count)
{
throw new Exception("The values array count must be equal to the count of the number of parameters.");
}
m_counter++;
if (m_counter == 1)
{
if (m_allowBulkInsert)
{
m_trans = m_dbCon.BeginTransaction();
}
m_cmd = m_dbCon.CreateCommand();
foreach (SQLiteParameter par in m_parameters.Values)
{
m_cmd.Parameters.Add(par);
}
m_cmd.CommandText = this.CommandText;
}
int i = 0;
foreach (SQLiteParameter par in m_parameters.Values)
{
par.Value = paramValues[i];
i++;
}
//执行出错 会导致连接卡死（可能是sqlite的连接独占原因）
m_cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
if (m_trans != null)
{
m_trans.Dispose();
}
m_trans = null;
m_counter = 0;
throw new Exception("Could not do ExecuteNonQuery. See InnerException for more details:" + ex.Message);
}
if (m_counter == m_commitMax)
{
try
{
if (m_trans != null)
{
m_trans.Commit();
}
}
catch (Exception ex) { }
finally
{
if (m_trans != null)
{
m_trans.Dispose();
m_trans = null;
}
m_counter = 0;
}
}
}
internal static bool InsertUpdateItem(EntityItem paraItem)
{
bool bInsert = false;
int iInsert = -1;
string connectionString = Common.CommonDa.GetDbConnection();
SQLiteTransaction transaction = null;
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
transaction = conn.BeginTransaction();
try
{
iInsert = 0;
string strSql2 = "update main.im_item set void=1 where void=0 and product_no=@product_no ";
SQLiteCommand cmd = new SQLiteCommand(strSql2, conn, transaction);
cmd.Parameters.AddWithValue("@product_no", paraItem.ProductNo);
iInsert = cmd.ExecuteNonQuery();
strSql2 = "update main.im_item_barcode set void=1 where void=0 and product_no=@product_no ";
cmd = new SQLiteCommand(strSql2, conn, transaction);
cmd.Parameters.AddWithValue("@product_no", paraItem.ProductNo);
iInsert += cmd.ExecuteNonQuery();
string strSql = "insert into main.im_item (product_no,item_no,item_desc,customer_id,update_uid,update_time) values (@product_no,@item_no,@item_desc,@customer_id,@update_uid,datetime('now'))";
cmd = new SQLiteCommand(strSql, conn, transaction);
cmd.Parameters.AddWithValue("@product_no", paraItem.ProductNo);
cmd.Parameters.AddWithValue("@item_no", paraItem.ItemNo);
cmd.Parameters.AddWithValue("@item_desc", paraItem.ItemDesc);
cmd.Parameters.AddWithValue("@customer_id", paraItem.CustomerId);
cmd.Parameters.AddWithValue("@update_uid", paraItem.UserId);
iInsert += cmd.ExecuteNonQuery();
foreach (EntityItemBarcode ibc in paraItem.ItemBarcodes)
{
string strSql1 = "insert into main.im_item_barcode (product_no,item_barcode,customer_id,update_uid,update_time) values (@product_no,@item_barcode,@customer_id,@update_uid,datetime('now'))";
cmd = new SQLiteCommand(strSql1, conn, transaction);
cmd.Parameters.AddWithValue("@product_no", ibc.ProductNo);
cmd.Parameters.AddWithValue("@item_barcode", ibc.ItemBarcode);
cmd.Parameters.AddWithValue("@customer_id", ibc.CustomerId);
cmd.Parameters.AddWithValue("@update_uid", ibc.UserId);
iInsert += cmd.ExecuteNonQuery();
}
if (iInsert > 0)
{
bInsert = true;
}
transaction.Commit();
}
catch (System.Data.SQLite.SQLiteException ex)
{
transaction.Rollback();
throw new Exception(ex.Message);
}
finally
{
//cmd.Dispose();
conn.Close();
}
}
return(bInsert);
}
public bool SaveDataTable(List <DataTable> dtx, string tableName, string CaseName, SQLiteConnection conn, Boolean UseRowId = false)
{
bool Worksx = true;
int RotationCount = 0;
if (dtx != null && dtx.Count != 0)
{
int ix = 0;
foreach (DataTable DT in dtx)
{
ix++;
try
{
Dictionary <string, string> Columnsx = GetColumnInfo(DT);
if (Columnsx.ContainsKey("Row_id"))
{
Columnsx.Remove("Row_id");
}
if (Columnsx.ContainsKey("RowId"))
{
Columnsx.Remove("RowId");
}
String Query = GetCreateTableCommand(tableName, Columnsx);
if (DT.Rows.Count > 0 || RotationCount == 0)
{
SqlCycloComplextyQuery(Query, conn);
}
}
catch (Exception e)
{
Worksx = false;
}
if (DT != null)
{
if (DT.Rows.Count > 0)
{
DataTable table = new DataTable();
try
{
foreach (DataRow row in DT.Rows)
{
if (row.RowState == DataRowState.Unchanged)
{
row.SetAdded();
}
}
string strColumns = "";
using (
SQLiteConnection connect =
new SQLiteConnection(conn))
{
try
{
connect.Open();
}
catch (Exception e)
{
}
string selectCommand = string.Format("SELECT * FROM [{0}]", tableName);
SQLiteDataAdapter adapter = new SQLiteDataAdapter(selectCommand, connect);
adapter.AcceptChangesDuringFill = false;
SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
SQLiteTransaction transaction = connect.BeginTransaction();
builder.GetInsertCommand().Transaction = transaction;
int rowsAffected = adapter.Update(DT.Select());
transaction.Commit();
connect.Close();
}
}
catch (Exception e)
{
String error = e.ToString();
Worksx = false;
}
}
}
RotationCount++;
}
}
else
{
Worksx = false;
}
return(Worksx);
}
//Add new Assembly Group
public int CreateNewAssemblyGroup(List <AssemblyGroupModel> assembly)
{
int affected = 0;
string commandString = "INSERT INTO `ASSEMBLY_GROUP`(`AssemblyId`,`PartsId`,`PartsQuantity`) " +
"VALUES (@p1,@p2,@p3);";
SQLiteConnection connection = null;
SQLiteTransaction transaction = null;
SQLiteCommand comm = null;
try
{
//Open a new connection
connection = SQLiteHelper.OpenConn();
//Start transaction (ATOMICITY)
transaction = connection.BeginTransaction();
//Start a new query and assign the transaction
comm = connection.CreateCommand();
comm.Transaction = transaction;
//Start inserting items
foreach (AssemblyGroupModel item in assembly)
{
comm.CommandText = commandString;
comm.Parameters.Add("@p1", DbType.Int32).Value = item.AssemblyID;
comm.Parameters.Add("@p2", DbType.Int32).Value = item.PartsID;
comm.Parameters.Add("@p3", DbType.Int32).Value = item.PartsQuantity;
affected += comm.ExecuteNonQuery();
}
//Commit transaction
transaction.Commit();
//Close connection
SQLiteHelper.CloseConn();
}
catch (Exception e)
{
//Transaction Rollback Failure
if (transaction != null)
{
try
{
transaction.Rollback();
affected = 0;
}
catch (SQLiteException sqlEx)
{
MessageBox.Show("Transaction rollback Failure: " + sqlEx.Message);
}
finally
{
transaction.Dispose();
}
}
Console.WriteLine(e.Message);
SQLiteHelper.CloseConn();
MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
affected = -1;
}
return(affected);
}
private void add2index(bool updcontent = false)
{
SetStatus("Scanning for files");
var files = Directory.GetFiles(txtpath4src.Text, txtmask.Text, System.IO.SearchOption.AllDirectories);
if (files.Length == 0)
{
return;
}
SetPBVisible(true);
SetPBMax(files.Length);
SetStatus("Scan complite");
string qry = "";
int i = 0;
foreach (string fname in files)
{
SetStatus($"Add '{fname}' to index.");
if (File.Exists(fname))
{
try
{
SRCResult FndData = _FilesInDB.FirstOrDefault(x => x.FileName == fname);
int rowid = FndData != null ? FndData.FileID : -1;
string FileHash = TMD5.ComputeFilesMD5(fname);
string runqry = "";
if ((updcontent && FileHash != FndData?.FileHash) || rowid == -1)
{
using (SQLiteTransaction myTransaction = sqlcon.BeginTransaction())
{
using (SQLiteCommand servCommand = new SQLiteCommand(sqlcon))
{
SQLiteParameter FileName = new SQLiteParameter();
if (rowid != -1)
{
sql3runquery($"delete from {_FTS_TABLE} where fileid={rowid}", ref sqlcon);
sql3runquery($"delete from {_FTS_TABLE}_Files where fileid={rowid}", ref sqlcon);
}
servCommand.CommandText = $"INSERT INTO {_FTS_TABLE}_Files (FileName) VALUES (?)";
FileName.Value = fname;
servCommand.Parameters.Add(FileName);
var result = servCommand.ExecuteNonQueryAsync();
if (result.IsFaulted)
{
Invoke((MethodInvoker) delegate
{
rlog.Visible = true;
rlog.AppendText($"InsertFileName fault:{result.Exception.InnerException.Message}");
rlog.Height = 55;
});
break;
}
long rid = servCommand.Connection.LastInsertRowId;
int newrowid = GetRowID(fname);
SQLiteCommand insCommand = new SQLiteCommand(sqlcon);
insCommand.CommandText = $"INSERT OR REPLACE INTO {_FTS_TABLE} (fileid, content) VALUES(?, ?)";
SQLiteParameter FileData = new SQLiteParameter();
SQLiteParameter FileID = new SQLiteParameter();
FileData.Value = File.ReadAllText(fname);
FileID.Value = newrowid;
insCommand.Parameters.Add(FileID);
insCommand.Parameters.Add(FileData);
result = insCommand.ExecuteNonQueryAsync();
if (result.IsFaulted)
{
Invoke((MethodInvoker) delegate
{
rlog.Visible = true;
rlog.AppendText($"InsertFileName fault:{result.Exception.InnerException.Message}");
rlog.Height = 55;
});
break;
}
servCommand.CommandText = $"Update {_FTS_TABLE}_Files set FileHash='(?)' where FileID=(?)";
servCommand.Parameters.Add(new SQLiteParameter("FileHash", FileHash));
servCommand.Parameters.Add(new SQLiteParameter("FileID", rowid));
var result3 = servCommand.ExecuteNonQueryAsync();
if (result3.IsFaulted)
{
Invoke((MethodInvoker) delegate
{
rlog.Visible = true;
rlog.AppendText($"Update FileHash fault:{result3.Exception.InnerException.Message}");
rlog.Height = 55;
});
break;
}
}
myTransaction.Commit();
}
}
}
catch (Exception ex)
{
Invoke((MethodInvoker) delegate
{
rlog.Visible = true;
rlog.AppendText("Query ERR " + ex.Message + Environment.NewLine + qry);
rlog.Height = 55;
badd.Enabled = true;
});
}
}
SetStatus(i + "/" + files.Length);
UPDPB1(i);
i++;
}
SQLiteCommand srvCommand = new SQLiteCommand(sqlcon);
srvCommand.CommandText = $"INSERT INTO {_FTS_TABLE} VALUES('optimize','optimize')";
var res = srvCommand.ExecuteNonQueryAsync();
if (res.IsFaulted)
{
Invoke((MethodInvoker) delegate
{
rlog.Visible = true;
rlog.AppendText($"optimize fault:{res.Exception.InnerException.Message}");
rlog.Height = 55;
});
}
SetStatus("Ожидание");
UPDPB1(0);
GC.Collect();
SetPBVisible(false);
Task.Run(() => initDBFiles());
Invoke((MethodInvoker) delegate
{
badd.Enabled = true;
});
}
static void Main(string[] args)
{
Console.WriteLine("Downloading data from Dirble API...");
using (SQLiteConnection db = new SQLiteConnection("Data Source=waradio.db;Version=3;"))
{
db.Open();
using (WebClient Client = new WebClient())
{
using (SQLiteTransaction tr = db.BeginTransaction())
{
using (SQLiteCommand cmd = db.CreateCommand())
{
cmd.Transaction = tr;
Console.Write(" - countries");
cmd.CommandText = @"CREATE TABLE `countries` (
`country_code` TEXT NOT NULL,
`name` TEXT NOT NULL,
`region` TEXT NOT NULL,
`subregion` TEXT NOT NULL,
PRIMARY KEY(country_code)
) WITHOUT ROWID";
cmd.ExecuteNonQuery();
string data = Client.DownloadString("http://api.dirble.com/v2/countries?token=" + token);
dynamic countries = JsonConvert.DeserializeObject(data);
foreach (dynamic country in countries)
{
cmd.CommandText = "INSERT INTO countries VALUES (@code, @name, @region, @subregion)";
cmd.Parameters.AddWithValue("code", country.country_code);
cmd.Parameters.AddWithValue("name", country.name);
cmd.Parameters.AddWithValue("region", country.region);
cmd.Parameters.AddWithValue("subregion", country.subregion);
cmd.ExecuteNonQuery();
}
Console.WriteLine("\r + countries");
Console.Write(" - genres");
cmd.CommandText = @"CREATE TABLE `genres` (
`id` INTEGER NOT NULL,
`title` TEXT NOT NULL,
`description` TEXT,
`slug` TEXT NOT NULL,
`ancestry` TEXT,
PRIMARY KEY(id)
) WITHOUT ROWID";
cmd.ExecuteNonQuery();
data = Client.DownloadString("http://api.dirble.com/v2/categories?token=" + token);
dynamic genres = JsonConvert.DeserializeObject(data);
foreach (dynamic genre in genres)
{
cmd.CommandText = "INSERT INTO genres VALUES (@id, @title, @description, @slug, @ancestry)";
cmd.Parameters.AddWithValue("id", genre.id);
cmd.Parameters.AddWithValue("title", genre.title);
cmd.Parameters.AddWithValue("description", genre.description);
cmd.Parameters.AddWithValue("slug", genre.slug);
cmd.Parameters.AddWithValue("ancestry", genre.ancestry);
cmd.ExecuteNonQuery();
}
Console.WriteLine("\r + genres");
Console.Write(" - stations");
cmd.CommandText = @"CREATE TABLE `stations` (
`id` INTEGER NOT NULL,
`name` TEXT NOT NULL,
`country` TEXT NOT NULL,
`image` TEXT,
`slug` TEXT NOT NULL,
`website` TEXT,
PRIMARY KEY(id)
) WITHOUT ROWID";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE `streams` (
`station_id` INTEGER NOT NULL,
`stream` TEXT NOT NULL,
`bitrate` INTEGER NOT NULL,
`content_type` TEXT
)";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE `station_genres` (
`station_id` INTEGER NOT NULL,
`genre_id` INTEGER NOT NULL
)";
cmd.ExecuteNonQuery();
int page = 1;
while (true)
{
data = Client.DownloadString("http://api.dirble.com/v2/stations?token=" + token + "&per_page=30&page=" + page);
if (data.Equals("[]") || page > 500)
{
break;
}
dynamic stations = JsonConvert.DeserializeObject(data);
foreach (dynamic station in stations)
{
cmd.CommandText = "INSERT INTO stations VALUES (@id, @name, @country, @image, @slug, @website)";
cmd.Parameters.AddWithValue("id", station.id);
cmd.Parameters.AddWithValue("name", station.name);
cmd.Parameters.AddWithValue("country", station.country);
cmd.Parameters.AddWithValue("image", station.image.url);
cmd.Parameters.AddWithValue("slug", station.slug);
cmd.Parameters.AddWithValue("website", station.website);
cmd.ExecuteNonQuery();
foreach (dynamic streams in station.streams)
{
if (streams.status == 0)
{
continue;
}
cmd.CommandText = "INSERT INTO streams VALUES (@id, @stream, @bitrate, @content_type)";
cmd.Parameters.AddWithValue("id", station.id);
cmd.Parameters.AddWithValue("stream", streams.stream);
cmd.Parameters.AddWithValue("bitrate", streams.bitrate);
cmd.Parameters.AddWithValue("content_type", streams.content_type);
cmd.ExecuteNonQuery();
}
foreach (dynamic sgenres in station.categories)
{
cmd.CommandText = "INSERT INTO station_genres VALUES (@station, @genre)";
cmd.Parameters.AddWithValue("station", station.id);
cmd.Parameters.AddWithValue("genre", sgenres.id);
cmd.ExecuteNonQuery();
}
}
++page;
}
Console.WriteLine("\r + stations");
}
tr.Commit();
}
}
}
}
private void WorkerEntryPrt()
{
try
{
OnStatusChanged(new StatusChangedEventArgs("Connecting"));
while (true)
{
try
{
//Scan 10 items. If middle of scan, pick up there
ScanResponse response = ScanData(
PersistentState.Database.GetKey("crashdumps_last_timestamp"),
PersistentState.Database.GetKey("crashdumps_last_key")); //Start scanning based on last key in db
//Into anon type with a little extra info. DB lookup to see if known, parse guid
var newItems = response.Items
.Select(x => new { item = x, guid = Guid.Parse(x["crash_id"].S) })
.Select(old => new { item = old.item, guid = old.guid, known = PersistentState.Database.GetCrash(old.guid) != null })
.ToList();
//If all items are known
if (newItems.All(item => item.known))
{
//reset progress so we start from start (new first on dynamoDB)
PersistentState.Database.SetKey("crashdumps_last_timestamp", null);
PersistentState.Database.SetKey("crashdumps_last_key", null);
//And sleep for exponential backoff
int timeout = _backoff.GetSeconds();
OnStatusChanged(new StatusChangedEventArgs($"No data. Retrying in {TimeSpan.FromSeconds(timeout)}"));
for (int i = timeout - 1; i >= 0; i--)
{
Thread.Sleep(1000);
}
continue;
}
//Otherwise, add _NEW_ items to db
using (SQLiteTransaction transaction = PersistentState.Database.GetTransaction())
{
if (response.LastEvaluatedKey.Count == 0)
{
//If we reached the last (oldest), reset progress meter
PersistentState.Database.SetKey("crashdumps_last_timestamp", null);
PersistentState.Database.SetKey("crashdumps_last_key", null);
}
else
{
//Otherwise set next to take next block
Dictionary <string, AttributeValue> nextRead = response.LastEvaluatedKey;
PersistentState.Database.SetKey("crashdumps_last_timestamp", nextRead["upload_timestamp"].N);
PersistentState.Database.SetKey("crashdumps_last_key", nextRead["crash_id"].S);
}
//Write stuff
foreach (var item in newItems.Where(x => !x.known))
{
WriteCrashToDb(item.item);
//Don't take so long waiting for the next if we found anything.
//Theoretically this should keep it checking roughly same frequency as new items gets added
//in reality it is probably bull
_backoff.Sucess();
}
transaction.Commit();
}
//Tell the good news that we have new items. Also tell guids so it can be found
OnStatusChanged(new StatusChangedEventArgs("Working",
newItems
.Where(x => !x.known)
.Select(x => x.guid)
.ToList()
));
}
catch (InternalServerErrorException)
{
int timeout = _backoff.GetSeconds();
for (int i = timeout - 1; i >= 0; i--)
{
OnStatusChanged(new StatusChangedEventArgs($"Internal server error, retrying in {i} seconds"));
Thread.Sleep(1000);
}
}
catch (ProvisionedThroughputExceededException)
{
int timeout = _backoff.GetSeconds();
for (int i = timeout - 1; i >= 0; i--)
{
OnStatusChanged(new StatusChangedEventArgs($"Too fast, retrying in {i} seconds"));
Thread.Sleep(1000);
}
}
}
}
#if DEBUG
catch (StackOverflowException ex)
#else
catch (Exception ex)
#endif
{
OnStatusChanged(new StatusChangedEventArgs("Crashed", ex));
throw;
}
}
public static int InsertSPD(Spd s, List <Pengikut> pengikut, Biaya b)
{
int r = -1;
try {
using (SQLiteConnection conn = new SQLiteConnection(connectionString)) {
conn.Open();
using (SQLiteTransaction tr = conn.BeginTransaction()) {
using (SQLiteCommand cmd = new SQLiteCommand(conn)) {
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO data (kode, pegawai, tb, maksud, transport, t_berangkat, t_tujuan, penjabat, jabatan, lama, tgl_berangkat, tgl_kembali, no_surat, tgl_tugas, akun) VALUES (@kode,@pegawai,@tb,@maksud,@transport,@t_berangkat,@t_tujuan,@penjabat,@jabatan,@lama,@tgl_berangkat,@tgl_kembali,@no_surat,@tgl_tugas,@akun); SELECT last_insert_rowid()";
cmd.Prepare();
cmd.Parameters.AddWithValue("@kode", s.kode);
cmd.Parameters.AddWithValue("@pegawai", s.pegawai_id);
cmd.Parameters.AddWithValue("@tb", s.tb);
cmd.Parameters.AddWithValue("@maksud", s.maksud);
cmd.Parameters.AddWithValue("@transport", s.trasport_id);
cmd.Parameters.AddWithValue("@t_berangkat", s.t_berangkat_id);
cmd.Parameters.AddWithValue("@t_tujuan", s.t_tujuan_id);
cmd.Parameters.AddWithValue("@penjabat", s.penjabat);
cmd.Parameters.AddWithValue("@jabatan", s.t_jabatan);
cmd.Parameters.AddWithValue("@lama", s.lama);
cmd.Parameters.AddWithValue("@tgl_berangkat", s.tgl_berangkat);
cmd.Parameters.AddWithValue("@tgl_kembali", s.tgl_kembali);
cmd.Parameters.AddWithValue("@no_surat", s.no_surat);
cmd.Parameters.AddWithValue("@tgl_tugas", s.tgl_tugas);
cmd.Parameters.AddWithValue("@akun", s.akun);
int id = Int32.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "INSERT INTO biaya(harian, h_lama, h_total, penginapan, p_lama, p_total, transport_pp, transport_loak, damri, lain, data) VALUES (@harian,@h_lama,@h_total,@penginapan,@p_lama,@p_total,@transport_pp,@transport_loak,@damri,@lain,@data)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@harian", b.harian);
cmd.Parameters.AddWithValue("@h_lama", b.h_lama);
cmd.Parameters.AddWithValue("@h_total", b.h_total);
cmd.Parameters.AddWithValue("@penginapan", b.penginapan);
cmd.Parameters.AddWithValue("@p_lama", b.p_lama);
cmd.Parameters.AddWithValue("@p_total", b.p_total);
cmd.Parameters.AddWithValue("@transport_pp", b.transport_pp);
cmd.Parameters.AddWithValue("@transport_loak", b.transport_loak);
cmd.Parameters.AddWithValue("@damri", b.damri);
cmd.Parameters.AddWithValue("@lain", b.lain_lain);
cmd.Parameters.AddWithValue("@data", id);
cmd.ExecuteNonQuery();
foreach (var p in pengikut)
{
cmd.CommandText = "INSERT INTO pengikut (pegawai, ket, data, t_lahir) VALUES (@pegawai, @ket, @data, @t_lahir)";
cmd.Parameters.AddWithValue("@pegawai", p.pegawai);
cmd.Parameters.AddWithValue("@ket", p.ket);
cmd.Parameters.AddWithValue("@data", id);
cmd.Parameters.AddWithValue("@t_lahir", p.t_lahir);
cmd.ExecuteNonQuery();
}
}
tr.Commit();
r = 1;
}
conn.Close();
}
} catch (SQLiteException e) {
throw;
}
return(r);
}
public long SaveInvoice(SimplePOS.Invoicing.SaveableInvoice invoice)
{
mutex.WaitOne();
long number = -1;
connection.Open();
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
//request new number
command.CommandText = "SELECT invoice_id FROM invoice WHERE invoice_date > ? ORDER BY invoice_id DESC LIMIT 1;";
command.Parameters.Add(new SQLiteParameter("invoice_date",
SimplePOS.Util.Timehelper.getTimestampOfDateTime(new DateTime(
SimplePOS.Util.Timehelper.getDateTimeOfTimestamp(invoice.Date).Year, 1, 1))));
SQLiteDataReader reader = command.ExecuteReader();
if (reader.Read())
{
// got valid number
number = (long)reader[0];
number++;
}
else
{
// no Invoice for this year found
number = Preferences.PreferenceManager.INVOICE_NUMBER_START;
}
invoice.Number = number;
reader.Close();
// save invoice
command.Parameters.Clear();
command.CommandText = "INSERT INTO invoice (invoice_id, invoice_date, " +
"invoice_amount, currency) VALUES (?, ?, ?, ?);";
command.Parameters.Add(new SQLiteParameter("invoice_id", number));
command.Parameters.Add(new SQLiteParameter("invoice_date", invoice.Date));
command.Parameters.Add(new SQLiteParameter("invoice_amount", invoice.Amount));
command.Parameters.Add(new SQLiteParameter("currency", invoice.Currency));
command.ExecuteNonQuery();
// save items
SQLiteParameter invoice_id = new SQLiteParameter("invoice_id");
SQLiteParameter item_id = new SQLiteParameter("item_id");
SQLiteParameter item_name = new SQLiteParameter("item_name");
SQLiteParameter quantity = new SQLiteParameter("quantity");
SQLiteParameter price = new SQLiteParameter("price");
SQLiteParameter sum_price = new SQLiteParameter("sum_price");
SQLiteParameter tax = new SQLiteParameter("tax");
command.Parameters.Clear();
command.CommandText = "INSERT INTO invoice_item (invoice_id, item_id, " +
"item_name, quantity, price, sum_price, tax) VALUES (?,?,?,?,?,?,?);";
command.Parameters.Add(invoice_id);
command.Parameters.Add(item_id);
command.Parameters.Add(item_name);
command.Parameters.Add(quantity);
command.Parameters.Add(price);
command.Parameters.Add(sum_price);
command.Parameters.Add(tax);
foreach (SaveableInvoiceItem item in invoice.Items)
{
invoice_id.Value = number;
item_id.Value = item.Number;
item_name.Value = item.Name;
quantity.Value = item.Quantity;
price.Value = item.Price;
sum_price.Value = item.Sum;
tax.Value = item.Tax;
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
connection.Close();
mutex.Release();
return(number);
}
public static void CommitTransaction()
{
transaction.Commit();
transaction = null;
}
public void Commit()
{
_transaction.Commit();
_transaction = null;
}
internal static bool UpdateTriggers(List <DeviceTrigger> triggers)
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM [TRIGGERS];";
updatedRows += cmd.ExecuteNonQuery();
foreach (var trigger in triggers)
{
cmd.CommandText = "INSERT INTO [TRIGGERS] ([DeviceName], [Category], " +
"[Label], [TimeTrigger], [TriggerText], [Recurrence], [TriggerAudioFile], " +
"[TriggerAudioFileExists], [Tooltip]) " +
"VALUES(@deviceName, @category, @label, @timeTrigger, " +
"@triggerText, @recurrence, @triggerAudioFile, @triggerAudioFileExists, @tooltip);";
cmd.Parameters.Add(new SQLiteParameter("@deviceName", DbType.String)
{
Value = trigger.DeviceName
});
cmd.Parameters.Add(new SQLiteParameter("@category", DbType.String)
{
Value = trigger.Category
});
cmd.Parameters.Add(new SQLiteParameter("@label", DbType.String)
{
Value = trigger.Label
});
cmd.Parameters.Add(new SQLiteParameter("@timeTrigger", DbType.Int32)
{
Value = trigger.TimeTrigger ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@triggerText", DbType.String)
{
Value = trigger.TriggerText
});
cmd.Parameters.Add(new SQLiteParameter("@recurrence", DbType.String)
{
Value = trigger.Recurrence
});
cmd.Parameters.Add(new SQLiteParameter("@triggerAudioFile", DbType.String)
{
Value = trigger.TriggerAudioFile
});
cmd.Parameters.Add(new SQLiteParameter("@triggerAudioFileExists", DbType.Int32)
{
Value = trigger.TriggerAudioFileExists ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@tooltip", DbType.String)
{
Value = trigger.Tooltip
});
updatedRows += cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
return(updatedRows == triggers.Count);
}
}
private void ParseXccdfWithXmlReader(string fileName, ObservableCollection <MitigationItem> mitigationsList, string systemName)
{
try
{
XmlReaderSettings xmlReaderSettings = GenerateXmlReaderSettings();
fileNameWithoutPath = Path.GetFileName(fileName);
using (SQLiteTransaction sqliteTransaction = FindingsDatabaseActions.sqliteConnection.BeginTransaction())
{
using (SQLiteCommand sqliteCommand = FindingsDatabaseActions.sqliteConnection.CreateCommand())
{
sqliteCommand.Parameters.Add(new SQLiteParameter("GroupName", systemName));
sqliteCommand.CommandText = SetSqliteCommandText("Groups");
sqliteCommand.ExecuteNonQuery();
sqliteCommand.Parameters.Add(new SQLiteParameter("FindingType", "XCCDF"));
sqliteCommand.Parameters.Add(new SQLiteParameter("FileName", fileNameWithoutPath));
sqliteCommand.CommandText = SetSqliteCommandText("FileNames");
sqliteCommand.ExecuteNonQuery();
using (XmlReader xmlReader = XmlReader.Create(fileName, xmlReaderSettings))
{
while (xmlReader.Read())
{
if (xmlReader.IsStartElement())
{
switch (xmlReader.Prefix)
{
case "cdf":
{
ParseXccdfFromScc(xmlReader, systemName, sqliteCommand);
break;
}
case "xccdf":
{
ParseXccdfFromAcas(xmlReader, sqliteCommand);
break;
}
case "oval-res":
{
incorrectFileType = true;
return;
}
case "oval-var":
{
incorrectFileType = true;
return;
}
case "":
{
ParseXccdfFromScc(xmlReader, systemName, sqliteCommand);
break;
}
default:
{ break; }
}
}
}
}
}
sqliteTransaction.Commit();
}
}
catch (Exception exception)
{
log.Error("Unable to parse XCCDF using XML reader.");
throw exception;
}
}
private void ParseNessusWithXmlReader(Object.File file)
{
try
{
if (DatabaseBuilder.sqliteConnection.State.ToString().Equals("Closed"))
{
DatabaseBuilder.sqliteConnection.Open();
}
using (SQLiteTransaction sqliteTransaction = DatabaseBuilder.sqliteConnection.BeginTransaction())
{
using (SQLiteCommand sqliteCommand = DatabaseBuilder.sqliteConnection.CreateCommand())
{
databaseInterface.InsertParameterPlaceholders(sqliteCommand);
sqliteCommand.Parameters["FindingType"].Value = "ACAS";
sqliteCommand.Parameters["GroupName"].Value = string.IsNullOrWhiteSpace(_groupName) ? "All" : _groupName;
databaseInterface.InsertParsedFileSource(sqliteCommand, file);
XmlReaderSettings xmlReaderSettings = GenerateXmlReaderSettings();
using (XmlReader xmlReader = XmlReader.Create(file.FilePath, xmlReaderSettings))
{
while (xmlReader.Read())
{
if (xmlReader.IsStartElement())
{
switch (xmlReader.Name)
{
case "ReportHost":
{
ParseHostData(sqliteCommand, xmlReader);
break;
}
case "ReportItem":
{
ParseVulnerability(sqliteCommand, xmlReader);
break;
}
}
}
else if (xmlReader.NodeType == XmlNodeType.EndElement && xmlReader.Name.Equals("ReportHost"))
{
sqliteCommand.Parameters["Found21745"].Value = found21745;
sqliteCommand.Parameters["Found26917"].Value = found26917;
databaseInterface.SetCredentialedScanStatus(sqliteCommand);
if (!found21745 && !found26917)
{
sqliteCommand.Parameters.Add(new SQLiteParameter("UpdatedStatus", "Completed"));
List <string> ids =
databaseInterface.SelectOutdatedUniqueFindings(sqliteCommand, lastObserved);
foreach (string id in ids)
{
sqliteCommand.Parameters.Add(new SQLiteParameter("UniqueFinding_ID", id));
databaseInterface.UpdateUniqueFindingStatusById(sqliteCommand);
}
if (sqliteCommand.Parameters.Contains("UpdatedStatus"))
{
sqliteCommand.Parameters.Remove(sqliteCommand.Parameters["UpdatedStatus"]);
}
if (sqliteCommand.Parameters.Contains("UniqueFinding_ID"))
{
sqliteCommand.Parameters.Remove(sqliteCommand.Parameters["UniqueFinding_ID"]);
}
}
found21745 = found26917 = false;
}
}
}
}
sqliteTransaction.Commit();
}
}
catch (Exception exception)
{
LogWriter.LogError("Unable to parse ACAS Nessus file with XmlReader.");
throw exception;
}
finally
{ DatabaseBuilder.sqliteConnection.Close(); }
}
public static void FindEarliestGracesJapaneseEntry(String ConnectionString, String GracesJapaneseConnectionString)
{
using (SQLiteConnection ConnectionE = new SQLiteConnection(ConnectionString))
using (SQLiteConnection ConnectionJ = new SQLiteConnection(GracesJapaneseConnectionString)) {
ConnectionE.Open();
ConnectionJ.Open();
using (SQLiteTransaction TransactionE = ConnectionE.BeginTransaction())
using (SQLiteTransaction TransactionJ = ConnectionJ.BeginTransaction())
using (SQLiteCommand CommandEFetch = new SQLiteCommand(ConnectionE))
using (SQLiteCommand CommandEUpdate = new SQLiteCommand(ConnectionE))
using (SQLiteCommand CommandJ = new SQLiteCommand(ConnectionJ)) {
// fetch, from the individual game file Database, all IDs and corresponding GracesJapanese StringIDs
CommandEFetch.CommandText = "SELECT ID, StringID FROM Text ORDER BY ID";
SQLiteDataReader r = CommandEFetch.ExecuteReader();
List <GraceNoteDatabaseEntry> DatabaseEntries = new List <GraceNoteDatabaseEntry>();
while (r.Read())
{
int ID = r.GetInt32(0);
int StringID = r.GetInt32(1);
var gn = new GraceNoteDatabaseEntry();
gn.ID = ID;
gn.JPID = StringID;
DatabaseEntries.Add(gn);
}
r.Close();
CommandJ.CommandText = "PRAGMA case_sensitive_like = ON";
int affected = CommandJ.ExecuteNonQuery();
// This finds all entries in GracesJapanese that have the same Japanese text as the current game file DB entry
CommandJ.CommandText =
"SELECT ID FROM Japanese WHERE CAST(string AS BLOB) = "
+ "( SELECT CAST(string AS BLOB) FROM Japanese WHERE ID = ? ) ORDER BY ID ASC";
SQLiteParameter ParamJId = new SQLiteParameter();
CommandJ.Parameters.Add(ParamJId);
// This updates the game file DB with the new StringID
CommandEUpdate.CommandText = "UPDATE Text SET StringID = ? WHERE ID = ?";
SQLiteParameter ParamEStringId = new SQLiteParameter();
SQLiteParameter ParamEId = new SQLiteParameter();
CommandEUpdate.Parameters.Add(ParamEStringId);
CommandEUpdate.Parameters.Add(ParamEId);
int entryCounter = 0;
int alreadyCorrectChainCounter = 0;
foreach (var e in DatabaseEntries)
{
++entryCounter;
// get the lowest StringID
ParamJId.Value = e.JPID;
int?EarliestStringId = (int?)CommandJ.ExecuteScalar();
// and put it into the game file DB, if needed
if (EarliestStringId != null && EarliestStringId != e.JPID)
{
alreadyCorrectChainCounter = 0;
Console.WriteLine("Changing Entry #" + e.ID + " from StringID " + e.JPID + " to " + EarliestStringId);
ParamEId.Value = e.ID;
ParamEStringId.Value = EarliestStringId;
CommandEUpdate.ExecuteNonQuery();
}
else
{
++alreadyCorrectChainCounter;
if (alreadyCorrectChainCounter >= 10)
{
Console.WriteLine("Processing Entry " + entryCounter + " of " + DatabaseEntries.Count);
alreadyCorrectChainCounter = 0;
}
}
}
TransactionJ.Rollback();
TransactionE.Commit();
}
}
}
public void Complete()
{
_tr?.Commit();
_tr?.Dispose();
_tr = null;
}
public int Pay(bool isUseMoney, int memberId, decimal payMoney, int orderid, decimal discount)
{
//创建数据库的链接对象
using (SQLiteConnection conn = new SQLiteConnection(System.Configuration.ConfigurationManager.ConnectionStrings["itcastCater"].ConnectionString))
{
int result = 0;
//open connection
conn.Open();
SQLiteTransaction tran = conn.BeginTransaction();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Transaction = tran;
string sql = "";
SQLiteParameter[] ps;
try
{
//1、check if use credit
if (isUseMoney)
{
//use credit
sql = "update MemberInfo set mMoney=mMoney-@payMoney where mid=@mid";
ps = new SQLiteParameter[]
{
new SQLiteParameter("@payMoney", payMoney),
new SQLiteParameter("@mid", memberId)
};
cmd.CommandText = sql;
cmd.Parameters.AddRange(ps);
result += cmd.ExecuteNonQuery();
}
//2、change status to ispay
sql = "update orderInfo set isPay=1,memberId=@mid,discount=@discount where oid=@oid";
ps = new SQLiteParameter[]
{
new SQLiteParameter("@mid", memberId),
new SQLiteParameter("@discount", discount),
new SQLiteParameter("@oid", orderid)
};
cmd.CommandText = sql;
cmd.Parameters.Clear();
cmd.Parameters.AddRange(ps);
result += cmd.ExecuteNonQuery();
//3、change status to isfree
sql = "update tableInfo set tIsFree=1 where tid=(select tableId from orderinfo where oid=@oid)";
SQLiteParameter p = new SQLiteParameter("@oid", orderid);
cmd.CommandText = sql;
cmd.Parameters.Clear();
cmd.Parameters.Add(p);
result += cmd.ExecuteNonQuery();
//commit transaction
tran.Commit();
}
catch
{
result = 0;
//rollback
tran.Rollback();
}
return(result);
}
}
