VB.Net : How to insert a 2D array object to database?

My Task Background :

• I have list of data items and have to calculate/aggregate details about each data item and populate that to SQLSe开发者_开发知识库rver database.
• I can do 1 by 1 insert [Traditional method]

But, the application needs to perform good.

Is it possible to aggregate the data in a 2d array and dump into database @ once ?

' Populate this and insert it to database.
Private data(100, 100) As Object

So, how to do that ?

Also, is it possible to use dataset in some way ?

Sql server now supports arrays as arguments if you have 2005 or latter see here.

Yes, you can do this. You'll want to use the System.Data.SqlClient namespace for this. You'll need to open a connection by creating a connection string that looks something like this:

_connectionString = "packet size=4096;" & _
                        "integrated security=SSPI;" & _ 
                        serverName + ";" & _ 
                        "persist security info=False;" & _
                        "initial catalog=" + databaseName

Use that string to create an instance of an sqlConnection. From there, you can store sql queries in strings and pass them to an sqlCommand instance to be used for a query. Use your array to build a query string, something like...

query = "INSERT INTO MYTABLE " & _
        "VALUES('" & myArray(0,0) & "','" & myArray(1,0) & "')"

(My sql syntax may be a bit off) Look more into the namespace I mentioned above, and write yourself a helper class to handle the overhead for you. It will save you time in the long run and make your code a hell of a lot more readable.

You can easily add rows and columns to a dataset. Now once a dataset is ready, you can now serialize the dataset and store it in the DB. To read, just read from the db, deserialize and assign it to the dataset.

You will find lots of example on as to how to serialize-deserialize a dataset to XML and read it back into the dataset.

I did it :

by looping over to form my query, in the form described here : http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/