/// <summary>
/// 保存本地设备所属科室信息
/// </summary>
public void SaveLocalDeviceOffice(string DeviceID, List <string> OfficeIDs, List <string> OfficeNames)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
conn.BeginTransaction();
try
{
string SqlText = @"Delete From [iDeviceOffice] Where [DeviceID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, DeviceID);
cmd.ExecuteNonQuery();
SqlText = @"Insert Into [iDeviceOffice] ([DOID],[DeviceID], [OfficeID],[OfficeName],[CreateDate]) Values (@1,@2,@3,@4,@5)";
cmd.CommandText = SqlText;
for (int i = 0; i < OfficeIDs.Count; i++)
{
cmd.ClearBindings();
cmd.Bind(Guid.NewGuid().ToString());
cmd.Bind(DeviceID);
cmd.Bind(OfficeIDs[i]); cmd.Bind(OfficeNames[i]);
cmd.Bind(iCommon.DateNow);
cmd.ExecuteNonQuery();
}
conn.Commit();
}
catch
{
conn.Rollback();
}
}
}
private EventArgs UpdateLocalData(SQLite.SQLiteConnection conn, string SqlDelete, string SqlInsert, List <Data.DataObject> DataList, string TableName, string PrimaryKey)
{
SQLite.SQLiteCommand cmdDelete = conn.CreateCommand(SqlDelete);
SQLite.SQLiteCommand cmdInsert = conn.CreateCommand(SqlInsert);
dynamic arg = null;
if (TableName == "DoctorCheckLog")
{
arg = new CheckLogSyncEventArgs();
}
foreach (Data.DataObject data in DataList)
{
cmdDelete.ClearBindings();
cmdInsert.ClearBindings();
cmdDelete.Bind(data.GetExtValue(PrimaryKey));
cmdDelete.ExecuteNonQuery();
if (data.GetExtValue("ChangeType") != "DELETE")
{
for (int d = 0; d < data.Data.Length - 1; d++)
{
cmdInsert.Bind(data.Data[d]);
}
cmdInsert.ExecuteNonQuery();
}
if (TableName == "DoctorCheckLog")
{
arg.DCLID.Add(data.GetExtValue(PrimaryKey));
arg.InhosID.Add(data.GetExtValue("InhosID"));
arg.ChangeType.Add(data.GetExtValue("ChangeType"));
arg.MedicalLog.Add(data.GetExtBytes("MedicalLog"));
arg.PhotoLog.Add(data.GetExtBytes("Photo"));
}
}
return(arg);
}
public bool InsertRecord(SQLite.SQLiteConnection m_dbConnection, string query, Logger logger)
{
bool isRecordInserted = false;
try
{
//SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;");
//m_dbConnection.Open();
//string isErrorString = IsError ? "1" : "0";
//string query = $"INSERT INTO {tableName}(FlatFileRowNumber, IsError) VALUES({flatFileRecordNumber},{isErrorString})";
SQLite.SQLiteCommand command = new SQLite.SQLiteCommand(m_dbConnection);
command.CommandText = query;
command.ExecuteNonQuery();
//m_dbConnection.Close();
isRecordInserted = true;
}
catch (Exception excp)
{
logger.Error("Error while inserting sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace);
}
return(isRecordInserted);
}
public void AddNote(string title, string description)
{
try{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "insert into tblNotes(title,description) values ('" + title + "','" + description + "')";
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
public void RememberCity(string name)
{
try{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "update tblCity set name='" + name + "' where cityID=1";
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
public void EditNote(string title, string description, int noteid)
{
try{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "update tblNotes set title='" + title + "', description='" + description + "' where noteID='" + noteid + "'";
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
public void DeleteNote(int noteid)
{
try{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "delete from tblNotes where noteID='" + noteid + "'";
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
public void AddItem(string title, string details)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "insert into tblQuickMemo(Title,Details) values('" + title + "','" + details + "')";
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
public void DeleteItem(int listid)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "delete from tblQuickMemo where Listid = " + listid;
cmd.ExecuteNonQuery();
}
} catch (Exception ex) {
Console.WriteLine("Error:" + ex.Message);
}
}
public void EditItem(string title, string details, int listid)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) {
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "update tblQuickMemo set Title='" + title + "', Details='" + details + "' where Listid=" + listid;
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
Console.WriteLine("Error:" + e.Message);
}
}
//Deleting Item to DataBase
public void DeleteItem(int noteid)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbpath))
{
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "delete from tblNotesListData where NotedId=" + noteid;
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.Write("Exception Ocuured", e.Message);
}
}
//Function for executing qurey to the database, pass in query string
public void runQuery(string query)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath))
{
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = query;
var rowcount = cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.WriteLine("Error:" + e.Message);
}
}
public void writeScores(string playersName, int playersScore)
{
try
{
using (var conn2 = new SQLite.SQLiteConnection(dbPath))
{
var cmd2 = new SQLite.SQLiteCommand(conn2);
cmd2.CommandText = "Insert into ScoreTable (UserName,Score) Values ('" + playersName + "', " + playersScore.ToString() + ")";
var executeCmd = cmd2.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void AddLeaderboard(string name, int score)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath))
{
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "insert into tblScores(Name, Score) values('" + name + "','" + score + "')";
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.WriteLine("Error:" + e.Message);
}
}
public void EditItem(string title, string details, int noteid)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath))
{
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "update tblNeatNotes set Title='" + title + "', NoteDetails='" + details + "' where NoteId=" + noteid;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine("Error:" + ex.Message);
}
}
//Adding Item to DataBase
public void AddItem(String Titel, String Details)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbpath))
{
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = "Insert into tblNotesListData(Title,Details) values('" + Titel + "','" + Details + "')";
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.Write("Exception Ocuured", e.Message);
}
}
} //----Makes changes to notes already stored
public void deleteNote(string id)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) //-----------------Prepares connection to database
{
var cmd = new SQLite.SQLiteCommand(conn); //--------------------------Prepares communication with database
string sql = "DELETE from main.NOTES_TBL WHERE NoteID = " + id; //---SQLite query to pass back
cmd.CommandText = sql; //---------------------------------------------Passes query to db
cmd.ExecuteNonQuery(); //---------------------------------------------Writes information to db
ViewAll(); //---------------------------------------------------------Gets a list of the notes and passes it in
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message); //------------------------------Write error in english to output
}
} //-----------------------------Removes notes
} //---Creates a new note
public void editNote(string title, string body, string id)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) //----------------------------------------------------------Prepares connection to database
{
var cmd = new SQLite.SQLiteCommand(conn); //-------------------------------------------------------------------Prepares communication with database
string sql = "UPDATE NOTES_TBL SET Title = '" + title + "', Body = '" + body + "' WHERE NoteID = " + id; //---SQLite query to pass back
cmd.CommandText = sql; //--------------------------------------------------------------------------------------Passes query to db
cmd.ExecuteNonQuery(); //--------------------------------------------------------------------------------------Writes information to db
ViewAll(); //--------------------------------------------------------------------------------------------------Gets a list of the notes and passes it in
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message); //-----------------------------------------------------------------------Write error in english to output
}
} //----Makes changes to notes already stored
public void DeleteCheckLog(Data.DoctorCheckLog CheckLog)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile))
{
string SqlCheckLog = @"Delete From [DoctorCheckLog] Where [DCLID]=?";
SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlCheckLog, CheckLog.DCLID);
cmd.ExecuteNonQuery();
Data.SyncLog slCheckLog = new SyncLog()
{
TableName = "[DoctorCheckLog]",
KeyField = "DCLID",
KeyValue = CheckLog.DCLID,
ChangeType = "DELETE"
};
SaveSyncLog(conn, slCheckLog);
}
}
} //-------------------------------Gets and passes in all the notes
public void writeNote(string title, string dt, string body)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) //--------------------------------------------------------------Prepares connection to database
{
var cmd = new SQLite.SQLiteCommand(conn); //-----------------------------------------------------------------------Prepares communication with database
string sql = "Insert into NOTES_TBL (Title, Body, Dt) values ('" + title + "','" + body + "', '" + dt + "')"; //---SQLite query to pass back
cmd.CommandText = sql; //------------------------------------------------------------------------------------------Passes query to db
cmd.ExecuteNonQuery(); //------------------------------------------------------------------------------------------Writes information to db
ViewAll(); //------------------------------------------------------------------------------------------------------Gets a list of the notes and passes it in
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message); //---------------------------------------------------------------------------Write error in english to output
}
} //---Creates a new note
//Updating Item to DataBase
public void EditItem(String Details, int noteid)
{
String text;
try
{
using (var conn = new SQLite.SQLiteConnection(dbpath))
{
var cmd = new SQLite.SQLiteCommand(conn);
text = "update tblNotesListData set Date='" + DateTime.Now + "',Details='" + Details + "' where NotedId=" + noteid;
Console.Write(text);
cmd.CommandText = text;
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.Write("Exception Ocuured", e.Message);
}
}
/// <summary>
/// 从服务器同步有变化的数据到本地
/// </summary>
/// <param name="ChangeTableList"></param>
/// <returns></returns>
private async Task <bool> SyncDataFromServer(List <Data.iTableChange> ChangeTableList, SQLite.SQLiteConnection conn, string IsInitData)
{
foreach (Data.iTableChange itc in ChangeTableList)
{
object Result = await RemoteExcute(new object[] { iCommon.DeviceID, itc.TableName, itc.PrimaryKey, IsInitData });
if (Result == null)
{
continue;
}
List <Data.DataObject> DataList = Deserialize <Data.DataObject>(Result as byte[]);
if (DataList == null || DataList.Count == 0)
{
continue;
}
string SqlDelete = String.Format(@"Delete From {0} Where {1}=?", itc.TableName, itc.PrimaryKey);
Data.DataObject bo = DataList[0];
string[] Fields = bo.FieldArray;
string SqlInsert = "Insert Into " + itc.TableName + " (";
string txtParams = " (";
for (int i = 0; i < Fields.Length - 1; i++)
{
SqlInsert += Fields[i] + ",";
txtParams += "?,";
}
SqlInsert = SqlInsert.TrimEnd(',');
txtParams = txtParams.TrimEnd(',');
SqlInsert = SqlInsert + ") Values " + txtParams + ")";
conn.BeginTransaction();
try
{
SQLite.SQLiteCommand cmdDelete = conn.CreateCommand(SqlDelete);
SQLite.SQLiteCommand cmdInsert = conn.CreateCommand(SqlInsert);
foreach (Data.DataObject data in DataList)
{
cmdDelete.ClearBindings();
cmdInsert.ClearBindings();
cmdDelete.Bind(data.GetExtValue(itc.PrimaryKey));
cmdDelete.ExecuteNonQuery();
if (data.GetExtValue("ChangeType") != "DELETE")
{
for (int d = 0; d < data.Data.Length - 1; d++)
{
cmdInsert.Bind(data.Data[d]);
}
cmdInsert.ExecuteNonQuery();
}
}
conn.Delete(itc);
conn.Insert(itc);
bool isSaveSuccess = await SavePadSyncLog(iCommon.DeviceID, itc.TableName, itc.LastChangeDate);
if (isSaveSuccess)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
catch (Exception ex)
{
conn.Rollback();
}
}
return(true);
}