public FileWord ReadFileWordUsingWord(FileWord model)
{
Session.Clear();
ISQLQuery query = Session.CreateSQLQuery(
" SELECT *" +
" FROM TBL_FILE_WORD" +
" WHERE USR_ID = :userId" +
" AND FILE_ID = :fileId" +
" AND FILE_WD = :fileWord");
query.SetParameter("userId", model.UserId);
query.SetParameter("fileId", model.FileId);
query.SetParameter("fileWord", model.Word);
query.AddEntity(typeof(FileWord));
return((FileWord)query.UniqueResult());
/*
* return Session.QueryOver<FileWord>()
* .Where(m
* => (m.UserId == model.UserId)
* && (m.FileId == model.FileId)
* && (m.Word == model.Word))
* .SingleOrDefault<FileWord>();
*/
}
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 int ReadMaxFileWordId(FileModel model)
{
Session.Clear();
ISQLQuery query = Session.CreateSQLQuery(
" SELECT ISNULL(MAX(FILE_WD_ID), 0)" +
" FROM TBL_FILE_WORD" +
" WHERE USR_ID = :userId" +
" AND FILE_ID = :fileId");
query.SetParameter("userId", model.UserId);
query.SetParameter("fileId", model.FileId);
int result = -1;
try
{
result = (int)query.UniqueResult();
System.Console.Out.WriteLine("max: " + result);
return(result);
}
catch (System.Exception ex)
{
System.Console.Out.WriteLine(ex.Message);
System.Console.Out.WriteLine(ex.StackTrace);
return(result);
}
}
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 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();
}
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 ISQLQuery GetExpensesMPAListQuery(long?CompanyID, long?RequesterID, long?CurrentUserID, bool isCount, string sortExpression)
{
StringBuilder sql = new StringBuilder();
if (!isCount)
{
sql.AppendLine("Select doc.DocumentNo, doc.Subject, doc.DocumentDate, mpaDoc.MPADocumentID ");
}
else
{
sql.AppendLine(" SELECT COUNT(*) ExpensesCount ");
}
sql.AppendLine(" from Document AS doc ");
sql.AppendLine(" LEFT JOIN MPADocument AS mpaDoc ");
sql.AppendLine(" ON doc.DocumentID = mpaDoc.DocumentID ");
sql.AppendLine(" LEFT JOIN MPAItem AS mpaItem ");
sql.AppendLine(" ON mpaItem.MPADocumentID = mpaDoc.MPADocumentID ");
sql.AppendLine(" WHERE doc.CompanyID = :CompanyID ");
sql.AppendLine(" AND mpaItem.UserID = :RequesterID ");
sql.AppendLine(" AND GETDATE() Between mpaDoc.StartDate AND mpaDoc.EndDate ");
sql.AppendLine(" AND doc.CacheCurrentStateName = 'Complete'");
if (!isCount)
{
if (string.IsNullOrEmpty(sortExpression))
{
sql.AppendLine(" ORDER BY doc.DocumentNo");
}
else
{
sql.AppendLine(string.Format(" ORDER BY {0}", sortExpression));
}
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
query.SetParameter("CompanyID", Convert.ToString(CompanyID));
query.SetParameter("RequesterID", Convert.ToString(RequesterID));
if (!isCount)
{
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.FillParameters(query);
query.AddScalar("DocumentNo", NHibernateUtil.String);
query.AddScalar("Subject", NHibernateUtil.String);
query.AddScalar("DocumentDate", NHibernateUtil.Date);
query.AddScalar("MPADocumentID", NHibernateUtil.Int64);
query.SetResultTransformer(Transformers.AliasToBean(typeof(ExpensesMPA))).List <ExpensesMPA>();
}
else
{
query.AddScalar("ExpensesCount", NHibernateUtil.Int32);
query.UniqueResult();
}
return(query);
}
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 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();
}
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();
}
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();
}
/// <summary>
/// Cuando se espera que en la cadena de parámetros venga un valor vacio (es decir un null) y el sp espera un string
/// se debe invocar este método
/// </summary>
/// <param name="querySession"></param>
/// <param name="idParam"></param>
/// <param name="value"></param>
private void addParamStringNulleable(ISQLQuery querySession, int idParam, string value)
{
if (value == null)
{
querySession.SetParameter(idParam, null, NHibernateUtil.String);
}
else
{
querySession.SetParameter(idParam, value);
}
}
/// <summary>
/// Cuando se espera que en la cadena de parámetros venga un valor vacio (es decir un null) y el sp espera un number
/// se debe invocar este método
/// </summary>
/// <param name="querySession"></param>
/// <param name="idParam"></param>
/// <param name="value"></param>
private void addParamIntNulleable(ISQLQuery querySession, int idParam, Int64 value)
{
if (value == null)
{
querySession.SetParameter(idParam, null, NHibernateUtil.Int64);
}
else
{
querySession.SetParameter(idParam, value);
}
}
public int ReadMaxHtmlWordId(HtmlModel model)
{
ISQLQuery query = Session.CreateSQLQuery(
" SELECT ISNULL(MAX(HTML_WD_ID), 0)" +
" FROM TBL_HTML_WORD" +
" WHERE USR_ID = :userId" +
" AND HTML_ID = :htmlId");
query.SetParameter("userId", model.UserId);
query.SetParameter("htmlId", model.HtmlId);
return((int)query.UniqueResult());
}
public int ReadMaxFileIOStatisticSequence(FileModel model)
{
ISQLQuery query = Session.CreateSQLQuery(
" SELECT ISNULL(MAX(FILE_IO_STAT_SEQ), 0)" +
" FROM TBL_FILE_IO_STAT" +
" WHERE USR_ID = :userId" +
" AND FILE_ID = :fileId");
query.SetParameter("userId", model.UserId);
query.SetParameter("fileId", model.FileId);
return((int)query.UniqueResult());
}
static void Main(string[] args)
{
try
{
using (ISession session = OpenSession())
{
Type
t = typeof(NHibernate.Connection.DriverConnectionProvider);
PropertyInfo
pi = t.GetProperty("ConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);
string connectionString = (string)pi.GetValue(((NHibernate.Impl.SessionFactoryImpl)(session.SessionFactory)).ConnectionProvider /*session.SessionFactory.ConnectionProvider*/, null);
IQuery
query;
query = session.CreateSQLQuery("select goodsutil.getgoodpropnumbyfs(:g_id, :price_id, :fs_id) as price from dual");
query.SetParameter("g_id", 5619974);
query.SetParameter("price_id", 10290);
query.SetParameter("fs_id", 2000);
object
tmpObject;
decimal
tmpDecimal = (tmpObject = query.UniqueResult()) != null && !Convert.IsDBNull(tmpObject) ? Convert.ToDecimal(tmpObject) : 0m;
ISQLQuery
SQLquery = session.CreateSQLQuery("select goodsutil.getgoodpropnumbyfs(:g_id, :price_id, :fs_id) as price from dual");
SQLquery.SetParameter("g_id", 5619974);
SQLquery.SetParameter("price_id", 10290);
SQLquery.SetParameter("fs_id", 2000);
//SQLquery.AddScalar("price", NHibernateUtil.Decimal);
tmpDecimal = (tmpObject = SQLquery.UniqueResult()) != null && !Convert.IsDBNull(tmpObject) ? Convert.ToDecimal(tmpObject) : 0m;
query = session.GetNamedQuery("NamedQuery1");
query.SetParameterList("goodId", new List <int>(new int[] { 3032803, 5619974 }));
query.SetParameter("price_id", 10290);
query.SetParameter("fs_id", 2000);
List <TestGood>
tmlList = (List <TestGood>)query.List <TestGood>();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public IList <HtmlWord> ReadHtmlWordList(HtmlModel model)
{
ISQLQuery query = Session.CreateSQLQuery(
" SELECT *" +
" FROM TBL_HTML_WORD" +
" WHERE USR_ID = :userId" +
" AND HTML_ID = :htmlId" +
" ORDER BY USR_ID, HTML_ID");
query.SetParameter("userId", model.UserId);
query.SetParameter("htmlId", model.HtmlId);
query.AddEntity(typeof(HtmlWord));
return(query.List <HtmlWord>());
}
public int ReadMaxFileIOLogSequence(FileModel model)
{
ISQLQuery query = Session.CreateSQLQuery(
" SELECT ISNULL(MAX(FILE_IO_LOG_SEQ), 0)" +
" FROM TBL_FILE_IO_LOG" +
" WHERE USR_ID = :userId" +
" AND FILE_ID = :fileId");
query.SetParameter("userId", model.UserId);
query.SetParameter("fileId", model.FileId);
System.Console.Out.WriteLine(query.UniqueResult());
return((int)query.UniqueResult());
}
public IList <FileIOLog> ReadFileIOLogList(FileModel model)
{
ISQLQuery query = Session.CreateSQLQuery(
" SELECT *" +
" FROM TBL_FILE_IO_LOG" +
" WHERE USR_ID = :userId" +
" AND FILE_ID = :fileId" +
" ORDER BY USR_ID, FILE_ID");
query.SetParameter("userId", model.UserId);
query.SetParameter("fileId", model.FileId);
query.AddEntity(typeof(FileIOLog));
return(query.List <FileIOLog>());
}
public virtual IList <IUserVO> RetrieveUserList(IList <string> excludedLoginIdList)
{
string query = RETRIEVE_USER;
if (excludedLoginIdList != null && excludedLoginIdList.Count > 0)
{
for (int counter = 0; counter < excludedLoginIdList.Count(); counter++)
{
query += (" AND su.login_id <> :LoginId" + counter);
}
}
ISQLQuery sqlQuery = CurrentSession.CreateSQLQuery(query);
sqlQuery.SetResultTransformer(Transformers.AliasToBean <UserVO>());
if (excludedLoginIdList != null && excludedLoginIdList.Count > 0)
{
for (int counter = 0; counter < excludedLoginIdList.Count(); counter++)
{
sqlQuery.SetParameter(("LoginId" + counter), excludedLoginIdList.ElementAt(counter));
}
}
return(sqlQuery.List <IUserVO>());
}
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 EditAntenna(Guid id, string name, string type, double horizontal_beamwidth, double vertical_beamwidth, string polarization, int number_of_feed, double horizontal_dimension, double vertical_dimension, string location)
{
ISQLQuery query = _session.CreateSQLQuery("UPDATE Antenna SET name = :name, type = :type, horizontal_beamwidth = :horizontal_beamwidth, vertical_beamwidth = :vertical_beamwidth, polarization = :polarization, number_of_feed = :number_of_feed, horizontal_dimension = :horizontal_dimension, vertical_dimension = :vertical_dimension, location = :location WHERE ID = :ID");
query.SetParameter("ID", id);
query.SetParameter("name", name);
query.SetParameter("type", type);
query.SetParameter("horizontal_beamwidth", horizontal_beamwidth);
query.SetParameter("vertical_beamwidth", vertical_beamwidth);
query.SetParameter("polarization", polarization);
query.SetParameter("number_of_feed", number_of_feed);
query.SetParameter("horizontal_dimension", horizontal_dimension);
query.SetParameter("vertical_dimension", vertical_dimension);
query.SetParameter("location", location);
query.ExecuteUpdate();
}
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 DeleteReceiver(Guid ID)
{
ISQLQuery query = _session.CreateSQLQuery("DELETE FROM Receiver WHERE ID = :ID");
query.SetParameter("ID", ID);
query.ExecuteUpdate();
}
public IList <T> FindAllWithNativeSql <T>(string sql, object[] values, IType[] types, IDictionary <string, NullableType> scalars)
{
if (sql == null || sql.Length == 0)
{
throw new ArgumentNullException("queryString");
}
if (values != null && types != null && types.Length != values.Length)
{
throw new ArgumentException("Length of values array must match length of types array");
}
using (ISession session = GetSession())
{
try
{
ISQLQuery query = session.CreateSQLQuery(sql);
if (values != null)
{
for (int i = 0; i < values.Length; i++)
{
if (types != null && types[i] != null)
{
query.SetParameter(i, values[i], types[i]);
}
else
{
query.SetParameter(i, values[i]);
}
}
}
foreach (string column in scalars.Keys)
{
query.AddScalar(column, scalars[column]);
}
IList <T> result = query.List <T>();
return(result);
}
catch (Exception ex)
{
throw new DataException("Could not perform Find for custom query : " + sql, ex);
}
}
}
private static ISQLQuery CreateSQLQuery(string sqlQuery, Parameter[] parameters)
{
ISQLQuery query = Session.CreateSQLQuery(sqlQuery);
foreach (Parameter parameter in parameters)
{
if (parameter.Type == null)
{
query.SetParameter(parameter.Name, parameter.Value);
}
else
{
query.SetParameter(parameter.Name, parameter.Value, parameter.Type);
}
}
AddCaching(query);
return(query);
}
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();
}