C#フォームでSQLite。他のDBでも通用するINSERT文とは…？

※※※※※※※※※※※※※※※※※※※※※
Qiitaと同様の投稿内容となっております。
ご容赦ください。
※※※※※※※※※※※※※※※※※※※※※

C#でSQLiteを扱うプログラムを書いていて、お偉いさんにもらった指摘からどう改良していけばいいかで詰んでるお話。

何をするのか

現存するテーブル「TABLE」に、
・ID：テーブル内の最高値の次の番号
・NAME：TextBoxから入力
・POINT1～3：NumericUpDownから入力
をINSERTでレコードとして追加していくだけなので、単純といえば単純。

とりあえず組んでみた

lang
1/// <summary>
2/// レコードを追加
3/// </summary>
4private void recordAdd()
5{
6    // 宣言
7    SQLiteConnection cn = null;
8
9    try
10    {
11        // DB接続文字列
12        string connectionString = string.Format("Data Source={0};Version=3;", Application.StartupPath + @"\result.db");
13
14        // DB Open
15        cn = new SQLiteConnection(connectionString);
16        cn.Open();
17
18        SQLiteCommand cmd = cn.CreateCommand();
19        cmd.CommandText = string.Format("INSERT INTO TABLE(ID, NAME, POINT1, POINT2, POINT3) SELECT MAX(ID) + 1, {0}, {1}, {2}, {3} FROM TABLE;",
20            "'" + this.textBox_Name.Text + "'",
21            this.numericUpDown_Point1.Value.ToString(),
22            this.numericUpDown_Point2.Value.ToString(),
23            this.numericUpDown_Point3.Value.ToString());
24        cmd.ExecuteNonQuery();
25    }
26    finally
27    {
28        // DBクローズ
29        if (cn != null)
30        {
31            cn.Close();
32        }
33    }
34}

…とまあ、こんな感じで。実行中も特にエラーは起きなかったので、これで提出すると…。

お偉いさんからの返事

SQLiteはプライマリーキーに数字が割り当てられている>と自動で一番大きな値+1をしてID発行するようになっている。
データがない場合MAXはnullになることを考慮しているかは不明だが、たまたまうまく動作している原因となっている。
nullを考慮したSQLもしくはプログラムを組んでほしい（他のDBでも同じようなSQLを書いてしまうのを防ぐため）。

…どゆこと？

MAX(ID) + 1
と書くのがよろしくないのか…？
テーブルが空であるときにIDの最高値を見てもnullだから、SQLiteはともかく他のDBで同様に動作する保証はないので、
他のDBでも問題なく動作しそうなコードに改良しろ、ということか…。

当然、INSERTするIDはDBから取得して決定するのが普通だろうから、
DBの中身が空であるかどうかを判定すればいいのか。

で、どっちをいじるべきかな…と迷いだす。
ちょっと考えればif文とかで強引に組めるんだろうけど、SQL文ちょっといじるだけで突破できるならそうしたいよねっていう。

…どなたかご教授ください…(;꒪ ω꒪)

追記 3/18 11:30

お偉いさんに、

IDを指定せずにオートインクリメントで付けいていくのは、仕様に甘えている。
たしかに普通はオートインクリメントを設定しておくものだが、
今回に限りその設定がないものとして考えてみてくれ。

とのお言葉をいただいて参りました。なんじゃそりゃ。

bm000999さんのコメントにある、ISNULL(MAX(ID) + 1, 0)を利用してみようと思いましたが、そのまま組み込んで、

lang
1INSERT
2  INTO TABLE
3  ( ID
4  , NAME
5  , POINT1
6  , POINT2
7  , POINT3 )
8SELECT
9  ISNULL(MAX(ID) + 1, 0)
10, {0} /* TextBox */
11, {1} /* NumericUpDown */
12, {2} /* NumericUpDown */
13, {3} /* NumericUpDown */
14FROM
15  TABLE
16;

としてみたのですが、try-catchで例外を返されてしまいました。

SQL logic error or missing database near "ISNULL": syntax error

あれ、クエリが間違ってる…？もしかしてINSERT文のときはISNULLを使えないとか…？
というかそもそもSQLiteにISNULLって存在するのか…？

行動規範の内容に同意します

回答3件

TABLEテーブルが適切に定義されていれば

lang
1cmd.CommandText = string.Format(
2  "INSERT INTO TABLE(NAME, POINT1, POINT2, POINT3) VALUES({0}, {1}, {2}, {3});",
3  "'" + this.textBox_Name.Text + "'",
4  this.numericUpDown_Point1.Value.ToString(),
5  this.numericUpDown_Point2.Value.ToString(),
6  this.numericUpDown_Point3.Value.ToString());

で動作する、という話だと思います。

投稿2015/03/17 13:07

hello-world

総合スコア1342

bm000999

2015/03/17 20:41

私も同意です。補足させて頂きます。たとえば、SQLserverに対して、データのないテーブルに、MAX(ID) + 1 ですとNULLが返って来てそのままnull + 1 でnullがインサートされます。ですので、ISNULL(MAX(ID)+ 1, 0)で対応できなくもないですが、ほぼ同時にインサートが発生した時など数値が重複する可能性があります。インクリメントはテーブルに委ねると整合性が保たれます。通常、列にシーケンスを定義します。インサート時は、インクリメント対象列は指定しません。各DBの設定方法については、[こちら](http://atamoco.boy.jp/oracle/20110414_2.php)を参考にしてください。そういった意味で、テーブルの対象の列を定義する必要があります。

行動規範の内容に同意します

ベストアンサー

結局、CASE文で書きました。

lang
1INSERT
2  INTO TABLE
3  ( ID
4  , NAME
5  , POINT1
6  , POINT2
7  , POINT3)
8SELECT
9  CASE
10    WHEN COUNT(*) = 0 THEN 1
11    ELSE MAX(ID) + 1
12  END
13, {0} /* TextBox */
14, {1} /* NumericUpDown */
15, {2} /* NumericUpDown */
16, {3} /* NumericUpDown */
17FROM
18  TABLE
19;

最初からこうすればよかった…。
なにはともあれ解決。結果もごくごく単純なものでした。

皆様、ご協力ありがとうございました。

投稿2015/03/18 04:03

退会済みユーザー

総合スコア0

takiru

2015/03/19 00:03

お偉いさんの言ってることに矛盾、もしくは余計なことばかり言ってますよね。『SQLiteは(その仕様上)、プライマリーキーに数字が割り当てられていると自動で一番大きな値+1をしてID発行するようになっている。』『IDを指定せずにオートインクリメントで付けいていくのは、仕様に甘えている。今回に限りその設定がないものとして考えてみてくれ。』だったら、最初の前置きはなんだったのか。余計なこと言ってないで、 SQLiteの仕様ではなく、業務仕様を伝えろよって感じです。ちなみにCASEだといちいち仰々しいので、 COALESCE(MAX(ID), 0) + 1 でいいと思います。

行動規範の内容に同意します