コメント |
@DIV
Imports System.Data.Odbc
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
Module MyModule
' ********************************************************
' 実行
' ********************************************************
Sub Main()
' ============================================
' @C:red(コンストラクタの呼び出し)
' ============================================
Dim lb As lightbox = New lightbox( _
"Driver={MySQL ODBC 3.51 Driver};" + _
"SERVER=localhost;" + _
"DATABASE=mydb;" + _
"UID=root;" + _
"PWD=" )
' ============================================
' @C:red(プログラムが存在するディレクトリを取得)
' ============================================
Dim pgDir As String = lb.GetProgramDir()
MessageBox.Show( pgDir )
' ============================================
' @C:red(変換したパスを初期ディレクトリとして設定)
' ============================================
Dim dlg As New Windows.Forms.OpenFileDialog
dlg.InitialDirectory = pgDir
dlg.Filter = "インポート用CSV|*.csv|全て|*.*"
dlg.FilterIndex = 2
dlg.RestoreDirectory = True
' 列名保存用配列
Dim aDataName As String()
' データ名用配列
Dim aDataValue As String()
' ファイルの中身用
Dim myStream As Stream
' SQL 作成用
Dim str As String = ""
' 存在チェック用
Dim strQuery As String = ""
' ============================================
' @C:red(ファイルを開くダイアログを表示)
' ============================================
If dlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
' ============================================
' @C:red(myStream としてファイルの中身を取得)
' ============================================
myStream = dlg.OpenFile()
If Not (myStream Is Nothing) Then
' ============================================
' @C:red(DB 接続)
' ============================================
if Not lb.Connect() then
myStream.Close()
MessageBox.Show( "接続失敗の為、終了します " )
End
end if
' ============================================
' @C:red(myStream を読む為の StreamReader クラスを作成)
' @C:red(ファイルは Shift_JIS なので明示する必要がある)
' ============================================
Dim sr As StreamReader = _
New StreamReader( _
myStream, _
Encoding.GetEncoding("Shift_JIS") _
)
' ============================================
' @C:red(ループ処理で行を結合して、SQL 文を作成)
' ============================================
Dim nCounter As Integer = 0
Dim I As Integer
Do While sr.Peek() >= 0
If nCounter = 0 Then
aDataName = lb.GetArray(sr.ReadLine(),",")
Else
aDataValue = lb.GetArray(sr.ReadLine(),",")
strQuery = "select * from 社員マスタ where "
strQuery += "社員コード = '" + aDataValue(0) + "'"
if lb.ExistData( strQuery ) then
' update 作成
str = "update 社員マスタ set "
For I = 0 To aDataName.Length - 1
' データ内のシングルクォートの処理
aDataValue(I) = aDataValue(I).Replace("'", "''")
If I <> 0 Then
str += ","
End If
If I < 4 Then
str += aDataName(I)
str += " = '"
str += aDataValue(I)
str += "'"
End If
If I = 4 Then
str += aDataName(I)
str += " = "
str += aDataValue(I)
End If
If I > 4 Then
str += aDataName(I)
str += " = '"
str += aDataValue(I)
str += "'"
End If
Next
str += " where 社員コード = "
str += "'" + aDataValue(0) + "'"
else
' insert 作成
str = "insert into 社員マスタ ("
For I = 0 To aDataName.Length - 1
If I <> 0 Then
str += ","
End If
str += aDataName(I)
Next
str += ") values("
For I = 0 To aDataName.Length - 1
' データ内のシングルクォートの処理
aDataValue(I) = aDataValue(I).Replace("'", "''")
If I <> 0 Then
str += ","
End If
If I < 4 Then
str += "'"
str += aDataValue(I)
str += "'"
End If
If I = 4 Then
str += aDataValue(I)
End If
If I > 4 Then
str += "'"
str += aDataValue(I)
str += "'"
End If
Next
str += ")"
end if
MessageBox.Show(str)
lb.UpdateDB(str)
End If
nCounter += 1
Loop
' ============================================
' @C:red(リーダとストリームを閉じる)
' ============================================
sr.Close()
myStream.Close()
' ============================================
' @C:red(接続解除)
' ============================================
lb.Close()
End If
End If
End Sub
' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' 処理クラス
' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Public Class lightbox
' 接続オブジェクト
Private Dim myCon As OdbcConnection = New OdbcConnection
' コマンドオブジェクト
Private Dim myCommand As OdbcCommand = New OdbcCommand
' 接続文字列
Public Dim strCon As String = String.Empty
' ********************************************************
' @C:green(コンストラクタの定義)( Sub で定義する )
' ********************************************************
Public Sub New( ByVal str As String )
strCon = str
End Sub
' ********************************************************
' @C:green(接続処理)
' ********************************************************
Public Function Connect() As Boolean
Connect = True
' 接続文字列セット
myCon.ConnectionString = strCon
' ====================================================
' 例外処理
' ====================================================
Try
' 接続
myCon.Open()
Catch ex As Exception
Connect = False
MessageBox.Show( ex.Message )
End Try
' コマンドオブジェクトを接続に関係付ける
myCommand.Connection = myCon
End Function
' ********************************************************
' @C:green(接続解除処理)
' ********************************************************
Public Sub Close()
myCon.Close()
End Sub
' ********************************************************
' @C:green(配列化関数)
' ********************************************************
Public Function GetArray(ByVal str As String,ByVal d As String) As String()
' 区切り文字定義
Dim delimStr As String = d
Dim delimiter As Char() = delimStr.ToCharArray()
' 分解
Dim aRet As String() = str.Split(delimiter)
Return aRet
End Function
' ********************************************************
' @C:green(存在チェック)
' ********************************************************
Public Function ExistData(ByVal strQuery As String) As Boolean
Dim myReader As OdbcDataReader
' コマンドセット
myCommand.CommandText = strQuery
' ====================================================
' 例外処理
' ====================================================
Try
' リーダーオブジェクト取得
myReader = myCommand.ExecuteReader()
Catch ex As Exception
myCon.Close()
MessageBox.Show( ex.Message )
End
End Try
ExistData = myReader.HasRows
myReader.Close()
End Function
' ********************************************************
' @C:green(更新関数)
' ********************************************************
Public Function UpdateDB(ByVal strExec As String) As Boolean
myCommand.CommandText = strExec
' ====================================================
' 例外処理
' ====================================================
Try
' 実行
myCommand.ExecuteNonQuery()
Catch ex As Exception
myCon.Close()
MessageBox.Show( ex.Message )
End
End Try
Return True
End Function
' ********************************************************
' @C:green(プログラムが実行されているディレクトリを取得)
' ********************************************************
Public Function GetProgramDir( ) As String
Dim ab As Reflection.Assembly = Reflection.Assembly.GetExecutingAssembly()
Dim md As Reflection.Module = ab.GetModules()(0)
' **************************************************************
' ディレクトリ部分を取得( System.IO.Path.GetDirectoryName が簡単 )
' **************************************************************
Dim aDataDir As String() = Getarray( md.FullyQualifiedName, "\" )
Dim pgDir As String = String.Join("\", aDataDir, 0, aDataDir.Length - 1)
Return pgDir
End Function
End Class
End Module
@END
|