/// <summary>
/// Добавя категория
/// </summary>
private void btnIN_Click(object sender, EventArgs e)
{
if ((cbTNCategoriesSub.SelectedValue == null)
|| cbTNCategoriesSub.SelectedValue.ToString().Equals(""))
{ return; }
if (dgvCatOUT.CurrentRow == null)
{ return; }
Int32 iCategoryID = Convert.ToInt32(dgvCatOUT.CurrentRow.Cells["gcCategoryID_OUT"].Value);
try
{
using (SqliteConnection sqlCnnctn = new SqliteConnection())
{
sqlCnnctn.ConnectionString = _ConnectionString;
sqlCnnctn.Open();
using (SqliteCommand sqlCmmnd = new SqliteCommand())
{
sqlCmmnd.Connection = sqlCnnctn;
sqlCmmnd.CommandText =
"UPDATE x_news_nweb " +
" SET nweb_category_id = " + cbTNCategoriesSub.SelectedValue.ToString() +
" WHERE news_category_id = " + iCategoryID.ToString();
sqlCmmnd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cbTNCategoriesSub_SelectedIndexChanged(null, null);
}
public void CreateCrypted(string databaseFile)
{
try
{
// Check if database already exists
if (!File.Exists (databaseFile))
{
// Create the database
SqliteConnection.CreateFile (databaseFile);
// Connect to the database
// using (SqliteConnection sqlCon = GetConnection (String.Format ("Data Source = {0};", databaseFile),"haluk"))
using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0}", databaseFile)))
{
sqlCon.SetPassword("haluk");
sqlCon.Open ();
// Create a table
using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
{
sqlCom.CommandText = "CREATE TABLE Personel (ID INTEGER PRIMARY KEY, UserName VARCHAR(20), Password VARCHAR(20))";
//veri Ekleme
//Update
// sqlCom.CommandText = "UPDATE Customers SET FirstName= 'Haluk' WHERE LastName = @lastName";
// sqlCom.Parameters.Add(new SqliteParameter("@lastName","Haluky"));
sqlCom.ExecuteNonQuery ();
Console.WriteLine(sqlCom.ExecuteNonQuery());
}
//end using sqlCom
sqlCon.Close ();
}
//end using sqlCon
this.durumGostericiT.Text = "Database hazır!";
}else
{
this.durumGostericiT.Text = "Database Mevcut!";
}//end if else
} catch (Exception ex)
{
this.durumGostericiT.Text = String.Format ("Sqlite error: {0}", ex.Message);
}//end try catch
}
public static int Fill(string commandText, SqliteConnection connection, DataTable dataTable)
{
int res = 0;
using (SqliteCommand command = new SqliteCommand())
{
command.Connection = connection;
command.CommandText = commandText;
using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(command))
{
res = sqlDAdapter.Fill(dataTable);
}
}
return res;
}
public AuthenticationData Get(UUID principalID)
{
AuthenticationData ret = new AuthenticationData();
ret.Data = new Dictionary<string, object>();
SqliteCommand cmd = new SqliteCommand("select * from `" + m_Realm + "` where UUID = :PrincipalID");
cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString()));
IDataReader result = ExecuteReader(cmd, m_Connection);
try
{
if (result.Read())
{
ret.PrincipalID = principalID;
if (m_ColumnNames == null)
{
m_ColumnNames = new List<string>();
DataTable schemaTable = result.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
m_ColumnNames.Add(row["ColumnName"].ToString());
}
foreach (string s in m_ColumnNames)
{
if (s == "UUID")
continue;
ret.Data[s] = result[s].ToString();
}
return ret;
}
else
{
return null;
}
}
catch
{
}
finally
{
//CloseCommand(cmd);
}
return null;
}
protected IDataReader ExecuteReader(SqliteCommand cmd, SqliteConnection connection)
{
lock (connection)
{
//SqliteConnection newConnection =
// (SqliteConnection)((ICloneable)connection).Clone();
//newConnection.Open();
//cmd.Connection = newConnection;
cmd.Connection = connection;
//Console.WriteLine("XXX " + cmd.CommandText);
return cmd.ExecuteReader();
}
}
public void LoadNews()
{
try
{
using (SqliteConnection sqlCnnctn = new SqliteConnection())
{
sqlCnnctn.ConnectionString = _ConnectionString;
sqlCnnctn.Open();
//
using (SqliteCommand sqlCmmndNews = new SqliteCommand())
{
using (SqliteCommand sqlCmmndEncl = new SqliteCommand())
{
sqlCmmndNews.Connection = sqlCnnctn;
sqlCmmndEncl.Connection = sqlCnnctn;
// сваля и зарежда новините
foreach (NewsSource ns in _PNews.NSource)
{
try
{
OnLog(LogMessageType.Event, ns.Title);
//
LoadNewsRSS(sqlCmmndNews, sqlCmmndEncl, ns);
//
OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg3_NewsChargedHandle);
TmpNewsRepair(sqlCmmndNews);
//
OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg4_LoadingNews);
TmpNewsTransfer(sqlCmmndNews, sqlCmmndEncl);
//
OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg5_Done);
}
catch (Exception ex)
{
OnLog(LogMessageType.Error, "Exception /Load:" + ns.URL + "/: " + ex.Message);
}
}
}
}
}
}
catch (Exception ex)
{
OnLog(LogMessageType.Error, "Exception: " + ex.Message);
}
}
internal SqliteDataReader (SqliteCommand cmd, csSQLite.Vdbe pVm, int version)
{
command = cmd;
rows = new ArrayList ();
column_names_sens = new Hashtable ();
#if NET_2_0
column_names_insens = new Hashtable (StringComparer.InvariantCultureIgnoreCase);
#else
column_names_insens = new Hashtable (CaseInsensitiveHashCodeProvider.DefaultInvariant,
CaseInsensitiveComparer.DefaultInvariant);
#endif
closed = false;
current_row = -1;
reading = true;
ReadpVm (pVm, version, cmd);
ReadingDone ();
}
//////////////////////////////////////////////////////////////
//
// All non queries are funneled through one connection
// to increase performance a little
//
protected int ExecuteNonQuery(SqliteCommand cmd, SqliteConnection connection)
{
lock (connection)
{
/*
SqliteConnection newConnection =
(SqliteConnection)((ICloneable)connection).Clone();
newConnection.Open();
cmd.Connection = newConnection;
*/
cmd.Connection = connection;
//Console.WriteLine("XXX " + cmd.CommandText);
return cmd.ExecuteNonQuery();
}
}
public static int Fill(string commandText, string connectionString, DataTable dataTable)
{
int res = 0;
using (SqliteConnection connection = dUtils.OpenConnection(connectionString))
{
using (SqliteCommand command = new SqliteCommand())
{
command.Connection = connection;
command.CommandText = commandText;
using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(command))
{
res = sqlDAdapter.Fill(dataTable);
}
}
connection.Close();
}
return res;
}
/// <summary>
/// Изтрива невалидни записи
/// </summary>
private void btnDeleteInvalidEntries_Click(object sender, EventArgs e)
{
try
{
listBox1.Items.Clear();
SetMessage("Старт: Изтрива невалидни записи");
using (SqliteConnection sqlCnnctn = new SqliteConnection())
{
sqlCnnctn.ConnectionString = _ConnectionString;
sqlCnnctn.Open();
using (SqliteCommand sqlCmmnd = new SqliteCommand())
{
sqlCmmnd.Connection = sqlCnnctn;
// Новини без картинки
sqlCmmnd.CommandText =
"DELETE FROM news " +
" WHERE news_id NOT IN (SELECT DISTINCT news_id FROM enclosure ) ";
Int32 iRows = sqlCmmnd.ExecuteNonQuery();
SetMessage("Изтити новини без картинки: " + iRows.ToString());
// Картинки без новини
sqlCmmnd.CommandText =
"DELETE FROM enclosure " +
" WHERE news_id NOT IN (SELECT news_id FROM news) ";
iRows = sqlCmmnd.ExecuteNonQuery();
SetMessage("Изтити картинки без новини: " + iRows.ToString());
}
sqlCnnctn.Close();
}
SetMessage("Край");
}
catch (Exception ex)
{
SetMessage(ex.Message);
}
}
public override IEnumerable<INodeFactory> GetNodeChildren( IContext context )
{
var filter = context.Filter;
var sql = "SELECT * FROM " + Name;
if( ! String.IsNullOrEmpty( filter ) )
{
sql += " WHERE ( " + filter + " )";
}
var command = new SqliteCommand(sql , _context.Connection);
var table = new DataTable( Name );
context.WriteVerbose( sql );
using( var data = new SqliteDataAdapter(command) )
{
data.MissingSchemaAction = MissingSchemaAction.AddWithKey;
data.Fill(table);
}
foreach( DataRow row in table.Rows )
{
yield return new SQLiteRecordNodeFactory( _context, row);
}
}
private void btn_Save_Click(object sender, EventArgs e)
{
try
{
using (SqliteConnection sqlCnnctn = new SqliteConnection())
{
sqlCnnctn.ConnectionString = _ConnectionString;
sqlCnnctn.Open();
using (SqliteCommand sqlCmmnd = new SqliteCommand())
{
sqlCmmnd.Connection = sqlCnnctn;
sqlCmmnd.CommandText =
"UPDATE t_news_category " +
" SET n_dscr = @n_dscr " +
" WHERE news_category_id = " + _CatID.ToString();
sqlCmmnd.Parameters.Add("@n_dscr", DbType.String).Value = tbx_NDscr.Text.Trim();
using (SqliteDataReader sqlDReader = sqlCmmnd.ExecuteReader())
{
if (sqlDReader.HasRows && sqlDReader.Read())
{
lbl_NCategory.Text = sqlDReader["news_category"].ToString();
lbl_NSource.Text = sqlDReader["n_source"].ToString();
tbx_NDscr.Text = sqlDReader["n_dscr"].ToString();
}
sqlDReader.Close();
}
}
sqlCnnctn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
Close();
}
public void Sync(IInputInvoiceDocumentService InputInvoiceDocumentService, Action <int, int> callback = null)
{
try
{
SyncInputInvoiceDocumentRequest request = new SyncInputInvoiceDocumentRequest();
request.CompanyId = MainWindow.CurrentCompanyId;
request.LastUpdatedAt = GetLastUpdatedAt(MainWindow.CurrentCompanyId);
int toSync = 0;
int syncedItems = 0;
InputInvoiceDocumentListResponse response = InputInvoiceDocumentService.Sync(request);
if (response.Success)
{
toSync = response?.InputInvoiceDocuments?.Count ?? 0;
List <InputInvoiceDocumentViewModel> items = response.InputInvoiceDocuments;
using (SqliteConnection db = new SqliteConnection("Filename=SirmiumERPGFC.db"))
{
db.Open();
using (var transaction = db.BeginTransaction())
{
SqliteCommand deleteCommand = db.CreateCommand();
deleteCommand.CommandText = "DELETE FROM InputInvoiceDocuments WHERE Identifier = @Identifier";
SqliteCommand insertCommand = db.CreateCommand();
insertCommand.CommandText = SqlCommandInsertPart;
foreach (var item in items)
{
deleteCommand.Parameters.AddWithValue("@Identifier", item.Identifier);
deleteCommand.ExecuteNonQuery();
deleteCommand.Parameters.Clear();
if (item.IsActive)
{
item.IsSynced = true;
insertCommand = AddCreateParameters(insertCommand, item);
insertCommand.ExecuteNonQuery();
insertCommand.Parameters.Clear();
syncedItems++;
callback?.Invoke(syncedItems, toSync);
}
}
transaction.Commit();
}
db.Close();
}
}
else
{
throw new Exception(response.Message);
}
}
catch (Exception ex)
{
MainWindow.ErrorMessage = ex.Message;
}
}
public static void TestPacienteUpdate()
{
using (var conn = new SqliteConnection(GlobalController.path))
{
conn.Open();
Pessoa.Insert("fake name1", "m", "1995-01-01", "6198732711", null);
Pessoa.Insert("fake name2", "m", "1995-01-02", "6198732712", null);
Paciente.Insert(1, "observacao teste");
Paciente.Insert(2, null);
Paciente.Update(1, 2, null);
Paciente.Update(2, 1, "teste observacao");
var check = "SELECT * FROM PACIENTE;";
var id = 0;
var result = "";
int i = 1;
using (var cmd = new SqliteCommand(check, conn))
{
using (IDataReader reader = cmd.ExecuteReader())
{
try
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
id = reader.GetInt32(0);
Assert.AreEqual(id, i);
}
if (!reader.IsDBNull(1))
{
id = reader.GetInt32(1);
Assert.AreEqual(id, 3 - i);
}
if (!reader.IsDBNull(2))
{
result = reader.GetString(2);
Assert.AreEqual(result, "teste observacao");
}
i++;
}
}
finally
{
reader.Dispose();
reader.Close();
}
}
cmd.Dispose();
}
conn.Dispose();
conn.Close();
}
}
private void ThenTheCommandIsReplicatedToAllStateMachines(UpdateFileConfiguration expecteds)
{
//dirty sleep to give a chance to replicate...
var stopwatch = Stopwatch.StartNew();
while (stopwatch.ElapsedMilliseconds < 2000)
{
}
bool CommandCalledOnAllStateMachines()
{
try
{
var passed = 0;
foreach (var peer in _peers.Peers)
{
var path = $"{peer.HostAndPort.Replace("/","").Replace(":","")}.db";
using (var connection = new SqliteConnection($"Data Source={path};"))
{
connection.Open();
var sql = @"select count(id) from logs";
using (var command = new SqliteCommand(sql, connection))
{
var index = Convert.ToInt32(command.ExecuteScalar());
index.ShouldBe(1);
}
}
_httpClientForAssertions.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", _token.AccessToken);
var result = _httpClientForAssertions.GetAsync($"{peer.HostAndPort}/administration/configuration").Result;
var json = result.Content.ReadAsStringAsync().Result;
var response = JsonConvert.DeserializeObject <FileConfiguration>(json, new JsonSerializerSettings {
TypeNameHandling = TypeNameHandling.All
});
response.GlobalConfiguration.RequestIdKey.ShouldBe(expecteds.Configuration.GlobalConfiguration.RequestIdKey);
response.GlobalConfiguration.ServiceDiscoveryProvider.Host.ShouldBe(expecteds.Configuration.GlobalConfiguration.ServiceDiscoveryProvider.Host);
response.GlobalConfiguration.ServiceDiscoveryProvider.Port.ShouldBe(expecteds.Configuration.GlobalConfiguration.ServiceDiscoveryProvider.Port);
for (var i = 0; i < response.ReRoutes.Count; i++)
{
for (var j = 0; j < response.ReRoutes[i].DownstreamHostAndPorts.Count; j++)
{
var res = response.ReRoutes[i].DownstreamHostAndPorts[j];
var expected = expecteds.Configuration.ReRoutes[i].DownstreamHostAndPorts[j];
res.Host.ShouldBe(expected.Host);
res.Port.ShouldBe(expected.Port);
}
response.ReRoutes[i].DownstreamPathTemplate.ShouldBe(expecteds.Configuration.ReRoutes[i].DownstreamPathTemplate);
response.ReRoutes[i].DownstreamScheme.ShouldBe(expecteds.Configuration.ReRoutes[i].DownstreamScheme);
response.ReRoutes[i].UpstreamPathTemplate.ShouldBe(expecteds.Configuration.ReRoutes[i].UpstreamPathTemplate);
response.ReRoutes[i].UpstreamHttpMethod.ShouldBe(expecteds.Configuration.ReRoutes[i].UpstreamHttpMethod);
}
passed++;
}
return(passed == 5);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
var commandOnAllStateMachines = WaitFor(20000).Until(() => CommandCalledOnAllStateMachines());
commandOnAllStateMachines.ShouldBeTrue();
}
//Transaction Update call dbcmdTr will be used
public static void Update(bool dbconOpened, EncoderSQL es, SqliteCommand dbcmdTr)
{
update(dbconOpened, es, dbcmdTr);
}
/// <summary>
/// Проверява за нови категории
/// </summary>
private bool HasNewCategory(SqliteCommand aSQLCmmnd)
{
bool bRes = false;
//
aSQLCmmnd.CommandText =
"SELECT news_category " +
" FROM tmp_news " +
" WHERE ( ifnull(news_category_id, 0) = 0 ); ";
using (SqliteDataReader dr = aSQLCmmnd.ExecuteReader())
{
bRes = dr.HasRows;
dr.Close();
}
return bRes;
}
public Boolean GetDayBoundaries(Int32 threadId, Int32 day, out DateTime startTime,
out DateTime endTime, out Int32 endPost)
{
startTime = DateTime.MinValue;
endTime = DateTime.Now;
endPost = 0;
String sql =
@"
SELECT starttime, endtime FROM Day
WHERE (threadid = @p1) AND (day = @p2)
LIMIT 1;
";
Stopwatch watch = new Stopwatch();
watch.Start();
using (SqliteConnection dbRead = new SqliteConnection(_connect))
{
dbRead.Open();
using (SqliteCommand cmd = new SqliteCommand(sql, dbRead))
{
cmd.Parameters.Add(new SqliteParameter("@p1", threadId));
cmd.Parameters.Add(new SqliteParameter("@p2", day));
using (SqliteDataReader r = cmd.ExecuteReader())
{
if (r.Read())
{
startTime = DateTime.SpecifyKind(r.GetDateTime(0), DateTimeKind.Local);
endTime = DateTime.SpecifyKind(r.GetDateTime(1), DateTimeKind.Local);
}
else
{
return(false);
}
}
}
}
watch.Stop();
//Trace.TraceInformation("after ReadDayBoundaries {0}", watch.Elapsed.ToString());
String sqlTime =
@"SELECT postnumber
FROM Post
WHERE (Post.threadid = @p2) AND (Post.posttime <= @p3) AND (Post.posttime >= @p1)
ORDER BY postid DESC LIMIT 1";
watch.Reset();
watch.Start();
using (SqliteConnection dbRead = new SqliteConnection(_connect))
{
dbRead.Open();
using (SqliteCommand cmd = new SqliteCommand(sqlTime, dbRead))
{
cmd.Parameters.Add(new SqliteParameter("@p1", startTime));
cmd.Parameters.Add(new SqliteParameter("@p2", threadId));
SqliteParameter pEndTime = new SqliteParameter("@p3", System.Data.DbType.DateTime);
pEndTime.Value = endTime.ToUniversalTime();
cmd.Parameters.Add(pEndTime);
using (SqliteDataReader r = cmd.ExecuteReader())
{
if (r.Read())
{
endPost = r.GetInt32(0);
}
}
}
}
watch.Stop();
//Trace.TraceInformation("after get EndPost {0}", watch.Elapsed.ToString());
return(true);
}
public IEnumerable <VoterInfo> GetVotes(Int32 threadId, DateTime startTime, DateTime endTime, object game)
{
String sql =
@"
SELECT GameRole.roleid, Player.posterid, Poster.postername,
(SELECT COUNT(*)
FROM Post WHERE
(GameRole.threadid = @p2)
AND ((Player.endtime IS NULL) OR (player.endtime > @p4))
AND (Post.threadid = GameRole.threadid)
AND (Post.posterid = Player.posterid)
AND (Post.posttime >= @p4)
AND (Post.posttime <= @p3)
) AS postcount,
(SELECT MAX(Post.postid)
FROM Bolded, Post WHERE
(GameRole.threadid = @p2)
AND ((Player.endtime IS NULL) OR (player.endtime > @p4))
AND (Post.threadid = GameRole.threadid)
AND (Bolded.postid = Post.postid)
AND (Post.posterid = Player.posterid)
AND (Post.posttime >= @p4)
AND (Post.posttime <= @p3)
AND (Bolded.ignore = 0)
) AS bolded
FROM GameRole
JOIN Player ON (GameRole.roleid = Player.roleid)
JOIN Poster ON (Poster.posterid = Player.posterid)
WHERE (GameRole.threadid = @p2)
AND ((Player.endtime IS NULL) OR (player.endtime > @p4))
GROUP BY Poster.postername
;
";
SortableBindingList <VoterInfo> voters = new SortableBindingList <VoterInfo>();
Stopwatch watch = new Stopwatch();
watch.Start();
using (SqliteConnection dbRead = new SqliteConnection(_connect))
{
dbRead.Open();
using (SqliteCommand cmd = new SqliteCommand(sql, dbRead))
{
cmd.Parameters.Add(new SqliteParameter("@p2", threadId));
SqliteParameter pEndTime = new SqliteParameter("@p3", System.Data.DbType.DateTime);
pEndTime.Value = endTime.ToUniversalTime();
cmd.Parameters.Add(pEndTime);
SqliteParameter pStartTime = new SqliteParameter("@p4", System.Data.DbType.DateTime);
pStartTime.Value = startTime.ToUniversalTime();
cmd.Parameters.Add(pStartTime);
using (SqliteDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
Int32 roleId = r.GetInt32(0);
Int32 playerId = r.GetInt32(1);
String name = r.GetString(2);
Int32 count = r.GetInt32(3);
Int32? boldedPost = null;
if (!r.IsDBNull(4))
{
boldedPost = r.GetInt32(4);
}
VoterInfo v = new VoterInfo(name, count, boldedPost);
voters.Add(v);
}
}
}
sql =
@"
SELECT Bolded.bolded, Bolded.position, Post.postnumber, Post.posttime
FROM Bolded
JOIN Post ON (Bolded.postid = Post.postid)
WHERE
(Bolded.postid = @p1)
AND (Bolded.ignore = 0)
ORDER BY Bolded.position DESC
LIMIT 1
;
";
using (SqliteCommand cmd = new SqliteCommand(sql, dbRead))
{
foreach (VoterInfo v in voters)
{
Int32 id = v.PostId;
if (id <= 0)
{
continue;
}
cmd.Parameters.Add(new SqliteParameter("@p1", id));
using (SqliteDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
String bolded = r.GetString(0);
Int32 position = r.GetInt32(1);
Int32 number = r.GetInt32(2);
DateTimeOffset time = r.GetDateTime(3);
v.SetVote(bolded, number, time, id, position);
}
}
}
}
}
watch.Stop();
//Trace.TraceInformation("after post counts {0}", watch.Elapsed.ToString());
return(voters);
}
public void AddPosts(Posts posts)
{
Stopwatch watch = new Stopwatch();
watch.Start();
using (SqliteConnection dbWrite = new SqliteConnection(_connect))
{
dbWrite.Open();
using (SqliteTransaction trans = dbWrite.BeginTransaction())
{
String sql =
@"INSERT OR IGNORE INTO Poster (posterid, postername) VALUES (@p3, @p8);
INSERT OR IGNORE INTO Post (
postid,
threadid,
posterid,
postnumber,
posttime)
VALUES (@p1, @p2, @p3, @p4, @p7);";
using (SqliteCommand cmd = new SqliteCommand(sql, dbWrite, trans))
{
SqliteParameter pPostId = new SqliteParameter("@p1");
SqliteParameter pThreadId = new SqliteParameter("@p2");
SqliteParameter pPosterId = new SqliteParameter("@p3");
SqliteParameter pPostNumber = new SqliteParameter("@p4");
SqliteParameter pContent = new SqliteParameter("@p5");
SqliteParameter pTitle = new SqliteParameter("@p6");
SqliteParameter pTime = new SqliteParameter("@p7", System.Data.DbType.DateTime);
SqliteParameter pPosterName = new SqliteParameter("@p8");
cmd.Parameters.Add(pPostId);
cmd.Parameters.Add(pThreadId);
cmd.Parameters.Add(pPosterName);
cmd.Parameters.Add(pPosterId);
cmd.Parameters.Add(pPostNumber);
cmd.Parameters.Add(pTime);
foreach (Post p in posts)
{
pPostId.Value = p.PostId;
pThreadId.Value = p.ThreadId;
pPosterName.Value = p.Poster.Name;
pPosterId.Value = p.Poster.Id;
pPostNumber.Value = p.PostNumber;
pContent.Value = p.Content;
pTitle.Value = p.Title;
pTime.Value = p.Time.UtcDateTime;
int e = cmd.ExecuteNonQuery();
int ix = 0;
String sqlBold =
@"INSERT OR IGNORE INTO Bolded (
postid,
position,
bolded,
ignore)
VALUES (@p1, @p2, @p3, @p4);";
if (p.Bolded != null)
{
using (SqliteCommand cmdBold = new SqliteCommand(sqlBold, dbWrite, trans))
{
SqliteParameter pForeignPostId = new SqliteParameter("@p1");
SqliteParameter pIx = new SqliteParameter("@p2");
SqliteParameter pBolded = new SqliteParameter("@p3");
SqliteParameter pIgnore = new SqliteParameter("@p4");
cmdBold.Parameters.Add(pForeignPostId);
cmdBold.Parameters.Add(pIx);
cmdBold.Parameters.Add(pBolded);
cmdBold.Parameters.Add(pIgnore);
foreach (Bold b in p.Bolded)
{
pForeignPostId.Value = p.PostId;
pIx.Value = ix;
pBolded.Value = b.Content;
pIgnore.Value = b.Ignore;
e = cmdBold.ExecuteNonQuery();
ix++;
}
}
}
}
}
trans.Commit();
}
}
watch.Stop();
//Trace.TraceInformation("after AddPostsToDB {0}", watch.Elapsed.ToString());
}
void CreateTables(SqliteConnection db)
{
String[] tables =
{
@"CREATE TABLE IF NOT EXISTS Thread (
threadid INTEGER NOT NULL PRIMARY KEY,
url TEXT,
turbo INTEGER,
active INTEGER DEFAULT 1
);",
@"CREATE TABLE IF NOT EXISTS Day (
threadid INTEGER REFERENCES Thread(threadid) ON DELETE CASCADE,
day INTEGER,
starttime TIMESTAMP,
endtime TIMESTAMP,
PRIMARY KEY(threadid, day)
);",
@"CREATE TABLE IF NOT EXISTS Poster (
posterid INTEGER NOT NULL PRIMARY KEY,
postername TEXT COLLATE NOCASE
);",
@"CREATE INDEX IF NOT EXISTS
postersname
ON
Poster (postername)
;",
@"CREATE TABLE IF NOT EXISTS GameRole (
roleid INTEGER PRIMARY KEY AUTOINCREMENT,
threadid INTEGER REFERENCES Thread(threadid) ON DELETE CASCADE,
birthtime INTEGER DEFAULT 1,
deathtime INTEGER
);",
@"CREATE INDEX IF NOT EXISTS
rolesthread
ON
GameRole (threadid)
;",
@"CREATE TABLE IF NOT EXISTS Post (
postid INTEGER NOT NULL PRIMARY KEY,
threadid INTEGER REFERENCES Thread(threadid) ON DELETE CASCADE,
posterid INTEGER REFERENCES Poster(posterid) ON DELETE CASCADE,
postnumber INTEGER,
posttime TIMESTAMP
);",
@"CREATE INDEX IF NOT EXISTS
poststhreadposter
ON
Post (threadid, posterid)
;",
@"CREATE VIRTUAL TABLE IF NOT EXISTS PostContent USING FTS4 (
title TEXT,
content TEXT,
editreason TEXT
);",
@"CREATE TABLE IF NOT EXISTS PostMeta (
postid INTEGER NOT NULL REFERENCES Post(postid) ON DELETE CASCADE,
contentid INTEGER NOT NULL REFERENCES PostContent(ROWID) ON DELETE CASCADE,
editorid INTEGER,
edittime TIMESTAMP,
PRIMARY KEY(postid, contentid)
);",
@"CREATE TABLE IF NOT EXISTS Bolded (
postid INTEGER NOT NULL REFERENCES Post(postid) ON DELETE CASCADE,
position INTEGER,
bolded TEXT,
ignore INTEGER,
PRIMARY KEY(postid, position)
);",
@"CREATE TABLE IF NOT EXISTS Player (
roleid INTEGER REFERENCES GameRole(roleid) ON DELETE CASCADE,
posterid INTEGER REFERENCES Poster(posterid) ON DELETE CASCADE,
starttime TIMESTAMP,
endtime TIMESTAMP,
PRIMARY KEY(roleid, posterid)
);",
@"CREATE TABLE IF NOT EXISTS Alias (
threadid INTEGER REFERENCES Thread(threadid) ON DELETE CASCADE,
bolded TEXT COLLATE NOCASE,
posterid TEXT COLLATE NOCASE,
PRIMARY KEY(threadid, bolded)
);",
@"CREATE INDEX IF NOT EXISTS
aliasesthread
ON
Alias (threadid)
;",
};
foreach (String sql in tables)
{
using (SqliteCommand cmd = new SqliteCommand(sql, db))
{
int e = cmd.ExecuteNonQuery();
}
}
}
public void WriteRoster(Int32 threadId, IEnumerable <CensusEntry> census)
{
Dictionary <String, List <CensusEntry> > roleList = new Dictionary <String, List <CensusEntry> >();
HashSet <String> subOut = new HashSet <string>();
HashSet <String> subIn = new HashSet <string>();
HashSet <String> all = new HashSet <string>();
foreach (CensusEntry ce in census)
{
if ((ce.Name == null) || (ce.Name == String.Empty))
{
continue;
}
if (!roleList.ContainsKey(ce.Name))
{
all.Add(ce.Name);
roleList.Add(ce.Name, new List <CensusEntry> {
ce
});
if ((ce.Replacement != null) && (ce.Replacement != String.Empty))
{
subOut.Add(ce.Name);
subIn.Add(ce.Replacement);
}
}
}
HashSet <String> originals = new HashSet <string>(all.Except(subIn));
// merge subs
foreach (String original in originals)
{
String current = roleList[original][0].Replacement;
while ((current != String.Empty) && (current != null))
{
if (!roleList.ContainsKey(current))
{
break;
}
roleList[original].AddRange(roleList[current]);
String next = roleList[current].Last().Replacement;
roleList.Remove(current);
current = next;
}
}
Stopwatch watch = new Stopwatch();
watch.Start();
using (SqliteConnection dbWrite = new SqliteConnection(_connect))
{
dbWrite.Open();
using (SqliteTransaction trans = dbWrite.BeginTransaction())
{
String sqlDelete =
@"DELETE
FROM GameRole
where (threadid = @p1);";
using (SqliteCommand cmd = new SqliteCommand(sqlDelete, dbWrite, trans))
{
cmd.Parameters.Add(new SqliteParameter("@p1", threadId));
int e = cmd.ExecuteNonQuery();
}
String sql =
@"INSERT INTO GameRole (
threadid, deathtime)
VALUES (@p1, @p2);
SELECT last_insert_rowid();";
using (SqliteCommand cmd = new SqliteCommand(sql, dbWrite, trans))
{
SqliteParameter pThreadId = new SqliteParameter("@p1");
cmd.Parameters.Add(pThreadId);
pThreadId.Value = threadId;
foreach (List <CensusEntry> role in roleList.Values)
{
Int32 id = -1;
DateTime? deathTime = null;
CensusEntry lastPlayer = role.Last();
if (lastPlayer.Alive == "Dead")
{
deathTime = lastPlayer.EndPostTime;
}
SqliteParameter pDeath = new SqliteParameter("@p2", System.Data.DbType.DateTime);
pDeath.Value = deathTime;
cmd.Parameters.Add(pDeath);
using (SqliteDataReader r = cmd.ExecuteReader())
{
if (r.Read())
{
id = r.GetInt32(0);
}
}
if (id == -1)
{
continue;
}
string sqlPlayer =
@"INSERT INTO Player
(roleid, posterid, starttime, endtime)
VALUES(@p1, @p2, @p3, @p4);";
using (SqliteCommand cmdPlayer = new SqliteCommand(sqlPlayer, dbWrite, trans))
{
SqliteParameter pRoleId = new SqliteParameter("@p1");
pRoleId.Value = id;
DateTime?startTime = null;
foreach (CensusEntry player in role)
{
SqliteParameter pPosterId = new SqliteParameter("@p2");
Int32 playerId = GetPlayerId(player.Name);
if (playerId < 0)
{
break;
}
pPosterId.Value = playerId;
cmdPlayer.Parameters.Add(pRoleId);
cmdPlayer.Parameters.Add(pPosterId);
SqliteParameter pStartTime = new SqliteParameter("@p3", System.Data.DbType.DateTime);
pStartTime.Value = startTime;
cmdPlayer.Parameters.Add(pStartTime);
SqliteParameter pEndTime = new SqliteParameter("@p4", System.Data.DbType.DateTime);
if (player.Alive != "Alive")
{
DateTime?endTime = null;
if (player.EndPostTime != null)
{
endTime = player.EndPostTime.Value.ToUniversalTime();
}
pEndTime.Value = endTime;
startTime = endTime;
}
cmdPlayer.Parameters.Add(pEndTime);
int e = cmdPlayer.ExecuteNonQuery();
}
}
}
}
trans.Commit();
}
}
watch.Stop();
//Trace.TraceInformation("after ReplacePlayerList {0}", watch.Elapsed.ToString());
}
public IEnumerable <CensusEntry> ReadRoster(Int32 threadId)
{
SortableBindingList <CensusEntry> census = new SortableBindingList <CensusEntry>();
String sql =
@"
SELECT Player.roleid, GameRole.deathtime, Player.posterid, Player.endtime, Poster.postername
FROM GameRole, Player, Poster
WHERE
(GameRole.threadid = @p1)
AND (Player.roleid = GameRole.roleid)
AND (Player.posterid = Poster.posterid)
ORDER BY GameRole.roleid ASC,
(CASE WHEN Player.endtime IS NULL THEN 0 ELSE 1 END),
Player.endtime ASC
;
";
Stopwatch watch = new Stopwatch();
watch.Start();
using (SqliteConnection dbRead = new SqliteConnection(_connect))
{
dbRead.Open();
using (SqliteCommand cmd = new SqliteCommand(sql, dbRead))
{
cmd.Parameters.Add(new SqliteParameter("@p1", threadId));
using (SqliteDataReader r = cmd.ExecuteReader())
{
Int32 currentRole = -1;
String newestName = String.Empty;
while (r.Read())
{
CensusEntry ce = new CensusEntry();
Int32 role = r.GetInt32(0);
if (currentRole == role)
{
ce.Alive = "Sub Out";
ce.Replacement = newestName;
}
else
{
currentRole = role;
if (!r.IsDBNull(1))
{
ce.EndPostTime = r.GetDateTime(1);
ce.Alive = "Dead";
}
}
Int32 playerId = r.GetInt32(2);
if (!r.IsDBNull(3))
{
ce.EndPostTime = r.GetDateTime(3);
}
ce.Name = r.GetString(4);
newestName = ce.Name;
census.Add(ce);
}
}
}
}
watch.Stop();
//Trace.TraceInformation("After ReadRoster {0}", watch.Elapsed.ToString());
return(census);
}
public void ReplacePlayerList(Int32 threadId, IEnumerable <String> players)
{
Stopwatch watch = new Stopwatch();
watch.Start();
using (SqliteConnection dbWrite = new SqliteConnection(_connect))
{
dbWrite.Open();
using (SqliteTransaction trans = dbWrite.BeginTransaction())
{
String sqlDelete =
@"DELETE
FROM GameRole
where (threadid = @p1);";
using (SqliteCommand cmd = new SqliteCommand(sqlDelete, dbWrite, trans))
{
cmd.Parameters.Add(new SqliteParameter("@p1", threadId));
int e = cmd.ExecuteNonQuery();
}
String sql =
@"INSERT INTO GameRole (
threadid)
VALUES (@p1);
SELECT last_insert_rowid();";
string sqlPlayer =
@"INSERT INTO Player (roleid, posterid) VALUES(@p1, @p2);";
using (SqliteCommand cmd = new SqliteCommand(sql, dbWrite, trans))
{
SqliteParameter pThreadId = new SqliteParameter("@p1");
cmd.Parameters.Add(pThreadId);
pThreadId.Value = threadId;
foreach (String player in players)
{
Int32 id = -1;
using (SqliteDataReader r = cmd.ExecuteReader())
{
if (r.Read())
{
id = r.GetInt32(0);
}
}
if (id == -1)
{
continue;
}
using (SqliteCommand cmdPlayer = new SqliteCommand(sqlPlayer, dbWrite, trans))
{
SqliteParameter pRoleId = new SqliteParameter("@p1");
pRoleId.Value = id;
SqliteParameter pPosterId = new SqliteParameter("@p2");
pPosterId.Value = GetPlayerId(player);
cmdPlayer.Parameters.Add(pRoleId);
cmdPlayer.Parameters.Add(pPosterId);
int e = cmdPlayer.ExecuteNonQuery();
}
}
}
trans.Commit();
}
}
watch.Stop();
//Trace.TraceInformation("after ReplacePlayerList {0}", watch.Elapsed.ToString());
}
/// <summary>
/// Зарежда новите новини
/// </summary>
public void TmpNewsTransfer(SqliteCommand aCmmndNews, SqliteCommand aCmmndEncl)
{
DataTable dtNews = new DataTable();
aCmmndNews.CommandText =
"SELECT tmp_news.news_id FROM tmp_news ";
using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(aCmmndNews))
{
dtNews.Clear();
sqlDAdapter.Fill(dtNews);
}
// Ако няма новини - КРАЙ
if (dtNews.Rows.Count == 0)
{ return; }
Int32 iNewsID = 0;
// ID на последната новина
aCmmndNews.CommandText =
"SELECT ifnull(max(news.news_id),0) AS news_id " +
" FROM news ";
using (SqliteDataReader dr = aCmmndNews.ExecuteReader())
{
if (dr.HasRows && dr.Read())
{
if (dr["news_id"].ToString().Equals(""))
{
iNewsID = 1;
}
else
{
iNewsID = Convert.ToInt32(dr["news_id"]) + 1;
}
}
else
{
throw new Exception("Exception: TmpNewsTransfer");
}
dr.Close();
}
// Новини
aCmmndNews.CommandText = "INSERT INTO news ( news_id, news_site_id, news_guid, news_title, news_link, news_description, news_body, news_comments, news_pubdate, news_category_id ) "
+ " SELECT @news_id "
+ " , tmp_news.news_site_id "
+ " , tmp_news.news_guid "
+ " , tmp_news.news_title "
+ " , tmp_news.news_link "
+ " , tmp_news.news_description "
+ " , tmp_news.news_body "
+ " , tmp_news.news_comments "
+ " , tmp_news.news_pubdate "
+ " , tmp_news.news_category_id "
+ " FROM tmp_news "
+ " WHERE ( tmp_news.news_id = @tmp_news_id ); ";
aCmmndNews.Parameters.Clear();
aCmmndNews.Parameters.Add("@news_id", DbType.Int32);
aCmmndNews.Parameters.Add("@tmp_news_id", DbType.Int32);
// Новини - Картинки
aCmmndEncl.CommandText = "INSERT INTO enclosure ( news_id, encl_link, encl_id ) "
+ " SELECT @news_id "
+ " , tmp_enclosure.encl_link "
+ " , tmp_enclosure.encl_id "
+ " FROM tmp_enclosure "
+ " WHERE ( tmp_enclosure.news_id = @tmp_news_id ); ";
aCmmndEncl.Parameters.Clear();
aCmmndEncl.Parameters.Add("@news_id", DbType.Int32);
aCmmndEncl.Parameters.Add("@tmp_news_id", DbType.Int32);
foreach (DataRow dr in dtNews.Rows)
{
//
aCmmndNews.Parameters["@news_id"].Value = iNewsID;
aCmmndNews.Parameters["@tmp_news_id"].Value = dr["news_id"];
aCmmndNews.ExecuteNonQuery();
aCmmndEncl.Parameters["@news_id"].Value = iNewsID;
aCmmndEncl.Parameters["@tmp_news_id"].Value = dr["news_id"];
aCmmndEncl.ExecuteNonQuery();
iNewsID++;
}
}
public ActionResult GetResultTypes(string BaseId, string CompareId)
{
var json_out = new Dictionary <string, bool>()
{
{ "File", false },
{ "Certificate", false },
{ "Registry", false },
{ "Port", false },
{ "Service", false },
{ "User", false }
};
var count = new Dictionary <string, int>()
{
{ "File", 0 },
{ "Certificate", 0 },
{ "Registry", 0 },
{ "Port", 0 },
{ "Service", 0 },
{ "User", 0 }
};
using (var cmd = new SqliteCommand(SQL_GET_RESULT_TYPES, DatabaseManager.Connection, DatabaseManager.Transaction))
{
cmd.Parameters.AddWithValue("@base_run_id", BaseId?.ToString());
cmd.Parameters.AddWithValue("@compare_run_id", CompareId?.ToString());
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (int.Parse(reader["file_system"].ToString()) != 0)
{
count["File"]++;
}
if (int.Parse(reader["ports"].ToString()) != 0)
{
count["Port"]++;
}
if (int.Parse(reader["users"].ToString()) != 0)
{
count["User"]++;
}
if (int.Parse(reader["services"].ToString()) != 0)
{
count["Service"]++;
}
if (int.Parse(reader["registry"].ToString()) != 0)
{
count["Registry"]++;
}
if (int.Parse(reader["certificates"].ToString()) != 0)
{
count["Certificate"]++;
}
}
}
}
foreach (KeyValuePair <string, int> entry in count)
{
if (entry.Value == 2)
{
json_out[entry.Key] = true;
}
}
return(Json(json_out));
}
/// <summary>
/// Определя категорията на новината
/// </summary>
private void SetNewsCategory(SqliteCommand aSQLCmmnd)
{
aSQLCmmnd.CommandText =
"UPDATE tmp_news " +
" SET news_category_id = (SELECT nctg.news_category_id " +
" FROM t_news_category nctg " +
" WHERE tmp_news.news_category = nctg.news_category " +
" AND tmp_news.news_site_id = nctg.news_site_id); ";
aSQLCmmnd.ExecuteNonQuery();
}
public static SqliteDataReader Command(SqliteCommand command)
{
var reader = command.ExecuteReader();
return(reader);
}
/// <summary>
/// Returns a list of AssetMetadata objects. The list is a subset of
/// the entire data set offset by <paramref name="start" /> containing
/// <paramref name="count" /> elements.
/// </summary>
/// <param name="start">The number of results to discard from the total data set.</param>
/// <param name="count">The number of rows the returned list should contain.</param>
/// <returns>A list of AssetMetadata objects.</returns>
public override List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
{
List<AssetMetadata> retList = new List<AssetMetadata>(count);
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(SelectAssetMetadataSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":start", start));
cmd.Parameters.Add(new SqliteParameter(":count", count));
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
AssetMetadata metadata = buildAssetMetadata(reader);
retList.Add(metadata);
}
}
}
}
return retList;
}
public Dictionary <string, List <string> > ViewAll()
{
Dictionary <string, List <string> > allResults = new Dictionary <string, List <string> >();
List <string> names = new List <string>();
List <string> age = new List <string>();
List <string> gender = new List <string>();
List <string> bio = new List <string>();
List <string> level = new List <string>();
List <string> race = new List <string>();
List <string> cClass = new List <string>();
List <string> spell = new List <string>();
List <string> hits = new List <string>();
List <string> ability = new List <string>();
try
{
using (con = new SqliteConnection("Data Source=DnDBuilderDB.sqlite;Version=3;"))
{
con.Open();
SqliteCommand chkNameExist = new SqliteCommand("SELECT count(*) FROM DnDCharacter", con);
int count = Convert.ToInt32(chkNameExist.ExecuteScalar());
if (count > 0)
{
SqliteCommand vAll = new SqliteCommand("SELECT * FROM DnDCharacter", con);
var ret = vAll.ExecuteReader();
while (ret.Read())
{
names.Add(ret.GetString(0));
age.Add(Convert.ToString(ret.GetInt32(1)));
gender.Add(ret.GetString(2));
bio.Add(ret.GetString(3));
level.Add(Convert.ToString(ret.GetInt32(4)));
race.Add(ret.GetString(5));
cClass.Add(ret.GetString(6));
spell.Add(ret.GetString(7));
hits.Add(Convert.ToString(ret.GetInt32(8)));
ability.Add(Convert.ToString(ret.GetInt32(9)));
}
ret.NextResult();
allResults.Add("Name", names);
allResults.Add("Age", age);
allResults.Add("Gender", gender);
allResults.Add("Bio", bio);
allResults.Add("Level", level);
allResults.Add("Race", race);
allResults.Add("Class", cClass);
allResults.Add("Spellcaster", spell);
allResults.Add("Hit Points", hits);
allResults.Add("Ability Scores", ability);
}
else
{
List <string> err = new List <string>();
err.Add("No Records available");
allResults.Add("Error", err);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(allResults);
}
private static SqliteDataReader ExecuteCommand(SqliteCommand command)
{
return(command.ExecuteReader());
}
public string Download(string name)
{
string msg = null;
DataTable dt = new DataTable();
dt.TableName = "dndCharacter";
try
{
using (con = new SqliteConnection("Data Source=DnDBuilderDB.sqlite;Version=3;"))
{
con.Open();
SqliteCommand chkNameExist = new SqliteCommand("SELECT count(*) FROM DnDCharacter WHERE Name='" + name + "'", con);
int count = Convert.ToInt32(chkNameExist.ExecuteScalar());
if (count != 0)
{
SqliteCommand vSingle = new SqliteCommand("SELECT * FROM DnDCharacter WHERE Name='" + name + "'", con);
var ret = vSingle.ExecuteReader();
DataColumn dc1 = new DataColumn("Name");
DataColumn dc2 = new DataColumn("Age");
DataColumn dc3 = new DataColumn("Gender");
DataColumn dc4 = new DataColumn("Bio");
DataColumn dc5 = new DataColumn("Level");
DataColumn dc6 = new DataColumn("Race");
DataColumn dc7 = new DataColumn("Class");
DataColumn dc8 = new DataColumn("Spellcaster");
DataColumn dc9 = new DataColumn("Hit Points");
DataColumn dc10 = new DataColumn("Ability Score");
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc3);
dt.Columns.Add(dc4);
dt.Columns.Add(dc5);
dt.Columns.Add(dc6);
dt.Columns.Add(dc7);
dt.Columns.Add(dc8);
dt.Columns.Add(dc9);
dt.Columns.Add(dc10);
dt.Rows.Add(ret[0], Convert.ToString(ret[1]), ret[2], ret[3], Convert.ToString(ret[4]), ret[5], ret[6], ret[7], Convert.ToString(ret[8]), Convert.ToString(ret[9]));
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ds.WriteXml(ret[0] + ".xml");
msg = "Downloaded successfully";
con.Close();
}
else
{
msg = "Record does not exist";
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(msg);
}
public void CreateDropdowns()
{
foreach (Dropdown dropdownObj in dropdowns)
{
Destroy(dropdownObj.gameObject);
}
dropdowns.Clear();
if (numberOfRuns.options[numberOfRuns.value].text == "All Runs from Date...")
{
dropdownInstance = Instantiate(runNumberPrefab);
dropdownInstance.GetComponentInChildren <Text>().text = "Date";
dropdownInstance.transform.position = new Vector3(90, 275, 0);
dropdownInstance.transform.parent = interfaceCanvas.transform;
dropdownInstance.ClearOptions();
dropdowns.Add(dropdownInstance);
List <string> options = new List <string>();
sql = "select * from RunID order by RunID asc";
command = new SqliteCommand(sql, m_dbConnectionRunID);
SqliteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
bool isNotInOptions = true;
foreach (string date in options)
{
if (reader["Date"].ToString() == date)
{
isNotInOptions = false;
}
}
if (isNotInOptions)
{
options.Add(reader["Date"].ToString());
}
}
dropdownInstance.AddOptions(options);
}
else if (numberOfRuns.options[numberOfRuns.value].text == "All Runs with Event...")
{
dropdownInstance = Instantiate(runNumberPrefab);
dropdownInstance.transform.position = new Vector3(90, 275, 0);
dropdownInstance.transform.parent = interfaceCanvas.transform;
dropdownInstance.ClearOptions();
dropdowns.Add(dropdownInstance);
List <string> options = new List <string>();
sql = "select * from RunData order by RunID asc";
command = new SqliteCommand(sql, m_dbConnectionRunData);
SqliteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (reader["CustomEvent"].ToString() != "N/A")
{
bool isNotInOptions = true;
foreach (string events in options)
{
if (reader["CustomEvent"].ToString() == events)
{
isNotInOptions = false;
}
}
if (isNotInOptions)
{
options.Add(reader["CustomEvent"].ToString());
}
}
}
dropdownInstance.AddOptions(options);
//Look for content in custom events column
//If any, put whatever the value is as an option on the dropdown
//Then when the user selects that event, go through all runs and find any runs with that event, then use them in the heatmap
}
else
{
noOfRunsInt = numberOfRuns.value + 1;
int posX = 90;
int posY = 275;
for (int i = 0; i < noOfRunsInt; i++)
{
if (i % 6 == 0 && i != 0)
{
Debug.Log("THis is happening");
posY = 275;
posX += 180;
}
Dropdown instance = Instantiate(runNumberPrefab);
instance.transform.position = new Vector3(posX, posY, 0);
instance.transform.parent = interfaceCanvas.transform;
dropdowns.Add(instance);
posY -= 50;
dropdowns[i].ClearOptions();
dropdowns[i].AddOptions(runIDs);
//dropdowns.Add()
}
}
}
/// <summary>
/// Avoids the additional database trip that using SqliteCommandBuilder requires.
/// </summary>
/// <returns>The insert command.</returns>
/// <param name="table">Table.</param>
/// <param name="values">Values.</param>
/// <param name="conflictResolutionStrategy">Conflict resolution strategy.</param>
SqliteCommand GetInsertCommand (String table, ContentValues values, ConflictResolutionStrategy conflictResolutionStrategy)
{
var builder = new StringBuilder("INSERT");
if (conflictResolutionStrategy != ConflictResolutionStrategy.None) {
builder.Append(" OR ");
builder.Append(conflictResolutionStrategy);
}
builder.Append(" INTO ");
builder.Append(table);
builder.Append(" (");
// Append our content column names and create our SQL parameters.
var valueSet = values.ValueSet();
var sqlParams = new SqliteParameter[valueSet.LongCount()];
var valueBuilder = new StringBuilder();
var index = 0L;
foreach(var column in valueSet)
{
if (index > 0) {
builder.Append(",");
valueBuilder.Append(",");
}
builder.AppendFormat( "{0}", column.Key);
valueBuilder.AppendFormat("@{0}", column.Key);
sqlParams[index++] = new SqliteParameter(column.Key, column.Value);
}
builder.Append(") VALUES (");
builder.Append(valueBuilder);
builder.Append(")");
var sql = builder.ToString();
var command = new SqliteCommand(sql, Connection, currentTransaction);
command.Parameters.Clear();
command.Parameters.AddRange(sqlParams);
return command;
}
public static void TestPacienteDeleteValue()
{
using (var conn = new SqliteConnection(GlobalController.path))
{
conn.Open();
Pessoa.Insert("fake name1", "m", "1995-01-01", "6198732711", null);
Paciente.Insert(1, "observacao teste");
var check = "SELECT EXISTS(SELECT 1 FROM 'PACIENTE' WHERE \"idPaciente\" = \"1\" LIMIT 1)";
var result = 0;
using (var cmd = new SqliteCommand(check, conn))
{
using (IDataReader reader = cmd.ExecuteReader())
{
try
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
result = reader.GetInt32(0);
}
}
}
finally
{
reader.Dispose();
reader.Close();
}
}
cmd.Dispose();
}
Assert.AreEqual(result, 1);
Paciente.DeleteValue(1);
result = 0;
using (var cmd = new SqliteCommand(check, conn))
{
using (IDataReader reader = cmd.ExecuteReader())
{
try
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
result = reader.GetInt32(0);
}
}
}
finally
{
reader.Dispose();
reader.Close();
}
}
cmd.Dispose();
}
Assert.AreEqual(result, 0);
conn.Dispose();
conn.Close();
}
}
/// <summary>
/// Delete an asset from database
/// </summary>
/// <param name="uuid"></param>
public override bool Delete(string id)
{
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", id));
cmd.ExecuteNonQuery();
}
}
return true;
}
public void GenerateHeatMap()
{
List <Vector3> transforms = new List <Vector3>();
List <GameObject> cubes = new List <GameObject>();
bool makeCubeGreen = false;
if (numberOfRuns.options[numberOfRuns.value].text == "All Runs from Date...")
{
//Find all entries with the chosen date in RUNID table
List <string> applicableRuns = new List <string>();
sql = "select * from RunID order by RunID asc";
command = new SqliteCommand(sql, m_dbConnectionRunID);
SqliteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (reader["Date"].ToString() == dropdownInstance.options[dropdownInstance.value].text)
{
//Put those RUNIDs into a list of strings
applicableRuns.Add(reader["RunID"].ToString());
}
}
//Then for each RUNID in the list, go through each entry in the RunData table and find that run,
foreach (string run in applicableRuns)
{
string runIDInUse = run;
sql = "select * from RunData where RunID =" + runIDInUse + " order by Frame asc ";
command = new SqliteCommand(sql, m_dbConnectionRunData);
reader = command.ExecuteReader();
//then make a cube for each entry in that run
while (reader.Read())
{
if (reader["CustomEvent"].ToString() != "N/A")
{
makeCubeGreen = true;
}
//Convert the transform into a string, then back into a vector
string sVector = reader["Transform"].ToString();
if (sVector.StartsWith("(") && sVector.EndsWith(")"))
{
sVector = sVector.Substring(1, sVector.Length - 2);
}
string[] vSplit = sVector.Split(',');
Vector3 finalTransform = new Vector3(
float.Parse(vSplit[0]),
float.Parse(vSplit[1]),
float.Parse(vSplit[2]));
//If makecubegreen is true, then spawn the cube and add it to cubes?
if (makeCubeGreen)
{
GameObject instance = Instantiate(heatmapPoints, finalTransform, Quaternion.identity);
instance.GetComponent <Renderer>().material.color = new Color(0, 50, 0);
instance.GetComponent <Light>().color = new Color(0, 25, 0);
cubes.Add(instance);
makeCubeGreen = false;
}
else
{
//else just add it as normal?
transforms.Add(finalTransform);
}
}
}
}
else if (numberOfRuns.options[numberOfRuns.value].text == "All Runs with Event...")
{
//Find all entries with the chosen event in the RunData
//Get the RunID's of those entries
//Put them into a string list, then do the same as with Dates
//Add some logic to make the cube green if it was a custom event
List <string> applicableRuns = new List <string>();
sql = "select * from RunData order by RunID asc";
command = new SqliteCommand(sql, m_dbConnectionRunData);
SqliteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (reader["CustomEvent"].ToString() == dropdownInstance.options[dropdownInstance.value].text)
{
//Put those RUNIDs into a list of strings
applicableRuns.Add(reader["RunID"].ToString());
}
}
foreach (string run in applicableRuns)
{
string runIDInUse = run;
sql = "select * from RunData where RunID =" + runIDInUse + " order by Frame asc ";
command = new SqliteCommand(sql, m_dbConnectionRunData);
reader = command.ExecuteReader();
//then make a cube for each entry in that run
while (reader.Read())
{
if (reader["CustomEvent"].ToString() != "N/A")
{
makeCubeGreen = true;
}
//Convert the transform into a string, then back into a vector
string sVector = reader["Transform"].ToString();
if (sVector.StartsWith("(") && sVector.EndsWith(")"))
{
sVector = sVector.Substring(1, sVector.Length - 2);
}
string[] vSplit = sVector.Split(',');
Vector3 finalTransform = new Vector3(
float.Parse(vSplit[0]),
float.Parse(vSplit[1]),
float.Parse(vSplit[2]));
//If makecubegreen is true, then spawn the cube and add it to cubes?
if (makeCubeGreen)
{
GameObject instance = Instantiate(heatmapPoints, finalTransform, Quaternion.identity);
instance.GetComponent <Renderer>().material.color = new Color(0, 50, 0);
instance.GetComponent <Light>().color = new Color(0, 25, 0);
cubes.Add(instance);
makeCubeGreen = false;
}
else
{
//else just add it as normal?
transforms.Add(finalTransform);
}
}
}
}
else
{
for (int i = 0; i < dropdowns.Count; i++)
{
//Look at value of Dropdown
// Debug.Log(dropdown.value + 1);
int runIDInUse = dropdowns[i].value + 1;
//Using SQLite, try to find all entries with the same Run ID as the Dropdown number in the RunData database
sql = "select * from RunData where RunID =" + runIDInUse + " order by Frame asc ";
command = new SqliteCommand(sql, m_dbConnectionRunData);
SqliteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// Debug.Log("RunID " + reader["RunID"]);
//Debug.Log("Frame: " + reader["Frame"]);
//Debug.Log("Transform: " + reader["Transform"]);
if (reader["CustomEvent"].ToString() != "N/A")
{
makeCubeGreen = true;
}
//Convert the transform into a string, then back into a vector
string sVector = reader["Transform"].ToString();
if (sVector.StartsWith("(") && sVector.EndsWith(")"))
{
sVector = sVector.Substring(1, sVector.Length - 2);
}
string[] vSplit = sVector.Split(',');
Vector3 finalTransform = new Vector3(
float.Parse(vSplit[0]),
float.Parse(vSplit[1]),
float.Parse(vSplit[2]));
//If makecubegreen is true, then spawn the cube and add it to cubes?
if (makeCubeGreen)
{
GameObject instance = Instantiate(heatmapPoints, finalTransform, Quaternion.identity);
instance.GetComponent <Renderer>().material.color = new Color(0, 50, 0);
instance.GetComponent <Light>().color = new Color(155, 255, 155);
cubes.Add(instance);
makeCubeGreen = false;
}
else
{
//else just add it as normal?
transforms.Add(finalTransform);
}
}
}
}
//Take the transform for each entry and spawn a light/red cube at that location
foreach (Vector3 v3 in transforms)
{
bool makeCube = true;
for (int i = 0; i < cubes.Count; i++)
{
if (v3 == cubes[i].transform.position)
{
//increase colour intensity of cube i
makeCube = false;
Color currentColor = cubes[i].GetComponent <Light>().color;
// cubes[i].GetComponent<Renderer>().material.color = currentColor + new Color(1, 0, 0);
cubes[i].GetComponent <Light>().color = currentColor + new Color(0, -1, -1);
cubes[i].GetComponent <Light>().intensity += 0.01f;
}
}
if (makeCube)
{
GameObject instance = Instantiate(heatmapPoints, v3, Quaternion.identity);
cubes.Add(instance);
}
}
//Using the snapshot camera, take a screenshot of the current scene
cam.TakeScreenshot();
foreach (GameObject cube in cubes)
{
Destroy(cube.gameObject);
}
}
/*
* 执行增删改
*/
public void ExecuteI_D_U(string sql)
{
cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
/// <summary>
/// Avoids the additional database trip that using SqliteCommandBuilder requires.
/// </summary>
/// <returns>The delete command.</returns>
/// <param name="table">Table.</param>
/// <param name="whereClause">Where clause.</param>
/// <param name="whereArgs">Where arguments.</param>
SqliteCommand GetDeleteCommand (string table, string whereClause, string[] whereArgs)
{
var builder = new StringBuilder("DELETE FROM ");
builder.Append(table);
if (!whereClause.IsEmpty()) {
builder.Append(" WHERE ");
builder.Append(whereClause.ReplacePositionalParams());
}
var command = new SqliteCommand(builder.ToString(), Connection, currentTransaction);
command.Parameters.Clear();
command.Parameters.AddRange(whereArgs.ToSqliteParameters());
return command;
}
/// <summary>
/// SqlDataAdapter
/// </summary>
/// <param name="command"></param>
public SqliteDataAdapter(SqliteCommand command)
{
this.command = command;
}
private void LoadData()
{
lbl_ID.Text = _CatID.ToString();
using (SqliteConnection sqlCnnctn = new SqliteConnection())
{
sqlCnnctn.ConnectionString = _ConnectionString;
sqlCnnctn.Open();
using (SqliteCommand sqlCmmnd = new SqliteCommand())
{
sqlCmmnd.Connection = sqlCnnctn;
sqlCmmnd.CommandText =
"SELECT nct.news_category " +
" , ifnull(nct.n_dscr, '') AS n_dscr " +
" , src.n_source " +
" FROM t_news_category nct " +
" , t_news_sources src " +
" WHERE nct.news_category_id = " + _CatID.ToString() +
" AND src.n_source_id = nct.n_source_id ";
using (SqliteDataReader sqlDReader = sqlCmmnd.ExecuteReader())
{
if (sqlDReader.HasRows && sqlDReader.Read())
{
lbl_NCategory.Text = sqlDReader["news_category"].ToString();
lbl_NSource.Text = sqlDReader["n_source"].ToString();
tbx_NDscr.Text = sqlDReader["n_dscr"].ToString();
}
sqlDReader.Close();
}
}
sqlCnnctn.Close();
}
}
public void Truncate(string runid)
{
var cmd = new SqliteCommand(SQL_TRUNCATE, DatabaseManager.Connection, DatabaseManager.Transaction);
cmd.Parameters.AddWithValue("@run_id", runId);
}
/// <summary>
/// Обработва новните във временните таблици
/// </summary>
public void TmpNewsRepair(SqliteCommand aCmmndNews)
{
// Изтрива старите новини
aCmmndNews.CommandText =
"DELETE FROM tmp_news " +
" WHERE news_pubdate < @FromDate ";
aCmmndNews.Parameters.Clear();
aCmmndNews.Parameters.Add("@FromDate", DbType.Date).Value = DateTime.Now.AddMonths(-1);
aCmmndNews.ExecuteNonQuery();
// Изтрива новини с бъдеща дата
aCmmndNews.CommandText =
"DELETE FROM tmp_news " +
" WHERE news_pubdate > @FromDate ";
aCmmndNews.Parameters.Clear();
aCmmndNews.Parameters.Add("@FromDate", DbType.Date).Value = DateTime.Now.AddDays(1);
aCmmndNews.ExecuteNonQuery();
//
aCmmndNews.Parameters.Clear();
// Изтрива дублиращите се новини
aCmmndNews.CommandText =
"DELETE FROM tmp_news " +
" WHERE tmp_news.news_id in (SELECT tmp_news.news_id " +
" FROM news " +
" INNER JOIN tmp_news " +
" ON (news.news_guid = tmp_news.news_guid) " +
" AND (news.news_site_id = tmp_news.news_site_id)); ";
aCmmndNews.ExecuteNonQuery();
// Изтрива новините без картинки
aCmmndNews.CommandText =
"DELETE FROM tmp_news " +
" WHERE news_id NOT IN ( SELECT news_id FROM tmp_enclosure ) ";
OnLog(LogMessageType.Event, "- Изтрити новините без картинки: " + aCmmndNews.ExecuteNonQuery().ToString());
// Новини за добавяне
using (DataTable dtNews = new DataTable())
{
aCmmndNews.CommandText =
"SELECT tmp_news.news_id " +
" FROM tmp_news ";
using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(aCmmndNews))
{
dtNews.Clear();
sqlDAdapter.Fill(dtNews);
}
// Ако няма новини - КРАЙ
if (dtNews.Rows.Count == 0)
{ return; }
}
// Определя типа на Enclosure
aCmmndNews.CommandText =
"UPDATE tmp_enclosure " +
" SET encl_id = (SELECT encl.encl_id " +
" FROM t_enclosure encl " +
" WHERE tmp_enclosure.encl_type = encl.encl_type); ";
aCmmndNews.ExecuteNonQuery();
// Проверка за непознат тип на Enclosure
aCmmndNews.CommandText = "SELECT tmp_enclosure.encl_type "
+ " FROM tmp_enclosure "
+ " WHERE ( tmp_enclosure.encl_id = 0 ); ";
using (SqliteDataReader dr = aCmmndNews.ExecuteReader())
{
if (dr.HasRows)
{
string sMsg = "Неразпознат тип на 'Enclosure' !";
if (dr.Read())
{
sMsg += dr["encl_type"].ToString();
}
throw new Exception(sMsg);
}
dr.Close();
}
// Категории на новините
/* прецаква кодовата таблица
// Стандартизира news_category
aCmmndNews.CommandText =
"UPDATE tmp_news " +
" SET news_category = lower(trim(news_category)); ";
aCmmndNews.ExecuteNonQuery();
*/
// Определя категориите на новините
SetNewsCategory(aCmmndNews);
while (HasNewCategory(aCmmndNews))
{
AddNewCategory(aCmmndNews);
SetNewsCategory(aCmmndNews);
}
// Връзка м/у таблициците с категории
aCmmndNews.CommandText =
"INSERT INTO x_news_nweb " +
" ( news_category_id, nweb_category_id ) " +
" SELECT nct.news_category_id " +
" , 0 " +
" FROM t_news_category nct " +
" LEFT JOIN x_news_nweb xnn " +
" ON nct.news_category_id = xnn.news_category_id " +
" WHERE ifnull(xnn.news_category_id, 0) <= 0 " +
" ORDER BY nct.news_category_id; ";
aCmmndNews.ExecuteNonQuery();
}
public void SqList(string databaseFile,string opinion)
{
try
{
if (!File.Exists (databaseFile))
{
return;
}
//end if
// Connect to database
using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0}", databaseFile)))
{
sqlCon.SetPassword("haluk");
sqlCon.Open ();
using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
{
sqlCom.CommandText="SELECT * FROM Personel";
// if(opinion=="Z")
// sqlCom.CommandText = "SELECT * FROM ZSUBE";
//
// else if(opinion=="Y")
// sqlCom.CommandText = "SELECT * FROM YSUBE";
//
// Execute the SELECT statement and retrieve the data
using (SqliteDataReader dbReader = sqlCom.ExecuteReader ())
{
if (dbReader.HasRows)
{
int i;
// Advance through each row
tasks = new List<TaskX> ();
while (dbReader.Read ())
{
TaskX dtask= new TaskX() {Id = Convert.ToInt16( dbReader["ID"]),
Name = String.Format (Convert.ToString (dbReader["UserName"])),
Notes=String.Format ( Convert.ToString (dbReader["Password"])),
Done=false};
tasks.Add(dtask);
};
}
};
TableView.Source = new RootTableSource(tasks.ToArray ());
}
sqlCon.Close ();
}
} catch (Exception ex)
{ }
}
/// <summary>
/// Добавя новите категории
/// </summary>
private void AddNewCategory(SqliteCommand aSQLCmmnd)
{
aSQLCmmnd.CommandText =
"INSERT INTO t_news_category " +
" ( news_category_id, news_category, news_site_id ) " +
" SELECT (SELECT ifnull(MAX(news_category_id),0) +1 FROM t_news_category) " +
" , news_category " +
" , news_site_id " +
" FROM tmp_news nws " +
" WHERE ifnull(nws.news_category_id, 0) = 0 " +
" LIMIT 1 ";
aSQLCmmnd.ExecuteNonQuery();
}
protected void CloseCommand(SqliteCommand cmd)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
cmd.Dispose();
}
/// <summary>
/// Сваля новините и ги зарежда в tmp таблици
/// </summary>
private void LoadNewsRSS(SqliteCommand aCmmndNews, SqliteCommand aCmmndEncl, NewsSource aNSource)
{
// Освобождата tmp таблиците
OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg1_ExemptionТmpТables);
// tmp_news
aCmmndNews.CommandText = "DELETE FROM tmp_news;";
aCmmndNews.ExecuteNonQuery();
// tmp_enclosure
aCmmndEncl.CommandText = "DELETE FROM tmp_enclosure;";
aCmmndEncl.ExecuteNonQuery();
// Зарежда tmp таблиците
// tmp_news
aCmmndNews.CommandText =
"INSERT INTO tmp_news " +
" ( news_site_id, news_id, news_guid, news_title, news_link, news_description, news_body, news_comments, news_pubdate, news_category ) " +
" VALUES ( @news_site_id, @news_id, @news_guid, @news_title, @news_link, @news_description, @news_body, @news_comments, @news_pubdate, @news_category ); ";
aCmmndNews.Parameters.Clear();
aCmmndNews.Parameters.Add("@news_site_id", DbType.Int32).Value = _PNews.WSaitID;
aCmmndNews.Parameters.Add("@news_id", DbType.Int32);
aCmmndNews.Parameters.Add("@news_guid", DbType.String);
aCmmndNews.Parameters.Add("@news_title", DbType.String);
aCmmndNews.Parameters.Add("@news_link", DbType.String);
aCmmndNews.Parameters.Add("@news_description", DbType.String);
aCmmndNews.Parameters.Add("@news_body", DbType.String);
aCmmndNews.Parameters.Add("@news_comments", DbType.String);
aCmmndNews.Parameters.Add("@news_pubdate", DbType.DateTime);
aCmmndNews.Parameters.Add("@news_category", DbType.String);
// tmp_enclosure
aCmmndEncl.CommandText =
"INSERT INTO tmp_enclosure " +
" ( news_id, encl_link, encl_type ) " +
" VALUES ( @news_id, @encl_link, @encl_type ); ";
aCmmndEncl.Parameters.Clear();
aCmmndEncl.Parameters.Add("@news_id", DbType.Int32);
aCmmndEncl.Parameters.Add("@encl_link", DbType.String);
aCmmndEncl.Parameters.Add("@encl_type", DbType.String);
//
OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg2_DownloadedAndFilledNews);
// create a new xml doc
XmlDocument doc = new XmlDocument();
doc.XmlResolver = null;
// load the xml doc
// doc.Load(aNSource.URL);
string rssPage = RSSFeeds.dUtils.DownloadRss(aNSource.URL);
int indexEnd = rssPage.IndexOf("</rss>");
if (indexEnd > 0)
{
indexEnd += 6;
int endCount = rssPage.Length - indexEnd;
rssPage = rssPage.Remove(indexEnd, endCount);
}
doc.LoadXml(rssPage);
// get an xpath navigator
XPathNavigator navigator = doc.CreateNavigator();
NewsItem NData = new NewsItem();
// Get the links from the RSS feed
XPathNodeIterator nodesLink = navigator.Select("//rss/channel/item");
int iNewsID = 1;
while (nodesLink.MoveNext())
{
NData.Clear();
// clean up the link
XPathNodeIterator RNode = nodesLink.Current.SelectChildren(XPathNodeType.Element);
//
bool bRes = true;
while (RNode.MoveNext() && bRes)
{
bRes = _PNews.ParseNews(NData, RNode.Current);
}
// Ако е успешно заредена новината
if (bRes)
{
// tmp_news
aCmmndNews.Parameters["@news_id"].Value = iNewsID;
aCmmndNews.Parameters["@news_guid"].Value = NData.Guid;
aCmmndNews.Parameters["@news_title"].Value = NData.Title;
aCmmndNews.Parameters["@news_link"].Value = NData.Link;
aCmmndNews.Parameters["@news_description"].Value = NData.Description;
aCmmndNews.Parameters["@news_body"].Value = NData.Body;
aCmmndNews.Parameters["@news_comments"].Value = NData.Comments;
aCmmndNews.Parameters["@news_pubdate"].Value = NData.pubDate;
aCmmndNews.Parameters["@news_category"].Value = (NData.Category.Equals("") ? aNSource.DefCategory : NData.Category).ToLower().Trim();
aCmmndNews.ExecuteNonQuery();
// tmp_enclosure
aCmmndEncl.Parameters["@news_id"].Value = iNewsID;
foreach (Enclosure encl in NData.Enclosure)
{
aCmmndEncl.Parameters["@encl_link"].Value = encl.Link;
aCmmndEncl.Parameters["@encl_type"].Value = encl.Type;
aCmmndEncl.ExecuteNonQuery();
}
//
iNewsID++;
}
NData.Clear();
}
}
/// <summary>
/// Create an asset
/// </summary>
/// <param name="asset">Asset Base</param>
override public void StoreAsset(AssetBase asset)
{
//m_log.Info("[ASSET DB]: Creating Asset " + asset.FullID.ToString());
if (ExistsAsset(asset.FullID))
{
//LogAssetLoad(asset);
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
cmd.Parameters.Add(new SqliteParameter(":Flags", asset.Flags));
cmd.Parameters.Add(new SqliteParameter(":CreatorID", asset.Metadata.CreatorID));
cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
cmd.ExecuteNonQuery();
}
}
}
else
{
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
cmd.Parameters.Add(new SqliteParameter(":Flags", asset.Flags));
cmd.Parameters.Add(new SqliteParameter(":CreatorID", asset.Metadata.CreatorID));
cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
cmd.ExecuteNonQuery();
}
}
}
}
public void Test1()
{
Console.WriteLine("Test1 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
{
File.Delete(dbFilename);
}
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TEST_TABLE...");
cmd.CommandText = "SELECT COLA, COLB, COLC FROM TEST_TABLE";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("COLA"));
Console.WriteLine(" COLA: {0}", i);
string s = reader.GetString(reader.GetOrdinal("COLB"));
Console.WriteLine(" COLB: {0}", s);
DateTime dt = reader.GetDateTime(reader.GetOrdinal("COLC"));
Console.WriteLine(" COLB: {0}", dt.ToString("MM/dd/yyyy HH:mm:ss"));
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
SqliteCommand command = new SqliteCommand("PRAGMA table_info('TEST_TABLE')", con);
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
DisplayDataTable(dataTable, "Columns");
#endif
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test1 Done.");
}
private SqliteCommand BuildTableCommand()
{
var command = new SqliteCommand();
var stringBuilder = new StringBuilder($"CREATE TABLE IF NOT EXISTS {tableName.Quoted().ToString()} (");
string empty = string.Empty;
stringBuilder.AppendLine();
foreach (var column in this.tableDescription.Columns)
{
var columnName = ParserName.Parse(column).Quoted().ToString();
var columnTypeString = this.sqliteDbMetadata.TryGetOwnerDbTypeString(column.OriginalDbType, column.DbType, false, false, column.MaxLength, this.tableDescription.OriginalProvider, SqliteSyncProvider.ProviderType);
var columnPrecisionString = this.sqliteDbMetadata.TryGetOwnerDbTypePrecision(column.OriginalDbType, column.DbType, false, false, column.MaxLength, column.Precision, column.Scale, this.tableDescription.OriginalProvider, SqliteSyncProvider.ProviderType);
var columnType = $"{columnTypeString} {columnPrecisionString}";
// check case
string casesensitive = "";
if (this.sqliteDbMetadata.IsTextType(column.DbType))
{
casesensitive = this.tableDescription.CaseSensitive ? "" : "COLLATE NOCASE";
//check if it's a primary key, then, even if it's case sensitive, we turn on case insensitive
if (this.tableDescription.CaseSensitive)
{
if (this.tableDescription.PrimaryKey.Columns.Contains(column))
{
casesensitive = "COLLATE NOCASE";
}
}
}
var identity = string.Empty;
if (column.IsAutoIncrement)
{
var(step, seed) = column.GetAutoIncrementSeedAndStep();
if (seed > 1 || step > 1)
{
throw new NotSupportedException("can't establish a seed / step in Sqlite autoinc value");
}
//identity = $"AUTOINCREMENT";
// Actually no need to set AutoIncrement, if we insert a null value
identity = "";
}
var nullString = column.AllowDBNull ? "NULL" : "NOT NULL";
// if auto inc, don't specify NOT NULL option, since we need to insert a null value to make it auto inc.
if (column.IsAutoIncrement)
{
nullString = "";
}
// if it's a readonly column, it could be a computed column, so we need to allow null
else if (column.IsReadOnly)
{
nullString = "NULL";
}
stringBuilder.AppendLine($"\t{empty}{columnName} {columnType} {identity} {nullString} {casesensitive}");
empty = ",";
}
stringBuilder.Append("\t,PRIMARY KEY (");
for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++)
{
var pkColumn = this.tableDescription.PrimaryKey.Columns[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKey.Columns.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
// Constraints
foreach (var constraint in this.tableDescription.ParentRelations)
{
// Don't want foreign key on same table since it could be a problem on first
// sync. We are not sure that parent row will be inserted in first position
if (string.Equals(constraint.ParentTable.TableName, constraint.ChildTable.TableName, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
var parentTable = constraint.ParentTable;
var parentTableName = ParserName.Parse(parentTable.TableName).Quoted().ToString();
stringBuilder.AppendLine();
stringBuilder.Append($"\tFOREIGN KEY (");
empty = string.Empty;
foreach (var column in constraint.ChildColumns)
{
var columnName = ParserName.Parse(column).Quoted().ToString();
stringBuilder.Append($"{empty} {columnName}");
empty = ", ";
}
stringBuilder.Append($") ");
stringBuilder.Append($"REFERENCES {parentTableName}(");
empty = string.Empty;
foreach (var column in constraint.ParentColumns)
{
var columnName = ParserName.Parse(column).Quoted().ToString();
stringBuilder.Append($"{empty} {columnName}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
}
stringBuilder.Append(")");
return(new SqliteCommand(stringBuilder.ToString()));
}
private EstateSettings DoLoad(SqliteCommand cmd, UUID regionID, bool create)
{
EstateSettings es = new EstateSettings();
es.OnSave += StoreEstateSettings;
IDataReader r = null;
try
{
r = cmd.ExecuteReader();
}
catch (SqliteException)
{
m_log.Error("[SQLITE]: There was an issue loading the estate settings. This can happen the first time running OpenSimulator with CSharpSqlite the first time. OpenSimulator will probably crash, restart it and it should be good to go.");
}
if (r != null && r.Read())
{
foreach (string name in FieldList)
{
if (m_FieldMap[name].GetValue(es) is bool)
{
int v = Convert.ToInt32(r[name]);
if (v != 0)
m_FieldMap[name].SetValue(es, true);
else
m_FieldMap[name].SetValue(es, false);
}
else if (m_FieldMap[name].GetValue(es) is UUID)
{
UUID uuid = UUID.Zero;
UUID.TryParse(r[name].ToString(), out uuid);
m_FieldMap[name].SetValue(es, uuid);
}
else
{
m_FieldMap[name].SetValue(es, Convert.ChangeType(r[name], m_FieldMap[name].FieldType));
}
}
r.Close();
}
else if (create)
{
r.Close();
List<string> names = new List<string>(FieldList);
names.Remove("EstateID");
string sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( :"+String.Join(", :", names.ToArray())+")";
cmd.CommandText = sql;
cmd.Parameters.Clear();
foreach (string name in FieldList)
{
if (m_FieldMap[name].GetValue(es) is bool)
{
if ((bool)m_FieldMap[name].GetValue(es))
cmd.Parameters.AddWithValue(":"+name, "1");
else
cmd.Parameters.AddWithValue(":"+name, "0");
}
else
{
cmd.Parameters.AddWithValue(":"+name, m_FieldMap[name].GetValue(es).ToString());
}
}
cmd.ExecuteNonQuery();
cmd.CommandText = "select LAST_INSERT_ROWID() as id";
cmd.Parameters.Clear();
r = cmd.ExecuteReader();
r.Read();
es.EstateID = Convert.ToUInt32(r["id"]);
r.Close();
cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
cmd.Parameters.AddWithValue(":RegionID", regionID.ToString());
cmd.Parameters.AddWithValue(":EstateID", es.EstateID.ToString());
// This will throw on dupe key
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
es.Save();
}
LoadBanList(es);
es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
return es;
}
public void Test2()
{
Console.WriteLine("Test2 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
{
File.Delete(dbFilename);
}
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, 'ä¸æ–‡' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TBL...");
cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = 'ä¸æ–‡'";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("ID"));
Console.WriteLine(" ID: {0}", i);
string s = reader.GetString(reader.GetOrdinal("NAME"));
Console.WriteLine(" NAME: {0} = {1}", s, s == "ä¸æ–‡");
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
SqliteCommand command = new SqliteCommand("PRAGMA table_info('TEST_TABLE')", con);
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
DisplayDataTable(dataTable, "Columns");
#endif
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test1 Done.");
}
public static DataTable GetDataTable(this IDbContextCore context, string sql, params DbParameter[] parameters)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
var db = context.GetDatabase();
db.EnsureCreated();
var connection = db.GetDbConnection();
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var ds = new DataSet();
var dt = new DataTable();
DbCommand cmd;
DataAdapter da;
if (db.IsSqlServer())
{
cmd = new SqlCommand(sql, (SqlConnection)connection);
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
da = new SqlDataAdapter((SqlCommand)cmd);
}
else if (db.IsMySql())
{
cmd = new MySqlCommand(sql, (MySqlConnection)connection);
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
da = new MySqlDataAdapter((MySqlCommand)cmd);
}
else if (db.IsNpgsql())
{
cmd = new NpgsqlCommand(sql, (NpgsqlConnection)connection);
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
da = new NpgsqlDataAdapter((NpgsqlCommand)cmd);
}
else if (db.IsSqlite())
{
cmd = new SqliteCommand(sql, (SqliteConnection)connection);
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
dt = cmd.ExecuteReader().GetSchemaTable();
cmd.Dispose();
connection.Close();
return(dt);
}
else if (db.IsOracle())
{
cmd = new OracleCommand(sql, (OracleConnection)connection);
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
da = new OracleDataAdapter((OracleCommand)cmd);
}
else
{
throw new NotSupportedException("This method does not support current database yet.");
}
da.Fill(ds);
dt = ds.Tables[0];
da.Dispose();
connection.Close();
return(dt);
}
static void CheckForPatches()
{
using (var connection = new SqliteConnection(GetConnectionString()))
{
string[] expectedTables = { "assets", "clientSettings", "simulations" };
connection.Open();
if (!TablesExist(expectedTables, connection))
{
var sql = Resources.Load <TextAsset>("Database/simulator");
using (var command = new SqliteCommand(sql.text, connection))
{
command.ExecuteNonQuery();
}
}
connection.Close();
}
long currentVersion;
using (var connection = new SqliteConnection(GetConnectionString()))
{
connection.Open();
using (var command = new SqliteCommand(connection))
{
command.CommandText = "PRAGMA user_version";
using (SqliteDataReader reader = command.ExecuteReader())
{
currentVersion = reader.GetFieldValue <long>(0);
}
}
connection.Close();
}
Debug.Log($"Current Database Version: {currentVersion}");
for (long i = currentVersion + 1; ; i++)
{
TextAsset patch = Resources.Load <TextAsset>($"Database/Patches/{i}");
if (patch == null)
{
currentVersion = i - 1;
break;
}
string version = patch.name;
Debug.Log($"Applying patch {version} to database...");
using (var connection = new SqliteConnection(GetConnectionString()))
{
connection.Open();
using (var command = new SqliteCommand(connection))
{
command.CommandText = patch.text;
command.ExecuteNonQuery();
}
connection.Close();
}
}
using (var connection = new SqliteConnection(GetConnectionString()))
{
connection.Open();
using (var command = new SqliteCommand(connection))
{
command.CommandText = $"PRAGMA user_version = {currentVersion};";
command.ExecuteNonQuery();
}
connection.Close();
}
Debug.Log($"Final Database Version: {currentVersion}");
}
/// <summary>
/// Fetch Asset
/// </summary>
/// <param name="uuid">UUID of ... ?</param>
/// <returns>Asset base</returns>
override public AssetBase GetAsset(UUID uuid)
{
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
AssetBase asset = buildAsset(reader);
reader.Close();
return asset;
}
else
{
reader.Close();
return null;
}
}
}
}
}
public static Series GetIngredientReportChartA_B(long id, DateTime dateA, DateTime dateB)
{
if (dateA == dateB)
{
MessageBox.Show(
"تاریخ شروع و پایان یکسان هستند.",
"بازه تاریخ نامعتبر است",
MessageBoxButtons.OK,
MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1,
MessageBoxOptions.RtlReading
);
return(null);
}
using (SqliteConnection conn = new SqliteConnection(
ConfigurationManager.ConnectionStrings["FoodDB"].ConnectionString
))
{
conn.Open();
SqliteCommand command = new SqliteCommand(DateA_B_SQLcommand(dateA, dateB)
, conn);
command.Parameters.AddWithValue("$IngredientId", id);
if (dateB > dateA)
{
command.Parameters.AddWithValue("$ReportDateA", dateA.ToString(sqliteDateFormat));
}
else
{
command.Parameters.AddWithValue("$ReportDateA", dateB.ToString(sqliteDateFormat));
}
try
{
SqliteDataReader Report = command.ExecuteReader();
Series ReportSeries = new Series(GetIngredient(id).name);
if (Report.Read())
{
int daysBetween = dateB.Subtract(dateA).Days;
for (int i = 0; i < daysBetween; i++)
{
if (Report.IsDBNull(i))
{
ReportSeries.Points.AddXY(i + 1, 0.0d);
ReportSeries.Points[i].AxisLabel = Converter.ShamsiDay(dateA.AddDays(i));
}
else
{
ReportSeries.Points.AddXY(i + 1, Report.GetDouble(i));
ReportSeries.Points[i].AxisLabel = Converter.ShamsiDay(dateA.AddDays(i));
}
}
return(ReportSeries);
}
else
{
return(null);
}
}
catch (SqliteException ex)
{
MessageBox.Show(ex.Message);
return(null);
}
}
}
// /// <summary>
// /// Some... logging functionnality
// /// </summary>
// /// <param name="asset"></param>
// private static void LogAssetLoad(AssetBase asset)
// {
// string temporary = asset.Temporary ? "Temporary" : "Stored";
// string local = asset.Local ? "Local" : "Remote";
//
// int assetLength = (asset.Data != null) ? asset.Data.Length : 0;
//
// m_log.Debug("[ASSET DB]: " +
// string.Format("Loaded {5} {4} Asset: [{0}][{3}] \"{1}\":{2} ({6} bytes)",
// asset.FullID, asset.Name, asset.Description, asset.Type,
// temporary, local, assetLength));
// }
/// <summary>
/// Check if an asset exist in database
/// </summary>
/// <param name="uuid">The asset UUID</param>
/// <returns>True if exist, or false.</returns>
override public bool ExistsAsset(UUID uuid)
{
lock (this) {
using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
reader.Close();
return true;
}
else
{
reader.Close();
return false;
}
}
}
}
}
/// <summary>
/// This will query for all urdfs in the database.
/// </summary>
/// <returns>A list of all urdfs in the database.</returns>
public List <UrdfItemModel> GetUrdfs()
{
List <UrdfItemModel> list = new List <UrdfItemModel>();
string sql = @"SELECT
`uid`,
`name`,
`modelNumber`,
`internalCost`,
`externalCost`,
`weight`,
`powerUsage`,
`notes`,
`visibility`,
`fk_type_id`,
`fk_category_id`,
`usable`,
`urdfFilename`,
`prefabFilename`,
`time`
FROM `tblUrdfs`;";
SqliteCommand cmd = _engine.conn.CreateCommand();
cmd.CommandText = sql;
SqliteDataReader reader = cmd.ExecuteReader();
UrdfItemModel item;
try
{
while (reader.Read())
{
item = new UrdfItemModel();
item.uid = reader.GetInt32(0);
item.name = (!reader.IsDBNull(1) ? reader.GetString(1) : String.Empty);
item.modelNumber = (!reader.IsDBNull(2) ? reader.GetString(2) : String.Empty);
item.internalCost = (!reader.IsDBNull(3) ? reader.GetFloat(3) : 0.0f);
item.externalCost = (!reader.IsDBNull(4) ? reader.GetFloat(4) : 0.0f);
item.weight = (!reader.IsDBNull(5) ? reader.GetFloat(5) : 0.0f);
item.powerUsage = (!reader.IsDBNull(6) ? reader.GetFloat(6) : 0.0f);
item.notes = (!reader.IsDBNull(7) ? reader.GetString(7) : String.Empty);
item.visibility = (!reader.IsDBNull(8) ? reader.GetInt32(8) : 0);
item.fk_type_id = (!reader.IsDBNull(9) ? reader.GetInt32(9) : 0);
item.fk_category_id = (!reader.IsDBNull(10) ? reader.GetInt32(10) : 0);
item.usable = (!reader.IsDBNull(11) ? reader.GetInt32(11) : 0);
item.urdfFilename = (!reader.IsDBNull(12) ? reader.GetString(12) : String.Empty);
item.prefabFilename = (!reader.IsDBNull(13) ? reader.GetString(13) : String.Empty);
item.time = (!reader.IsDBNull(14) ? reader.GetFloat(14) : 0.0f);
list.Add(item);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
finally
{
reader.Close();
reader = null;
cmd.Dispose();
cmd = null;
}
return(list);
}
// TODO: (AlexRa): one of these is to be removed eventually (?)
/// <summary>
/// Delete an asset from database
/// </summary>
/// <param name="uuid"></param>
public bool DeleteAsset(UUID uuid)
{
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
cmd.ExecuteNonQuery();
}
}
return true;
}
public void Sync(IPhysicalPersonAttachmentService PhysicalPersonAttachmentService, Action <int, int> callback = null)
{
try
{
SyncPhysicalPersonAttachmentRequest request = new SyncPhysicalPersonAttachmentRequest();
request.CompanyId = MainWindow.CurrentCompanyId;
request.LastUpdatedAt = GetLastUpdatedAt(MainWindow.CurrentCompanyId);
int toSync = 0;
int syncedItems = 0;
PhysicalPersonAttachmentListResponse response = PhysicalPersonAttachmentService.Sync(request);
if (response.Success)
{
toSync = response?.PhysicalPersonAttachments?.Count ?? 0;
List <PhysicalPersonAttachmentViewModel> PhysicalPersonsFromDB = response.PhysicalPersonAttachments;
using (SqliteConnection db = new SqliteConnection(SQLiteHelper.SqLiteTableName))
{
db.Open();
using (var transaction = db.BeginTransaction())
{
SqliteCommand deleteCommand = db.CreateCommand();
deleteCommand.CommandText = "DELETE FROM PhysicalPersonAttachments WHERE Identifier = @Identifier";
SqliteCommand insertCommand = db.CreateCommand();
insertCommand.CommandText = SqlCommandInsertPart;
foreach (var PhysicalPerson in PhysicalPersonsFromDB)
{
deleteCommand.Parameters.AddWithValue("@Identifier", PhysicalPerson.Identifier);
deleteCommand.ExecuteNonQuery();
deleteCommand.Parameters.Clear();
if (PhysicalPerson.IsActive)
{
PhysicalPerson.IsSynced = true;
insertCommand = AddCreateParameters(insertCommand, PhysicalPerson);
insertCommand.ExecuteNonQuery();
insertCommand.Parameters.Clear();
syncedItems++;
callback?.Invoke(syncedItems, toSync);
}
}
transaction.Commit();
}
db.Close();
}
}
else
{
throw new Exception(response.Message);
}
}
catch (Exception ex)
{
MainWindow.ErrorMessage = ex.Message;
}
}
