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(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 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(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(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;
}
/// <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 void btnOutParameter_Click(System.Object sender, System.EventArgs e)
{
//AddOutParameter
DbCommand cmd = db.GetStoredProcCommand("GetSampleDataItemNameValue");
db.AddInParameter(cmd, "id", DbType.Int32, 1);
db.AddOutParameter(cmd, "name", DbType.String, 50);
db.AddOutParameter(cmd, "value", DbType.String, -1); //Note that using -1 returns the correct length
db.ExecuteNonQuery(cmd);
txtResults.Text = string.Format("Name: {0}, Value: {1}", cmd.Parameters[0].Value, cmd.Parameters[1].Value);
}
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;
}
/// <summary>
/// Parametros para Base de Datos :
/// 1. @page (Número de página)
/// 2. @rowsPerPage (Filas por página)
/// 3. @sortDir (Ordenamiento ascendente ó descendente)
/// 4. @sortType (Ordenamiento por tipo de campo)
/// 5. @rowCount (Variable de salida de total de registros)
/// </summary>
/// <param name="oDatabase"></param>
/// <param name="oDbCommand"></param>
/// <param name="oPaginacion"></param>
/// <returns></returns>
public static Database DefaultParams(Database oDatabase, DbCommand oDbCommand, Paginacion oPaginacion)
{
oDatabase.AddInParameter(oDbCommand, "@page", DbType.Int32, oPaginacion.Page);
oDatabase.AddInParameter(oDbCommand, "@rowsPerPage", DbType.Int32, oPaginacion.RowsPerPage);
oDatabase.AddInParameter(oDbCommand, "@sortDir", DbType.String, oPaginacion.SortDir);
oDatabase.AddInParameter(oDbCommand, "@sortType", DbType.String, oPaginacion.SortType);
oDatabase.AddOutParameter(oDbCommand, "@rowCount", DbType.Int32, 0);
return oDatabase;
}
public static DbCommand GetCreateAttachmentCommand(Database database, RegisterAttachmentEntityNewLogic attach)
{
DbCommand cmd = database.GetStoredProcCommand("P_CreateRegisterAttachment");
database.AddOutParameter(cmd, "Id", DbType.String, 36);
database.AddInParameter(cmd, "RegisterId", DbType.String, attach.RegisterId);
database.AddInParameter(cmd, "SaveName", DbType.String, attach.SaveName);
database.AddInParameter(cmd, "ShowName", DbType.String, attach.ShowName);
return cmd;
}
public static DbCommand GetCreateStockCommand(Database database, StockEntity entity)
{
DbCommand cmd = database.GetStoredProcCommand("P_CreateStock");
database.AddOutParameter(cmd, "StockId", DbType.Int32, 4);
#region 参数赋值
database.AddInParameter(cmd, "StockNo", DbType.String, entity.StockNo);
database.AddInParameter(cmd, "StockName", DbType.String, entity.StockName);
database.AddInParameter(cmd, "CreateId", DbType.String, entity.CreateId);
database.AddInParameter(cmd, "Remark", DbType.String, entity.Remark);
#endregion
return cmd;
}
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;
}
/// <summary>
/// 加载参数
/// </summary>
public static void BuildDBParameter(Database db, DbCommand dbCommand, params IDataParameter[] cmdParms)
{
foreach (SqlParameter sp in cmdParms)
{
if (sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.Output)
{
db.AddOutParameter(dbCommand, sp.ParameterName, sp.DbType, sp.Size);
}
else
{
db.AddInParameter(dbCommand, sp.ParameterName, sp.DbType, sp.Value);
}
}
}
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);
}
/// <summary>
/// Create a new record in the database.
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
/// <remarks></remarks>
public int CreateDatafile(Datafile file)
{
int newid = 0;
DbCommand cmd = store.GetStoredProcCommand("CreateDatafile");
store.AddInParameter(cmd, "category", DbType.String, file.Category);
store.AddInParameter(cmd, "group", DbType.String, file.Group);
store.AddInParameter(cmd, "filename", DbType.String, file.Filename);
store.AddInParameter(cmd, "extension", DbType.String, file.Extension);
store.AddInParameter(cmd, "content", DbType.Binary, file.Content);
store.AddOutParameter(cmd, "newid", DbType.Int32, newid);
store.ExecuteNonQuery(cmd);
newid = System.Convert.ToInt32(cmd.Parameters[5].Value);
return(newid);
}
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 static DbCommand GetCreateRegisterCommand(Database database, RegisterEntity entity)
{
DbCommand cmd = database.GetStoredProcCommand("P_CreateRegister");
#region 参数赋值
database.AddOutParameter(cmd, "RegisterId", DbType.String, 36);
database.AddInParameter(cmd, "RegisterNo", DbType.String, entity.RegisterNo);
database.AddInParameter(cmd, "RegisterProductName", DbType.String, entity.RegisterProductName);
database.AddInParameter(cmd, "StandardCode", DbType.String, entity.StandardCode);
database.AddInParameter(cmd, "RegisterNo1", DbType.String, entity.RegisterNo1);
database.AddInParameter(cmd, "RegisterProductName1", DbType.String, entity.RegisterProductName1);
database.AddInParameter(cmd, "StandardCode1", DbType.String, entity.StandardCode1);
database.AddInParameter(cmd, "RegisterFile", DbType.String, entity.RegisterFile);
database.AddInParameter(cmd, "StartDate", DbType.String, entity.StartDate);
database.AddInParameter(cmd, "EndDate", DbType.String, entity.EndDate);
database.AddInParameter(cmd, "CreateId", DbType.String, entity.CreateId);
database.AddInParameter(cmd, "Remark", DbType.String, entity.Remark);
#endregion
return cmd;
}
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 static DbCommand GetCreateCompanyCommand(Database database, CompanyEntity entity)
{
DbCommand cmd = database.GetStoredProcCommand("P_CreateCompany");
#region 参数赋值
database.AddOutParameter(cmd, "CompanyId", DbType.Int32, 4);
database.AddInParameter(cmd, "CompanyNo", DbType.String, entity.CompanyNo);
database.AddInParameter(cmd, "CompanyName", DbType.String, entity.CompanyName);
database.AddInParameter(cmd, "Address", DbType.String, entity.Address);
database.AddInParameter(cmd, "Tel", DbType.String, entity.Tel);
database.AddInParameter(cmd, "Fax", DbType.String, entity.Fax);
database.AddInParameter(cmd, "PostCode", DbType.String, entity.PostCode);
database.AddInParameter(cmd, "EMail", DbType.String, entity.EMail);
database.AddInParameter(cmd, "Person", DbType.String, entity.Person);
database.AddInParameter(cmd, "BankAndAcount", DbType.String, entity.BankAndAcount);
database.AddInParameter(cmd, "TaxNumber", DbType.String, entity.TaxNumber);
database.AddInParameter(cmd, "ARTotal", DbType.Decimal, entity.ARTotal);
database.AddInParameter(cmd, "APTotal", DbType.Decimal, entity.APTotal);
database.AddInParameter(cmd, "Remark", DbType.String, entity.Remark);
database.AddInParameter(cmd, "Password", DbType.String, entity.Password);
database.AddInParameter(cmd, "PId", DbType.Int32, entity.PId);
database.AddInParameter(cmd, "CreateId", DbType.String, entity.CreateId);
#endregion
return cmd;
}
public bool InsertUpdateDelete(DataSet dsComments, Database db, DbTransaction transaction)
{
DbCommand commandInsert = db.GetStoredProcCommand("usp_CommentInsert");
db.AddInParameter(commandInsert, "CommentId", DbType.Int32, "CommentId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "CommentText", DbType.String, "CommentText", DataRowVersion.Current);
db.AddInParameter(commandInsert, "RatingValue", DbType.Decimal, "RatingValue", DataRowVersion.Current);
db.AddInParameter(commandInsert, "ContextTypeId", DbType.Int32, "ContextTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "CommentTypeId", DbType.Int32, "CommentTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "FilePath", DbType.String, "FilePath", DataRowVersion.Current);
db.AddInParameter(commandInsert, "IsDeleted", DbType.String, "IsDeleted", DataRowVersion.Current);
db.AddInParameter(commandInsert, "CreatedBy", DbType.Guid, "CreatedBy", DataRowVersion.Current);
db.AddOutParameter(commandInsert, "CreatedDate", DbType.DateTime, 30);
DbCommand commandUpdate = db.GetStoredProcCommand("usp_CommentUpdate");
db.AddInParameter(commandUpdate, "CommentId", DbType.Int32, "CommentId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "CommentText", DbType.String, "CommentText", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "RatingValue", DbType.Decimal, "RatingValue", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "ContextTypeId", DbType.Int32, "ContextType", DataRowVersion.Current);
db.AddInParameter(commandInsert, "CommentTypeId", DbType.Int32, "CommentTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "FilePath", DbType.String, "FilePath", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "IsDeleted", DbType.Boolean, "IsDeleted", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "UpdatedBy", DbType.Guid, "UpdatedBy", DataRowVersion.Current);
db.AddOutParameter(commandUpdate, "UpdatedDate", DbType.DateTime, 30);
DbCommand commandDelete = db.GetStoredProcCommand("usp_CommentDelete");
db.AddInParameter(commandDelete, "CommentId", DbType.Int32, "CommentId", DataRowVersion.Current);
db.AddInParameter(commandDelete, "UpdatedBy", DbType.Guid, "UpdatedBy", DataRowVersion.Current);
db.UpdateDataSet(dsComments, dsComments.Tables[0].TableName, commandInsert, commandUpdate, commandDelete, transaction);
return true;
}
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;
}
public static DbCommand GetCreateRawMateCommand(Database database, RawMateEntity entity)
{
DbCommand cmd = database.GetStoredProcCommand("P_CreateRawMate");
database.AddOutParameter(cmd, "RawMateId", DbType.Int32, 4);
#region 参数赋值
database.AddInParameter(cmd, "RawMateNo", DbType.String, entity.RawMateNo);
database.AddInParameter(cmd, "RawMateName", DbType.String, entity.RawMateName);
database.AddInParameter(cmd, "Spec", DbType.String, entity.Spec);
database.AddInParameter(cmd, "MinStore", DbType.Decimal, entity.MinStore);
database.AddInParameter(cmd, "MaxStore", DbType.Decimal, entity.MaxStore);
database.AddInParameter(cmd, "Unit", DbType.String, entity.Unit);
database.AddInParameter(cmd, "IsRemind", DbType.Boolean, entity.IsRemind);
database.AddInParameter(cmd, "QtyMode", DbType.Int16, entity.QtyMode);
database.AddInParameter(cmd, "preprice1", DbType.Decimal, entity.preprice1);
database.AddInParameter(cmd, "preprice2", DbType.Decimal, entity.preprice2);
database.AddInParameter(cmd, "preprice3", DbType.Decimal, entity.preprice3);
database.AddInParameter(cmd, "recprice", DbType.Decimal, entity.recprice);
database.AddInParameter(cmd, "Remark1", DbType.String, entity.Remark1);
database.AddInParameter(cmd, "Remark2", DbType.String, entity.Remark2);
database.AddInParameter(cmd, "Remark3", DbType.String, entity.Remark3);
database.AddInParameter(cmd, "Remark4", DbType.String, entity.Remark4);
database.AddInParameter(cmd, "PId", DbType.Int32, entity.PId);
database.AddInParameter(cmd, "CreateId", DbType.String, entity.CreateId);
#endregion
return cmd;
}
/// <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);
}
}
private static void EstablecerParametros(List<DbParameter> parametros, Database sqlDatabase, DbCommand comando)
{
foreach (DbParameter parametro in parametros)
{
if (parametro.Direction == ParameterDirection.Input)
{
sqlDatabase.AddInParameter(comando, parametro.ParameterName, parametro.DbType, parametro.Value);
}
else
{
sqlDatabase.AddOutParameter(comando, parametro.ParameterName, parametro.DbType, parametro.Size);
}
}
}
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(PartialHouse partialHouse, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_PartialHouseInsert");
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, "UpdatedBy", DbType.Guid, partialHouse.CreatedBy);
db.AddOutParameter(command, "UpdatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
partialHouse.UpdatedDate = Convert.ToDateTime(db.GetParameterValue(command, "UpdatedDate").ToString());
return true;
}
