/// <summary> /// /// </summary> /// <param name="id"></param> /// <returns></returns> public Cart SelectById(int id) { Cart cart = null; const string sqlStatement = "SELECT [Id], [Cookie], [CartDate], [ItemCount], [Rowid], [CreatedOn], [CreatedBy], [ChangedOn], [ChangedBy] " + "FROM dbo.Cart WHERE [Id]=@Id "; var connection = Db.CreateOpenConnection(); using (var cmd = Db.CreateCommand(sqlStatement, connection)) { cmd.Parameters.Add(Db.CreateParameter("@Id", id)); using (var dr = cmd.ExecuteReader()) { if (dr.Read()) { cart = LoadCart(dr); } } } return(cart); }
/// <summary> /// /// </summary> /// <param name="id"></param> /// <returns></returns> public OrderNumber SelectById(int id) { const string sqlStatement = "SELECT [Id], [Number], [CreatedOn], [CreatedBy], [ChangedOn], [ChangedBy] " + "FROM dbo.OrderNumber WHERE [Id]=@Id "; OrderNumber orderN = null; var connection = Db.CreateOpenConnection(); using (var cmd = Db.CreateCommand(sqlStatement, connection)) { cmd.Parameters.Add(Db.CreateParameter("@Id", id)); using (var dr = cmd.ExecuteReader()) { if (dr.Read()) { orderN = LoadOrderNumber(dr); } } } return(orderN); }
/// <summary> /// /// </summary> /// <returns></returns> public List <OrderNumber> Select() { // WARNING! Performance const string sqlStatement = "SELECT [Id], [Number], [CreatedOn], [CreatedBy], [ChangedOn], [ChangedBy] FROM dbo.OrderNumber "; var result = new List <OrderNumber>(); var connection = Db.CreateOpenConnection(); using (var cmd = Db.CreateCommand(sqlStatement, connection)) { using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { var orderN = LoadOrderNumber(dr); // Mapper result.Add(orderN); } } } return(result); }
/// <summary> /// /// </summary> /// <param name="id"></param> /// <returns></returns> public Dealer SelectById(int id) { const string sqlStatement = "SELECT [Id], [FirstName], [LastName], [CategoryId], [CountryId], [Description], [TotalProducts], [Rowid], [CreatedOn], [CreatedBy], [ChangedOn], [ChangedBy] " + "FROM dbo.Dealer WHERE [Id]=@Id "; Dealer dealer = null; var connection = Db.CreateOpenConnection(); using (var cmd = Db.CreateCommand(sqlStatement, connection)) { cmd.Parameters.Add(Db.CreateParameter("@Id", id)); using (var dr = cmd.ExecuteReader()) { if (dr.Read()) { dealer = LoadDealer(dr); } } } return(dealer); }
/// <summary> /// /// </summary> /// <returns></returns> public List <Dealer> Select() { // WARNING! Performance const string sqlStatement = "SELECT [Id], [FirstName], [LastName], [CategoryId], [CountryId], [Description], [TotalProducts], [Rowid], [CreatedOn], [CreatedBy], [ChangedOn], [ChangedBy] FROM dbo.Dealer "; var result = new List <Dealer>(); var connection = Db.CreateOpenConnection(); using (var cmd = Db.CreateCommand(sqlStatement, connection)) { using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { var dealer = LoadDealer(dr); // Mapper result.Add(dealer); } } } return(result); }
/// <summary> /// /// </summary> /// <returns></returns> public List <Role> SelectByUserId(int id) { const string sqlStatement = "SELECT a.Id, a.Name FROM AspNetRoles a " + "JOIN AspNetUserRoles b ON a.Id = b.RoleId WHERE b.UserId = @userId;"; var result = new List <Role>(); var connection = Db.CreateOpenConnection(); using (var cmd = Db.CreateCommand(sqlStatement, connection)) { cmd.Parameters.Add(Db.CreateParameter("@userId", id)); using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { var Role = LoadRole(dr); // Mapper result.Add(Role); } } } return(result); }
/// <summary> /// /// </summary> /// <returns></returns> public List <User> SelectByUserName(string UserName) { // WARNING! Performance const string sqlStatement = "SELECT * FROM AspNetUsers WHERE UserName = @UserName"; var result = new List <User>(); var connection = Db.CreateOpenConnection(); using (var cmd = Db.CreateCommand(sqlStatement, connection)) { cmd.Parameters.Add(Db.CreateParameter("@UserName", UserName)); using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { var User = LoadUser(dr); // Mapper result.Add(User); } } } return(result); }
/// <summary> /// /// </summary> /// <param name="cartiitem"></param> public void UpdateById(CartItem cartitem) { const string sqlStatement = "UPDATE dbo.CartItem " + "SET [ProductId]=@ProductId, " + "[Price]=@Price, " + "[Quantity]=@Quantity, " + "[ChangedOn]=@ChangedOn, " + "[ChangedBy]=@ChangedBy " + "WHERE [Id]=@Id "; var connection = Db.CreateOpenConnection(); using (var cmd = Db.CreateCommand(sqlStatement, connection)) { cmd.Parameters.Add(Db.CreateParameter("@CartId", cartitem.CartId)); cmd.Parameters.Add(Db.CreateParameter("@ProductId", cartitem.ProductId)); cmd.Parameters.Add(Db.CreateParameter("@Price", cartitem.Price)); cmd.Parameters.Add(Db.CreateParameter("@Quantity", cartitem.Quantity)); cmd.Parameters.Add(Db.CreateParameter("@ChangedOn", cartitem.ChangedOn)); cmd.Parameters.Add(Db.CreateParameter("@ChangedBy", cartitem.ChangedBy)); cmd.Parameters.Add(Db.CreateParameter("@Id", cartitem.Id)); cmd.ExecuteNonQuery(); } }
/// <summary> /// /// </summary> /// <param name="rating"></param> public void UpdateById(Rating rating) { const string sqlStatement = "UPDATE dbo.Rating " + "SET [ClientId]=@ClientId, " + "[ProductId]=@ProductId, " + "[Stars]=@Stars, " + "[ChangedOn]=@ChangedOn, " + "[ChangedBy]=@ChangedBy " + "WHERE [Id]=@Id "; var connection = Db.CreateOpenConnection(); using (var cmd = Db.CreateCommand(sqlStatement, connection)) { cmd.Parameters.Add(Db.CreateParameter("@ClientId", rating.ClientId)); cmd.Parameters.Add(Db.CreateParameter("@ProductId", rating.ProductId)); cmd.Parameters.Add(Db.CreateParameter("@Stars", rating.Stars)); cmd.Parameters.Add(Db.CreateParameter("@ChangedOn", rating.ChangedOn)); cmd.Parameters.Add(Db.CreateParameter("@ChangedBy", rating.ChangedBy)); cmd.Parameters.Add(Db.CreateParameter("@Id", rating.Id)); cmd.ExecuteNonQuery(); } }
/// <summary> /// /// </summary> /// <param name="client"></param> /// <returns></returns> public Client Create(Client client) { const string sqlStatement = "INSERT INTO dbo.Client ([FirstName], [LastName], [Email], [CountryId], " + "[AspNetUsers], [City], [CreatedOn], [CreatedBy]) VALUES(@FirstName, @LastName, @Email, @CountryId, " + "@AspNetUsers, @City, @CreatedOn, @CreatedBy); SELECT SCOPE_IDENTITY();"; var connection = Db.CreateOpenConnection(); using (var cmd = Db.CreateCommand(sqlStatement, connection)) { cmd.Parameters.Add(Db.CreateParameter("@FirstName", client.FirstName)); cmd.Parameters.Add(Db.CreateParameter("@LastName", client.LastName)); cmd.Parameters.Add(Db.CreateParameter("@Email", client.Email)); cmd.Parameters.Add(Db.CreateParameter("@CountryId", client.CountryId)); cmd.Parameters.Add(Db.CreateParameter("@AspNetUsers", client.AspNetUsers)); cmd.Parameters.Add(Db.CreateParameter("@City", client.City)); cmd.Parameters.Add(Db.CreateParameter("@CreatedOn", DateTime.Now)); cmd.Parameters.Add(Db.CreateParameter("@CreatedBy", client.CreatedBy)); // Obtener el valor de la primary key. client.Id = Convert.ToInt32(cmd.ExecuteScalar()); } return(client); }