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