public static tb_node_model GetOneNode(DbConn PubConn, int id)
{
tb_node_dal dal = new tb_node_dal();
DataRow dr = dal.GetOneNode(PubConn, id);
tb_node_model model = CreateModelForNode(dr);
return(model);
}
public static List <tb_node_model> GetNodeList(DbConn PubConn, string keyword, int pagesize, int pageindex, out int count)
{
DataSet ds = new tb_node_dal().GetList(PubConn, keyword, pagesize, pageindex, out count);
List <tb_node_model> Model = new List <tb_node_model>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_node_model m = CreateModelForNode(dr);
Model.Add(m);
}
return(Model);
}
public ActionResult Update(int id)
{
return(this.Visit(EnumUserRole.Admin, () =>
{
tb_node_dal dal = new tb_node_dal();
using (DbConn PubConn = DbConn.CreateConn(Config.TaskConnectString))
{
PubConn.Open();
tb_node_model model = dal.GetOneNode(PubConn, id);
return View(model);
}
}));
}
public int Update(DbConn PubConn, tb_node_model model)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("@nodename", model.nodename);
ps.Add("@nodeip", model.nodeip);
ps.Add("@id", model.id);
ps.Add("@ifcheckstate", model.ifcheckstate);
string updatecmd = "update tb_node set nodeip=@nodeip,nodename=@nodename,ifcheckstate=@ifcheckstate where id=@id";
return PubConn.ExecuteSql(updatecmd, ps.ToParameters());
}));
}
public ActionResult Update(tb_node_model model)
{
return(this.Visit(EnumUserRole.Admin, () =>
{
tb_node_dal Dal = new tb_node_dal();
model.nodecreatetime = DateTime.Now;
model.nodelastupdatetime = DateTime.Now;
using (DbConn PubConn = DbConn.CreateConn(Config.TaskConnectString))
{
PubConn.Open();
Dal.Update(PubConn, model);
}
return RedirectToAction("index");
}));
}
public List <tb_node_model> GetListAll(DbConn PubConn)
{
return(SqlHelper.Visit <List <tb_node_model> >(ps =>
{
List <tb_node_model> Model = new List <tb_node_model>();
string sql = "select id,nodename,nodecreatetime,nodeip,nodelastupdatetime,ifcheckstate from tb_node";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_node_model n = CreateModel(dr);
Model.Add(n);
}
return Model;
}));
}
public List <tb_node_model> GetAllStopNodesWithNeedCheckState(DbConn PubConn)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("@nodelastupdatetime", PubConn.GetServerDate().AddMinutes(-5));
string sql = "select id,nodename,nodecreatetime,nodeip,nodelastupdatetime,ifcheckstate from tb_node where nodelastupdatetime<@nodelastupdatetime and ifcheckstate='true'";
DataSet ds = new DataSet();
List <tb_node_model> rs = new List <tb_node_model>();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_node_model n = CreateModel(dr);
rs.Add(n);
}
return rs;
}));
}
public int AddOrUpdate(DbConn PubConn, tb_node_model model)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("@nodename", model.nodename);
ps.Add("@nodecreatetime", model.nodecreatetime);
ps.Add("@nodeip", model.nodeip);
ps.Add("@nodelastupdatetime", model.nodelastupdatetime);
ps.Add("@ifcheckstate", model.ifcheckstate);
ps.Add("@id", model.id);
string updatecmd = "update tb_node set nodeip=@nodeip,nodelastupdatetime=@nodelastupdatetime where id=@id";
string insertcmd = @"insert into tb_node(nodename,nodecreatetime,nodeip,ifcheckstate)
values(@nodename,@nodecreatetime,@nodeip,@ifcheckstate)" ;
if (PubConn.ExecuteSql(updatecmd, ps.ToParameters()) <= 0)
{
PubConn.ExecuteSql(insertcmd, ps.ToParameters());
}
return 1;
}));
}
public virtual bool Add(DbConn PubConn, tb_node_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@nodename", model.nodename),
//
new ProcedureParameter("@nodecreatetime", model.nodecreatetime),
//
new ProcedureParameter("@nodeip", model.nodeip),
//
new ProcedureParameter("@nodelastupdatetime", model.nodelastupdatetime),
//
new ProcedureParameter("@ifcheckstate", model.ifcheckstate)
};
int rev = PubConn.ExecuteSql(@"insert into tb_node(nodename,nodecreatetime,nodeip,nodelastupdatetime,ifcheckstate)
values(@nodename,@nodecreatetime,@nodeip,@nodelastupdatetime,@ifcheckstate)" , Par);
return(rev == 1);
}
public List <tb_node_model> GetList(DbConn PubConn, string keyword, string cstime, string cetime, int pagesize, int pageindex, out int count)
{
int _count = 0;
List <tb_node_model> Model = new List <tb_node_model>();
DataSet dsList = SqlHelper.Visit <DataSet>(ps =>
{
string sqlwhere = "";
string sql = "select ROW_NUMBER() over(order by id desc) as rownum,id,nodename,nodecreatetime,nodeip,nodelastupdatetime,ifcheckstate from tb_node where 1=1 ";
//string sql = "select * from tb_node where 1=1 ";
if (!string.IsNullOrWhiteSpace(keyword))
{
ps.Add("keyword", keyword);
sqlwhere = " and (nodename like '%'+@keyword+'%' or nodeip like '%'+@keyword+'%') ";
}
DateTime d = DateTime.Now;
if (DateTime.TryParse(cstime, out d))
{
ps.Add("CStime", Convert.ToDateTime(cstime));
sqlwhere += " and nodecreatetime>=@CStime";
}
if (DateTime.TryParse(cetime, out d))
{
ps.Add("CEtime", Convert.ToDateTime(cetime));
sqlwhere += " and nodecreatetime<=@CEtime";
}
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_node where 1=1 " + sqlwhere, ps.ToParameters()));
DataSet ds = new DataSet();
string sqlSel = "select * from (" + sql + sqlwhere + ") A where rownum between " + ((pageindex - 1) * pagesize + 1) + " and " + pagesize * pageindex;
//string sqlSel = sql + sqlwhere + " order by id desc limit " + ((pageindex - 1) * pagesize ) + "," + pagesize;
PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters());
return(ds);
});
foreach (DataRow dr in dsList.Tables[0].Rows)
{
tb_node_model n = CreateModel(dr);
Model.Add(n);
}
count = _count;
return(Model);
}
public virtual bool Edit(DbConn PubConn, tb_node_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@nodename", model.nodename),
//
new ProcedureParameter("@nodecreatetime", model.nodecreatetime),
//
new ProcedureParameter("@nodeip", model.nodeip),
//
new ProcedureParameter("@nodelastupdatetime", model.nodelastupdatetime),
//
new ProcedureParameter("@ifcheckstate", model.ifcheckstate)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_node set nodename=@nodename,nodecreatetime=@nodecreatetime,nodeip=@nodeip,nodelastupdatetime=@nodelastupdatetime,ifcheckstate=@ifcheckstate where id=@id", Par);
return(rev == 1);
}
public virtual tb_node_model CreateModel(DataRow dr)
{
var o = new tb_node_model();
//
if (dr.Table.Columns.Contains("id"))
{
o.id = dr["id"].Toint();
}
//
if (dr.Table.Columns.Contains("nodename"))
{
o.nodename = dr["nodename"].Tostring();
}
//
if (dr.Table.Columns.Contains("nodecreatetime"))
{
o.nodecreatetime = dr["nodecreatetime"].ToDateTime();
}
//
if (dr.Table.Columns.Contains("nodeip"))
{
o.nodeip = dr["nodeip"].Tostring();
}
//
if (dr.Table.Columns.Contains("nodelastupdatetime"))
{
o.nodelastupdatetime = dr["nodelastupdatetime"].ToDateTime();
}
//
if (dr.Table.Columns.Contains("ifcheckstate"))
{
o.ifcheckstate = dr["ifcheckstate"].Tobool();
}
return(o);
}
