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 = " + TimeUtils.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(); }
internal void ReinsertCodeIntoDatabase(string ConnectionString) { SQLiteConnection Connection = new SQLiteConnection(ConnectionString); Connection.Open(); using (SQLiteTransaction Transaction = Connection.BeginTransaction()) using (SQLiteCommand CopyCommand = new SQLiteCommand(Connection)) using (SQLiteCommand Command = new SQLiteCommand(Connection)) { SQLiteParameter EnglishIDParam = new SQLiteParameter(); SQLiteParameter CodeTextParam = new SQLiteParameter(); SQLiteParameter CopyIdParam = new SQLiteParameter(); Command.CommandText = "UPDATE Text SET english = ?, UpdatedBy = 'HyoutaTools', UpdatedTimestamp = " + TimeUtils.DateTimeToUnixTime(DateTime.UtcNow) + " WHERE ID = ?"; Command.Parameters.Add(CodeTextParam); Command.Parameters.Add(EnglishIDParam); CopyCommand.CommandText = "INSERT INTO History(ID, english, comment, status, UpdatedBy, UpdatedTimestamp) SELECT ID, english, comment, status, UpdatedBy, UpdatedTimestamp FROM Text WHERE ID = ?"; CopyCommand.Parameters.Add(CopyIdParam); int ENID = 1; string TextToInsert = ""; foreach (ScriptEntry s in this.ScriptData) { if (s.Type == 0x02) { CopyIdParam.Value = ENID; CopyCommand.ExecuteNonQuery(); CodeTextParam.Value = TextToInsert; EnglishIDParam.Value = ENID; Command.ExecuteNonQuery(); ENID++; ENID++; TextToInsert = ""; continue; } TextToInsert = TextToInsert + s.FormatForGraceNote() + '\n'; } if (TextToInsert != null) { CopyIdParam.Value = ENID; CopyCommand.ExecuteNonQuery(); CodeTextParam.Value = TextToInsert; EnglishIDParam.Value = ENID; Command.ExecuteNonQuery(); ENID++; } Transaction.Commit(); } Connection.Close(); return; }
/// <summary>Utility Function to insert Entries into a GraceNote database. Usually used when ripping text from a game file.</summary> /// <param name="Entries">The Entries to insert. Required values in the entries are: TextJP, TextEN, Comment, Status, PointerRef, IdentifyString, and IdentifyPointerRef. The rest is filled automatically.</param> public static void InsertSQL(GraceNoteDatabaseEntry[] Entries, String ConnectionString, String ConnectionStringGracesJapanese) { SQLiteConnection Connection = new SQLiteConnection(ConnectionString); SQLiteConnection ConnectionGracesJapanese = new SQLiteConnection(ConnectionStringGracesJapanese); Connection.Open(); ConnectionGracesJapanese.Open(); using (SQLiteTransaction Transaction = Connection.BeginTransaction()) using (SQLiteTransaction TransactionGracesJapanese = ConnectionGracesJapanese.BeginTransaction()) using (SQLiteCommand CommandInsertEntry = new SQLiteCommand(Connection)) using (SQLiteCommand CommandInsertJapanese = new SQLiteCommand(ConnectionGracesJapanese)) using (SQLiteCommand CommandGetMaxJapaneseID = new SQLiteCommand(ConnectionGracesJapanese)) using (SQLiteCommand CommandSearchJapanese = new SQLiteCommand(ConnectionGracesJapanese)) { SQLiteParameter JapaneseIDParam = new SQLiteParameter(); SQLiteParameter JapaneseParam = new SQLiteParameter(); SQLiteParameter EnglishIDParam = new SQLiteParameter(); SQLiteParameter StringIDParam = new SQLiteParameter(); SQLiteParameter EnglishParam = new SQLiteParameter(); SQLiteParameter CommentParam = new SQLiteParameter(); SQLiteParameter EnglishStatusParam = new SQLiteParameter(); SQLiteParameter PointerRefParam = new SQLiteParameter(); SQLiteParameter IdentifyStringParam = new SQLiteParameter(); SQLiteParameter IdentifyPointerRefParam = new SQLiteParameter(); SQLiteParameter UpdatedTimestampParam = new SQLiteParameter(); SQLiteParameter JapaneseSearchParam = new SQLiteParameter(); CommandInsertJapanese.CommandText = "INSERT INTO Japanese (ID, string, debug) VALUES (?, ?, 0)"; CommandInsertJapanese.Parameters.Add(JapaneseIDParam); CommandInsertJapanese.Parameters.Add(JapaneseParam); CommandInsertEntry.CommandText = "INSERT INTO Text (ID, StringID, english, comment, updated, status, PointerRef, IdentifyString, IdentifyPointerRef, UpdatedBy, UpdatedTimestamp)" + " VALUES (?, ?, ?, ?, 0, ?, ?, ?, ?, \"HyoutaTools\", ?)"; CommandInsertEntry.Parameters.Add(EnglishIDParam); CommandInsertEntry.Parameters.Add(StringIDParam); CommandInsertEntry.Parameters.Add(EnglishParam); CommandInsertEntry.Parameters.Add(CommentParam); CommandInsertEntry.Parameters.Add(EnglishStatusParam); CommandInsertEntry.Parameters.Add(PointerRefParam); CommandInsertEntry.Parameters.Add(IdentifyStringParam); CommandInsertEntry.Parameters.Add(IdentifyPointerRefParam); CommandInsertEntry.Parameters.Add(UpdatedTimestampParam); CommandGetMaxJapaneseID.CommandText = "SELECT MAX(ID)+1 FROM Japanese"; CommandSearchJapanese.CommandText = "SELECT ID FROM Japanese WHERE string = ? AND debug = 0"; CommandSearchJapanese.Parameters.Add(JapaneseSearchParam); int JPID; object JPMaxIDObject = CommandGetMaxJapaneseID.ExecuteScalar(); int JPMaxID; try { JPMaxID = Int32.Parse(JPMaxIDObject.ToString()); } catch (System.FormatException) { // there's no ID in the database, just start with 0 JPMaxID = 0; } int ENID = 1; foreach (GraceNoteDatabaseEntry e in Entries) { // fetch GracesJapanese ID or generate new & insert new text JapaneseSearchParam.Value = e.TextJP; object JPIDobj = CommandSearchJapanese.ExecuteScalar(); if (JPIDobj != null) { JPID = (int)JPIDobj; } else { JPID = JPMaxID++; JapaneseIDParam.Value = JPID; JapaneseParam.Value = e.TextJP; CommandInsertJapanese.ExecuteNonQuery(); } // insert text into English table EnglishIDParam.Value = ENID; StringIDParam.Value = JPID; EnglishParam.Value = e.TextEN; CommentParam.Value = e.Comment; EnglishStatusParam.Value = e.Status; PointerRefParam.Value = e.PointerRef; IdentifyStringParam.Value = e.IdentifyString; IdentifyPointerRefParam.Value = e.IdentifyPointerRef; UpdatedTimestampParam.Value = TimeUtils.DateTimeToUnixTime(DateTime.Now); CommandInsertEntry.ExecuteNonQuery(); ENID++; } Transaction.Commit(); TransactionGracesJapanese.Commit(); } ConnectionGracesJapanese.Close(); Connection.Close(); return; }