public Task <List <Product> > ListAll(int skip, int take, string orderBy, string direction = "DESC", string search = "") { var product = Task.FromResult(_dapperManager.GetAll <Product> ($"select P.ProductName, P.ProductPrice, P.ProductOrigin, P.ProductDetails , sum(S.StockQty) As ProductTotalStock from [Product] as P Inner Join [Stock] as S on P.ID = S.ProductID WHERE P.ProductName like '%{search}%' AND P.DELETED = 'False' ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY group by P.ProductName, P.ProductOrigin, P.ProductPrice, P.ProductDetails; ", null, commandType: CommandType.Text)); return(product); }
//Metodo que obtiene una lista de todos los clientes habilitados de la empresa, que recibe por parametros el id de la institucion public Task <List <Clientes> > ListarClientes(string id_institucion) { var clientes = Task.FromResult(_dapperManager.GetAll <Clientes> ($"SELECT * FROM [Clientes] WHERE ID_Institucion = '{id_institucion}' AND Estado = 'Habilitado'", null, commandType: CommandType.Text)); return(clientes); }
public Task <List <Vehiculos> > ListAll(string id_institucion) { var vehiculos = Task.FromResult(_dapperManager.GetAll <Vehiculos> ($"SELECT * FROM [Vehiculos] WHERE ID_Institucion = '{id_institucion}' AND Estado = 'Habilitado'", null, commandType: CommandType.Text)); return(vehiculos); }
public Task <List <Customer> > ListAll(string customerID) { var customer = Task.FromResult(_dapperManager.GetAll <Customer> ($"SELECT * FROM [Customer] WHERE CAST(CustomerPhone AS VARCHAR(20)) LIKE '{customerID}%';", null, commandType: CommandType.Text)); return(customer); }
public Task <List <Stock> > ListAll(int skip, int take, string orderBy, int productID, string direction = "DESC") { var stock = Task.FromResult(_dapperManager.GetAll <Stock> ($"SELECT * FROM [Stock] WHERE ProductID = {productID} ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ", null, commandType: CommandType.Text)); return(stock); }
public Task <Clients> GetById(int id) { return(Task.Run(() => { var article = _dapperManager.Get <Clients>($"select * from [Clients] where ClientId = {id}", null, commandType: CommandType.Text); article.BookList = _dapperManager.GetAll <Books>($"select b.* From Books as b Inner join ClientBook as c on c.BookId = b.BookId where c.ClientId = {id}", null, commandType: CommandType.Text); return article; })); }
public async Task <List <dtInvAccounts> > GetAccounts(string AccCategory) { var dbPara = new DynamicParameters(); dbPara.Add("AccCategory", AccCategory, DbType.String); dbPara.Add("Criteria", "AccountMaster", DbType.String); //dtInvVoucher voucher = new dtInvVoucher(); var Accounts = Task.FromResult(_dapperManager.GetAll <dtInvAccounts> ("[FINWEB_INVENTORYVoucherSP]", dbPara, commandType: CommandType.StoredProcedure)); //($"SELECT * FROM [Article] WHERE Title like '%{search}%' ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ", null, commandType: CommandType.Text)); return(await Accounts); }
public Task <Books> GetById(int id) { return(Task.Run(() => { var article = _dapperManager.Get <Books>($"select * from [Books] where BookId = {id}", null, commandType: CommandType.Text); article.AuthorList = _dapperManager.GetAll <Authors> ($"SELECT a.* FROM dbo.Authors a INNER JOIN dbo.AuthorBook ab ON a.AuthorId = ab.AuthorId WHERE ab.BookId = {id}; ", null, commandType: CommandType.Text); article.BookGenreList = _dapperManager.GetAll <BookGenres> ($"SELECT a.* FROM dbo.BookGenres a INNER JOIN dbo.BookBookGenres ab ON a.BookGenreId = ab.BookGenreId WHERE ab.BookId = {id}; ", null, commandType: CommandType.Text); return article; } )); }
public Task <List <Factura> > ListAll(string id_institucion) { var vehiculos = Task.FromResult(_dapperManager.GetAll <Factura> ($"SELECT * FROM [Factura] WHERE ID_Institucion = '{id_institucion}'", null, commandType: CommandType.Text)); return(vehiculos); }
public Task <List <c_order> > ListAll(int skip, int take, string orderBy, string direction = "DESC", string search = "") { var c_Orders = Task.FromResult(_dapperManager.GetAll <c_order> ($"SELECT * FROM c_order WHERE name like '%{search}%' ORDER BY {orderBy} {direction} LIMIT {skip}, {take} ; ", null, commandType: CommandType.Text)); return(c_Orders); //LIMIT {skip} ROWS FETCH NEXT {take} ROWS ONLY }
public Task <List <Article> > ListAll(int skip, int take, string orderBy, string direction = "DESC", string search = "") { var articles = Task.FromResult(_dapperManager.GetAll <Article> ($"SELECT * FROM [Article] WHERE Title like '%{search}%' ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ", null, commandType: CommandType.Text)); return(articles); }
public Task <List <Article> > ListAll(int skip, int take, string orderBy, string direction = "DESC", string search = "") { var articles = Task.FromResult(_dapperManager.GetAll <Article> ($"WITH Artcl AS (SELECT ROW_NUMBER() OVER(ORDER BY a.ID) AS rownumber, * FROM [Article] as a WHERE Title LIKE '%{search}%') SELECT * FROM Artcl WHERE (rownumber > {take} * {skip}) AND (rownumber <= {take} * {skip + 1}) ORDER BY {orderBy} {direction}; ", null, commandType: CommandType.Text)); return(articles); }
public async Task <List <LoginModel> > LoginUserNew(string UserName, string Password) { var dbPara = new DynamicParameters(); //dbPara.Add("Criteria", "AccountMaster", DbType.String); var Accounts = Task.FromResult(_dapperManager.GetAll <LoginModel> ("Select ID,UserName,Password from [dbo].[Users]", dbPara, commandType: CommandType.Text)); return(await Accounts); }
public async override Task <IResponseMessage <GeneralSettingsVM> > GetAll(QueryParameters request) { string sql = ""; var dbPara = new DynamicParameters(); #region Page and filter code sample Codes //string filter = string.IsNullOrEmpty(request.filter) ? "" :request.filter; //dbPara.Add("@Filter", (string.IsNullOrEmpty(filter) ? "" : filter.ToLower()), DbType.String); //dbPara.Add("@Offset", request.Offset, DbType.Int32); //dbPara.Add("@Next", request.Next, DbType.Int32); //string order = ""; //if (string.IsNullOrEmpty(request.OrderBy)) //{ // order = "BranchID"; //} //else { order = request.OrderBy; } //sql = "; with pagedDT as "; //sql += " ( "; //sql += " select *, ROW_NUMBER() OVER(ORDER BY " + order + " " + request.SortDirection + ") AS rowNumber from tb_branches "; //if (!string.IsNullOrEmpty(request.filter)) //{ // sql += " where (Lower(BranchName) like '%'+@Filter+'%' ) "; //} //sql += " ) "; //sql += " select *,(select Count(*) from tb_branches) as TotalCount from pagedDT "; //sql += " where "; //sql += " (rowNumber > @Offset and rowNumber<= @Next) "; //if (settings != null && settings.Count > 0 && !string.IsNullOrEmpty(request.filter)) //{ // settings[0].TotalCount = settings.Count(); //} #endregion sql = "select * from dbo.GeneralSettings"; var settings = await Task.FromResult(dapperManager.GetAll <GeneralSettingsVM>(sql, dbPara, commandType: CommandType.Text)); return(new ResponseMessageBase <GeneralSettingsVM>() { MessageType = MessageTypes.Success, ResponseData = settings }); }
public async Task <List <dtInvVoucherEntry> > GetVoucherEntry(long vid) { var dbPara = new DynamicParameters(); dbPara.Add("VId", vid, DbType.Int32); dbPara.Add("Criteria", "VoucherEntry", DbType.String); //dtInvVoucher voucher = new dtInvVoucher(); var voucherentry = Task.FromResult(_dapperManager.GetAll <dtInvVoucherEntry> ("[FINWEB_INVENTORYVoucherSP]", dbPara, commandType: CommandType.StoredProcedure)); //($"SELECT * FROM [Article] WHERE Title like '%{search}%' ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ", null, commandType: CommandType.Text)); return(await voucherentry); }
public async Task <List <VoucherMaster> > ListAll(int vtype) //(int skip, int take, string orderBy, string direction = "DESC", string search = "") { var dbPara = new DynamicParameters(); dbPara.Add("VType", vtype, DbType.Int32); dbPara.Add("BranchId", 31, DbType.Int32); dbPara.Add("Criteria", "VoucherMaster", DbType.String); var vouchermaster = Task.FromResult(_dapperManager.GetAll <VoucherMaster> ("[FINWEB_INVENTORYVoucherSP]", dbPara, commandType: CommandType.StoredProcedure)); //($"SELECT * FROM [Article] WHERE Title like '%{search}%' ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ", null, commandType: CommandType.Text)); return(await vouchermaster); }
public Task <List <Article> > ListAll() { var articles = Task.FromResult(_dapperManager.GetAll <Article>("select * from [Article]", null, commandType: CommandType.Text)); return(articles); }