C# DbConn.ExecuteScalar方法代码示例

 /// <summary>
 /// nodeList
 /// </summary>
 /// <param name="pageIndex"></param>
 /// <param name="pageSize"></param>
 /// <param name="count"></param>
 /// <returns></returns>
 public IList<tb_datanode_model> GetPageList(DbConn conn, int pageIndex, int pageSize, ref int count)
 {
     int tempCount = 0;
     IList<tb_datanode_model> list = new List<tb_datanode_model>();
     var result = SqlHelper.Visit((ps) =>
      {
          string sql = "SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_datanode WITH(NOLOCK)";
          string countSql = "SELECT COUNT(1) FROM tb_datanode WITH(NOLOCK) ";
          object obj = conn.ExecuteScalar(countSql, null);
          if (obj != DBNull.Value && obj != null)
          {
              tempCount = LibConvert.ObjToInt(obj);
          }
          string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
          DataTable dt = conn.SqlToDataTable(sqlPage, null);
          if (dt != null && dt.Rows.Count > 0)
          {
              foreach (DataRow dr in dt.Rows)
              {
                  tb_datanode_model model = CreateModel(dr);
                  list.Add(model);
              }
          }
          return list;
      });
     count = tempCount;
     return result;
 }

        /// <summary>
        /// debug日志分页列表
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public IList<tb_debuglog_model> GetPageList(DbConn conn, DateTime? startTime, DateTime? endTime, string mqpathid, string mqpath, string methodname, string info, int pageSize, int pageIndex, ref int count)
        {
            int tempCount = 0;
            var result = SqlHelper.Visit((ps) =>
             {
             IList<tb_debuglog_model> list = new List<tb_debuglog_model>();
             StringBuilder where = new StringBuilder();
             List<ProcedureParameter> parameters = new List<ProcedureParameter>();
             where.Append(" WHERE 1=1 ");
             if (startTime != null && endTime != null)
             {
                 parameters.Add(new ProcedureParameter("startTime", startTime.Value.ToString("yyyy-MM-dd")));
                 parameters.Add(new ProcedureParameter("endTime", endTime.Value.ToString("yyyy-MM-dd")));
                 where.Append(" AND createtime>[email protected] AND createtime<[email protected] ");
             }
             if (!string.IsNullOrWhiteSpace(mqpathid))
             {
                 parameters.Add(new ProcedureParameter("mqpathid", mqpathid));
                 where.Append(" AND [email protected] ");
             }
             if (!string.IsNullOrWhiteSpace(mqpath))
             {
                 parameters.Add(new ProcedureParameter("mqpath", mqpath));
                 where.Append(" AND [email protected] ");
             }
             if (!string.IsNullOrWhiteSpace(methodname))
             {
                 parameters.Add(new ProcedureParameter("methodname", methodname));
                 where.Append(" AND methodname like '%'[email protected]+'%' ");
             }
             if (!string.IsNullOrWhiteSpace(info))
             {
                 parameters.Add(new ProcedureParameter("info", info));
                 where.Append(" AND info like '%'[email protected]+'%' ");
             }
             StringBuilder sql = new StringBuilder();
             sql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_debuglog WITH(NOLOCK)");
             string countSql = string.Concat("SELECT COUNT(1) FROM tb_debuglog WITH(NOLOCK) ", where.ToString());
             object obj = conn.ExecuteScalar(countSql, parameters);
             if (obj != DBNull.Value && obj != null)
             {
                 tempCount = LibConvert.ObjToInt(obj);
             }
             string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
             DataTable dt = conn.SqlToDataTable(sqlPage, parameters);
             if (dt != null && dt.Rows.Count > 0)
             {
                 foreach (DataRow dr in dt.Rows)
                 {
                     tb_debuglog_model model = CreateModel(dr);
                     list.Add(model);
                 }
             }
             return list;

             });
            count = tempCount;
            return result;
        }

 public int GetVersion(DbConn PubConn, int taskid)
 {
     return SqlHelper.Visit(ps =>
     {
         ps.Add("taskid", taskid);
         string sql = "select max(version) version from tb_version where [email protected]";
         int version = Convert.ToInt32(PubConn.ExecuteScalar(sql, ps.ToParameters()));
         return version;
     });
 }

 public long GetLastMqIdByPartitionId(DbConn conn, int partitionId)
 {
     return SqlHelper.Visit((ps) =>
        {
        string sql = string.Format("SELECT lastmqid FROM tb_consumer_partition WITH(NOLOCK) WHERE partitionId={0}", partitionId.Tostring());
        object obj = conn.ExecuteScalar(sql, null);
        if (obj != DBNull.Value && obj != null)
        {
            return LibConvert.ObjToInt64(obj);
        }
        return 0;
        });
 }

 public IList<tb_producterview_model> GetPageList(DbConn conn, string mqpathid, string name, string ip, int pageIndex, int pageSize, ref int count)
 {
     int tempCount = 0;
     IList<tb_producterview_model> list = new List<tb_producterview_model>();
     var result = SqlHelper.Visit((ps) =>
     {
         StringBuilder where = new StringBuilder("");// WHERE 1=1
         if (!string.IsNullOrEmpty(name))
         {
             where.AppendFormat(" AND p.productername LIKE '%{0}%'", name);
         }
         if (!string.IsNullOrEmpty(ip))
         {
             where.AppendFormat(" AND p.ip='{0}'", ip);
         }
         if (!string.IsNullOrWhiteSpace(mqpathid))
         {
             int temp = 0;
             if (int.TryParse(mqpathid, out temp))
             {
                 where.AppendFormat(" and (p.mqpathid='{0}')", mqpathid);
             }
             else
             {
                 where.AppendFormat(" and (m.mqpath like '%'+'{0}'+'%')", mqpathid);
             }
         }
         string sql = "SELECT ROW_NUMBER() OVER(ORDER BY p.Id DESC) AS rownum,p.*,m.mqpath FROM tb_producter p WITH(NOLOCK),tb_mqpath m WITH(NOLOCK) where p.mqpathid=m.id ";
         string countSql = "SELECT COUNT(1) FROM tb_producter p WITH(NOLOCK),tb_mqpath m WITH(NOLOCK) where p.mqpathid=m.id " + where;
         object obj = conn.ExecuteScalar(countSql, null);
         if (obj != DBNull.Value && obj != null)
         {
             tempCount = LibConvert.ObjToInt(obj);
         }
         string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
         DataTable dt = conn.SqlToDataTable(sqlPage, null);
         if (dt != null && dt.Rows.Count > 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 tb_producterview_model model = new tb_producterview_model();
                 model.ProducterModel = CreateModel(dr);
                 model.mqpath = Convert.ToString(dr["mqpath"]);
                 list.Add(model);
             }
         }
         return list;
     });
     count = tempCount;
     return result;
 }

 public int GetNonProductCount(DbConn conn, int mqPathId, int sec)
 {
     return SqlHelper.Visit((ps) =>
     {
         string sql = string.Format("SELECT COUNT(1) FROM tb_producter WITH(NOLOCK) WHERE [email protected] AND datediff(s,lastheartbeat,getdate())>={0}", sec);
         ps.Add("@mqpathid", mqPathId);
         object obj = conn.ExecuteScalar(sql, ps.ToParameters());
         if (obj != DBNull.Value && obj != null)
         {
             return LibConvert.ObjToInt(obj);
         }
         return 0;
     });
 }

 public int GetActiveConsumerCount(DbConn conn, int mqpathId)
 {
     return SqlHelper.Visit((ps) =>
     {
         string sql = "SELECT COUNT (p.consumerclientid) FROM tb_consumer_partition p WITH(NOLOCK),tb_consumer c WITH(NOLOCK),tb_mqpath_partition m WITH(NOLOCK) WHERE p.lastconsumertempid=c.tempid and p.partitionid=m.partitionid and [email protected]";
         ps.Add("@mqpathid", mqpathId);
         object obj = conn.ExecuteScalar(sql, ps.ToParameters());
         if (obj != DBNull.Value && obj != null)
         {
             return LibConvert.ObjToInt(obj);
         }
         return 0;
     });
 }

 public bool AddReport(DbConn conn, tb_partition_messagequeue_report_model model)
 {
     return SqlHelper.Visit((ps) =>
      {
      string sql = "SELECT ID from tb_partition_messagequeue_report WITH(NOLOCK) WHERE [email protected] AND [email protected]";
      ps.Add("@partitionid", model.partitionid);
      ps.Add("@day", model.day);
      object obj = conn.ExecuteScalar(sql, ps.ToParameters());
      if (obj != DBNull.Value && obj != null)
      {
          int id = Convert.ToInt32(obj);
          return this.UpdateMsgCount(conn, model.mqmaxid, model.mqminid, model.mqcount, id);
      }
      else
      {
          return Add2(conn, model);
      }
      });
 }

 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 ";
         if (!string.IsNullOrWhiteSpace(keyword))
         {
             ps.Add("keyword", keyword);
             sqlwhere = " and (nodename like '%'[email protected]+'%' or nodeip like '%'[email protected]+'%') ";
         }
         DateTime d = DateTime.Now;
         if (DateTime.TryParse(cstime, out d))
         {
             ps.Add("CStime", Convert.ToDateTime(cstime));
             sqlwhere += " and nodecreatetime>[email protected]";
         }
         if (DateTime.TryParse(cetime, out d))
         {
             ps.Add("CEtime", Convert.ToDateTime(cetime));
             sqlwhere += " and nodecreatetime<[email protected]";
         }
         _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;
         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 List<tb_tasklist_model> GetList(DbConn PubConn, string taskid, string keyword, string CStime, string CEtime, int categoryid, int nodeid ,int userid, int state , int pagesize, int pageindex, out int count)
 {
     int _count = 0;
     List<tb_tasklist_model> model = new List<tb_tasklist_model>();
     DataSet dsList = SqlHelper.Visit<DataSet>(ps =>
     {
         string sqlwhere = "";
         StringBuilder sql = new StringBuilder();
         sql.Append("select ROW_NUMBER() over(order by T.id desc) as rownum,T.*,C.categoryname,N.nodename,U.username from tb_task T ");
         sql.Append("left join tb_category C on C.id=T.categoryid ");
         sql.Append("left join tb_user U on U.id=T.taskcreateuserid ");
         sql.Append("left join tb_node N on N.id=T.nodeid where 1=1 ");
         if (!string.IsNullOrWhiteSpace(taskid))
         {
             ps.Add("taskid", taskid);
             sqlwhere += " and ( T.id [email protected] )";
         }
         if (!string.IsNullOrWhiteSpace(keyword))
         {
             ps.Add("keyword", keyword);
             sqlwhere += " and ( T.taskname like '%'[email protected]+'%' or T.taskremark like '%'[email protected]+'%' )";
         }
         if (categoryid != -1)
         {
             ps.Add("categoryid", categoryid);
             sqlwhere += " and [email protected]";
         }
         if (nodeid != -1)
         {
             ps.Add("nodeid", nodeid);
             sqlwhere += " and [email protected]";
         }
         if (state != -999)
         {
             ps.Add("taskstate", state);
             sqlwhere += " and [email protected]";
         }
         if (userid != -1)
         {
             ps.Add("taskcreateuserid", userid);
             sqlwhere += " and [email protected]";
         }
         DateTime d=DateTime.Now;
         if (DateTime.TryParse(CStime, out d))
         {
             ps.Add("CStime", Convert.ToDateTime(CStime));
             sqlwhere += " and T.taskcreatetime>[email protected]";
         }
         if (DateTime.TryParse(CEtime, out d))
         {
             ps.Add("CEtime", Convert.ToDateTime(CEtime));
             sqlwhere += " and T.taskcreatetime<[email protected]";
         }
         _count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_task T 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;
         PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters());
         return ds;
     });
     foreach (DataRow dr in dsList.Tables[0].Rows)
     {
         tb_tasklist_model m = CreateModelList(dr);
         model.Add(m);
     }
     count = _count;
     return model;
 }

 public int SetState(DbConn conn, int partitionid,int state)
 {
     return SqlHelper.Visit((ps) =>
     {
         string sql = "update tb_mqpath_partition set [email protected] WHERE [email protected]";
         ps.Add("partitionid", partitionid);
         ps.Add("state", state);
         object obj = conn.ExecuteScalar(sql, ps.ToParameters());
         if (obj != DBNull.Value && obj != null)
         {
             return LibConvert.ObjToInt(obj);
         }
         return 0;
     });
 }

 public int GetCountOfPartition(DbConn conn,int mqpathid)
 {
     return SqlHelper.Visit((ps) =>
     {
         ps.Add("@mqpathid", mqpathid);
         string sql = "select count(0) FROM tb_mqpath_partition WITH(NOLOCK)  WHERE [email protected]";
         return Convert.ToInt32(conn.ExecuteScalar(sql,ps.ToParameters()));
     });
 }

 public IList<tb_messagequeue_model> GetPageList(DbConn conn, int pageIndex, int pageSize, string id, ref int count)
 {
     int tempCount = 0;
     IList<tb_messagequeue_model> list = new List<tb_messagequeue_model>();
     var result = SqlHelper.Visit((ps) =>
     {
         StringBuilder where = new StringBuilder(" WHERE 1=1");
         if (!string.IsNullOrWhiteSpace(id))
         {
             where.AppendFormat(" AND id={0}", id);
         }
         string sql = string.Format("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM {0}  WITH(NOLOCK)", TableName) + where;
         string countSql = string.Format("SELECT COUNT(1) FROM {0} WITH(NOLOCK)", TableName) + where;
         object obj = conn.ExecuteScalar(countSql, null);
         if (obj != DBNull.Value && obj != null)
         {
             tempCount = LibConvert.ObjToInt(obj);
         }
         string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
         DataTable dt = conn.SqlToDataTable(sqlPage, null);
         if (dt != null && dt.Rows.Count > 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 tb_messagequeue_model model = CreateModel(dr);
                 list.Add(model);
             }
         }
         return list;
     });
     count = tempCount;
     return result;
 }

 public long GetMaxId(DbConn PubConn)
 {
     return SqlHelper.Visit((ps) =>
     {
         string cmd = string.Format("select max(id) from {0} s WITH(NOLOCK)", TableName);
         var o = PubConn.ExecuteScalar(cmd, null);
         if (o == null || o is DBNull)
             return -1;
         else
             return Convert.ToInt64(o);
     });
 }

        public IList<ConsumerModel> GetPageList(DbConn conn, string name, int pageIndex, int pageSize, ref int count)
        {
            int tempCount = 0;
            IList<ConsumerModel> list = new List<ConsumerModel>();
            ConsumerModel cm = new ConsumerModel();
            var result = SqlHelper.Visit((ps) =>
            {
                StringBuilder where = new StringBuilder(" WHERE 1=1 ");
                if (!string.IsNullOrEmpty(name))
                {
                    where.AppendFormat(" AND  clientname LIKE '%{0}%'", name);
                }
                string sql = "SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_consumer WITH(NOLOCK)";
                string countSql = "SELECT COUNT(1) FROM tb_consumer WITH(NOLOCK) " + where.ToString();
                object obj = conn.ExecuteScalar(countSql, null);
                if (obj != DBNull.Value && obj != null)
                {
                    tempCount = LibConvert.ObjToInt(obj);
                }
                string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
                DataTable dt = conn.SqlToDataTable(sqlPage, null);
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        ConsumerModel model = cm.CreateModel(dr);

                        IList<tb_consumer_partition_model> consumerList = partitionDal.GetPartitionByConsumerId(conn, model.consumerclientid);
                        if (consumerList != null && consumerList.Count > 0)
                        {
                            IList<ConsumerPartition> partitionList = new List<ConsumerPartition>();

                            foreach (var item in consumerList)
                            {
                                ConsumerPartition m = new ConsumerPartition();
                                m.PartitionId = item.partitionid;

                                PartitionIDInfo partitionInfo = PartitionRuleHelper.GetPartitionIDInfo(item.partitionid);
                                string node = string.Empty;
                                if (partitionInfo.DataNodePartition < 10)
                                {
                                    node = "0" + partitionInfo.DataNodePartition.Tostring();
                                }
                                else
                                {
                                    node = partitionInfo.DataNodePartition.Tostring();
                                }

                                using (DbConn nodeConn = DbConfig.CreateConn(DataConfig.DataNodeParConn(node)))
                                {
                                    nodeConn.Open();
                                    tb_partition_model partitionModel = new tb_partition_dal().Get(conn, item.partitionid);
                                    if (partitionModel != null)
                                    {
                                        m.IsOnline = partitionModel.isused;
                                    }
                                    string table = msgDal.GetMaxMqTable(nodeConn, node);
                                    m.Msg = msgDal.GetMsgCount(nodeConn, table, 0);
                                    m.NonMsg = msgDal.GetMsgCount(nodeConn, table, 1);
                                    partitionList.Add(m);
                                }
                            }
                            model.PartitionList = partitionList;
                        }
                        list.Add(model);
                    }
                }
                return list;
            });
            count = tempCount;
            return result;
        }