C# DbConn.SqlToDataTable方法代码示例

 public List<tb_partition_model> GetClientCreateTime(DbConn conn)
 {
     try
     {
         List<tb_partition_model> list = new List<tb_partition_model>();
         string sql = @"select partitionid,clientname,tb_consumer_partition.createtime from tb_consumer_partition,[tb_consumer]  WITH(NOLOCK)
                         where [tb_consumer].tempid=tb_consumer_partition.lastconsumertempid";
         DataTable dt = conn.SqlToDataTable(sql, null);
         if (dt != null && dt.Rows.Count > 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 tb_partition_model model = new tb_partition_model();
                 model.partitionid = Convert.ToInt32(dr["partitionid"]);
                 model.createtime = Convert.ToDateTime(dr["createtime"]);
                 list.Add(model);
             }
         }
         return list;
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

 /// <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 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 tb_consumer_partition_model GetByPartitionId(DbConn conn, int partitionId)
 {
     return SqlHelper.Visit((ps) =>
     {
         IList<int> list = new List<int>();
         string sql = "SELECT top 1 * FROM tb_consumer_partition WITH(NOLOCK) WHERE [email protected]";
         ps.Add("@id", partitionId);
         DataTable dt = conn.SqlToDataTable(sql, ps.ToParameters());
         tb_consumer_partition_model model = null;
         if (dt != null && dt.Rows.Count > 0)
         {
             model = CreateModel(dt.Rows[0]);
         }
         return model;
     });
 }

 /// <summary>
 ///获取所有队列
 /// </summary>
 /// <param name="conn"></param>
 /// <returns></returns>
 public IList<tb_mqpath_model> GetAllMaPath(DbConn conn)
 {
     return SqlHelper.Visit((ps) =>
     {
         IList<tb_mqpath_model> list = new List<tb_mqpath_model>();
         string sql = "SELECT * FROM tb_mqpath WITH(NOLOCK)";
         DataTable dt = conn.SqlToDataTable(sql, null);
         if (dt != null && dt.Rows.Count > 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 tb_mqpath_model model = CreateModel(dr);
                 list.Add(model);
             }
         }
         return list;
     });
 }

 public virtual List<tb_consumer_model> GetAllList(DbConn PubConn)
 {
     return SqlHelper.Visit((ps) =>
     {
         List<tb_consumer_model> rs = new List<tb_consumer_model>();
         string sql = "SELECT * FROM  tb_consumer  WITH(NOLOCK) ";
         DataTable dt = PubConn.SqlToDataTable(sql, ps.ToParameters());
         if (dt != null && dt.Rows.Count > 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 tb_consumer_model model = CreateModel(dr);
                 rs.Add(model);
             }
         }
         return rs;
     });
 }

 /// <summary>
 /// ��ȡ���нڵ�
 /// </summary>
 /// <param name="conn"></param>
 /// <returns></returns>
 public IList<string> GetNodeList(DbConn conn)
 {
     return SqlHelper.Visit((ps) =>
        {
        string sql = "SELECT datanodepartition FROM tb_datanode  WITH(NOLOCK)  ORDER BY  datanodepartition";
        IList<string> list = new List<string>();
        DataTable dt = conn.SqlToDataTable(sql, null);
        string temp = string.Empty;
        if (dt != null && dt.Rows.Count > 0)
        {
            foreach (DataRow dr in dt.Rows)
            {
                int node = LibConvert.ObjToInt(dr["datanodepartition"]);
                list.Add(XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.PartitionNameRule(node));
            }
        }
        return list;
        });
 }

        public IList<MqPathPartitionModel> GetPageList(DbConn conn, string mqPathid, string partitionId, int pageIndex, int pageSize, ref int count)
        {
            int tempCount = 0;
            IList<MqPathPartitionModel> list = new List<MqPathPartitionModel>();

            var result = SqlHelper.Visit((ps) =>
            {
                StringBuilder where = new StringBuilder("");
                if (!string.IsNullOrWhiteSpace(mqPathid))
                {
                    if (!mqPathid.isint())
                        where.AppendFormat(" AND m.mqpath like '%'+'{0}'+'%'", mqPathid);
                    else
                        where.AppendFormat(" AND m.id ='{0}'", mqPathid);
                }
                if (!string.IsNullOrWhiteSpace(partitionId))
                {
                    where.AppendFormat(" AND p.partitionId={0}", partitionId);
                }
                string sql = "SELECT ROW_NUMBER() OVER(ORDER BY p.mqpathid DESC,p.[partitionindex] desc) AS rownum,p.*,m.mqpath,(select max(partitionindex) from tb_mqpath_partition p1 where p.mqpathid=p1.mqpathid) as maxpartitionindex FROM tb_mqpath_partition p WITH(NOLOCK),tb_mqpath m with(nolock) where p.mqpathid=m.id";
                string countSql = "SELECT COUNT(1) FROM tb_mqpath_partition p WITH(NOLOCK),tb_mqpath m with(nolock) where p.mqpathid=m.id" + 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)
                    {
                        MqPathPartitionModel m = new MqPathPartitionModel();
                        m.mqpath_partition_model = CreateModel(dr);
                        m.mqpath = Convert.ToString(dr["mqpath"]);
                        m.maxpartitionindex = Convert.ToInt32(dr["maxpartitionindex"]);
                        list.Add(m);
                    }
                }
                return list;
            });
            count = tempCount;
            return result;
        }

 public List<ConsumerPartitionModel> ListByPartitionIds(DbConn conn, List<int> partitionids)
 {
     return SqlHelper.Visit((ps) =>
     {
         var pps = ps.ToParameters();
         List<ConsumerPartitionModel> list = new List<ConsumerPartitionModel>();
         if (partitionids.Count > 0)
         {
             string sql = string.Format("SELECT p.*,c.client FROM tb_consumer_partition  p WITH(NOLOCK),tb_consumer_client c WITH(NOLOCK) WHERE p.consumerclientid=c.id and p.partitionid in ({0})", SqlHelper.CmdIn<int>(pps, partitionids));
             DataTable dt = conn.SqlToDataTable(sql, pps);
             if (dt != null && dt.Rows.Count > 0)
             {
                 foreach (DataRow dr in dt.Rows)
                 {
                     ConsumerPartitionModel m = new ConsumerPartitionModel();
                     m.consumerpartitionmodel = CreateModel(dr);
                     m.msgCount = reportDal.GetMsgCount(conn, m.consumerpartitionmodel.lastmqid);
                     m.nonMsgCount = reportDal.GetNonMsgCount(conn,m.consumerpartitionmodel.lastmqid);
                     m.client = Convert.ToString(dr["client"]);
                     list.Add(m);
                 }
             }
         }
         return list;
     });
 }

 public IList<int> GetPartitionByPartitionId(DbConn conn, int partitionId)
 {
     return SqlHelper.Visit((ps) =>
     {
         IList<int> list = new List<int>();
         string sql = "SELECT * FROM tb_consumer_partition WITH(NOLOCK) WHERE [email protected]";
         ps.Add("@id", partitionId);
         DataTable dt = conn.SqlToDataTable(sql, ps.ToParameters());
         if (dt != null && dt.Rows.Count > 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 tb_consumer_partition_model model = CreateModel(dr);
                 list.Add(model.consumerclientid);
             }
         }
         return list;
     });
 }

 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 tb_messagequeue_model GetModel(DbConn conn, long id, string tableName)
 {
     return SqlHelper.Visit((ps) =>
        {
        tb_messagequeue_model model = null;
        string sql = string.Format("SELECT TOP 1 * FROM {0}  WITH(NOLOCK)  WHERE [email protected]", tableName);
        ps.Add("@id", id);
        DataTable dt = conn.SqlToDataTable(sql, ps.ToParameters());
        if (dt != null && dt.Rows.Count > 0)
        {
            foreach (DataRow dr in dt.Rows)
            {
                model = CreateModel(dr);
            }
        }
        return model;
        });
 }

 /// <summary>
 /// ��ȡָ��node�ڵ��µ����б���
 /// </summary>
 /// <param name="conn"></param>
 /// <param name="node"></param>
 /// <returns></returns>
 public ICollection<string> GetDataNodeTableString(DbConn conn)
 {
     return SqlHelper.Visit((ps) =>
     {
         string sql = "SELECT Name FROM SysObjects  WITH(NOLOCK)  Where XType='U' order BY Name DESC";
         IList<string> list = new List<string>();
         DataTable dt = conn.SqlToDataTable(sql, null);
         if (dt != null && dt.Rows.Count > 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 list.Add(dr["Name"].Tostring());
             }
         }
         return list;
     });
 }

 public List<TableInfo> GetDataNodeTable(DbConn conn, DateTime fromcreatedate)
 {
     return SqlHelper.Visit((ps) =>
     {
         string sql = "SELECT Name FROM SysObjects  WITH(NOLOCK)  Where XType='U' and name like 'tb_messagequeue_%' and crdate>[email protected] order by name desc";
         ps.Add("crdate", fromcreatedate);
         List<TableInfo> list = new List<TableInfo>();
         DataTable dt = conn.SqlToDataTable(sql, ps.ToParameters());
         if (dt != null && dt.Rows.Count > 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 list.Add(XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.GetTableInfo(Convert.ToString(dr["Name"])));
             }
         }
         return list;
     });
 }