Example #1
0
        public async Task <QueryResult <JwtEnpoint> > QueryByPage(string name, int page, int pageSize)
        {
            QueryResult <JwtEnpoint> result = new QueryResult <JwtEnpoint>();

            await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _jwtConnectionFactory.CreateReadForJwt(), async (conn, transaction) =>
            {
                SqlTransaction sqlTran = null;
                if (transaction != null)
                {
                    sqlTran = (SqlTransaction)transaction;
                }

                using (SqlCommand commond = new SqlCommand()
                {
                    Connection = (SqlConnection)conn,
                    CommandType = CommandType.Text,
                    Transaction = sqlTran,
                    CommandText = string.Format(@"set @currentpage=@page
		                           select @count= count(*) from JwtEndpoint where [name] like @name
		                            
                                    select {0} from JwtEndpoint where [name] like @name
                                    order by [sequence]
		                            offset (@pagesize * (@currentpage - 1)) rows 
		                            fetch next @pagesize rows only;"        , StoreHelper.GetJwtEnpointSelectFields(string.Empty))
                })
                {
                    var parameter = new SqlParameter("@page", SqlDbType.Int)
                    {
                        Value = page
                    };
                    commond.Parameters.Add(parameter);

                    parameter = new SqlParameter("@pagesize", SqlDbType.Int)
                    {
                        Value = pageSize
                    };
                    commond.Parameters.Add(parameter);

                    parameter = new SqlParameter("@name", SqlDbType.VarChar, 155)
                    {
                        Value = $"{name.ToSqlLike()}%"
                    };
                    commond.Parameters.Add(parameter);

                    parameter = new SqlParameter("@count", SqlDbType.Int)
                    {
                        Direction = ParameterDirection.Output
                    };
                    commond.Parameters.Add(parameter);

                    parameter = new SqlParameter("@currentpage", SqlDbType.Int)
                    {
                        Direction = ParameterDirection.Output
                    };
                    commond.Parameters.Add(parameter);

                    commond.Prepare();

                    using (var reader = await commond.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var endpoint = new JwtEnpoint();
                            StoreHelper.SetJwtEnpointSelectFields(endpoint, reader, string.Empty);
                            result.Results.Add(endpoint);
                        }

                        reader.Close();

                        result.TotalCount  = (int)commond.Parameters["@count"].Value;
                        result.CurrentPage = (int)commond.Parameters["@currentpage"].Value;
                    }
                }
            });

            return(result);
        }
Example #2
0
        public async Task <JwtEnpoint> QueryByName(string name)
        {
            JwtEnpoint endpoint = null;

            await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _jwtConnectionFactory.CreateReadForJwt(), async (conn, transaction) =>
            {
                SqlTransaction sqlTran = null;
                if (transaction != null)
                {
                    sqlTran = (SqlTransaction)transaction;
                }

                using (SqlCommand commond = new SqlCommand()
                {
                    Connection = (SqlConnection)conn,
                    CommandType = CommandType.Text,
                    Transaction = sqlTran,
                    CommandText = string.Format(@"select {0} from JwtEndpoint where [name]=@name", StoreHelper.GetJwtEnpointSelectFields(string.Empty))
                })
                {
                    var parameter = new SqlParameter("@name", SqlDbType.VarChar, 150)
                    {
                        Value = name
                    };
                    commond.Parameters.Add(parameter);

                    commond.Prepare();

                    using (var reader = await commond.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            endpoint = new JwtEnpoint();
                            StoreHelper.SetJwtEnpointSelectFields(endpoint, reader, string.Empty);
                        }

                        reader.Close();
                    }
                }
            });

            return(endpoint);
        }