.net framework下使用sqlite

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

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

 3. 创建数据库 

1.  
   public class Db
2.  
   {
3.  
   public SQLiteConnection m_dbConnection;
4.  
   private DeviceTable deviceTable; // 设备表
5.  
    
6.  
   public Db()
7.  
   {
8.  
   Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
9.  
   SqliteDB sqliteDB = config.GetSection("sqliteDB") as SqliteDB;
10.  
    string fileName = sqliteDB.FileName;
11.  
     
12.  
    if (!File.Exists(fileName))
13.  
    {
14.  
    SQLiteConnection.CreateFile(fileName); // "xph.sqlite"
15.  
    }
16.  
     
17.  
    string sqlStr = "Data Source="
18.  
    fileName
19.  
    ";Version=3;";
20.  
    try
21.  
    {
22.  
    m_dbConnection = new SQLiteConnection($"Data Source = {fileName}; Version=3");
23.  
    m_dbConnection.Open();
24.  
     
25.  
    deviceTable = new DeviceTable(m_dbConnection);
26.  
    }
27.  
    catch (SQLiteException ex)
28.  
    {
29.  
    Console.WriteLine(ex);
30.  
    }
31.  
    }
32.  
    }

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

1.  
   public class SqliteDB : ConfigurationSection
2.  
   {
3.  
   [ConfigurationProperty("fileName", IsRequired = true)]
4.  
   public string FileName
5.  
   {
6.  
   get { return this["fileName"].ToString(); }
7.  
   set { this["fileName"] = value; }
8.  
   }
9.  
   }

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

1.  
   <configSections>
2.  
       <section name="sagSqliteDB" type="Baoxun.CenterServer.TCPServer.SagSqliteDB, Baoxun.CenterServer.TCPServer" />
3.  
    </configSections>
4.  
    
5.  
     <sagSqliteDB fileName="sag.sqlite" />

4. 创建数据库表

1.  
   public class DeviceTable
