Ejemplo n.º 1
0
        public IList <SourceDTO> GetAllSourceDTOs(IStatelessSession session, bool excludeBinaryIndexedSources, bool excludeSourceLogged)
        {
            string sql = @"
                SELECT s.SourceID, s.SourceName, s.FullReference, s.SourcePath, s.SourceDate, s.FileExtension, s.IsRestricted, s.FileDateTimeStamp, s.Archive, 
                    s.IsReadOnly, s.Notes, s.IsPublic, DATALENGTH(s.FileData) AS FileSize, j.CaseNumber AS JhroCaseNumber, j.JhroCaseID,
                    (CASE WHEN s.OriginalFileData IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END) AS HasOcrText,
                    au.UserID AS UploadedByUserID
                FROM PRF_Source s LEFT JOIN PRF_JhroCase j ON s.JhroCaseID = j.JhroCaseID
                LEFT JOIN PRF_FeedingSource fs ON s.SourceID = fs.SourceID
                LEFT JOIN PRF_AdminUser au ON fs.UploadedByID = au.AdminUserID
                WHERE s.Archive = 0
            ";

            if (excludeBinaryIndexedSources)
            {
                sql += " AND s.SourceID NOT IN (SELECT SourceID FROM PRF_SourceIndexLog)";
            }
            if (excludeSourceLogged)
            {
                sql += " AND s.SourceID NOT IN (SELECT SourceID FROM PRF_SourceLog)";
            }

            return((session == null ? Session.CreateSQLQuery(sql) : session.CreateSQLQuery(sql))
                   .SetResultTransformer(Transformers.AliasToBean(typeof(SourceDTO)))
                   .List <SourceDTO>());
        }
        public async Task <(IEnumerable <T> update, IEnumerable <string> delete, long version)> GetAsync(SyncAzureQuery query, CancellationToken token)
        {
            var sql = query.Version == 0 ? FirstQuery : VersionSql;

            var sqlQuery = _session.CreateSQLQuery(sql);

            sqlQuery.SetInt32("PageSize", PageSize);
            sqlQuery.SetInt32("PageNumber", query.Page);
            if (sqlQuery.NamedParameters.Any(a => string.Equals(a, "Version", StringComparison.OrdinalIgnoreCase)))
            {
                sqlQuery.SetInt64("Version", query.Version);
            }
            sqlQuery.SetResultTransformer(new AzureSyncBaseDtoTransformer <AzureSyncBaseDto <T>, T>());


            var result = await sqlQuery.ListAsync <AzureSyncBaseDto <T> >(token);

            if (result.Count == 0)
            {
                return(Enumerable.Empty <T>(), Enumerable.Empty <string>(), 0);
            }

            var lookupTable = SeparateUpdateFromDelete(result);

            var max = result.Max(m => m.SYS_CHANGE_VERSION.GetValueOrDefault());

            return(lookupTable[false].Select(s => s.Data), lookupTable[true].Select(s => s.Id), max);
        }
Ejemplo n.º 3
0
            public async Task <IEnumerable <TransactionDto> > GetAsync(UserTransactionQuery query, CancellationToken token)
            {
                var sqlQueryStr = $@"with cte
as
(
select id as id, 
created as Created,
action as action,
type as  type,
price as price 
from sb.[transaction]
where user_id = :userId
) select Created as Date,
action as Action,
type as Type,
price as Amount,
( select
SUM(price) as balance from cte  
 where id<=c.id) as Balance
from cte c
order by id desc";
                var sqlQuery    = _session.CreateSQLQuery(sqlQueryStr);

                sqlQuery.SetInt64("userId", query.Id);
                sqlQuery.SetResultTransformer(Transformers.AliasToBean <TransactionDto>());

                return(await sqlQuery.ListAsync <TransactionDto>(token));
            }
