【C#】【SQL】【Linq】Linq to SQL ~ストアド編~

サンプル

テーブル

Personテーブル

CREATE TABLE [dbo].[Person](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Address] [nvarchar](50) NULL,
	[Email] [nvarchar](50) NULL,
	[Age] [int] NULL
) ON [PRIMARY]

Employeeテーブル

CREATE TABLE [dbo].[Employee](
	[ID] [nchar](4) NULL,
	[PersonID] [int] NULL,
	[CompanyCode] [varchar](4) NULL,
	[BrunchCode] [varchar](4) NULL
) ON [PRIMARY]

ビュー

CREATE VIEW [dbo].[SamplePersonView]
AS
SELECT
 p.Id, p.Name, p.Address, p.Email, p.Age, e.CompanyCode, e.BrunchCode
FROM
 dbo.Person AS p
INNER JOIN
  dbo.Employee AS e ON p.Id = e.PersonID

ストアド

CREATE PROCEDURE [dbo].[SamplePersonWithNoOption]
AS
SELECT * from [dbo].[SamplePersonView]
 OPTION(FORCE ORDER, EXPAND VIEWS)

プログラム例

* dbmlファイル(DataClasses1.dbml)を追加し、ストアドを登録しておく
http://chicasharp.net/scottgu/result2.aspx?target=LINQ+to+SQL+(%E3%83%91%E3%83%BC%E3%83%88+6+-+%E3%82%B9%E3%83%88%E3%82%A2%E3%83%89%E3%83%97%E3%83%AD%E3%82%B7%E3%83%BC%E3%82%B8%E3%83%A3%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E5%8F%96%E5%BE%97)+
private void button1_Click(object sender, EventArgs e)
{

    using (var db = new DataClasses1DataContext())
    {
        var query = db.SamplePersonWithNoOption();
        this.dataGridView1.DataSource = query;
    }
}