Fluent NHibernate and Mysql,SQLite

http://codeofrob.com/entries/sqlite-csharp-and-nhibernate.html

https://code.google.com/archive/p/csharp-sqlite/downloads

https://github.com/davybrion/NHibernateWorkshop

MySQL

sql:

#my sql test

DROP TABLE Department;

CREATE TABLE Department
(
	Id  INT  AUTO_INCREMENT PRIMARY KEY,
	DepName VARCHAR(50),
	PhoneNumber VARCHAR(50)
);

INSERT INTO Department(DepName,PhoneNumber) VALUES('IT','222323');

select * from Department;


CREATE TABLE Employee
(
	Id  INT AUTO_INCREMENT PRIMARY KEY,
	FirstName VARCHAR(50),	
	Position  VARCHAR(50),
	DepartmentId INT not null,
	  CONSTRAINT  dememp_id foreign key(DepartmentId) REFERENCES Department(Id) 
      ON DELETE NO ACTION 
      ON UPDATE CASCADE
);

INSERT INTO Employee(FirstName,Position,DepartmentId) VALUES('sd','SE',1)

  /// <summary>
         ///MySQL 創建ISessionFactory
         /// </summary>
         /// <returns></returns>
         public static ISessionFactory GetSessionFactory()
         {
             if (_sessionFactory == null)
             {
                 lock (_objLock)
                 {
                     if (_sessionFactory == null)
                     {
                         //配置ISessionFactory
                         _sessionFactory = FluentNHibernate.Cfg.Fluently.Configure()
                             //資料庫配置
                        .Database(FluentNHibernate.Cfg.Db.MySQLConfiguration.Standard
                            .ConnectionString(c=>c.Server("")
                            .Database("geovindu")
                            .Password("520")
                            .Username("root"))
                            )                            
                        .Mappings(m => m
                         //.FluentMappings.PersistenceModel
                         //.FluentMappings.AddFromAssembly();                       
                         .FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly())) //用法註意            
                     .BuildSessionFactory();
                        

                    //    Fluently.Configure().Database(
                    //    MySqlConfiguration.Standard.ConnectionString(
                    //        c => c.FromConnectionStringWithKey("ConnectionString")
                    //    )
                    //)
                    //.Mappings(m => m.FluentMappings.AddFromAssemblyOf<MyAutofacModule>())
                    //.BuildSessionFactory())

                     }
                 }
             }
             return _sessionFactory;
 
         }
         /// <summary>
         /// 重置Session
         /// </summary>
         /// <returns></returns>
         public static ISession ResetSession()
         {
             if (_session.IsOpen)
                 _session.Close();
             _session = _sessionFactory.OpenSession();
             return _session;
         }
         /// <summary>
         /// 打開ISession
         /// </summary>
         /// <returns></returns>
         public static ISession GetSession()
         {
              GetSessionFactory();
             if (_session == null)
             {
                 lock (_objLock)
                 {
                     if (_session == null)
                     {
                         _session = _sessionFactory.OpenSession();
                     }
                 }
             }
             return _session;
         }

　SQLite sql:

--sqlite
--create database geovindu;

--use geovindu;

drop table Department;

CREATE TABLE Department
(
	Id  INTEGER PRIMARY KEY  AUTOINCREMENT,
	DepName VARCHAR(50),
	PhoneNumber VARCHAR(50)
);

INSERT INTO Department(DepName,PhoneNumber) VALUES('IT','222323');

select * from Department;

drop table Employee;

CREATE TABLE Employee
(
	Id  INTEGER PRIMARY KEY AUTOINCREMENT ,
	FirstName VARCHAR(50),	
	Position  VARCHAR(50),
	DepartmentId INT not null,
	  CONSTRAINT  dememp_id foreign key(DepartmentId) REFERENCES Department(Id) 
      ON DELETE NO ACTION 
      ON UPDATE CASCADE
);

INSERT INTO Employee(FirstName,Position,DepartmentId) VALUES('sd','SE',1)

select * from Employee

　　https://github.com/ladenedge/FluentNHibernate.Cfg.Db.CsharpSqlite

SQLite (測試ISessionFactory還存在問題)

 /// <summary>
    /// http://frankdecaire.blogspot.com/2014/04/fluent-nhibernate-unit-testing-using.html
    /// </summary>
    public static class SQLLiteSessionFactory
    {
        private static ISessionFactory _sessionFactory;
        private static ISessionFactory SessionFactory
        {
            get
            {
                if (_sessionFactory == null)
                {
                    _sessionFactory = Fluently.Configure()
                    .Database(SQLiteConfiguration
                            .Standard
                            .InMemory()
                            .UsingFile("sibodu.db")
                    )
                    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<NHibernateTestProject.Entites.Department>())
                    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<NHibernateTestProject.Entites.Employee>())
                    .ExposeConfiguration(cfg => new SchemaExport(cfg).Create(true, true))
                    .BuildSessionFactory();
                }
                return _sessionFactory;
            }
        }
        public static ISession OpenSession()
        {
            return SessionFactory.OpenSession();
        }
    }

