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); } }