INNER JOIN などによる新規 schema を扱うための TableAdapter の作成方法

Question

※ 旧タイトル.... TableAdapter.GetDataBy___() の使い方を知りたい

 

50音を元にテーブルを作っています。

columns テーブル

50音の行。あ、か、さ、た、な....など

chars テーブル

行に含まれるひらがな。あ行の場合、あ、い、う、え、お。

persons テーブル

行に関係付けられた人名。「あ行」の「阿部さん」、「伊藤さん」など。

chars_persons テーブル

50個のひらがなと人名の対応付けを記録。「あ」の「阿部さん」、「い」の「伊藤さん」など。

それぞれを下図 (1),(2) のように関連付けています。

さて、行番号 (column_id) を元に、対応する人名 (person) とひらがな
(char) を芋づる式に抽出できないか、と考え、クエリ ビルダーを使って参考
図 (3) のようにクエリを組みました。

column_id = 1 を代入した場合の結果が参考図 (3) の下部に得られています。

この結果をプログラムでも得られないかと考え、以下のように安直に組んでみました。

Code Snippet

private void Form1_Load(object sender, EventArgs e)
{
  this.columnsTableAdapter.Fill(this.syllabaryDataSet.columns);
  this.personsTableAdapter.Fill(this.syllabaryDataSet.persons);
  this.charsTableAdapter.Fill(this.syllabaryDataSet.chars);
  this.chars_personsTableAdapter.Fill(this.syllabaryDataSet.chars_persons);

 

  var persons = this.personsTableAdapter.GetDataByColumnId(1);
}

 

そうすると、参考 (4) のような例外が発生してしまいました。

図 (3) 下部の表のようにデータを取得するにはどうした良いのでしょう？

 

 

 

 

---

参考図 (1)
(略)

参考図 (2)
(略)

参考図 (3) クエリ ビルダーで GetDataByColumnId() を作成

参考 (4)

Code Snippet

System.Data.ConstraintException はハンドルされませんでした。
  Message="制約を有効にできませんでした。
    行に入力できるのは、Null 以外の値、一意な値、あるいは外部キーですが、
    この制約の違反が 1 つ以上の行で発生しています。"
  Source="System.Data"
  StackTrace:
    場所 System.Data.DataTable.EnableConstraints()
    場所 System.Data.DataTable.set_EnforceConstraints()
    場所 System.Data.DataTable.EndLoadData()
    場所 System.Data.Common.DataAdapter.FillFromReader()
    場所 System.Data.Common.DataAdapter.Fill()
    場所 System.Data.Common.DbDataAdapter.FillInternal()
    場所 System.Data.Common.DbDataAdapter.Fill()
    場所 System.Data.Common.DbDataAdapter.Fill()
    場所 getPersonsWithCharsInfo.syllabaryDataSetTableAdapters.personsTableAdapter.GetDataByColumnId()
    場所 C:\Documents and Settings\custar\....\syllabaryDataSet.Designer.cs:行 3370
    場所 getPersonsWithCharsInfo.Form1.Form1_Load()
    場所 C:\Documents and Settings\custar\....\getPersonsWithCharsInfo\Form1.cs:行 34
    場所 System.Windows.Forms.Form.OnLoad()
    場所 System.Windows.Forms.Form.OnCreateControl()
    場所 System.Windows.Forms.Control.CreateControl()
    場所 System.Windows.Forms.Control.CreateControl()
    場所 System.Windows.Forms.Control.WmShowWindow()
    場所 System.Windows.Forms.Control.WndProc()
    場所 System.Windows.Forms.ScrollableControl.WndProc()
    場所 System.Windows.Forms.ContainerControl.WndProc()
    場所 System.Windows.Forms.Form.WmShowWindow()
    場所 System.Windows.Forms.Form.WndProc()
    場所 System.Windows.Forms.Control.ControlNativeWindow.OnMessage()
    場所 System.Windows.Forms.Control.ControlNativeWindow.WndProc()
    場所 System.Windows.Forms.NativeWindow.DebuggableCallback()
    場所 System.Windows.Forms.SafeNativeMethods.ShowWindow()
    場所 System.Windows.Forms.Control.SetVisibleCore()
    場所 System.Windows.Forms.Form.SetVisibleCore()
    場所 System.Windows.Forms.Control.set_Visible()
    場所 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner()
    場所 System.Windows.Forms.Application.ThreadContext.RunMessageLoop()
    場所 System.Windows.Forms.Application.Run()
    場所 getPersonsWithCharsInfo.Program.Main()
    場所 C:\Documents and Settings\custar\....\Program.cs:行 18
    場所 System.AppDomain._nExecuteAssembly()
    場所 System.AppDomain.ExecuteAssembly()
    場所 Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
    場所 System.Threading.ThreadHelper.ThreadStart_Context()
    場所 System.Threading.ExecutionContext.Run()
    場所 System.Threading.ThreadHelper.ThreadStart()
  InnerException:

 

 

