リード開発メモ

大阪のソフトウェア会社です。 技術的な事柄についてのメモとしてブログを始めます。

2013年10月

PetaPoco を使ってみる(その3)

今回は PetaPoco を使って 3 テーブルを JOIN してデータ取得してみる。

やはりテーブル定義などは Dapper のときと同じなので、そちらの記事を参照してほしい。
Dapper を使ってみる(その1)
Dapper を使ってみる(その2)
Dapper を使ってみる(その3)

3 テーブルを JOIN した場合のコードは以下のようになる。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
 
using PetaPoco;
 
namespace PetaPocoTest1
{
    public partial class Form1 : Form
    {
        private string connectionString = "Data Source=(local);Database=ORMTest;User ID=ORMTest_user;Password=ORMTest_password";
 
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
            var resultList = db.Query<Product, SubCategory, Category, Product>(
                    (p, s, c) => { p.SubCategory = s; s.Category = c; return p; },
                    @"SELECT p.*, s.*, c.* FROM Product p 
                    INNER JOIN SubCategory s ON s.SubCategoryId = p.SubCategoryId
                    INNER JOIN Category c ON c.CategoryId = s.CategoryId"
                );
 
            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.Columns.AddRange(
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "ProductId",
                    Name = "ProductId",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "Name",
                    Name = "Name",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "Price",
                    Name = "Price",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "SubCategoryId",
                    Name = "SubCategoryId",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "SubCategoryName",
                    Name = "SubCategoryName",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "CategoryName",
                    Name = "CategoryName",
                }
            );
 
            dataGridView1.DataSource = resultList.ToList();
        }
    }
 
    public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public int Price { get; set; }
        public int SubCategoryId { get; set; }
 
        public SubCategory SubCategory { get; set; }
        public string SubCategoryName { get { return SubCategory.Name; } }
        public string CategoryName { get { return SubCategory.Category.Name; } }
    }
 
    public class SubCategory
    {
        public int SubCategoryId { get; set; }
        public string Name { get; set; }
        public int CategoryId { get; set; }
 
        public Category Category { get; set; }
    }
 
    public class Category
    {
        public int CategoryId { get; set; }
        public string Name { get; set; }
    }
}

変更内容は Dapper のときとほぼ同じである。

PetaPoco.cs を見てみると、4つまでのテーブルの JOIN をサポートしているようだ。Dapper が6つまでをサポートしているのでこの点は Dapper に軍配が上がる。5つ以上の JOIN をする場面はあまりないとは思うが。。。

以上。

PetaPoco を使ってみる(その2)

前回は PetaPoco を使ってひとつのテーブルからデータを取得したが、今回は JOIN した場合をやってみる。

使うデータベースは Dapper と同じなので以下を参照のこと。
Dapper を使ってみる(その1)
Dapper を使ってみる(その2)

PetaPoco で JOIN したデータを取得するのは以下のようになる。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
 
using PetaPoco;
 
namespace PetaPocoTest1
{
    public partial class Form1 : Form
    {
        private string connectionString = "Data Source=(local);Database=ORMTest;User ID=ORMTest_user;Password=ORMTest_password";
 
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
            var resultList = db.Query<Product, SubCategory, Product>(
                    (p, s) => { p.SubCategory = s; return p; },
                    @"SELECT p.*, s.* FROM Product p 
                    INNER JOIN SubCategory s ON s.SubCategoryId = p.SubCategoryId"
                );
 
            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.Columns.AddRange(
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "ProductId",
                    Name = "ProductId",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "Name",
                    Name = "Name",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "Price",
                    Name = "Price",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "SubCategoryId",
                    Name = "SubCategoryId",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "SubCategoryName",
                    Name = "SubCategoryName",
                }
            );
 
            dataGridView1.DataSource = resultList.ToList();
        }
    }
 
    public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public int Price { get; set; }
        public int SubCategoryId { get; set; }
 
        public SubCategory SubCategory { get; set; }
        public string SubCategoryName { get { return SubCategory.Name; } }
    }
 
    public class SubCategory
    {
        public int SubCategoryId { get; set; }
        public string Name { get; set; }
        public int CategoryId { get; set; }
    }
}

