Go through TextGridView TextBox

1

How about, I have a Windows Forms application in which in a TextBox I simulate the name of a table, in another entry the rows that the table will have and in the remaining 4 the 4 columns that will contain the table.

This file generates a CSV file and a txt file with the SQL syntax to create the query. The problem is that I want to add the number of fields that are, it can be 1, 10, 100, and so on. Adding them in TextBox would be a bit complicated and I really can not find a way to do it.

Next I leave images of the generated files and the source code that is what is currently done. I hope you can help me.

The code I use is the following:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports System.IO

Public Partial Class MainForm

    Public Sub New()
        ' The Me.InitializeComponent call is required for Windows Forms designer support.
        Me.InitializeComponent()
    '
    ' TODO : Add constructor code after InitializeComponents
    '
    End Sub

    Dim nombres As String() = {"Aaron", "Abel", "Abelardo", "Abraham", "Adalberto", "Adolfo", "Adrian", "Agustin", "Alan", "Alejandro", "Benjamin", "Bernardo", "Baldomero", "Baltasar", "Barack", "Josh"}

    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"}

    Sub BtnCrearClick(sender As Object, e As EventArgs)
        Const separador = ";"
        Const folder = "C:\Users\desarrollo\Documents\CSV"
        Const titulo = "archivo.csv"
        Dim headers As String() = {txtID.Text, txtNombre.Text, txtApp.Text, txtApm.Text}
        Dim filePath = Path.Combine(folder, titulo)
        Dim sw As New StreamWriter(filePath, False, Encoding.UTF8)
        sw.WriteLine(String.Join(separador, headers))
        Dim aleatorio As New Random
        For i = 1 To Convert.ToInt32(txtFilas.Text)
            Dim fields As String() = {i.ToString(), nombres(aleatorio.Next(0, nombres.Length)), apellidos(aleatorio.Next(0, apellidos.Length)), paises(aleatorio.Next(0, paises.Length))}
            sw.WriteLine(String.Join(separador, fields))
        Next

        sw.Close
    '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 = "Apellidos"
    'row(txtNombre.Text)=apellidos(aleatorio.Next(0,apellidos.Length))
    'ElseIf cbo2.SelectedItem = "Paises"
    'row(txtNombre.Text)=paises(aleatorio.Next(0,paises.Length))
    'End If
    'If cbo3.SelectedItem="Nombres" Then
    'row(txtApp.Text)=nombres(aleatorio.Next(0,nombres.Length)) 
    'ElseIf cbo3.SelectedItem="Apellidos"
    'row(txtApp.Text)=apellidos(aleatorio.Next(0,nombres.Length))   
    'ElseIf cbo3.SelectedItem="Paises"
    'row(txtApp.Text)=paises(aleatorio.Next(0, paises.Length))
    'End If
    'If cbo4.SelectedItem="Nombres" Then
    'row(txtApm.Text)=nombres(aleatorio.Next(0,nombres.Length))
    'ElseIf cbo4.SelectedItem="Apellidos"
    'row(txtApm.Text)=apellidos(aleatorio.Next(0,nombres.Length))
    'ElseIf cbo4.SelectedItem="Paises"
    'row(txtApm.Text)=paises(aleatorio.Next(0,nombres.Length))
    'End If
    'TablaNombres.Rows.Add(row)
    'Next
    'tabla1.DataSource=TablaNombres
    'For i=1 To Convert.ToInt32(txtFilas.Text)
    'MsgBox("INSERT INTO " & txtTabla.Text & " (" & txtID.Text & ", " & txtNombre.Text & ", " & txtApp.Text & ", " & txtApm.Text & ") VALUES (" & i & ",'" & nombres(aleatorio.Next(0,nombres.Length)) &"','" & apellidos(aleatorio.Next(0,apellidos.Length)) &"','" & paises(aleatorio.Next(0,paises.Length)) &"')", MsgBoxStyle.OkOnly)
    'Next
    End Sub

    Sub BtnSQLClick(sender As Object, e As EventArgs)
        Const separador = ";"
        Const folder = "C:\Users\desarrollo\Documents\CSV"
        Const titulo = "archivo.txt"
        'Dim headers As String()={txtID.Text, txtNombre.Text, txtApp.Text, txtApm.Text}
        Dim filePath = Path.Combine(folder, titulo)
        Dim sw As New StreamWriter(filePath, False, Encoding.UTF8)
        'sw.WriteLine(String.Join(separador, headers))
        Dim aleatorio As New Random
        For i = 1 To Convert.ToInt32(txtFilas.Text)
            Dim insert As String = "INSERT INTO " & txtTabla.Text & " (" & txtID.Text & ", " & txtNombre.Text & ", " & txtApp.Text & ", " & txtApm.Text & ") VALUES (" & i & ",'" & nombres(aleatorio.Next(0, nombres.Length)) & "','" & apellidos(aleatorio.Next(0, apellidos.Length)) & "','" & paises(aleatorio.Next(0, paises.Length)) & "')"
            sw.WriteLine(String.Join(separador, insert))
        Next

        sw.Close
    End Sub

    Sub MainFormLoad(sender As Object, e As EventArgs)
    End Sub
End Class

Thanks in advance.

    
asked by Guillermo Ricardo Spindola Bri 21.07.2016 в 20:22
source

1 answer

1

You can use a control DataGridView (DGV) for entering the names of fields and data types:

Me.DataGridView1.Columns.Add("NombreCampo", "Nombre del campo")
Me.DataGridView1.Columns.Add("TipoDatos", "Tipo de datos")