Answer 1

GetDataByColumnId()の保存先であるデータテーブルに余計なキーが自動で出来ていませんか？　たまに私も消すことがあります。

ところで、テーブルの設計ですが、以下で良いように思えます。目的がわからないので違っていたらごめんなさい。

 

▼columns
columnsId
name

 

▼chars
charsId
columnsId
name

 

▼persons
personsId
charsId
name

 

外部キーは

columns(columnsId) -> chars(columnsId) -> persons(charsId)

Answer 2

（落ちる原因ではないですが）

テーブル構成自体がなんだか変なような気がします。
　　
　　テーブルcharsはcolumnsのメンバーじゃないの？（別テーブルにする意味があるのか）
　　　　　または
　　テーブルPersonsのcolumns_idはchars_idになるべきじゃないのかな？

参考になればと思います。

Answer 3

trapemiya さんからの引用

GetDataByColumnId() の保存先であるデータテーブルに余計なキーが自動で出 来ていませんか？

どうやって確認するのでしょう？

 

Designer.cs をのぞくと、

Code Snippet

public virtual syllabaryDataSet.personsDataTable GetDataByColumnId(int column_id) {
  this.Adapter.SelectCommand = this.CommandCollection[1];
  this.Adapter.SelectCommand.Parameters[0].Value = ((int)(column_id));
  syllabaryDataSet.personsDataTable dataTable = new syllabaryDataSet.personsDataTable();
  this.Adapter.Fill(dataTable);
  return dataTable;
}

 

となっています。これは personsDataTable のスキームに合うものしか返さな
いのではないか、と考えました。とすると、今回のように、そのスキームに合
わない column_id, column_name, char_id, char_name なども含ませる検索結
果では、出力できないのではないか、と。

そう思って、検索結果だけのためのテーブルを用意しましたが、今度は型が違
いますといわれてしまいます。当然でしょう。personsDataTable から別な
DataTable へのキャストですから。

で、手詰まりになってしまっています。

 

 

 

trapemiya さんからの引用

ところで、テーブルの設計ですが、以下で良いように思えます。目的がわから ないので違っていたらごめんなさい。   Code Snippet ▼columns columnsId <--+ name         |              | ▼chars      | charsId   <-----+ columnsId ---+  | name            |                 | ▼persons       | personsId ------+ charsId name   外部キーは columns(columnsId) -> chars(columnsId) -> persons(charsId)

意図は分かります。普通そうしますが、今回は c# 触り始めの頃から安直に作っ
たもので通しました。

ただ、chars_persons のようなテーブルを使うことがある場合に備えています。
同じ親を持つのだが、子同士の関係付けを保持するテーブルのようなものを。

web のフレームワークを扱っている際に、

• user がいて、
• その user がポストした記事 (blog) があり、
• その user はその記事に対してある複数のタグを付けた (tag)、

というものがありました。

 

記事もタグも同じ user に属しますが、記事とタグの関係は M 対 N です。
ある記事は複数のタグを付けられており、
あるタグは複数の記事に付けられている。

こんな場合、前記 web フレームワーク内では blog_id と tag_id の組み合わ
せを保持するテーブルを用意していました。それに合わせる簡単なものという
意図で、chars_persons なるテーブルを用意しました。.net でどう扱えるのか
を知る目的で。

Answer 4

indigo-x さんからの引用

テーブル構成自体がなんだか変なような気がします。 　　   テーブル chars は columns のメンバーじゃないの？（別テーブルにする意味があるのか）     または   テーブル Persons の columns_id は chars_id になるべきじゃないのかな？

「テーブル構成自体がなんだか変」は同意します。

50音の行 (column) から個々のひらがな (char) へ、そして人 (person) とい
う流れはごもっとも。

