Is it possible to insert a new record where the values are stored in a dictionary instead of a structure? I've seen this code:
Dim ord As New Order With _
{.OrderID = 12000, _
.ShipCity = "Seattle", _
.OrderDate = DateTime.Now}
' Add the new object to the Orders collection.
db.Orders.InsertOnSubmit(ord)
but I have 250 odd values/fields to add to a datatable for each record and would prefer to use a dictionary rather than define all the column fields and add values in a class like the example above. Can this be done?
dim dctValues as new dictionary(of string,integer)
key="val" & x
'loop through calc and increase x from 1 to 250
'key goes from "val1" to "val250"
dctValues.add(key,999)
...etc for 250 fields/columns for one record
db.Orders.InsertonSubmit(dctValues)
Suppose the InsertOnSubmit works well for every item in your dictionary, you could call it by For Each Loop like this
For Each item As MyNewClass In dct.Values
db.Orders.InsertonSubmit(item)
Next
The more difficult part would be to define MyNewClass and put that in your Dictionary to later process it easily
Dim dctValues As New Dictionary(Of String, MyNewClass) 'Note the Value is MyNewClass
Edit:
Using List, you will have to have first your MyNewClass still declared somewhere and takes care of three variables OrderID, ShipCity, and OrderDate.
Public Class MyNewClass
Public OrderID As Integer
Public ShipCity As String
Public OrderDate As Date
End Class
But now you do not need to put the Key when adding item:
Dim list As List(Of MyNewClass) 'List declaration, later will have 250 items
Then for each object you have, first put them in your List by for loop then put that to the data base with
For Each item As MyNewClass In list
db.Orders.InsertonSubmit(item) 'no need to submit per item, but using For Each loop
Next
Edit 2 (from comments):
If it is a DataTable we may still parse it (as one DataTable implies multiply data of the same class). To solve it, I think it is best still to declare a class which represents a row in your data table. But one row in your case is 250 columns. This is a lot. And there is no way to put the row correctly unless we know what is the type of each data. But since the number of data column is quite a lot, I could further suggest you to do one of the following two alternative options:
First way is to split the table into multiple tables whose data relation is in the Third Normal Form (please find more about this). This way you minimize the size of the data and will have good structure of the data relation. This way will make you end up with several smaller tables, but that's worth doing for sustainability and expandability. The only cost of Third Normal Form that people consider is usually the query speed. But 100 records of 250 columns will not produce this issue.
Second way is by using column info of your data table to get the data types for each individual column. Though the number of columns are plenty, the number of data type is more limited. Then, you need to create a generic way to map each data type to the produce data of equivalent type in C#. Then for all data in the 250 columns, you use this generic class' method to translate it to VB.Net datatype & vice versa according to your process.This method is more efficient and enduring, yet you might find a little trouble creating the generic class. But this way you don't need to change your table
Why normally people fix this issue more structurally with the second way is because of the reusability. Given new case with X columns and Y records, the previous solution can be used for new problem. But if you play with this one set of data a lot more, having the first way is not a bad choice. Alternatively, if neither the first nor the second suits your situation, then yes, you could take some pain to declare a class with 250 members