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