Use sqlite3 and nhib.pdf in. net programs

SQLite and nhib.pdf are used in. net programs.

Author: Finan <li.zhongnan@hotmail.com>

1. Discard access

I once made a program in VB. For the sake of simplicity, I used a shared directory to access the database from multiple machines. In the end, I found this was a nightmare. The program could not run normally and I had to use MySQL.

Recently, I made a single-host desktop program and wanted to use access + nhib.pdf to perform database operations. In the first test, I tried to fail. I simply ignored it and switched to SQLite.

2. Related Software

Hibernate is an excellent data access framework written in Java. It completely shields the use of SQL statements for database operations. In hibernate
Operations are simplified to object-based operations. Nhib.pdf is the. NET version of hibernate. The latest stable version of nhib.pdf is 1.2.1.ga.
Select Download from the following page:

Www.hibernate.org/6.html

Nhib.pdf consists of. NET 1.1 and. NET 2.0. Take the 2.0 version we will use as an example. decompress the downloaded software to obtain the following important files we need:

Castle. dynamicproxy. dll
Iesi. Collections. dll
Log4net. dll
Nhib.pdf. dll

SQLite is an embedded and lightweight small database engine similar to access. Countless software or systems use SQLite as a database to store application data, such
Firefox,
IPhone, etc. Unlike most other SQL databases, SQLite does not have an independent service process. SQLite directly reads and writes the original disk file, one with multiple tables, indexes, and triggers
The complete SQL database of the server and view is included in an independent disk file. The format of database files is cross-platform. You can freely repeat between 32-bit and 64-bit systems and between different operating systems.
Database. The latest version of SQLite is 3.5.7. When using SQLite in other programs, you need the SQLite engine file sqlite3.dll. You can use the following
Download SQLite:

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

Decompress the package and obtain an executable file like sqlite3.exe. Run the following command to the decompressed Directory: sqlite3.exe test. DB, go to the sqlite3 management interface, and run the SQL statement:

Create Table users (ID integer, emailaddress varchar (50), username varchar (50), password varchar (50 ));

Create a table to be used in the following example and enter. Exit to exit. A file named test. DB will be created.

This file contains a SQLite command-line client program that can be used to create and access the SQLite database.

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

Decompress the file and generate a DLL-format SQLite engine file: sqlite3.dll

Http://www.sqlite.org/cvstrac/wiki? P = managementtools

This page lists many GUI programs for managing SQLite databases. These GUI programs allow you to easily and intuitively create and manage SQLite databases. A SQLite database is a file, such as test. dB. The file extension can be any value, generally dB or DB3.

To use SQLite in. net, you also need an ADO. net provider for SQLite, which can be downloaded from the following website:

Http://sourceforge.net/projects/adodotnetsqlite

Decompress this file to generate a file we need: SQLite. net. dll

4. Exercise in vs. NET 2005

(1) create a C # console project named nhibernatewithsqlite in vs. NET 2005.

(2) copy the following files to the project directory:

Castle. dynamicproxy. dll
Iesi. Collections. dll
Log4net. dll
Nhib.pdf. dll
SQLite. net. dll

Sqlite3.dll
Test. DB

(3) Change

Castle. dynamicproxy. dll
Iesi. Collections. dll
Log4net. dll
Nhib.pdf. dll
SQLite. net. dll

Add as a reference to the project.

(4) add test. DB and sqlite3.dll to the project. In addition, in the file properties, set "Copy to output directory" to "copy if new ".

(5) modify the program. CS file and copy the following content to the program. CS file:

Using system;
Using system. diagnostics;
Using log4net;
Using nhib.pdf;
Using nhib.pdf. 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 nhib.pdf ...");
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 shoshould 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 shocould not have succeeded .");
}
Catch (objectdeletedexception ex)
{
Console. writeline (ex. Message );
}

Session. Flush ();
}

Console. writeline ("done. Press enter to exit ...");
Console. Readline ();

}
}
}

(6) Add a user. CS file to the project with the following content:

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) Add a user. HBM. xml file with the following content:

<? 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) In the file attributes, set the "generate operation" of the file to "embedded resources ".

Add an app. config file with the following content:

<? XML version = "1.0" encoding = "UTF-8"?>
<Configuration>
<Configsections>
<Section name = "log4net"
Type = "log4net. config. log4netconfigurationsectionhandler, log4net"/>
<Section name = "hibernate-configuration"
Type = "nhib.pdf. cfg. configurationsectionhandler, nhib.pdf"/>
</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"> nhib.pdf. Connection. driverconnectionprovider </property>
<Property name = "connection. driver_class"> nhib.pdf. Driver. sqlitedriver </property>
<Property name = "connection. connection_string"> Data Source = test. DB; version = 3 </property>
<Property name = "dialect"> nhib.pdf. dialect. sqlitedialect </property>
<Property name = "query. substitutions"> true = 1; false = 0 </property>
</Session-factory>
</Hibernate-configuration>

<Log4net>
<Appender name = "console"
Type = "log4net. appender. leleappender, log4net">
<Layout type = "log4net. layout. patternlayout, log4net">
<Param name = "conversionpattern"
Value = "% d {absolute} %-5 p % 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] %-5 level % logger [% property {NDC}]-% message % newline"/>
</Layout>
</Appender>

<Root>
<Level value = "Warn"/>
<Appender-ref = "console"/>
<Appender-ref = "fileappender"/>
</Root>
</Log4net>

</Configuration>

This is the configuration file used to configure how Nhibernate connects to the database and log4net.

(9) Compile and run

(10) congratulations on the successful running of your program

5. File directory structure

6. Structure of the project in the Resource Manager