2.  
   {
3.  
   public SQLiteConnection m_dbConnection;
4.  
   public DeviceTable(SQLiteConnection sQLiteConnection)
5.  
   {
6.  
   m_dbConnection = sQLiteConnection;
7.  
   CreateTable();
8.  
   }
9.  
    
10.  
    private void CreateTable()
11.  
    {
12.  
    string sql = "create table if not exists device ("
13.  
    "ip TEXT not null, "
14.  
    "port INTEGER not null, "
15.  
    "dev TEXT, "
16.  
    "info TEXT, "
17.  
    "lastConnection TEXT,"
18.  
    "lastCommand TEXT, "
19.  
    "alarmTime TEXT, "
20.  
    "alarmType INTEGER, "
21.  
    "voltage INTEGER, "
22.  
    "status INTEGER not null, "
23.  
    "primary key (ip, port) "
24.  
    ")";
25.  
    try
26.  
    {
27.  
    SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
28.  
    command.ExecuteNonQuery();
29.  
    }
30.  
    catch (SQLiteException ex)
31.  
    {
32.  
    Console.WriteLine(ex);
33.  
    }
34.  
    }
35.  
     
36.  
    // 插入设备信息
37.  
    public int Insert(string ip, int port, string dev, int status)
38.  
    {
39.  
    string sql = "insert into device (ip, port, dev, status) values ('"
40.  
    ip "', "
41.  
    port ", '"
42.  
    dev "', "
43.  
    status ")";
44.  
     
45.  
    try
46.  
    {
47.  
    SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
48.  
    return command.ExecuteNonQuery();
49.  
    }
50.  
    catch (SQLiteException ex)
51.  
    {
52.  
    Console.WriteLine(ex);
53.  
    }
54.  
     
55.  
    return 0;
56.  
    }
57.  
     
58.  
    // 删除设备
59.  
    public int Delete(string ip, int port)
60.  
    {
61.  
    string sql = "delete from device where "
62.  
    "ip = '" ip "' and "
63.  
    "port = " port;
64.  
     
65.  
    try
66.  
    {
67.  
    SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
68.  
    return command.ExecuteNonQuery();
69.  
    }
70.  
    catch (SQLiteException ex)
71.  
    {
72.  
    Console.WriteLine(ex);
73.  
    }
74.  
     
75.  
    return 0;
76.  
    }
77.  
     
78.  
    // 全选
79.  
    public SQLiteDataReader SelectAll()
80.  
    {
81.  
    string sql = "select * from device";
82.  
     
83.  
    try
84.  
    {
85.  
    SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
86.  
    return command.ExecuteReader();
87.  
    }
88.  
    catch (SQLiteException ex)
89.  
    {
90.  
    Console.WriteLine(ex);
91.  
    }
92.  
     
93.  
    return null;
94.  
    }
95.  
     
96.  
     
97.  
    //更新设备状态
98.  
    public int UpdataStatus(string ip, int port, int status)
99.  
    {
100.  
     string sql = "update device set status = " status " where "
101.  
     "ip = '" ip "' and "
102.  
     "port = " port;
103.  
      
104.  
     try
105.  
     {
106.  
     SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
107.  
     return command.ExecuteNonQuery();
108.  
     }
109.  
     catch (SQLiteException ex)
110.  
     {
111.  
     Console.WriteLine(ex);
112.  
     }
113.  
      
114.  
     return 0;
115.  
     }
116.  
      
117.  
     //获得设备状态
118.  
     public int GetStatus(string ip, int port)
119.  
     {
120.  
     string sql = "select status from devices where "
121.  
     "ip = '" ip "' and "
122.  
     "port = " port;
123.  
      
124.  
     try
125.  
     {
126.  
     SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
127.  
     SQLiteDataReader read = command.ExecuteReader();
128.  
     if (read.Read())
129.  
     {
130.  
     return read.GetInt32(0);
131.  
     }
132.  
     }
133.  
     catch (Exception ex)
134.  
     {
135.  
     Console.WriteLine(ex);
136.  
     }
137.  
      
138.  
     return 0;
139.  
     }
140.  
      
141.  
     // 更新设备告警
142.  
      
143.  
     public int UpdataAlarm(string ip, int port, string alarmTime, int alarmType, int voltage)
144.  
     {
145.  
     string sql = "update devices "
146.  
     "set "
147.  
     "alarmTime = '" alarmTime "', "
148.  
     "alarmType = " alarmType ", "
149.  
     "voltage = '" voltage "' "
150.  
     " where "
151.  
     "ip = '" ip "' and "
152.  
     "port = " port;
153.  
      
154.  
     try
155.  
     {
156.  
     SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
157.  
     return command.ExecuteNonQuery();
158.  
     }
159.  
     catch (SQLiteException ex)
160.  
     {
161.  
     Console.WriteLine(ex);
162.  
     }
163.  
      
164.  
     return 0;
165.  
     }
166.  
      
167.  
     // 更新设备信息
168.  
      
169.  
     public int UpdataInfo(string ip, int port, string info)
170.  
     {
171.  
     string sql = "update devices set info = '" info "' where "
172.  
     "ip = '" ip "' and "
173.  
     "port = " port;
174.  
      
175.  
     try
176.  
     {
177.  
     SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
178.  
     return command.ExecuteNonQuery();
179.  
     }
180.  
     catch (SQLiteException ex)
181.  
     {
182.  
     Console.WriteLine(ex);
183.  
     }
184.  
      
185.  
     return 0;
186.  
     }
187.  
      
188.  
     public int UpdataLastConnection(string ip, int port, string LastConnection, string lastCommand)
189.  
     {
190.  
     string sql = "update devices set "
191.  
     "LastConnection = '" LastConnection "',"
192.  
     "lastCommand = '" lastCommand ""
193.  
     "' where "
194.  
     "ip = '" ip "' and "
195.  
     "port = " port;
196.  
      
197.  
     try
198.  
     {
199.  
     SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
200.  
     return command.ExecuteNonQuery();
201.  
     }
202.  
     catch (SQLiteException ex)
203.  
     {
204.  
     Console.WriteLine(ex);
205.  
     }
206.  
      
207.  
     return 0;
208.  
     }
209.  
     }

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

1.  
   /// <summary>
2.  
   /// device
3.  
   /// </summary>
4.  
   public List<QsjyDevice> GetDevices()
