.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);
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhgakfbf
系列文章
更多
同类精品
更多
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01