public bool Insert(Photo photo, Database db, DbTransaction transaction = null)
{
DbCommand command = db.GetStoredProcCommand("usp_PhotoInsert");
photo.PhotoId = Guid.NewGuid();
db.AddInParameter(command, "PhotoId", DbType.Guid, photo.PhotoId);
db.AddInParameter(command, "FileName", DbType.String, photo.FileName);
db.AddInParameter(command, "FilePath", DbType.String, photo.FilePath);
db.AddInParameter(command, "ContextId", DbType.Guid, photo.ContextId);
db.AddInParameter(command, "Description", DbType.String, photo.Description);
db.AddInParameter(command, "ContextTypeId", DbType.Int32, (int)photo.ContextType);
db.AddInParameter(command, "ContextSubTypeId", DbType.Int32, photo.ContextSubTypeId);
db.AddInParameter(command, "PhotoCategoryId", DbType.Int32, (int)photo.PhotoCategory);
db.AddInParameter(command, "IsDeleted", DbType.String, photo.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, photo.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
photo.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
photo.UpdatedDate = photo.CreatedDate;
return true;
}
//Funcion para registrar autos
public int RegistrarAutos(Autos DatosA, DbTransaction tran, Database db)
{
string sqlCommand = "dbo.insertar_autos";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
try {
db.AddInParameter(dbCommand, "@INTplaca", DbType.Int32, Utilerías.ObtenerValor(DatosA.Placa));
db.AddInParameter(dbCommand, "@STRmarca", DbType.String, Utilerías.ObtenerValor(DatosA.Marca));
db.AddInParameter(dbCommand, "@STRmodelo", DbType.String, Utilerías.ObtenerValor(DatosA.Modelo));
db.AddInParameter(dbCommand, "@INTanno", DbType.Int32, Utilerías.ObtenerValor(DatosA.Año));
db.AddInParameter(dbCommand, "@INTnumero_vin", DbType.Int32, Utilerías.ObtenerValor(DatosA.Vin));
db.AddInParameter(dbCommand, "@STRcolor", DbType.String, Utilerías.ObtenerValor(DatosA.Color));
db.AddOutParameter(dbCommand, "@nStatus", DbType.Int16, 2);
db.AddOutParameter(dbCommand, "@strMessage", DbType.String, 250);
db.AddOutParameter(dbCommand, "@INTid_Auto", DbType.Int32, 4);
db.ExecuteNonQuery(dbCommand, tran);
if (int.Parse(db.GetParameterValue(dbCommand, "@nStatus").ToString()) > 0)
throw new Exception(db.GetParameterValue(dbCommand, "@strMessage").ToString());
//retorna el id del auto que acaba de registrar
return int.Parse(db.GetParameterValue(dbCommand, "@INTid_Auto").ToString());
} catch (Exception ex) {
throw new Exception(ex.Message);
}
}
public bool Insert(PartialUser partialUser, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_PartialUserInsert");
partialUser.PartialUserId = Guid.NewGuid();
db.AddInParameter(command, "PartialUserId", DbType.Guid, partialUser.PartialUserId);
db.AddInParameter(command, "Email", DbType.String, partialUser.Email);
db.AddInParameter(command, "FirstName", DbType.String, partialUser.FirstName);
db.AddInParameter(command, "MiddleName", DbType.String, partialUser.MiddleName);
db.AddInParameter(command, "LastName", DbType.String, partialUser.LastName);
db.AddInParameter(command, "Contact", DbType.String, partialUser.Contact);
db.AddInParameter(command, "RoleId", DbType.Guid, partialUser.RoleId);
db.AddInParameter(command, "UserId", DbType.Guid, partialUser.UserId);
db.AddInParameter(command, "PartialHouseId", DbType.Guid, partialUser.PartialHouseId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, partialUser.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, partialUser.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
partialUser.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
partialUser.UpdatedDate = partialUser.CreatedDate;
return true;
}
public bool Insert(Option Option, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionInsert");
db.AddInParameter(command, "Name", DbType.String, Option.Name);
db.AddInParameter(command, "Description", DbType.String, Option.Description);
db.AddInParameter(command, "OptionCategoryId", DbType.Int16, Option.OptionCategoryId);
db.AddInParameter(command, "ParentOptionId", DbType.Int16, Option.ParentOptionId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, Option.IsDeleted);
db.AddInParameter(command, "IsMultiSelect", DbType.Boolean, Option.IsMultiSelect);
db.AddInParameter(command, "Points", DbType.Int16, Option.Points);
db.AddOutParameter(command, "OptionId", DbType.Int16, 3);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
Option.OptionId = Convert.ToInt16(db.GetParameterValue(command, "OptionId").ToString());
return true;
}
public bool Insert(OptionItem OptionItem, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionItemInsert");
db.AddInParameter(command, "Name", DbType.String, OptionItem.Name);
db.AddInParameter(command, "Description", DbType.String, OptionItem.Description);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, OptionItem.IsDeleted);
db.AddInParameter(command, "OptionId", DbType.Int16, OptionItem.OptionId);
db.AddInParameter(command, "CreatedBy", DbType.Guid, OptionItem.CreatedBy);
db.AddOutParameter(command, "OptionItemId", DbType.Int16, 3);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
OptionItem.OptionItemId = Convert.ToInt16(db.GetParameterValue(command, "OptionItemId").ToString());
return true;
}
public bool Insert(School school, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SchoolInsert");
db.AddInParameter(command, "SchoolId", DbType.Guid, Guid.NewGuid());
db.AddInParameter(command, "Name", DbType.String, school.Name);
db.AddInParameter(command, "StreetAddress", DbType.String, school.StreetAddress);
db.AddInParameter(command, "City", DbType.String, school.City);
db.AddInParameter(command, "State", DbType.String, school.State);
db.AddInParameter(command, "Zip", DbType.String, school.Zip);
db.AddInParameter(command, "ContactNumber", DbType.String, school.ContactNumber);
db.AddInParameter(command, "Email", DbType.String, school.Email);
db.AddInParameter(command, "Location", DbType.String, school.Location);
db.AddInParameter(command, "WebsiteURL", DbType.String, school.WebsiteURL);
db.AddInParameter(command, "RatingValue", DbType.Decimal, school.RatingValue);
db.AddInParameter(command, "CreatedBy", DbType.Guid, school.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
school.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
school.UpdatedDate = school.CreatedDate;
return true;
}
public bool Insert(Student student, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_StudentInsert");
db.AddInParameter(command, "StudentId", DbType.Guid, Guid.NewGuid());
db.AddInParameter(command, "UserId", DbType.Guid, student.StudentUser.UserId);
db.AddInParameter(command, "SchoolId", DbType.Guid, student.School.SchoolId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, student.IsDeleted);
db.AddInParameter(command, "Year", DbType.String, student.School.Year);
db.AddInParameter(command, "StartYear", DbType.String, student.StartYear);
db.AddInParameter(command, "StartMonth", DbType.String, student.StartMonth);
db.AddInParameter(command, "Status", DbType.String, student.Status);
db.AddInParameter(command, "PreviousSchoolInfo", DbType.String, student.PreviousSchoolInfo);
db.AddInParameter(command, "PreviousSchool", DbType.String, student.PreviousSchool);
db.AddInParameter(command, "MajorId", DbType.Int16, student.MajorId);
db.AddInParameter(command, "CreatedBy", DbType.Guid, student.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
student.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
student.UpdatedDate = student.CreatedDate;
return true;
}
public bool Delete(User user, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_UserDelete");
db.AddInParameter(command, "UpdatedBy", DbType.Guid, user.UpdatedBy);
db.AddInParameter(command, "UserId", DbType.Guid, user.UserId);
db.ExecuteNonQuery(command, transaction);
user.UpdatedDate = Convert.ToDateTime(db.GetParameterValue(command, "UpdatedDate").ToString());
return true;
}
/// <summary>
/// 根据传入的表名、视图或者SQL语句返回分页数据,大数据量时使用,使用二分法分页，效率比较高,返回泛型结果集
/// </summary>
/// <typeparam name="T">返回实体</typeparam>
/// <param name="storedProcedureName">存储过程名</param>
/// <param name="tblName">表名、视图名或者SQL语句</param>
/// <param name="fldName">字段名，必须填</param>
/// <param name="where ">查询条件(注意: 不要加where)</param>
/// <param name="fldSort">排序字段</param>
/// <param name="pageSize">每页显示条数</param>
/// <param name="page">查看的页面序号</param>
/// <param name="Counts">总记录条数</param>
/// <returns></returns>
public List <T> DataAdaptersPage <T>(string storedProcedureName, string tblName, string fldName, string where, string fldSort, int pageSize, int page, ref int Counts)
{
try
{
DataSet ds = new DataSet();
Open();
mDbCommand = mDatabase.GetStoredProcCommand(storedProcedureName);
mDbCommand.Connection = Connection;
mDatabase.AddInParameter(mDbCommand, "@TableName", DbType.AnsiString, tblName);
mDatabase.AddInParameter(mDbCommand, "@SelectFields", DbType.AnsiString, fldName);
mDatabase.AddInParameter(mDbCommand, "@Where", DbType.AnsiString, where);
mDatabase.AddInParameter(mDbCommand, "@OrderField", DbType.AnsiString, fldSort);
mDatabase.AddInParameter(mDbCommand, "@PageSize", DbType.Int32, pageSize);
mDatabase.AddInParameter(mDbCommand, "@PageIndex", DbType.Int32, page);
mDatabase.AddOutParameter(mDbCommand, "@RowCount", DbType.Int32, Counts);
ds = mDatabase.ExecuteDataSet(mDbCommand);
//pageCount = Convert.ToInt32(mDatabase.GetParameterValue(mDbCommand, "@pageCount"));
Counts = Convert.ToInt32(mDatabase.GetParameterValue(mDbCommand, "@RowCount"));
//return ModelHelper.ModelListInTable<T>(ds.Tables[0]);
return(DataTableConvertList <T>(ds.Tables[0]));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
Close();
}
}
public bool Insert(Spotlight spotlight, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SpotlightInsert");
db.AddInParameter(command, "UserId", DbType.Guid, spotlight.UserId);
db.AddInParameter(command, "Awards", DbType.String, spotlight.Awards);
db.AddInParameter(command, "Achievements", DbType.String, spotlight.Achievements);
db.AddInParameter(command, "CurentGPA", DbType.String, spotlight.CurentGPA);
db.AddInParameter(command, "OraganizationId", DbType.Int16, spotlight.OraganizationId);
db.AddInParameter(command, "Involvments", DbType.String, spotlight.Involvments);
db.AddInParameter(command, "FraternityId", DbType.Int16, spotlight.FraternityId);
db.AddInParameter(command, "SoroityId", DbType.Int16, spotlight.SoroityId);
db.AddInParameter(command, "GreekHonorSocitiesId", DbType.Int16, spotlight.GreekHonorSocitiesId);
db.AddInParameter(command, "GreakOrganizationId", DbType.Int16, spotlight.GreakOrganizationId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, spotlight.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, spotlight.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
spotlight.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
spotlight.UpdatedDate = spotlight.CreatedDate;
return true;
}
public bool Insert(StudentHouseLeave studentHouseLeave, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_StudentHouseLeaveInsert");
db.AddInParameter(command, "HouseId", DbType.Guid, studentHouseLeave.HouseId);
db.AddInParameter(command, "BaseHouseRoomId", DbType.Guid, studentHouseLeave.BaseHouseRoomId);
db.AddInParameter(command, "RequestBy", DbType.Guid, studentHouseLeave.RequestBy);
db.AddInParameter(command, "RequestTo", DbType.Guid, studentHouseLeave.RequestTo);
db.AddInParameter(command, "status", DbType.Int16, studentHouseLeave.status);
db.AddOutParameter(command, "RequestDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
studentHouseLeave.RequestDate = Convert.ToDateTime(db.GetParameterValue(command, "RequestDate").ToString());
studentHouseLeave.ResponseDate = studentHouseLeave.RequestDate;
return true;
}
public bool Insert(PartialHouse partialHouse, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_PartialHouseInsert");
partialHouse.PartialHouseId = Guid.NewGuid();
db.AddInParameter(command, "PartialHouseId", DbType.Guid, partialHouse.PartialHouseId);
db.AddInParameter(command, "PartialUserId", DbType.Guid, partialHouse.PartialUserId);
db.AddInParameter(command, "StateId", DbType.Int32, partialHouse.StateId);
db.AddInParameter(command, "ZipCode", DbType.String, partialHouse.ZipCode);
db.AddInParameter(command, "City", DbType.String, partialHouse.City);
db.AddInParameter(command, "Address", DbType.String, partialHouse.Address);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, partialHouse.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, partialHouse.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
partialHouse.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
partialHouse.UpdatedDate = partialHouse.CreatedDate;
return true;
}
public DetalleTarjetaLugarList GetDetalleTarjetaLugarPaginacion(int IdProyecto, Paginacion oPaginacion, out int RowCount)
{
DetalleTarjetaLugarList olista = new DetalleTarjetaLugarList();
DbCommand oDbCommand = oDatabase.GetStoredProcCommand(DetalleTarjetaLugar.Proc.Paginacion.Str());
oDatabase.AddInParameter(oDbCommand, "@IdProyecto", DbType.Int32, IdProyecto);
oDatabase = Pagination.DefaultParams(oDatabase, oDbCommand, oPaginacion);
using (IDataReader oIDataReader = oDatabase.ExecuteReader(oDbCommand))
{
int i1 = oIDataReader.GetOrdinal("IdDetTarjetaLugar");
int i2 = oIDataReader.GetOrdinal("IdProyecto");
int i3 = oIDataReader.GetOrdinal("SubRackId");
int i4 = oIDataReader.GetOrdinal("Slot");
int i5 = oIDataReader.GetOrdinal("BoardType");
while (oIDataReader.Read())
{
DetalleTarjetaLugar obj = new DetalleTarjetaLugar();
obj.IdDetalleTarjetaLugar = DataUtil.DbValueToDefault<Int32>(oIDataReader[i1]);
obj.IdProyecto = DataUtil.DbValueToDefault<Int32>(oIDataReader[i2]);
obj.SubRackId = DataUtil.DbValueToDefault<String>(oIDataReader[i3]);
obj.Slot = DataUtil.DbValueToDefault<String>(oIDataReader[i4]);
obj.BoardType = DataUtil.DbValueToDefault<String>(oIDataReader[i5]);
olista.Add(obj);
}
}
RowCount = Convert.ToInt32(oDatabase.GetParameterValue(oDbCommand, "@RowCount"));
return olista;
}
public ProyectosList GetBandejaPaginacion(Proyectos oPersona, int IdEmpleado,Paginacion oPaginacion, out int RowCount)
{
ProyectosList olista = new ProyectosList();
DbCommand oDbCommand = oDatabase.GetStoredProcCommand(Proyectos.Proc.Bandeja.Str());
oDatabase.AddInParameter(oDbCommand, "@Codigo", DbType.String, oPersona.Codigo.nullEmpty());
oDatabase.AddInParameter(oDbCommand, "@IdEmpleado", DbType.Int32, IdEmpleado);
oDatabase = Pagination.DefaultParams(oDatabase, oDbCommand, oPaginacion);
using (IDataReader oIDataReader = oDatabase.ExecuteReader(oDbCommand))
{
Proyectos obj = new Proyectos();
int i1 = oIDataReader.GetOrdinal("IdProyecto");
int i2 = oIDataReader.GetOrdinal("CodProyecto");
int i3 = oIDataReader.GetOrdinal("DescripProyecto");
int i4 = oIDataReader.GetOrdinal("NombreEstacion");
int i5 = oIDataReader.GetOrdinal("TipoEquipo");
int i6 = oIDataReader.GetOrdinal("NombreEquipo");
while (oIDataReader.Read())
{
obj = new Proyectos();
obj.IdProyecto = DataUtil.DbValueToDefault<Int32>(oIDataReader[i1]);
obj.Codigo = DataUtil.DbValueToDefault<String>(oIDataReader[i2]);
obj.Descripcion = DataUtil.DbValueToDefault<String>(oIDataReader[i3]);
obj.NombreEstacion = DataUtil.DbValueToDefault<String>(oIDataReader[i4]);
obj.TipoEquipo = DataUtil.DbValueToDefault<String>(oIDataReader[i5]);
obj.NombreEquipo = DataUtil.DbValueToDefault<String>(oIDataReader[i6]);
olista.Add(obj);
}
}
RowCount = Convert.ToInt32(oDatabase.GetParameterValue(oDbCommand, "@RowCount"));
return olista;
}
public bool Insert(Customer customer, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_CustomerInsert");
db.AddInParameter(command, "@CompanyId", DbType.Int32, customer.CompanyId);
db.AddInParameter(command, "@CustomerName", DbType.String, customer.CustomerName);
db.AddInParameter(command, "@Gender", DbType.String, customer.Gender);
db.AddInParameter(command, "@GuestTypeId", DbType.Int32, customer.GuestTypeId);
db.AddInParameter(command, "@Phone", DbType.String, customer.Phone);
db.AddInParameter(command, "@Fax", DbType.String, customer.Fax);
db.AddInParameter(command, "@Mobile", DbType.String, customer.Mobile);
db.AddInParameter(command, "@Email", DbType.String, customer.Email);
db.AddInParameter(command, "@CompanyName", DbType.String, customer.CompanyName);
db.AddInParameter(command, "@CompanyAddressLine1", DbType.String, customer.CompanyAddressLine1);
db.AddInParameter(command, "@CompanyAddressLine2", DbType.String, customer.CompanyAddressLine2);
db.AddInParameter(command, "@CompanyCity", DbType.String, customer.CompanyCity);
db.AddInParameter(command, "@CompanyState", DbType.String, customer.CompanyState);
db.AddInParameter(command, "@CompanyPostCode", DbType.String, customer.CompanyPostCode);
db.AddInParameter(command, "@CompanyCountryId", DbType.Int32, customer.CompanyCountryId);
db.AddInParameter(command, "@CompanyNotes", DbType.String, customer.CompanyNotes);
db.AddInParameter(command, "@BillingAddressLine1", DbType.String, customer.BillingAddressLine1);
db.AddInParameter(command, "@BillingAddressLine2", DbType.String, customer.BillingAddressLine2);
db.AddInParameter(command, "@BillingCity", DbType.String, customer.BillingCity);
db.AddInParameter(command, "@BillingState", DbType.String, customer.BillingState);
db.AddInParameter(command, "@BillingCountryId", DbType.Int32, customer.BillingCountryId);
db.AddInParameter(command, "@BillingPostCode", DbType.String, customer.BillingPostCode);
db.AddInParameter(command, "@PassportNumber", DbType.String, customer.PassportNumber);
db.AddInParameter(command, "@PassportCountryOfIssue", DbType.Int32, customer.PassportCountryOfIssue);
db.AddInParameter(command, "@PassportExpirationDate", DbType.DateTime, customer.PassportExpirationDate);
db.AddInParameter(command, "@CreditCardTypeId", DbType.Int32, customer.CreditCardTypeId);
db.AddInParameter(command, "@CCNo", DbType.String, customer.CCNo);
db.AddInParameter(command, "@CCExpirationDate", DbType.DateTime, customer.CCExpirationDate);
db.AddInParameter(command, "@CCNameOnCard", DbType.String, customer.CCNameOnCard);
db.AddInParameter(command, "@Car", DbType.String, customer.Car);
db.AddInParameter(command, "@CarLicensePlate", DbType.String, customer.CarLicensePlate);
db.AddInParameter(command, "@DriverLicense", DbType.String, customer.DriverLicense);
db.AddInParameter(command, "@CreatedUser", DbType.Int32, customer.CreatedUser);
db.AddInParameter(command, "@StatusId", DbType.Int32, customer.StatusId);
db.AddInParameter(command, "@CardSecurityCode", DbType.String, customer.CardSecurityCode);
db.AddInParameter(command, "@CardStartDate", DbType.DateTime, customer.CardStartDate);
db.AddInParameter(command, "@CardIssueNo", DbType.String, customer.CardIssueNo);
db.AddInParameter(command, "@UseSameBillingAddress", DbType.Boolean, customer.UseSameBillingAddress);
db.AddInParameter(command, "@IsGroupCustomer", DbType.Boolean, customer.IsGroupCustomer);
db.AddOutParameter(command, "@CustomerId", DbType.Int32, 8);
db.ExecuteNonQuery(command, transaction);
customer.CustomerId = int.Parse(db.GetParameterValue(command, "@CustomerId").ToString());
return true;
}
public int GetSchemaVersion(string name)
{
int version = 0;
using (DbCommand dbCmd = db.GetStoredProcCommand("chpt09_GetSchemaVersion"))
{
db.AddInParameter(dbCmd, "@Name", DbType.String, name);
db.AddOutParameter(dbCmd, "@Version", DbType.Int32, 0);
db.ExecuteNonQuery(dbCmd);
version = (int)db.GetParameterValue(dbCmd, "@Version");
}
return(version);
}
public bool Insert(Roles roles, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_RolesInsert");
db.AddInParameter(command, "@CompanyId", DbType.Int32, roles.CompanyId);
db.AddInParameter(command, "@RoleName", DbType.String, roles.RoleName);
db.AddInParameter(command, "@RoleDescription", DbType.String, roles.RoleDescription);
db.AddInParameter(command, "@CreatedUser", DbType.Int32, roles.CreatedUser);
db.AddOutParameter(command, "@RolesId", DbType.Int32, 8);
db.ExecuteNonQuery(command, transaction);
roles.RolesId = Convert.ToInt32(db.GetParameterValue(command, "@RolesId").ToString());
return true;
}
public bool Insert(Reservation reservation, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_ReservationInsert");
db.AddInParameter(command, "@CompanyId", DbType.Int32, reservation.CompanyId);
db.AddInParameter(command, "@CustomerId", DbType.Int32, reservation.CustomerId);
db.AddInParameter(command, "@StatusId", DbType.Int32, reservation.StatusId);
db.AddInParameter(command, "@CheckInDate", DbType.DateTime, reservation.CheckInDate);
db.AddInParameter(command, "@CheckOutDate", DbType.DateTime, reservation.CheckOutDate);
db.AddInParameter(command, "@SourceId", DbType.Int32, reservation.SourceId);
db.AddInParameter(command, "@RoomTotal", DbType.Decimal, reservation.RoomTotal);
db.AddInParameter(command, "@ServiceTotal", DbType.Decimal, reservation.ServiceTotal);
db.AddInParameter(command, "@NetTotal", DbType.Decimal, reservation.NetTotal);
db.AddInParameter(command, "@Discount", DbType.Decimal, reservation.Discount);
db.AddInParameter(command, "@TaxAmount", DbType.Decimal, reservation.TaxAmount);
db.AddInParameter(command, "@PaidAmount", DbType.Decimal, reservation.PaidAmount);
db.AddInParameter(command, "@Total", DbType.Decimal, reservation.Total);
db.AddInParameter(command, "@Balance", DbType.Decimal, reservation.Balance);
db.AddInParameter(command, "@CreatedUser", DbType.Int32, reservation.CreatedUser);
db.AddInParameter(command, "@TaxTypeId", DbType.Int32, reservation.TaxTypeId);
db.AddInParameter(command, "@TaxPercentage", DbType.Decimal, reservation.TaxPercentage);
db.AddOutParameter(command, "@NewReservationId", DbType.Int32,8);
db.ExecuteNonQuery(command);
//Int32 newReservationId = Convert.ToInt32(db.GetParameterValue(command, "@NewReservationId"));
reservation.ReservationId = Convert.ToInt32(db.GetParameterValue(command, "@NewReservationId"));
ReservationRoom reservationRoom = new ReservationRoom();
reservationRoom.ReservationId = reservation.ReservationId;
reservationRoom.ReservationRoomList = reservation.ReservationRoomDataSet;
reservationRoom.Save(db, transaction);
ReservationAdditionalService reservationAddtionalService = new ReservationAdditionalService();
reservationAddtionalService.ReservationId = reservation.ReservationId;
reservationAddtionalService.ReservationAdditionalServiceList = reservation.ReservationAdditionalServiceDataSet;
reservationAddtionalService.Save(db, transaction);
ReservationPayments reservationPayments = new ReservationPayments();
reservationPayments.ReservationId = reservation.ReservationId;
reservationPayments.ReservationPaymentList = reservation.ReservationPaymentDataSet;
reservationPayments.Save(db, transaction);
return true;
}
public bool Insert(Landlord landlord, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_LandlordInsert");
db.AddInParameter(command, "LandlordId", DbType.Guid, landlord.LandlordId);
db.AddInParameter(command, "UserId", DbType.Guid, landlord.user.UserId);
db.AddInParameter(command, "LandlordName", DbType.String, landlord.LandlordName);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, landlord.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, landlord.CreatedBy);
db.AddInParameter(command, "LandlordTypeId", DbType.Int32, landlord.LandlordTypeId);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
landlord.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
landlord.UpdatedDate = landlord.CreatedDate;
return true;
}
public bool Insert(User users, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_UserInsert");
db.AddInParameter(command, "UserId", DbType.Guid, users.UserId);
db.AddInParameter(command, "IsFBUser", DbType.Boolean, users.IsFBUser);
db.AddInParameter(command, "AspnetUserId", DbType.Guid, users.AspnetUserId);
db.AddInParameter(command, "IsPartialUser", DbType.Boolean, users.IsPartialUser);
db.AddInParameter(command, "FBAccessToken", DbType.String, users.FBAccessToken);
db.AddInParameter(command, "FBUrl", DbType.String, users.FBUrl);
db.AddInParameter(command, "FBProfilePictureURL", DbType.String, users.FBProfilePictureURL);
db.AddInParameter(command, "FirstName", DbType.String, users.FirstName);
db.AddInParameter(command, "MiddleName", DbType.String, users.MiddleName);
db.AddInParameter(command, "LastName", DbType.String, users.LastName);
db.AddInParameter(command, "StreetAddress", DbType.String, users.StreetAddress);
db.AddInParameter(command, "City", DbType.String, users.City);
db.AddInParameter(command, "StateId", DbType.Int32, users.StateId);
db.AddInParameter(command, "Zip", DbType.String, users.Zip);
db.AddInParameter(command, "DateOfBirth", DbType.Date, users.DateOfBirth);
db.AddInParameter(command, "BestContactNumber", DbType.String, users.BestContactNumber);
db.AddInParameter(command, "DriversLicenseNumber", DbType.String, users.DriversLicenseNumber);
db.AddInParameter(command, "Status", DbType.String, users.Status);//To be changed
db.AddInParameter(command, "PersonalEmail", DbType.String, users.PersonalEmail);
db.AddInParameter(command, "CreatedBy", DbType.Guid, users.CreatedBy);
db.AddInParameter(command, "RatingValue", DbType.Decimal, users.RatingValue);
db.AddInParameter(command, "FBid", DbType.String, users.FBid);
db.AddInParameter(command, "Gender", DbType.String, users.Gender);
db.AddInParameter(command, "HouseId", DbType.Guid, users.HouseId);
db.AddInParameter(command, "RoleId", DbType.Guid, users.RoleId);
db.AddInParameter(command, "ReferralCode", DbType.String, users.ReferralCode);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
users.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
users.UpdatedDate = users.CreatedDate;
return true;
}
public bool Insert(House house, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_HouseInsert");
house.HouseId = Guid.NewGuid();
db.AddInParameter(command, "HouseId", DbType.Guid, house.HouseId);
db.AddInParameter(command, "LandlordId", DbType.Guid, house.LandlordId);
db.AddInParameter(command, "StreetAddress", DbType.String, house.StreetAddress);
db.AddInParameter(command, "City", DbType.String, house.City);
db.AddInParameter(command, "StateId", DbType.Int32, house.StateId);
db.AddInParameter(command, "Zip", DbType.String, house.Zip);
db.AddInParameter(command, "YearHomeBuild", DbType.Int16, house.YearHomeBuild);
db.AddInParameter(command, "BedRooms", DbType.Int16, house.BathRooms);
db.AddInParameter(command, "BathRooms", DbType.Int16, house.BedRooms);
db.AddInParameter(command, "LotSquareFootage", DbType.Int32, house.LotSquareFootage);
db.AddInParameter(command, "TotalSquareFootage", DbType.Int32, house.TotalSquareFootage);
db.AddInParameter(command, "UtilitiesIncludedInRent", DbType.String, house.UtilitiesIncludedInRent);
db.AddInParameter(command, "PropertyImagePath", DbType.String, house.PropertyImagePath);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, house.IsDeleted);
db.AddInParameter(command, "IsPartialHouse", DbType.Boolean, house.IsPartialHouse);
db.AddInParameter(command, "CreatedBy", DbType.Guid, house.CreatedBy);
db.AddInParameter(command, "RatingValue", DbType.Decimal, house.RatingValue);
db.AddInParameter(command, "Price", DbType.Decimal, house.Price);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
house.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
house.UpdatedDate = house.CreatedDate;
return true;
}
public bool Insert(StudentHouse studentHouse, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_StudentHouseInsert");
db.AddInParameter(command, "HouseId", DbType.Guid, studentHouse.HouseId);
db.AddInParameter(command, "UserId", DbType.Guid, studentHouse.UserId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, studentHouse.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, studentHouse.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
studentHouse.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
studentHouse.UpdatedDate = studentHouse.CreatedDate;
return true;
}
public int ExecuteSpSetResultValue(Database database, string returnParameterName)
{
database.ExecuteNonQuery(dbCommand);
return Convert.ToInt32(database.GetParameterValue(dbCommand, returnParameterName));
}
//Funcion para registrar Mecanicos
public void RegistrarMecanicos(Mecánico DatosM, DbTransaction tran, Database db)
{
string sqlCommand = "dbo.insertar_mecanicos";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
try {
db.AddInParameter(dbCommand, "@INTpersona", DbType.Int32, Utilerías.ObtenerValor(DatosM.Persona));
db.AddInParameter(dbCommand, "@STRtitulo", DbType.String, Utilerías.ObtenerValor(DatosM.Título));
db.AddInParameter(dbCommand, "@INTcodigo", DbType.Int32, Utilerías.ObtenerValor(DatosM.Código));
db.AddInParameter(dbCommand, "@INTexperiencia", DbType.Int32, Utilerías.ObtenerValor(DatosM.Experiencia));
db.AddOutParameter(dbCommand, "@nStatus", DbType.Int16, 2);
db.AddOutParameter(dbCommand, "@strMessage", DbType.String, 250);
db.ExecuteNonQuery(dbCommand, tran);
if (int.Parse(db.GetParameterValue(dbCommand, "@nStatus").ToString()) > 0)
throw new Exception(db.GetParameterValue(dbCommand, "@strMessage").ToString());
} catch (Exception ex) {
throw new Exception(ex.Message);
}
}
public bool UpdateHouse(User users, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_UserUpdateHouse");
db.AddInParameter(command, "UserId", DbType.Guid, users.UserId);
db.AddInParameter(command, "HouseId", DbType.Guid, users.HouseId);
db.AddInParameter(command, "BaseHouseRoomId", DbType.Guid, users.BaseHouseRoomId);
db.AddOutParameter(command, "UpdatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
users.UpdatedDate = Convert.ToDateTime(db.GetParameterValue(command, "UpdatedDate").ToString());
return true;
}
//Funcion para registrar reparaciones
public void RegistrarReparaciones(Reparaciones DatosR, DbTransaction tran, Database db)
{
string sqlCommand = "dbo.insertar_reparaciones";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
try {
db.AddInParameter(dbCommand, "@INTauto", DbType.Int32, Utilerías.ObtenerValor(DatosR.Auto));
db.AddInParameter(dbCommand, "@DTfecha", DbType.DateTime, Utilerías.ObtenerValor(DatosR.Fecha));
db.AddInParameter(dbCommand, "@STRnotas", DbType.String, Utilerías.ObtenerValor(DatosR.Notas));
db.AddInParameter(dbCommand, "@INTdiagnostica", DbType.Int32, Utilerías.ObtenerValor(DatosR.Diagnostica));
db.AddInParameter(dbCommand, "@STRdiagnostico", DbType.String, Utilerías.ObtenerValor(DatosR.Diagnóstico));
db.AddInParameter(dbCommand, "@INTevalua", DbType.Int32, Utilerías.ObtenerValor(DatosR.Evalúa));
db.AddInParameter(dbCommand, "@STRevaluacion", DbType.String, Utilerías.ObtenerValor(DatosR.Evaluación));
db.AddInParameter(dbCommand, "@CHARcompletado", DbType.Boolean, Utilerías.ObtenerValor(DatosR.Completada));
db.AddInParameter(dbCommand, "@INTencargado", DbType.Int32, Utilerías.ObtenerValor(DatosR.Encargado));
db.AddInParameter(dbCommand, "@CHARes_duenno", DbType.Boolean, Utilerías.ObtenerValor(DatosR.EsDueño));
db.AddInParameter(dbCommand, "@CHARconfirmar_todo", DbType.Boolean, Utilerías.ObtenerValor(DatosR.Confirmar));
db.AddOutParameter(dbCommand, "@nStatus", DbType.Int16, 2);
db.AddOutParameter(dbCommand, "@strMessage", DbType.String, 250);
db.ExecuteNonQuery(dbCommand, tran);
if (int.Parse(db.GetParameterValue(dbCommand, "@nStatus").ToString()) > 0)
throw new Exception(db.GetParameterValue(dbCommand, "@strMessage").ToString());
} catch (Exception ex) {
throw new Exception(ex.Message);
}
}
/// <summary>
/// Método definido registrar las películas asocidas a ventas
/// </summary>
/// <param name="DatosVP"></param>
/// <param name="tran"></param>
/// <param name="db"></param>
public void RegistrarVentasPelículas(VentaPelícula DatosVP, DbTransaction tran, Database db)
{
string sqlCommand = "dbo.insertar_ventas_peliculas";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
try
{
db.AddInParameter(dbCommand, "@INTventa", DbType.String, Utilerías.Utilerías.ObtenerValor(DatosVP.Venta));
db.AddInParameter(dbCommand, "@INTpelicula", DbType.Int32, Utilerías.Utilerías.ObtenerValor(DatosVP.Película));
db.AddOutParameter(dbCommand, "@nStatus", DbType.Int16, 2);
db.AddOutParameter(dbCommand, "@strMessage", DbType.String, 250);
db.ExecuteNonQuery(dbCommand, tran);
if (int.Parse(db.GetParameterValue(dbCommand, "@nStatus").ToString()) > 0)
throw new Exception(db.GetParameterValue(dbCommand, "@strMessage").ToString());
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Método definido para asociar las películas asociadas a un alquiler
/// </summary>
/// <param name="DatosAP"></param>
/// <param name="tran"></param>
/// <param name="db"></param>
public void RegistrarAlquilerPelículas(AlquilerPelícula DatosAP, DbTransaction tran, Database db)
{
string sqlCommand = "dbo.insertar_alquileres_peliculas";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
try
{
db.AddInParameter(dbCommand, "@INTalquiler", DbType.String, Utilerías.Utilerías.ObtenerValor(DatosAP.Alquiler));
db.AddInParameter(dbCommand, "@INTpelicula", DbType.Int32, Utilerías.Utilerías.ObtenerValor(DatosAP.Película));
db.AddInParameter(dbCommand, "@INTtotal_dvd", DbType.Int32, Utilerías.Utilerías.ObtenerValor(DatosAP.TotalDVD));
db.AddInParameter(dbCommand, "@INTtotal_blueray", DbType.Int32, Utilerías.Utilerías.ObtenerValor(DatosAP.TotalBlueRay));
db.AddInParameter(dbCommand, "@INTtotal_hddvd", DbType.Int32, Utilerías.Utilerías.ObtenerValor(DatosAP.TotalHD));
db.AddOutParameter(dbCommand, "@nStatus", DbType.Int16, 2);
db.AddOutParameter(dbCommand, "@strMessage", DbType.String, 250);
db.ExecuteNonQuery(dbCommand, tran);
if (int.Parse(db.GetParameterValue(dbCommand, "@nStatus").ToString()) > 0)
throw new Exception(db.GetParameterValue(dbCommand, "@strMessage").ToString());
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Executes the WriteLog stored procedure
/// </summary>
/// <param name="logEntry">The LogEntry to store in the database.</param>
/// <param name="database">The database.</param>
/// <returns>An integer for the LogEntry Id</returns>
private int ExecuteWriteLogStoredProcedureForLogEntry(LogEntry logEntry, Database database)
{
try
{
DbCommand commmand = database.GetStoredProcCommand(WriteLogStoredProcName);
database.AddInParameter(commmand, "p_app_name", DbType.String, _applicationName);
database.AddInParameter(commmand, "p_event_id", DbType.Int32, logEntry.EventId);
database.AddInParameter(commmand, "p_priority", DbType.Int32, logEntry.Priority);
database.AddInParameter(commmand, "p_severity", DbType.String, logEntry.Severity.ToString());
database.AddInParameter(commmand, "p_title", DbType.String, logEntry.Title);
database.AddInParameter(commmand, "p_log_time", DbType.DateTime, logEntry.TimeStamp.ToLocalTime());
database.AddInParameter(commmand, "p_machine_name", DbType.String, logEntry.MachineName);
database.AddInParameter(commmand, "p_app_domain_name", DbType.String, logEntry.AppDomainName);
database.AddInParameter(commmand, "p_process_id", DbType.String, logEntry.ProcessId);
database.AddInParameter(commmand, "p_process_name", DbType.String, logEntry.ProcessName);
database.AddInParameter(commmand, "p_thread_name", DbType.String, logEntry.ManagedThreadName);
database.AddInParameter(commmand, "p_win32_thread_id", DbType.String, logEntry.Win32ThreadId);
database.AddInParameter(commmand, "p_message", DbType.String, logEntry.Message);
database.AddInParameter(commmand, "p_method_name", DbType.String, null);
database.AddInParameter(commmand, "p_activity_id", DbType.String, logEntry.ActivityId.ToString());
database.AddInParameter(commmand, "p_tracing_start_ticks", DbType.String, null);
database.AddInParameter(commmand, "p_tracing_end_ticks", DbType.String, null);
database.AddInParameter(commmand, "p_seconds_elapsed", DbType.Decimal, null);
database.AddInParameter(commmand, "p_user_name", DbType.String, null);
database.AddInParameter(commmand, "p_ip_address", DbType.String, null);
database.AddInParameter(commmand, "p_user_roles", DbType.String, null);
database.AddInParameter(commmand, "p_user_graphic_area", DbType.String, null);
database.AddInParameter(commmand, "p_organization", DbType.String, null);
database.AddInParameter(commmand, "p_office", DbType.String, null);
database.AddInParameter(commmand, "p_module_id", DbType.String, null);
database.AddInParameter(commmand, "p_function_id", DbType.String, null);
database.AddInParameter(commmand, "p_component", DbType.String, null);
database.AddInParameter(commmand, "p_parameter_values", DbType.String, null);
database.AddInParameter(commmand, "p_flag", DbType.Int32, 0);
database.AddInParameter(commmand, "p_instance_id", DbType.String, null);
database.AddInParameter(commmand, "p_return_value", DbType.String, null);
if (Formatter != null)
{
database.AddInParameter(commmand, "p_formatted_message", DbType.String, Formatter.Format(logEntry));
}
else
{
database.AddInParameter(commmand, "p_formatted_message", DbType.String, logEntry.Message);
}
database.AddOutParameter(commmand, "p_log_id", DbType.Int32, 9);
database.ExecuteNonQuery(commmand);
int logId = (int)database.GetParameterValue(commmand, "p_log_id");
return logId;
}
catch (DbException ex)
{
ExceptionHelper.HandleCommonDbException(ex);
throw;
}
}
public bool Insert(Comment comment, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_CommentInsert");
//comment.CommentId = Guid.NewGuid();
// db.AddInParameter(command, "CommentId", DbType.Guid, comment.CommentId);
db.AddInParameter(command, "CommentText", DbType.String, comment.CommentText);
db.AddInParameter(command, "RatingValue", DbType.Decimal, comment.RatingValue);
db.AddInParameter(command, "ContextId", DbType.Guid, comment.ContextId);
db.AddInParameter(command, "ContextTypeId", DbType.Int32, (int)comment.ContextType);
db.AddInParameter(command, "CommentTypeId", DbType.Int32, (int)comment.CommentType);
db.AddInParameter(command, "FilePath", DbType.String, comment.FilePath);
db.AddInParameter(command, "IsDeleted", DbType.String, comment.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, comment.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
comment.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
comment.UpdatedDate = comment.CreatedDate;
return true;
}
public bool Update(OptionCategory OptionCategory, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionCategoryUpdate");
db.AddInParameter(command, "Name", DbType.String, OptionCategory.Name);
db.AddInParameter(command, "Description", DbType.String, OptionCategory.Description);
db.AddInParameter(command, "OptionCategoryId", DbType.Int16, OptionCategory.OptionCategoryId);
db.AddInParameter(command, "UpdatedBy", DbType.Guid, OptionCategory.CreatedBy);
db.AddInParameter(command, "UpdatedDate", DbType.DateTime, OptionCategory.CreatedDate);
db.AddOutParameter(command, "OptionCategoryId", DbType.Int16, 3);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
OptionCategory.OptionCategoryId = Convert.ToInt16(db.GetParameterValue(command, "OptionCategoryId").ToString());
return true;
}