TSQL – Consultar e alterar LogicalName de uma base de dados

/*CODIGO PARA CONSULTAR*/
USE master
GO
/* Identify Database File Names */
SELECT name AS [Logical Name], physical_name AS [DB File Path], type_desc AS [File Type], state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N’newDataBaseName’)

/*CODIGO PARA ALTERAR*/
/* Set Database as a Single User */
ALTER DATABASE newDataBaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* Change Logical File Name */
ALTER DATABASE newDataBaseName MODIFY FILE (NAME=N’oldDataBaseName’, NEWNAME=N’newDataBaseName’)
GO
ALTER DATABASE newDataBaseName MODIFY FILE (NAME=N’oldDataBaseName_log’, NEWNAME=N’newDataBaseName_log’)
GO
ALTER DATABASE newDataBaseName SET MULTI_USER
GO

Estes códigos devem ser utilizados quando for necessário criar uma nova base a partir de uma já existente, como o caso acima, foi necessário criar a base newDataBaseName, e utilizamos um backup da base oldDataBaseName como base, então como criamos uma nova base e restauramos o backup de uma outra base devemos verificar a questão do logical name, que em geral o que fica é o nome da base restaurada e não da nova base

SQL SERVER – Aumentar tamanho do banco

USE master;

DECLARE @DB_NAME VARCHAR(MAX) = 'myDatabase' /*NOME DO BANCO*/
DECLARE @SIZE VARCHAR(MAX) = '4000' /*EM MB*/