ですが敢えて、あ行に属するひらがなグループ (chars) と、あ行に属する人名
グループ (person) がそれぞれあり、それらを結びつける (chars_persons) と
やってみました。

クエリ ビルダーで検索結果が出るのに、TableAdapter では出ないって所に何
か引っ掛かるものを感じてます。

 

 

 

INNER JOIN を扱うサンプルを探しましたが、LINQ のものしか見つからず。

- 方法 : 内部結合を実行する (C# プログラミング ガイド)

他に見つかったものは、SqlDataAdapter を使ったものがありましたが、現在の
ADO では TableAdapter がそれを内包しているので特に問題はないと思ってい
ます。ですが、その結果をどう取り込めば良いのか、というところも分かって
いません。

Answer 5

実際に使っているソースを置きます。

 

ダウンロード : ソース

 

 

クエリ ビルダーの以下の最終メッセージが意味する通り、
GetDataByColumnId() で返される DataTable は schema が本当に違っているの
で、ここをどう処理すれば良いのでしょう。

 

参考画像

 

 

今回のミソは「INNER JOIN」を含んだクエリの扱い方なのではないかと思って
います。

英語版の警告メッセージ (多分)

Code Snippet

The new command text returns data with schema different from the schema of the main query.
Check your query's command text if this is not desired

 

英語版のエラーメッセージ (多分)

Code Snippet

Failed to enable constraints.
One or more rows contain values violating non-null, unique, or foreign-key constraints.

 

参考
- Updating the TableAdapter to Use JOINs : The Official Microsoft ASP.NET Site
- Question regarding TableAdapters. - MSDN Forums

Answer 6

新しい TableAdapter を用意しました。
間違ったことをしていたら、ご指摘ください。

 

確認画像 (4) デザイナで TableAdapter の追加

確認画像 (5) クエリ ビルダーで参考画像 (3) と同じに設定追加後

 

 

Code Snippet

var persons = columns_chars_personsTableAdapter1.GetDataByColumnId(1);

 

 

として実行。

確認画像 (6) 変数内のデータを確認

Answer 7

custar さんからの引用

ただ、chars_persons のようなテーブルを使うことがある場合に備えています。 同じ親を持つのだが、子同士の関係付けを保持するテーブルのようなものを。

 

同じ親を持つのであれば、

 

select chars.id, persons.id from chars
          inner join persons on chars.columns_id = persons.columns_id
         
とすれば、chars_personsと同じテーブルが得られますので、chars_personsは必要ありません。同じ親を持たない、つまり、chars.columns_idとpersons_column_idが違う組み合わせを保持したいという場合にはchars_personsは必要ですが、今回の仕様から言ってこれはあり得ないんじゃないかと思います。

 

ということは、とりあえず置いといて。

 

ご提示された以下のSQLで新しくテーブルアダプタを作って下さい。その結果、そのSQLの取得に一致するデータテーブルも自動的に作成されます。

 

SELECT          columns.id AS column_id, columns.name AS column_name, chars.id AS char_id,
                      chars.name AS char_name, persons.id AS person_id,
                      persons.name AS person_name
FROM            chars INNER JOIN
                      columns ON chars.column_id = columns.id INNER JOIN
                      persons ON columns.id = persons.column_id INNER JOIN
                      chars_persons ON chars.id = chars_persons.char_id AND
                      persons.id = chars_persons.person_id
WHERE           (columns.id = @column_id)

 

＃ ↑　おっ、できましたか。(^^

Answer 8

データベースの仕様は仰る通りです。

trapemiya さんからの引用

ご提示された以下の SQL で新しくテーブルアダプタを作って下さい。その結果、 その SQL の取得に一致するデータテーブルも自動的に作成されます。

まさしく仰る通りでした。
# 相変わらず良い読みしますね。

ということは、間違ったことはしてないようですね。一安心。

 

 

- Updating the TableAdapter to Use JOINs : The Official Microsoft ASP.NET Site

 

良い資料でした。途中まで読んでて、オヤヤ！？と思ったので試してみました。

やっぱり英語圏のドキュメントは充実してますね。

他にも面白そうなドキュメントがありました。

 

 

Visual Studio、知らない機能が沢山ありますねぇ。自分に必要なところのみを
msdn を頼りに、全く書籍も読まずにやってるからこうなるんですが。

 

trapemiya さん、indigo-x さん、アドバイスありがとうございます。

 

p.s.

----

タイトルが内容に対して不適当なので変更しておきます。