すこし引数の順番が違ったりするが、考え方は Dapper と同じだ。Product クラスに SubCategory クラスへの参照が追加する点も Dapper と同じ。ただ、Dapper に比べて PetaPoco のほうが若干シンプルに書けるように思う。

以上。

PetaPoco を使ってみる(その1)

今回から micro-ORM である PetaPoco を試してみる。PetaPoco も Dapper と同じく、1ファイルのみからなっているが、Dapper と違って最初から Insert/Update/Delete メソッドを持っている。

PetaPoco は以下からダウンロードできる。
https://github.com/toptensoftware/petapoco

ダウンロードした中に「PetaPoco」というフォルダがあり、その中の「PetaPoco.cs」が、PetaPoco 本体である。プロジェクトにこのファイルを追加するだけで PetaPoco が使えるようになる。

サンプルで使うデータベースは前回までの Dapper で使ったものと同じなので、そちらを見てほしい。
プロジェクトを作成する。プロジェクト名は PetaPocoTest1 とした。

次に PetaPoco 本体である PetaPoco .cs をプロジェクトに追加する。ここで、
'ConfigurationManager' は現在のコンテキスト内に存在しません。

とのエラーが出ると思うので、参照設定に System.Configuration を追加する。

次に Form1.cs に以下を追加する。
using PetaPoco;

Form1 にButton と DataGridview を貼り付け、Button のクリックイベントで SQL Server からデータを取得し、DataGridview に表示することにする。このあたりは Dapper のときと同じだ。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
 
using PetaPoco;
 
namespace PetaPocoTest1
{
    public partial class Form1 : Form
    {
        private string connectionString = "Data Source=(local);Database=ORMTest;User ID=ORMTest_user;Password=ORMTest_password";
 
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
            var resultList = db.Query<Product>(@"SELECT * FROM Product ORDER BY ProductId");
            dataGridView1.DataSource = resultList.ToList();
        }
    }
 
    public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public int Price { get; set; }
        public int SubCategoryId { get; set; }
    }
}

Dapper よりも簡単に書ける。DB接続のオープンもクローズも書く必要がない。これはこれでなんか不安だが。

以上。

Dapper を使ってみる(その5)

前回、Dapper を使って INSERT、UPDATE、DELETE をしてみた。そのとき、更新したあと再表示まで行っていなかったが、今回は再表示してみたいと思う。

INSERT の対象となる Product テーブルの ProductId は、IDENTITY 属性が付けてある。自動インクリメントされる ID なので、新しい行の ProductId の値は INSERT してみるまで不明である。

そのため Dapper では INSERT すると同時に、SCOPE_IDENTITY 関数を使って追加された行の ProductId を取得する必要がある。Dapper.Contrib や Dapper.Rainbow の拡張を使えば、Insert メソッドが使えるためこのあたりは必要なくなると思う。他の micro-ORM と呼ばれるものも Insert メソッドがあるため必要ないだろう。素の Dapper にも Insert/Update/Deleteメソッドくらい用意してくれてもいいのにと思うが。。。

ソースコードは以下のようになる。なお、DataGridView の DataSource には、BindingList をセットするように変更してある。これによりバインドしているリストへの変更が自動的に DataGridView の表示にも反映される。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Dapper;
 
namespace DapperTest1
{
    public partial class Form1 : Form
    {
        private string connectionString = "Data Source=(local);Database=ORMTest;User ID=ORMTest_user;Password=ORMTest_password";
 
        public Form1()
        {
            InitializeComponent();
 
            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.Columns.AddRange(
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "ProductId",
                    Name = "ProductId",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "Name",
                    Name = "Name",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "Price",
                    Name = "Price",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "SubCategoryId",
                    Name = "SubCategoryId",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "SubCategoryName",
                    Name = "SubCategoryName",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "CategoryName",
                    Name = "CategoryName",
                }
            );
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            IList<Product> resultList = null;
 
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                resultList = conn.Query<Product, SubCategory, Category, Product>(
                    @"SELECT p.*, s.*, c.* FROM Product p 
                    INNER JOIN SubCategory s ON s.SubCategoryId = p.SubCategoryId
                    INNER JOIN Category c ON c.CategoryId = s.CategoryId",
                    (p, s, c) => { p.SubCategory = s; s.Category = c; return p; },
                    splitOn: "SubCategoryId, CategoryId"
                ).ToList();
                conn.Close();
            }
 