Then you can create the schema of the table, then randomize and finally record the insert instructions:

Sub BtnSQLClick(sender As Object, e As EventArgs)
    ' Crea una DataTable según el esquema dado por el usuario a través del DataGridView.'
    Dim dt As DataTable = CreaTabla(Me.DataGridView1, txtTabla.Text)

    ' Crea una DataTable Random para los nombres, apellidos y países.'
    Dim dtRandom As DataTable = CreaDataTableRandom()

    ' Asigna los valores aleatorios a dt.'
    LLenaAlAzar(dtRandom, dt, Me.txtFilas.Text)

    ' Graba las instrucciones Insert.'
    GrabaInserts(dt)
End Sub

The user specifies the characteristics of the table in the DGV. Then with CreaTabla that table becomes a DataTable :

Private Function CreaTabla(dgvTabla As DataGridView, NombreTabla As String) As DataTable
    Dim tabla As DataTable = New DataTable(NombreTabla)

    For Each row As DataGridViewRow In dgvTabla.Rows
        ' No tenemos en cuenta la fila NewRow.'
        If row.IsNewRow Then
            Continue For
        End If

        ' Extraemos los nombres de los campos.'
        Dim column As DataColumn = tabla.Columns.Add(row.Cells("NombreCampo").Value.ToString)

        Select Case row.Cells("TipoDatos").Value.ToString.ToUpper
            Case "ENTERO"
                column.DataType = GetType(Integer)

            Case "TEXTO"
                column.DataType = GetType(String)
        End Select
    Next

    Return tabla
End Function

To randomize there are several options, one of which can be:

Private Function CreaDataTableRandom() As DataTable
    Dim nombres As String() = {"Aaron", "Abel", "Abelardo", "Abraham", "Adalberto", "Adolfo", "Adrian", "Agustin", "Alan", "Alejandro", "Benjamin", "Bernardo", "Baldomero", "Baltasar", "Barack", "Josh"}
    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 dt As DataTable = New DataTable("Random")

    dt.Columns.Add("nombre", GetType(String))
    dt.Columns.Add("apellido", GetType(String))
    dt.Columns.Add("pais", GetType(String))

    ' Se tiene en cuenta el superíndice más alto.'
    For i As Integer = 0 To Math.Max(nombres.Length, Math.Max(apellidos.Length, paises.Length)) - 1
        ' El operador Mod es necesario en caso que los vectores sean de diferente tamaño.'
        dt.Rows.Add(nombres(i Mod nombres.Length), apellidos(i Mod apellidos.Length), paises(i Mod paises.Length))
    Next

    Return dt
End Function


Private Sub LLenaAlAzar(dtOrigen As DataTable, dtDestino As DataTable, numFilas As Integer)
    Dim aleatorio As New Random

    For i As Integer = 1 To numFilas

        Dim dr As DataRow = dtDestino.NewRow

        ' Asignamos los valores aleatorios, desde dtOrigen hacia dtDestino.'
        For j As Integer = 0 To dtOrigen.Columns.Count - 1
            Dim filaRandom As Integer = aleatorio.Next(0, dtOrigen.Rows.Count)
            Dim nombreCampo As String = dtOrigen.Columns(j).ColumnName
            dr(nombreCampo) = dtOrigen.Rows(filaRandom)(j)
        Next

        dtDestino.Rows.Add(dr)
    Next
End Sub

The Insert instructions can be saved with:

Private Sub GrabaInserts(dt As DataTable)
    ' Molde para la sentencia Insert.'
    Dim moldeInsert As String = CreaMoldeInsert(dt)

    Dim valores As System.Text.StringBuilder = New System.Text.StringBuilder

    For Each row As DataRow In dt.Rows

        valores.Clear()

        For Each column As DataColumn In dt.Columns

            Dim valor As String = row(column).ToString

            If String.IsNullOrEmpty(valor.Trim) Then
                valores.Append("null")
            Else
                ' Encerramos el valor entre comillas, dependiendo del tipo de datos.'
                ' Usamos Select Case por si hubiesen varios tipos de datos que necesiten un formato especial.'
                Select Case column.DataType
                    Case GetType(Integer)   ' No necesita formato especial.'
                        valores.Append(valor)

                    Case GetType(String)    ' Necesita ir entre comillas simples.'
                        valores.Append("'")
                        valores.Append(valor)
                        valores.Append("'")
                End Select
            End If

            ' El último campo no lleva separador al final.'
            If column.Ordinal < dt.Columns.Count - 1 Then
                valores.Append(CONST_SEPARADOR)
            End If
        Next

        ' Insert completa.'
        Dim insert As String = moldeInsert.Replace("valores", valores.ToString)

        ' La salida es por consola, por motivos de depuración, pero se puede convertir fácilmente en flujo con el código de la pregunta.'
        debug.WriteLine(insert)
    Next
End Sub


Public Function CreaMoldeInsert(dt As DataTable) As String

    Dim campos As System.Text.StringBuilder = New System.Text.StringBuilder(dt.Columns(0).ColumnName)

    For i As Integer = 1 To dt.Columns.Count - 1
        campos.Append(",")
        campos.Append(dt.Columns(i).ColumnName)
    Next

    Return CONST_INSERT.Replace("tabla", dt.TableName).Replace("campos", campos.ToString)
End Function

The constants used are:

Const CONST_INSERT As String = "INSERT INTO tabla (campos) VALUES (valores)"
Const CONST_SEPARADOR As String = ","

It's the simplest thing I could do. Maybe another more advanced programmer can help simplify the code.

    
answered by 27.07.2016 / 01:54
source