黄昏より暗きもの、血の流れより赤きもの

黄昏より暗きもの、血の流れより赤きもの

自分の好きな事を好きなように書いて行きます。

連想配列にセットされたキーと値から、SQLのInsert文を実行するスクリプト

お久しぶりです

皆さんお久しぶりです。自分はまあ派遣社員として現在働いています。今日は連想配列にセットされたキーと値から、SQLのInsert文を実行するスクリプトを紹介します。

昨今ではRuby On Railsと言ったWebフレームワークが盛んですね。配列に値をセットし、find()等を行なうと自動でSQLが呼ばれ、配列に変換してくれる関数などもあり、非常に作業が楽になったものです。

さてXCode(Objective-C)やVisual Studio.net(VB.NET , C#.NET) などでは、フォームやGUI部品はツールで十分に提供されている場合があります。ところがデータベース(Model)の内容を、フォームに反映する部分(Controller)は1から自作する必要がある場合も少なくありません。

今日は事情があってWebフレームワークが使えない、またはMVCで言うMとCの部分を自作する必要があるときで、SQLを自動生成するスクリプトを紹介していきます。尚 こちらを見てみると、Objective-Cの場合でも連想配列は使えるようなので書き換えて使う事が可能だと思います。

プログラム(SQL生成側)

以下ソースコードを紹介致します。具体的な説明はプログラムのコメントを見て頂ければと思います。

    ''' <summary>
    ''' Insert文を生成する
    ''' </summary>
    ''' <param name="TableName">テーブル名</param>
    ''' <param name="InsertHashTable">Insert文のキーと値</param>
    ''' <returns>Insertがうまくいったかを出力するフラグ</returns>
    Public Function Insert(TableName As String, InsertHashTable As Hashtable) As Boolean
        Dim Connection As New SQLiteConnection
        Dim Command As SQLiteCommand
        Dim TableSchema As SQLiteDataReader
        Dim TableSchemaHashTable As Hashtable = New Hashtable

        'SQL生成に必要な変数
        Dim InsertStr1 As String = "Insert Into " & TableName
        Dim InsertStr2 As String = vbNullString
        Dim InsertStr3 As String = vbNullString

        '最後に
        Dim InsertStr As String = vbNullString

        Insert = False
        '接続文字列を設定
        Connection.ConnectionString = SQLiteFilePath

        'オープン
        Connection.Open()

        'コマンド作成
        Command = Connection.CreateCommand

        ‘[1] テーブルの構造を取得
        ‘SQLiteの場合
        Command.CommandText = "PRAGMA table_info('" & TableName & "')"

        ‘MySQLの場合
        'Command.CommandText = "show full columns from “ & TableName

        TableSchema = Command.ExecuteReader()
        Command.Dispose()

        ‘[2] テーブル名一覧と型を取得。プログラム内で使いやすいように加工する
        Do Until Not TableSchema.Read
            'ハッシュテーブルの型を取得
            ‘MySQLなどの場合はカラムとカラムの定義が変わるので、書き換える
            TableSchemaHashTable(TableSchema("name").ToString) = TableSchema("type").ToString
        Loop

        'SQLのキーがあれば、インサート文を生成
        ‘Insert Into テーブル名 (…) の…部分をここで生成

        ‘ハッシュテーブルのキーの一覧を取得
        For Each key As String In InsertHashTable.Keys

            ‘ハッシュテーブルの一覧が、テーブルのカラムにあるときのみInsert文を追加
            If TableSchemaHashTable.ContainsKey(key) Then
                InsertStr2 = InsertStr2 & "," & key
            Else
            ‘テーブルのカラムに無いときは、エラーを返す
MessageBox.Show("DBのテーブルに無いカラム名が、連想配列のキーにセットされています。該当する連想配列のキー削除の上、もう一度実行して下さい")
                                   Insert = False
                                   Exit Function
                      End If
        Next

        ‘[3] Insert文のVALUES以下の部分を生成
        For Each key As String In InsertHashTable.Keys

        ‘SQLのテーブルの型の内、自分の使う物のみIf文で振り分けていく
            If TableSchemaHashTable(key) = "INTEGER" Then
                InsertStr3 = InsertStr3 & "," & InsertHashTable(key)
            ElseIf TableSchemaHashTable(key) = "NUMERIC" Then
                InsertStr3 = InsertStr3 & "," & InsertHashTable(key)
            ElseIf TableSchemaHashTable(key) = "REAL" Then
                InsertStr3 = InsertStr3 & "," & InsertHashTable(key)
            ElseIf TableSchemaHashTable(key) = "TEXT" Then
                InsertStr3 = InsertStr3 & "," & "'" & InsertHashTable(key) & "'"
            Else

                'エラーメッセージを表示(基本未使用)
                MessageBox.Show("テーブルのカラムの型が対応していません。(This function can use If type of table is only INTEGER , NUMERIC , REAL or TEXT.)")

                'Insertが失敗したことを示すフラグを返す
                Insert = False

                '処理を抜ける
                Exit Function

            End If
        Next

        '冒頭の,を削除
        InsertStr2 = InsertStr2.Substring(1)
        InsertStr3 = InsertStr3.Substring(1)

        'Insert文を結合
        InsertStr = InsertStr1 & " " & "(" & InsertStr2 & ")" & " " & "VALUES ( " & InsertStr3 & ")"

        'SQLをを実行、結果のフラグを返す
        Command.CommandText = InsertStr
        Insert = Command.ExecuteNonQuery()

        'Insertを終了する
        Command.Dispose()

    End Function

プログラム(SQL呼び出し側)

    ''' <summary>
    ''' 入出金リストを全て出力
    ''' </summary>
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ’ハッシュテーブルをセット
        Dim Test As Hashtable = New Hashtable

        'Insertがうまく行ったかを示すフラグ
        Dim InsertFlag As Boolean = False

        'テーブルのカラム名と値をセット
        Test("Column1") = 4
        Test("Column2") = "2016-06-01 00:00:00"
        Test("Column3") = 850

        'Insert文を実行
        InsertFlag = Insert("money", Test)

    End Sub

最後に

上記のスクリプトですが、使う人によっては使いにくい場合もあるかもしれません。又本日紹介した事は作業のための作業となります。このため実際の作業に取りかかる前にやらないと、プログラムの全ての部分の仕様が変わる恐れがあるのでご注意ください。

ソースコードコンパイルが通らない場合があるかもしれませんので、コピペして使用する際はご注意下さい。