sqlmetalとLinqToSqlを利用したDBアクセスのメモ。

DAOを自動生成

sqlmetal.exeでSqlServerのスキーマからDAOを自動生成させる。

sqlmetal.exe /server:192.168.113.161 /database:hogedb /user:sa /password:passewd /namespace:Bl.Test /code:Hogedb.cs /language:csharp /sprocs /functions /views

オプション詳細については、https://msdn.microsoft.com/ja-jp/library/bb386987(v=vs.110).aspx

/sprocs や /functions のオプションでプロシージャやファンクションもメソッド化できる。

作成されたオブジェクトは、System.Data.Linq.DataContextの派生クラスで、そのままDBを表すオブジェクトとなる。テーブル、View、プロシージャはそのサブクラス(Table)やメソッドとして定義される。

public partial class Hogedb : System.Data.Linq.DataContext
{
}

接続

作成したクラスコンストラクタにコネクション文字列を渡すか、IDbConnectionを渡しクラスを生成すればよい。接続を永続化させる場合は接続のクローズも含め考察が必要かと思われるが、ちょっとしたツール利用などでは簡単に利用可能。コネクション文字列でインスタンスを生成するよりIDbConnectionを利用した方がトランザクション、クローズの処理がしやすいように思ったが、Connectionプロパティで取得可能なので、コネクション文字列でインスタンス生成すれば十分。

string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\northwind.mdf;
    Integrated Security=True; Connect Timeout=30; User Instance=True";
SqlConnection connection = new SqlConnection(connString);
connection.Open();

var db = new Hogedb(connection);


....

connection.Close();
string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\northwind.mdf;
    Integrated Security=True; Connect Timeout=30; User Instance=True";
var db = new Hogedb(connString);
db.Connection.Open();

....

db.Connection.Close();

Disposeableなので、

using(var db = new Hogedb(connString))
{
    db.Connection.Open();

}

でいけば、勝手にクローズしてくれるかもね???

https://msdn.microsoft.com/ja-jp/library/bb386944(v=vs.110).aspx

Linqとしての利用

普通にLinqのfromでテーブルに相当するオブジェクトを指定すればよいだけ。

var query = from n in db.XMLG_AIR
            where n.Point >= 80 
            select n; 

SQLを直接書きたい場合は以下のような感じ

var results = db.ExecuteQuery(
    @"select c1.custid as CustomerID, c2.custName as ContactName
        from customer1 as c1, customer2 as c2
        where c1.custid = c2.custid"
);

パラメータは、String.Formatの形式で指定できる。実行時にはバインド変数となるのでSQLインジェクションは気にしなくてOK!!

var results = db.ExecuteQuery(
    "select contactname from customers where city = {0}",
    "London"
);

データ更新

インサート

db.XMLG_AIR.InsertOnSubmit(new XMLG_AIR()
{
	Hoge = "xxxxx",
    ....
});
db.SubmitChanges();

更新

XMLG_AIR air = db.XMLG_AIR.Single(m => m.Uid == 1);
air.Hoge = "newValue";
db.SubmitChanges();

※更新だけTableのメソッドを呼ばないのはちょっと違和感あるけど...

2016.1.23. LinqToSQLでは、変更をため込んでSubmitChanges()で一気にDBに吐き出すために、このような形になってたのね(汗)

削除

db.XMLG_AIR.DeleteOnSubmit(db.XMLG_AIR.Single(m => m.Uid == 2));
db.SubmitChanges();

トランザクション

普通にトランザクションをコネクションから作成する方法

SqlTransaction tx = db.Connection.BeginTransaction();
try
{
	XMLG_AIR air = db.XMLG_AIR.Single(m => m.Uid == 1);
	air.Hoge = "newValue";
	db.SubmitChanges();
	tx.Commit();
}
catch(Exception)
{
	tx.Rollback();
}

トランザクションスコープを利用する方法(MSはこちらをお勧め)

using (TransactionScope ts = new TransactionScope())
{
	XMLG_AIR air = db.XMLG_AIR.Single(m => m.Uid == 1);
	air.Hoge = "newValue";
	db.SubmitChanges();
	ts.Complete();
}

各種イベントハンドル用拡張メソッド

その他、各種イベントハンドル用の拡張メソッドが用意されているので特定のカラム更新へのインジェクションなども可能。

データベース

    partial void OnCreated();
    partial void Insertテーブル名(TEntity instance);
    partial void Updateテーブル名(TEntity instance);
    partial void Deleteテーブル名(TEntity instance);

テーブル

    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void Onカラム名Changing(object value);
    partial void Onカラム名Changed();

感想

説明もこれだけで済んでしまうほど簡単。コネクションの永続化と設定ファイルからのコネクション文字列取得だけ共通的なものを作っておけばいい感じです。

メモ

  • 他のプロバイダ、OracleとかMysqlとかは、dbmetalってのをググれば、sqlmetalの派生が見つかる。
  • 残念ながらsequenceはメソッド化してくれないので、ファンクションやプロシージャでラップしてやる必要あり。