|
' ******************************************************
' SQLServer ロード
' ******************************************************
Public Function LoadSqlServer(ByVal Query As String, _
Optional ByVal nMaxCount As Integer = 100) As Boolean
LoadSqlServer = True
Me.DbError = ""
' 接続文字列
_ConnectString = _
"Data Source=" & _DbServer & ";" + _
"Initial Catalog=" & _DbDatabase & ";" + _
"User ID=" & _DbUser & ";" + _
"Password=" & _DbPass & ";"
' 接続オブジェクト
Dim myCon As New SqlConnection()
myCon.ConnectionString = _ConnectString
Try
myCon.Open()
Catch e As Exception
DbError = e.Message
LoadSqlServer = False
Exit Function
End Try
' コマンドブジェクト
Dim myCommand As New SqlCommand()
myCommand.CommandText = Query
myCommand.Connection = myCon
' リーダ
Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
' 列数
Dim nCols As Integer = myReader.FieldCount
Dim idx As Integer
Dim fldName As String
Dim fldValue As String
Dim fldType As System.Type
' 列作成
Me.Reset()
For idx = 0 To nCols - 1
fldName = myReader.GetName(idx)
Me.AddColumn(fldName, fldName)
Next
Dim nRow As Integer = 0
Do While myReader.Read()
Me.AddRow()
nRow = nRow + 1
If nRow > nMaxCount Then
Exit Do
End If
For idx = 0 To nCols - 1
If Not myReader.IsDBNull(idx) Then
fldType = myReader.GetFieldType(idx)
If fldType.Name = "String" Then
fldValue = myReader.GetValue(idx) + ""
Me.SetColumnText(idx, fldValue)
ElseIf fldType.Name = "Int32" Then
fldValue = myReader.GetInt32(idx).ToString() + ""
Me.SetColumnText(idx, fldValue)
ElseIf fldType.Name = "DateTime" Then
fldValue = myReader.GetDateTime(idx).ToString() + ""
Me.SetColumnText(idx, fldValue)
Else
fldValue = myReader.GetValue(idx).ToString() + ""
Me.SetColumnText(idx, fldValue)
End If
Else
Me.SetColumnText(idx, "")
End If
Next
Loop
myReader.Close()
myCon.Close()
End Function
| |