/// http://www.cnblogs.com/vingi/articles/4302497.html
/// http://codeofrob.com/entries/sqlite-csharp-and-nhibernate.html
/// http://frankdecaire.blogspot.com/2014/04/fluent-nhibernate-unit-testing-using.html

 /// <summary>
    /// Nhibernate
    /// </summary>
    public class MonoSQLiteDriver : NHibernate.Driver.ReflectionBasedDriver
    {
        public MonoSQLiteDriver()
            : base(
            "Mono.Data.Sqlite",
            "Mono.Data.Sqlite",
            "Mono.Data.Sqlite.SqliteConnection",
            "Mono.Data.Sqlite.SqliteCommand")
        {
        }

        public override bool UseNamedPrefixInParameter
        {
            get
            {
                return true;
            }
        }

        public override bool UseNamedPrefixInSql
        {
            get
            {
                return true;
            }
        }

        public override string NamedPrefix
        {
            get
            {
                return "@";
            }
        }

        public override bool SupportsMultipleOpenReaders
        {
            get
            {
                return false;
            }
        }
    } 

/// <summary>
    /// Fluent NHibernate
    ///
    /// </summary>
    public class MonoSQLiteConfiguration : PersistenceConfiguration<MonoSQLiteConfiguration>
    {
        public static MonoSQLiteConfiguration Standard
        {
            get { return new MonoSQLiteConfiguration(); }
        }
        /// <summary>
        /// 
        /// </summary>
        public MonoSQLiteConfiguration()
        {
            Driver<MonoSQLiteDriver>();
            Dialect<SQLiteDialect>();
            Raw("query.substitutions", "true=1;false=0");
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public MonoSQLiteConfiguration InMemory()
        {
            Raw("connection.release_mode", "on_close");
            return ConnectionString(c => c
                .Is("Data Source=:memory:;Version=3;"));//New=True;

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public MonoSQLiteConfiguration UsingFile(string fileName)
        {
            return ConnectionString(c => c
                .Is(string.Format("Data Source={0};Version=3;Pooling=true;FailIfMissing=false;UTF8Encoding=True;", fileName)));//New=True;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public MonoSQLiteConfiguration UsingFileWithPassword(string fileName, string password)
        {
            return ConnectionString(c => c
                .Is(string.Format("Data Source={0};Version=3;New=True;Password={1};", fileName, password)));
        }
    }

　　PostgreSQL

//https://developer.jboss.org/wiki/DatabasessupportedbyNHibernate
//https://github.com/daanl/Fluent-NHibernate--PostgreSQL-column-array

sql:

--PostgreSQL
drop table Department;

CREATE TABLE Department
(
	Id  SERIAL PRIMARY KEY,
	DepName VARCHAR(50),
	PhoneNumber VARCHAR(50)
);

INSERT INTO Department(DepName,PhoneNumber) VALUES('IT','222323');

select * from Department;

drop table Employee;

CREATE TABLE Employee
(
	Id  SERIAL PRIMARY KEY ,
	FirstName VARCHAR(50),	
	Position  VARCHAR(50),
	DepartmentId INT not null,
	  CONSTRAINT  dememp_id foreign key(DepartmentId) REFERENCES Department(Id) 
);

INSERT INTO Employee(FirstName,Position,DepartmentId) VALUES('sd','SE',1)

select * from Employee

   /// <summary>
        /// PostgreSQL
        /// </summary>
        /// <returns></returns>
        public static ISessionFactory GetSessionFactory()
        {
               
            if (_sessionFactory == null)
            {
                lock (_objLock)
                {
                    if (_sessionFactory == null)
                    {
                        var connectionStr = "Server=localhost;Port=5432;Database=geovindu;User Id=postgres;Password=770214;";
                        ISessionFactory sessionFactory = Fluently
                         .Configure()
                         .Database(PostgreSQLConfiguration.Standard.ConnectionString(connectionStr).ShowSql())
                         .Mappings(m => m.FluentMappings
                         //AddFromAssemblyOf<FluentNHibernateHelper>())  //TypeOfFluentNHibernateMapping
                         .AddFromAssembly(Assembly.GetExecutingAssembly())) //用法註意    
                         //.ExposeConfiguration(CreateSchema)
                         .BuildSessionFactory();

                    }
                }
            }
            return _sessionFactory;

        }