public int GetTotalRecordsInTable(SQLite.SQLiteConnection m_dbConnection, string tableName, Logger logger) { int records = 0;// new DataSet(); try { string query = $"SELECT count(1) TotalRecords FROM {tableName};"; //records = m_dbConnection.Query<ProcessedDetails1>(query).Count; SQLite.SQLiteCommand command = new SQLite.SQLiteCommand(m_dbConnection); command.CommandText = query; records = command.ExecuteScalar <int>(); //SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;"); //m_dbConnection.Open(); //SQLiteDataAdapter myAdapter = new SQLiteDataAdapter(m_dbConnection); ////myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; //DataSet myDataSet = new DataSet(); //myAdapter.Fill(myDataSet, "Records"); //if (myDataSet.Tables[0].Rows.Count > 0) //{ // records = Convert.ToInt32(myDataSet.Tables[0].Rows[0][0].ToString()); //} //m_dbConnection.Close(); } catch (Exception excp) { logger.Error("Error while retrieving from sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace); } return(records); }
/// <summary> /// 保存一个查房日志到本地 /// </summary> /// <param name="CheckLog"></param> public void SaveCheckLogToLocal(Data.DoctorCheckLog CheckLog) {//To do:修改病人的同步标志,标识需同步查房日志 using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlCheckLog = @"Select * From [DoctorCheckLog] Where [DCLID]=?"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlCheckLog, CheckLog.DCLID); Data.DoctorCheckLog existCheckLog = cmd.ExecuteQuery <DoctorCheckLog>().SingleOrDefault(); Data.SyncLog slCheckLog = new SyncLog() { TableName = "[DoctorCheckLog]", KeyField = "DCLID", KeyValue = CheckLog.DCLID, ChangeType = "INSERT" }; if (existCheckLog == null) { conn.Insert(CheckLog); } else { conn.Update(CheckLog); slCheckLog.ChangeType = "UPDATE"; } SaveSyncLog(conn, slCheckLog); } }
private async Task <bool> SyncLocalData(SQLite.SQLiteConnection conn, Data.SyncLog sl) { SQLite.SQLiteCommand cmd = conn.CreateCommand(String.Format(SqlText, sl.TableName, sl.KeyField), sl.KeyValue); dynamic Result; switch (sl.TableName) { case "DoctorCheckLog": Result = cmd.ExecuteQuery <Data.DoctorCheckLog>().SingleOrDefault(); break; case "PadAdvice": Result = cmd.ExecuteQuery <Data.PadAdvice>().SingleOrDefault(); break; default: return(false); } if (Result != null) { object[] Data = sl.ChangeType != "DELETE" ? Result.Data : null; string[] Fields = Result.FieldArray; object SyncResult = await RemoteExcute(new object[] { sl.DeviceID, sl.InhosID, sl.TableName, sl.KeyField, sl.KeyValue, sl.ChangeType, sl.ChangeDate, Fields, Data }); if (SyncResult != null && (bool)SyncResult) { conn.Delete(sl); } } return(true); }
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); }
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); }
/// <summary> /// 查询有变化的数据表 /// </summary> /// <returns></returns> private async Task <bool> QueryDeviceChangeTable() { string isInitData; List <string> TableList = new List <string>(); using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [iDeviceTableChange]"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText); List <Data.iDeviceTableChange> TableChangeList = cmd.ExecuteQuery <Data.iDeviceTableChange>(); foreach (Data.iDeviceTableChange itc in TableChangeList) { TableList.Add(itc.TableName + "|" + itc.LastChangeDate); } isInitData = TableChangeList.Count == 0 ? "0" : "1"; //0:表示初始数据(新安装)1:表示正常数据同步 } object Result = await RemoteExcute(new object[] { iCommon.DeviceID, TableList.ToArray() }); List <Data.iDeviceTableChange> ChangeTableList = Deserialize <Data.iDeviceTableChange>(Result as byte[]); if (ChangeTableList == null) { return(false); } return(await SyncDeviceDataFromServer(ChangeTableList, isInitData)); }
/// <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(); } } }
/// <summary> /// 查询本地病人LIS结果 /// </summary> /// <param name="SampleNo">样本号</param> /// <param name="TestDate">检验日期</param> public List <Data.LisResult> QueryLocalLisResult(string IllcaseNo) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [LisResult] Where [IllcaseNo]=? Order By [ReportDate] Desc"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, IllcaseNo); var Result = cmd.ExecuteQuery <LisResult>(); return(Result); } }
/// <summary> /// 查询本地病人住院医嘱信息 /// </summary> /// <param name="InhosID">住院流水号</param> public List <Data.PatientAdvices> QueryLocalPatientAdvices(string InhosID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select pa.* From [PatientAdvices] pa Where pa.[InhosID]=? Order By [ListDate]"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID); var Result = cmd.ExecuteQuery <PatientAdvices>(); return(Result); } }
/// <summary> /// 查询本地医嘱子类别 /// </summary> public List <Data.iType2> QueryLocalSubType(string AdviceTypeID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [iType2] Where [AdviceTypeID]=?"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, AdviceTypeID); var Result = cmd.ExecuteQuery <iType2>(); return(Result); } }
/// <summary> /// 查询本地医嘱类别 /// </summary> public List <Data.iCode> QueryLocalAdviceType() { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [iCode] Where [GroupName]=?"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, "医嘱类别"); var Result = cmd.ExecuteQuery <iCode>(); return(Result); } }
/// <summary> /// 查询病人的查房日志 /// </summary> /// <param name="InhosID"></param> /// <returns></returns> public List <DoctorCheckLog> QueryLocalPatientCheckPhoto(string InhosID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [DoctorCheckLog] Where[InhosID]=? And [LogType]=? Order By CheckDate||CheckTime Desc"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID, "照片"); var Result = cmd.ExecuteQuery <DoctorCheckLog>(); return(Result); } }
/// <summary> /// 获取本地值班病人 /// </summary> /// <param name="DoctorID"></param> /// <returns></returns> public List <Patient> QueryLocalTodayPatientList(string DoctorID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [Patient] p Where Exists (Select 1 From [DoctorPatients] dp Where dp.[DoctorID]=? And dp.[InhosID] = p.[InhosID] And dp.[DPType] = ? And dp.[CreateDate]=?)"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, DoctorID, "近期病人", iCommon.Today); var Result = cmd.ExecuteQuery <Patient>(); return(Result); } }
/// <summary> /// 查询本地检查结果 /// </summary> public List <Data.CheckResult> QueryLocalCheckResult(string InhosID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select cr.* From [CheckApply] ca,[CheckResult] cr Where ca.[InhosID] = @1 and ca.[ApplyNo] = cr.[ApplyNo] Order By [SerialNo]"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID); var Result = cmd.ExecuteQuery <CheckResult>(); return(Result); } }
/// <summary> /// 查询本地检查申请 /// </summary> /// <param name="InhosID"></param> public List <Data.CheckApply> QueryLocalCheckApply(string InhosID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [CheckApply] Where [InhosID]=? Order By [ReportDate] Desc"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID); var Result = cmd.ExecuteQuery <CheckApply>(); return(Result); } }
/// <summary> /// 获取本地科室病人 /// </summary> /// <param name="OfficeID"></param> /// <returns></returns> public List <Patient> QueryLocalOfficePatientList(string OfficeID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select p.* From [Patient] p Where p.[OfficeID]=? And p.[InhosStatus]=?"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, OfficeID, "正住院"); var Result = cmd.ExecuteQuery <Patient>(); return(Result); } }
/// <summary> /// 查询所有本地代码信息 /// </summary> public List <Data.iCode> QueryLocalCodeList() { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [iCode]"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText); var Result = cmd.ExecuteQuery <iCode>(); return(Result); } }
/// <summary> /// 获取本地科室病人 /// </summary> /// <param name="DoctorID"></param> /// <returns></returns> public List <Patient> QueryLocalHistoryPatientList(string DoctorID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [Patient] p Where p.[BedDoctor]=? And p.[InhosStatus]=?"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, DoctorID, "已出院"); var Result = cmd.ExecuteQuery <Patient>(); return(Result); } }
/// <summary> /// 查询本地病人病历文书信息 /// </summary> /// <param name="InhosID">住院流水号</param> public List <Data.PatientEmrDoc> QueryLocalEmrDocHeader(string InhosID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select [InhosID],[SectionNo],[DocTypeID],[DocTypeName],[Title],[Creator],[CreateDate],[CheckDate] From [PatientEmrDoc] pa Where pa.[InhosID]=?"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID); var Result = cmd.ExecuteQuery <PatientEmrDoc>(); return(Result); } }
public int QueryLoaclDataCount() { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select Count(1) From [Patient]"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText); var Result = cmd.ExecuteScalar <string>(); return(Convert.ToInt32(Result)); } }
/// <summary> /// 查询本地病人预交款 /// </summary> /// <param name="InhosID">住院流水号</param> public List <Data.PatientPrepay> QueryLocalPrepay(string InhosID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [PatientPrepay] Where [InhosID]=?"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, InhosID); var Result = cmd.ExecuteQuery <PatientPrepay>(); return(Result); } }
/// <summary> /// 查询本地设备所属科室信息 /// </summary> public List <Data.iDeviceOffice> QueryLocalDeviceOffice(string DeviceID) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [iDeviceOffice] Where [DeviceID]=?"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, DeviceID); var Result = cmd.ExecuteQuery <iDeviceOffice>(); return(Result); } }
/// <summary> /// 查询本地医嘱子类别 /// </summary> public List <Data.iAdvice> QueryLocalAdvice(string StandingFlag, string AdviceTypeID, string AdviceFilter) { using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(iDB.DbFile)) { string SqlText = @"Select * From [iAdvice] Where [AdviceType]=? And [AdviceFilter] Like '%'||?||'%' And ([StandingType]='" + StandingFlag + "' Or [StandingType]='9')"; SQLite.SQLiteCommand cmd = conn.CreateCommand(SqlText, AdviceTypeID, AdviceFilter); var Result = cmd.ExecuteQuery <iAdvice>(); return(Result); } }
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 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 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 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); } }
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 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); } }