Public Function ReadData() As List(Of MyData)
Dim mdList As New List(Of MyData)
Dim con As New SQLiteConnection
Try
con.ConnectionString = My.Settings.ConnectionString
con.Open()
Dim sql As String = "SELECT * FROM TableName"
'Create a new SQL command
Dim cmd As SQLiteCommand = con.CreateCommand()
cmd.CommandText = sql
Dim reader As SQLiteDataReader = cmd.ExecuteReader()
Using reader
While reader.Read
Dim md As New MyData
With md
.Item1 = GetValue(reader, 0)
.Item2 = GetValue(reader, 1)
.Item3 = GetValue(reader, 2)
End With
'各行情報リストに記録する
mdList.Add(md)
End While
End Using
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
'Cleanup and close the connection
If Not IsNothing(con) Then con.Close()
End Try
Return mdList
End Function
Private Function GetValue(reader As SQLiteDataReader, Optional ordinalIndex As Integer = -1, Optional ordinalName As String = "") As Object
Try
Dim ordinal As Integer = -1
If Not String.IsNullOrEmpty(ordinalName) Then
ordinal = reader.GetOrdinal(ordinalName)
Else
ordinal = ordinalIndex
End If
If Not reader.IsDBNull(ordinal) Then
Dim dbType As Type = reader.GetFieldType(ordinal)
Select Case dbType
Case GetType(Boolean)
Return reader.GetBoolean(ordinal)
Case GetType(Byte)
Return reader.GetByte(ordinal)
Case GetType(Char)
Return reader.GetChar(ordinal)
Case GetType(DateTime), GetType(Date)
Return reader.GetDateTime(ordinal)
Case GetType(Decimal)
Return reader.GetDecimal(ordinal)
Case GetType(Double)
Return reader.GetDouble(ordinal)
Case GetType(Single)
Return reader.GetFloat(ordinal)
Case GetType(Guid)
Return reader.GetGuid(ordinal)
Case GetType(Int16), GetType(Short)
Return reader.GetInt16(ordinal)
Case GetType(Int32), GetType(Integer)
Return reader.GetInt32(ordinal)
Case GetType(Int64), GetType(Long)
Return reader.GetInt64(ordinal)
Case GetType(DataTable)
Return reader.GetSchemaTable(ordinal)
Case GetType(String)
Return reader.GetString(ordinal)
End Select
End If
Catch ex As Exception
Throw New Exception(ex.Message, ex)
End Try
Return Nothing
End Function
Public Class MyData
Public Property Item1 As String
Public Property Item2 As String
Public Property Item3 As String
End Class