/// <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 void CommitTran()
{
if (tran != null)
{
tran.Commit();
tran = null;
}
}
public bool StopTransaction()
{
transaction.Commit();
transaction.Dispose();
transaction = null;
cnn.Close();
cnn.Dispose();
cnn = null;
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();
}
}
}
/// 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();
}
}
static public void ImportEnsemblExons()
{
App.DB.OpenFast();
App.Log("Converting Exon Data");
App.DB.Exec("DELETE FROM exon");
int c = 0;
String sql = "SELECT e.id, e.start, e.end, e.strand, e.name, et.indexRank, chromosome.id as chrId, g.id as geneId " +
"FROM exonEns as e " +
"JOIN geneEns as g ON g.isCurrent=1 AND g.status='KNOWN' AND g.biotype='protein_coding' " +
"JOIN chromosome ON chromosome.regionId=g.region " +
"JOIN transcriptEns as t on t.geneId=g.id " +
"JOIN exonTranscriptEns as et ON et.transcriptId=t.id " +
"WHERE e.isCurrent=1 and e.id=et.exonId " +
"GROUP BY e.id";
App.Log("Querying: " + sql);
System.Data.SQLite.SQLiteCommand command = App.DB.CreateCommand();
System.Data.SQLite.SQLiteTransaction transaction = App.DB.BeginTransaction();
command.Transaction = transaction;
String ins = "INSERT OR IGNORE INTO exon (exonId, geneId, chromosomeId, name, start, end, strand, isVirtual) VALUES (" +
"@exonId, @geneId, @chromosomeId, @name, @start, @end, @strand, 0)";
command.CommandText = ins;
System.Data.Common.DbDataReader res = App.DB.Query(sql);
App.Log("Inserting Exon Rows...");
while (res.Read())
{
int chromosomeId = Convert.ToInt16(res["chrId"].ToString());
int geneId = Convert.ToInt32(res["geneId"].ToString());
int indexRank = Convert.ToInt32(res["indexRank"].ToString());
int exonId = Convert.ToInt32(res["id"].ToString());
int start = Convert.ToInt32(res["start"].ToString());
int end = Convert.ToInt32(res["end"].ToString());
int strand = Convert.ToInt32(res["strand"].ToString());
String name = res["name"].ToString();
command.Parameters.AddWithValue("@exonId", exonId);
command.Parameters.AddWithValue("@geneId", geneId);
command.Parameters.AddWithValue("@chromosomeId", chromosomeId);
command.Parameters.AddWithValue("@start", start);
command.Parameters.AddWithValue("@end", end);
command.Parameters.AddWithValue("@strand", strand);
command.Parameters.AddWithValue("@name", name);
command.ExecuteNonQuery();
/* String ins = "INSERT OR IGNORE INTO exon (exonId, geneId, chromosomeId, start, end, strand, indexRank) VALUES (" +
* exonId.ToString()+","+geneId.ToString()+","+chromosomeId.ToString()+","+
* start.ToString()+","+end.ToString()+","+strand.ToString()+","+
* indexRank.ToString()+")";*/
// App.DB.Exec(ins);
// App.Log(ins);
c++;
if ((c % 10) == 0)
{
App.Status("Exon #" + c.ToString());
}
}
App.Status();
App.Log(c.ToString() + " exons inserted");
transaction.Commit();
command.Dispose();
App.DB.Close();
}
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 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);
}
//디비에 넣자
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;
* }
*/
}
}
}
