// Выполняет запрос, и в случае если это был INSERT, возвратит rowid добавленной записи
private int ExecuteSQLQuery(string query, out int lastInsertedRowId)
{
using (var connection = new SqliteConnection($"Data Source={m_fileName}"))
{
connection.Open();
int rowCount;
using (var command = connection.CreateCommand())
{
command.CommandText = query;
rowCount = command.ExecuteNonQuery();
}
// Получаем rowid последней добавленной записи. Важно чтобы он был в пределах того же соединения
// что и INSERT запрос (который предположительно идет прямо перед этим), иначе всегда возвращается 0
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT last_insert_rowid();";
using (var reader = command.ExecuteReader())
{
Debug.Assert(reader.HasRows);
reader.Read();
lastInsertedRowId = reader.GetInt32(0);
}
}
connection.Close();
return rowCount;
}
}
public override List <String> SelectQueryString(string strQuery)
{
///<summary>
/// same as SelectQuery but single parameter of string. Allows for more complex select query.
/// </summary>
///<params>
/// </params>
///<output>
/// </output>
#region SelectQueryString_declarations
int intNewConnectionCount = 0;
var lstQueryResults = new List <string>();
var lstReader = new List <string>();
#endregion
#region SelectQueryString_validaton
//valiate strTable parameter
while (this.ConnectionState != "Open")
{
if (intNewConnectionCount < 1)
{
NewConnection();
}
else
{
throw new InvalidOperationException("Unable to obtain active connection to server.");
}
}
#endregion
#region SelectQueryString_procedure
// Execute query
using (var dbCMD = _dbConn.CreateCommand())
{
// Create new command
dbCMD.CommandText = strQuery;
// Get the results from the database
using (var dbRS = dbCMD.ExecuteReader())
{
// Read the display field from the query
while (dbRS.Read())
{
// Read result from query
for (int i = 0; i < dbRS.FieldCount; i++)
{
lstReader.Add(dbRS[i].ToString());
}
lstQueryResults.Add(string.Join("|", lstReader.ToArray()));
lstReader.Clear();
}
}
}
return(lstQueryResults);
#endregion
}
public static void Main(string[] args)
{
var bookmarks = new Bookmarks();
using (var dbConn = new Mono.Data.Sqlite.SqliteConnection("Data Source=/mnt/ramdisk/test.db"))
{
dbConn.Open();
var cmdGetGroups = dbConn.CreateCommand();
cmdGetGroups.CommandText = "SELECT gid, name, IFNULL(pid,0) FROM rel JOIN groups ON rel.gid=groups.rowid WHERE depth=1";
using (IDataReader groups = cmdGetGroups.ExecuteReader())
{
ApplyRow(groups, (g) => bookmarks.GroupAdd(g.GetInt32(0), new Group {
Id = g.GetInt32(0), Name = g.GetString(1), Pid = g.GetInt32(2)
}));
}
var cmdGetRows = dbConn.CreateCommand();
cmdGetRows.CommandText = "SELECT IFNULL(gid,0), key, value FROM data";
using (IDataReader rows = cmdGetRows.ExecuteReader())
{
ApplyRow(rows, (r) => bookmarks.LinkAdd(r.GetInt32(0), new LinkItem {
Title = r.GetString(1), Target = r.GetString(2)
}));
}
}
var document = new XDocument();
document.Add(bookmarks.ToXml());
document.Save("test.xml");
}
//constructor
static folios()
{
try {
//conexion a DB
var connection = new SqliteConnection(dbSource);
connection.Open();
//crear query
var query = connection.CreateCommand();
query.CommandText = "select consecutivo from folios where fecha='" + fecha + "'";
//inicia en el folio de la DB
generarConsecutivo=Convert.ToInt16(query.ExecuteScalar());
//si el valor es 0, entonces creamos un nuevo renglon para ese dia
//esto del 0 no necesariamente funciona en VistaDB
if(generarConsecutivo.Equals(0))
{
generarConsecutivo=1;
query = connection.CreateCommand();
query.CommandText = "insert into folios (fecha,consecutivo) values ('" + fecha + "'," + generarConsecutivo.ToString() + ")";
query.ExecuteNonQuery();
}
//cerrar conexion a DB
connection.Close();
} catch(Exception ex) {
//en caso de error en la base de datos empezamos en 1
generarConsecutivo=1;
}
}
public List<dateData> LoadHistory_DB(Quote q)
{
List<dateData> dataList = new List<dateData>();
string tblNm = GetTableNameHistory_DB(q);
if (!TableExist(tblNm))
return dataList;
SqliteConnection conn = null;
string sql = "";
try {
conn = new SqliteConnection(souce);
conn.Open();
SqliteCommand cmd = conn.CreateCommand();
sql = "select date, volume, open, high, low, close from " + tblNm + " order by date desc;";
cmd.CommandText = sql;
SqliteDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()) {
dateData dd = new dateData();
dd._date = rdr.GetInt64(0);
dd._indic._volume = rdr.GetInt64(1);
dd._price._open = rdr.GetDouble(2);
dd._price._high = rdr.GetDouble(3);
dd._price._low = rdr.GetDouble(4);
dd._price._close = rdr.GetDouble(5);
dataList.Add(dd);
}
} catch (Exception e) {
Output.LogException(" sql (" + sql + ") error : " + e.Message);
}
if (conn != null)
conn.Close();
return dataList;
}
/// <summary>
/// Loads all empty quotes.
/// </summary>
/// <returns>
/// The all quotes only have basic information like code.
/// </returns>
public List<Quote> LoadQuotesInfo_DB()
{
List<Quote> allQ = new List<Quote>();
SqliteConnection conn = null;
string sql = "";
try {
conn = new SqliteConnection(souce);
conn.Open();
SqliteCommand cmd = conn.CreateCommand();
sql = "select si_market, si_code, si_name from " + TableNameInfo + ";";
cmd.CommandText = sql;
SqliteDataReader rdr = cmd.ExecuteReader();
information info = information.EMPTY;
int count = 0;
while (rdr.Read()) {
info._market._value = (market.type)rdr.GetInt32(0);
info.CodeInt = rdr.GetInt32(1);
info._name = rdr.GetString(2);
Quote q = new Quote(info);
allQ.Add(q);
Output.Log("" + ++count + " quotes loaded - " + q.Describe);
}
} catch (Exception e) {
Output.LogException("sql(" + sql + ") error : " + e.Message);
}
if (conn != null)
conn.Close();
return allQ;
}
public static User getDatabase()
{
User result = new User();
using (SqliteConnection co = new SqliteConnection("Data Source=" + databasePath))
{
co.Open();
SqliteCommand cmd = co.CreateCommand();
cmd.CommandText = "Select * From User";
try
{
SqliteDataReader read = cmd.ExecuteReader();
if(read.Read())
{
try { result.usrID = (string)read["ID"]; } catch { result.usrID = ""; }
try { result.phrase = (string)read["Name"]; } catch { throw new Exception("NO Passphrase"); }
try { result.usrCH = (string)read["Chor"]; } catch { result.usrCH = ""; }
try { result.storage = (List<Appointment>)read["Appointments"]; } catch { }
}
}
catch(Exception ex)
{
Console.Write(ex);
}
}
return result;
}
public int DeletePhrase(Phrase item)
{
int r = 0;
lock (locker) {
if (item.id != 0) {
var connection = new SqliteConnection ("Data Source=" + path);
connection.Open ();
using (var command = connection.CreateCommand ()) {
command.CommandText = "Select * from [Phrase] where [id] = ?";
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.id });
var result = command.ExecuteReader ();
if (result.HasRows) {
command.Dispose ();
command.CommandText = "Delete from [Phrase] where [id] = ?";
command.Parameters.Add(new SqliteParameter (DbType.String) { Value = item.categoryId });
r = command.ExecuteNonQuery ();
}
connection.Close ();
}
}
}
return r;
}
public static SqliteConnection GetConnection()
{
var documents = Environment.GetFolderPath (
Environment.SpecialFolder.Personal);
string db = Path.Combine (documents, "mydb.db3");
bool exists = File.Exists (db);
if (!exists)
SqliteConnection.CreateFile (db);
var conn = new SqliteConnection ("Data Source=" + db);
if (!exists) {
var commands = new[] {
"CREATE TABLE People (Id INTEGER NOT NULL, FirstName ntext, LastName ntext, DateCreated datetime)",
"INSERT INTO People (Id, FirstName, LastName, DateCreated) VALUES (1, 'Homer', 'Simpson', '2007-01-01 10:00:00')",
};
foreach (var cmd in commands){
using (var c = conn.CreateCommand()) {
c.CommandText = cmd;
c.CommandType = CommandType.Text;
conn.Open ();
c.ExecuteNonQuery ();
conn.Close ();
}
}
}
return conn;
}
/// <summary>
/// Constructs the transaction object, binding it to the supplied connection
/// </summary>
/// <param name="connection">The connection to open a transaction on</param>
/// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param>
internal SqliteTransaction(SqliteConnection connection, bool deferredLock)
{
_cnn = connection;
_version = _cnn._version;
_level = (deferredLock == true) ? IsolationLevel.ReadCommitted : IsolationLevel.Serializable;
if (_cnn._transactionLevel++ == 0)
{
try
{
using (SqliteCommand cmd = _cnn.CreateCommand())
{
if (!deferredLock)
cmd.CommandText = "BEGIN IMMEDIATE";
else
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
}
}
catch (SqliteException)
{
_cnn._transactionLevel--;
_cnn = null;
throw;
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Tasky.DL.TaskDatabase"/> TaskDatabase.
/// if the database doesn't exist, it will create the database and all the tables.
/// </summary>
public ADODatabase(string dbPath)
{
var output = "";
path = dbPath;
// create the tables
bool exists = File.Exists (dbPath);
if (!exists) {
connection = new SqliteConnection ("Data Source=" + dbPath);
connection.Open ();
var commands = new[] {
"CREATE TABLE [Items] (_id INTEGER PRIMARY KEY ASC, Name NTEXT, Notes NTEXT, Done INTEGER);"
};
foreach (var command in commands) {
using (var c = connection.CreateCommand ()) {
c.CommandText = command;
var i = c.ExecuteNonQuery ();
}
}
} else {
// already exists, do nothing.
}
Console.WriteLine (output);
}
public void DataBaseRead(String query) //DB 읽어오기 - 인자로 쿼리문을 받는다.
{
IDbConnection dbConnection = new SqliteConnection(GetDBFilePath());
dbConnection.Open(); // DB 열기
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = query; // 쿼리 입력
IDataReader dataReader = dbCommand.ExecuteReader(); // 쿼리 실행
while (dataReader.Read()) // 쿼리로 돌아온 레코드 읽기
{
Debug.Log(dataReader.GetInt32(5)); // 5번 점수 필드 읽기
int maxScore = dataReader.GetInt32(5);
MaxScore_Text.text = "최고점수 : " + maxScore;
break; // 내림차순 정렬이므로 처음에 한 번만 레코드값을 가져오면 된다.
}
dataReader.Dispose(); // 생성순서와 반대로 닫아줍니다.
dataReader = null;
dbCommand.Dispose();
dbCommand = null;
// DB에는 1개의 쓰레드만이 접근할 수 있고 동시에 접근시 에러가 발생한다. 그래서 Open과 Close는 같이 써야한다.
dbConnection.Close();
dbConnection = null;
}
public void UpdateAllScores(List <Score> scores)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "DELETE FROM [Score]";
command.ExecuteNonQuery();
command.Dispose();
foreach (var score in scores)
{
this.addScore(conn, trans, score);
}
trans.Commit();
}
catch (Exception exc)
{
trans.Rollback();
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public CodeProjectDatabase ()
{
dbPath = Path.Combine (
Environment.GetFolderPath (Environment.SpecialFolder.Personal),
"items.db3");
bool exists = File.Exists (dbPath);
if (!exists) {
SqliteConnection.CreateFile (dbPath);
}
var connection = new SqliteConnection ("Data Source=" + dbPath);
connection.Open ();
if (!exists) {
var commands = new[]{
"CREATE TABLE [Member] (Key integer, Name ntext, ArticleCnt integer, BlogCnt integer, Reputation ntext, IsMe integer);"
};
foreach (var command in commands) {
using (var c = connection.CreateCommand ()) {
c.CommandText = command;
c.ExecuteNonQuery ();
}
}
}
}
public static SqliteConnection GetConnection()
{
var documents = Environment.GetFolderPath (
Environment.SpecialFolder.Personal);
string db = Path.Combine (documents, dbName);
bool exists = File.Exists (db);
if (!exists)
SqliteConnection.CreateFile (db);
var conn = new SqliteConnection ("Data Source=" + db);
if (!exists) {
var commands = new[] {
"CREATE TABLE Movie (Id INTEGER NOT NULL, Title ntext, Rating ntext, DateCreated datetime)",
"INSERT INTO Movie (Id, Title, Rating, DateCreated) VALUES (1, 'Traffic', 'R', '2007-01-01 10:00:00')",
"INSERT INTO Movie (Id, Title, Rating, DateCreated) VALUES (2, 'The Breakfast Club', 'R', '2007-01-01 10:00:00')",
};
foreach (var cmd in commands) {
using (var c = conn.CreateCommand()) {
c.CommandText = cmd;
c.CommandType = CommandType.Text;
conn.Open ();
c.ExecuteNonQuery ();
conn.Close ();
}
}
}
return conn;
}
public void DatabaseSQLAdd(string query) //삽입이라고 썼지만 삭제도 가능
{
IDbConnection dbConnection = new SqliteConnection(GetDBFilePath());
dbConnection.Open(); // DB 열기
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = query; // 쿼리 입력
try
{
dbCommand.ExecuteNonQuery(); // 쿼리 실행
}
catch (Exception e)
{
Debug.LogError(e);
test.text = e.ToString();
// 빌드시 에러확인을 위해 넣음 결과패널에 Game Over대신 에러 나오게 하는 코드
test_Result.GetComponent <Text>().fontSize = 30; // 폰트 사이즈 작게 변경 - 이유 : 오류가 길어서
test_Result.text = e.ToString();
}
dbCommand.Dispose();
dbCommand = null;
dbConnection.Close();
dbConnection = null;
Debug.Log("데이터 삽입 완료");
test.text = "결과 데이터 삽입 완료";
}
public DrinkDatabase(string dbPath)
{
path = dbPath;
// create the tables
bool exists = File.Exists(dbPath);
if (!exists)
{
connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
var commands = new[] {
"CREATE TABLE [Items] (_id INTEGER PRIMARY KEY ASC, Name NTEXT, About NTEXT, Volume REAL, AlcoholByVolume REAL, IconNumber INTEGER);"
};
foreach (var command in commands)
{
using (var c = connection.CreateCommand())
{
c.CommandText = command;
c.ExecuteNonQuery();
}
}
initializeValues();
}
}
public Bill LoadFavoriteBill(int id)
{
Bill favBill;
using (var connection = new SqliteConnection (connectionString)) {
using (var cmd = connection.CreateCommand ()) {
connection.Open ();
cmd.CommandText = "SELECT * FROM FavoriteBills WHERE id = @id";
var idParam = new SqliteParameter ("@id", id);
cmd.Parameters.Add (idParam);
using (var reader = cmd.ExecuteReader ()) {
reader.Read ();
favBill = new Bill {
Id = Convert.ToInt32 (reader ["id"]),
Title = (string)reader ["title"],
ThomasLink = (string)reader ["thomas_link"],
Notes = reader["notes"] == DBNull.Value ? "" : (string)reader["notes"]
};
}
}
}
return favBill;
}
/// <summary>
/// This code will handle creating the database and a table.
/// </summary>
void HandleTouchUpInside(object sender, EventArgs e)
{
// Create the database
var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
var pathToDatabase = Path.Combine(documents, "db_adonet.db");
SqliteConnection.CreateFile(pathToDatabase);
var msg = "Created new database at " + pathToDatabase;
// Create a table
var connectionString = String.Format("Data Source={0};Version=3;", pathToDatabase);
using (var conn= new SqliteConnection(connectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "CREATE TABLE People (PersonID INTEGE" +
"R PRIMARY KEY AUTOINCREMENT , FirstName ntext, LastName ntext)";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
// Let the user know that the database was created, and disable the button
// to prevent double clicks.
_txtView.Text = msg;
_btnCreateDatabase.Enabled = false;
}
public void UpdateAllAthleteResults(int athleteID, List <Result> results)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "DELETE FROM [Result] WHERE AthleteID=" + athleteID;
command.ExecuteNonQuery();
command.Dispose();
foreach (var result in results)
{
result.AthleteID = athleteID;
addResult(conn, trans, result);
}
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public SqliteVsMonoDSSpeedTests()
{
// create path and filename to the database file.
var documents = Environment.GetFolderPath (
Environment.SpecialFolder.Personal);
_db = Path.Combine (documents, "mydb.db3");
if (File.Exists (_db))
File.Delete (_db);
SqliteConnection.CreateFile (_db);
var conn = new SqliteConnection("URI=" + _db);
using (var c = conn.CreateCommand()) {
c.CommandText = "CREATE TABLE DataIndex (SearchKey INTEGER NOT NULL,Name TEXT NOT NULL,Email Text NOT NULL)";
conn.Open ();
c.ExecuteNonQuery ();
conn.Close();
}
conn.Dispose();
// create path and filename to the database file.
var documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments);
var libraryPath = Path.Combine (documentsPath, "..", "Library");
_dataDirectory = Path.Combine (libraryPath, "MonoDS");
_entity = "PersonEntity";
_serializer = new Serializer();
}
public string proDataExc(string[] strTT)
{
try
{
if (strTT.Length < 1) { return "No SQL"; }
string DatabaseName = "PUB.db3";
string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
string db = System.IO.Path.Combine(documents, DatabaseName);
var conn = new SqliteConnection("Data Source=" + db);
var sqlitecmd = conn.CreateCommand();
conn.Open();
sqlitecmd.CommandType = CommandType.Text;
for (int j = 0; j < strTT.Length; j++)
{
if (strTT[j] == "") { continue; }
sqlitecmd.CommandText = strTT[j];
sqlitecmd.ExecuteNonQuery();
}
conn.Close();
conn.Dispose();
return "";
}
catch (Exception Ex)
{
return Ex.ToString();
}
}
public DataStore()
{
#if WHACKDB
File.Delete("GSharp.db3");
#endif
SqliteConnection conn;
if ( !File.Exists("GSharp.db3"))
{
SqliteConnection.CreateFile("GSharp.db3");
using ( conn = new SqliteConnection ("DbLinqProvider=Sqlite;Data Source=GSharp.db3") )
{
var s = Assembly.GetExecutingAssembly().GetManifestResourceStream("GSharp.Data.GSharp.sql");
var sr = new StreamReader(s);
using ( var cmd = conn.CreateCommand() )
{
cmd.CommandText = sr.ReadToEnd();
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
conn = new SqliteConnection ("DbLinqProvider=Sqlite;Data Source=GSharp.db3");
database = new Main (conn);
}
void Start ()
{
string connectionString = "URI=file:" + Application.dataPath + "/GameMaster"; //Path to database.
IDbConnection dbcon = new SqliteConnection(connectionString) as IDbConnection;
dbcon.Open(); //Open connection to the database.
IDbCommand dbcmd = dbcon.CreateCommand();
dbcmd.CommandText = "SELECT firstname, lastname " + "FROM addressbook";
IDataReader reader = dbcmd.ExecuteReader();
while(reader.Read())
{
string FirstName = reader.GetString (0);
string LastName = reader.GetString (1);
Console.WriteLine("Name: " + FirstName + " " + LastName);
UnityEngine.Debug.LogWarning("Name: " + FirstName + " " + LastName);
}
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbcon.Close();
dbcon = null;
}
public void GotoColorfulScene()
{
SceneName = "Colorfull";
playGameUI.SetActive(true);
string connection = "URI=file:" + Application.dataPath + "/StreamingAssets/User.db";
Debug.Log(connection);
IDbConnection dbcon = new Mono.Data.Sqlite.SqliteConnection(connection);
dbcon.Open();
IDbCommand cmnd_read = dbcon.CreateCommand();
IDataReader reader;
string query = "SELECT * FROM my_user";
cmnd_read.CommandText = query;
reader = cmnd_read.ExecuteReader();
int index = 0;
while (reader.Read())
{
//Debug.Log(reader[0].ToString());
GameObject tempGO = Instantiate(rowSample) as GameObject;
tempGO.transform.Find("Name").GetComponent <Text>().text = reader[1].ToString();
tempGO.transform.Find("ID").GetComponent <Text>().text = reader[0].ToString();
tempGO.transform.GetComponent <PlayGame_Button_Control>().id = reader[0].ToString();
tempGO.transform.GetComponent <PlayGame_Button_Control>().sceneName = "Colorfull";
tempGO.transform.SetParent(rowParent.transform);
data.Add(tempGO);
}
dbcon.Close();
reader.Close();
cmnd_read.Dispose();
}
public void ClickGameStart()
{
del_InsertNewUserInfo InsertInfo = () =>
{
string conn = "URI=file:" + Application.dataPath +
"/StreamingAssets/GameUserDB/userDB.db";
using (IDbConnection dbconn = new SqliteConnection(conn))
{
dbconn.Open(); //Open connection to the database.
using (IDbCommand dbcmd = dbconn.CreateCommand())
{
try
{
string sqlQuery =
"INSERT INTO USER_INFO(name, level, type) " +
"VALUES(" + "'" + chName.text + "'" + ", " +
"'" + chLevel.text + "'" + ", " +
"'" + chType.text + "'" + ")";
dbcmd.CommandText = sqlQuery;
dbcmd.ExecuteNonQuery();
}
catch(SqliteException e)
{
// 이미 등록된 캐릭터이다.
Debug.Log(e.Message);
}
}
dbconn.Close();
}
};
InsertInfo();
GameLoadingProcess();
}
public List<Politician> LoadAllPoliticans()
{
var politicians = new List<Politician> ();
using (var connection = new SqliteConnection (connectionString)) {
using (var cmd = connection.CreateCommand ()) {
connection.Open ();
cmd.CommandText = String.Format ("SELECT bioguide_id, first_name, last_name, govtrack_id, phone, party, state FROM Politician ORDER BY last_name");
using (var reader = cmd.ExecuteReader ()) {
while (reader.Read ()) {
politicians.Add (new Politician {
FirstName = reader ["first_name"].ToString (),
LastName = reader ["last_name"].ToString (),
BioGuideId = reader ["bioguide_id"].ToString (),
GovTrackId = reader ["govtrack_id"].ToString (),
Phone = reader ["phone"].ToString (),
State = reader ["state"].ToString (),
Party = reader ["party"].ToString ()
});
}
}
}
}
return politicians;
}
internal static void DatabaseSQLAdd(string query) //삽입이라고 썼지만 삭제도 가능
{
IDbConnection dbConnection = new SqliteConnection(GetDBFilePath());
dbConnection.Open(); // DB 열기
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = query; // 쿼리 입력
try
{
dbCommand.ExecuteNonQuery(); // 쿼리 실행
}
catch (Exception e)
{
Debug.LogError(e);
//test.text = e.ToString();
}
dbCommand.Dispose();
dbCommand = null;
dbConnection.Close();
dbConnection = null;
Debug.Log("데이터 삽입 완료");
//test.text = "결과 데이터 삽입 완료";
}
private void CreateDatabase()
{
logger.Info ("Initializing config database at " + databasepath);
using (var conn= new SqliteConnection(connection))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "CREATE TABLE Config (ConfigId INTEGER PRIMARY KEY AUTOINCREMENT, Setting VARCHAR(20), SettingValue varchar(200))";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Config(Setting, SettingValue) VALUES('outputdevice', 'Built-in Output')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Config(Setting, SettingValue) VALUES('localonly', '0')";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE Voices (VoiceId INTEGER PRIMARY KEY AUTOINCREMENT, voice VARCHAR(255), lang varchar(2), id integer(4))";
cmd.ExecuteNonQuery();
for(var i=0; i<=19; i++) {
cmd.CommandText = string.Format ("INSERT INTO Voices(voice, lang, id) VALUES('{0}', 'en', {1})", DefaultVoice, i);
cmd.ExecuteNonQuery();
}
for(var i=0; i<=19; i++) {
cmd.CommandText = string.Format ("INSERT INTO Voices(voice, lang, id) VALUES('{0}', 'fr', {1})", DefaultVoice, i);
cmd.ExecuteNonQuery();
}
}
}
}
public DbUtils.Core.Api.IDbServerConnection[] RestoreSessions(SqliteConnection connection)
{
InitializeStateDb (connection);
IList<IDbServerConnection> sessions = new List<IDbServerConnection> ();
using (SqliteCommand cmd = connection.CreateCommand ()) {
cmd.CommandText = "select * from dbstate";
using (SqliteDataReader reader = cmd.ExecuteReader ()) {
while (reader.Read ()) {
string providerName = reader.GetString (reader.GetOrdinal ("provider"));
string state = reader.GetString (reader.GetOrdinal ("state"));
try {
Type providerType = Type.GetType(providerName);
ISessionStateProvider sessionProvider = (ISessionStateProvider) providerType.GetConstructor(new Type[]{}).Invoke(new object[]{});
IDbServerConnection session = sessionProvider.restoreSessionFromState(state);
sessions.Add(session);
} catch( Exception e) {
// todo, log error and continue
throw e;
}
}
}
}
return sessions.ToArray ();
}
public void SaveUserTest()
{
var sut = new UserPersister();
var user = new User { Email = "*****@*****.**", FirstName = "Louis",
LastName = "Salin", Password = "******" };
sut.Save(user);
using (var connection = new SqliteConnection("URI=file:Users.db3"))
{
var query = "SELECT * FROM Users WHERE Email='*****@*****.**';";
using (var cmd = connection.CreateCommand())
{
connection.Open();
cmd.CommandText = query;
using (var reader = cmd.ExecuteReader())
{
reader.Read();
reader["FirstName"].ShouldEqual("Louis");
reader["LastName"].ShouldEqual("Salin");
reader["Email"].ShouldEqual("*****@*****.**");
reader["Password"].ShouldEqual("pass123");
reader.Close();
}
connection.Close();
}
}
}
// Use this for initialization
void Start()
{
string conn = "URI=file:" + Application.dataPath + "/sqlite_userinfo";
IDbConnection dbconn = new SqliteConnection(conn);
dbconn.Open();
IDbCommand dbcmd = dbconn.CreateCommand();
string query = "SELECT idx, name, email FROM userinfo";
dbcmd.CommandText = query;
IDataReader reader = dbcmd.ExecuteReader();
while(reader.Read())
{
int idx = reader.GetInt32(0);
string name = reader.GetString(1);
string email = reader.GetString(2);
Debug.Log("idx : " + idx + ", name : " + name + ", email : " + email);
}
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbconn.Close();
dbconn = null;
}
private void SQLTest()
{
var dataFolder = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "FLangDictionary");
var dbName = "testDB.db";
var dbFilePath = System.IO.Path.Combine(dataFolder, dbName);
if (System.IO.File.Exists(dbFilePath))
System.IO.File.Delete(dbFilePath);
if (!System.IO.Directory.Exists(dataFolder))
System.IO.Directory.CreateDirectory(dataFolder);
SqliteConnection.CreateFile(dbFilePath);
string output = "";
using (var connection = new SqliteConnection("Data Source=" + dbFilePath))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "CREATE TABLE [Items] ([_id] int, [Symbol] ntext, [Name] ntext);";
var rowcount = command.ExecuteNonQuery();
}
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [Items] ([_id], [Symbol]) VALUES ('1', 'APPL')";
var rowcount = command.ExecuteNonQuery(); // rowcount will be 1
}
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM [Items]";
SqliteDataReader reader = command.ExecuteReader();
output += "\nReading data";
while (reader.Read())
output += $"\n\tKey={reader["_id"]}; Value={reader["Symbol"]}";
}
connection.Close();
}
MessageBox.Show(output);
}
private void CreateOrOpenDatabase()
{
lock (_lock)
{
this.DBfileFullPath = System.IO.Path.Combine(AmazonHookedPlatformInfo.Instance.PersistentDataPath, dbFileName);
string vacuumCommand = "PRAGMA auto_vacuum = 1";
string sqlCommand = string.Format(CultureInfo.InvariantCulture, "CREATE TABLE IF NOT EXISTS {0} ({1} TEXT NOT NULL,{2} TEXT NOT NULL UNIQUE,{3} TEXT NOT NULL, {4} INTEGER NOT NULL DEFAULT 0 )",
TABLE_NAME, EVENT_COLUMN_NAME, EVENT_ID_COLUMN_NAME, MA_APP_ID_COLUMN_NAME, EVENT_DELIVERY_ATTEMPT_COUNT_COLUMN_NAME);
if (!File.Exists(this.DBfileFullPath))
{
string directory = Path.GetDirectoryName(this.DBfileFullPath);
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
SqliteConnection.CreateFile(this.DBfileFullPath);
}
try
{
connection = new SqliteConnection("URI=file:" + this.DBfileFullPath);
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = vacuumCommand;
command.ExecuteNonQuery();
}
using (var command = connection.CreateCommand())
{
command.CommandText = sqlCommand;
command.ExecuteNonQuery();
}
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
}
}
public void AddNewUploadEntry(UploadEntry uploadentry)
{
using (SqliteConnection conn = new SqliteConnection(_connectionString))
{
conn.Open();
using (SqliteTransaction trans = conn.BeginTransaction())
{
using (SqliteCommand cmd = conn.CreateCommand())
{
cmd.Transaction = trans;
cmd.CommandText = @"UPDATE UploadEntries SET Cancelled = 1 WHERE Name = @name";
cmd.Parameters.Add(new SqliteParameter("@name", uploadentry.Name));
cmd.ExecuteNonQuery(); //TODO: log here how many other entries were cancelled.
cmd.CommandText = @"INSERT INTO UploadEntries(
Name,
Size,
CleanedName,
ObscuredName,
RemoveAfterVerify,
CreatedAt,
UploadedAt,
NotifiedIndexerAt,
SeenOnIndexerAt,
Cancelled)
VALUES(
@name,
@size,
@cleanedName,
@ObscuredName,
@removeAfterVerify,
@createdAt,
@uploadedAt,
@notifiedIndexerAt,
@seenOnIndexerAt,
@cancelled)";
cmd.Parameters.Add(new SqliteParameter("@name", uploadentry.Name));
cmd.Parameters.Add(new SqliteParameter("@size", uploadentry.Size));
cmd.Parameters.Add(new SqliteParameter("@cleanedName", uploadentry.CleanedName));
cmd.Parameters.Add(new SqliteParameter("@ObscuredName", uploadentry.ObscuredName));
cmd.Parameters.Add(new SqliteParameter("@removeAfterVerify", uploadentry.RemoveAfterVerify));
cmd.Parameters.Add(new SqliteParameter("@createdAt", GetDbValue(uploadentry.CreatedAt)));
cmd.Parameters.Add(new SqliteParameter("@uploadedAt", GetDbValue(uploadentry.UploadedAt)));
cmd.Parameters.Add(new SqliteParameter("@notifiedIndexerAt", GetDbValue(uploadentry.NotifiedIndexerAt)));
cmd.Parameters.Add(new SqliteParameter("@seenOnIndexerAt", GetDbValue(uploadentry.SeenOnIndexAt)));
cmd.Parameters.Add(new SqliteParameter("@cancelled", GetDbValue(uploadentry.Cancelled)));
cmd.ExecuteNonQuery();
cmd.CommandText = "select last_insert_rowid()";
cmd.Parameters.Clear();
uploadentry.ID = (Int64)cmd.ExecuteScalar();
}
trans.Commit();
}
}
}
public void NewCoreDB(string authorName, string rulesSetVersion, int startVersion)
{
SqliteConnection connection = new SqliteConnection ("Data Source=databases/cores/core-"+rulesSetVersion+"-by_"+authorName+".db");
connection.Open ();
IDbCommand command = connection.CreateCommand ();
command.CommandText =
"CREATE TABLE dbInfos(" +
"version varchar(32));";
command.ExecuteNonQuery ();
}
public string proDataCreate()
{
string DatabaseName = "PUB.db3";
string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
string db = System.IO.Path.Combine(documents, DatabaseName);
bool BFile = File.Exists(db);
if (!BFile) { SqliteConnection.CreateFile(db); }
var conn = new SqliteConnection("Data Source=" + db);
string[] commands = proDataCreateFmt();
try
{
foreach (var cmd in commands)
{
var sqlitecmd = conn.CreateCommand();
sqlitecmd.CommandText = cmd;
sqlitecmd.CommandType = CommandType.Text;
conn.Open();
sqlitecmd.ExecuteNonQuery();
conn.Close();
}
if (!BFile) // 初始化
{
var sqlitecmd = conn.CreateCommand();
conn.Open();
sqlitecmd.CommandType = CommandType.Text;
sqlitecmd.CommandText = "INSERT INTO T_PUB_USERID (USERID) VALUES (0);";
sqlitecmd.ExecuteNonQuery();
sqlitecmd.CommandText = "INSERT INTO T_PUB_USERTELNO (USERTELNO) VALUES ('');";
sqlitecmd.ExecuteNonQuery();
sqlitecmd.CommandText = "INSERT INTO T_PUB_USERTYPE (USERTYPE) VALUES (0);";
sqlitecmd.ExecuteNonQuery();
conn.Close();
}
conn.Dispose();
return "";
}
catch (System.Exception sysExc)
{
return "创建数据出错: " + sysExc.Message;
}
}
private void forcaCreateTable(){
using (IDbConnection dbConnection = new SqliteConnection (connectionString)) {
dbConnection.Open ();
using (IDbCommand dbCmd = dbConnection.CreateCommand ()) {
string sqlQuery = String.Format ("CREATE TABLE IF NOT EXISTS forcaGame_table (idpergunta INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , palavra TEXT NOT NULL , dica1 TEXT NOT NULL , dica2 TEXT NOT NULL , dica3 TEXT NOT NULL , dica4 TEXT NOT NULL , tempo_gasto_segundos INTEGER NOT NULL ,idtema INTEGER NOT NULL , tema TEXT NOT NULL , data DATETIME NOT NULL DEFAULT CURRENT_TIME);");
dbCmd.CommandText = sqlQuery;
dbCmd.ExecuteScalar();
dbConnection.Close();
}
}
}
private SqliteCommand createCommandForScore(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Score score)
{
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = sql;
command.Parameters.Add(new SqliteParameter("@AthleteAID", score.AthleteAID));
command.Parameters.Add(new SqliteParameter("@AthleteBID", score.AthleteBID));
command.Parameters.Add(new SqliteParameter("@Date", score.Date));
command.Parameters.Add(new SqliteParameter("@IsUnfinished", (int)(score.IsUnfinished ? 1 : 0)));
command.Parameters.Add(new SqliteParameter("@TimeModified", score.TimeModified));
command.Parameters.Add(new SqliteParameter("@Guid", score.Guid.ToString()));
command.Parameters.Add(new SqliteParameter("@IsDeleted", score.IsDeleted));
command.Parameters.Add(new SqliteParameter("@SportID", score.SportID));
if (score.VenueID == null)
{
command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@VenueID", score.VenueID));
}
if (score.Type1 == null)
{
command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Type1", score.Type1));
}
command.Parameters.Add(new SqliteParameter("@PointsA", score.PointsA));
command.Parameters.Add(new SqliteParameter("@PointsB", score.PointsB));
for (int i = 1; i <= 10; ++i)
{
command.Parameters.Add(new SqliteParameter("@InnerPoints" + i.ToString() + "A", score.InnerPointsA[i - 1]));
command.Parameters.Add(new SqliteParameter("@InnerPoints" + i.ToString() + "B", score.InnerPointsB[i - 1]));
}
command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", score.AthleteBConfirmation));
if (score.ExtraData == null)
{
command.Parameters.Add(new SqliteParameter("@ExtraData", DBNull.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@ExtraData", score.ExtraData));
}
return(command);
}
private void executeNonqueryCommand(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans)
{
var command = conn.CreateCommand();
if (trans != null)
{
command.Transaction = trans;
}
command.CommandText = sql;
command.ExecuteNonQuery();
command.Dispose();
}
private string DBCreate()
{
//<summary><summary/>
//<params><params/>
//<output><output/>
#region DBCreate_declarations
string strPath = Path.GetDirectoryName(Environment.GetCommandLineArgs()[0]);
string strDBPath = strPath + "/" + _localDBName;
var testStr = Path.GetPathRoot(strPath);
#endregion
#region DBCreate_validaton
// Create the database if it doesn't already exist
if (!File.Exists(strDBPath))
{
SqliteConnection.CreateFile(strDBPath);
}
else
{
return(strDBPath); //db exists so exit now and return default path
}
#endregion
#region DBCreate_procedure
// Create connection to the database
var sqliteConn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + strDBPath);
// Set the structure of the database
if (File.Exists(strDBPath))
{
var sqliteCMD = new[] { "CREATE TABLE LocalSettings (Type TEXT, StringValue1 TEXT, StringValue2 TEXT, StringValue3 TEXT, StringValue4 TEXT, StringValue5 TEXT)" };
sqliteConn.Open();
foreach (var cmd in sqliteCMD)
{
using (var c = sqliteConn.CreateCommand())
{
c.CommandText = cmd;
c.CommandType = System.Data.CommandType.Text;
c.ExecuteNonQuery();
}
}
}
sqliteConn.Close();
return(strDBPath);
#endregion
}
private object executeScalarCommand(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans)
{
var command = conn.CreateCommand();
if (trans != null)
{
command.Transaction = trans;
}
command.CommandText = sql;
object result = command.ExecuteScalar();
command.Dispose();
return(result);
}
//建立SQLite 資料庫
void btnSQLite_TouchUpInside(object sender, EventArgs e)
{
var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
var pathToDatabase = Path.Combine(documents, "normal.db");
Mono.Data.Sqlite.SqliteConnection.CreateFile(pathToDatabase);
var msg = "資料庫路徑為: " + pathToDatabase;
//建立Table
var connectionString = String.Format("Data Source={0};Version=3;", pathToDatabase);
using (var conn = new Mono.Data.Sqlite.SqliteConnection(connectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "CREATE TABLE People (PersonID INTEGER PRIMARY KEY AUTOINCREMENT , FirstName ntext, LastName ntext)";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
//新增資料
using (var conn = new Mono.Data.Sqlite.SqliteConnection(connectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "Insert into People(FirstName,LastName) Values('Terry','Lin') ;";
cmd.CommandText += "Insert into People(FirstName,LastName) Values('Ben','Lu') ";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
// 訊息輸出並停用Button
lblmsg.Text = msg;
this.btnSQLite.Enabled = false;
}
public void SetIsDeletedOnResult(int resultID, bool isDeleted)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
var command = conn.CreateCommand();
command.CommandText = "UPDATE [Result] SET TimeModified=@TimeModified,IsDeleted=" + (isDeleted ? "1" : "0") + " WHERE ResultID=" + resultID;
command.Parameters.Add(new SqliteParameter("@TimeModified", DateTimeHelper.GetUtcNow()));
command.ExecuteNonQuery();
command.Dispose();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public Athlete GetAthlete(int athleteID)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
var command = conn.CreateCommand();
command.CommandText = "SELECT * FROM [Athlete] WHERE AthleteID=" + athleteID;
var reader = command.ExecuteReader();
if (!reader.Read())
{
throw new Exception("no records for this athleteID: " + athleteID);
}
Athlete athlete = new Athlete();
athlete.AthleteID = int.Parse(reader["AthleteID"].ToString());
athlete.UserName = reader["EMail"].ToString();
athlete.Name = reader["Name"].ToString();
athlete.Gender = (int)reader["Gender"];
var country = reader["Country"];
if (country != null && country != DBNull.Value)
{
athlete.Country = country.ToString();
}
athlete.MetroID = (int)reader["MetroID"];
athlete.DOB = null;
var dob = reader["DOB"];
if (dob != DBNull.Value)
{
athlete.DOB = (DateTime)dob;
}
athlete.TimeCreated = (DateTime)reader["TimeCreated"];
athlete.TimeModified = (DateTime)reader["TimeModified"];
var facebookId = reader["FacebookId"];
if (facebookId != DBNull.Value)
{
athlete.FacebookId = (string)facebookId;
}
var picture = reader["Picture"];
if (picture != DBNull.Value)
{
athlete.Picture = (string)picture;
}
var snookerAbout = reader["SnookerAbout"];
if (snookerAbout != DBNull.Value)
{
athlete.SnookerAbout = (string)snookerAbout;
}
return(athlete);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void UpdateAthlete(Athlete athlete)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
if (athlete.Name == null)
{
athlete.Name = "";
}
if (athlete.UserName == null)
{
athlete.UserName = "";
}
var command = conn.CreateCommand();
command.CommandText =
"UPDATE [Athlete] SET " +
" Name=@Name,EMail=@EMail,Gender=" + ((int)athlete.Gender).ToString() + ",DOB=@DOB,Picture=@Picture,FacebookId=@FacebookId,TimeModified=@TimeModified" +
",Country=@Country,MetroID=" + athlete.MetroID +
",SnookerAbout=@SnookerAbout" +
" WHERE AthleteID=" + athlete.AthleteID;
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@Name", Value = athlete.Name
});
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@EMail", Value = athlete.UserName
});
if (athlete.Country == null)
{
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@Country", Value = DBNull.Value
});
}
else
{
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@Country", Value = athlete.Country
});
}
if (athlete.DOB == null)
{
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@DOB", Value = DBNull.Value
});
}
else
{
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@DOB", Value = athlete.DOB.Value
});
}
if (string.IsNullOrEmpty(athlete.FacebookId))
{
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@FacebookId", Value = DBNull.Value
});
}
else
{
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@FacebookId", Value = athlete.FacebookId
});
}
if (string.IsNullOrEmpty(athlete.Picture))
{
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@Picture", Value = DBNull.Value
});
}
else
{
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@Picture", Value = athlete.Picture
});
}
if (athlete.SnookerAbout == null)
{
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@SnookerAbout", Value = DBNull.Value
});
}
else
{
command.Parameters.Add(new SqliteParameter()
{
ParameterName = "@SnookerAbout", Value = athlete.SnookerAbout
});
}
command.Parameters.Add(new SqliteParameter("@TimeModified", athlete.TimeModified));
command.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void DataBaseRead_Chart(String query) //DB 읽어오기 - 인자로 쿼리문을 받는다.
{
IDbConnection dbConnection = new SqliteConnection(GetDBFilePath());
dbConnection.Open(); // DB 열기
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = query; // 쿼리 입력
IDataReader dataReader = dbCommand.ExecuteReader(); // 쿼리 실행
while (dataReader.Read()) // 쿼리로 돌아온 레코드 읽기
{
string date = dataReader.GetString(0).Substring(0, 13);
float maxRotation = (float)dataReader.GetDouble(3);
if (Dic.Keys.Count == 0)
{
Dic.Add(date, maxRotation);
}
if (Dic.ContainsKey(date).Equals(true)) // date값이 이미 키값으로 들어가있다면 Add 하지 않고 뒤에 벨류값만 비교함.
{
if (Dic[date] < maxRotation)
{
Dic[date] = maxRotation;
}
}
else
{
Dic.Add(date, maxRotation);
}
Debug.Log("date : " + date + ", Dic[date] : " + Dic[date]);
}
dataReader.Dispose(); // 생성순서와 반대로 닫아줍니다.
dataReader = null;
dbCommand.Dispose();
dbCommand = null;
// DB에는 1개의 쓰레드만이 접근할 수 있고 동시에 접근시 에러가 발생한다. 그래서 Open과 Close는 같이 써야한다.
dbConnection.Close();
dbConnection = null;
// 디비 확인 코드
foreach (KeyValuePair <string, float> item in Dic)
{
Debug.Log(item.Key + " : " + item.Value);
if (item.Value != 0)
{
if (item.Key.Substring(10, 1) == "0")
{
series1Data2.Add(item.Key.Substring(7, 1) + "/" + item.Key.Substring(11, 1) + ", " + item.Value.ToString("N0"));
}
else
{
series1Data2.Add(item.Key.Substring(7, 1) + "/" + item.Key.Substring(10, 2) + ", " + item.Value.ToString("N0"));
}
}
}
}
private List <Result> getResults(string sql)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
var command = conn.CreateCommand();
command.CommandText = sql;
var reader = command.ExecuteReader();
List <Result> results = new List <Result>();
while (reader.Read())
{
Result result = new Result();
result.AthleteID = (int)reader["AthleteID"];
result.ResultID = int.Parse(reader["ResultID"].ToString());
result.ResultTypeID = (int)reader["ResultTypeID"];
result.Date = null;
if (reader["Date"] != DBNull.Value)
{
result.Date = (DateTime)reader["Date"];
}
result.Time = null;
if (reader["Time"] != DBNull.Value)
{
result.Time = (double)reader["Time"];
}
result.Count = null;
if (reader["Count"] != DBNull.Value)
{
result.Count = (int)reader["Count"];
}
result.Count2 = null;
if (reader["Count2"] != DBNull.Value)
{
result.Count2 = (int)reader["Count2"];
}
result.Notes = (string)reader["Notes"];
result.TimeModified = (DateTime)reader["TimeModified"];
result.Guid = Guid.Parse(reader["Guid"].ToString());
string isDeleted = reader["IsDeleted"].ToString().ToLower();
if (isDeleted == "true")
{
result.IsDeleted = true;
}
else if (isDeleted == "false")
{
result.IsDeleted = false;
}
else
{
result.IsDeleted = int.Parse(isDeleted) > 0;
}
if (reader["OpponentAthleteID"] != DBNull.Value)
{
result.OpponentAthleteID = (int)reader["OpponentAthleteID"];
}
result.OpponentConfirmation = (int)reader["OpponentConfirmation"];
if (reader["Type1"] != DBNull.Value)
{
result.Type1 = (int)reader["Type1"];
}
if (reader["Details1"] != DBNull.Value)
{
result.Details1 = (string)reader["Details1"];
}
if (reader["VenueID"] != DBNull.Value)
{
result.VenueID = (int)reader["VenueID"];
}
results.Add(result);
}
return(results);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void SaveSecurity(int myAthleteID, bool userWantsToBeGuest, DateTime timeAthleteCreated)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
int myOldAthleteID = this.getMyAthleteID(conn);
bool myOldAthleteRecordAlreadyExists = int.Parse(this.executeScalarCommand("SELECT COUNT(*) FROM Athlete WHERE AthleteID=" + myOldAthleteID, conn, null).ToString()) == 1;
bool myNewAthleteRecordAlreadyExists = int.Parse(this.executeScalarCommand("SELECT COUNT(*) FROM Athlete WHERE AthleteID=" + myAthleteID, conn, null).ToString()) == 1;
trans = conn.BeginTransaction();
// update Singular row
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "UPDATE Singular SET MyAthleteID=" + myAthleteID + ", UserWantsToBeGuest=" + (userWantsToBeGuest ? "1" : "0");
//command.CommandText = "UPDATE Singular SET AccessToken=@AccessToken, MyAthleteID=" + myAthleteID + ", UserWantsToBeGuest=" + (userWantsToBeGuest ? "1" : "0");
//command.Parameters.Add(new SqliteParameter() { ParameterName = "@AccessToken", Value = Crypto.Encrypt(accessToken, "$EFK#$RF!#$#SDFwefasdWE@") });
command.ExecuteNonQuery();
command.Dispose();
if (myAthleteID != myOldAthleteID)
{
// create Athlete row
if (myNewAthleteRecordAlreadyExists == false)
{
createAhlete(myAthleteID, timeAthleteCreated, conn, trans);
}
// move results and scores
command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "UPDATE Result SET AthleteID=" + myAthleteID + " WHERE AthleteID=" + myOldAthleteID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Score SET AthleteAID=" + myAthleteID + " WHERE AthleteAID=" + myOldAthleteID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Score SET AthleteBID=" + myAthleteID + " WHERE AthleteBID=" + myOldAthleteID;
command.ExecuteNonQuery();
if (myOldAthleteRecordAlreadyExists == true)
{
this.executeNonqueryCommand("DELETE FROM [Athlete] WHERE AthleteID=" + myOldAthleteID, conn, trans);
}
}
trans.Commit();
}
catch (Exception exc)
{
if (trans != null)
{
trans.Rollback();
}
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
private void addResult(Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Result result)
{
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = @"INSERT INTO [Result] ([AthleteID],[ResultTypeID],[Time],[Distance],[Count],[Count2],[Date],[Notes],[TimeModified],[Guid],[IsDeleted],[VenueID],[OpponentAthleteID],[Type1],[Details1],[OpponentConfirmation])
VALUES (@AthleteID,@ResultTypeID,@Time,@Distance,@Count,@Count2,@Date,@Notes,@TimeModified,@Guid,@IsDeleted,@VenueID,@OpponentAthleteID,@Type1,@Details1,@OpponentConfirmation)";
command.Parameters.Add(new SqliteParameter("@AthleteID", result.AthleteID));
command.Parameters.Add(new SqliteParameter("@ResultTypeID", result.ResultTypeID));
if (result.Time != null)
{
command.Parameters.Add(new SqliteParameter("@Time", result.Time.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Time", DBNull.Value));
}
if (result.Distance != null)
{
command.Parameters.Add(new SqliteParameter("@Distance", result.Distance.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Distance", DBNull.Value));
}
if (result.Count != null)
{
command.Parameters.Add(new SqliteParameter("@Count", result.Count.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Count", DBNull.Value));
}
if (result.Count2 != null)
{
command.Parameters.Add(new SqliteParameter("@Count2", result.Count2.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Count2", DBNull.Value));
}
if (result.Date != null)
{
command.Parameters.Add(new SqliteParameter("@Date", result.Date.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Date", DBNull.Value));
}
command.Parameters.Add(new SqliteParameter("@Notes", result.Notes ?? ""));
command.Parameters.Add(new SqliteParameter("@TimeModified", result.TimeModified));
command.Parameters.Add(new SqliteParameter("@Guid", result.Guid.ToString()));
command.Parameters.Add(new SqliteParameter("@IsDeleted", result.IsDeleted));
if (result.VenueID != null)
{
command.Parameters.Add(new SqliteParameter("@VenueID", result.VenueID.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value));
}
if (result.OpponentAthleteID != null)
{
command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", result.OpponentAthleteID.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", DBNull.Value));
}
if (result.Type1 != null)
{
command.Parameters.Add(new SqliteParameter("@Type1", result.Type1.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value));
}
if (result.Details1 != null)
{
command.Parameters.Add(new SqliteParameter("@Details1", result.Details1));
}
else
{
command.Parameters.Add(new SqliteParameter("@Details1", DBNull.Value));
}
command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", result.OpponentConfirmation));
command.ExecuteNonQuery();
command.Dispose();
}
private void updateResult(Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Result result)
{
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = @"UPDATE [Result] SET [AthleteID]=@AthleteID,[ResultTypeID]=@ResultTypeID,[Time]=@Time,[Distance]=@Distance,[Count]=@Count,[Count2]=@Count2,[Date]=@Date,[Notes]=@Notes,[TimeModified]=@TimeModified,[Guid]=@Guid,[VenueID]=@VenueID,[OpponentAthleteID]=@OpponentAthleteID,[Type1]=@Type1,[Details1]=@Details1,[OpponentConfirmation]=@OpponentConfirmation WHERE ResultID=" + result.ResultID;
command.Parameters.Add(new SqliteParameter("@AthleteID", result.AthleteID));
command.Parameters.Add(new SqliteParameter("@ResultTypeID", result.ResultTypeID));
if (result.Time != null)
{
command.Parameters.Add(new SqliteParameter("@Time", result.Time.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Time", DBNull.Value));
}
if (result.Distance != null)
{
command.Parameters.Add(new SqliteParameter("@Distance", result.Distance.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Distance", DBNull.Value));
}
if (result.Count != null)
{
command.Parameters.Add(new SqliteParameter("@Count", result.Count.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Count", DBNull.Value));
}
if (result.Count2 != null)
{
command.Parameters.Add(new SqliteParameter("@Count2", result.Count2.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Count2", DBNull.Value));
}
if (result.Date != null)
{
command.Parameters.Add(new SqliteParameter("@Date", result.Date.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Date", DBNull.Value));
}
command.Parameters.Add(new SqliteParameter("@Notes", result.Notes ?? ""));
command.Parameters.Add(new SqliteParameter("@TimeModified", result.TimeModified));
command.Parameters.Add(new SqliteParameter("@Guid", result.Guid.ToString()));
if (result.VenueID != null)
{
command.Parameters.Add(new SqliteParameter("@VenueID", result.VenueID.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value));
}
if (result.OpponentAthleteID != null)
{
command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", result.OpponentAthleteID.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", DBNull.Value));
}
if (result.Type1 != null)
{
command.Parameters.Add(new SqliteParameter("@Type1", result.Type1.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value));
}
if (result.Details1 != null)
{
command.Parameters.Add(new SqliteParameter("@Details1", result.Details1));
}
else
{
command.Parameters.Add(new SqliteParameter("@Details1", DBNull.Value));
}
command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", result.OpponentConfirmation));
command.ExecuteNonQuery();
command.Dispose();
}
private List <Score> getScores(string sql)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
var command = conn.CreateCommand();
command.CommandText = sql;
var reader = command.ExecuteReader();
List <Score> scores = new List <Score>();
while (reader.Read())
{
Score score = new Score();
score.ScoreID = int.Parse(reader["ScoreID"].ToString());
score.AthleteAID = int.Parse(reader["AthleteAID"].ToString());
score.AthleteBID = int.Parse(reader["AthleteBID"].ToString());
score.Date = (DateTime)reader["Date"];
score.IsUnfinished = ((int)reader["IsUnfinished"]) > 0;
score.TimeModified = (DateTime)reader["TimeModified"];
string guid = reader["Guid"].ToString();
score.Guid = Guid.Parse(guid);
string isDeleted = reader["IsDeleted"].ToString().ToLower();
if (isDeleted == "true")
{
score.IsDeleted = true;
}
else if (isDeleted == "false")
{
score.IsDeleted = false;
}
else
{
score.IsDeleted = int.Parse(isDeleted) > 0;
}
score.SportID = int.Parse(reader["SportID"].ToString());
if (reader["VenueID"] != DBNull.Value)
{
score.VenueID = int.Parse(reader["VenueID"].ToString());
}
if (reader["Type1"] != DBNull.Value)
{
score.Type1 = int.Parse(reader["Type1"].ToString());
}
score.AthleteBConfirmation = int.Parse(reader["OpponentConfirmation"].ToString());
score.PointsA = int.Parse(reader["PointsA"].ToString());
score.PointsB = int.Parse(reader["PointsB"].ToString());
score.InnerPoints1A = int.Parse(reader["InnerPoints1A"].ToString());
score.InnerPoints1B = int.Parse(reader["InnerPoints1B"].ToString());
score.InnerPoints2A = int.Parse(reader["InnerPoints2A"].ToString());
score.InnerPoints2B = int.Parse(reader["InnerPoints2B"].ToString());
score.InnerPoints3A = int.Parse(reader["InnerPoints3A"].ToString());
score.InnerPoints3B = int.Parse(reader["InnerPoints3B"].ToString());
score.InnerPoints4A = int.Parse(reader["InnerPoints4A"].ToString());
score.InnerPoints4B = int.Parse(reader["InnerPoints4B"].ToString());
score.InnerPoints5A = int.Parse(reader["InnerPoints5A"].ToString());
score.InnerPoints5B = int.Parse(reader["InnerPoints5B"].ToString());
score.InnerPoints6A = int.Parse(reader["InnerPoints6A"].ToString());
score.InnerPoints6B = int.Parse(reader["InnerPoints6B"].ToString());
score.InnerPoints7A = int.Parse(reader["InnerPoints7A"].ToString());
score.InnerPoints7B = int.Parse(reader["InnerPoints7B"].ToString());
score.InnerPoints8A = int.Parse(reader["InnerPoints8A"].ToString());
score.InnerPoints8B = int.Parse(reader["InnerPoints8B"].ToString());
score.InnerPoints9A = int.Parse(reader["InnerPoints9A"].ToString());
score.InnerPoints9B = int.Parse(reader["InnerPoints9B"].ToString());
score.InnerPoints10A = int.Parse(reader["InnerPoints10A"].ToString());
score.InnerPoints10B = int.Parse(reader["InnerPoints10B"].ToString());
object extraData = reader["ExtraData"];
if (extraData != DBNull.Value)
{
score.ExtraData = (string)extraData;
}
scores.Add(score);
}
return(scores);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
