Laola/WpfTest1/SQLite/SQLiteModel.cs

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 数据库整体操作
        /// <summary>
        /// optmizeDatabase:从数据库中清空空闲列表
        /// </summary>
        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 idNum,string name, double height, double weight,int base_heartrate,int pregnancy_times,int birth_times,DateTime pregnancy_date,string mobile,string profession,string address,string category,string group_1, string firstLetterPY,string description,DateTime lastRecordDate,Boolean delete_flag, DateTime birth_date)
        {
            string sql = "INSERT INTO Patient(record_id,idNum,name,height,weight,base_heartrate,pregnancy_times,birth_times,pregnancy_date,mobile,profession,address,category,group_1,firstLetterPY,description,lastRecordDate,delete_flag,birth_date)values(@record_id,@idNum,@name,@height,@weight,@base_heartrate,@pregnancy_times,@birth_times,@pregnancy_date,@mobile,@profession,@address,@category,@group_1,@firstLetterPY,@description,@lastRecordDate,@delete_flag,@birth_date)";
            SQLiteHelper db = new SQLiteHelper(dbPath);
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                                                               new SQLiteParameter("@record_id",record_id),
                                                               new SQLiteParameter("@idNum",idNum),
                                                               new SQLiteParameter("@name",name),
                                                               new SQLiteParameter("@height",height),
                                                               new SQLiteParameter("@weight",weight),
                                                               new SQLiteParameter("@base_heartrate",base_heartrate),
                                                               new SQLiteParameter("@pregnancy_times",pregnancy_times),
                                                               new SQLiteParameter("@birth_times",birth_times),
                                                               new SQLiteParameter("@pregnancy_date",pregnancy_date),
                                                               new SQLiteParameter("@mobile",mobile),
                                                               new SQLiteParameter("@profession",profession),
                                                               new SQLiteParameter("@address",address),
                                                               new SQLiteParameter("@category",category),
                                                               new SQLiteParameter("@group_1",group_1),
                                                               new SQLiteParameter("@firstLetterPY",firstLetterPY),
                                                               new SQLiteParameter("@description",description),
                                                               new SQLiteParameter("@lastRecordDate",lastRecordDate),
                                                               new SQLiteParameter("@delete_flag",delete_flag),
                                                               new SQLiteParameter("@birth_date",birth_date)
                                                               };
            db.ExecuteNonQuery(sql, parameters);
        }
        //Patient表的插入 返回自增主键
        public static int InsertPatientAndReturnID(string record_id, string idNum, string name, double height, double weight, int base_heartrate, int pregnancy_times, int birth_times, DateTime pregnancy_date, string mobile, string profession, string address, string category, string group_1, string firstLetterPY, string description, DateTime lastRecordDate, Boolean delete_flag, DateTime birth_date)
        {
            string sql = "INSERT INTO Patient(record_id,idNum,name,height,weight,base_heartrate,pregnancy_times,birth_times,pregnancy_date,mobile,profession,address,category,group_1,firstLetterPY,description,lastRecordDate,delete_flag,birth_date)values(@record_id,@idNum,@name,@height,@weight,@base_heartrate,@pregnancy_times,@birth_times,@pregnancy_date,@mobile,@profession,@address,@category,@group_1,@firstLetterPY,@description,@lastRecordDate,@delete_flag,@birth_date);";
            SQLiteHelper db = new SQLiteHelper(dbPath);
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                                                               new SQLiteParameter("@record_id",record_id),
                                                               new SQLiteParameter("@idNum",idNum),
                                                               new SQLiteParameter("@name",name),
                                                               new SQLiteParameter("@height",height),
                                                               new SQLiteParameter("@weight",weight),
                                                               new SQLiteParameter("@base_heartrate",base_heartrate),
                                                               new SQLiteParameter("@pregnancy_times",pregnancy_times),
                                                               new SQLiteParameter("@birth_times",birth_times),
                                                               new SQLiteParameter("@pregnancy_date",pregnancy_date),
                                                               new SQLiteParameter("@mobile",mobile),
                                                               new SQLiteParameter("@profession",profession),
                                                               new SQLiteParameter("@address",address),
                                                               new SQLiteParameter("@category",category),
                                                               new SQLiteParameter("@group_1",group_1),
                                                               new SQLiteParameter("@firstLetterPY",firstLetterPY),
                                                               new SQLiteParameter("@description",description),
                                                               new SQLiteParameter("@lastRecordDate",lastRecordDate),
                                                               new SQLiteParameter("@delete_flag",delete_flag),
                                                               new SQLiteParameter("@birth_date",birth_date)
                                                               };
            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(string id, string record_id, string idNum, string name, double height, double weight, int base_heartrate, int pregnancy_times, int birth_times, DateTime pregnancy_date, string mobile, string profession, string address, string category, string group_1, string firstLetterPY, string description, Boolean delete_flag)
        {
            //Boolean a = false;
            string sql = "UPDATE Patient SET record_id=@record_id,idNum=@idNum,name=@name,height=@height,weight=@weight,base_heartrate=@base_heartrate,pregnancy_times=@pregnancy_times,birth_times = @birth_times,pregnancy_date= @pregnancy_date, mobile = @mobile,profession=@profession,address=@address,category=@category,group_1=@group_1,firstLetterPY=@firstLetterPY,description=@description,delete_flag=@delete_flag WHERE id=@id";
            SQLiteHelper db = new SQLiteHelper(dbPath);
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                                                               new SQLiteParameter("@record_id",record_id),
                                                               new SQLiteParameter("@idNum",idNum),
                                                               new SQLiteParameter("@name",name),
                                                               new SQLiteParameter("@height",height),
                                                               new SQLiteParameter("@weight",weight),
                                                               new SQLiteParameter("@base_heartrate",base_heartrate),
                                                               new SQLiteParameter("@pregnancy_times",pregnancy_times),
                                                               new SQLiteParameter("@birth_times",birth_times),
                                                               new SQLiteParameter("@pregnancy_date",pregnancy_date),
                                                               new SQLiteParameter("@mobile",mobile),
                                                               new SQLiteParameter("@profession",profession),
                                                               new SQLiteParameter("@address",address),
                                                               new SQLiteParameter("@category",category),
                                                               new SQLiteParameter("@group_1",group_1),
                                                               new SQLiteParameter("@firstLetterPY",firstLetterPY),
                                                               new SQLiteParameter("@description",description),
                                                               new SQLiteParameter("@delete_flag",delete_flag),
                                                               new SQLiteParameter("@id",id)
                                                               };
            db.ExecuteNonQuery(sql, parameters);
        }
        //Patient表的更新
        public static void UpdatePatientData(string id,string record_id ,string idNum, string name, double height, double weight, int base_heartrate, int pregnancy_times, int birth_times,DateTime pregnancy_date, string mobile, string profession, string address, string category, string group_1, string firstLetterPY, string description, Boolean delete_flag, DateTime birth_date)
        {
            //Boolean a = false;
            string sql = "UPDATE Patient SET record_id=@record_id,idNum=@idNum,name=@name,height=@height,weight=@weight,base_heartrate=@base_heartrate,pregnancy_times=@pregnancy_times,birth_times = @birth_times,pregnancy_date= @pregnancy_date, mobile = @mobile,profession=@profession,address=@address,category=@category,group_1=@group_1,firstLetterPY=@firstLetterPY,description=@description,delete_flag=@delete_flag,birth_date=@birth_date WHERE id=@id";
            SQLiteHelper db = new SQLiteHelper(dbPath);
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                                                               new SQLiteParameter("@record_id",record_id),
                                                               new SQLiteParameter("@idNum",idNum),
                                                               new SQLiteParameter("@name",name),
                                                               new SQLiteParameter("@height",height),
                                                               new SQLiteParameter("@weight",weight),
                                                               new SQLiteParameter("@base_heartrate",base_heartrate),
                                                               new SQLiteParameter("@pregnancy_times",pregnancy_times),
                                                               new SQLiteParameter("@birth_times",birth_times),
                                                               new SQLiteParameter("@pregnancy_date",pregnancy_date),
                                                               new SQLiteParameter("@mobile",mobile),
                                                               new SQLiteParameter("@profession",profession),
                                                               new SQLiteParameter("@address",address),
                                                               new SQLiteParameter("@category",category),
                                                               new SQLiteParameter("@group_1",group_1),
                                                               new SQLiteParameter("@firstLetterPY",firstLetterPY),
                                                               new SQLiteParameter("@description",description),
                                                               new SQLiteParameter("@delete_flag",delete_flag),
                                                               new SQLiteParameter("@birth_date",birth_date),
                                                               new SQLiteParameter("@id",id)
                                                               };
            db.ExecuteNonQuery(sql, parameters);
        }
        //Patient表的仅更新最后测量时间的方法
        public static void UpdatePatientDataWithLastMeasureDate(string id, DateTime lastRecordDate)
        {
            string sql = "UPDATE Patient SET lastRecordDate=@lastRecordDate WHERE 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 delete_flag=1 WHERE id=@id";
            SQLiteHelper db = new SQLiteHelper(dbPath);
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                                                               new SQLiteParameter("@id",id)
                                                               };
            return db.ExecuteNonQuery(sql, parameters);
        }
        /// <summary>
        /// getDeletedPatients:从Patient表中获取所有已被标记为删除的数据的id
        /// </summary>
        public static List<string> getDeletedPatients()
        {
            string sql = "SELECT id FROM Patient WHERE delete_flag = 1";
            SQLiteHelper db = new SQLiteHelper(dbPath);
            SQLiteParameter[] parameters = new SQLiteParameter[] { };
            List<string> result = new List<string>();
            using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    string id = reader.IsDBNull(0) ? "" : reader.GetString(0);
                    result.Add(id);
                }
            }
            return result;
        }
        /// <summary>
        /// realDeleteRecords:从Patient表中真实删除记录
        /// </summary>
        public static int realDeleteRecords()
        {
            string sql = "DELETE from Patient WHERE 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 idnum,string record_id)
        {
            string sql = "SELECT * FROM Patient WHERE (record_id = @record_id OR idNum = @idnum) AND delete_flag = 0";
            SQLiteHelper db = new SQLiteHelper(dbPath);
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                                                               new SQLiteParameter("@record_id",record_id),
                                                               new SQLiteParameter("@idnum",idnum)
                                                               };
            using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    reader.Close();
                    return true;
                }
            }
            return false;
        }
        //通过身份证号、孕次、产次查找Patient
        public static Patient getPatientByIdNum_pregnancy_brith(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 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())
                {
                    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;
        }
        //根据拼音获取patient记录
        public static void getPatientByPY(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(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(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<string> getCategory(string taskType="category")
        {
            string sql = "SELECT " + taskType + " FROM Patient WHERE delete_flag = 0 group by " + taskType;
            List<string> result = new List<string>();
            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<string, double> target, Dictionary<string, string> 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<string, double> temp = new KeyValuePair<string, double>(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<string, string> 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<string, double> temp = new KeyValuePair<string, double>(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
    }
}