how can i insert array 2 dimension data, insert in sql server data base

Question

_{Wednesday, September 25, 2019 4:46 AM}

hi

i have an array name  grid(row, col),  which have 20 row and 5 column, 

my requirements are

1.array take input from user

2. it is not necessary user fill the all 20 rows are 5 column.

3.when array is ready to put its value in sql server database 

MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

All replies (3)

_{Wednesday, September 25, 2019 10:03 AM}

Hi,

The array'data can from TextBox.Text or any other data that you input.Because it's simple, I don't provide the code to write to the 2 dimension array.

The main idea is to first put your two-dimensional array into the datatable, and then update the DataTable to the database. I use the SqlBulkCopy insertion method, which is relatively simple. As for other operation functions, you can add another one.

The results are as follows.

If you want this effect, you can try my code as follows.

Imports System.Data.SqlClient

Public Class Form1
    Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= D:\Local Database\190925.mdf"
    Dim dt As DataTable = New DataTable()

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim array3D As String(,) = {
            {"1", "Sara", "female", "21", ""},
            {"2", "Bela", "female", "21", ""},
            {"3", "Tina", "female", "21", ""},
            {"4", "Mark", "female", "21", ""},
            {"5", "Abby", "female", "21", ""},
            {"6", "Cola", "female", "21", ""}}
        DataGridView1.DataSource = Convert(New String() {"sno", "sname", "sex", "sage", "sremark"}, array3D)
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        DataTableToSQLServer(dt)
    End Sub

    Public Function Convert(ByVal ColumnNames As String(), ByVal Arrays As String(,)) As DataTable

        For Each ColumnName As String In ColumnNames
            dt.Columns.Add(ColumnName, GetType(String))
        Next

        For i1 As Integer = 0 To Arrays.GetLength(0) - 1
            Dim dr As DataRow = dt.NewRow()

            For i As Integer = 0 To ColumnNames.Length - 1
                dr(i) = Arrays(i1, i).ToString()
            Next

            dt.Rows.Add(dr)
        Next
        Return dt
    End Function

    Public Sub DataTableToSQLServer(ByVal dt As DataTable)

        Using conn As SqlConnection = New SqlConnection(constr)
            conn.Open()
            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(conn)
                Try
                    bulkCopy.DestinationTableName = "student"
                    bulkCopy.BatchSize = dt.Rows.Count
                    bulkCopy.ColumnMappings.Add("sno", "sno")
                    bulkCopy.ColumnMappings.Add("sname", "sname")
                    bulkCopy.ColumnMappings.Add("sex", "sex")
                    bulkCopy.ColumnMappings.Add("sage", "sage")
                    bulkCopy.ColumnMappings.Add("sremark", "sremark")
                    bulkCopy.WriteToServer(dt)
                    MsgBox("Insert Successful!")
                Catch ex As Exception
                    MsgBox(ex.Message)
                Finally
                End Try
            End Using
        End Using
    End Sub

End Class

Hope I can help you.

Best Regards,

Julie

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

---

_{Wednesday, September 25, 2019 10:54 AM}

Hello,

High level, create a class with properties which represent each column then iterate the data and place each row as an instance of the class into a list. 

Create a connection and command objects, create parameters for each column. Then in a for-each iterate the list, set parameter values then use command ExecuteNonQuery. Don't create parameters in the for-each but before the for-each.

It's important to use parameters to properly setup data types and to if escape things like single apostrophes in strings.

The following is for MS-Access but only difference between Access and SQL-Server is the data provider.

How to setup parameters. Example to insert many records.

SqlBulkCopy is overkill for less than say 500 records with less than 10 columns.

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

---

_{Wednesday, September 25, 2019 6:04 PM}

Hi Muhammad,

Your question seems so simple. However, we see already 2 replies. 

A third can be to serialize your data and put that as a string in your database.

If you retrieve it back you can then deserialize.

/en-us/dotnet/api/system.runtime.serialization.iserializable?redirectedfrom=MSDN&view=netframework-4.8

Success
Cor