C# ISession.CreateSQLQuery方法代码示例

        protected override void Run(ISession session)
        {
            var indicadoresHabilitados = session.QueryOver<Indicador>()
                    .Where(x => x.Habilitado)
                    .Fetch(x => x.Objetivos)
                    .Eager.List();
            indicadoresHabilitados.AsParallel().ForAll(indicador =>
                {
                    try
                    {
                        if (Log.IsInfoEnabled) Log.InfoFormat("Calculando el indicador '{0}) {1}'", indicador.Id, indicador.Nombre);
                        var fecha = DateTime.Now;

                        var denominadorQuery = session.CreateSQLQuery(indicador.DenominadorSql);
                        if(indicador.DenominadorSql.Contains(":Mes")) denominadorQuery.SetParameter("Mes", fecha.Month);
                        if(indicador.DenominadorSql.Contains(":Anio")) denominadorQuery.SetParameter("Anio", fecha.Year);
                        var denominador = denominadorQuery.UniqueResult<int>();

                        if (Log.IsDebugEnabled) Log.DebugFormat("Calculando denominador para el indicador '{0}) {1}': {2}", indicador.Id, indicador.Nombre, denominador);

                        var numeradorQuery = session.CreateSQLQuery(indicador.NumeradorSql);
                        if(indicador.NumeradorSql.Contains(":Mes")) numeradorQuery.SetParameter("Mes", fecha.Month);
                        if(indicador.NumeradorSql.Contains(":Anio")) numeradorQuery.SetParameter("Anio", fecha.Year);
                        var numerador = numeradorQuery.UniqueResult<int>();

                        if (Log.IsDebugEnabled) Log.DebugFormat("Calculando numerador para el indicador '{0}) {1}': {2}", indicador.Id, indicador.Nombre, numerador);

                        var valor = Convert.ToDecimal(numerador / denominador);

                        var objetivo = indicador.Objetivos.SingleOrDefault(o => o.Anio == fecha.Year && o.Mes == fecha.Month);

                        if (objetivo == null)
                        {
                            objetivo = new ObjetivoIndicador
                            {
                                Anio = fecha.Year,
                                Mes = fecha.Month,
                                ValorMaximo = valor,
                                ValorMinimo = valor,
                                Indicador = indicador
                            };
                            indicador.AgregarObjetivo(objetivo);
                        }
                        objetivo.FechaLectura = fecha;
                        objetivo.Valor = valor;

                        session.Save(objetivo);

                        if (Log.IsInfoEnabled) Log.InfoFormat("Indicador '{0}) {1}' calculado con valor: {2}", indicador.Id, indicador.Nombre, valor);
                    }
                    catch (Exception ex)
                    {
                        Log.ErrorFormat("Hubo un error al calcular el indicador '{0}) {1}'. Exc: {2}", indicador.Id, indicador.Nombre, ex);
                    }
                });
        }

        public static double calcularRendaPorAnoVendedor(int idVendedor, int ano, ISession _session)
        {
            string query = "select sum(vv.valor)  from venda v inner join vendavendedor vv on vv.IdVenda = v.Idvenda where vv.IdVendedor = :idVendedor and year(v.dataVenda)= :ano";

            double valorComissao = _session.CreateSQLQuery(query).SetParameter("idVendedor", idVendedor)
                    .SetParameter("ano", ano).UniqueResult<double>();

            return valorComissao;
        }

 protected override ISQLQuery SqlAsBinaryLineString(ISession session)
 {
     return session.CreateSQLQuery(@"
     SELECT ST_AsBinary(the_geom) as result
     FROM linestringtest
     WHERE oid = ?
     AND the_geom IS NOT NULL
     ")
     .AddScalar("result", NHibernateUtil.BinaryBlob);
 }

 protected override ISQLQuery SqlIsSimpleLineString(ISession session)
 {
     return session.CreateSQLQuery(@"
     SELECT ST_IsSimple(the_geom) as result
     FROM linestringtest
     WHERE oid = ?
     AND the_geom IS NOT NULL
     ")
         .AddScalar("result", NHibernateUtil.Boolean);
 }

        public double TotalVendasAnualPorVendedor(int ano, int idVendedor, ISession _session)
        {
            string query = "select sum(vv.valor)  from venda v inner join vendavendedor vv on vv.IdVenda = v.Idvenda where vv.IdVendedor = :idVendedor and year(v.dataVenda)= :ano";

            double valorVendas = _session.CreateSQLQuery(query)
                .SetParameter("idVendedor", idVendedor)
                    .SetParameter("ano", ano).UniqueResult<double>();

            return valorVendas;
        }

        private static void ImportDirects(ISession session, int id)
        {
            var q = session.CreateSQLQuery(
                "insert into [transaction] (date, amount, comment, currency_id, source_id) values(:date, :amount, :comment, :currency, :source)");
            var qq = session.CreateSQLQuery("insert into transactiontargets (transaction_id, bro_id) values(:transaction, :bro)");
            q.SetParameter("currency", 1);
            foreach (string[] transaction in directs)
            {
                q.SetParameter("amount", transaction[3]);
                q.SetParameter("comment", transaction[4]);
                q.SetParameter("source", broMap[int.Parse(transaction[2])]);
                q.SetParameter("date", transaction[5] == "0000-00-00" ? "2013-01-01 00:00:00" : transaction[5] + " 00:00:00");
                q.ExecuteUpdate();

                qq.SetParameter("transaction", id);
                qq.SetParameter("bro", broMap[int.Parse(transaction[1])]);
                qq.ExecuteUpdate();
                id++;
            }
        }

        /// <summary>
        /// Método para establecer el identificador del usuario en el contexto de sesión para el
        /// repositorio de datos MSSQLServer 2005 o superior
        /// </summary>
        /// <param name="session">Objeto de sesión a la base de datos</param>
        public static void SetMssqlContext(ISession session)
        {
            string cuenta = GetUserName();
            string app = HttpContext.Current == null ? "DOTNET":"ASPNET";

            string str = string.Format("DECLARE @BinVar varbinary (128)" +
                                       " SET @BinVar = CAST(N'{0}&{1}|' AS varbinary(128) ) " +
                                       " SET CONTEXT_INFO @BinVar ", app, cuenta);


            session.CreateSQLQuery(str).ExecuteUpdate();
        }

        public void RunSyndication(ISession session, ManagedSecurityContext sec)
        {
            IQuery query = session.CreateSQLQuery(
                "SELECT {AccountFeed.*} FROM AccountFeed" +
                " WHERE (NOT EXISTS ( SELECT AccountFeedItem_Id FROM AccountFeedItem item WHERE item.AccountFeed_Id = AccountFeed.AccountFeed_Id ))" +
                " OR ( AccountFeed.LastError NOT LIKE '' )" +
                " OR ( DATEDIFF(hour, AccountFeed.Updated, getutcdate()) > AccountFeed.UpdateFrequency )" +
                " ORDER BY AccountFeed.Updated ASC")
            .AddEntity("AccountFeed", typeof(AccountFeed));

            IList<AccountFeed> list = query.List<AccountFeed>();

            foreach(AccountFeed feed in list)
            {
                if (IsStopping)
                    break;

                try
                {
                    ManagedAccountFeed m_feed = new ManagedAccountFeed(session, feed);
                    if (IsDebug)
                    {
                        EventLogManager.WriteEntry(string.Format("Syndication service updating {0} ({1}).",
                            feed.Name, feed.Id), EventLogEntryType.Information);
                    }
                    m_feed.Update(sec);
                    m_feed.UpdateImages(sec);
                    m_feed.UpdateMedias(sec);
                }
                catch (ThreadAbortException)
                {
                    throw;
                }
                catch (Exception ex)
                {
                    feed.LastError = ex.Message;
                    session.Save(feed);
                }

                session.Flush();
                Thread.Sleep(1000 * InterruptInterval);
            }
        }

        public void RollupExistingReferrerHosts(ISession session)
        {
            // find a host that is exactly the target
            ReferrerHost targethost = session.CreateCriteria(typeof(ReferrerHost))
                .Add(Expression.Eq("Name", mReferrerHostRollup.Rollup))
                .UniqueResult<ReferrerHost>();

            if (targethost == null)
            {
                targethost = new ReferrerHost();
                targethost.Name = mReferrerHostRollup.Rollup;
                targethost.RequestCount = 0;
                targethost.LastSource = targethost.LastUrl = "http://localhost/";
                targethost.Created = targethost.Updated = DateTime.UtcNow;
                session.Save(targethost);
            }

            IList<ReferrerHost> hosts = session.CreateSQLQuery(
                    "SELECT {R.*} FROM ReferrerHost {R}" +
                    " WHERE Name LIKE '" + Renderer.SqlEncode(mReferrerHostRollup.Name) + "'")
                    .AddEntity("R", typeof(ReferrerHost)).List<ReferrerHost>();

            foreach (ReferrerHost host in hosts)
            {
                if (host != targethost)
                {
                    targethost.LastSource = host.LastSource;
                    targethost.LastUrl = host.LastUrl;
                    targethost.RequestCount += host.RequestCount;
                    session.Delete(host);
                }
            }

            session.Save(targethost);
            session.Flush();
        }

 private static void ImportEvents(ISession session)
 {
     var q = session.CreateSQLQuery("insert into event (name, startdate, description) values(:name, :startdate, :description)");
     q.SetParameter("startdate", "2013-01-01 00:00:00");
     q.SetParameter("description", "");
     int id = 1;
     foreach (string[] e in events)
     {
         q.SetParameter("name", e[1]);
         q.ExecuteUpdate();
         eventMap[int.Parse(e[0])] = id;
         id++;
     }
 }

 private static void AddQuery(ISession session, string query)
 {
     session.CreateSQLQuery(query).ExecuteUpdate();
 }

        private void CreateHtmlReport(BackgroundWorker bg, string outFolder,
                                      IDictionary<string, TableExporter.ITable> enumerableTables,
                                      IDictionary<string, List<List<string>>> textTables,
                                      IDictionary<string, List<TableExporter.TableTreeNode>> treeTables,
                                      DataFilter viewFilter, DataFilter basicFilter, ISession session)
        {
            if (!Directory.Exists(outFolder))
                Directory.CreateDirectory(outFolder);
            var reportName = Path.GetFileName(outFolder);

            //generate resource files
            var css = Properties.Resources.idpicker_style;
            var jsFunctions = Properties.Resources.idpicker_scripts;
            var cssStream = new StreamWriter(Path.Combine(outFolder, "idpicker-style.css"));
            var jsStream = new StreamWriter(Path.Combine(outFolder, "idpicker-scripts.js"));
            cssStream.Write(css);
            cssStream.Flush();
            cssStream.Close();
            jsStream.Write(jsFunctions);
            jsStream.Flush();
            jsStream.Close();
            
            double progress = 5.0;
            bg.ReportProgress((int) Math.Round(progress), "creating tables");

            int totalTables = enumerableTables.Count + textTables.Count + treeTables.Count;
            double progressPerTable = 35.0 / totalTables;

            //generate html files
            foreach(var nameTablePair in enumerableTables)
            {
                var tableList = new List<TableExporter.ITable> { nameTablePair.Value };
                TableExporter.CreateHTMLTablePage(tableList,
                                                  Path.Combine(outFolder, String.Format("{0} {1}.html", reportName, nameTablePair.Key)),
                                                  String.Format("{0} - {1}", reportName, nameTablePair.Key),
                                                  true, false, false);
                progress += progressPerTable;
                bg.ReportProgress((int) Math.Round(progress), "creating tables");
            }

            foreach(var nameTablePair in textTables)
            {
                var tableList = new List<List<List<string>>> { nameTablePair.Value };
                TableExporter.CreateHTMLTablePage(tableList,
                                                  Path.Combine(outFolder, String.Format("{0} {1}.html", reportName, nameTablePair.Key)),
                                                  String.Format("{0} - {1}", reportName, nameTablePair.Key),
                                                  true, false, false);
                progress += progressPerTable;
                bg.ReportProgress((int) Math.Round(progress), "creating tables");
            }

            foreach (var nameTablePair in treeTables)
            {
                var tableList = new List<List<TableExporter.TableTreeNode>> { nameTablePair.Value };
                TableExporter.CreateHTMLTreePage(nameTablePair.Value, Path.Combine(outFolder, String.Format("{0} {1}.html", reportName, nameTablePair.Key)),
                                                 String.Format("{0} - {1}", reportName, nameTablePair.Key));
                progress += progressPerTable;
                bg.ReportProgress((int )Math.Round(progress), "creating tree tables");
            }

            var clusterList = new List<string[]>();
            if (session != null)
            {
                //try to pre-cache data if possible
                //report scales horribly with cluster count (655 clusters took 40 minutes)
                //limiting calls to the database should speed this up considerably
                var clusterIDList = session.CreateSQLQuery("select distinct cluster from protein").List().Cast<int>().ToList();
                var tempFilter = new DataFilter(viewFilter) { Cluster = new List<int>(), Protein = new List<Protein>() };
                var clusterToProteinList = new Dictionary<int, List<ProteinTableForm.ProteinGroupRow>>();
                var proteinAccessionToPeptideList = new Dictionary<string, HashSet<int>>();
                List<PeptideTableForm.DistinctPeptideRow> peptideList;

                progress = 50;
                bg.ReportProgress((int) Math.Round(progress), "precaching cluster data");

                double progressPerCluster = 25.0 / clusterIDList.Count;

                try
                {
                    //seperating proteinList namespace in order to try to conserve memory
                    {
                        var proteinList = ProteinTableForm.ProteinGroupRow.GetRows(session, tempFilter).ToList();
                        foreach (var protein in proteinList)
                        {
                            if (!clusterToProteinList.ContainsKey(protein.FirstProtein.Cluster))
                                clusterToProteinList.Add(protein.FirstProtein.Cluster,
                                                         new List<ProteinTableForm.ProteinGroupRow> { protein });
                            else
                                clusterToProteinList[protein.FirstProtein.Cluster].Add(protein);
                        }
                    }

                    peptideList = PeptideTableForm.DistinctPeptideRow.GetRows(session, tempFilter).ToList();
                    for (var x = 0; x < peptideList.Count; x++)
                    {
                        var peptide = peptideList[x].Peptide;
                        foreach (var instance in peptide.Instances)
                        {
                            var proteinAccession = instance.Protein.Accession;
                            if (!proteinAccessionToPeptideList.ContainsKey(proteinAccession))
//.........这里部分代码省略.........

 public static void DeleteResults(ISession session, DbPeptideFileAnalysis dbPeptideFileAnalysis)
 {
     dbPeptideFileAnalysis.PrecursorEnrichment = null;
     dbPeptideFileAnalysis.PrecursorEnrichmentFormula = null;
     dbPeptideFileAnalysis.TracerPercent = null;
     dbPeptideFileAnalysis.Turnover = null;
     dbPeptideFileAnalysis.DeconvolutionScore = null;
     dbPeptideFileAnalysis.IntegrationNote = null;
     session.CreateSQLQuery("DELETE FROM DbPeak WHERE PeptideFileAnalysis = :peptideFileAnalysisId")
         .SetParameter("peptideFileAnalysisId", dbPeptideFileAnalysis.Id)
         .ExecuteUpdate();
 }

        private void initSFRDImagem(ISession session, long frdbId)
        {
            IQuery sfrdiq = session.CreateQuery("select c from SFRDImagemEntity as c where c.FRDBase.Id = " + frdbId + " and (c.Tipo = 'Web' OR c.Tipo = 'Fedora') AND c.IsDeleted = 0 ");
            sfrdiq.SetTimeout(1000);
            this.NumImagens = sfrdiq.List().Count > 0 ? "sim" : "nao";

            IQuery ods = session.CreateSQLQuery(
                "SELECT img.IDFRDBase FROM SFRDImagem img " +
                "inner join SFRDImagemObjetoDigital imgOD on imgOD.idx = img.idx and imgOD.IDFRDBase = img.IDFRDBase and imgOD.isDeleted = 0 " +
                "inner join ObjetoDigital od on od.ID = imgOD.IDObjetoDigital and od.isDeleted = 0 and od.Publicado = 0 " +
                "WHERE img.isDeleted = 0 and img.IDFRDBase = " +
                frdbId
            );
            ods.SetTimeout(1000);

            this.NumODsNaoPublicados = ods.List<long>().ToList().Count > 0 ? "sim" : "nao";

            ods = session.CreateSQLQuery(
                "SELECT img.IDFRDBase FROM SFRDImagem img " +
                "inner join SFRDImagemObjetoDigital imgOD on imgOD.idx = img.idx and imgOD.IDFRDBase = img.IDFRDBase and imgOD.isDeleted = 0 " +
                "inner join ObjetoDigital od on od.ID = imgOD.IDObjetoDigital and od.isDeleted = 0 and od.Publicado = 1 " +
                "WHERE img.isDeleted = 0 and img.IDFRDBase = " +
                frdbId
            );
            ods.SetTimeout(1000);

            this.NumODsPublicados = ods.List<long>().ToList().Count > 0 ? "sim" : "nao";
        }

        public ISQLQuery ToSqlQuery(ISession session)
        {
            var query = session
                .CreateSQLQuery(ToString());

            foreach (var parameter in _parameters)
            {
                query.SetParameter(parameter.Key, parameter.Value);
            }

            return query;
        }