.NET VisualStudioでSQLiteを使用する
今回は既存のOracleデータベースの選択したテーブルから、SQLiteにテーブルを作成しデータを移送するコードです。
データ移送がメンドイので作りました。自分用のメモです。
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 件のコメント:
コメントを投稿