public void Create(Person person) { CreateConnectionAndQuery( String.Format("INSERT INTO Person(FirstName, LastName, Age) VALUES('{0}', '{1}', {2})", person.Id, person.FirstName, person.LastName, person.Age) ); query.ExecuteUpdate(); DisposeConnection(); }
/// <summary> /// Delete all damage extents of project in the database /// </summary> /// <param name="projectId"></param> public void deleteDamageExtentsFromDB(int projectId) { if (projectId < 1) { return; } string _deleteQueryString = $"delete " + $"from \"DamageExtent\" " + $"where \"DamageExtent\".\"MappedObjectId\" in " + $"(select damageextent.\"MappedObjectId\" " + $"from \"DamageExtent\" as damageextent, \"Intensity\" as intensity " + $"where intensity.\"Project_Id\" = {projectId} " + $"and damageextent.\"IntensityId\" = intensity.\"ID\") "; using (var transaction = DBManager.ActiveSession.BeginTransaction()) { ISQLQuery query = ActiveSession.CreateSQLQuery(_deleteQueryString); query.ExecuteUpdate(); DBManager.ActiveSession.Flush(); transaction.Commit(); } //Change the project to state "Started" var _resultController = new ResultController(); _resultController.setProjectStatus(projectId, 1); }
/// <summary> /// 使用原生sql语句执行非查询操作 /// </summary> /// <param name="sql"></param> /// <returns></returns> public bool ExecuteSql(string strSql) { ISession session = null; ITransaction transaction = null; try { session = SessionFactory.OpenSession(); transaction = session.BeginTransaction(); ISQLQuery q = session.CreateSQLQuery(strSql); q.ExecuteUpdate(); transaction.Commit(); return(true); } catch (Exception ex) { LogHelper.Error(ex.Message, ex); transaction?.Rollback(); throw; } finally { session.Close(); } }
public bool setProjectStatus(int projectId, int statusId) { if (projectId < 1 || statusId < 1 || statusId > 5) { return(false); } //update "Project" //set "ProjectState_ID" = 1 //where "Id" = 9; string _setProjectStatusString = $"update \"Project\" " + $"set \"ProjectState_ID\" = {statusId} " + $"where \"Id\"={projectId} "; using (var transaction = DBManager.ActiveSession.BeginTransaction()) { ISQLQuery query = DBManager.ActiveSession.CreateSQLQuery(_setProjectStatusString); query.ExecuteUpdate(); DBManager.ActiveSession.Flush(); transaction.Commit(); } return(true); }
/// <summary> /// Выполнить хранимую процедуру восстановления БД. /// </summary> /// <param name="spName"></param> public void CallRepairSP(string spName) { #region Validate parameters if (String.IsNullOrEmpty(spName)) { throw new ArgumentException("Не указано имя хранимой процедуры.", "spName"); } #endregion string sql = String.Format("EXEC {0}", spName); try { using (UnitOfWork work = BeginWork()) { ISQLQuery sqlQuery = work.CreateSQLQuery(sql); sqlQuery.SetTimeout(this.ExecuteTimeout); int res = sqlQuery.ExecuteUpdate(); work.End(); } } catch (Exception ex) { throw CatchStoredProcedureException(spName, ex); } }
//[Category("InitDatabase_999")] //[Test] private void DropCreateDatebase(string connString) { var cfg = GetNHibernateConfiguration(connString); string[] sqlFiles = new string[] { @"..\..\sql\DropRViews.sql", @"..\..\sql\DropStateIdForeignKeyConstraints.sql", @"..\..\sql\hbm2ddl_create_fix.sql", @"..\..\sql\DropRViewNameConflictedTables.sql", @"..\..\sql\CreateRViews.sql", @"..\..\sql\AddStateIdForeignKeyConstraints.sql", }; var sf = cfg.BuildSessionFactory(); foreach (string file in sqlFiles) { using (StreamReader reader = new StreamReader(File.OpenRead(file))) { string sql = reader.ReadToEnd(); using (ISession session = sf.OpenSession()) { ISQLQuery query = session.CreateSQLQuery(sql); query.ExecuteUpdate(); } } } }
//update loan status to Paid when all installment have been paid public void UpdateLoanToPaid(string loanId) { StringBuilder sql = new StringBuilder(); sql.AppendLine(@" update l set loan_status = :loanStatus from t_loan l, ( select l.loan_id, count(i.installment_id) count_ins from dbo.T_LOAN l left join dbo.T_INSTALLMENT i on l.loan_id = i.loan_id and i.installment_status = :insStatus where l.loan_id = :loanId group by l.loan_id having count(i.installment_id) = 0 ) i where l.loan_id = i.loan_id and i.count_ins = 0 and l.loan_status = :currentLoanStatus and l.loan_id = :loanId; "); ISQLQuery q = Session.CreateSQLQuery(sql.ToString()); q.SetString("loanId", loanId); q.SetString("loanStatus", EnumLoanStatus.Paid.ToString()); q.SetString("insStatus", EnumInstallmentStatus.Not_Paid.ToString()); q.SetString("currentLoanStatus", EnumLoanStatus.OK.ToString()); q.ExecuteUpdate(); }
public void RetrieveFromCacheUpdate() { using (ISession session = SessionProvider.ISessionFactory .WithOptions() .OpenSession()) { // First Select var user = session.Query <User>().Where(x => x.UserCode == "user1") .WithOptions(x => x.SetCacheable(true)).FirstOrDefault(); // Second Select from cache var user2 = session.Query <User>().Where(x => x.UserCode == "user1") .WithOptions(x => x.SetCacheable(true)).FirstOrDefault(); // native Sql Update ISQLQuery query = session.CreateSQLQuery("UPDATE CUSTOMER SET NAME=:Name where GUID= :Guid"); query.SetParameter("Name", "Test"); query.SetParameter("Guid", 100); query.ExecuteUpdate(); // third select : User entity not changed should retrieve from cache. But hits Db var user3 = session.Query <User>().Where(x => x.UserCode == "user1") .WithOptions(x => x.SetCacheable(true)).FirstOrDefault(); Assert.That(1 == 1); } }
public async Task DeleteReceiver(Guid ID) { ISQLQuery query = _session.CreateSQLQuery("DELETE FROM Receiver WHERE ID = :ID"); query.SetParameter("ID", ID); query.ExecuteUpdate(); }
/// <summary> /// 使用原生sql语句执行非查询操作 /// </summary> /// <param name="sql"></param> /// <returns></returns> public bool ExecuteSql(string strSql) { ISession session = null; ITransaction transaction = null; try { session = SessionFactory.OpenSession(); transaction = session.BeginTransaction(); ISQLQuery q = session.CreateSQLQuery(strSql); q.ExecuteUpdate(); transaction.Commit(); return(true); } catch { if (transaction != null) { transaction.Rollback(); } throw; } finally { session.Close(); } }
public async Task DeleteAntennaScanUsingScanID(Guid id) { ISQLQuery query = _session.CreateSQLQuery("DELETE FROM AntennaScan WHERE scan_id = :scan_id"); query.SetParameter("scan_id", id); query.ExecuteUpdate(); }
public async Task DeleteLocation(Guid id) { ISQLQuery query = _session.CreateSQLQuery("DELETE FROM Location WHERE ID = :ID"); query.SetParameter("ID", id); query.ExecuteUpdate(); }
//private static IRepository repository = null; #endregion /// <summary> /// inserts the <see cref="BenutzerverwaltungBL.Model.DataObjects.Rechnung"/> as pdf in the db /// the titel is genrated out of the details of the <see cref="BenutzerverwaltungBL.Model.DataObjects.Rechnung"/> /// throws an exception if an error occurs /// </summary> /// <param name="rechnungsId">the identifier of the <see cref="BenutzerverwaltungBL.Model.DataObjects.Rechnung"/></param> /// <returns>true or throws an exception</returns> public static bool InsertRechnungAsDoc(int rechnungsId) { try { bool ret = true; Rechnung rechnungn; using (IRepository repository = RepositoryFactory.Instance.CreateRepository <Repository>()) { rechnungn = repository.SelectSingle <Rechnung>(DetachedCriteria.For <Rechnung>() .Add(Restrictions.IdEq(rechnungsId))); ISQLQuery query = repository.GetQuery("insert into " + TABLERECHNUNGDOCS + "(Title,Text) values (?,?)"); query.SetString(0, GenerateTitel(rechnungn)); query.SetParameter(1, GeneratePDF(rechnungn), NHibernateUtil.BinaryBlob); query.ExecuteUpdate(); rechnungn.IsAlreadyPdf = true; repository.SaveOrUpdate <Rechnung>(rechnungn); } return(ret); } catch (DatabaseException) { throw; } catch (Exception ex) { throw (new DatabaseException(ex, "Fehler beim ausstellen der Rechnung", "Überprüfen Sie ob die Rechnung bereits aussgestellt wurde", "Wenn Sie keine Lösung finden, melden Sie sich bei unserem Service Team.", "Danke")); } }
public async Task EditMode(Mode mode) { ISQLQuery query = _session.CreateSQLQuery("UPDATE Mode SET name = :name WHERE ID = :ID"); query.SetParameter("ID", mode.ID); query.SetParameter("name", mode.name); query.ExecuteUpdate(); }
public async Task DeleteAntennaScan(AntennaScan entity) { ISQLQuery query = _session.CreateSQLQuery("DELETE FROM AntennaScan WHERE antenna_id = :antenna_id AND scan_id = :scan_id"); query.SetParameter("antenna_id", entity.antenna_id); query.SetParameter("scan_id", entity.scan_id); query.ExecuteUpdate(); }
internal void RenameAntenna(Guid ID, string newName) { ISQLQuery query = _session.CreateSQLQuery("UPDATE Antenna SET name = :name WHERE ID = :ID"); query.SetParameter("ID", ID); query.SetParameter("name", newName); query.ExecuteUpdate(); }
public async Task SaveAntennaScan(AntennaScan entity) { ISQLQuery query = _session.CreateSQLQuery("INSERT INTO AntennaScan VALUES(:antenna_id, :scan_id)"); query.SetParameter("antenna_id", entity.antenna_id); query.SetParameter("scan_id", entity.scan_id); query.ExecuteUpdate(); }
internal void RenameReceiver(Guid id, String newName) { ISQLQuery query = _session.CreateSQLQuery("UPDATE Receiver SET name = :name WHERE ID = :ID"); query.SetParameter("ID", id); query.SetParameter("name", newName); query.ExecuteUpdate(); }
public async Task DeleteScan(Guid id) { ISQLQuery query = _session.CreateSQLQuery("DELETE FROM Scan WHERE ID in " + "(SELECT ID FROM Submode WHERE mode_id IN( SELECT ID FROM Mode " + "WHERE radar_id = :ID)); "); query.SetParameter("ID", id); query.ExecuteUpdate(); }
public async Task SaveMode(Mode entity) { ISQLQuery query = _session.CreateSQLQuery("INSERT INTO Mode VALUES(:ID, :name, :radar_id)"); query.SetParameter("ID", entity.ID); query.SetParameter("name", entity.name); query.SetParameter("radar_id", entity.radar_id); query.ExecuteUpdate(); }
public void UpdateTokenLastUpdateTime(String time) { using (ISession session = NhibernateSessionFactory.GetSessionFactory(NhibernateSessionFactory.SessionFactoryConfiguration.Application).OpenSession()) { ISQLQuery query = session.CreateSQLQuery(String.Format(@"UPDATE TOKEN SET VALUE = '{0}' WHERE TOKEN_ID = {1} AND KEY LIKE 'LastUpdateTime'", time, ID)); query.ExecuteUpdate(); session.Flush(); } }
public async Task EditRadar(Guid id, String name, String system, String configuration) { ISQLQuery query = _session.CreateSQLQuery("UPDATE Radar SET name = :name, system = :system, configuration = :configuration WHERE ID = :ID"); query.SetParameter("ID", id); query.SetParameter("name", name); query.SetParameter("system", system); query.SetParameter("configuration", configuration); query.ExecuteUpdate(); }
internal void UpdateSituacaoByIdLicitacao(int id, string situacao) { using (ISession session = NHibernateHelper.OpenSession()) { ISQLQuery query = session.CreateSQLQuery(string.Format("UPDATE licitacoes SET situacao = '{0}' WHERE idlicitacoes = {1}", situacao, id)); int result = query.ExecuteUpdate(); session.Close(); } }
private void RegisterUser() { using (var s = SessionFactory.OpenSession()) { string script = string.Empty; using (var reader = new StreamReader(Server.MapPath("~/" + ScriptFileName))) script = reader.ReadToEnd(); ISQLQuery query = s.CreateSQLQuery(script); query.ExecuteUpdate(); } }
public int ExcuteSql(String sql, IDictionary <string, Object> namedParameters) { CheckSqlInjection(sql, true); ISQLQuery q = Session.CreateSQLQuery(sql); foreach (var namedParameter in namedParameters) { q.SetParameter(namedParameter.Key, namedParameter.Value); } return(q.ExecuteUpdate()); }
public async Task SaveReceiver(Receiver entity) { ISQLQuery query = _session.CreateSQLQuery("INSERT INTO Receiver VALUES (:ID, :name, :listening_time, :rest_time, :recovery_time)"); query.SetParameter("ID", entity.ID); query.SetParameter("name", entity.name); query.SetParameter("listening_time", entity.listening_time); query.SetParameter("rest_time", entity.rest_time); query.SetParameter("recovery_time", entity.recovery_time); query.ExecuteUpdate(); }
public void UpdateLastForumsNotification(IEnumerable <string> notifiedMailAddresses, DateTime notificationTime) { // Users cannot edit their mailadresses so there is no SQL injection possible string joinedMailAddresses = string.Join(",", notifiedMailAddresses.Select(m => "'" + m + "'")); ISQLQuery updateQuery = this.Session.CreateSQLQuery( @"UPDATE MediaCommUsers SET LastForumsNotification = :notificationTime WHERE EMailAddress IN ( " + joinedMailAddresses + " )"); updateQuery.SetDateTime("notificationTime", notificationTime); updateQuery.ExecuteUpdate(); }
public async Task EditReceiver(Guid id, string name, double listening_time, double rest_time, double recovery_time) { ISQLQuery query = _session.CreateSQLQuery("UPDATE Receiver SET name = :name, listening_time = :listening_time, rest_time = :rest_time, recovery_time = :recovery_time WHERE ID = :ID"); query.SetParameter("ID", id); query.SetParameter("name", name); query.SetParameter("listening_time", listening_time); query.SetParameter("rest_time", rest_time); query.SetParameter("recovery_time", recovery_time); query.ExecuteUpdate(); }
///// <summary> ///// Удалить удаленные сообщения. ///// </summary> ///// <param name="channel">* - все.</param> //public virtual void DeleteDeletedMessages(string channel) //{ // var msgLinks = new List<int>(); // using ( IDataQuery dataQuery = OpenQuery() ) // { // var query = QueryMessages(dataQuery) // .Where(msg => msg.Status.Value == MessageStatus.DELETED); // if ( String.IsNullOrEmpty(channel) ) // query.Where(msg => msg.Channel == null || msg.Channel == ""); // else if ( channel != "*" ) // query.Where(msg => msg.Channel == channel); // msgLinks = query.List().Select(msg => msg.LINK).ToList(); // } // if ( msgLinks.Count > 0 ) // { // msgLinks.Sort(); // DeleteMessages(msgLinks); // } //} ///// <summary> ///// Удалить устаревшие сообщения. ///// System.InvalidOperationException: Ошибка удаления устаревших сообщений. ---> System.Exception: Cannot supply null value to operator LessThanOrEqual ///// </summary> ///// <param name="channel">* - все.</param> ///// <param name="expiredDate"></param> ///// <param name="statuses"></param> //public virtual void DeleteExpiredMessages(string channel, DateTime expiredDate, List<string> statuses) //{ // var msgLinks = new List<int>(); // using ( IDataQuery dataQuery = OpenQuery() ) // { // var query = QueryMessages(dataQuery) // .Where(msg => (msg.TTL == null && msg.Date <= expiredDate) || (msg.TTL <= DateTime.Now)) // .AndRestrictionOn(msg => msg.Status.Value).IsIn(statuses); // if ( String.IsNullOrEmpty(channel) ) // query.Where(msg => msg.Channel == null || msg.Channel == ""); // else if ( channel != "*" ) // query.Where(msg => msg.Channel == channel); // msgLinks = query.List().Select(msg => msg.LINK).ToList(); // } // if ( msgLinks.Count > 0 ) // { // msgLinks.Sort(); // DeleteMessages(msgLinks); // } //} /// <summary> /// /// </summary> /// <param name="msgLinks"></param> public virtual void DeleteMessages(IEnumerable <int> msgLinks) { #region Validate parameters if (msgLinks == null) { throw new ArgumentNullException("msgLinks"); } #endregion int count = msgLinks.Count(); if (count > 0) { int skip = 0; while (skip < count) { List <int> links = msgLinks.Skip(skip).Take(1000).ToList(); skip += links.Count; using (UnitOfWork work = BeginWork()) { ISQLQuery query = work.CreateSQLQuery(String.Format("DELETE FROM {0} WHERE MESSAGE_LINK IN (:messages)", Database.Tables.MESSAGE_PROPERTIES)); query.SetParameterList("messages", links); query.ExecuteUpdate(); query = work.CreateSQLQuery(String.Format("DELETE FROM {0} WHERE MESSAGE_LINK IN (:messages)", Database.Tables.MESSAGE_CONTENTS)); query.SetParameterList("messages", links); query.ExecuteUpdate(); //query = work.CreateSQLQuery(String.Format("DELETE FROM {0} WHERE MESSAGE_LINK IN (:messages)", Database.Tables.MESSAGE_POSTS)); //query.SetParameterList("messages", links); //query.ExecuteUpdate(); query = work.CreateSQLQuery(String.Format("DELETE FROM {0} WHERE LINK IN (:messages)", Database.Tables.MESSAGES)); query.SetParameterList("messages", links); query.ExecuteUpdate(); work.End(); } } } }
public async Task SaveTransmitter(Transmitter entity) { ISQLQuery query = _session.CreateSQLQuery("INSERT INTO Transmitter VALUES(:ID, :name, :modulation_type, :max_frequency, :min_frequency, :power)"); query.SetParameter("ID", entity.ID); query.SetParameter("name", entity.name); query.SetParameter("modulation_type", entity.modulation_type); query.SetParameter("max_frequency", entity.max_frequency); query.SetParameter("min_frequency", entity.min_frequency); query.SetParameter("power", entity.power); query.ExecuteUpdate(); }