            dataGridView1.DataSource = new BindingList<Product>(resultList);
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            Product newRow = null;
 
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                int newId = conn.Query<int>(
                    @"INSERT Product (Name, Price, SubCategoryId) 
                    VALUES (@Name, @Price, @SubCategoryId);
                    SELECT CAST(SCOPE_IDENTITY() as int);",
                    new { Name = "弱虫ペダル(30)", Price = 450, SubCategoryId = 100 }
                ).Single();
                newRow = conn.Query<Product, SubCategory, Category, Product>(
                    @"SELECT p.*, s.*, c.* FROM Product p 
                    INNER JOIN SubCategory s ON s.SubCategoryId = p.SubCategoryId
                    INNER JOIN Category c ON c.CategoryId = s.CategoryId
                    WHERE p.ProductId = @ProductId",
                    (p, s, c) => { p.SubCategory = s; s.Category = c; return p; },
                    param: new { ProductId = newId },
                    splitOn: "SubCategoryId, CategoryId"
                ).First();
                conn.Close();
            }
 
            var bs = dataGridView1.DataSource as BindingList<Product>;
            bs.Add(newRow);
        }
 
        private void button3_Click(object sender, EventArgs e)
        {
            IList<Product> resultList = null;
 
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                int count = conn.Execute(
                    @"UPDATE Product SET
                    Name = @Name, Price = @Price, SubCategoryId = @SubCategoryId 
                    WHERE ProductId >= @ProductId",
                    new { Name = "ポケットモンスター X", Price = 3918, SubCategoryId = 200, ProductId = 6 });
                resultList = conn.Query<Product, SubCategory, Category, Product>(
                    @"SELECT p.*, s.*, c.* FROM Product p 
                    INNER JOIN SubCategory s ON s.SubCategoryId = p.SubCategoryId
                    INNER JOIN Category c ON c.CategoryId = s.CategoryId
                    WHERE ProductId >= @ProductId",
                    (p, s, c) => { p.SubCategory = s; s.Category = c; return p; },
                    param: new { ProductId = 6 },
                    splitOn: "SubCategoryId, CategoryId"
                ).ToList();
                conn.Close();
            }
 
            var bs = dataGridView1.DataSource as BindingList<Product>;
            resultList.ToList().ForEach(m =>
            {
                for (int i = 0; i < bs.Count(); i++)
                {
                    if (bs[i].ProductId == m.ProductId)
                    {
                        bs[i] = m;
                    }
                }
            });
        }
 
        private void button4_Click(object sender, EventArgs e)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                int count = conn.Execute(
                    @"DELETE FROM Product
                    WHERE ProductId >= @ProductId",
                    new { ProductId = 6 });
                conn.Close();
            }
 
            var bs = dataGridView1.DataSource as BindingList<Product>;
            var removeList = bs.Where(m => m.ProductId > 6).ToList();
            removeList.ForEach(m => bs.Remove(m));
        }
    }
 
    public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public int Price { get; set; }
        public int SubCategoryId { get; set; }
 
        public SubCategory SubCategory { get; set; }
        public string SubCategoryName { get { return SubCategory.Name; } }
        public string CategoryName { get { return SubCategory.Category.Name; } }
    }
 
    public class SubCategory
    {
        public int SubCategoryId { get; set; }
        public string Name { get; set; }
        public int CategoryId { get; set; }
 
        public Category Category { get; set; }
    }
 
    public class Category
    {
        public int CategoryId { get; set; }
        public string Name { get; set; }
    }
}

データベースを更新した後、DataGridView のデータソースに反映する部分はもっとスマートにできる気がするが。。。

以上。

Dapper を使ってみる(その4)

