.net framework下如何使用sqlite

1. 创建一个.net Framework控制台工程

2. nuget 搜索并添加 system.data.sqlite

 3. 创建数据库 

 public class Db
    {
        public SQLiteConnection m_dbConnection;
        private DeviceTable deviceTable; // 设备表

        public Db()
        {
            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            SqliteDB sqliteDB = config.GetSection("sqliteDB") as SqliteDB;
            string fileName = sqliteDB.FileName;

            if (!File.Exists(fileName))
            {
                SQLiteConnection.CreateFile(fileName); // "xph.sqlite"
            }

            string sqlStr = "Data Source=" +
                fileName +
                ";Version=3;";
            try
            {
                m_dbConnection = new SQLiteConnection($"Data Source = {fileName}; Version=3");
                m_dbConnection.Open();

                deviceTable = new DeviceTable(m_dbConnection);
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }
        }
}

这里使用配置文件, 来保存数据库配置

    public class SqliteDB : ConfigurationSection
    {
        [ConfigurationProperty("fileName", IsRequired = true)]
        public string FileName
        {
            get { return this["fileName"].ToString(); }
            set { this["fileName"] = value; }
        }
    }

在app.config中配置数据库项:

<configSections>
    <section name="sagSqliteDB" type="Baoxun.CenterServer.TCPServer.SagSqliteDB, Baoxun.CenterServer.TCPServer" />
 </configSections>

  <sagSqliteDB fileName="sag.sqlite" />

4. 创建数据库表

