C# Db.DbConn类代码示例

 /// <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;
 }

        public virtual bool Add(DbConn PubConn, tb_user_model model)
        {

            List<ProcedureParameter> Par = new List<ProcedureParameter>()
                {
					
					//员工工号
					new ProcedureParameter("@userstaffno",    model.userstaffno),
					//
					new ProcedureParameter("@username",    model.username),
					//员工角色，查看代码枚举：开发人员，管理员
					new ProcedureParameter("@userrole",    model.userrole),
					//
					new ProcedureParameter("@usercreatetime",    model.usercreatetime),
					//员工手机号码
					new ProcedureParameter("@usertel",    model.usertel),
					//
					new ProcedureParameter("@useremail",    model.useremail),
					//登录密码
					new ProcedureParameter("@userpsw",    model.userpsw)  
                };
            int rev = PubConn.ExecuteSql(@"insert into tb_user(userstaffno,username,userrole,usercreatetime,usertel,useremail,userpsw)
										   values(@userstaffno,@username,@userrole,@usercreatetime,@usertel,@useremail,@userpsw)", Par);
            return rev == 1;

        }

        public virtual bool Edit(DbConn PubConn, tb_user_model model)
        {
            List<ProcedureParameter> Par = new List<ProcedureParameter>()
            {
                    
					//员工工号
					new ProcedureParameter("@userstaffno",    model.userstaffno),
					//
					new ProcedureParameter("@username",    model.username),
					//员工角色，查看代码枚举：开发人员，管理员
					new ProcedureParameter("@userrole",    model.userrole),
					//
					new ProcedureParameter("@usercreatetime",    model.usercreatetime),
					//员工手机号码
					new ProcedureParameter("@usertel",    model.usertel),
					//
					new ProcedureParameter("@useremail",    model.useremail),
                    //登录密码
					new ProcedureParameter("@userpsw",    model.userpsw) 
            };
            Par.Add(new ProcedureParameter("@id", model.id));

            int rev = PubConn.ExecuteSql("update tb_user set [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected] where [email protected]", Par);
            return rev == 1;

        }

        public virtual bool Add2(DbConn PubConn, tb_consumer_model model)
        {
            return SqlHelper.Visit((ps) =>
            {
                List<ProcedureParameter> Par = new List<ProcedureParameter>()
                    {

                        //��������ʱid(�����������Ψһ,Guidתlong)
                        new ProcedureParameter("@tempid",    model.tempid),
                        //������clinet��id
                        new ProcedureParameter("@consumerclientid",    model.consumerclientid),
                        //֧�ֵķ���˳���(֧�ֶ��˳���)
                        new ProcedureParameter("@partitionindexs",    model.partitionindexs),
                        //�ͻ�������
                        new ProcedureParameter("@clientname",    model.clientname),
                        ////�������ʱ��(�Ե�ǰ��ʱ��Ϊ׼)
                        //new ProcedureParameter("@lastheartbeat",    model.lastheartbeat),
                        ////��һ�θ���ʱ��(�Ե�ǰ��ʱ��Ϊ׼)
                        //new ProcedureParameter("@lastupdatetime",    model.lastupdatetime),
                        ////�ͻ��˴���ʱ��(�Ե�ǰ��ʱ��Ϊ׼)
                        //new ProcedureParameter("@createtime",    model.createtime)
                    };
                int rev = PubConn.ExecuteSql(@"insert into tb_consumer(tempid,consumerclientid,partitionindexs,clientname,lastheartbeat,lastupdatetime,createtime)
                                               values(@tempid,@consumerclientid,@partitionindexs,@clientname,getdate(),getdate(),getdate())", Par);
                return rev == 1;
            });
        }

 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;
     }
 }

 public virtual List<tb_shuntruleconn_config_model> Get(DbConn PubConn,DateTime lastUpdateTime)
 {
     return XXF.ProjectTool.SqlHelper.Visit(ps =>
     {
         List<ProcedureParameter> Par = new List<ProcedureParameter>();
         StringBuilder stringSql = new StringBuilder();
         if (lastUpdateTime == default(DateTime))
         {
             stringSql.Append(@"select s.* from tb_distribution_rule_config s");
         }
         else
         {
             stringSql.Append(@"select s.* from tb_distribution_rule_config s where s.f_last_update_time>[email protected]");
             ps.Add("lastTime", lastUpdateTime);
         }
       //      int rev = PubConn.ExecuteSql(stringSql.ToString(), ps.ToParameters());
         DataSet ds = new DataSet();
         PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
         var rs = new List<tb_shuntruleconn_config_model>();
         if (ds != null && ds.Tables.Count > 0)
         {
             foreach (DataRow dr in ds.Tables[0].Rows)
             {
                 var r = CreateModel(dr);
                 rs.Add(r);
             }
         }
         return rs;
     });
 }

 /// <summary>
 /// ɾ������
 /// </summary>
 /// <param name="conn"></param>
 /// <param name="mqNodeConn"></param>
 /// <param name="id"></param>
 /// <param name="partitionId"></param>
 /// <returns></returns>
 public int DeleteMqQueue(DbConn conn, DbConn mqNodeConn, int id, int partitionId)
 {
     int flag = this.IsDeleted(conn, mqNodeConn, id, partitionId);
     if (flag != 1) return flag;
     try
     {
         conn.BeginTransaction();
         if (this.DeletePartition(conn, partitionId))
         {
             if (!partitionDal.UpdateIsUsed(conn, 0, partitionId))
                 throw new Exception("���³���");
         }
         else
         {
             throw new Exception("ɾ������");
         }
         conn.Commit();
     }
     catch (Exception ex)
     {
         conn.Rollback();
         XXF.Log.ErrorLog.Write("ɾ��MQ���г���:", ex);
         return -1;
     }
     return 1;
 }

        /// <summary>
        /// createtime expiresetime不用传。
        /// </summary>
        /// <param name="PubConn"></param>
        /// <param name="model"></param>
        /// <param name="tokentype"></param>
        /// <returns></returns>
        public virtual bool Add(DbConn PubConn, DbModels.tb_token model, Models.DbModels.TokenType tokentype)
        {
            DateTime nowtime = PubConn.GetServerDate();
            model.createtime = nowtime;
            model.expires = nowtime.AddMinutes(GetExpiresminutes(tokentype));
            List<ProcedureParameter> Par = new List<ProcedureParameter>()
                {

                    //
                    new ProcedureParameter("@token",    model.token),
                    //
                    new ProcedureParameter("@userid",    model.userid),
                    new ProcedureParameter("@id",    model.id),
                    //
                    new ProcedureParameter("@username",    model.username),
                    //
                    new ProcedureParameter("@appid",    model.appid),
                    //
                    new ProcedureParameter("@createtime",   model.createtime),
                    //
                    new ProcedureParameter("@expires", model.expires  )
                };
            int rev = PubConn.ExecuteSql("insert into " + tokentype.ToString() + " (token,userid,id,username,appid,createtime,expires)" +
                                         "  values(@token,@userid,@id,@username,@appid,@createtime,@expires)", Par);
            return rev == 1;
        }

        public virtual bool Add2(DbConn PubConn, tb_producter_model model)
        {
            return SqlHelper.Visit((ps) =>
            {
                List<ProcedureParameter> Par = new List<ProcedureParameter>()
                {

                    //��������ʱid(�����������Ψһ,Guidתlong)
                    new ProcedureParameter("@tempid",    model.tempid),
                    //����������
                    new ProcedureParameter("@productername",    model.productername),
                    //ip��ַ
                    new ProcedureParameter("@ip",    model.ip),
                    //����id
                    new ProcedureParameter("@mqpathid",    model.mqpathid),
                    ////�������������ʱ��
                    //new ProcedureParameter("@lastheartbeat",    model.lastheartbeat),
                    ////�����ߴ���ʱ��
                    //new ProcedureParameter("@createtime",    model.createtime)
                };
                int rev = PubConn.ExecuteSql(@"insert into tb_producter(tempid,productername,ip,mqpathid,lastheartbeat,createtime)
                                           values(@tempid,@productername,@ip,@mqpathid,getdate(),getdate())", Par);
                return rev == 1;
            });
        }

        public ProducterInfo GetProducterInfo(DbConn PubConn, string mqpath,string productername)
        {
            ProducterInfo r = new ProducterInfo();
            tb_mqpath_dal mqpathdal = new tb_mqpath_dal();
            r.MqPathModel = mqpathdal.Get(PubConn,mqpath);
            if (r.MqPathModel == null)
                throw new BusinessMQException(string.Format("当前mqpath:{0}未在MQ中注册队列,请联系管理员注册成功后使用。",mqpath));
            tb_mqpath_partition_dal mqpathpartitiondal = new tb_mqpath_partition_dal();
            r.MqPathParitionModel = mqpathpartitiondal.GetList(PubConn,r.MqPathModel.id);
            if (r.MqPathParitionModel == null||r.MqPathParitionModel.Count==0)
                throw new BusinessMQException(string.Format("当前mqpath:{0}未在MQ中分配相应的分区,请联系管理员分配分区后使用。", mqpath));

            //注册生产者
            r.ProducterModel = this.RegisterProducter(PubConn, r.MqPathModel.id, productername);
            //获取分区相关节点
            List<int> datanodepartition = new List<int>();
            foreach (var p in r.MqPathParitionModel)
            {
                var partitionidinfo = PartitionRuleHelper.GetPartitionIDInfo(p.partitionid);
                if (!datanodepartition.Contains(partitionidinfo.DataNodePartition))
                    datanodepartition.Add(partitionidinfo.DataNodePartition);
            }
            r.DataNodeModelDic = this.GetDataNodeModelsDic(PubConn, datanodepartition);

            return r;
        }

 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;
 }

        /// <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 virtual string GetDataNodeConnectString(DbConn PubConn, int datanodepartition)
 {
     tb_datanode_dal dal = new tb_datanode_dal();
     var model = dal.Get2(PubConn, datanodepartition);
     if (model == null)
         throw new BusinessMQException("当前数据节点不存在:" + datanodepartition);
     return GetDataNodeConnectString(SystemParamConfig.Consumer_DataNode_ConnectString_Template, model);
 }

 public Dictionary<int, Model.tb_datanode_model> GetDataNodeModelsDic(DbConn PubConn, List<int> datanodepartition)
 {
     tb_datanode_dal datanodedal = new tb_datanode_dal();
     var rs = new Dictionary<int, Model.tb_datanode_model>();
     foreach (var o in datanodedal.List(PubConn, datanodepartition))
         rs.Add(o.datanodepartition, o);
     return rs;
 }

 public tb_producter_model RegisterProducter(DbConn PubConn, int mqpathid,string productername)
 {
     long tempid = CommonHelper.GenerateIntID();
     tb_producter_dal dal = new tb_producter_dal();
     dal.DeleteNotOnLineByMqpathid(PubConn, mqpathid, SystemParamConfig.Producter_Heartbeat_MAX_TIME_OUT);
     dal.Add2(PubConn, new tb_producter_model() { tempid = tempid, ip = CommonHelper.GetDefaultIP(), mqpathid=mqpathid, productername=productername });
     return dal.Get(PubConn, tempid, mqpathid);
 }