Ejemplo n.º 4
0
        public override IList <AcessoDocumento> GetEntities(IStatelessSession session)
        {
            const string Sql = @"
select
    profiles.usr_code UsrCode, access_doc.groupdoc_code Category
from 
    access_doc access_doc,
    (select usr_code from usr where usr_type = 'G') profiles
where
    access_doc.usr_code = profiles.usr_code";

            var profileCategories = new List <AcessoDocumento>();

            var dtos = session
                       .CreateSQLQuery(Sql)
                       .SetResultTransformer(CustomResultTransformer <AccessDocDto> .Do())
                       .List <AccessDocDto>();

            var i = 1;

            foreach (var dto in dtos)
            {
                profileCategories.Add(new AcessoDocumento
                {
                    Id            = i++,
                    TipoDocumento = this.Create <TipoDocumento>(dto.Category),
                    AtorId        = dto.UsrCode.ToInt(),
                    Papel         = Papel.Pefil
                });
            }

            return(profileCategories);
        }
Ejemplo n.º 5
0
        protected object[] LoadStat(IStatelessSession session)
        {
            if (ActualAddress == null || CurrentOffer.Value == null)
            {
                return(null);
            }

            if (CurrentOffer.Value.StatLoaded)
            {
                return(null);
            }

            if (session == null)
            {
                return(null);
            }

            var begin = DateTime.Now.AddMonths(-1);

            return(Util.Cache(Cache,
                              Tuple.Create(ActualAddress.Id, CurrentOffer.Value.ProductId),
                              k => session.CreateSQLQuery(@"select avg(cost) as avgCost, avg(count) as avgCount
from SentOrderLines ol
join SentOrders o on o.Id = ol.OrderId
where o.SentOn > :begin and ol.ProductId = :productId and o.AddressId = :addressId")
                              .SetParameter("begin", begin)
                              .SetParameter("productId", CurrentOffer.Value.ProductId)
                              .SetParameter("addressId", ActualAddress.Id)
                              .UniqueResult <object[]>()));
        }
Ejemplo n.º 6
0
        private IQuery PrepareQuery()
        {
            var sb = new StringBuilder();

            foreach (var param in _parameters)
            {
                sb.AppendFormat(":{0} ,", param.Key);
            }

            var paramlist = sb.ToString();

            if (paramlist.Length > 0)
            {
                paramlist = paramlist.Substring(0, paramlist.Length - 1);
            }


            IQuery query =
                _session.CreateSQLQuery(string.Format(_dbType == DataBaseType.Oracle ? OraProcedureBody : MssqlProcedureBody,
                                                      _procedureName, paramlist));

            foreach (var param in _parameters)
            {
                if (param.Value != null)
                {
                    query.SetParameter(param.Key, param.Value);
                }
                else
                {
                    query.SetParameter(param.Key, param.Value, NHibernateUtil.Int32);
                }
            }
            return(query);
        }
Ejemplo n.º 7
0
        public async Task CreateUpdateReadDeleteAsync()
        {
            Document doc;
            DateTime?initVersion;

            using (IStatelessSession ss = Sfi.OpenStatelessSession())
            {
                ITransaction tx;
                using (tx = ss.BeginTransaction())
                {
                    doc = new Document("blah blah blah", "Blahs");
                    await(ss.InsertAsync(doc));
                    Assert.IsNotNull(doc.LastModified);
                    initVersion = doc.LastModified;
                    Assert.IsTrue(initVersion.HasValue);
                    await(tx.CommitAsync());
                }
                await(Task.Delay(1100));                  // Ensure version increment (some dialects lack fractional seconds).
                using (tx = ss.BeginTransaction())
                {
                    doc.Text = "blah blah blah .... blah";
                    await(ss.UpdateAsync(doc));
                    Assert.IsTrue(doc.LastModified.HasValue);
                    Assert.AreNotEqual(initVersion, doc.LastModified);
                    await(tx.CommitAsync());
                }
                using (tx = ss.BeginTransaction())
                {
                    doc.Text = "blah blah blah .... blah blay";
                    await(ss.UpdateAsync(doc));
                    await(tx.CommitAsync());
                }
                var doc2 = await(ss.GetAsync <Document>("Blahs"));
                Assert.AreEqual("Blahs", doc2.Name);
                Assert.AreEqual(doc.Text, doc2.Text);

                doc2 = (Document)await(ss.CreateQuery("from Document where text is not null").UniqueResultAsync());
                Assert.AreEqual("Blahs", doc2.Name);
                Assert.AreEqual(doc.Text, doc2.Text);

                doc2 = (Document)await(ss.CreateSQLQuery("select * from Document").AddEntity(typeof(Document)).UniqueResultAsync());
                Assert.AreEqual("Blahs", doc2.Name);
                Assert.AreEqual(doc.Text, doc2.Text);

                doc2 = await(ss.CreateCriteria <Document>().UniqueResultAsync <Document>());
                Assert.AreEqual("Blahs", doc2.Name);
                Assert.AreEqual(doc.Text, doc2.Text);

                doc2 = (Document)await(ss.CreateCriteria(typeof(Document)).UniqueResultAsync());
                Assert.AreEqual("Blahs", doc2.Name);
                Assert.AreEqual(doc.Text, doc2.Text);

                using (tx = ss.BeginTransaction())
                {
                    await(ss.DeleteAsync(doc));
                    await(tx.CommitAsync());
                }
            }
        }
        public void CreateUpdateReadDelete()
        {
            Document doc;
            DateTime?initVersion;

            using (IStatelessSession ss = sessions.OpenStatelessSession())
            {
                ITransaction tx;
                using (tx = ss.BeginTransaction())
                {
                    doc = new Document("blah blah blah", "Blahs");
                    ss.Insert(doc);
                    Assert.IsNotNull(doc.LastModified);
                    initVersion = doc.LastModified;
                    Assert.IsTrue(initVersion.HasValue);
                    tx.Commit();
                }
                Thread.Sleep(100);                 // Only to be secure that next modification have a different version
                using (tx = ss.BeginTransaction())
                {
                    doc.Text = "blah blah blah .... blah";
                    ss.Update(doc);
                    Assert.IsTrue(doc.LastModified.HasValue);
                    Assert.AreNotEqual(initVersion, doc.LastModified);
                    tx.Commit();
                }
                using (tx = ss.BeginTransaction())
                {
                    doc.Text = "blah blah blah .... blah blay";
                    ss.Update(doc);
                    tx.Commit();
                }
                var doc2 = ss.Get <Document>("Blahs");
                Assert.AreEqual("Blahs", doc2.Name);
                Assert.AreEqual(doc.Text, doc2.Text);

                doc2 = (Document)ss.CreateQuery("from Document where text is not null").UniqueResult();
                Assert.AreEqual("Blahs", doc2.Name);
                Assert.AreEqual(doc.Text, doc2.Text);

                doc2 = (Document)ss.CreateSQLQuery("select * from Document").AddEntity(typeof(Document)).UniqueResult();
                Assert.AreEqual("Blahs", doc2.Name);
                Assert.AreEqual(doc.Text, doc2.Text);

                doc2 = ss.CreateCriteria <Document>().UniqueResult <Document>();
                Assert.AreEqual("Blahs", doc2.Name);
                Assert.AreEqual(doc.Text, doc2.Text);

                doc2 = (Document)ss.CreateCriteria(typeof(Document)).UniqueResult();
                Assert.AreEqual("Blahs", doc2.Name);
                Assert.AreEqual(doc.Text, doc2.Text);

                using (tx = ss.BeginTransaction())
                {
                    ss.Delete(doc);
                    tx.Commit();
                }
            }
        }
Ejemplo n.º 9
0
        public void CreateSQLQueryAll()
        {
            using IStatelessSession session = SessionFactory.OpenStatelessSession();

            session.CreateSQLQuery("select * from Person")
            .SetResultTransformer(Transformers.AliasToBean <Person>())
            .List <Person>().Consume(consumer);
        }
Ejemplo n.º 10
0
 public Post SQL()
 {
     Step();
     return(_sql.CreateSQLQuery(@"select * from Posts where Id = :id")
            .SetInt32("id", i)
            .SetResultTransformer(Transformers.AliasToBean <Post>())
            .List <Post>()[0]);
 }
Ejemplo n.º 11
0
        private UserList GetUser(IStatelessSession session)
        {
            var userLogin = session.CreateSQLQuery("SELECT USER FROM DUAL").UniqueResult <string>();

            return
                (session.QueryOver <UserList>()
                 .Where(x => x.AUTHID == userLogin)
                 .UnderlyingCriteria.UniqueResult <UserList>());
        }
Ejemplo n.º 12
0
        public Person CreateSQLQueryFirst()
        {
            using IStatelessSession session = sessionFactory.OpenStatelessSession();

            return(session.CreateSQLQuery("select * from Person where Id = :id")
                   .SetInt32("id", CurrentId)
                   .SetResultTransformer(Transformers.AliasToBean <Person>())
                   .List <Person>()[0]);
        }
 public ISQLQuery CreateSQLQuery(string query)
 {
     if (_isStateless)
     {
         return(_wrapStateless.CreateSQLQuery(query));
     }
     else
     {
         return(_wrap.CreateSQLQuery(query));
     }
 }
Ejemplo n.º 14
0
        public static void CalcJunk(IStatelessSession session, Settings settings)
        {
            session.CreateSQLQuery(@"
update Offers
set Junk = OriginalJunk or (Exp is not null and Exp < :end);

update OrderLines
set Junk = OriginalJunk or (Exp is not null and Exp < :end);")
            .SetParameter("end", DateTime.Now.AddMonths(settings.JunkPeriod))
            .ExecuteUpdate();
        }
Ejemplo n.º 15
0
 private void UpdateWaybills(BaseScreen context, IList <Waybill> waybills, bool newState, IStatelessSession session)
 {
     foreach (var waybill in waybills)
     {
         waybill.IsNew = newState;
         session.CreateSQLQuery("update waybills set IsNew = :isNew where Id = :id")
         .SetParameter("isNew", newState ? 1 : 0)
         .SetParameter("id", waybill.Id)
         .ExecuteUpdate();
     }
     context.Env.RxQuery(x => x.Query <Waybill>().Count(r => r.IsNew)).Subscribe(context.Shell.NewDocsCount);
 }
Ejemplo n.º 16
0
        public static IList <Offer> QueryByFullText(IStatelessSession statelessSession, string term)
        {
            return(statelessSession
                   .CreateSQLQuery(@"
select {o.*}, {p.*}, match (productsynonym) against (:term in natural language mode)
from Offers o
	join Prices p on p.PriceId = o.PriceId and p.RegionId = o.RegionId
where match (ProductSynonym) against (:term in natural language mode)")
                   .AddEntity("o", typeof(Offer))
                   .AddJoin("p", "o.Price")
                   .SetParameter("term", term)
                   .List <Offer>());
        }
Ejemplo n.º 17
0
        public override List <Documento> GetEntities(IStatelessSession session, int startRow, int endRow)
        {
            const string Sql = @"
select
    ROW_NUMBER() over (order by predoc_code) as RowNum, 
    predoc_code Code, 
    groupdoc_code GroupDocCode, 
    predoc_usr Usr, 
    predoc_date Date
from 
    predoc
where 
	predoc_processed = 'N'"    ;

            var documentos = new List <Documento>();

            var dtos = session
                       .CreateSQLQuery(this.GetPaginatedSql(Sql, startRow, endRow))
                       .SetResultTransformer(CustomResultTransformer <PreIndexedFileDto> .Do())
                       .List <PreIndexedFileDto>();

            foreach (var preIndexedFileDto in dtos)
            {
                Log.App.DebugFormat("Importando PreIndexedFileDto #{0}", preIndexedFileDto.Code);

                var documento = new Documento();

                documento.Id          = preIndexedFileDto.Code.ToInt();
                documento.DataCriacao = preIndexedFileDto.Date;

                if (string.IsNullOrEmpty(preIndexedFileDto.Usr) == false)
                {
                    documento.Usuario = this.Create <Usuario>(preIndexedFileDto.Usr);
                }
                else
                {
                    documento.Usuario = this.Create <Usuario>("1");
                }

                documento.TipoDocumento  = this.Create <TipoDocumento>(preIndexedFileDto.GroupDocCode);
                documento.SearchStatus   = SearchStatus.DontIndex;
                documento.EhPreIndexacao = true;
                documento.Assunto        = "(sem assunto)";

                documentos.Add(documento);
            }

            return(documentos);
        }
        /// <summary>
        /// SQLs the query.
        /// </summary>
        /// <typeparam name="TParameters">The type of the t parameters.</typeparam>
        /// <typeparam name="TReturn">The type of the t return.</typeparam>
        /// <param name="session">The session.</param>
        /// <param name="procedure">The procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>ISQLQuery.</returns>
        private static ISQLQuery SqlQueryNoReturn <TParameters>(IStatelessSession session, string procedure, TParameters parameters)
            where TParameters : class
        {
            var properties = parameters.GetType().GetProperties();

            string[] parameterNames          = properties.Select(s => $":{s.Name}").ToArray();
            string   procedureWithParameters = $"{procedure} {string.Join(",", parameterNames)}";

            var query = session.CreateSQLQuery(procedureWithParameters);

            foreach (var property in properties)
            {
                query.SetParameter(property.Name, property.GetValue(parameters));
            }
            return(query);
        }
Ejemplo n.º 19
0
        public async Task <IEnumerable <DocumentFeedDto> > GetAsync(SimilarDocumentsQuery query, CancellationToken token)
        {
            const string sql = @"with cte as
                                (
                                select d.UniversityId, CourseName, u.Country
                                from sb.Document d 
                                join sb.[user] u
	                                on d.UserId = u.Id
                                where d.Id = :Id
                                )

                                select top 10 d.Id, d.UpdateTime as DateTime, d.CourseName as Course, d.Name as Title,  
                                un.Name as University, 
                                coalesce(d.Description, d.MetaContent) as Snippet, d.Views, d.Downloads, d.Price, 
                                d.DocumentType, d.Duration,  
                                (
	                                select count(1)
	                                from sb.[Transaction] t 
	                                where t.TransactionType='Document' and 
	                                t.DocumentId=d.Id
	                                and t.[Action]='SoldDocument'
                                ) as Purchased, 
                                d.VoteCount as [Vote.Votes],
                                u.Id as [User.Id], u.Name as [User.Name], 
                                u.ImageName as [User.Image]
                                from sb.[Document] d 
                                inner join sb.[User] u on d.UserId=u.Id 
                                left outer join sb.[University] un on d.UniversityId=un.Id,
                                cte
                                where d.CourseName = cte.CourseName and u.Country = cte.Country
                                and d.Id != :Id and d.[State]='ok'
                                order by case when d.UniversityId=cte.UniversityId
                                then 1 else 0 end desc, d.DocumentType desc, d.UpdateTime desc;
                                ";

            var res = await _session.CreateSQLQuery(sql)
                      .SetInt64("Id", query.DocumentId)
                      .SetResultTransformer(new DeepTransformer <DocumentFeedDto>('.', new SbAliasToBeanResultTransformer <DocumentFeedDto>()))
                      .ListAsync <DocumentFeedDto>(token);

            return(res.Select(s =>
            {
                s.User.Image = _urlBuilder.BuildUserImageEndpoint(s.User.Id, s.User.Image);
                s.Preview = _urlBuilder.BuildDocumentThumbnailEndpoint(s.Id);
                return s;
            }));
        }
Ejemplo n.º 20
0
        protected void FinishConstruct(bool createNew, IPersistenceConfigurer configurer)
        {
            m_configurer = configurer;
            var fluentConfig = Fluently.Configure().Database(configurer)
                               .Mappings(m => m.FluentMappings.AddFromAssemblyOf <DataEngineBase>());

            m_config           = fluentConfig.BuildConfiguration();
            m_sessionFactory   = m_config.BuildSessionFactory();
            m_statelessSession = OpenStatelessSession();

            if (createNew)
            {
                PreCreateSchema();
                var export = new SchemaExport(m_config);
                export.Execute(true, true, false, Connection, Console.Out);

                WriteCoreProperties();
                //create an entry for the first snapshot in the db
                var firstSnapshot = m_statelessSession.CreateSQLQuery("INSERT INTO Snapshots (Id, Name, DateTime) VALUES (:id, :name, :datetime)")
                                    .SetInt32("id", 0)
                                    .SetString("name", "Current")
                                    .SetInt64("datetime", DateTime.Now.ToFileTime());
                firstSnapshot.ExecuteUpdate();
            }
            else
            {
                using (var session = OpenSession())
                    using (var tx = session.BeginTransaction())
                    {
                        var applicationProp = session.Get <Property>("Application");
                        if (applicationProp == null || applicationProp.Value != System.Windows.Forms.Application.ProductName)
                        {
                            throw new System.IO.InvalidDataException("Wrong or missing application name.");
                        }

                        var versionProp = session.Get <Property>("FileVersion");
                        if (versionProp == null || int.Parse(versionProp.Value) != 3)
                        {
                            throw new System.IO.InvalidDataException("Wrong file version.");
                        }

                        tx.Commit();
                    }
            }

            PrepareCommands();
        }
Ejemplo n.º 21
0
        public static IList <object[]> StatelessGetBySql(string sql)
        {
            CheckSqlInjection(sql);
            IStatelessSession session = GetStatelessSession();

            try{
                return(session.CreateSQLQuery(sql).List <object[]>());
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                session.Close();
            }
        }
Ejemplo n.º 22
0
        public static object StatelessGetScalarBySql(string sql)
        {
            CheckSqlInjection(sql);
            IStatelessSession session = GetStatelessSession();

            try{
                return(session.CreateSQLQuery(sql).UniqueResult());
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                session.Close();
            }
        }
Ejemplo n.º 23
0
        public List <DefectusLine> LoadItems(IStatelessSession session)
        {
            if (Address == null)
            {
                return(new List <DefectusLine>());
            }
            // возвращает остатки по текущему адресу
            return(session.CreateSQLQuery(@"
select IFNULL(SUM(s.Quantity), 0) as {d.Quantity}, {d.*}
from defectuslines d
left outer join stocks s on s.ProductId = d.ProductId and s.AddressId = :address
group by d.ProductId
order by d.Product")
                   .AddEntity("d", typeof(DefectusLine))
                   .SetParameter("address", Address.Id)
                   .List <DefectusLine>()
                   .ToList());
        }
Ejemplo n.º 24
0
        public static void StatelessExecuteSql(string sql)
        {
            CheckSqlInjection(sql, true);
            IStatelessSession session = GetStatelessSession();

            try
            {
                session.CreateSQLQuery(sql).ExecuteUpdate();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                session.Close();
            }
        }
Ejemplo n.º 25
0
            public async Task <IEnumerable <AccountQuestionDto> > GetAsync(AccountQuestionsQuery query, CancellationToken token)
            {
                const string sql = @"with cte as (
                                select top 1 * from (select 1 as o, u2.Id as UniversityId, COALESCE(u2.country,u.country) as Country, u.id as userid
                                 from sb.[user] u
                                 left join sb.University u2 on u.UniversityId2 = u2.Id
                                 where u.id = :userid
                                 union
                                 select 2,null,:country,0) t
                                 order by o
                                )

                                select top 50 q.Id,
				                                q.Text,
				                                q.Updated as [DateTime],
				                                u.Id as [User.Id],
				                                u.Name as [User.Name],
				                                u.ImageName as [User.Image]
                                from sb.Question q
                                join sb.[user] u
	                                on u.Id = q.UserId
                                ,cte
                                where not exists (select Id from sb.Answer where QuestionId = q.Id and State = 'Ok' and UserId = :userid) 
                                and q.Updated > GETUTCDATE() - 182
                                and q.State = 'Ok'
                                and q.userId != :userid
                                and cte.country = u.country
                                order by
                                case when q.CourseId in (select courseId from sb.usersCourses where userid = cte.userid) then 4 else 0 end +
                                case when q.UniversityId = cte.UniversityId then 3 else 0 end  +
                                cast(1 as float)/ISNULL(nullif( DATEDIFF(minute, q.Updated, GETUTCDATE()   ),0),1) desc";

                var res = await _session.CreateSQLQuery(sql)
                          .SetInt64("userid", query.Id)
                          .SetString("country", query.Country)
                          .SetResultTransformer(new DeepTransformer <AccountQuestionDto>('.', new SbAliasToBeanResultTransformer <AccountQuestionDto>()))
                          .ListAsync <AccountQuestionDto>(token);

                return(res.Select(s =>
                {
                    s.User.Image = _urlBuilder.BuildUserImageEndpoint(s.User.Id, s.User.Image);
                    return s;
                }));
            }
Ejemplo n.º 26
0
        /// <summary>
        /// Flushes the queue of stored attribute updates and commits the changes to the database
        /// </summary>
        private void CommitCurrentAttributeUpdates()
        {
            if (GlobalSession.IsOpen)
            {
                GlobalSession.Close();
            }

            using (IStatelessSession session = SessionFactory.OpenStatelessSession())
            {
                var transaction = session.BeginTransaction();
                foreach (KeyValuePair <Guid, Dictionary <string, object> > componentUpdate in ComponentAttributesToPersist)
                {
                    Guid componentGuid = componentUpdate.Key;
                    foreach (KeyValuePair <string, object> attributeUpdate in componentUpdate.Value)
                    {
                        String updateQuery = "update attributes_to_components set value = :newValue where componentID = :componentGuid AND attributeName = :attributeName";
                        IQuery sqlQuery    = session.CreateSQLQuery(updateQuery)
                                             .SetBinary("newValue", ObjectToByteArray(attributeUpdate.Value))
                                             .SetParameter("componentGuid", componentGuid)
                                             .SetParameter("attributeName", attributeUpdate.Key);
                        sqlQuery.ExecuteUpdate();
                    }
                }
                try
                {
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    logger.WarnException("Failed to update Attribute", e);
                    transaction.Rollback();
                }
                finally
                {
                    session.Close();
                }
            }
        }
Ejemplo n.º 27
0
        public static IList <object[]> StatelessGetBySql(string sql, IDictionary <string, Object> namedParameters)
        {
            CheckSqlInjection(sql);
            IStatelessSession session = GetStatelessSession();

            try{
                var q = session.CreateSQLQuery(sql);
                foreach (var namedParameter in namedParameters)
                {
                    q.SetParameter(namedParameter.Key, namedParameter.Value);
                }
                var l = q.List <object[]>();
                return(l);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                session.Close();
            }
        }
Ejemplo n.º 28
0
        public static void StatelessExecuteSql(String sql, IDictionary <string, Object> namedParameters)
        {
            CheckSqlInjection(sql, true);
            IStatelessSession session = GetStatelessSession();

            try
            {
                var q = session.CreateSQLQuery(sql);
                foreach (var namedParameter in namedParameters)
                {
                    q.SetParameter(namedParameter.Key, namedParameter.Value);
                }
                q.ExecuteUpdate();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                session.Close();
            }
        }
Ejemplo n.º 29
0
        public T ExecuteDynamic <T>(string queryString, Dictionary <string, object> parameters = null)
        {
            if (parameters != null && !Contract.ForAll(parameters, (KeyValuePair <string, object> p) => p.Value != null))
            {
                throw new ArgumentException("The parameter array has a null element", "parameters");
            }

            T result = default(T);
            IStatelessSession session = this.Session;

            try
            {
                //session.BeginTransaction();
                IQuery query = session.CreateSQLQuery(queryString);
                if (parameters != null)
                {
                    foreach (var item in parameters)
                    {
                        query.SetParameter(item.Key, item.Value);
                    }
                }
                //query.executeUpdate(); // ??
                result = query.UniqueResult <T>();
                //session.Transaction.Commit();
            }
            catch (Exception ex)
            {
                //session.Transaction.Rollback();
                throw new Exception(string.Format($"Failed for execute the submitted native query.  Reason: '{ex.Message}'"));
            }
            finally
            {
                // Do Nothing
            }
            return(result);
        }
Ejemplo n.º 30
0
        private List <WaybillLine> LoadMatchedWaybill(SentOrderLine line, IStatelessSession session)
        {
            if (line == null)
            {
                return(new List <WaybillLine>());
            }
            var ids = session
                      .CreateSQLQuery("select DocumentLineId from WaybillOrders where OrderLineId = :orderLineId")
                      .SetParameter("orderLineId", line.ServerId)
                      .List()
                      .Cast <object>()
                      .Select(Convert.ToUInt32)
                      .ToList();

            if (ids.Count == 0)
            {
                return(new List <WaybillLine>());
            }
            var lines = session.Query <WaybillLine>()
                        .Where(l => ids.Contains(l.Id))
                        .Fetch(l => l.Waybill)
                        .ThenFetch(l => l.Supplier)
                        .Fetch(l => l.Waybill)
                        .ThenFetchMany(w => w.Lines)
                        .ToList();

            if (lines.Count > 0)
            {
                var result = lines[0].Waybill.Lines.OrderBy(l => l.Product).ToList();
                //будь бдителен - хотя с точки зрения бд lines[0] и result.First(l => l.Id == lines[0].Id) один и тот же объект
                //nhibernate интерпретирует их как два разных объекта и выделение строки в ui не будет работать
                CurrentWaybillLine.Value = result.First(l => l.Id == lines[0].Id);
                return(result);
            }
            return(lines);
        }
Ejemplo n.º 31
0
		protected void FinishConstruct(bool createNew, IPersistenceConfigurer configurer)
		{
			m_configurer = configurer;
			var fluentConfig = Fluently.Configure().Database(configurer)
				.Mappings(m => m.FluentMappings.AddFromAssemblyOf<DataEngineBase>());
			m_config = fluentConfig.BuildConfiguration();
			m_sessionFactory = m_config.BuildSessionFactory();
			m_statelessSession = OpenStatelessSession();

			if(createNew)
			{
				PreCreateSchema();
				var export = new SchemaExport(m_config);
				export.Execute(true, true, false, Connection, Console.Out);

				WriteCoreProperties();
				//create an entry for the first snapshot in the db
				var firstSnapshot = m_statelessSession.CreateSQLQuery("INSERT INTO Snapshots (Id, Name, DateTime) VALUES (:id, :name, :datetime)")
					.SetInt32("id", 0)
					.SetString("name", "Current")
					.SetInt64("datetime", DateTime.Now.ToFileTime());
				firstSnapshot.ExecuteUpdate();
			}
			else
			{
				using(var session = OpenSession())
				using(var tx = session.BeginTransaction())
				{
					var applicationProp = session.Get<Property>("Application");
					if(applicationProp == null || applicationProp.Value != System.Windows.Forms.Application.ProductName)
					{
						throw new System.IO.InvalidDataException("Wrong or missing application name.");
					}

					var versionProp = session.Get<Property>("FileVersion");
					if(versionProp == null || int.Parse(versionProp.Value) != 3)
					{
						throw new System.IO.InvalidDataException("Wrong file version.");
					}

					tx.Commit();
				}
			}

			PrepareCommands();
		}