public class DeviceTable
    {
        public SQLiteConnection m_dbConnection;
        public DeviceTable(SQLiteConnection sQLiteConnection)
        {
            m_dbConnection = sQLiteConnection;
            CreateTable();
        }

        private void CreateTable()
        {
            string sql = "create table if not exists device (" +
                "ip TEXT not null, " + 
                "port INTEGER not null, " +                
                "dev TEXT, " +
                "info TEXT, " +
                "lastConnection TEXT," +
                "lastCommand TEXT, " +
                "alarmTime TEXT, " +
                "alarmType INTEGER, " +
                "voltage INTEGER, " +
                "status INTEGER not null, " +
                "primary key (ip, port) " +
                ")";
            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }
        }

        // 插入设备信息
        public int Insert(string ip, int port, string dev, int status) 
        {
            string sql = "insert into device (ip, port, dev, status) values ('" +
                ip + "', " +
                port + ", '" +
                dev + "', " +
                status + ")";

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        // 删除设备
        public int Delete(string ip, int port)
        {
            string sql = "delete from device where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        // 全选
        public SQLiteDataReader SelectAll()
        {
            string sql = "select * from device";

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteReader();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return null;
        }


        //更新设备状态
        public int UpdataStatus(string ip, int port, int status)
        {
            string sql = "update device set status = " + status + " where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        //获得设备状态
        public int GetStatus(string ip, int port)
        {
            string sql = "select status from devices where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                SQLiteDataReader read = command.ExecuteReader();
                if (read.Read())
                {
                    return read.GetInt32(0);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        // 更新设备告警

        public int UpdataAlarm(string ip, int port, string alarmTime, int alarmType, int voltage)
        {
            string sql = "update devices " +
                "set " +
                "alarmTime = '" + alarmTime + "', " +
                "alarmType = " + alarmType + ", " +
                "voltage = '" + voltage + "' " +
                " where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        // 更新设备信息

        public int UpdataInfo(string ip, int port, string info)
        {
            string sql = "update devices set info = '" + info + "' where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        public int UpdataLastConnection(string ip, int port, string LastConnection, string lastCommand)
        {
            string sql = "update devices set " +
                "LastConnection = '" + LastConnection + "'," +
                "lastCommand = '" + lastCommand + "" +
                "' where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }
    }

5. 在数据库文件中, 添加调用方法

/// <summary>
        /// device
        /// </summary>
        public List<QsjyDevice> GetDevices()
        {
            SQLiteDataReader reader = deviceTable.SelectAll();
            List<QsjyDevice> devices = new List<QsjyDevice>();
            while (reader.Read())
            {
                QsjyDevice device = new QsjyDevice();
                device.Ip = reader["ip"].ToString();
                device.Port = reader.GetInt32(1);

                device.Dev = new Dictionary<string, string>();
                string dev = reader["dev"].ToString();
                var devs = JsonConvert.DeserializeObject<Dictionary<string, string>>(dev);
                foreach (var d in devs)
                {
                    device.Dev.Add(d.Key, d.Value);
                }

                device.Info = new Dictionary<string, string>();
                string info = reader["info"].ToString();
                var infos = JsonConvert.DeserializeObject<Dictionary<string, string>>(info);
                foreach (var i in infos)
                {
                    device.Info.Add(i.Key, i.Value);
                }
                
                device.LastConnection = reader["lastConnection"].ToString();
                device.LastCommand = reader["lastCommand"].ToString();
                device.AlarmTime = reader["alarmTime"].ToString();
                device.AlarmType = reader.GetInt32(7);
                device.Voltage = reader.GetInt32(8);
                device.Status = reader.GetInt32(9);

                devices.Add(device);
            }

            return devices;
        }

        public int AddDevice(QsjyDevice device)
        {
            string str = JsonConvert.SerializeObject(device.Dev);

            foreach (var channel in device.Channels)
            {
                AddChannel(channel.Value);
            }

            return deviceTable.Insert(device.Ip, device.Port, str, device.Status);
        }

        public int DeleteDevice(string ip, int port)
        {
            return deviceTable.Delete(ip, port);
        }

        public int UpdataDeviceStatus(string ip, int port, int status)
        {
            return deviceTable.UpdataStatus(ip, port, status);
        }

        public int GetDeviceStatus(string ip, int port)
        {
            return deviceTable.GetStatus(ip, port);
        }

        public int UpdataLastConnection(string ip, int port, string lastConnection, string lastCommand)
        {
            return deviceTable.UpdataLastConnection(ip, port, lastConnection, lastCommand);
        }

        public int UpdataDeviceInfo(string ip, int port, Dictionary<string, string> info)
        {
            string str = JsonConvert.SerializeObject(info);
            return deviceTable.UpdataInfo(ip, port, str);
        }

        public int UpdataDeviceAlarm(string ip, int port, string alarmTime, int alarmType, int voltage)
        {
            return deviceTable.UpdataAlarm(ip, port, alarmTime, alarmType, voltage);
        }

这里定义了QsjyDevice, 来储存数据库的每一条记录值:

public class QsjyDevice
    {
        public string Ip { get; set; } // 远端IP
        public int Port { get; set; } // 远端端口
        public Dictionary<string, string> Dev { get; set; } // DEV信息, 注册01
        public Dictionary<string, string> Info { get; set; } //基站信息, 查询07
        public Dictionary<int, QsjyChannel> Channels { get; set; } // 通道信息
        public string LastConnection { get; set; }
        public string LastCommand { get; set; }
        public string AlarmTime { get; set; } // 时间
        public int AlarmType { get; set; }  // 1: 设备断电报警; 2: 电池电压过低报警
        public int Voltage { get; set; } // 电压
        public int Status { get; set; } // 0 离线, 1 在线
    }

6. 在program.cs中, 创建数据库实例

            Db db = new Db();

            QsjyDevice device = new QsjyDevice();

            device.Ip = "ip";
            device.Port = 10;  
            device.LastConnection = "last Connection";
            device.LastCommand = "last command";
            device.AlarmTime = "Alarm Time";
            device.Voltage = 1234;
            device.Status = 0;

            db.AddDevice(device);
            db.UpdataDeviceAlarm(device.Ip, device.Port, device.AlarmTime, device.AlarmType, 123);
            db.UpdataDeviceStatus(device.Ip, device.Port, 1);
            db.UpdataDeviceInfo(device.Ip, device.Port, device.Info);
            db.UpdataLastConnection(device.Ip, device.Port, device.LastConnection, device.LastCommand);
            db.UpdataChannelStatus(channel.Ip, channel.Port, channel.ChannelId, 1);
            db.UpdataChannelValue(channel.Ip, channel.Port, channel.ChannelId, channel.Value, channel.LastReportTime);

            List<QsjyDevice> devices = db.GetDevices();
            
            count = db.DeleteDevice(device.Ip, device.Port);