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 int InsertPatientAndReturnID(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);
//Console.WriteLine(db.ExecuteScalar("select last_insert_rowid() from Patient",null).ToString());
//int new_id = Int32.Parse(db.ExecuteScalar("select id from Patient order by id desc", parameters).ToString());
int new_id = Convert.ToInt32(db.ExecuteFindNewID("select max(id) from Patient;"));
//int new_id = Convert.ToInt32(db.ExecuteFindNewID("select last_insert_rowid(id) from Patient;"));
//Console.WriteLine(new_id);
//return (Int32)db.ExecuteScalar("select last_insert_rowid();", new SQLiteParameter[] { });
return new_id;
}
//Patient表的更新 不更新生日
public static void UpdatePatientData2_useless(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 = @mobile,p_address=@address,p_history=@history,p_diagnosis=@diagnosis,p_name_py=@name_py WHERE 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("@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 UpdatePatientDataWithLastFilterDate(string 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;
}
//通过身份证号、孕次、产次查找Patient
public static Patient getPatientByIdNum_pregnancy_brith_useless(string idNum,int pregnancy_times, int birth_times)
{
string sql = "SELECT * FROM Patient WHERE idNUm = @idNum AND pregnancy_times = @pregnancy_times AND birth_times = @birth_times AND delete_flag = 0 LIMIT 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@idNum",idNum),
new SQLiteParameter("@pregnancy_times",pregnancy_times),
new SQLiteParameter("@birth_times",birth_times),
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{/*
Patient temp = new Patient();
temp.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.record_id = reader.IsDBNull(1) ? "" : reader.GetString(1);
temp.idNum = reader.IsDBNull(2) ? "" : reader.GetString(2);
temp.name = reader.IsDBNull(3) ? "" : reader.GetString(3);
temp.height = reader.IsDBNull(4) ? 0 : reader.GetDouble(4);
temp.weight = reader.IsDBNull(5) ? 0 : reader.GetDouble(5);
temp.base_heartrate = reader.IsDBNull(6) ? 0 : reader.GetInt32(6);
temp.pregnancy_times = reader.IsDBNull(7) ? 0 :reader.GetInt32(7);
temp.birth_times = reader.IsDBNull(8) ? 0 : reader.GetInt32(8);
temp.pregnancy_date = reader.IsDBNull(9) ? DateTime.MinValue : reader.GetDateTime(9);
temp.mobile = reader.IsDBNull(10)? "" : reader.GetString(10);
temp.profession = reader.IsDBNull(11) ? "" : reader.GetString(11);
temp.address = reader.IsDBNull(12) ? "" : reader.GetString(12);
temp.category = reader.IsDBNull(13) ? "" : reader.GetString(13);
temp.group_1 = reader.IsDBNull(14) ? "" : reader.GetString(14);
temp.firstLetterPY = reader.IsDBNull(15) ? "" : reader.GetString(15);
temp.description = reader.IsDBNull(16) ? "" : reader.GetString(16);
return temp;*/
}
}
return null;
}
//通过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;
}
//根据拼音获取patient记录
public static void getPatientByPY_useless(string py)
{
//查询从20条起的50条记录
string sql = "SELECT * FROM Patient WHERE firstLetterPY like\'%" + py + "%\' AND delete_flag=0";
SQLiteHelper db = new SQLiteHelper(dbPath);
using (SQLiteDataReader reader = db.ExecuteReader(sql, null))
{
while (reader.Read())
{
Console.WriteLine("姓名:{0},职业:{1}", reader.GetString(2), reader.GetString(9));
}
}
}
//根据姓名获取patient记录
public static void getPatientByName_useless(string name)
{
string sql = "SELECT * FROM Patient WHERE name like\'%" + name+ "%\' AND delete_flag=0";
Console.WriteLine(sql);
SQLiteHelper db = new SQLiteHelper(dbPath);
using (SQLiteDataReader reader = db.ExecuteReader(sql, null))
{
while (reader.Read())
{
//Console.WriteLine("DDDDDDDDDDDDDDDDDDDD");
Console.WriteLine("姓名:{0},职业:{1}", reader.GetString(2), reader.GetString(9));
}
}
}
//根据condition获取patient记录
public static DataView getPatientByCondition_useless(string condition)
{
/*
*
string sql = "SELECT * FROM Patient WHERE delete_flag=0 " + condition;
Console.WriteLine(sql);
SQLiteHelper db = new SQLiteHelper(dbPath);
using (SQLiteDataReader reader = db.ExecuteReader(sql, null))
{
return reader;
while (reader.Read())
{
//Console.WriteLine("DDDDDDDDDDDDDDDDDDDD");
Console.WriteLine("姓名:{0},职业:{1}", reader.GetString(2), reader.GetString(9));
}
}
*/
//var path1 = "d:\\temp\\temp.db";
string sql = "SELECT * FROM Patient WHERE delete_flag=0 " + condition;
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = dbPath;
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
conn.ConnectionString = connstr.ToString();
conn.Open();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataView dv = ds.Tables[0].DefaultView;
conn.Close();
conn.Dispose();
return dv;
}
//获取Patient表的用户所有分类和所有用户组
public static List getCategory_useless(string taskType="category")
{
string sql = "SELECT " + taskType + " FROM Patient WHERE delete_flag = 0 group by " + taskType;
List result = new List();
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{ };
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
if (reader.IsDBNull(0))
continue;
result.Add(reader.GetString(0));
}
reader.Close();
}
return result;
}
#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 常用语CommonWords表操作
//通过id主键查找CommonWords
public static CommonWords getCommonWordsById(string id)
{
string sql = "SELECT * FROM CommonWords WHERE id = @id 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())
{
CommonWords temp = new CommonWords();
temp.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.content = reader.IsDBNull(1) ? "" : reader.GetString(1);
temp.describe = reader.IsDBNull(2) ? "" : reader.GetString(2);
return temp;
}
}
return null;
}
//CommonWords表的插入
public static void InsertCommonWordsData(string content, string describe)
{
string sql = "INSERT INTO CommonWords(content,describe)values(@content,@describe)";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@content",content),
new SQLiteParameter("@describe",describe)
};
db.ExecuteNonQuery(sql, parameters);
}
//CommonWords表的更新
public static void UpdateCommonWordsData(string id, string content, string describe)
{
//Boolean a = false;
string sql = "UPDATE CommonWords SET content=@content,describe=@describe WHERE id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@content",content),
new SQLiteParameter("@describe",describe),
new SQLiteParameter("@id",id)
};
db.ExecuteNonQuery(sql, parameters);
}
//CommonWords表记录的删除
public static int DeleteCommonWordsItem(string id)
{
string sql = "DELETE FROM CommonWords WHERE id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@id",id)
};
return db.ExecuteNonQuery(sql, parameters);
}
#endregion
#region Record标的相关操作
// //通过id主键查找Record
public static Record getRecordById(string id)
{
string sql = "SELECT * FROM Record WHERE id = @id 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())
{
Record temp = new Record();
//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);
temp.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.patientId = reader.IsDBNull(1) ? 0 : reader.GetInt32(1);
temp.doctorId = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);
temp.recordTime = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
//左手
if(!reader.IsDBNull(4))
{
long len = reader.GetBytes(reader.GetOrdinal("dataLeft"), 0, null, 0, 0);
byte[] buffer = new byte[len];
reader.GetBytes(reader.GetOrdinal("dataLeft"), 0, buffer, 0, (int)len);
temp.dataLeft = buffer;
}
else
{
temp.dataLeft = new byte[] { };
}
//右手
if (!reader.IsDBNull(5))
{
long len = reader.GetBytes(reader.GetOrdinal("dataRight"), 0, null, 0, 0);
byte[] buffer = new byte[len];
reader.GetBytes(reader.GetOrdinal("dataRight"), 0, buffer, 0, (int)len);
temp.dataRight = buffer;
}
else
{
temp.dataRight = new byte[] { };
}
//绕关节
if (!reader.IsDBNull(6))
{
long len = reader.GetBytes(reader.GetOrdinal("dataWrist"), 0, null, 0, 0);
byte[] buffer = new byte[len];
reader.GetBytes(reader.GetOrdinal("dataWrist"), 0, buffer, 0, (int)len);
temp.dataWrist = buffer;
}
else
{
temp.dataWrist = new byte[] { };
}
temp.topBP = reader.IsDBNull(7) ? 0 : reader.GetDouble(7);
temp.bottomBP = reader.IsDBNull(8) ? 0 : reader.GetDouble(8);
temp.heartRate = reader.IsDBNull(9) ? 0 : reader.GetDouble(9);
temp.waveTpye = reader.IsDBNull(10) ? "" : reader.GetString(10);
temp.eigenValueSaved = reader.IsDBNull(11) ? 0: reader.GetInt32(11);
temp.comments = reader.IsDBNull(12) ? "" : reader.GetString(12);
temp.hand = reader.IsDBNull(14) ? "b" : reader.GetString(14);
return temp;
}
}
return null;
}
//查找Record中病人id和时间相同的记录个数
public static int getRecordCountByPatientIdAndTime(int patient_id, DateTime recordTime)
{
string sql = "SELECT count(*) FROM Record WHERE patientId = @patient_id AND recordTime=@recordTime";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@patient_id",patient_id),
new SQLiteParameter("@recordTime",recordTime),
};
int count = Convert.ToInt32(db.ExecuteFindCount(sql,parameters));
return count;
}
//Record表的插入
public static void InsertRecordData(int PatientId, int doctorId, DateTime RecordTime, byte[] DataLeft,byte[] DataRight, byte[] DataWrist, double TopBP, double BottomBP, double HeartRate, int EigenValueSaved, string Comments, string waveType, string hand)
{
string sql = "INSERT INTO Record(PatientId,doctorId,RecordTime,DataLeft,DataRight,DataWrist,TopBP,BottomBP,HeartRate,waveType,EigenValueSaved,Comments,hand)values(@PatientId,@doctorId,@RecordTime,@DataLeft,@DataRight,@DataWrist,@TopBP,@BottomBP,@HeartRate,@waveType,@EigenValueSaved,@Comments,@hand)";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@PatientId",PatientId),
new SQLiteParameter("@doctorId",doctorId),
new SQLiteParameter("@RecordTime",RecordTime),
new SQLiteParameter("@DataLeft",DbType.Binary),
new SQLiteParameter("@DataRight",DbType.Binary),
new SQLiteParameter("@DataWrist",DbType.Binary),
new SQLiteParameter("@TopBP",TopBP),
new SQLiteParameter("@BottomBP",BottomBP),
new SQLiteParameter("@HeartRate",HeartRate),
new SQLiteParameter("@waveType",waveType),
new SQLiteParameter("@EigenValueSaved",EigenValueSaved),
new SQLiteParameter("@Comments",Comments),
new SQLiteParameter("@hand",hand)
};
parameters[3].Value = DataLeft;
parameters[4].Value = DataRight;
parameters[5].Value = DataWrist;
db.ExecuteNonQuery(sql, parameters);
}
////Record表的更新
public static void UpdateRecordData(int id,int PatientId, int doctorId, DateTime RecordTime, byte[] DataLeft, byte[] DataRight, byte[] DataWrist, double TopBP, double BottomBP, double HeartRate, int EigenValueSaved, string Comments, string waveType)
{
string sql = "UPDATE Record SET PatientId=@PatientId,doctorId=@doctorId,RecordTime=@RecordTime,DataLeft=@DataLeft,DataRight=@DataLeft,DataWrist=@DataWrist,TopBP=@TopBP,BottomBP=@BottomBP,HeartRate=@HeartRate,EigenValueSaved=@EigenValueSaved,comments=@Comments,waveType=@waveType WHERE id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@PatientId",PatientId),
new SQLiteParameter("@doctorId",doctorId),
new SQLiteParameter("@RecordTime",RecordTime),
new SQLiteParameter("@DataLeft",DataLeft),
new SQLiteParameter("@DataRight",DataRight),
new SQLiteParameter("@DataWrist",DataWrist),
new SQLiteParameter("@TopBP",TopBP),
new SQLiteParameter("@BottomBP",BottomBP),
new SQLiteParameter("@HeartRate",HeartRate),
new SQLiteParameter("@EigenValueSaved",EigenValueSaved),
new SQLiteParameter("@Comments",Comments),
new SQLiteParameter("@id",id),
new SQLiteParameter("@waveType",waveType)
};
db.ExecuteNonQuery(sql, parameters);
}
//Record表更新医生的诊断
public static void UpdateDiagnosisData(int id, string Comments)
{
string sql = "UPDATE Record SET comments=@Comments WHERE id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@Comments",Comments),
new SQLiteParameter("@id",id),
};
db.ExecuteNonQuery(sql, parameters);
}
//Record表删除操作
public static int DeleteRecordItem(string id)
{
string sql = "DELETE FROM Record WHERE id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@id",id)
};
return db.ExecuteNonQuery(sql, parameters);
}
#endregion
#region Expression表的相关操作
//查找所有的expression的content和position列,privilege_flag为1时才显示所有的公示，否则只显示isCache=1的公式
public static void GetAllExpressionContent(Dictionary target, Dictionary positions, int privilege_flag)
{
string sql = "SELECT expression_content,position FROM Expression WHERE ";
if(privilege_flag == 1)
{
sql += "1";
}
else
{
sql += "isCache = 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 tempPosition = reader.IsDBNull(1) ? "" : reader.GetString(1);
if (tempKey == "")
{
break;
}
//KeyValuePair temp = new KeyValuePair(tempKey,0);
target.Add(tempKey,0);
positions.Add(tempKey, tempPosition);
}
}
}
//通过id主键查找Expression
public static Expression getExpressionById(string id)
{
string sql = "SELECT * FROM Expression WHERE id = @id 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())
{
Expression temp = new Expression();
temp.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.expression_content = reader.IsDBNull(1) ? "" : reader.GetString(1);
temp.expression_description = reader.IsDBNull(2) ? "" : reader.GetString(2);
temp.range = reader.IsDBNull(3) ? 0.0 : reader.GetDouble(3);
temp.isRelative = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
temp.isCache = reader.IsDBNull(5) ? 0 : reader.GetInt32(5);
temp.upBound = reader.IsDBNull(6) ? 0 : reader.GetDouble(6);
temp.downBound = reader.IsDBNull(7) ? 0 : reader.GetDouble(7);
temp.position = reader.IsDBNull(8) ? "o" : reader.GetString(8);
return temp;
}
}
return null;
}
//通过公式值查找Expression
public static Expression getExpressionByContent(string content)
{
string sql = "SELECT * FROM Expression WHERE expression_content = @content LIMIT 1";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@content",content),
};
using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
{
while (reader.Read())
{
Expression temp = new Expression();
temp.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
temp.expression_content = reader.IsDBNull(1) ? "" : reader.GetString(1);
temp.expression_description = reader.IsDBNull(2) ? "" : reader.GetString(2);
temp.range = reader.IsDBNull(3) ? 0.0 : reader.GetDouble(3);
temp.isRelative = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
temp.isCache = reader.IsDBNull(5) ? 0 : reader.GetInt32(5);
temp.upBound = reader.IsDBNull(6) ? 0 : reader.GetDouble(6);
temp.downBound = reader.IsDBNull(7) ? 0 : reader.GetDouble(7);
temp.position = reader.IsDBNull(8) ? "o" : reader.GetString(8);
return temp;
}
}
return null;
}
//Expression表的插入
public static void InsertExpressionData(string content, string describe,double range, int relative,double upBound, double downBound, int isCache, string position)
{
string sql = "INSERT INTO Expression(expression_content,expression_description,range,isRelative,upBound,downBound,isCache,position)values(@content,@describe,@range,@relative,@upBound,@downBound,@isCache,@position)";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@content",content),
new SQLiteParameter("@describe",describe),
new SQLiteParameter("@range",range),
new SQLiteParameter("@relative",relative),
new SQLiteParameter("@upBound",upBound),
new SQLiteParameter("@downBound",downBound),
new SQLiteParameter("@isCache",isCache),
new SQLiteParameter("@position",position)
};
db.ExecuteNonQuery(sql, parameters);
}
//Expression表的更新
public static void UpdateExpressionData(string id, string content, string describe, double range, int relative, double upBound, double downBound, int isCache,string position)
{
//Boolean a = false;
string sql = "UPDATE Expression SET expression_content=@content,expression_description=@describe,range=@range,isRelative=@relative,upBound=@upBound,downBound=@downBound,isCache=@isCache,position=@position WHERE id=@id";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@content",content),
new SQLiteParameter("@describe",describe),
new SQLiteParameter("@id",id),
new SQLiteParameter("@range",range),
new SQLiteParameter("@relative",relative),
new SQLiteParameter("@upBound",upBound),
new SQLiteParameter("@downBound",downBound),
new SQLiteParameter("@isCache",isCache),
new SQLiteParameter("@position",position)
};
db.ExecuteNonQuery(sql, parameters);
}
//Expression表仅更新取值范围的方法
public static void UpdateExpressionDataOnlyWithRange(string content, double range, int relative)
{
//Boolean a = false;
string sql = "UPDATE Expression SET range=@range,isRelative=@relative WHERE expression_content=@content";
SQLiteHelper db = new SQLiteHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@content",content),
new SQLiteParameter("@range",range),
new SQLiteParameter("@relative",relative)
};
db.ExecuteNonQuery(sql, parameters);
}
//Expression表记录的删除
public static int DeleteExpressionItem(string id)
{
string sql = "DELETE FROM Expression 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
}
}