Insert DataTable in SQL Table

0

Good morning, how could I insert the following code into the database? I hope you can help me.

    Sub BtnCrearClick(sender As Object, e As EventArgs)
    Dim IDColumna As New DataColumn(txtID.Text)
    IDColumna.DataType=GetType(Integer)
    IDColumna.AutoIncrement=True

    Dim Nombre As New DataColumn(txtNombre.Text)
    Nombre.DataType=GetType(String)

    Dim App As New DataColumn(txtApp.Text)
    App.DataType=GetType(String)

    Dim Apm As New DataColumn(txtApm.Text)
    Apm.DataType=GetType(String)

    Dim TablaNombres As New DataTable(txtTabla.Text)
    TablaNombres.Columns.Add(IDColumna)
    TablaNombres.Columns.Add(Nombre)
    TablaNombres.Columns.Add(App)
    TablaNombres.Columns.Add(Apm)

    TablaNombres.Constraints.Add("Key1", IDColumna, True)

    Dim nombres As String()={"Aaron","Abel","Abelardo","Abraham","Adalberto","Adolfo","Adrian","Agustin","Alan","Alejandro",
        "Benjamin","Bernardo","Baldomero","Baltasar","Barack"}
    Dim apellidos As String()={"Pineda", "Bernal", "Espinoza", "Spindola", "Brisuela", "Gutierrez", "Escarcega", "Muñiz", "Lopez", "Martinez", "Piña",
        "Vega", "Ortiz", "Barcenas", "Lopez", "Martinez"}
    Dim paises As String()={"Estados Unidos","Mexico","Costa Rica","Jamaica","Panama","Haiti","Colombia","Venezuela","Ecuador","Peru",
        "Bolivia","Chile","Brasil","Uruguay","Paraguay","Argentina"}
    Dim aleatorio As New Random

    Dim row As DataRow
    Dim i As Integer
    For i=1 To Convert.ToInt32(txtFilas.Text)
        row=TablaNombres.NewRow()
        row(txtID.Text)=i
        If cbo2.SelectedItem = "Nombres" Then
            row(txtNombre.Text)=nombres(aleatorio.Next(0,nombres.Length))
        ElseIf cbo2.SelectedItem = "A.Paterno"
            row(txtNombre.Text)=apellidos(aleatorio.Next(0,apellidos.Length))
        ElseIf cbo2.SelectedItem = "Países"
            row(txtNombre.Text)=paises(aleatorio.Next(0,paises.Length))
        End If
        row(txtApp.Text)=nombres(aleatorio.Next(0,nombres.Length))          
        row(txtApm.Text)=apellidos(aleatorio.Next(0,nombres.Length))
        TablaNombres.Rows.Add(row)
    Next

    tabla1.DataSource=TablaNombres
End Sub
    
asked by Guillermo Ricardo Spindola Bri 08.06.2016 в 02:52
source

1 answer

1

Since you have a table [PERSON]

CREATE TABLE [dbo].[Persona](
    [ID] [int] NOT NULL,
    [Nombre] [varchar](50) NOT NULL,
    [App] [varchar](50) NOT NULL,
    [Apm] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Persona] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) ON [PRIMARY]
)

Create a data type in SQL server that supports the structure of the table:

    CREATE TYPE dbo.TipoDatoTabla AS TABLE 
(   [ID] [int] NOT NULL,
    [Nombre] [varchar](50) NOT NULL,
    [App] [varchar](50) NOT NULL,
    [Apm] [varchar](50) NOT NULL
)
GO

StoreProcedure to insert the data:

CREATE PROCEDURE dbo.DataTable_To_SQL (@LaTabla dbo.TipoDatoTabla READONLY)
AS
    INSERT dbo.Persona(ID, Nombre, App, Apm)
    SELECT ID, Nombre, App, Apm 
    FROM    @LaTabla;
GO

Finally execute your code which I add the call to the Store Procedure at the end:

  Sub BtnCrearClick(sender As Object, e As EventArgs) Handles btnCrear.Click
        Dim IDColumna As New DataColumn(txtID.Text)
        IDColumna.DataType = GetType(Integer)
        IDColumna.AutoIncrement = True

        Dim Nombre As New DataColumn(txtNombre.Text)
        Nombre.DataType = GetType(String)

        Dim App As New DataColumn(txtApp.Text)
        App.DataType = GetType(String)

        Dim Apm As New DataColumn(txtApm.Text)
        Apm.DataType = GetType(String)

        Dim TablaNombres As New DataTable(txtTabla.Text)
        TablaNombres.Columns.Add(IDColumna)
        TablaNombres.Columns.Add(Nombre)
        TablaNombres.Columns.Add(App)
        TablaNombres.Columns.Add(Apm)

        TablaNombres.Constraints.Add("Key1", IDColumna, True)

        Dim nombres As String() = {"Aaron", "Abel", "Abelardo", "Abraham", "Adalberto", "Adolfo", "Adrian", "Agustin", "Alan", "Alejandro",
            "Benjamin", "Bernardo", "Baldomero", "Baltasar", "Barack"}
        Dim apellidos As String() = {"Pineda", "Bernal", "Espinoza", "Spindola", "Brisuela", "Gutierrez", "Escarcega", "Muñiz", "Lopez", "Martinez", "Piña",
            "Vega", "Ortiz", "Barcenas", "Lopez", "Martinez"}
        Dim paises As String() = {"Estados Unidos", "Mexico", "Costa Rica", "Jamaica", "Panama", "Haiti", "Colombia", "Venezuela", "Ecuador", "Peru",
            "Bolivia", "Chile", "Brasil", "Uruguay", "Paraguay", "Argentina"}
        Dim aleatorio As New Random

        Dim row As DataRow
        Dim i As Integer
        For i = 1 To Convert.ToInt32(txtFilas.Text)
            row = TablaNombres.NewRow()
            row(txtID.Text) = i
            If cbo2.SelectedItem = "Nombres" Then
                row(txtNombre.Text) = nombres(aleatorio.Next(0, nombres.Length))
            ElseIf cbo2.SelectedItem = "A.Paterno" Then
                row(txtNombre.Text) = apellidos(aleatorio.Next(0, apellidos.Length))
            ElseIf cbo2.SelectedItem = "Países" Then
                row(txtNombre.Text) = paises(aleatorio.Next(0, paises.Length))
            End If
            row(txtApp.Text) = nombres(aleatorio.Next(0, nombres.Length))
            row(txtApm.Text) = apellidos(aleatorio.Next(0, nombres.Length))
            TablaNombres.Rows.Add(row)
        Next

        'Inserta el DataTable en la tabla'
        Using oCon As New SqlClient.SqlConnection(New SqlClient.SqlConnectionStringBuilder With {.DataSource = ".", .InitialCatalog = "DBPersona", .IntegratedSecurity = True}.ConnectionString)
            oCon.Open()
            Using oCmd As New SqlClient.SqlCommand("DataTable_To_SQL", oCon)
                With oCmd
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.AddWithValue("@LaTabla", TablaNombres)
                    Call .ExecuteNonQuery()
                End With
            End Using
        End Using
    End Sub
    
answered by 06.07.2016 в 09:06