Пример #1
0
 // use ordinals to speed up access to DataReader
 // links:
 //  docLink: http://sql2x.org/documentationLink/327451c3-64a8-4de8-b359-76742d634497
 // parameters:
 //  reader: IDataReader from SQLClient
 public void Populate(IDataReader reader, DefaultUserListDataOrdinals ordinals)
 {
     if (!reader.IsDBNull(ordinals.LastActivityDateTime))
     {
         LastActivityDateTime = reader.GetDateTime(ordinals.LastActivityDateTime);
     }
     if (!reader.IsDBNull(ordinals.DefaultUserName))
     {
         DefaultUserName = reader.GetString(ordinals.DefaultUserName);
     }
     if (!reader.IsDBNull(ordinals.DefaultUserCode))
     {
         DefaultUserCode = reader.GetString(ordinals.DefaultUserCode);
     }
     if (!reader.IsDBNull(ordinals.CreatedDateTime))
     {
         CreatedDateTime = reader.GetDateTime(ordinals.CreatedDateTime);
     }
     if (!reader.IsDBNull(ordinals.Email))
     {
         Email = reader.GetString(ordinals.Email);
     }
     if (!reader.IsDBNull(ordinals.DefaultStateName))
     {
         DefaultStateName = reader.GetString(ordinals.DefaultStateName);
     }
     if (!reader.IsDBNull(ordinals.ActivityCount))
     {
         ActivityCount = reader.GetInt32(ordinals.ActivityCount);
     }
     if (!reader.IsDBNull(ordinals.CreatedByDefaultUserCode))
     {
         CreatedByDefaultUserCode = reader.GetString(ordinals.CreatedByDefaultUserCode);
     }
     if (!reader.IsDBNull(ordinals.CreatedByDefaultUserName))
     {
         CreatedByDefaultUserName = reader.GetString(ordinals.CreatedByDefaultUserName);
     }
     if (!reader.IsDBNull(ordinals.DefaultUserId))
     {
         DefaultUserId = reader.GetGuid(ordinals.DefaultUserId);
     }
 }
Пример #2
0
        /// <summary>Get user list</summary>
        /// <summary>With indicators of user activities</summary>
        /// <cardinality>Many</cardinality>
        public List <DefaultUserListData> DefaultUserList()
        {
            var ret = new List <DefaultUserListData>();

            string sql = @" 
                select top 100
	                 du.last_activity_date_time
	                ,du.default_user_name
	                ,du.default_user_code
	                ,du.date_time				as created_date_time
	                ,du.email
	                ,dsr.default_state_name
	                ,(	select 
			                count(*)
		                from default_user_activity as dua
		                where dua.default_user_id = du.default_user_id
		                )						as activity_count
	                ,duc.default_user_code		as created_by_default_user_code
	                ,duc.default_user_name		as created_by_default_user_name
	                ,du.default_user_id
                from default_user				as du
                inner join default_state_ref	as dsr	on dsr.default_state_rcd = du.default_state_rcd
                inner join default_user			as duc	on duc.default_user_id = du.created_by_default_user_id
                --realwhere
                order by du.last_activity_date_time desc
                ";

            /*
             *                  ,(	select count(*)
             *                      from default_user_activity as dua
             *                      where dua.originating_address = du.default_user_code
             *                      )                       as activity_count
             */
            /*
             *                  ,(	select
             *                          count(*)
             *                      from default_user_activity as dua
             *                      where dua.default_user_id = du.default_user_id
             *                      )						as activity_count
             */
            Logging log = Logging.PerformanceTimeStart("Default", "DataAccessLayer", "DefaultSearch", "DefaultUserList");

            try {
                using (var conn = new SqlConnection(ConfigurationManager.AppSettings["Conn"])) {
                    conn.Open();
                    conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit();

                    using (var command = new SqlCommand(sql, conn)) {
                        log.PerformanceTimePrepare(command);
                        IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);
                        log.PerformanceTimeCheck();

                        var ordinals = new DefaultUserListDataOrdinals(reader);
                        while (reader.Read())
                        {
                            var data = new DefaultUserListData();
                            data.Populate(reader, ordinals);
                            ret.Add(data);
                        }

                        reader.Close();
                    }
                }
            } catch (Exception ex) {
                log.Error(ex);
            }
            return(ret);
        }