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

  • Question

  • 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

    Wednesday, September 25, 2019 4:46 AM

All replies

  • 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:03 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
    profile for Karen Payne on Stack Exchange

    Wednesday, September 25, 2019 10:54 AM
  • 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.

    https://docs.microsoft.com/en-us/dotnet/api/system.runtime.serialization.iserializable?redirectedfrom=MSDN&view=netframework-4.8


    Success
    Cor

    Wednesday, September 25, 2019 6:04 PM