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;
}
public static void CreateDataAdapterCommands(Database db, ref DbCommand insertCommand, ref DbCommand updateCommand, ref DbCommand deleteCommand)
{
insertCommand = db.GetStoredProcCommand("RegionInsert");
updateCommand = db.GetStoredProcCommand("RegionUpdate");
deleteCommand = db.GetStoredProcCommand("RegionDelete");
db.AddInParameter(insertCommand, "vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
db.AddInParameter(insertCommand, "vRegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);
db.AddInParameter(updateCommand, "vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
db.AddInParameter(updateCommand, "vRegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);
db.AddInParameter(deleteCommand, "vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
}
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 Update(Spotlight spotlight, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SpotlightUpdate");
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, "UpdatedBy", DbType.Guid, spotlight.UpdatedBy);
db.AddOutParameter(command, "UpdatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
spotlight.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "UpdatedDate").ToString());
spotlight.UpdatedDate = spotlight.CreatedDate;
return true;
}
/// <summary>
/// Instanciates a Admin1 object from the database via the admin1ID
/// </summary>
public Admin1(int admin1ID)
{
db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("AG_GetAdmin1Byadmin1ID");
db.AddInParameter(dbCommand, "admin1ID", DbType.Int32, admin1ID);
//execute the stored procedure
using (IDataReader dr = db.ExecuteReader(dbCommand))
{
ColumnFieldList list = new ColumnFieldList(dr);
if (dr.Read())
{
if (list.IsColumnPresent("admin1ID")) { this._admin1ID = (int)dr["admin1ID"]; }
if (list.IsColumnPresent("CountryID")) { this._countryID = (int)dr["CountryID"]; }
if (list.IsColumnPresent("admin1CD")) { this._admin1CD = (string)dr["admin1CD"]; }
if (list.IsColumnPresent("Name")) { this._name = (string)dr["Name"]; }
}
else
{
throw new Exception("There is no Admin1 in the database with the ID " + admin1ID);
}
dr.Close();
}
}
public static DbCommand Get_SP_ExecuteSQL(Database db, string stmtStr, string paramsStr)
{
DbCommand command = db.GetStoredProcCommand("sp_executesql");
db.AddInParameter(command, "stmt", DbType.String, stmtStr);
db.AddInParameter(command, "params", DbType.String, paramsStr);
return command;
}
public bool InsertUpdateDelete(Customer customer, Database db, DbTransaction transaction)
{
DbCommand commandInsert = db.GetStoredProcCommand("usp_CustomerGroupInsert");
db.AddInParameter(commandInsert, "@GroupId", DbType.Int32, customer.CustomerId);
db.AddInParameter(commandInsert, "@CompanyId", DbType.Int32, customer.CompanyId);
db.AddInParameter(commandInsert, "@CustomerName", DbType.String, "CustomerName", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@PassportNumber", DbType.String, "PassportNumber", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Gender", DbType.String, "Gender", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@GuestTypeId", DbType.Int32, "GuestTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Phone", DbType.String, "Phone", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@StatusId", DbType.Int32, (int)HBM.Common.Enums.HBMStatus.Active);
db.AddInParameter(commandInsert, "@IsGroupCustomer", DbType.Boolean, customer.IsGroupCustomer);
db.AddInParameter(commandInsert, "@CreatedUser", DbType.Int32, "CreatedUser", DataRowVersion.Current);
DbCommand commandUpdate = db.GetStoredProcCommand("usp_CustomerGroupUpdate");
db.AddInParameter(commandUpdate, "@CustomerId", DbType.Int32, "CustomerId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CustomerName", DbType.String, "CustomerName", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@PassportNumber", DbType.String, "PassportNumber", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Gender", DbType.String, "Gender", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@GuestTypeId", DbType.Int32, "GuestTypeId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Phone", DbType.String, "Phone", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@StatusId", DbType.Int32, (int)HBM.Common.Enums.HBMStatus.Active);
db.AddInParameter(commandUpdate, "@UpdatedUser", DbType.Int32, "UpdatedUser", DataRowVersion.Current);
DbCommand commandDelete = db.GetStoredProcCommand("usp_CustomerDelete");
db.AddInParameter(commandDelete, "@CustomerId", DbType.Int32, "CustomerId", DataRowVersion.Current);
db.UpdateDataSet(customer.DsGroupCustomers, customer.DsGroupCustomers.Tables[0].TableName, commandInsert, commandUpdate, commandDelete, transaction);
return true;
}
public bool DeleteRoleRightsByRoleId(Role roles, Database db, DbTransaction transaction)
{
DbCommand dbCommand = db.GetStoredProcCommand("usp_RoleRightDelete");
db.AddInParameter(dbCommand, "RoleId", DbType.Guid, roles.RoleId);
db.ExecuteNonQuery(dbCommand, transaction);
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;
}
//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(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(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(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 Delete(OptionCategory ropertyOptionCategory, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionCategoryDelete");
db.AddInParameter(command, "OptionCategoryId", DbType.Guid, ropertyOptionCategory.OptionCategoryId);
db.ExecuteNonQuery(command, transaction);
return true;
}
public bool InsertRoleRights(Role roles, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_RoleRightInsert");
db.AddInParameter(command, "@RoleId", DbType.Guid, roles.RoleId);
db.AddInParameter(command, "@RightId", DbType.Int32, roles.RightId);
db.AddInParameter(command, "@CreatedBy", DbType.Guid, roles.CreatedBy);
db.ExecuteNonQuery(command, transaction);
return true;
}
/// <summary>
/// Retrieve all files for a category and group
/// </summary>
/// <param name="category"></param>
/// <returns></returns>
/// <remarks></remarks>
public List <Datafile> RetrieveDataFiles(string category, string group)
{
List <Datafile> files = new List <Datafile>();
DbCommand cmd = store.GetStoredProcCommand("RetrieveDatafiles");
store.AddInParameter(cmd, "category", DbType.String, category);
store.AddInParameter(cmd, "group", DbType.String, group);
using (IDataReader reader = store.ExecuteReader(cmd))
{
while (reader.Read())
{
Datafile newfile = new Datafile();
newfile.ID = reader.GetInt32(reader.GetOrdinal("id"));
newfile.Category = category;
newfile.Group = System.Convert.ToString(reader.SafeGetString(reader.GetOrdinal("group")));
newfile.Filename = System.Convert.ToString(reader.SafeGetString(reader.GetOrdinal("filename")));
newfile.Extension = System.Convert.ToString(reader.SafeGetString(reader.GetOrdinal("extension")));
newfile.Content = (byte[])(reader.GetValue(reader.GetOrdinal("content")));
files.Add(newfile);
}
}
return(default(List <Datafile>));
}
public bool InsertUpdateDelete(ReservationAdditionalService reservationAddtionalService, Database db, DbTransaction transaction)
{
DbCommand commandInsert = db.GetStoredProcCommand("usp_ReservationAdditionalServiceInsert");
db.AddInParameter(commandInsert, "@ReservationId", DbType.Int64,reservationAddtionalService.ReservationId);
db.AddInParameter(commandInsert, "@Note", DbType.String, "Note", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Amount", DbType.Decimal, "Amount", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CreatedUser", DbType.Int32, "CreatedUser", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@StatusId", DbType.Int32, "StatusId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@AdditionalServiceId", DbType.Int32, "AdditionalServiceId", DataRowVersion.Current);
DbCommand commandUpdate = db.GetStoredProcCommand("usp_ReservationAdditionalServiceUpdate");
db.AddInParameter(commandUpdate, "@ReservationAdditionalServiceId", DbType.Int64, "ReservationAdditionalServiceId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Note", DbType.String, "Note", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Amount", DbType.String, "Amount", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@UpdatedUser", DbType.Int32, "UpdatedUser", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@StatusId", DbType.Int32, "StatusId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@AdditionalServiceId", DbType.Int32, "AdditionalServiceId", DataRowVersion.Current);
DbCommand commandDelete = db.GetStoredProcCommand("usp_ReservationAdditionalServiceDelete");
db.AddInParameter(commandDelete, "@ReservationAdditionalServiceId", DbType.Int64, "ReservationAdditionalServiceId", DataRowVersion.Current);
db.UpdateDataSet(reservationAddtionalService.ReservationAdditionalServiceList, reservationAddtionalService.ReservationAdditionalServiceList.Tables[0].TableName, commandInsert, commandUpdate, commandDelete, transaction);
return true;
}
public void btnInsert_Click(System.Object sender, System.EventArgs e)
{
//GetStoredProcCommand
DbCommand cmd = db.GetStoredProcCommand("InsertSampleData");
db.AddInParameter(cmd, "name", DbType.String, "Insert");
db.AddInParameter(cmd, "value", DbType.String, "New");
db.ExecuteNonQuery(cmd);
}
/// <summary>
/// Descripción: Metodo para guardar y actualizar un registro con los datos de la clase Informacion sin el manejo de la transaccion.
/// </summary>
/// <param name="oclsTblpreguntasInformacion">Instancia de la clase que se guardara.</param>
/// <param name="pdb">Instancia de la Base de Datos.</param>
public void m_Save(BC.Modelos.Informacion.clsTblpreguntasInformacion oclsTblpreguntasInformacion, Microsoft.Practices.EnterpriseLibrary.Data.Database pdb)
{
string vstrSP = string.Empty;
try
{
if (oclsTblpreguntasInformacion.bInsert)
{
vstrSP = "sva_Tblpreguntas_Ins";
}
else
{
vstrSP = "sva_Tblpreguntas_Upd";
}
DbCommand oCmd = pdb.GetStoredProcCommand(vstrSP);
pdb.AddInParameter(oCmd, "piIdpreguntas", DbType.Int32, oclsTblpreguntasInformacion.iIdpreguntas);
pdb.AddInParameter(oCmd, "psNombre", DbType.String, oclsTblpreguntasInformacion.sNombre);
pdb.AddInParameter(oCmd, "psParam", DbType.String, oclsTblpreguntasInformacion.sParam);
pdb.AddInParameter(oCmd, "piOrden", DbType.Int32, oclsTblpreguntasInformacion.iOrden);
pdb.AddInParameter(oCmd, "pdtFechacreacion", DbType.DateTime, oclsTblpreguntasInformacion.dtFechacreacion);
pdb.AddInParameter(oCmd, "pdtFechamodificacion", DbType.DateTime, oclsTblpreguntasInformacion.dtFechamodificacion);
pdb.AddInParameter(oCmd, "pdtFechabaja", DbType.DateTime, oclsTblpreguntasInformacion.dtFechabaja);
pdb.AddInParameter(oCmd, "pbActivo", DbType.Boolean, oclsTblpreguntasInformacion.bActivo);
pdb.AddInParameter(oCmd, "pbBaja", DbType.Boolean, oclsTblpreguntasInformacion.bBaja);
pdb.ExecuteNonQuery(oCmd);
oclsTblpreguntasInformacion.bInsert = false;
}
catch (Exception ex)
{
throw ex;
}
}
public static bool Insert(SurveyEntity survey, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SurveyInsert");
db.AddInParameter(command, "MyUniversity", DbType.String, survey.MyUniversity);
db.AddInParameter(command, "UniversityName", DbType.String, survey.UniversityName);
db.AddInParameter(command, "UniversityAddress", DbType.String, survey.UniversityAddress);
db.AddInParameter(command, "TypeOfResidence", DbType.String, survey.TypeOfResidence);
db.AddInParameter(command, "TypeOfResidenceOption", DbType.String, survey.TypeOfResidenceOption);
db.AddInParameter(command, "NameOfResidence", DbType.String, survey.NameOfResidence);
db.AddInParameter(command, "AddressOfResidence", DbType.String, survey.AddressOfResidence);
db.AddInParameter(command, "PropertyOwnerComment", DbType.String, survey.PropertyOwnerComment);
db.AddInParameter(command, "Email", DbType.String, survey.Email);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
internal static DataSet ExecuteDataSet(string CommandName, Int32 tipo, DbParameter[] param = null, DbTransaction transaction = null)
{
try
{
db = SetEnviroment(tipo);
using (DbCommand cmd = db.GetStoredProcCommand(CommandName))
{
if (param != null)
{
cmd.Parameters.AddRange(param);
}
//db.AddInParameter(cmd, "dni", DbType.String,"41856906");
DataSet ds = null;
if (transaction != null)
{
ds = db.ExecuteDataSet(cmd, transaction);
}
else
{
ds = db.ExecuteDataSet(cmd);
}
cmd.Dispose();
return(ds);
}
}
catch (Exception ex)
{
throw new Exception("Error Inesperado=>" + ex.Message, ex);
}
}
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;
}
internal static Int32 ExecuteNonQueryOut(String CommandName, Int32 tipo, DbParameter[] param, ref DbCommand cmdx, ref Database dbx, DbTransaction transaction = null) //out List<DbParameter> outputParameters
{
try
{
// DatabaseProviderFactory factory = new DatabaseProviderFactory();
//Database db = factory.Create(tipo == 1 ? cadConexion : cadConexion2);
db = SetEnviroment(tipo);
Int32 result = 0;
using (DbCommand cmd = db.GetStoredProcCommand(CommandName))
{
cmd.Parameters.AddRange(param);
if (transaction != null)
{
result = db.ExecuteNonQuery(cmd, transaction);
}
else
{
result = db.ExecuteNonQuery(cmd);
}
dbx = db;
cmdx = cmd;
return(result);
}
}
catch (Exception ex)
{
throw new Exception("Error Inesperado =>" + ex.Message, ex);
}
}
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;
}
internal static Int32 ExecuteNonQuery(String CommandName, Int32 tipo, DbParameter[] param = null, DbTransaction transaction = null)
{
try
{
db = SetEnviroment(tipo);
Int32 result = 0;
using (DbCommand cmd = db.GetStoredProcCommand(CommandName))
{
if (param != null)
{
cmd.Parameters.AddRange(param);
}
if (transaction != null)
{
result = db.ExecuteNonQuery(cmd, transaction);
}
else
{
result = db.ExecuteNonQuery(cmd);
}
return(result);
}
}
catch (Exception ex)
{
throw new Exception("Error Inesperado =>" + ex.Message, ex);
}
}
internal static Int32 ExecuteNonQueryOutWithOutDBX(String CommandName, DbParameter[] param, ref DbCommand cmdx, ref Database dbx, DbTransaction transaction = null) //out List<DbParameter> outputParameters
{
try
{
Int32 result = 0;
using (DbCommand cmd = db.GetStoredProcCommand(CommandName))
{
cmd.Parameters.AddRange(param);
if (transaction != null)
{
result = db.ExecuteNonQuery(cmd, transaction);
}
else
{
result = db.ExecuteNonQuery(cmd);
}
dbx = db;
cmdx = cmd;
return(result);
}
}
catch (Exception ex)
{
throw new Exception("Error Inesperado =>" + ex.Message, ex);
}
}
public void ChangeUserInfo(string userName, string firstName, string lastName, int deptId)
{
Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
this.ValidateParam("userName", userName);
DbCommand dbCommand = db.GetStoredProcCommand("ChangePersonalInformation", userName, firstName, lastName, deptId);
db.ExecuteNonQuery(dbCommand);
}
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 override bool Delete(Database db, DbTransaction transaction)
{
string proc = "WCS.DeleteAsrv";
DbCommand cmd = db.GetStoredProcCommand(proc);
db.AddInParameter(cmd, "pKeyValue", DbType.Int32, KeyValue);
db.ExecuteNonQuery(cmd, transaction);
return base.Delete(db, transaction);
}
public bool Delete(OptionItem OptionItem, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionItemDelete");
db.AddInParameter(command, "OptionItemId", DbType.Guid, OptionItem.OptionItemId);
db.AddInParameter(command, "IsDeleted", DbType.Guid, OptionItem.IsDeleted);
db.ExecuteNonQuery(command, transaction);
return true;
}
public bool Delete(Landlord landlord, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_LandlordDelete");
db.AddInParameter(command, "LandlordId", DbType.Guid, landlord.LandlordId);
db.ExecuteNonQuery(command, transaction);
return true;
}
public object ExecuteScalar(string query, CommandType commandType, List <IDbDataParameter> parameters)
{
DbConnection _connection;
_connection = _database.CreateConnection();
_connection.Open();
var cmd = commandType == CommandType.StoredProcedure ? _database.GetStoredProcCommand(query) : _database.GetSqlStringCommand(query);
cmd.CommandTimeout = 300;
cmd.Connection = _connection;
using (cmd)
{
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
return(_database.ExecuteScalar(cmd));
}
}
public bool Delete(Student student, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_StudentDelete");
db.AddInParameter(command, "StudentId", DbType.Guid, student.StudentId);
db.ExecuteNonQuery(command, transaction);
return true;
}
public bool Delete(Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("");
db.AddInParameter(command, "@ReservationGuestId", DbType.Int32, ReservationGuestId);
db.AddInParameter(command, "@ReservationId", DbType.Int32, ReservationId);
db.ExecuteNonQuery(command, transaction);
return true;
}
public bool Delete(Spotlight spotlight, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SpotlightDelete");
db.AddInParameter(command, "SpotlightId", DbType.Guid, spotlight.SpotlightId);
db.AddInParameter(command, "UpdatedBy", DbType.Guid, spotlight.UpdatedBy);
db.ExecuteNonQuery(command, transaction);
return true;
}
public bool InsertUpdateDelete(ReservationPayments reservationPayments, Database db, DbTransaction transaction)
{
DbCommand commandInsert = db.GetStoredProcCommand("usp_ReservationPaymentInsert");
db.AddInParameter(commandInsert, "@ReservationId", DbType.Int64, reservationPayments.ReservationId);
db.AddInParameter(commandInsert, "@PaymentDate", DbType.DateTime, "PaymentDate", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@ReferenceNumber", DbType.String, "ReferenceNumber", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Notes", DbType.String, "Notes", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@PaymentTypeId", DbType.Int32, "PaymentTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CurrencyId", DbType.Int32, "CurrencyId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CreditCardTypeId", DbType.Int32, "CreditCardTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CCNo", DbType.String, "CCNo", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CCExpirationDate", DbType.DateTime, "CCExpirationDate", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CCNameOnCard", DbType.String, "CCNameOnCard", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CreatedUser", DbType.Int32, "CreatedUser", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@StatusId", DbType.Int32, "StatusId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Amount", DbType.Decimal, "Amount", DataRowVersion.Current);
DbCommand commandUpdate = db.GetStoredProcCommand("usp_ReservationPaymentUpdate");
db.AddInParameter(commandUpdate, "@ReservationPaymentId", DbType.Int64, "ReservationPaymentId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@PaymentDate", DbType.DateTime, "PaymentDate", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@ReferenceNumber", DbType.String, "ReferenceNumber", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Notes", DbType.String, "Notes", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@PaymentTypeId", DbType.Int32, "PaymentTypeId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CurrencyId", DbType.Int32, "CurrencyId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CreditCardTypeId", DbType.Int32, "CreditCardTypeId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CCNo", DbType.String, "CCNo", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CCExpirationDate", DbType.DateTime, "CCExpirationDate", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CCNameOnCard", DbType.String, "CCNameOnCard", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@UpdatedUser", DbType.Int32, "UpdatedUser", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@StatusId", DbType.Int32, "StatusId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Amount", DbType.Decimal, "Amount", DataRowVersion.Current);
DbCommand commandDelete = db.GetStoredProcCommand("usp_ReservationPaymentDelete");
db.AddInParameter(commandDelete, "@ReservationPaymentId", DbType.Int64, "ReservationPaymentId", DataRowVersion.Current);
db.UpdateDataSet(reservationPayments.ReservationPaymentList, reservationPayments.ReservationPaymentList.Tables[0].TableName, commandInsert, commandUpdate, commandDelete, transaction);
return true;
}
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;
}
/// <summary>
/// Descripción: Método para obtener todos los registros de la base de datos en un DataSet. Generalmente este obtiene solo los registros que no estan dados de baja.
/// </summary>
/// <param name="pdb">Instancia de la Base de Datos</param>
/// <returns>Devuelve un objeto DataSet con la coleccion de los registros obtenidos en la consulta.</returns>
public System.Data.DataSet m_Load(Microsoft.Practices.EnterpriseLibrary.Data.Database pdb)
{
try
{
DbCommand oCmd = pdb.GetStoredProcCommand("svc_Tblcodigopostal");
DataSet ds = pdb.ExecuteDataSet(oCmd);
return(ds);
}
catch (Exception ex)
{
throw ex;
}
}
public void TestInitialize()
{
EnvironmentHelper.AssertOracleClientIsInstalled();
DatabaseProviderFactory factory = new DatabaseProviderFactory(OracleTestConfigurationSource.CreateConfigurationSource());
db = factory.Create("OracleTest");
storedProcedure = db.GetStoredProcCommand("NWND_CustOrdersOrders");
connection = db.CreateConnection();
connection.Open();
storedProcedure.Connection = connection;
cache = new ParameterCache();
baseFixture = new ParameterDiscoveryFixture(storedProcedure);
}
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 DbRulesManager()
//{
// dbRules = DatabaseFactory.CreateDatabase();
//}
/// <summary>
/// Retrieves a rule from the database
/// </summary>
/// <param name="Name">The name of the rule</param>
/// <returns>An AuthorizationRuleData object</returns>
public AuthorizationRuleData GetRule(string name)
{
AuthorizationRuleData rule = null;
DbCommand cmd = dbRules.GetStoredProcCommand("dbo.GetRuleByName");
dbRules.AddInParameter(cmd, "Name", DbType.String, name);
using (IDataReader reader = dbRules.ExecuteReader(cmd))
{
if (reader.Read())
{
rule = GetRuleFromReader(reader);
}
}
return(rule);
}
public DataSet ExecuteProcedure(string procedureName, List <SqlParameter> sqlParameters)
{
DatabaseProviderFactory factory = new DatabaseProviderFactory();
Microsoft.Practices.EnterpriseLibrary.Data.Database db = factory.Create("dProvider");
DbCommand dbCommand = db.GetStoredProcCommand(procedureName);
if ((sqlParameters != null) && (sqlParameters.Count > 0))
{
foreach (SqlParameter sqlParameter in sqlParameters)
{
db.AddInParameter(dbCommand, sqlParameter.ParameterName, sqlParameter.DbType, sqlParameter.Value);
}
}
DataSet customerDataSet = db.ExecuteDataSet(dbCommand);
return(customerDataSet);
}
/// <summary>
/// Descripción: Metodo para guardar y actualizar un registro con los datos de la clase Informacion con el manejo de la transacción.
/// </summary>
/// <param name="oclsTblcatladaInformacion">Instancia de la clase que se guardara.</param>
/// <param name="pdb">Instancia de la Base de Datos.</param>
/// <param name="poTrans">Instancia de la Transacción.</param>
public void m_Save(BC.Modelos.Informacion.clsTblcatladaInformacion oclsTblcatladaInformacion, Microsoft.Practices.EnterpriseLibrary.Data.Database pdb, System.Data.Common.DbTransaction poTrans)
{
string vstrSP = string.Empty;
try
{
if (oclsTblcatladaInformacion.bInsert)
{
vstrSP = "sva_Tblcatlada_Ins";
}
else
{
vstrSP = "sva_Tblcatlada_Upd";
}
DbCommand oCmd = pdb.GetStoredProcCommand(vstrSP);
pdb.AddInParameter(oCmd, "piIdlada", DbType.Int32, oclsTblcatladaInformacion.iIdlada);
pdb.AddInParameter(oCmd, "psNombre", DbType.String, oclsTblcatladaInformacion.sNombre);
pdb.AddInParameter(oCmd, "psDescripcion", DbType.String, oclsTblcatladaInformacion.sDescripcion);
pdb.AddInParameter(oCmd, "pbActivo", DbType.String, oclsTblcatladaInformacion.bActivo);
pdb.AddInParameter(oCmd, "pbBaja", DbType.String, oclsTblcatladaInformacion.bBaja);
pdb.AddInParameter(oCmd, "piIdusuariocreacion", DbType.String, oclsTblcatladaInformacion.iIdusuariocreacion);
pdb.AddInParameter(oCmd, "pdTfechacreacion", DbType.String, oclsTblcatladaInformacion.dTfechacreacion);
pdb.AddInParameter(oCmd, "piIdusuariomodificacion", DbType.String, oclsTblcatladaInformacion.iIdusuariomodificacion);
pdb.AddInParameter(oCmd, "pdTfechamodificacion", DbType.String, oclsTblcatladaInformacion.dTfechamodificacion);
pdb.AddInParameter(oCmd, "piIdusuarioabaja", DbType.String, oclsTblcatladaInformacion.iIdusuarioabaja);
pdb.AddInParameter(oCmd, "pdTfechabaja", DbType.String, oclsTblcatladaInformacion.dTfechabaja);
pdb.ExecuteNonQuery(oCmd, poTrans);
oclsTblcatladaInformacion.bInsert = false;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Descripción: Metodo para guardar y actualizar un registro con los datos de la clase Informacion con el manejo de la transacción.
/// </summary>
/// <param name="oclsTblcodigopostalInformacion">Instancia de la clase que se guardara.</param>
/// <param name="pdb">Instancia de la Base de Datos.</param>
/// <param name="poTrans">Instancia de la Transacción.</param>
public void m_Save(BC.Modelos.Informacion.clsTblcodigopostalInformacion oclsTblcodigopostalInformacion, Microsoft.Practices.EnterpriseLibrary.Data.Database pdb, System.Data.Common.DbTransaction poTrans)
{
string vstrSP = string.Empty;
try
{
if (oclsTblcodigopostalInformacion.bInsert)
{
vstrSP = "sva_Tblcodigopostal_Ins";
}
else
{
vstrSP = "sva_Tblcodigopostal_Upd";
}
DbCommand oCmd = pdb.GetStoredProcCommand(vstrSP);
pdb.AddInParameter(oCmd, "piIdcodigopostal", DbType.Int32, oclsTblcodigopostalInformacion.iIdcodigopostal);
pdb.AddInParameter(oCmd, "psCodigo", DbType.String, oclsTblcodigopostalInformacion.sCodigo);
pdb.AddInParameter(oCmd, "psAsentamiento", DbType.String, oclsTblcodigopostalInformacion.sAsentamiento);
pdb.AddInParameter(oCmd, "psTipoasentamiento", DbType.String, oclsTblcodigopostalInformacion.sTipoasentamiento);
pdb.AddInParameter(oCmd, "psMunicipio", DbType.String, oclsTblcodigopostalInformacion.sMunicipio);
pdb.AddInParameter(oCmd, "psEstado", DbType.String, oclsTblcodigopostalInformacion.sEstado);
pdb.AddInParameter(oCmd, "psCiudad", DbType.String, oclsTblcodigopostalInformacion.sCiudad);
pdb.AddInParameter(oCmd, "pdtFechacreacion", DbType.DateTime, oclsTblcodigopostalInformacion.dtFechacreacion);
pdb.AddInParameter(oCmd, "pdtFechamodificacion", DbType.DateTime, oclsTblcodigopostalInformacion.dtFechamodificacion);
pdb.AddInParameter(oCmd, "pdtFechabaja", DbType.DateTime, oclsTblcodigopostalInformacion.dtFechabaja);
pdb.AddInParameter(oCmd, "pbBaja", DbType.Boolean, oclsTblcodigopostalInformacion.bBaja);
pdb.ExecuteNonQuery(oCmd, poTrans);
oclsTblcodigopostalInformacion.bInsert = false;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// ExecuteNonQuery 数据添加、修改、删除
/// </summary>
/// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
/// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>受影响的行数</returns>
public int ExecuteNonQuery(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
try
{
this.Open();
if (cmdType == CommandType.StoredProcedure)
{
mDbCommand = mDatabase.GetStoredProcCommand(cmdText);
}
else
{
mDbCommand = mDatabase.GetSqlStringCommand(cmdText);
}
mDbCommand.Connection = Connection;
if (Transaction != null)
{
mDbCommand.Transaction = Transaction;
}
if ((commandParameters != null) && (commandParameters.Length > 0))
{
foreach (IDbDataParameter mParameter in commandParameters)
{
mDatabase.AddParameter(mDbCommand, mParameter.ParameterName, mParameter.DbType, mParameter.Direction, mParameter.SourceColumn, mParameter.SourceVersion, mParameter.Value);
}
}
return(mDbCommand.ExecuteNonQuery());
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (Transaction == null)
{
this.Close();
}
}
}
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;
}