5.  
   {
6.  
   SQLiteDataReader reader = deviceTable.SelectAll();
7.  
   List<QsjyDevice> devices = new List<QsjyDevice>();
8.  
   while (reader.Read())
9.  
   {
10.  
    QsjyDevice device = new QsjyDevice();
11.  
    device.Ip = reader["ip"].ToString();
12.  
    device.Port = reader.GetInt32(1);
13.  
     
14.  
    device.Dev = new Dictionary<string, string>();
15.  
    string dev = reader["dev"].ToString();
16.  
    var devs = JsonConvert.DeserializeObject<Dictionary<string, string>>(dev);
17.  
    foreach (var d in devs)
18.  
    {
19.  
    device.Dev.Add(d.Key, d.Value);
20.  
    }
21.  
     
22.  
    device.Info = new Dictionary<string, string>();
23.  
    string info = reader["info"].ToString();
24.  
    var infos = JsonConvert.DeserializeObject<Dictionary<string, string>>(info);
25.  
    foreach (var i in infos)
26.  
    {
27.  
    device.Info.Add(i.Key, i.Value);
28.  
    }
29.  
     
30.  
    device.LastConnection = reader["lastConnection"].ToString();
31.  
    device.LastCommand = reader["lastCommand"].ToString();
32.  
    device.AlarmTime = reader["alarmTime"].ToString();
33.  
    device.AlarmType = reader.GetInt32(7);
34.  
    device.Voltage = reader.GetInt32(8);
35.  
    device.Status = reader.GetInt32(9);
36.  
     
37.  
    devices.Add(device);
38.  
    }
39.  
     
40.  
    return devices;
41.  
    }
42.  
     
43.  
    public int AddDevice(QsjyDevice device)
44.  
    {
45.  
    string str = JsonConvert.SerializeObject(device.Dev);
46.  
     
47.  
    foreach (var channel in device.Channels)
48.  
    {
49.  
    AddChannel(channel.Value);
50.  
    }
51.  
     
52.  
    return deviceTable.Insert(device.Ip, device.Port, str, device.Status);
53.  
    }
54.  
     
55.  
    public int DeleteDevice(string ip, int port)
56.  
    {
57.  
    return deviceTable.Delete(ip, port);
58.  
    }
59.  
     
60.  
    public int UpdataDeviceStatus(string ip, int port, int status)
61.  
    {
62.  
    return deviceTable.UpdataStatus(ip, port, status);
63.  
    }
64.  
     
65.  
    public int GetDeviceStatus(string ip, int port)
66.  
    {
67.  
    return deviceTable.GetStatus(ip, port);
68.  
    }
69.  
     
70.  
    public int UpdataLastConnection(string ip, int port, string lastConnection, string lastCommand)
71.  
    {
72.  
    return deviceTable.UpdataLastConnection(ip, port, lastConnection, lastCommand);
73.  
    }
74.  
     
75.  
    public int UpdataDeviceInfo(string ip, int port, Dictionary<string, string> info)
76.  
    {
77.  
    string str = JsonConvert.SerializeObject(info);
78.  
    return deviceTable.UpdataInfo(ip, port, str);
79.  
    }
80.  
     
81.  
    public int UpdataDeviceAlarm(string ip, int port, string alarmTime, int alarmType, int voltage)
82.  
    {
83.  
    return deviceTable.UpdataAlarm(ip, port, alarmTime, alarmType, voltage);
84.  
    }

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

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

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

1.  
   Db db = new Db();
2.  
    
3.  
   QsjyDevice device = new QsjyDevice();
4.  
    
5.  
   device.Ip = "ip";
6.  
   device.Port = 10;
7.  
   device.LastConnection = "last Connection";
8.  
   device.LastCommand = "last command";
9.  
   device.AlarmTime = "Alarm Time";
10.  
    device.Voltage = 1234;
11.  
    device.Status = 0;
12.  
     
13.  
    db.AddDevice(device);
14.  
    db.UpdataDeviceAlarm(device.Ip, device.Port, device.AlarmTime, device.AlarmType, 123);
15.  
    db.UpdataDeviceStatus(device.Ip, device.Port, 1);
16.  
    db.UpdataDeviceInfo(device.Ip, device.Port, device.Info);
17.  
    db.UpdataLastConnection(device.Ip, device.Port, device.LastConnection, device.LastCommand);
18.  
    db.UpdataChannelStatus(channel.Ip, channel.Port, channel.ChannelId, 1);
19.  
    db.UpdataChannelValue(channel.Ip, channel.Port, channel.ChannelId, channel.Value, channel.LastReportTime);
20.  
     
21.  
    List<QsjyDevice> devices = db.GetDevices();
22.  
     
23.  
    count = db.DeleteDevice(device.Ip, device.Port);

这篇好文章是转载于：学新通技术网