/* SET DATABASE AS A SINGLE USER */
EXEC('ALTER DATABASE ' + @DB_NAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
/* SET NEW SIZE */
EXEC(' ALTER DATABASE ' + @DB_NAME + ' MODIFY FILE (NAME = '+@DB_NAME+', SIZE = '+@SIZE+'MB);')
/* SET DATABASE AS A MULTI USER */
EXEC ( 'ALTER DATABASE '+ @DB_NAME + ' SET MULTI_USER ')

TSQL – Restaurar base de dados através do código

Bom, estava precisando agilizar a minha vida por aqui dai surgiu o script abaixo, neste script o caminho do diretório dever ser informado no formato abreviado. Para conseguir o nome abreviado do diretório basta abrir o Command, ir até o diretório que contem a pasta desejada e utilizar o comando “dir /x” este comando irá retornar o nome do diretório abreviado:

06/03/2012 11:09 PROGRA~1 Program Files
06/03/2012 11:06 PROGRA~2 Program Files (x86)

DECLARE @PATH_BAK VARCHAR(8000)
DECLARE @PATH_DB VARCHAR(8000)
DECLARE @DB_NAME VARCHAR(8000)

SET @PATH_BAK = N'C:\TEMP\arquivo.bak'
SET @PATH_DB = N'C:\BancoDeDados\'
SET @DB_NAME = 'nomeDaBase'

DECLARE @LogicalName VARCHAR(8000)
DECLARE @LogicalName_log VARCHAR(8000)
DECLARE @PhysicalName VARCHAR(8000)
DECLARE @PhysicalName_log VARCHAR(8000)
DECLARE @TMP TABLE (
      LogicalName NVARCHAR(128) , PhysicalName NVARCHAR(260) , [Type] CHAR(1) , FileGroupName NVARCHAR(128) , Size NUMERIC(20, 0) ,
      MaxSize NUMERIC(20, 0) , FileID BIGINT , CreateLSN NUMERIC(25, 0) , DropLSN NUMERIC(25, 0) , UniqueID UNIQUEIDENTIFIER ,
      ReadOnlyLSN NUMERIC(25, 0) , ReadWriteLSN NUMERIC(25, 0) , BackupSizeInBytes BIGINT , SourceBlockSize INT , FileGroupID INT ,
      LogGroupGUID UNIQUEIDENTIFIER , DifferentialBaseLSN NUMERIC(25, 0) , DifferentialBaseGUID UNIQUEIDENTIFIER ,
      IsReadOnl BIT , IsPresent BIT , TDEThumbprint VARBINARY(32)
)
INSERT  INTO @TMP EXEC ( 'RESTORE FILELISTONLY FROM DISK = '''+@PATH_BAK+''' WITH FILE=1;' )

SET @LogicalName =  ( SELECT LogicalName FROM ( SELECT ROW_NUMBER() OVER (ORDER BY LogicalName ASC) AS rownumber,LogicalName FROM @TMP ) AS LogicalName WHERE rownumber = 1 )
SET @LogicalName_log = ( SELECT LogicalName FROM ( SELECT ROW_NUMBER() OVER (ORDER BY LogicalName ASC) AS rownumber,LogicalName FROM @TMP ) AS LogicalName WHERE rownumber = 2 )

SET @PhysicalName = @PATH_DB + @LogicalName + '.mdf'
SET @PhysicalName_log = @PATH_DB + @LogicalName_log + '.ldf'

EXEC(' ALTER DATABASE '+@DB_NAME+'  SET SINGLE_USER WITH ROLLBACK IMMEDIATE ')
EXEC('RESTORE DATABASE '+@DB_NAME+' FROM DISK = '''+@PATH_BAK+''' WITH REPLACE, MOVE '''+@LogicalName+''' TO '''+@PhysicalName+''', MOVE '''+@LogicalName_log+''' TO '''+@PhysicalName_log+''' ')
EXEC(' ALTER DATABASE '+@DB_NAME+'  SET MULTI_USER ')

TSQL – IF com BEGIN/END

Hoje pela manhã descobri que no TSQL assim como no C# deve-se utilizar BEGIN/END para blocos de código que tenham mais de uma linha.

ASP.NET MVC 3 com FluentNHibernate + Ninject

Estou com um novo projeto, e acabei chegando nesta proposta de arquitetura… gostaria que opniassem…

GenericDAO == Base para as demais DAO´s

DaoFacade   == Concentra todos objetos DAO´s para expor para a aplicação MVC, assim não preciso ficar dando using em cada DAO, muito menos ficar injetando as DAO´s

NHibernateHelper == Expõe as operações do NHibernate para o ActionFilter TransactionPerRequest, assim minha aplicação ASP.NET não precisa conhecer nada de NHibernate…

TransactionPerRequest == Ainda tenho duvidas se todas as operações precisam abrir uma Transaction, estou achando que estou fazendo uma redundancia dentro da GenericDAO, ainda estou debugando para confirmar (se alguem puder opniar deixa um comment),  mas pelo menos está garantindo que a ISession seja Closed a cada final de Request, e também posso indicar as Actions que usam, também só injeto a DaoFacade na Action que vai utilizar…

GenericDAO

using System;
using System.Collections.Generic;
using NHibernate;
using NHibernate.Criterion;
using Portal.Domain.Infrastructure;

namespace Portal.Domain.Core
{
    public abstract class GenericDAO<T, IdT> : IDao<T, IdT>
    {
        private Type persitentType = typeof(T);

        protected ISession NHibernateSession
        {
            get { return NHibernateHelper.GetCurrentSession(); }
        }

        /// <summary>
        /// Loads an instance of type TypeOfListItem from the DB based on its ID.
        /// </summary>
        public T GetById(IdT id)
        {
            T entity;
            entity = (T)NHibernateSession.Load(persitentType, id);
            return entity;
        }

        /// <summary>
        /// Save a new Entity
        /// </summary>
        public T Save(T entity)
        {
            using (ITransaction transaction = NHibernateSession.BeginTransaction())
            {
                try
                {
                    NHibernateSession.Save(entity);
                    transaction.Commit();
                    NHibernateSession.Flush();
                }
                catch (HibernateException ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
            }
            return entity;
        }

        /// <summary>
        /// Save an Entity that don't exists, or update an existing Entity
        /// </summary>
        public T SaveOrUpdate(T entity)
        {
            using (ITransaction transaction = NHibernateSession.BeginTransaction())
            {
                try
                {
                    NHibernateSession.SaveOrUpdate(entity);
                    transaction.Commit();
                    NHibernateSession.Flush();
                }
                catch (HibernateException)
                {
                    transaction.Rollback();
                    throw new HibernateException();
                }
            }
            return entity;
        }

        /// <summary>
        /// Update an existing Entity
        /// </summary>
        public T Update(T entity)
        {
            using (ITransaction transaction = NHibernateSession.BeginTransaction())
            {
                try
                {
                    NHibernateSession.Update(entity);
                    transaction.Commit();
                    NHibernateSession.Flush();
                }
                catch (HibernateException)
                {
                    transaction.Rollback();
                    throw new HibernateException();
                }
            }
            return entity;
        }

        /// <summary>
        /// Delete an existing Entity
        /// </summary>
        public void Delete(T entity)
        {
            using (ITransaction transaction = NHibernateSession.BeginTransaction())
            {
                try
                {
                    NHibernateSession.Delete(entity);
                    transaction.Commit();
                    NHibernateSession.Flush();
                }
                catch (HibernateException)
                {
                    transaction.Rollback();
                    throw new HibernateException();
                }
            }
        }

        /// <summary>
        /// Delete an List of existing Entities
        /// </summary>
        public void DeleteAll(IList<T> itemsToDelete)
        {
            foreach (T item in itemsToDelete)
            {
                try
                {

                    using (ITransaction transaction = NHibernateSession.BeginTransaction())
                    {
                        NHibernateSession.Delete(item);
                        transaction.Commit();
                        NHibernateSession.Flush();
                    }
                }
                catch (HibernateException)
                {
                    NHibernateSession.Transaction.Rollback();
                }
            }

        }
    }
}

NHibernateHelper

using NHibernate;
using NHibernate.Context;
using FluentNHibernate.Cfg;
using System.Reflection;

namespace Portal.Domain.Infrastructure
{
    public class NHibernateHelper
    {
        private static ISessionFactory sessionFactory;

        /// <summary>
        /// SessionFactory is static because it is expensive to create and is therefore at application scope.
        /// The property exists to provide 'instantiate on first use' behaviour.
        /// </summary>
        private static ISessionFactory SessionFactory
        {
            get
            {
                if (sessionFactory == null)
                {
                    var cfg = new NHibernate.Cfg.Configuration();
                    cfg.Configure();
                    sessionFactory = Fluently.Configure(cfg)
                        .Mappings(x => x.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly()))
                        .BuildSessionFactory();
                }
                return sessionFactory;
            }
        }

        public static ISession GetCurrentSession()
        {
            if (!CurrentSessionContext.HasBind(SessionFactory))
            {
                CurrentSessionContext.Bind(SessionFactory.OpenSession());
            }
            return SessionFactory.GetCurrentSession();
        }

        public static void DisposeSession()
        {
            var session = GetCurrentSession();
            session.Close();
            session.Dispose();
        }

        public static void BeginTransaction()
        {
            GetCurrentSession().BeginTransaction();
        }

        public static void CommitTransaction()
        {
            var session = GetCurrentSession();
            if (session.Transaction.IsActive)
            {
                session.Transaction.Commit();
            }
        }

        public static void RollbackTransaction()
        {
            var session = GetCurrentSession();
            if (session.Transaction.IsActive)
            {
                session.Transaction.Rollback();
            }
        }
    }
}

DaoFacede

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Portal.Domain.Core;
using Portal.Domain.DAO;

namespace Portal.Domain.Infrastructure
{
    public class DaoFacede
    {
        private CategoriaDAO categoriaDAO;
        public CategoriaDAO CategoriaDAO
        {
            get
            {
                if (categoriaDAO == null)
                {
                    categoriaDAO = new CategoriaDAO();
                }
                return categoriaDAO;
            }
        }
    }
}

NinjectMVC3

[assembly: WebActivator.PreApplicationStartMethod(typeof(Portal.Site.App_Start.NinjectMVC3), "Start")]
[assembly: WebActivator.ApplicationShutdownMethodAttribute(typeof(Portal.Site.App_Start.NinjectMVC3), "Stop")]

namespace Portal.Site.App_Start
{
    using System.Reflection;
    using Microsoft.Web.Infrastructure.DynamicModuleHelper;
    using Ninject;
    using Ninject.Web.Mvc;
    using Portal.Domain.Infrastructure;

    public static class NinjectMVC3
    {
        private static readonly Bootstrapper bootstrapper = new Bootstrapper();

        /// <summary>
        /// Starts the application
        /// </summary>
        public static void Start()
        {
            DynamicModuleUtility.RegisterModule(typeof(OnePerRequestModule));
            DynamicModuleUtility.RegisterModule(typeof(HttpApplicationInitializationModule));
            bootstrapper.Initialize(CreateKernel);
        }

        /// <summary>
        /// Stops the application.
        /// </summary>
        public static void Stop()
        {
            bootstrapper.ShutDown();
        }

        /// <summary>
        /// Creates the kernel that will manage your application.
        /// </summary>
        /// <returns>The created kernel.</returns>
        private static IKernel CreateKernel()
        {
            var kernel = new StandardKernel();
            RegisterServices(kernel);
            return kernel;
        }

        /// <summary>
        /// Load your modules or register your services here!
        /// </summary>
        /// <param name="kernel">The kernel.</param>
        private static void RegisterServices(IKernel kernel)
        {
            kernel.Bind<DaoFacede>().ToSelf();
        }
    }
}

TransactionPerRequest

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Portal.Domain.Infrastructure;

namespace Portal.Site
{
    /// <summary>
    /// Use this filter for actions that need to persist state.
    /// </summary>
    public class TransactionPerRequest : ActionFilterAttribute
    {
        public override void OnActionExecuting(ActionExecutingContext filterContext)
        {
            NHibernateHelper.BeginTransaction();
        }

        public override void OnActionExecuted(ActionExecutedContext filterContext)
        {
            // use this method instead of OnResultExecuted if the transaction / session
            // does not need be open for the view rendering.
        }

        public override void OnResultExecuted(ResultExecutedContext filterContext)
        {
            if (filterContext.Exception == null)
            {
                NHibernateHelper.CommitTransaction();
            }
            NHibernateHelper.DisposeSession();
        }
    }
}

HomeController

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Portal.Domain.Infrastructure;
using Portal.Domain;

namespace Portal.Site.Controllers
{
    public class HomeController : Controller
    {

        public HomeController()
        {

        }

        [TransactionPerRequest]
        public ActionResult Index(DaoFacede daoFacede)
        {
            Categoria objAtual = daoFacede.CategoriaDAO.GetById(1);
            if (objAtual != null)
            {
                ViewBag.Teste = objAtual.NomeController;
            }

            return View();
        }

    }
}