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; }