タイトル通り。ついでにEFから呼べるようにもする。まずはNugetで↓をインストール。

そしたら、まず、SQL直接書くやつ。

using MySql.Data.MySqlClient;
public void test()
{
    string constr = @"Database=hogedb;Data Source=fugaserver;User Id=root;Password=passpass";
    string error = "";
    string title = "";
    using (MySqlConnection cn = new MySqlConnection(constr))
    {
        try
        {
            cn.Open();// 接続
            //クエリ発行
            MySqlDataAdapter da = new MySqlDataAdapter("select title from hogetables limit 1;", cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
                title = row[0].ToString();
            }
            cn.Close();//クローズ
        }
        catch (Exception e)
        {
            error = e.Message;
        }
    }
}

次はEF。多分↑のインストールとは別に、EFを入れないとダメ。

最初はWeb.Config。まーこれでやる必要はないけど何となく。

<connectionStrings>
  <add name="RealConnection" connectionString="Database=hogedb;Data Source=fugaserver;User Id=root;Password=passpass" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

テーブル用のPOCO。

[Table("hogetables")]
public class hogetable
{
    [Key]
    public int id { get; set; }
    public string title { get; set; }
}

public class MOGEModels : DbContext
{
    public MOGEModels() : base("RealConnection")
    {
    }
    public DbSet<hogetable> hogetables{ get; set; }
    
    public void test()
    {
       foreach (var tmp in this.hogetables.Where(n => n.id == 10))
       {
          tmp.title;
       }
    }
}

とゆーことで、普通に使えますねって当たり前か。。。

正直、RedmineとかのDBをちょっと他から触りたくなっただけです。Rubyでプラグインとか作るのめんどー。

いつもはSQLServer使ってたんだけど、たまにはPostgreSQLも使ってみようかと思いまして、やってみた。

MVCはVisualStudioでテキトーにテンプレを使う。メンドーなので認証なし。そしたらNugetで下の3つのパッケージをインストール。

EFはEFのために必要。EFでPostgre使うためにNpgsql.EntityFrameworkが必要。これのためにNpgsqlが必要。みたいな感じらしい。EFは6。

そしたら次はWeb.configの設定

<!--ここはインストールすると勝手に入る-->
<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  <providers>
    <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, Npgsql.EntityFramework" />
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  </providers>
</entityFramework>
<!--ここを手で追記-->
<system.data>
  <DbProviderFactories>
    <add name="Npgsql Data Provider" invariant="Npgsql" description="Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql" />
  </DbProviderFactories>
</system.data>
<!--接続文字列の指定-->
<connectionStrings>
  <add name="DefaultConnectionPGSQL" connectionString="Server=localhost;Port=5432;User Id=postgres;Password=＊＊＊＊＊;Database=hogedb" providerName="Npgsql" />
</connectionStrings>

手で追記する部分があるっぽい。ここ入れないと動かなかった。あとは接続文字列の指定。

ためしに作ったテーブルは下の二つ。日本語列名がいけるか試したいので日本語で。

--主キーがserial(SQLServerで言うIdentity)じゃないやつ
create table dbo.ホゲテーブル
(
ホゲ varchar(100) 
,数値 int
,CONSTRAINT wkpk PRIMARY KEY (ホゲ)
)
--主キーにserial(SQLServerで言うIdentity)
create table dbo.フガテーブル
(
フガid SERIAL
,テスト varchar(100) 
,数値 int
)

で、ワナが一つ。SQLServerはデフォでスキーマ名が「dbo」になる。なので、EFも何もしないとSQLに「dbo.table」みたいに書かれる。なので、Postgreのスキーマはdboで作っておいた方が吉。スキーマを変える場合は後述。

あとは、ためし実装。Model側。

public class HomeModels : DbContext
{
    public HomeModels() : base("DefaultConnectionPGSQL")
    {

    }
    public DbSet<HogeTable> HogeTables { get; set; }
    public DbSet<FugaTable> FugaTables { get; set; }

    public HomeViewModel getTable()
    {
        HomeViewModel resultModel = new HomeViewModel();
        resultModel.hogeT = this.HogeTables.Where(n => n.hogeval > 0).ToList();
        resultModel.fugaT = this.FugaTables.Where(n => n.数値 > 0).ToList();

        return resultModel;
    }
    public void modelAdd()
    {
        HogeTable addM = new HogeTable();
        addM.hogekey = DateTime.Now.ToString("yyyyMMddhhmmssfff");
        addM.hogeval = 123;
        this.HogeTables.Add(addM);


        FugaTable addM2 = new FugaTable();
        addM2.テスト = DateTime.Now.ToString("yyyyMMddhhmmssfff");
        addM2.数値 = 123;
        this.FugaTables.Add(addM2);
        this.SaveChanges();
    }

}
public class HomeViewModel
{
    public List<HogeTable> hogeT { get; set; }
    public List<FugaTable> fugaT { get; set; }
}

[Table("ホゲテーブル")] //テーブル名指定
public class HogeTable
{
    [Key] //主キーの設定
    [Column("ホゲ")] //列名指定
    public string hogekey { get; set; }
    [Column("数値")]//列名指定
    public int hogeval { get; set; }
}

[Table("フガテーブル", Schema = "dbo")] //スキーマ変えたいとき
public class FugaTable
{
    [Key] //主キーの設定
    public int フガid { get; set; }
    public string テスト { get; set; }
    public int 数値 { get; set; }
}

