/// <summary>
/// 删除
/// </summary>
/// <param name="model"></param>
/// <returns>成功返回true</returns>
public static bool DeleteCascading(Cascading model)
{
if (model != null)
{
var con = SqLiteHelper.Open(_dbPath);
try
{
string mysqlString = "delete from ipvt_cascadingtable where CascadingID=?id";
var param = new MySqlParameter("?id", model.Id);
CustomMySqlHelper.ExecuteNonQuery(mysqlString, param);
string sqliteSql = string.Format("delete from db_data where RowID={0}", model.RowId);
SqLiteHelper.ExecuteNonquery(con, sqliteSql);
return(true);
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (con != null)
{
con.Close();
}
}
}
return(false);
}
/// <summary>
/// 删除过期日志
/// </summary>
/// <param name="time">过去时间</param>
public static int DeleteLogs(DateTime time)
{
if (HaveDb())
{
string sql = "delete from log where logtime<@time";
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, new SQLiteParameter("@time", time)));
}
return(0);
}
/// <summary>
/// 删除设备
/// </summary>
/// <param name="device"></param>
public static int DeleteDevice(Device device)
{
if (device != null)
{
HaveDb();
string sql = "delete from device where did=@did";
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, new SQLiteParameter("@did", device.Id)));
}
return(-1);
}
/// <summary>
/// 插入日志
/// </summary>
/// <param name="log"></param>
public static int InsetLog(LogMessage log)
{
if (log != null)
{
HaveDb();
string sql = "insert into log(logtime,logmsg,logtype,username,caller,callpanel) values(@time,@msg,@type,@name,@caller,@pan)";
SQLiteParameter[] ps = new SQLiteParameter[6];
ps[0] = new SQLiteParameter("@time", log.LogTime);
ps[1] = new SQLiteParameter("@msg", log.LogMsg);
ps[2] = new SQLiteParameter("@type", log.LogType);
ps[3] = new SQLiteParameter("@name", log.OperaterId);
ps[4] = new SQLiteParameter("@caller", Convert.ToInt32(log.FromNo));
ps[5] = new SQLiteParameter("@pan", log.PanelNum);
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, ps));
}
return(-1);
}
/// <summary>
/// 判断core.db是否异常
/// </summary>
public static void JudeCoreDb()
{
string path = Path.Combine(@"C:\Users\Administrator\Desktop\db", "core.db");
//string path = Path.Combine(RootPath, "db", "core.db");
if (File.Exists(path))
{
SQLiteConnection conn;
bool b = SqLiteHelper.TryOpen(path, out conn);
if (b)
{
string sql = "select * from registrations order by RowID desc limit 1";
object obj = SqLiteHelper.ExecuteScalar(conn, sql);
if (obj == null)
{
string s = "delete from registrations";
//string s = "delete from registrations where RowID=(select RowID from registrations order by RowID desc limit 1)";
SqLiteHelper.ExecuteNonquery(conn, s);
}
sql = "select * from complete order by RowID desc limit 1";
obj = SqLiteHelper.ExecuteScalar(conn, sql);
if (obj == null)
{
string s = "delete from complete";
//string s = "delete from registrations where RowID=(select RowID from registrations order by RowID desc limit 1)";
SqLiteHelper.ExecuteNonquery(conn, s);
}
sql = "select * from interfaces order by RowID desc limit 1";
obj = SqLiteHelper.ExecuteScalar(conn, sql);
if (obj == null)
{
string s = "delete from interfaces";
//string s = "delete from registrations where RowID=(select RowID from registrations order by RowID desc limit 1)";
SqLiteHelper.ExecuteNonquery(conn, s);
}
}
else
{
File.Delete(path);
}
}
}
/// <summary>
/// 更新设备
/// </summary>
/// <param name="device"></param>
public static int UpdateDevice(Device device)
{
if (device != null)
{
HaveDb();
string sql = "update device set ip=@ip,name=@name,type=@type,generation=@gen,extno=@ext,panelnum=@pan where did=@did";
SQLiteParameter[] ps = new SQLiteParameter[7];
ps[0] = new SQLiteParameter("@did", device.Id);
ps[1] = new SQLiteParameter("@ip", device.Ip);
ps[2] = new SQLiteParameter("@name", device.Name);
ps[3] = new SQLiteParameter("@type", device.Type);
ps[4] = new SQLiteParameter("@gen", device.Generation);
ps[5] = new SQLiteParameter("@ext", device.Extension.Number);
ps[6] = new SQLiteParameter("@pan", device.PanelNum);
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, ps));
}
return(-1);
}
/// <summary>
/// 插入设备
/// </summary>
/// <param name="device"></param>
public static int InsetDevice(Device device)
{
if (device != null)
{
HaveDb();
string sql = "insert into device(did,ip,name,type,generation,extno,panelnum) values(@did,@ip,@name,@type,@gen,@ext,@pan)";
SQLiteParameter[] ps = new SQLiteParameter[7];
ps[0] = new SQLiteParameter("@did", device.Id);
ps[1] = new SQLiteParameter("@ip", device.Ip);
ps[2] = new SQLiteParameter("@name", device.Name);
ps[3] = new SQLiteParameter("@type", device.Type);
ps[4] = new SQLiteParameter("@gen", device.Generation);
ps[5] = new SQLiteParameter("@ext", device.Extension.Number);
ps[6] = new SQLiteParameter("@pan", device.PanelNum);
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, ps));
}
return(-1);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="model"></param>
/// <returns>成功返回true</returns>
public static bool UpdataCascading(Cascading model)
{
if (model != null && model.Id != 0)
{
var con = SqLiteHelper.Open(_dbPath);
try
{
string mysqlString =
"update ipvt_cascadingtable set Data_key=?key,Data=?data,Remark=?remark where CascadingID=?id";
var ps = new MySqlParameter[4];
ps[0] = new MySqlParameter("?key", model.Key);
ps[1] = new MySqlParameter("?data", model.Ip);
ps[2] = new MySqlParameter("?remark", model.Remark);
ps[3] = new MySqlParameter("?id", model.Id);
CustomMySqlHelper.ExecuteNonQuery(mysqlString, ps); //更新
string sqliteString =
string.Format("update db_data set data_key='{0}',data='{1}' where RowID={2}",
model.Key, model.Ip, model.RowId);
SqLiteHelper.ExecuteNonquery(con, sqliteString); //SQLite更新
return(true);
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (con != null)
{
con.Close();
}
}
}
return(false);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns>成功返回true</returns>
public static bool InsertCascading(Cascading model)
{
if (model != null)
{
var con = SqLiteHelper.Open(_dbPath);
try
{
string mysqlString =
"insert into ipvt_cascadingtable(Data_key,Data,Remark) values(?key,?data,?remark)";
var ps = new MySqlParameter[3];
ps[0] = new MySqlParameter("?key", model.Key);
ps[1] = new MySqlParameter("?data", model.Ip);
ps[2] = new MySqlParameter("?remark", model.Remark);
CustomMySqlHelper.ExecuteNonQuery(mysqlString, ps);//插入
string sqliteString =
string.Format("insert into db_data(realm,data_key,data) values('hy_extern_domain','{0}','{1}')",
model.Key, model.Ip);
SqLiteHelper.ExecuteNonquery(con, sqliteString);//SQLite插入
return(true);
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (con != null)
{
con.Close();
}
}
}
return(false);
}
