Introduction to ORM Petapoco (ii)

1. petapoco Basic usage

1.1. Creating a sample project

Create a project file first, and create a project file of the type: WindowsApplication for easy presentation of the data. Named as: Petapocotest.

The final layout and function preview of the program are as follows:

1.2. adding Petapoco packages

Right-click on the reference of the project file, select "Manage NuGet packages" and search for Petapoco, install it.

1.3. Adding a database connection

Add the database connection string in the app. config or Web. config file.

Here is the connection to SQL Server:

  <connectionStrings>    <add name= "defaultconnection" connectionstring= "Data source=huhm\sqlexpress;i Nitial Catalog=northwind; Persist Security info=true; User id=aspnet; password=***, "providername=" System.Data.SqlClient "/>  </connectionStrings>

Here is the connection to MySQL:

<add name= "defaultconnection" connectionstring= "SERVER=HUHM; Port=3306;database=northwind; Uid=aspnet; pwd=***;p ooling=false; "providername=" MySql.Data.MySqlClient "/>

Since Petapoco is a database-agnostic ORM component, the code for adding, deleting, changing, and checking db is independent of which physical database type is connected.

1.4. defining poco-entity classes

public class article{public    long article_id {get; set;}    public string Title {get; set;}    Public DateTime date_created {get; set;}    public bool Draft {get; set;}    public string content {get; set;}}

1.5. Creating a Petapoco Action object

Next, create a Petapoco.database object.

var db=new petapoco.database ("defaultconnection");

1.6. Querying Data

Query all Data    foreach (var a in db). Query<article> ("SELECT * from Articles")) {    Console.WriteLine ("{0}-{1}", a.article_id, A.title);} Query scalar long count=db. Executescalar<long> ("Select Count (*) from articles");//Query single data var a = db. Singleordefault<article> ("select * from articles WHERE [email protected]", 123));

1.7. Paging Query

var result=db. Page<article> (1, +,//<--page number and items per page        "SELECT * from articles WHERE [email protected] OR DER by date_posted DESC "," Coolstuff ");

Returns a Pagedfetch object that includes the following properties:

public class page<t> where T:new () {public    long currentpage {get; set;}    Public long ItemsPerPage {get; set;}    Public long TotalPages {get; set;}    Public long TotalItems {get; set;}    Public list<t> Items {get; set;}}

1.8. Query and Fetch methods

Petapoco supports 2 ways to query data: Query and Fetch. Fetch returns the List<t> data object, and query uses the yield iterator to return the IEnumerable, and not all the data is fetched to memory at once.

1.9. non-query commands

Executes a non-query statement using the Execute method.

Db. Execute ("DELETE from articles WHERE draft<>0");

1.10. Adding and deleting changes

Petapoco very well support the additions and deletions to the search.

To insert a record, you need to declare the table name and the primary key:

Create the Articlevar a=new article (); a.title= "my title"; a.content= "test data by Tinyhu"; a.date_created=datetime.utcnow;// Insert ITDB. Insert ("articles", "article_id", a);

Update data:

Get a Recordvar a=db. Singleordefault<article> ("select * from articles WHERE [email protected]", 123);//Change ita.content= "test data by Tiny Hu ";//Save ITDB. Update ("articles", "article_id", a);

You can pass in an anonymous type to update only part of the field. For example, only the titles Title column is updated below.

There are 2 ways to delete:

Delete an article extracting the primary key from a recorddb. Delete ("articles", "article_id", a);/Or If you already has the ID elsewheredb. Delete ("articles", "article_id", NULL, 123);

1.11. declaring a Poco object

In the above example, you need to declare the table name and the primary key to add and remove, to simplify, the TableName and Primarkey properties can be added to the Poco object, so that the crud operation is no longer required to declare the table name and the primary key.

[Petapoco.tablename ("articles")] [Petapoco.primarykey ("article_id")]public class article{public    long article_id {get; set;}    public string Title {get; set;}    Public DateTime date_created {get; set;}    public bool Draft {get; set;}    public string content {get; set;}}

Delete, update, or delete an entity object directly, as in the following example.

Insert a Recordvar a=new article (); a.title= "Test title"; a.content= "test data by Tinyhu"; a.date_created=datetime.utcnow;db. Insert (a);//Update ita.content= "Modify, modify ...";d B. Update (a);//Delete ITDB. Delete (a);

You can declare some fields to ignore the update, as in the following example:

public class article{    [Petapoco.ignore] public    long somecalculatedfieldperhaps    {         get; set;     }}

1.12. Automatic SELECT clauses

When using Petapoco, most queries start with "SELECT * FROM table". You can omit the SELECT * FROM table clause because Petapoco will automatically build it for us.

For example, the following sentence:

Get a Recordvar a=db. Singleordefault<article> ("select * from articles WHERE [email protected]", 123);

can be abbreviated as:

Get a Recordvar a=db. Singleordefault<article> ("WHERE [email protected]", 123);

1.13. isnew and Save methods

1.14. Transaction Transactions

1. 15. sql Builder for Petapoco

SOURCE Quote: http://www.cnblogs.com/tinyhu/archive/2013/06/02/3113692.html

Introduction to ORM Petapoco (ii)