2012年9月21日金曜日

.NET OracleデータベースのデータをSQLiteデータベースに移送する

前回、VisualStudioでSQLiteを使用できるようにしました。
.NET VisualStudioでSQLiteを使用する

今回は既存のOracleデータベースの選択したテーブルから、SQLiteにテーブルを作成しデータを移送するコードです。
データ移送がメンドイので作りました。自分用のメモです。

画面を表示するとリストボックスにOracleデータベースのテーブル一覧が表示されます。
SQLiteDB選択ボタンでデータを移送するSQLiteデータベースを選択してください。
データ移送ボタンで処理を実行します。

Imports System.Data.Common

Public Class FrmOracleToSqlite

    Private Const CONNECTION_STRING_PC As String = "user id=xxxxx;password=xxxxx;data source=xxxxx"
    Private Const PROVIDER_NAME_PC As String = "System.Data.OracleClient"

    Private _ConnectionStringAndroid As String = "data source={0}"
    Private Const PROVIDER_NAME_ANDROID As String = "System.Data.SQLite"

    Private Sub Form_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        SetTableNameList()
    End Sub

    Private Sub cmdSelectDb_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSelectDb.Click
        Using dialog As New OpenFileDialog
            If dialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Me._ConnectionStringAndroid = String.Format(Me._ConnectionStringAndroid, dialog.FileName)
                Me.lblDbNm.Text = Me._ConnectionStringAndroid
            End If
        End Using
    End Sub
   
    Private Sub cmdExec_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExec.Click
        If Me.lstTableName.CheckedItems.Count = 0 Then
            MessageBox.Show("テーブルを選択して下さい。")
            Return
        End If
        System.Windows.Forms.Cursor.Current = Cursors.WaitCursor
        For Each sTableName As String In Me.lstTableName.CheckedItems
            CreateTableForAndroid(sTableName)
        Next
        MessageBox.Show("終了~!")
    End Sub

    '-----Private-----

    Private Sub SetTableNameList()
        Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(PROVIDER_NAME_PC)
        Dim tbl As New DataTable
        Using cnn As DbConnection = factory.CreateConnection()
            cnn.ConnectionString = CONNECTION_STRING_PC
            cnn.Open()
            Try
                Using cmd As DbCommand = cnn.CreateCommand
                    cmd.CommandText = "select table_name from user_tables order by table_name"
                    Using adp As DbDataAdapter = factory.CreateDataAdapter
                        adp.SelectCommand = cmd
                        adp.Fill(tbl)
                    End Using
                End Using
            Finally
                cnn.Close()
            End Try
        End Using 'cnn

        For idx As Integer = 0 To tbl.Rows.Count - 1
            Me.lstTableName.Items.Add(tbl.Rows(idx).Item(0).ToString, False)
        Next
    End Sub
    

    Private Sub CreateTableForAndroid(ByVal sTableName As String)
        Dim factoryPC As DbProviderFactory = DbProviderFactories.GetFactory(PROVIDER_NAME_PC)
        Dim factoryAD As DbProviderFactory = DbProviderFactories.GetFactory(PROVIDER_NAME_ANDROID)

        'PC側テーブル情報を取得
        Dim tblSchema As New DataTable
        Dim tblPKey As New DataTable
        Using cnn As DbConnection = factoryPC.CreateConnection()
            cnn.ConnectionString = CONNECTION_STRING_PC
            cnn.Open()
            Try
                Using adp As DbDataAdapter = factoryPC.CreateDataAdapter
                    '--列情報
                    Using cmd As DbCommand = cnn.CreateCommand
                        adp.SelectCommand = cmd
                        Dim sbSql As New System.Text.StringBuilder
                        With sbSql
                            .AppendLine("select")
                            .AppendLine("   C.Column_Name")
                            .AppendLine("   ,C.Data_type")
                            .AppendLine("   ,C.Data_precision")
                            .AppendLine("   ,C.Data_scale")
                            .AppendLine("   ,C.Nullable")
                            .AppendLine("FROM USER_TAB_COLUMNS C")
                            .AppendLine("   INNER JOIN USER_TABLES T ON")
                            .AppendLine("       C.Table_Name = T.Table_Name")
                            .AppendLine("WHERE")
                            .AppendLine("   T.Table_Name='" & sTableName & "'")
                        End With
                        cmd.CommandText = sbSql.ToString
                        adp.Fill(tblSchema)
                    End Using
                    '--主キー情報
                    Using cmd As DbCommand = cnn.CreateCommand
                        adp.SelectCommand = cmd
                        Dim sbSql As New System.Text.StringBuilder
                        With sbSql
                            .AppendLine("SELECT")
                            .AppendLine("   B.COLUMN_NAME AS COL_NAME")
                            .AppendLine("FROM USER_CONSTRAINTS A")
                            .AppendLine("   LEFT JOIN USER_CONS_COLUMNS B ON")
                            .AppendLine("       B.TABLE_NAME = A.TABLE_NAME")
                            .AppendLine("       AND B.CONSTRAINT_NAME = A.CONSTRAINT_NAME")
                            .AppendLine("WHERE")
                            .AppendLine("   B.TABLE_NAME = '" & sTableName & "'")
                            .AppendLine("   AND A.CONSTRAINT_TYPE = 'P'")
                        End With
                        cmd.CommandText = sbSql.ToString
                        adp.Fill(tblPKey)
                    End Using
                End Using
            Finally
                cnn.Close()
            End Try
        End Using 'cnn

        'Android側にテーブル作成
        '--Drop文作成
        Dim sSqlDrop As String = "DROP TABLE " & sTableName
        '--主キーリスト作成
        Dim lstPKey As New List(Of String)
        For Each r As DataRow In tblPKey.Rows
            lstPKey.Add(r.Item("COL_NAME").ToString)
        Next
        '--Create文作成
        Dim sqlCreate As New System.Text.StringBuilder
        With sqlCreate
            .AppendLine("CREATE TABLE " & sTableName & "(")
            For idx As Integer = 0 To tblSchema.Rows.Count - 1
                If idx <> 0 Then
                    .Append(",")
                End If
                .Append(tblSchema.Rows(idx).Item("Column_Name").ToString)
                Select Case tblSchema.Rows(idx).Item("Data_type").ToString
                    Case "VARCHAR2", "DATE"
                        .Append("   TEXT")
                    Case "NUMBER"
                        If CInt(tblSchema.Rows(idx).Item("data_scale")) = 0 Then
                            .Append("   NUMBER")
                        Else
                            .Append("   REAL")
                        End If
                End Select
                If tblSchema.Rows(idx).Item("Nullable").ToString = "N" Then
                    .Append("   NOT NULL")
                End If
                .AppendLine("")
            Next
            sqlCreate.AppendLine(",PRIMARY KEY (" & String.Join(",", lstPKey.ToArray) & ")")
            sqlCreate.AppendLine(")")
        End With
        '--テーブル作成
        Using cnn As DbConnection = factoryAD.CreateConnection()
            cnn.ConnectionString = Me._ConnectionStringAndroid
            cnn.Open()
            Try
                Using adp As DbDataAdapter = factoryAD.CreateDataAdapter
                    Using cmd As DbCommand = cnn.CreateCommand
                        cmd.CommandText = sSqlDrop
                        Try
                            cmd.ExecuteNonQuery()
                        Catch ex As Exception
                            '何もしない
                        End Try

                        cmd.CommandText = sqlCreate.ToString
                        cmd.ExecuteNonQuery()
                    End Using
                End Using

            Finally
                cnn.Close()
            End Try
        End Using 'cnn

        'データ移送
        Const DATA_CNT As Integer = 100
        Dim iSt As Integer = 0
        Dim iEd As Integer = iSt + DATA_CNT - 1
        '--PC側データのSELECT文の作成
        Dim sqlSelect As New System.Text.StringBuilder
        With sqlSelect
            .AppendLine("SELECT TMPTABLE2.* FROM")
            .AppendLine(String.Format("(SELECT ROWNUM AS ROW_NUM, {0}.* FROM {0} ) TMPTABLE2", sTableName))
            .AppendLine("WHERE ROW_NUM >= :ROW_NUM_Sta")
            .AppendLine("AND   ROW_NUM <= :ROW_NUM_End")
        End With
        Dim cmdSelect As DbCommand = factoryPC.CreateCommand
        cmdSelect.CommandText = sqlSelect.ToString
        Dim pSt As DbParameter = factoryPC.CreateParameter
        pSt.ParameterName = "ROW_NUM_Sta"
        cmdSelect.Parameters.Add(pSt)
        Dim pEd As DbParameter = factoryPC.CreateParameter
        pEd.ParameterName = "ROW_NUM_End"
        cmdSelect.Parameters.Add(pEd)
        '--Android側のInsert文の作成
        Dim lstColNm As New List(Of String)
        Dim lstPrmmNm As New List(Of String)
        For Each row As DataRow In tblSchema.Rows
            lstColNm.Add(row.Item("Column_Name").ToString)
            lstPrmmNm.Add("@" & row.Item("Column_Name").ToString)
        Next
        Dim sqlInsert As New System.Text.StringBuilder
        With sqlInsert
            .AppendLine("INSERT INTO " & sTableName & "(")
            .AppendLine(String.Join(","c, lstColNm.ToArray))
            .AppendLine(")VALUES(")
            .AppendLine(String.Join(","c, lstPrmmNm.ToArray))
            .AppendLine(")")
        End With
        Dim cmdInsert As DbCommand = factoryAD.CreateCommand
        cmdInsert.CommandText = sqlInsert.ToString
        For Each colnm As String In lstColNm
            Dim prm As DbParameter = factoryAD.CreateParameter
            prm.ParameterName = colnm
            prm.SourceColumn = colnm
            prm.SourceVersion = DataRowVersion.Current
            cmdInsert.Parameters.Add(prm)
        Next
        '--データをSelectしInsertをDATA_CNT件づつ繰り返す
        Using cnnPC As DbConnection = factoryPC.CreateConnection()
            cnnPC.ConnectionString = CONNECTION_STRING_PC
            cnnPC.Open()
            Try
                Using cnnAd As DbConnection = factoryAD.CreateConnection()
                    cnnAd.ConnectionString = Me._ConnectionStringAndroid
                    cnnAd.Open()
                    Try
                        cmdSelect.Connection = cnnPC
                        cmdInsert.Connection = cnnAd
                        Do
                            '----Select実行
                            Dim tblSelect As New DataTable
                            Using adp As DbDataAdapter = factoryPC.CreateDataAdapter
                                adp.SelectCommand = cmdSelect
                                cmdSelect.Parameters(pSt.ParameterName).Value = iSt
                                cmdSelect.Parameters(pEd.ParameterName).Value = iEd
                                adp.Fill(tblSelect)
                            End Using
                            If tblSelect.Rows.Count = 0 Then
                                Exit Do
                            End If
                            '----取得したデータの行状態を追加にする
                            For Each row As DataRow In tblSelect.Rows
                                row.SetAdded()
                            Next
                            '----Insert実行
                            Using adp As DbDataAdapter = factoryAD.CreateDataAdapter
                                adp.InsertCommand = cmdInsert
                                adp.Update(tblSelect)
                            End Using
                            '----インクリメント
                            iSt = iEd + 1
                            iEd = iSt + DATA_CNT - 1
                        Loop

                    Finally
                        cnnAd.Close()
                    End Try
                End Using 'cnnAd
            Finally
                cnnPC.Close()
            End Try
        End Using 'cnnPC
    End Sub

End Class

0 件のコメント: