myConnectString = _
	"Provider=MSDASQL;" & _
	"Driver={SQL Native Client};" & _
	"SERVER=NIGHT_TCP;" & _
	"DATABASE=lightbox;" & _
	"UID=sa;" & _
	"PWD=passwordpassword;"

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text

Module MyModule

' ********************************************************
' SQLExpress(SQLServer) への接続と処理
' ********************************************************
Sub Main()

	Dim myCon As SqlConnection = Nothing
	Dim myCon2 As SqlConnection = Nothing
	Dim myCommand As SqlCommand = New SqlCommand()
	Dim myReader As SqlDataReader = Nothing		
	Dim execCommand As SqlCommand = New SqlCommand()	' 更新用
	Dim myError As String
	Dim strValue As String

	' ******************************************************
	' 接続文字列
	' ******************************************************
	Dim myConnectString As String = _
		 "Data Source=NIGHT_TCP;" + _
		 "Initial Catalog=lightbox;" + _
		 "User ID=sa;" + _
		 "Password=passwordpassword;"

	' ******************************************************
	' 接続準備
	' ******************************************************
	myCon = New SqlConnection()	' 接続オブジェクト
	' 接続文字列をセット
	myCon.ConnectionString = myConnectString

	myCon2 = New SqlConnection()	' 接続オブジェクト
	' 接続文字列をセット
	myCon2.ConnectionString = myConnectString

	' ******************************************************
	' 接続
	' ******************************************************
	Try
		myCon.Open()
		myCon2.Open()
		' 接続が成功したら、コマンド用に接続をセット
		myCommand.Connection = myCon
		execCommand.Connection = myCon2
	Catch ex As Exception
		myCon = Nothing
		myError = "接続エラーです" + ControlChars.CrLf
		myError += "接続文字列=" + myConnectString + ControlChars.CrLf
		myError += "システムのメッセージ=" + ex.Message + ControlChars.CrLf
		Console.Out.WriteLine( myError )
		return
	End Try


	' ******************************************************
	' SQL をコマンドにセット
	' ******************************************************
	Dim Query As String = "select * from [社員マスタ]"
	myCommand.CommandText = Query

	' ******************************************************
	' SQL を実行
	' ******************************************************
	Try
		' このオブジェクトからフィールド情報を取得
		myReader = myCommand.ExecuteReader()
	Catch ex As Exception
		myError = ex.Message
		Console.Out.WriteLine( myError )
		return
	End Try

	' ******************************************************
	' 出力 CSV ファイル
	' ******************************************************
	Dim WriteFile As StreamWriter = New StreamWriter( _
		"社員マスタ.csv", _
		False, _
		Encoding.GetEncoding(932) _
	)

	' ******************************************************
	' タイトル部分の出力
	' ******************************************************
	Dim I As Integer
	strValue = ""
	For I = 0 To myReader.FieldCount - 1
		if strValue <> "" then
			strValue &= ","
		end if
		strValue &= myReader.GetName(I)
	Next
	WriteFile.WriteLine( strValue )

	' ******************************************************
	' データ部分の出力
	' ******************************************************
	Dim nCount As Integer = 0
	Do While myReader.Read()

		strValue = ""
		I = myReader.GetOrdinal("社員コード")
		strValue &= myReader.GetString( I )

		strValue &= ","
		I = myReader.GetOrdinal("氏名")
		strValue &= myReader.GetString( I )

		strValue &= ","
		I = myReader.GetOrdinal("所属")
		strValue &= myReader.GetString( I )

		strValue &= ","
		I = myReader.GetOrdinal("性別")
		' 整数
		strValue &= myReader.GetInt32( I ).ToString()

		strValue &= ","
		I = myReader.GetOrdinal("作成日")
		' 日付部分のみ
		strValue &= myReader.GetDateTime( I ).ToString("d")

		strValue &= ","
		I = myReader.GetOrdinal("更新日")
		strValue &= myReader.GetDateTime( I ).ToString()

		strValue &= ","
		I = myReader.GetOrdinal("給与")
		strValue &= myReader.GetValue( I ).ToString()

		' NULL データの可能性のあるデータのチェック
		I = myReader.GetOrdinal("手当")
		If myReader.IsDBNull( I ) Then
			strValue &= ","
		Else
			strValue &= ","
			strValue &= myReader.GetValue( I ).ToString()
		End If

		' NULL データの可能性のあるデータのチェック
		I = myReader.GetOrdinal("管理者")
		If myReader.IsDBNull( I ) Then
			strValue &= ","
		Else
			strValue &= ","
			strValue &= myReader.GetString( I )
		End If

		' NULL データの可能性のあるデータのチェック
		I = myReader.GetOrdinal("生年月日")
		If myReader.IsDBNull( I ) Then
			strValue &= ","
		Else
			strValue &= ","
			strValue &= myReader.GetDateTime( I ).ToString("d")
		End If
		WriteFile.WriteLine( strValue )

		' ******************************************************
		' 更新処理
		' ******************************************************
		Query = "update [社員マスタ] set 生年月日 = "
		Query &= " '2005/01/0" & ((nCount Mod 5)+1).ToString() & "'"
		Query &= " where 社員コード = '"
		Query &= myReader.GetString( myReader.GetOrdinal("社員コード") )
		Query &= "'"

		execCommand.CommandText = Query
		Try
			execCommand.ExecuteNonQuery()
		Catch ex As Exception
			myError = ex.Message
			Console.Out.WriteLine( myError )
		End Try


		nCount += 1

	Loop

	' ******************************************************
	' CSV の後処理
	' ******************************************************
	WriteFile.Close()
	WriteFile.Dispose()

	' ******************************************************
	' 読み取りオブジェクトが閉じていない場合は、閉じる
	' ******************************************************
	If Not myReader Is Nothing Then
		If Not myReader.IsClosed Then
			myReader.Close()
		End If
	End If

	' ******************************************************
	' 接続解除
	' ******************************************************
	If Not myCon Is Nothing Then
		' 接続を閉じる
		If myCon.State = ConnectionState.Open Then
			myCon.Close()
			myCon2.Close()
		End If

		' リソースの開放
		myCon.Dispose()
		myCon2.Dispose()
		myCommand.Dispose()
		execCommand.Dispose()

		' 次に備えて初期化
		myCon = Nothing
	End If

End Sub

End Module