CODEWITHSUNDEEP
vb.net
Salvatore Fucito
Salvatore Fucito

Reputation: 355

How to store database resultset?

I want to know which is the best way to store database value in vb.net, For example, I've this code:

 Dim conn As MySqlConnection = Connection() 'Return a connection obj
 conn.Open()

 Dim transaction = conn.BeginTransaction
 Dim cmd = New MySqlCommand("SELECT * FROM tb ORDER BY ID")
 Dim ResultSet = Cmd.ExecuteReader

 If ResultSet.HasRows Then

    Do While ResultSet.Read

       'Here the value should be store

    Loop

 End if

How you can see I take all table from my database, now I want store each records in a structure that allow me to access it like the table. An array is good for do this? A list? Which is the best way?

Upvotes: 1

Views: 859

Answers (1)

Reza Aghaei
Reza Aghaei

Reputation: 125277

There is no best way. You have different options. For example:

  • As an option you can use a DataTable
  • Also you can store values in a generic List(Of T).

If you prefer working with DataTable it will be more simple to use DataAdapter to fill the DataTable. This way you can also create your List(Of T) from your DataTable.

Example of Using DataDapter to fill DataTable

Dim Connection = "Connection String"
Dim Command = "SELECT * FROM Category"
Dim Adapter = New SqlDataAdapter(Command, Connection)
Dim Table = New DataTable()
Adapter.Fill(Table)

This way the DataTable will have columns with name of columns in your result set.

Also you can fill your data in a List(Of T) using the DataTable this way:

Dim ListOfCategory = Table.Rows.Cast(Of DataRow) _
                          .Select(Function(Row)
                                     Return New Category With 
                                     {
                                         .Id = Row.Field(Of Integer)("Id"),
                                         .Name = Row.Field(Of String)("Name")
                                     }
                                  End Function).ToList()

Note

If can change the way you are writing your application, you can use Entity Framework as a good option to work with data.

Upvotes: 1

Related Questions