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