今回は Dapper を使って INSERT、UPDATE、DELETE をしてみる。

フォームにボタンを3つ追加し、button2でINSERT、button3でUPDATE、button4でDELETE の処理を行う。簡単にするため、これらのボタン処理では DB を更新するだけなので、表示に反映させるためには button1 で SELECT しなおす必要がある。
なお、何度か button1 を押すことになるので、グリッドのカラムを設定する処理はコンストラクタに移動させた。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Dapper;
 
namespace DapperTest1
{
    public partial class Form1 : Form
    {
        private string connectionString = "Data Source=(local);Database=ORMTest;User ID=ORMTest_user;Password=ORMTest_password";
 
        public Form1()
        {
            InitializeComponent();
 
            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.Columns.AddRange(
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "ProductId",
                    Name = "ProductId",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "Name",
                    Name = "Name",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "Price",
                    Name = "Price",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "SubCategoryId",
                    Name = "SubCategoryId",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "SubCategoryName",
                    Name = "SubCategoryName",
                },
                new DataGridViewTextBoxColumn()
                {
                    DataPropertyName = "CategoryName",
                    Name = "CategoryName",
                }
            );
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            IEnumerable<Product> resultList = null;
 
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                resultList = conn.Query<Product, SubCategory, Category, Product>(
                    @"SELECT p.*, s.*, c.* FROM Product p 
                    INNER JOIN SubCategory s ON s.SubCategoryId = p.SubCategoryId
                    INNER JOIN Category c ON c.CategoryId = s.CategoryId",
                    (p, s, c) => { p.SubCategory = s; s.Category = c; return p; },
                    splitOn: "SubCategoryId, CategoryId"
                ).ToList();
                conn.Close();
            }
 
            dataGridView1.DataSource = resultList;
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                int count = conn.Execute(
                    @"INSERT Product (Name, Price, SubCategoryId) 
                    VALUES (@Name, @Price, @SubCategoryId)",
                    new { Name = "弱虫ペダル(30)", Price = 450, SubCategoryId = 100 });
                conn.Close();
            }
        }
 
        private void button3_Click(object sender, EventArgs e)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                int count = conn.Execute(
                    @"UPDATE Product SET
                    Name = @Name, Price = @Price, SubCategoryId = @SubCategoryId 
                    WHERE ProductId >= @ProductId",
                    new { Name = "ポケットモンスター X", Price = 3918, SubCategoryId = 200, ProductId = 6 });
                conn.Close();
            }
        }
 
        private void button4_Click(object sender, EventArgs e)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                int count = conn.Execute(
                    @"DELETE FROM Product
                    WHERE ProductId >= @ProductId",
                    new { ProductId = 6 });
                conn.Close();
            }
        }
    }
 
    public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public int Price { get; set; }
        public int SubCategoryId { get; set; }
 
        public SubCategory SubCategory { get; set; }
        public string SubCategoryName { get { return SubCategory.Name; } }
        public string CategoryName { get { return SubCategory.Category.Name; } }
    }
 
    public class SubCategory
    {
        public int SubCategoryId { get; set; }
        public string Name { get; set; }
        public int CategoryId { get; set; }
 
        public Category Category { get; set; }
    }
 
    public class Category
    {
        public int CategoryId { get; set; }
        public string Name { get; set; }
    }
}

Dapper では、SQL で INSERT、UPDATE、DELETE 文を作り、Execute メソッドで実行することになる。パラメタライズドクエリーなだけで、本当に生のSQLを書く必要があるようだ。

ただし、Dapper には DapperExtention や Rainbow などの拡張が用意されており、それらを使うと Insert メソッド、Update メソッド、Delete メソッドが使えるようだ。それで少しは便利になるが、Dapper の導入は1ファイルを追加するだけという売りがなくなってしまうのであるが。。。

実行結果は以下のとおり。まず button1 で表示。 dapper-4

button2 で追加後、button1 で再表示。 dapper-5

button3 で更新後、button1 で再表示。 dapper-6

button4 で削除後、button1 で再表示。 dapper-7

以上。

アクセスカウンター
  • 今日:
  • 昨日:
  • 累計: