SQLite3 与 NHibernate 在 .NET 程序中使用

时间:2022-06-10 01:17:39

SQLite 与 NHibernate 在 .NET 程序中使用

作者:终南   <li.zhongnan@hotmail.com>

1。抛弃Access

曾经在VB下作一个程序,为了简单起见,通过共享目录来实现多台机器访问Access数据库,最终却发现这是个噩梦,程序根本没有办法正常运行,不得已采用Mysql。

最近作一个单机桌面程序,想采用Access+NHibernate来实现对数据库的操作,初次试验便告失败,索性不去理它,转而采用SQLite。

2。相关软件

Hibernate是一个Java编写的非常优秀的数据访问框架,它彻底屏蔽了使用SQL语句对数据库的操作,在Hibernate下,对数据库的操作简化为成对象的操作。NHibernate是Hibernate的.Net版本。当前NHibernate最新的稳定版本为1.2.1.GA,可以通过以下页面选择下载:

www.hibernate.org/6.html

NHibernate包括.Net 1.1 和 .Net 2.0两个版本,以我们将要用到的2.0为例,解压下载后的软件得到以下我们需要用到的重要文件:

Castle.DynamicProxy.dll
Iesi.Collections.dll
log4net.dll
NHibernate.dll

SQLite是一个类似于Access的嵌入式、轻量级的小型数据库引擎,无数的软件或系统都采用SQLite作为数据库保存应用数据,如Firefox,Iphone等。与其它大多数SQL数据库不同的是,SQLite没有独立的服务进程。SQLite直接读写原始的磁盘文件,一个拥有多个表、索引、触发器和视图的完整SQL数据库就包含在一个独立的磁盘文件中。数据库文件的格式是跨平台的,你可以在32位和64位系统之间、在不同的操作系统之间*地复制数据库。SQLite目前最新的版本为3.5.7,在其他程序中使用SQLite时,需要SQLite的引擎文件sqlite3.dll。可以通过如下网址下载SQLite:

http://www.sqlite.org/sqlite-3_5_7.zip

解压后,得到 sqlite3.exe 这样一个可执行文件,我们在命令行进入到解压后的目录,然后运行 sqlite3.exe test.db,进入 sqlite3 管理界面,然后执行SQL语句:

create table Users (id integer, EmailAddress varchar(50), UserName varchar(50), Password varchar(50));

创建一个下面例子要用到的表,然后输入.exit 退出。将会有一个名为test.db的文件被创建。

该文件包含一个SQLite命令行式的客户端程序,可以用来创建和访问SQLite数据库。

www.sqlite.org/sqlitedll-3_5_7.zip

解压该文件后,生成一个dll格式的SQLite引擎文件:sqlite3.dll

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

该页面列出了许多管理SQLite数据库的GUI程序,利用这些GUI程序,可以很方便、直观的创建和管理SQLite数据库。通过,一个SQLite数据库就是一个文件,如test.db,文件扩展名可以为任何值,一般为db或者db3。

要在.Net中使用SQLite,还需要一个针对SQLite的ADO.NET Provider,可以通过以下网站下载:

http://sourceforge.net/projects/adodotnetsqlite

解压该文件会生成一个我们需要的文件:SQLite.NET.dll

4。在VS.NET 2005中练习一下

(1) 在VS.NET 2005中创建一个名为 NHibernateWithSQLite 的C#控制台工程。

(2)把以下文件复制到工程目录下:

Castle.DynamicProxy.dll
Iesi.Collections.dll
log4net.dll
NHibernate.dll
SQLite.NET.dll

sqlite3.dll
test.db

(3)将

Castle.DynamicProxy.dll
Iesi.Collections.dll
log4net.dll
NHibernate.dll
SQLite.NET.dll

添加为工程的引用。

(4)将 test.db 和 sqlite3.dll 添加到项目中。并且在文件属性中,将文件的“复制到输出目录”设置为“如果较新则复制”。

(5)修改Program.cs文件,将以下内容复制到Program.cs文件中:

using System;
using System.Diagnostics;
using log4net;
using NHibernate;
using NHibernate.Cfg;
using NHibernateWithSQLite;

namespace NHibernateWithSQLite
{

    class Program
    {

        private const string TestEmailAddress = "li.zhongnan@hotmail.com";
        private const string TestPassword = "password";
        private const string TestUserName = "username";

