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 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 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 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;
}
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;
}
public bool Insert(Role roles, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_RoleInsert");
roles.RoleId = Guid.NewGuid();
db.AddInParameter(command, "@RoleName", DbType.String, roles.RoleName);
db.AddInParameter(command, "@RoleDescription", DbType.String, roles.RoleDescription);
db.AddInParameter(command, "@CreatedBy", DbType.Guid, roles.CreatedBy);
db.AddInParameter(command, "@RoleId", DbType.Guid, roles.RoleId);
db.ExecuteNonQuery(command, transaction);
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 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;
}
/// <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 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 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(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 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);
}
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 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 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;
}
/// <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 bool UpdateTrServicemasterInfo(TrServicemasterEntity trServicemasterEntity, Database db, DbTransaction transaction)
{
string sql = "UPDATE TR_ServiceMaster SET ServiceName= @Servicename, Description= @Description WHERE Id=@Id";
DbCommand dbCommand = db.GetSqlStringCommand(sql);
db.AddInParameter(dbCommand, "Id",DbType.String, trServicemasterEntity.Id);
db.AddInParameter(dbCommand, "Servicename", DbType.String, trServicemasterEntity.Servicename);
db.AddInParameter(dbCommand, "Description", DbType.String, trServicemasterEntity.Description);
db.ExecuteNonQuery(dbCommand, 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 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 SaveTrServicemasterInfo(TrServicemasterEntity trServicemasterEntity, Database db, DbTransaction transaction)
{
string sql = "INSERT INTO TR_ServiceMaster ( ServiceName, Description) VALUES ( @Servicename, @Description )";
DbCommand dbCommand = db.GetSqlStringCommand(sql);
db.AddInParameter(dbCommand, "Servicename", DbType.String, trServicemasterEntity.Servicename);
db.AddInParameter(dbCommand, "Description", DbType.String, trServicemasterEntity.Description);
db.ExecuteNonQuery(dbCommand, 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>
/// Delete SAS_ExportData Data...
/// <summary>
/// <param name=sender></param>
/// <param name= e></param>
public bool Delete(ExportDataEN argEn)
{
bool lbRes = false;
string sqlCmd = "DELETE FROM SAS_ExportData WHERE InterfaceID = @InterfaceID";
Microsoft.Practices.EnterpriseLibrary.Data.Database coDb = DatabaseFactory.CreateDatabase(csConnectionStr);
try
{
using (DbCommand cmd = coDb.GetSqlStringCommand(sqlCmd))
{
coDb.AddInParameter(cmd, "@InterfaceID", DbType.String, argEn.InterfaceID);
int liRowAffected = coDb.ExecuteNonQuery(cmd);
if (liRowAffected > -1)
{
lbRes = true;
}
else
{
throw new Exception("Deletion Failed! No Row has been deleted...");
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lbRes);
}
/// <summary>
/// GetItem SAS_ExportData Data...
/// <summary>
/// <param name=sender></param>
/// <param name= e></param>
public ExportDataEN GetItem(ExportDataEN argEn)
{
ExportDataEN loItem = new ExportDataEN();
string sqlCmd = "Select * FROM SAS_ExportData WHERE InterfaceID = @InterfaceID";
Microsoft.Practices.EnterpriseLibrary.Data.Database coDb = DatabaseFactory.CreateDatabase(csConnectionStr);
try
{
using (DbCommand cmd = coDb.GetSqlStringCommand(sqlCmd))
{
coDb.AddInParameter(cmd, "@InterfaceID", DbType.String, argEn.InterfaceID);
using (IDataReader loReader = coDb.ExecuteReader(cmd))
{
if (loReader != null)
{
loReader.Read();
loItem = LoadObject(loReader);
}
loReader.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(loItem);
}
public bool Delete(Option Option, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionDelete");
db.AddInParameter(command, "OptionId", DbType.Int16, Option.OptionId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, Option.IsDeleted);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
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 ExisteUsuario(string Usuario)
{
stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT COUNT(1) FROM DbSistemas.dbo.TBLSECUSUARIOS WHERE USUARIO = @USUARIO");
DbCommand commad = Database.GetSqlStringCommand(stringBuilder.ToString());
Database.AddInParameter(commad, "USUARIO", System.Data.DbType.String, Usuario);
if ((int)Database.ExecuteScalar(commad) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Delete(School school, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SchoolDelete");
db.AddInParameter(command, "SchoolId", DbType.Guid, school.SchoolId);
db.AddInParameter(command, "UpdatedBy", DbType.Guid, school.UpdatedBy);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
/// <summary>
/// 加载参数
/// </summary>
public static void BuildDBParameter(Database db, DbCommand dbCommand, params SqlParameter[] cmdParms)
{
foreach (SqlParameter sp in cmdParms)
{
db.AddInParameter(dbCommand, sp.ParameterName, sp.DbType,sp.Value);
}
}
public bool Delete(StudentHouse studentHouse, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_StudentHouseDelete");
db.AddInParameter(command, "StudentHouseId", DbType.Int16, studentHouse.StudentHouseId);
db.AddInParameter(command, "UpdatedBy", DbType.Guid, studentHouse.UpdatedBy);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
/// <summary>
/// 用户添加角色
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public DbCommand AddRolesForUser(Database db)
{
if (db == null) throw new ArgumentNullException("dataAccess");
string sql_insert = @"INSERT INTO mh_UsersInRoles(UserId,RoleId,[Status])
VALUES(@UserId,@RoleId,@Status)";
DbCommand dbCommand = db.GetSqlStringCommand(sql_insert);
dbCommand.CommandType = CommandType.Text;
db.AddInParameter(dbCommand, "@UserId", DbType.Int32, UserId);
db.AddInParameter(dbCommand, "@RoleId", DbType.Int32, RoleId);
db.AddInParameter(dbCommand, "@Status", DbType.Int32, Status);
dbCommand.Dispose();
return dbCommand;
}
public int Delete(int secenekId,DbTransaction dbTransaction,Database db)
{
sqlText="DELETE SORU_SECENEK_ARATABLO where SECENEK_ID=@secenekId ";
dbComm = db.GetSqlStringCommand(sqlText);
db.AddInParameter(dbComm, "@secenekId", DbType.Int32, secenekId);
SoruSecenekAratablo nesne = new SoruSecenekAratablo();
nesne.SecenekId = secenekId;
return base.Delete(nesne, dbComm, dbTransaction, db);
}
public void GetTiemposDeAlimentacion(int TiemposDeAlimentacionID)
{
DatabaseProviderFactory databaseProviderFactory = new DatabaseProviderFactory();
Microsoft.Practices.EnterpriseLibrary.Data.Database database = databaseProviderFactory.Create("DefaultConnection");
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("SELECT Nombre FROM TblTiemposDeAlimentacion WHERE TiemposDeAlimentacionID=@PTiemposDeAlimentacionID");
DbCommand command = database.GetSqlStringCommand(stringBuilder.ToString());
database.AddInParameter(command, "PTiemposDeAlimentacionID", System.Data.DbType.String, TiemposDeAlimentacionID);
Nombre = database.ExecuteScalar(command).ToString();
}
public void GetModuloHabitacional(string ModuloHabitacionalID)
{
DatabaseProviderFactory databaseProviderFactory = new DatabaseProviderFactory();
Microsoft.Practices.EnterpriseLibrary.Data.Database database = databaseProviderFactory.Create("DefaultConnection");
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("SELECT Nombre FROM TblModulosHabitacionales WHERE ModuloHabitacional=@PModuloHabitacional");
DbCommand command = database.GetSqlStringCommand(stringBuilder.ToString());
database.AddInParameter(command, "PModuloHabitacional", System.Data.DbType.String, ModuloHabitacionalID);
Nombre = database.ExecuteScalar(command).ToString();
}
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>
///// Thêm các tham số mặc định
///// </summary>
//public static void ApplyDefaultParameters(DbCommand command, bool autoAddDivisionID, bool isUsingCondition = true)
//{
// AddInParameter(command, PARA_CREATEUSERID, DbType.String, DongAEnvironment.UserID, true);
// AddInParameter(command, PARA_LASTMODIFYUSERID, DbType.String, DongAEnvironment.UserID, true);
// ASOFTCondition condition = DongAEnvironment.GetCondition();
// if (condition != null && isUsingCondition)
// {
// AddCondition(command,
// PARA_CONDITIONVT, condition.ConditionVT,
// PARA_ISUSEDCONDITIONVT, condition.IsUsedConditionVT);
// AddCondition(command,
// PARA_CONDITIONOB, condition.ConditionOB,
// PARA_ISUSEDCONDITIONOB, condition.IsUsedConditionOB);
// AddCondition(command,
// PARA_CONDITIONWA, condition.ConditionWA,
// PARA_ISUSEDCONDITIONWA, condition.IsUsedConditionWA);
// AddCondition(command,
// PARA_CONDITIONIV, condition.ConditionIV,
// PARA_ISUSEDCONDITIONIV, condition.IsUsedConditionIV);
// AddCondition(command,
// PARA_CONDITIONAC, condition.ConditionAC,
// PARA_ISUSEDCONDITIONAC, condition.IsUsedConditionAC);
// AddCondition(command,
// PARA_CONDITIONDE, condition.ConditionDE,
// PARA_ISUSEDCONDITIONDE, condition.IsUsedConditionDE);
// }
//}
public static void AddInParameter(DbCommand command, string name, DbType dbType, object value, bool overrideValue)
{
// Đảm bảo tên tham số đúng chuẩn
if (!name.StartsWith("@"))
{
name = "@" + name;
}
if (!command.Parameters.Contains(name))
{
Instance.AddInParameter(command, name, dbType, value);
}
else if (overrideValue)
{
Instance.SetParameterValue(command, name, value);
}
}
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);
}
//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);
}
/// <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();
}
}
/// <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>
/// 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>));
}
/// <summary>
/// Update SAS_ExportData Data...
/// <summary>
/// <param name=sender></param>
/// <param name= e></param>
public bool Update(ExportDataEN argEn)
{
bool lbRes = false;
int iOut = 0;
string sqlCmd = "Select count(*) as cnt From SAS_ExportData WHERE InterfaceID = '" + argEn.InterfaceID + "'";
try
{
Microsoft.Practices.EnterpriseLibrary.Data.Database loDbSel = DatabaseFactory.CreateDatabase(csConnectionStr);
using (DbCommand cmdSel = loDbSel.GetSqlStringCommand(sqlCmd))
{
using (IDataReader dr = loDbSel.ExecuteReader(cmdSel))
{
if (dr.Read())
{
iOut = GetValue <int>(dr, "cnt");
}
if (iOut < 0)
{
throw new Exception("Update Failed! No Record Exist!");
}
}
if (iOut != 0)
{
sqlCmd = "UPDATE SAS_ExportData SET InterfaceID = @InterfaceID, FileFormat = @FileFormat, Interface = @Interface, Frequency = @Frequency, TimeofExport = @TimeofExport, Filepath = @Filepath, PreviousData = @PreviousData,DateRange = @DateRange, DateFrom = @DateFrom, DateTo = @DateTo, LastUpdatedBy = @LastUpdatedBy, LastUpdatedDateTime = @LastUpdatedDateTime WHERE InterfaceID = @InterfaceID";
Microsoft.Practices.EnterpriseLibrary.Data.Database loDbUpd = DatabaseFactory.CreateDatabase(csConnectionStr);
using (DbCommand cmd = loDbUpd.GetSqlStringCommand(sqlCmd))
{
loDbUpd.AddInParameter(cmd, "@InterfaceID", DbType.String, argEn.InterfaceID);
loDbUpd.AddInParameter(cmd, "@FileFormat", DbType.String, argEn.FileFormat);
loDbUpd.AddInParameter(cmd, "@Interface", DbType.String, argEn.Interface);
loDbUpd.AddInParameter(cmd, "@Frequency", DbType.String, argEn.Frequency);
loDbUpd.AddInParameter(cmd, "@TimeofExport", DbType.String, argEn.TimeofExport);
loDbUpd.AddInParameter(cmd, "@Filepath", DbType.String, argEn.Filepath);
loDbUpd.AddInParameter(cmd, "@PreviousData", DbType.Boolean, argEn.PreviousData);
loDbUpd.AddInParameter(cmd, "@DateRange", DbType.Boolean, argEn.DateRange);
loDbUpd.AddInParameter(cmd, "@DateFrom", DbType.DateTime, argEn.DateFrom);
loDbUpd.AddInParameter(cmd, "@DateTo", DbType.DateTime, argEn.DateTo);
loDbUpd.AddInParameter(cmd, "@LastUpdatedBy", DbType.String, argEn.LastUpdatedBy);
loDbUpd.AddInParameter(cmd, "@LastUpdatedDateTime", DbType.DateTime, argEn.LastUpdatedDateTime);
int liRowAffected = loDbUpd.ExecuteNonQuery(cmd);
if (liRowAffected > -1)
{
System.Messaging.Message mm = new System.Messaging.Message(argEn, new System.Messaging.XmlMessageFormatter(new Type[] { typeof(ExportDataEN), typeof(string) }));
mm.Label = argEn.InterfaceID;
MessageQueueTransaction Transaction = new MessageQueueTransaction();
Transaction.Begin();
mq.Send(mm, Transaction);
Transaction.Commit();
lbRes = true;
}
else
{
throw new Exception("Update Failed! No Row has been updated...");
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lbRes);
}
public bool ActualizarPersonalTiemposDeAlimentacion(PersonalTiemposDeAlimentacion personalTiemposDeAlimentacion)
{
stringBuilder = new StringBuilder();
stringBuilder.AppendLine("UPDATE TblPersonalTiemposDeAlimentacion SET NumeroDeEmpleado=@NumeroDeEmpleado,Fecha=@Fecha,TiemposDeAlimentacionID=@TiemposDeAlimentacionID,ModuloHabitacional=@ModuloHabitacional");
stringBuilder.AppendLine(" WHERE PersonalTiemposDeAlimentacionID=@PersonalTiemposDeAlimentacionID");
DbCommand cmd = Database.GetSqlStringCommand(stringBuilder.ToString());
Database.AddInParameter(cmd, "NumeroDeEmpleado", DbType.String, personalTiemposDeAlimentacion.NumeroDeEmpleado);
Database.AddInParameter(cmd, "Fecha", DbType.DateTime, personalTiemposDeAlimentacion.Fecha);
Database.AddInParameter(cmd, "TiemposDeAlimentacionID", DbType.Int16, personalTiemposDeAlimentacion.TiemposDeAlimentacionID);
Database.AddInParameter(cmd, "ModuloHabitacional", DbType.Int16, personalTiemposDeAlimentacion.ModuloHabitacional);
Database.AddInParameter(cmd, "PersonalTiemposDeAlimentacionID", DbType.Int32, personalTiemposDeAlimentacion.PersonalTiemposDeAlimentacionID);
if (Database.ExecuteNonQuery(cmd) > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <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;
}
}
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;
}
