public int Insert(IdentityShop identity)
{
//Common syntax
var sqlCmd = @"M_Shop_Insert";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@IsInternal", identity.IsInternal },
{ "@Name", identity.Name },
{ "@Code", identity.Code },
{ "@ProviderId", identity.ProviderId },
{ "@Phone", identity.Phone },
{ "@Address", identity.Address },
{ "@ContactInfo", identity.ContactInfo },
{ "@AreaId", identity.AreaId },
{ "@CountryId", identity.CountryId },
{ "@ProvinceId", identity.ProvinceId },
{ "@DistrictId", identity.DistrictId },
{ "@Openned", identity.Openned },
{ "@PostCode", identity.PostCode },
{ "@Description", identity.Description },
{ "@CreatedBy", identity.CreatedBy },
{ "@Status", identity.Status },
{ "@ShopType", identity.ShopType },
{ "@Email", identity.Email }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);
newId = Convert.ToInt32(returnObj);
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_Shop_Insert. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
public bool Update(IdentityShop identity)
{
//Common syntax
var sqlCmd = @"M_Shop_Update";
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@Id", identity.Id },
{ "@IsInternal", identity.IsInternal },
{ "@Name", identity.Name },
{ "@Code", identity.Code },
{ "@ProviderId", identity.ProviderId },
{ "@Phone", identity.Phone },
{ "@Address", identity.Address },
{ "@ContactInfo", identity.ContactInfo },
{ "@AreaId", identity.AreaId },
{ "@CountryId", identity.CountryId },
{ "@ProvinceId", identity.ProvinceId },
{ "@DistrictId", identity.DistrictId },
{ "@Openned", identity.Openned },
{ "@PostCode", identity.PostCode },
{ "@Description", identity.Description },
{ "@LastUpdatedBy", identity.LastUpdatedBy },
{ "@Status", identity.Status },
{ "@ShopType", identity.ShopType },
{ "@Email", identity.Email }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
MsSqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, sqlCmd, parameters);
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_Shop_Update. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(true);
}
public IdentityShop GetById(int Id)
{
var info = new IdentityShop();
var sqlCmd = @"Shop_GetById";
var parameters = new Dictionary <string, object>
{
{ "@Id", Id }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
{
//Base information
if (reader.Read())
{
info = ExtractShopData(reader);
}
//Images
if (reader.NextResult())
{
info.Images = ExtractShopImageData(reader);
}
//Meta data
if (reader.NextResult())
{
info.MetaData = ExtractShopMetaData(reader);
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute Shop_GetById. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(info);
}
public List <IdentityShop> GetAssigned(int userId)
{
//Common syntax
var sqlCmd = @"M_Shop_GetAssigned";
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@UserId", userId },
};
List <IdentityShop> listData = new List <IdentityShop>();
try
{
using (var conn = new SqlConnection(_connectionString))
{
using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
{
while (reader.Read())
{
var record = new IdentityShop();
record.Id = Utils.ConvertToInt32(reader["Id"]);
record.Name = reader["Name"].ToString();
record.IsOwner = Utils.ConvertToBoolean(reader["IsOwner"]);
listData.Add(record);
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_Shop_GetAssigned. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(listData);
}
public List <IdentityShop> GetByPage(IdentityShop filter, int currentPage, int pageSize)
{
//Common syntax
var sqlCmd = @"M_Shop_GetByPage";
List <IdentityShop> listData = null;
//For paging
int offset = (currentPage - 1) * pageSize;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@Keyword", filter.Keyword },
{ "@Status", filter.Status },
{ "@CategoryId", filter.CategoryId },
{ "@Offset", offset },
{ "@PageSize", pageSize },
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
{
listData = ParsingListShopFromReader(reader);
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute M_Shop_GetByPage. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(listData);
}
private IdentityShop ExtractShopData(IDataReader reader)
{
var record = new IdentityShop();
//Seperate properties
record.Id = Utils.ConvertToInt32(reader["Id"]);
record.IsInternal = Convert.ToInt32(reader["IsInternal"]);
record.Name = reader["Name"].ToString();
record.Code = reader["Code"].ToString();
record.Cover = reader["Cover"].ToString();
record.ProviderId = Utils.ConvertToInt32(reader["ProviderId"]);
record.Phone = reader["Phone"].ToString();
record.Address = reader["Address"].ToString();
record.ContactInfo = reader["ContactInfo"].ToString();
record.AreaId = Utils.ConvertToInt32(reader["AreaId"]);
record.CountryId = Utils.ConvertToInt32(reader["CountryId"]);
record.ProvinceId = Utils.ConvertToInt32(reader["ProvinceId"]);
record.DistrictId = Utils.ConvertToInt32(reader["DistrictId"]);
record.Openned = Utils.ConvertToInt32(reader["Openned"]);
record.PostCode = reader["PostCode"].ToString();
record.Description = reader["Description"].ToString();
record.Longitude = reader["Longitude"].ToString();
record.Latitude = reader["Latitude"].ToString();
record.CreatedBy = reader["CreatedBy"].ToString();
record.CreatedDate = reader["CreatedDate"] == DBNull.Value ? null : (DateTime?)reader["CreatedDate"];
record.LastUpdated = reader["LastUpdated"] == DBNull.Value ? null : (DateTime?)reader["LastUpdated"];
record.LastUpdatedBy = reader["LastUpdatedBy"].ToString();
record.LastBooking = reader["LastBooking"] == DBNull.Value ? null : (DateTime?)reader["LastBooking"];
record.Status = Utils.ConvertToInt32(reader["Status"]);
record.Email = reader["Email"].ToString();
record.CategoryId = Utils.ConvertToInt32(reader["CategoryId"]);
return(record);
}
public bool Update(IdentityShop identity)
{
return(myRepository.Update(identity));
}
public int Insert(IdentityShop identity)
{
return(myRepository.Insert(identity));
}
public List <IdentityShop> GetByPage(IdentityShop filter, int currentPage, int pageSize)
{
return(myRepository.GetByPage(filter, currentPage, pageSize));
}