        static void Main(string[] args)
        {

            // ----------------------------------------
            // Step 1: log4net configuration (optional)
            // ----------------------------------------
            log4net.Config.XmlConfigurator.Configure();


            // ---------------------
            // Step 2: Configuration
            // ---------------------
            Configuration configuration = new Configuration();
            Console.WriteLine("Configure NHibernate...");
            configuration.Configure();
            Console.WriteLine("Loading mapping files in this executable...");
            configuration.AddAssembly(System.Reflection.Assembly.GetExecutingAssembly());


            // --------------------------------
            // Step 3: Create a Session Factory
            // --------------------------------
            ISessionFactory factory = configuration.BuildSessionFactory();


            // ---------------------
            // Test 1: Create a User
            // ---------------------

            int firstId;

            using (ISession session = factory.OpenSession())
            {

                Console.WriteLine("Saving a test User object to the database...");

                User user = new User();
                user.EmailAddress = TestEmailAddress;
                user.Password = TestPassword;
                user.UserName = TestUserName;

                session.Save(user);
                session.Flush();

                firstId = user.Id;

                Console.WriteLine("The User object was assigned an ID of " + firstId + "!");

                Debug.Assert(
                    firstId > 0, "The Id should have been returned.");
            }


            // ---------------------
            // Test 2: Load the User
            // ---------------------
            using (ISession session = factory.OpenSession())
            {

                Console.WriteLine("Attempting to reload User with ID " + firstId + "...");

                User user = session.Load<User>(firstId);

                Debug.Assert(
                    user.Id == firstId,
                    "The wrong Id field was returned.");
               
                Debug.Assert(
                    user.EmailAddress == TestEmailAddress,
                    "The wrong EmailAddress was returned.");

                Debug.Assert(
                    user.Password == TestPassword,
                    "The wrong Password was returned.");

                Debug.Assert(
                    user.UserName == TestUserName,
                    "The wrong UserName was returned.");

                Console.WriteLine("The user was reloaded successfully!");

            }


            // -----------------------
            // Test 3: Delete the User
            // -----------------------
            using (ISession session = factory.OpenSession())
            {
                Console.WriteLine("Deleting the user...");
                User user = session.Load<User>(firstId);
                session.Delete(user);

                try
                {
                    Console.WriteLine("Confirming the user record is deleted...");
                    User deletedUser = session.Get<User>(firstId);
                    throw new InvalidOperationException("The load should not have succeeded.");
                }
                catch(ObjectDeletedException ex)
                {
                    Console.WriteLine(ex.Message);
                }

                session.Flush();
            }

            Console.WriteLine("Done. Press Enter to exit...");
            Console.ReadLine();

        }
    }
}

(6)在项目中添加一个User.cs文件,内容为:

using System;
using System.Collections.Generic;
using System.Text;

namespace NHibernateWithSQLite
{
    public class User
    {

        private int id;
        private string emailAddress;
        private string username;
        private string password;


        public virtual int Id
        {
            get
            {
                return this.id;
            }
            set
            {
                this.id = value;
            }
        }

        public virtual string EmailAddress
        {
            get
            {
                return this.emailAddress;
            }
            set
            {
                this.emailAddress = value;
            }
        }

        public virtual string Password
        {
            get
            {
                return this.password;
            }
            set
            {
                this.password = value;
            }
        }

        public virtual string UserName
        {
            get
            {
                return this.username;
            }
            set
            {
                this.username = value;
            }
        }

    }
}

(7)添加一个 User.hbm.xml文件,内容为:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="NHibernateWithSQLite.User, NHibernateWithSQLite" table="Users">

    <id name="Id" column="Id" type="int" unsaved-value="0">
      <generator class="increment" />
    </id>

    <property name="EmailAddress" length="50" />
    <property name="UserName" column="UserName" type="String" length="50"/>
    <property name="Password" column="[Password]" type="String" length="50"/>

</class>
</hibernate-mapping>
  

(8)在文件属性中,将该文件的“生成操作”设置为“嵌入的资源”。

添加一个App.config文件,其内容为:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
    <section name="log4net"
       type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
    <section name="hibernate-configuration"
            type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate"/>
</configSections>

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory>
      <property name="hibernate.show_sql">true</property>
      <property name="hibernate.connection.release_mode">on_close</property>
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="connection.driver_class">NHibernate.Driver.SQLiteDriver</property>
      <property name="connection.connection_string">Data Source=test.db;Version=3</property>
      <property name="dialect">NHibernate.Dialect.SQLiteDialect</property>
      <property name="query.substitutions">true=1;false=0</property>
    </session-factory>
</hibernate-configuration>

<log4net>
    <appender name="console"
      type="log4net.Appender.ConsoleAppender, log4net">
      <layout type="log4net.Layout.PatternLayout,log4net">
        <param name="ConversionPattern"
        value="%d{ABSOLUTE} %-5p %c{1}:%L - %m%n" />
      </layout>
    </appender>

    <appender name="fileAppender" type="log4net.Appender.FileAppender">
      <file value="log-file.txt" />
      <appendToFile value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
      </layout>
    </appender>

    <root>
      <level value="WARN" />
      <appender-ref ref="console" />
      <appender-ref ref="fileAppender" />
    </root>
</log4net>

</configuration>

该文件为配置文件,用来配置NHibernate如何连接数据库,以及log4net。

(9)编译并运行

(10)祝贺您的程序成功运行

5。文件的目录结构

   SQLite3 与 NHibernate 在 .NET 程序中使用

6。项目在资源管理器中的结构

SQLite3 与 NHibernate 在 .NET 程序中使用