using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
namespace WpfTest1.SQLite
{
public class SQLiteModel
{
public static string dbPath = Toolkits.Constants.dbPath;
#region 各类建表操作
//Patient表的创建
//Patient表的创建
public static void CreatePatientTable()
{
//如果不存在改数据库文件,则创建该数据库文件
if (!System.IO.File.Exists(dbPath))
{
SQLiteHelper.CreateDB(dbPath);
//Console.WriteLine("OK");
}
SQLiteHelper db = new SQLiteHelper(dbPath);
string sql = @"CREATE TABLE Patient(
id integer primary key autoincrement not null,
record_id varchar(100),
idNum varchar(20),
name varchar(20),
height float,
weight float,
base_heartrate integer,
pregnancy_times integer,
birth_times integer,
pregnancy_date Date,
mobile varchar(20),
profession varchar(40),
address varchar(140),
category varchar(40),
group_1 varchar(40),
firstLetterPY varchar(10),
description varchar(200),
lastRecordDate datetime,
delete_flag boolean
)";
db.ExecuteNonQuery(sql, null);
string sql_1 = "CREATE INDEX Patient_idNum on Patient(idNum)";
db.ExecuteNonQuery(sql_1, null);
}
//创建记录表
public static void CreateRecordTable()
{
//如果不存在改数据库文件,则创建该数据库文件
if (!System.IO.File.Exists(dbPath))
{
SQLiteHelper.CreateDB(dbPath);
}
SQLiteHelper db = new SQLiteHelper(dbPath);
string sql = @"CREATE TABLE Record(
id integer primary key autoincrement not null ,
patientId integer,
recordTime datetime ,
dataLeft blob,
dataRight blob,
dataWrist blob,
topBP integer,
bottomBP integer,
heartRate integer,
eigenValueSaved integer ,
comments varchar(200)
)";
db.ExecuteNonQuery(sql, null);
}
//创建医生表
public static void CreateDoctorTable()
{
//如果不存在改数据库文件,则创建该数据库文件
if (!System.IO.File.Exists(dbPath))
{
SQLiteHelper.CreateDB(dbPath);
}
SQLiteHelper db = new SQLiteHelper(dbPath);
string sql = @"CREATE TABLE Doctor(
id integer primary key autoincrement not null ,
name varchar(20),
pwd_hash varchar(32),
phone_number varchar(20),
director_flag boolean,
delete_flag boolean)";
db.ExecuteNonQuery(sql, null);
}
//创建CommonWords表
public static void CreateCommonWordsTable()
{
//如果不存在改数据库文件,则创建该数据库文件
if (!System.IO.File.Exists(dbPath))
{
SQLiteHelper.CreateDB(dbPath);
}
SQLiteHelper db = new SQLiteHelper(dbPath);
string sql = @"CREATE TABLE CommonWords(
id integer primary key autoincrement not null ,
content varchar(200),
describe varchar(20))";
db.ExecuteNonQuery(sql, null);
}
//创建表达式表
public static void CreateExpressionTable()
{
//如果不存在改数据库文件,则创建该数据库文件
if (!System.IO.File.Exists(dbPath))
{
SQLiteHelper.CreateDB(dbPath);
}
SQLiteHelper db = new SQLiteHelper(dbPath);
string sql = @"CREATE TABLE Expression(
id integer primary key autoincrement not null ,
expression_content varchar(500),
expression_description varchar(500)
)";
db.ExecuteNonQuery(sql, null);
}
//创建配置表
public static void CreateConfigTable()
{
//如果不存在改数据库文件,则创建该数据库文件
if (!System.IO.File.Exists(dbPath))
{
SQLiteHelper.CreateDB(dbPath);
}
SQLiteHelper db = new SQLiteHelper(dbPath);
string sql = @"CREATE TABLE Config(
key varchar(100) primary key not null ,
value varchar(500)
)";
db.ExecuteNonQuery(sql, null);
}
#endregion
#region 数据库整体操作
///
/// optmizeDatabase:从数据库中清空空闲列表
///
public static int optmizeDatabase()
{
string sql = "vacuum";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[] { };
return db.ExecuteNonQuery(sql, parameters);
}
#endregion
#region Patient表各类操作
//Patient表的插入
public static void InsertPatientData(string record_id,string name, string gender, double height, double weight,int pregnancy_times,DateTime birth_date,string phone,string address,string history,string diagnosis, string name_py)
{
string sql = "INSERT INTO Patient(p_record_id,p_name,p_gender,p_height,p_weight,p_pregnancy_time,p_birthdate,p_phone,p_address,p_history,p_diagnosis, p_name_py)"+
"values(@record_id,@name,@gender,@height,@weight,@pregnancy_times,@birthdate,@phone,@address,@history,@diagnosis,@name_py)";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@record_id",record_id),
new SQLiteParameter("@name",name),
new SQLiteParameter("@gender",gender),
new SQLiteParameter("@height",height),
new SQLiteParameter("@weight",weight),
new SQLiteParameter("@pregnancy_times",pregnancy_times),
new SQLiteParameter("@birthdate",birth_date),
new SQLiteParameter("@phone",phone),
new SQLiteParameter("@address",address),
new SQLiteParameter("@history",history),
new SQLiteParameter("@diagnosis",diagnosis),
new SQLiteParameter("@name_py",name_py),
};
db.ExecuteNonQuery(sql, parameters);
}
//Patient表的更新
public static void UpdatePatientData(string id, string record_id, string name, string gender, double height, double weight, int pregnancy_times, DateTime birth_date, string phone, string address, string history, string diagnosis, string name_py)
{
//Boolean a = false;
string sql = "UPDATE Patient SET p_record_id=@record_id,p_name=@name,p_gender=@gender,p_height=@height,p_weight=@weight,p_pregnancy_time=@pregnancy_times,p_birthdate=@birth_date,p_phone=@phone,p_address=@address,p_history=@history,p_diagnosis=@diagnosis,p_name_py=@name_py WHERE p_id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@record_id",record_id),
new SQLiteParameter("@name",name),
new SQLiteParameter("@gender",gender),
new SQLiteParameter("@height",height),
new SQLiteParameter("@weight",weight),
new SQLiteParameter("@pregnancy_times",pregnancy_times),
new SQLiteParameter("@birth_date",birth_date),
new SQLiteParameter("@phone",phone),
new SQLiteParameter("@address",address),
new SQLiteParameter("@history",history),
new SQLiteParameter("@diagnosis",diagnosis),
new SQLiteParameter("@name_py",name_py),
new SQLiteParameter("@id",id),
};
//System.Console.WriteLine(sql);
db.ExecuteNonQuery(sql, parameters);
}
//Patient表的仅更新最后XX时间的方法
//type 为 filter 或 evaluation
public static void UpdatePatientDataWithLastDate(int id, string type, DateTime lastRecordDate)
{
string colum_select = "p_last_filter_time";
if(type == "evaluation")
colum_select = "p_last_evaluation_time";
string sql = "UPDATE Patient SET " + colum_select + "=@lastRecordDate WHERE p_id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@lastRecordDate",lastRecordDate),
new SQLiteParameter("@id",id)
};
db.ExecuteNonQuery(sql, parameters);
}
//Patient表记录的删除
public static int DeletePatientItem(string id)
{
string sql = "UPDATE Patient SET p_delete_flag=1 WHERE p_id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@id",id)
};
return db.ExecuteNonQuery(sql, parameters);
}
///
/// getDeletedPatients:从Patient表中获取所有已被标记为删除的数据的id
///
public static List getDeletedPatients()
{
string sql = "SELECT p_id FROM Patient WHERE p_delete_flag = 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[] { };
List result = new List();
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
string id = reader.IsDBNull(0) ? "" : reader.GetString(0);
result.Add(id);
}
}
return result;
}
///
/// realDeleteRecords:从Patient表中真实删除记录
///
public static int realDeleteRecords()
{
string sql = "DELETE from Patient WHERE p_delete_flag=1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{ };
return db.ExecuteNonQuery(sql, parameters);
}
//检查Patient表是否重复，条件提病例号.重复返回true，否则返回false
public static bool checkRepeatPatient(string record_id)
{
string sql = "SELECT * FROM Patient WHERE p_record_id = @record_id AND p_delete_flag = 0";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@record_id",record_id)
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
reader.Close();
return true;
}
}
return false;
}
//通过id主键查找Patient
public static Patient getPatientById(string id)
{
string sql = "SELECT * FROM Patient WHERE p_id = @id AND p_delete_flag = 0 LIMIT 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@id",id),
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
Patient temp = new Patient();
temp.p_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.p_name = reader.IsDBNull(1) ? "" : reader.GetString(1);
temp.p_name_py = reader.IsDBNull(2) ? "" : reader.GetString(2);
temp.p_record_id = reader.IsDBNull(3) ? "" : reader.GetString(3);
temp.p_gender = reader.IsDBNull(4) ? "" : reader.GetString(4);
temp.p_birthdate = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);
temp.p_height = reader.IsDBNull(6) ? 0 : reader.GetDouble(6);
temp.p_weight = reader.IsDBNull(7) ? 0 : reader.GetDouble(7);
temp.p_pregnancy_time = reader.IsDBNull(8) ? 0 : reader.GetInt32(8);
temp.p_phone = reader.IsDBNull(9) ? "" : reader.GetString(9);
temp.p_address = reader.IsDBNull(10) ? "" : reader.GetString(10);
temp.p_history = reader.IsDBNull(11) ? "" : reader.GetString(11);
temp.p_diagnosis = reader.IsDBNull(12) ? "" : reader.GetString(12);
temp.doc_id = reader.IsDBNull(13) ? 0 : reader.GetInt32(13);
temp.p_last_filter_time = reader.IsDBNull(14) ? DateTime.MinValue : reader.GetDateTime(14);
temp.p_last_evaluation_time = reader.IsDBNull(15) ? DateTime.MinValue : reader.GetDateTime(15);
temp.p_delete_flag = reader.IsDBNull(16) ? false : reader.GetBoolean(16); ;
return temp;
}
}
return null;
}
#endregion
#region Question_template表的相关操作
public static List getQuestions(string type = "filter")
{
List questions = new List();
string condition = "q_type = 1";
if(type == "evaluation")
{
condition = "q_type = 2";
}
string sql = "SELECT * FROM Question_template WHERE " + condition + " order by q_number asc";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
Question temp = new Question();
temp.q_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.q_type = reader.IsDBNull(1) ? 0 : reader.GetInt32(1);
temp.q_number = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);
temp.q_content = reader.IsDBNull(3) ? "" : reader.GetString(3);
temp.filter_a_id = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
questions.Add(temp);
}
}
return questions;
}
public static Question getQuestionById(string qid)
{
string sql = "SELECT * FROM Question_template WHERE q_id=@qid LIMIT 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@qid",qid),
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
Question temp = new Question();
temp.q_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.q_type = reader.IsDBNull(1) ? 0 : reader.GetInt32(1);
temp.q_number = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);
temp.q_content = reader.IsDBNull(3) ? "" : reader.GetString(3);
temp.filter_a_id = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
return temp;
}
}
return null;
}
#endregion
#region Answer_template表相关操作
public static List getAnswersByQid(string qid)
{
List questions = new List();
string sql = "SELECT * FROM Answer_template WHERE q_id=@qid order by a_id asc";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@qid",qid),
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
Answer temp = new Answer();
temp.a_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.q_id = reader.IsDBNull(1) ? 0 : reader.GetInt32(1);
temp.a_content = reader.IsDBNull(2) ? "" : reader.GetString(2);
temp.a_value = reader.IsDBNull(3) ? 0 : reader.GetInt32(3);
temp.evaluation_q_id = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
temp.a_description_text = reader.IsDBNull(5) ? "" : reader.GetString(5);
temp.a_description_img = reader.IsDBNull(6) ? "" : reader.GetString(6);
temp.a_suggestion_text = reader.IsDBNull(7) ? "" : reader.GetString(7);
temp.a_suggestion_img = reader.IsDBNull(8) ? "" : reader.GetString(8);
questions.Add(temp);
}
}
return questions;
}
public static Answer getAnswerById(string aid)
{
string sql = "SELECT * FROM Answer_template WHERE a_id=@aid LIMIT 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@aid",aid),
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
Answer temp = new Answer();
temp.a_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.q_id = reader.IsDBNull(1) ? 0 : reader.GetInt32(1);
temp.a_content = reader.IsDBNull(2) ? "" : reader.GetString(2);
temp.a_value = reader.IsDBNull(3) ? 0 : reader.GetInt32(3);
temp.evaluation_q_id = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
temp.a_description_text = reader.IsDBNull(5) ? "" : reader.GetString(5);
temp.a_description_img = reader.IsDBNull(6) ? "" : reader.GetString(6);
temp.a_suggestion_text = reader.IsDBNull(7) ? "" : reader.GetString(7);
temp.a_suggestion_img = reader.IsDBNull(8) ? "" : reader.GetString(8);
return temp;
}
}
return null;
}
#endregion
#region Record表相关操作
public static void insertRecord(int r_type, int p_id, int d_id, int filter_id, DateTime r_time, int r_count, string r_selection)
{
string sql = "INSERT INTO Record(r_type,p_id,d_id,filter_id,r_time,r_count,r_selection)" +
"values(@r_type,@p_id,@d_id,@filter_id,@r_time,@r_count,@r_selection)";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@r_type",r_type),
new SQLiteParameter("@p_id",p_id),
new SQLiteParameter("@d_id",d_id),
new SQLiteParameter("@filter_id",filter_id),
new SQLiteParameter("@r_time",r_time),
new SQLiteParameter("@r_count",r_count),
new SQLiteParameter("@r_selection",r_selection)
};
db.ExecuteNonQuery(sql, parameters);
}
#endregion
#region doctor表相关的操作
//医生登录操作
public static doctor doctorLogin(string name,string passwordHash)
{
string sql = "SELECT * FROM Doctor WHERE name = @name and pwd_hash=@passwordHash and delete_flag =0 LIMIT 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@name",name),
new SQLiteParameter("@passwordHash",passwordHash),
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
doctor oneDoctor = new doctor();
oneDoctor.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
oneDoctor.name = name;
oneDoctor.phone_number = reader.IsDBNull(3) ? "" : reader.GetString(3);
oneDoctor.director_flag = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
oneDoctor.privilege_flag = reader.IsDBNull(6) ? -1 : reader.GetInt32(6);
return oneDoctor;
}
}
return null;
}
//主任登录操作
public static doctor directorLogin(string name, string passwordHash)
{
string sql = "SELECT * FROM Doctor WHERE name = @name and pwd_hash=@passwordHash and delete_flag = 0 and director_flag = 1 LIMIT 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@name",name),
new SQLiteParameter("@passwordHash",passwordHash),
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
doctor oneDoctor = new doctor();
oneDoctor.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
oneDoctor.name = name;
oneDoctor.phone_number = reader.IsDBNull(3) ? "" : reader.GetString(3);
oneDoctor.director_flag = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
return oneDoctor;
}
}
return null;
}
//厂商登录操作
public static doctor OEMLogin(string name, string passwordHash)
{
string sql = "SELECT * FROM Doctor WHERE name = @name and pwd_hash=@passwordHash and delete_flag = 0 and privilege_flag = 1 LIMIT 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@name",name),
new SQLiteParameter("@passwordHash",passwordHash),
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
doctor oneDoctor = new doctor();
oneDoctor.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
oneDoctor.name = name;
oneDoctor.phone_number = reader.IsDBNull(3) ? "" : reader.GetString(3);
oneDoctor.director_flag = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
return oneDoctor;
}
}
return null;
}
//查询医生用户名是否存在 存在返回true
public static bool checkRepeatDoctor(string name)
{
string sql = "SELECT * FROM Doctor WHERE name = @name AND delete_flag = 0";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@name",name)
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
reader.Close();
return true;
}
}
return false;
}
//通过id主键查找Doctor
public static doctor getDoctorById(string id)
{
string sql = "SELECT * FROM Doctor WHERE id = @id AND delete_flag = 0 LIMIT 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@id",id),
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
doctor temp = new doctor();
temp.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.name = reader.IsDBNull(1) ? "" : reader.GetString(1);
temp.pwd_hash = reader.IsDBNull(2) ? "" : reader.GetString(2);
temp.phone_number = reader.IsDBNull(3) ? "" : reader.GetString(3);
temp.director_flag = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
return temp;
}
}
return null;
}
//查找Doctor 返回目前还使用的医生id
public static int getDoctorIdByNoDelete()
{
string sql = "SELECT * FROM Doctor WHERE delete_flag = 0 LIMIT 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
doctor temp = new doctor();
temp.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.name = reader.IsDBNull(1) ? "" : reader.GetString(1);
temp.pwd_hash = reader.IsDBNull(2) ? "" : reader.GetString(2);
temp.phone_number = reader.IsDBNull(3) ? "" : reader.GetString(3);
temp.director_flag = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
return temp.id;
}
}
return -1;
}
//Doctort表的插入
public static void InsertDoctorData(string name, string pwd_hash, string phone_number, Boolean director_flag = false, Boolean delete_flag = false)
{
string sql = "INSERT INTO Doctor(name,pwd_hash,phone_number,director_flag,delete_flag)values(@name,@pwd_hash,@phone_number,@director_flag,@delete_flag)";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@name",name),
new SQLiteParameter("@pwd_hash",pwd_hash),
new SQLiteParameter("@phone_number",phone_number),
new SQLiteParameter("@director_flag",director_flag),
new SQLiteParameter("@delete_flag",delete_flag)
};
db.ExecuteNonQuery(sql, parameters);
}
//Doctor表的更新
public static void UpdateDoctorData(string id, string name, string pwd_hash, string phone_number, Boolean director_flag = false, Boolean delete_flag = false)
{
//Boolean a = false;
string sql = "UPDATE Doctor SET name=@name,pwd_hash=@pwd_hash,phone_number=@phone_number,director_flag=@director_flag,delete_flag=@delete_flag WHERE id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@name",name),
new SQLiteParameter("@pwd_hash",pwd_hash),
new SQLiteParameter("@phone_number",phone_number),
new SQLiteParameter("@director_flag",director_flag),
new SQLiteParameter("@delete_flag",delete_flag),
new SQLiteParameter("@id",id)
};
db.ExecuteNonQuery(sql, parameters);
}
//Doctor表记录的删除
public static int DeleteDoctorItem(string id)
{
string sql = "UPDATE Doctor SET delete_flag=1 WHERE id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@id",id)
};
return db.ExecuteNonQuery(sql, parameters);
}
#endregion
#region 配置表Config的相关操作
//查找所有的expression的content列
public static void GetAllConfigContent(Dictionary target)
{
target.Clear();
string sql = "SELECT key,value FROM Config WHERE 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
string tempKey = reader.IsDBNull(0) ? "" : reader.GetString(0);
string tempValue = reader.IsDBNull(1) ? "" : reader.GetString(1);
if (tempKey == "")
{
break;
}
//KeyValuePair temp = new KeyValuePair(tempKey,0);
target.Add(tempKey, tempValue);
}
}
}
//更新激活码信息
public static void UpdateRegisterCode(string name, string pw, string code)
{
//Boolean a = false;
string sql = "UPDATE Config SET value=@name WHERE key='organization_name'";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@name",name)
};
db.ExecuteNonQuery(sql, parameters);
sql = "UPDATE Config SET value=@pw WHERE key='organization_pw'";
db = new SQLiteHelper(dbPath);
parameters = new SQLiteParameter[]{
new SQLiteParameter("@pw",pw)
};
db.ExecuteNonQuery(sql, parameters);
sql = "UPDATE Config SET value=@code WHERE key='organization_active_code'";
db = new SQLiteHelper(dbPath);
parameters = new SQLiteParameter[]{
new SQLiteParameter("@code",code)
};
db.ExecuteNonQuery(sql, parameters);
}
//更新血压单位显示hhmg或者kpa，如果为1则是hhmg
public static void UpdateDocSetting(string value)
{
//Boolean a = false;
string sql = "UPDATE Config SET value=@value WHERE key='bp_unit_mmhg'";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@value",value)
};
db.ExecuteNonQuery(sql, parameters);
}
//更新平滑参数
public static void UpdateSmoothPara(int ori_pts, int ori_times, int dri_pts, int dri_times, int filter_when_saving)
{
//Boolean a = false;
string sql = "UPDATE Config SET value=@ori_pts WHERE key='original_smooth_points'";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@ori_pts",ori_pts)
};
db.ExecuteNonQuery(sql, parameters);
sql = "UPDATE Config SET value=@ori_times WHERE key='original_smooth_times'";
db = new SQLiteHelper(dbPath);
parameters = new SQLiteParameter[]{
new SQLiteParameter("@ori_times",ori_times)
};
db.ExecuteNonQuery(sql, parameters);
sql = "UPDATE Config SET value=@dri_pts WHERE key='derive_smooth_points'";
db = new SQLiteHelper(dbPath);
parameters = new SQLiteParameter[]{
new SQLiteParameter("@dri_pts",dri_pts)
};
db.ExecuteNonQuery(sql, parameters);
sql = "UPDATE Config SET value=@dri_times WHERE key='derive_smooth_times'";
db = new SQLiteHelper(dbPath);
parameters = new SQLiteParameter[]{
new SQLiteParameter("@dri_times",dri_times)
};
db.ExecuteNonQuery(sql, parameters);
sql = "UPDATE Config SET value=@filter_when_saving WHERE key='filter_when_saving'";
db = new SQLiteHelper(dbPath);
parameters = new SQLiteParameter[]{
new SQLiteParameter("@filter_when_saving",filter_when_saving)
};
db.ExecuteNonQuery(sql, parameters);
}
//只更新特征值数列的平滑参数
public static void UpdateEigenSmoothPara(int egi_pts, int egi_times)
{
//Boolean a = false;
string sql = "UPDATE Config SET value=@egi_pts WHERE key='eigen_smooth_points'";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@egi_pts",egi_pts)
};
db.ExecuteNonQuery(sql, parameters);
sql = "UPDATE Config SET value=@egi_times WHERE key='eigen_smooth_times'";
db = new SQLiteHelper(dbPath);
parameters = new SQLiteParameter[]{
new SQLiteParameter("@egi_times",egi_times)
};
db.ExecuteNonQuery(sql, parameters);
}
//更新报告中report_figure_name
public static void UpdateReportFigureName(string new_name)
{
string sql = "UPDATE Config SET value=@new_name WHERE key='report_figure_name'";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@new_name",new_name)
};
db.ExecuteNonQuery(sql, parameters);
}
//更新主公式等参数
public static void UpdateGeneralExpression(string new_segment_name, string segement_threhold, string new_expression)
{
string sql = "UPDATE Config SET value=@new_segment_name WHERE key='segement_name'";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@new_segment_name",new_segment_name)
};
db.ExecuteNonQuery(sql, parameters);
sql = "UPDATE Config SET value=@segement_threhold WHERE key='segement_threhold'";
db = new SQLiteHelper(dbPath);
parameters = new SQLiteParameter[]{
new SQLiteParameter("@segement_threhold",segement_threhold)
};
db.ExecuteNonQuery(sql, parameters);
sql = "UPDATE Config SET value=@new_expression WHERE key='general_expression'";
db = new SQLiteHelper(dbPath);
parameters = new SQLiteParameter[]{
new SQLiteParameter("@new_expression",new_expression)
};
db.ExecuteNonQuery(sql, parameters);
}
#endregion
#region 以下是测试功能，请在生产环境之前删除这些功能
public static byte[] getRecordData()
{
string dbPathTest = @"D:\\sourceCode\\github\\Junde_New\bin\\Debug\\data\\data.db";
string sql = "SELECT Data FROM Record WHERE id = 22";
SQLiteHelper db = new SQLiteHelper(dbPathTest);
Console.WriteLine("database ok!");
using (SQLiteDataReader reader = db.ExecuteReader(sql, null))
{
byte[] buffer = null;
if (reader.HasRows)
{
reader.Read();
long len = reader.GetBytes(reader.GetOrdinal("Data"), 0, null, 0, 0);
Console.WriteLine("len is :" + len.ToString());
buffer = new byte[len];
len = reader.GetBytes(reader.GetOrdinal("Data"), 0, buffer, 0, (int)len);
for(int i =0;i<100;i++)
{
Console.WriteLine(buffer[i]);
}
return buffer;
//System.IO.MemoryStream ms = new System.IO.MemoryStream(buffer);
//System.Drawing.Image iamge = System.Drawing.Image.FromStream(ms);
//pictureBox1.Image = iamge;
}
}
return null;
}
#endregion
}
}