コントローラ側。HomeのIndexだけで、あと、データ追加用のAction。

HomeModels db = new HomeModels();
public ActionResult Index()
{
    HomeViewModel result = db.getTable();

    return View(result);
}

public ActionResult AddAction()
{
    db.modelAdd();
    return RedirectToAction("Index");
}

View側はこれ。

@model mvcdemo.Models.HomeViewModel
@{
    ViewBag.Title = "Home Page";
}

<div>
    @Html.ActionLink("Add","AddAction")
</div>

<h1>ホゲテーブル</h1>
<div>
    @foreach(var tmp in Model.hogeT)
    {
        <table>
            <tr>
                <td>
                    @tmp.hogekey
                </td>
                <td>
                    @tmp.hogeval
                </td>
            </tr>
            
        </table>
    }
</div>

<h1>フガテーブル</h1>
<div>
    <table>
        @foreach (var tmp in Model.fugaT)
        {
            <tr>
                <td>
                    @tmp.フガid
                </td>
                <td>
                    @tmp.テスト
                </td>
            </tr>
        }    
    </table>
</div>

実行結果で、何回か追加した後。

ちゃんとIDも振られてるし問題なし。。。フツーに使える。よかたよかた。

スキーマ変えたいときはテーブルのModel作るときに属性で「[Table("フガテーブル", Schema = "dbo")] 」って感じにする。全体通してやる場合はDbContextのクラスの中に下を入れておく。

public class HomeModels : DbContext
{
    public HomeModels() : base("DefaultConnectionSQLServer")//DefaultConnectionPGSQL
    {

    }
    //これを入れる
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("dbo");
    }
    public DbSet<HogeTable> HogeTables { get; set; }
    public DbSet<FugaTable> FugaTables { get; set; }
    ・・・

Modelのインスタンス作った時に呼ばれるやつではないかと。。。

ちなみにこの状態のまま、SQLServer側に同じテーブル作って、接続文字列の指定でSQLServer用のものを書けばそのままDB切り替えられました。ためしに色々やるのに便利。。。

Postgerはフツーに使えていい感じなんだけど、なんつーか、Postgreの管理ツールがびみょー。なんかいいの無いのかしら。

pgAdminなるものを使用してみたけど。微妙じゃない？なんつーか自分がMicrosoftに染められてるんだろーけど、インテリセンス弱すぎじゃない？あと、エラーとか警告とか結果の出力とかもろもろもろもろもろ。SQLServerのManegementStudioはやっぱりさすがにさすがにって感じなんだろーか。。。

EF使う際に、POCO的なオブジェクトでテーブルの定義を作っておく。↓の感じ

[Table("HOGETABLE")]
public class TestTable
{
    [Key]
    public int HOGETABLEID {get;set;}
    public string 何か { get; set; }
}

テーブル名指定には↑の感じで属性で定義しておく。

で、EFじゃなくて、独自にクエリとか作ったりしたい場合に、これの属性のテーブル名が欲しかったりする。その取り方。

public class TestModel : DbContext
{
    public DbSet<TestTable> TestTables { get; set; }

}

string TableName = "";
using (var context = new TestModel())
{
	foreach (var tmp in context.TestTables.GetType().CustomAttributes)
	{
	    if (tmp.AttributeType.Name == "TableAttribute")//テーブル名を入れてるとこ
	    {
	        TableName = tmp.ConstructorArguments[0].Value.ToString();
	        break;
	    }
	}
}

EntityFrameworkを.net MVCで使う場合、Web.Configに↓の感じで接続文字列を設定しておく。

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=***********;Initial Catalog=HOGEDB;Persist Security Info=True; User ID=sa;Password=xxxxxx" providerName="System.Data.SqlClient" />
  </connectionStrings>

で、EFは使い勝手はそこそこいいんだけど、個別にDBにつないで自分でやりたい場合もある。その場合に、この接続文字列を取得したい。やり方は色々あると思うけど、Web｡Configの中身を直で持ってくるやり方。

string wkStr = "";
for (int i = 0; i < System.Configuration.ConfigurationManager.ConnectionStrings.Count; i++)
{
    if (System.Configuration.ConfigurationManager.ConnectionStrings[i].Name == "DefaultConnection")
    {
        wkStr = System.Configuration.ConfigurationManager.ConnectionStrings[i].ConnectionString;
        break;
    }
}

設定が複数ある場合もあるので、接続名指定して取得する。色々試してみて、これしかやりようなかった。他に何かいい方法ないかしら。。。

「System.Configuration.ConfigurationManager.ConnectionStringst」ってforeach出来ないのね。Whereとかも使えない。IEnumにどーにかできないものだろうか。

EF使ってちょろちょろ作ってるけど、発行してるSQLが微妙な感じがたまにする。

特にidentity使ってるテーブルにインサートするとき、インサート文に↓の奴がくっつく。

SELECT ID
FROM [dbo].[TABLENAME]
WHERE @@ROWCOUNT > 0 AND [TABLENAME] = scope_identity()

インサートした直後のidの値をとってるっぽい。インサート後にそのオブジェクトのIDに勝手にID振ってくれてるけど、それようのクエリでしょうね。ただ、クエリとしては遅くないんだけど、プロファイラで見ると、Readsが大きいし、CPUを割とくってる。大量に発行すると致命的な気がするのですが・・・。 インサート直後にそのオブジェクトのID見たいとかあんまりないからなぁ。

んーインサート系は自分でオブジェクトから動的にクエリ作るの作らないとダメかな。