Insertar registros de datatable en BBDD

Question

Buenas tardes.

Tengo un "datatable" que he llenado manualmente con varios registros. Ahora me gustaría copiar todos los registros del datatable en una base de datos Access que ya tengo creada, haciendo sólamente un comando INSERT.

Gracias.

Answer 1

La forma más sencilla de hacer la inserción es conectar un OleDbDataAdapter al DataTable y llamar al método Update del dataadapter.

Pero ojo, eso internamente te generará varios Inserts, uno por cada fila. Me temo que con Access no hay forma de meter las varias filas en un solo Insert. Con SQL Server y con MySql sí que se puede hacer, pero Access no soporta esa sintaxis. Tendrás que conformarte con que se transmitan múltiples inserts.

Dim da As New OleDbDataAdapter("select * from MiTabla", MiConexion)
Dim scb As New OleDbCommandBuilder(da)
da.Update(MiDataTable)

Answer 2

Hola:
Para este ejemplo he creado una tabla llamada prueba que esta en una bae llamada bd.accdb como la de la imagen

En un Form como el de la imagen

Copia y pega el siguiente código

Option Explicit On
Option Strict On
Imports System.Data.OleDb

Public Class Form2
    Private msCadenaACCESS As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\bd.accdb"
    Private mdtDataTable As New DataTable

    Private Sub Form21_Load(sender As Object, e As EventArgs) Handles Me.Load
        Me.DataGridView1.AllowUserToAddRows = False
        mdtDataTable.Columns.Add("id", GetType(Integer))
        mdtDataTable.Columns.Add("nombre", GetType(String))
        mdtDataTable.Columns.Add("fecha", GetType(Date))
        Me.DataGridView1.DataSource = mdtDataTable
    End Sub

    Private Sub btnAñadir_Click(sender As Object, e As EventArgs) Handles btnAñadir.Click
        'agregamos al datatable
        Dim row As DataRow = mdtDataTable.NewRow()
        row("id") = Me.txtId.Text
        row("nombre") = Me.txtNombre.Text
        row("fecha") = Me.dtpFecha.Value
        mdtDataTable.Rows.Add(row)
        '
        Me.txtId.Text = ""
        Me.txtNombre.Text = ""
        Me.dtpFecha.Value = Date.Today
    End Sub

    Private Sub btnGrabar_Click(sender As Object, e As EventArgs) Handles btnGrabar.Click
        If mdtDataTable.Rows.Count = 0 Then
            MessageBox.Show("NO hay filas para grabar")
            Return
        End If
        lP_DataTableToTable("prueba")
        mdtDataTable.Clear()
    End Sub

    Private Sub lP_DataTableToTable(ByVal vsTabla As String)
        Try
            Using loConexion As New OleDbConnection(msCadenaACCESS)
                loConexion.Open()   ' abrir conexión
                Dim loDataAdapter As OleDbDataAdapter = Nothing
                Dim cmdBuilder As OleDbCommandBuilder = Nothing
                loDataAdapter = New OleDbDataAdapter("SELECT * FROM " & vsTabla, loConexion)
                cmdBuilder = New OleDbCommandBuilder(loDataAdapter)
                '
                cmdBuilder.DataAdapter.Update(mdtDataTable)
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message, vsTabla, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub
End Class

Un saludo desde Bilbo
Carlos

Answer 3

Entonces cómo sería el código si hiciera la base de datos con Mysql? Me interesa que los 1000 o 2000 registros que pueda añadir se agreguen ràpidamente, entonces si con Mysql puedo conseguir que sea así, haría mi BBDD con Mysql.

Gracias.

Answer 4

Con MySql o con SQL Server escribirías una sentencia así:

Insert into laTabla(...campos...) VALUES (...), (...), (...)...

donde dentro de cada paréntesis va la lista de valores para un registro. Con SQL Server el límite es de 1000 de una sola vez; no sé qué limitaciones tiene MySql.

La sentencia la tendrías que generar recorriendo el datatable con un par de bucles anidados y concatenando los datos en un texto para formar la sentencia. No hay un automatismo para generar de forma automática una Insert con este estilo.

Alternativamente, podrías usar un "bulk insert", pero nótese que esto requiere pasar a través de un fichero:

https://social.technet.microsoft.com/wiki/contents/articles/25441.bulk-insert-into-mysql-database-using-c.aspx

Antes de ponerte a hacer cambios, prueba a jugar con el valor del BatchSize del DataAdapter usando el ejemplo inicial. Es posible que cambiando el tamaño del batch consigas un incremento de velocidad significativo.

Answer 5

Hola:
> Entonces cómo sería el código si hiciera la base de datos con Mysql <
Con MySql Y SQLServer, su pueden mandar mas de 1 registro cada vez, pero con las 2.
¿Porque MySql en vez de SQLServer Express, cuando ambas son gratuitas y con mas capacidad de almacenamiento que acces?
Te pongo un ejemplo en el que se insertan 1000 filas de un datatable en una tabla y se muestra el tiempo que dura el proceso para los 3 tipos de base dedatos

En un Form como el de la imagen

Copia y pega el siguiente codigo

Option Explicit On
Option Strict On
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports MySql.Data.MySqlClient

Public Class Form2
    Private mdtAccess As New DataTable
    Private mdtSQLServer As New DataTable
    Private mdtMySql As New DataTable
    Private msCadenaACCESS As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\bd.accdb"
    Private msCadenaSQLServer As String = "Data Source=.\SQLEXPRESS;Initial Catalog=TU_BASE_DE_DATOS;Integrated Security=True"

    Private msCadenaMySql As String = "Data source=127.0.0.1;userid=root;password='xxxxxx';database=TU_BASE_DE_DATOS;charset=utf8;"

    Private Sub Form21_Load(sender As Object, e As EventArgs) Handles Me.Load
        Me.dgvAccess.AllowUserToAddRows = False
        Me.dgvSQLServer.AllowUserToAddRows = False
        Me.dgvMySql.AllowUserToAddRows = False
        '
        mdtAccess.Columns.Add("id", GetType(Integer))
        mdtAccess.Columns.Add("nombre", GetType(String))
        mdtAccess.Columns.Add("fecha", GetType(Date))
        Me.dgvAccess.DataSource = mdtAccess
        '
        Dim lsNombre As String = String.Empty
        Dim ldFecha As Date = Date.Today
        Dim FilaAccess As DataRow = Nothing
        For liCiclo As Integer = 1 To 1000
            lsNombre = "Fila " & liCiclo.ToString
            ldFecha = DateAdd(DateInterval.Day, 1, ldFecha)
            '
            FilaAccess = mdtAccess.NewRow()
            FilaAccess("id") = liCiclo
            FilaAccess("nombre") = lsNombre
            FilaAccess("fecha") = ldFecha
            mdtAccess.Rows.Add(FilaAccess)
        Next
        mdtSQLServer = mdtAccess.Copy
        Me.dgvSQLServer.DataSource = mdtSQLServer
        mdtMySql = mdtAccess.Copy
        Me.dgvMySql.DataSource = mdtMySql
    End Sub

    Private Sub btnAccess_Click(sender As Object, e As EventArgs) Handles btnAccess.Click
        If mdtAccess.Rows.Count = 0 Then
            MessageBox.Show("NO hay filas para grabar")
            Return
        End If
        Dim lStopWatch As New Stopwatch
        lStopWatch.Start()
        Dim Duracion As TimeSpan
        lP_Access("prueba")
        lStopWatch.Stop()
        Duracion = lStopWatch.Elapsed
        Dim lsDiferencia As String = String.Format("{0:00}:{1:00}:{2:00}", Duracion.Hours, Duracion.Minutes, Duracion.Seconds)
        Me.lblAccess.Text = lsDiferencia
        Me.btnAccess.Enabled = False
        MessageBox.Show("Datatable Access grabado")
    End Sub

    Private Sub lP_Access(ByVal vsTabla As String)
        Try
            Using loConexion As New OleDbConnection(msCadenaACCESS)
                loConexion.Open()   ' abrir conexión
                Dim loDataAdapter As OleDbDataAdapter = Nothing
                Dim cmdBuilder As OleDbCommandBuilder = Nothing
                loDataAdapter = New OleDbDataAdapter("SELECT * FROM " & vsTabla, loConexion)
                cmdBuilder = New OleDbCommandBuilder(loDataAdapter)
                '
                cmdBuilder.DataAdapter.Update(mdtAccess)
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message, vsTabla, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub

    Private Sub btnSQLServer_Click(sender As Object, e As EventArgs) Handles btnSQLServer.Click
        If mdtAccess.Rows.Count = 0 Then
            MessageBox.Show("NO hay filas para grabar")
            Return
        End If
        Dim lStopWatch As New Stopwatch
        lStopWatch.Start()
        Dim Duracion As TimeSpan
        lP_SQLServer("prueba")
        lStopWatch.Stop()
        Duracion = lStopWatch.Elapsed
        Dim lsDiferencia As String = String.Format("{0:00}:{1:00}:{2:00}", Duracion.Hours, Duracion.Minutes, Duracion.Seconds)
        Me.lblSQLServer.Text = lsDiferencia
        Me.btnSQLServer.Enabled = False
        MessageBox.Show("Datatable SQLServer grabado")
    End Sub

    Private Sub lP_SQLServer(ByVal vsTabla As String)
        Try
            'Iniciamos una transaccion; hay que añadir la Referencia System.Transactions
            Using MiTransaccion As New Transactions.TransactionScope
                Using loConexion As New SqlConnection(msCadenaSQLServer)
                    loConexion.Open()   ' abrir conexión
                    Dim loDataAdapter As SqlDataAdapter = Nothing
                    Dim cmdBuilder As SqlCommandBuilder = Nothing
                    loDataAdapter = New SqlDataAdapter("SELECT * FROM " & vsTabla, loConexion)
                    'numero de registros que se envian a grabar en cada llamada desde al cliente a la base de datos
                    loDataAdapter.UpdateBatchSize = 100
                    cmdBuilder = New SqlCommandBuilder(loDataAdapter)
                    '
                    cmdBuilder.DataAdapter.Update(mdtMySql)
                End Using
                MiTransaccion.Complete()
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message, vsTabla, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub

    Private Sub btnMySql_Click(sender As Object, e As EventArgs) Handles btnMySql.Click
        If mdtMySql.Rows.Count = 0 Then
            MessageBox.Show("NO hay filas para grabar")
            Return
        End If
        Dim lStopWatch As New Stopwatch
        lStopWatch.Start()
        Dim Duracion As TimeSpan
        lP_MySql("prueba")
        lStopWatch.Stop()
        Duracion = lStopWatch.Elapsed
        Dim lsDiferencia As String = String.Format("{0:00}:{1:00}:{2:00}", Duracion.Hours, Duracion.Minutes, Duracion.Seconds)
        Me.lblMySql.Text = lsDiferencia
        Me.btnMySql.Enabled = False
        MessageBox.Show("Datatable MySql grabado")
    End Sub

    Private Sub lP_MySql(ByVal vsTabla As String)
        Try
            'Iniciamos una transaccion; hay que añadir la Referencia System.Transactions
            Using MiTransaccion As New Transactions.TransactionScope
                Using loConexion As New MySqlConnection(msCadenaMySql)
                    loConexion.Open()   ' abrir conexión
                    Dim loDataAdapter As MySqlDataAdapter = Nothing
                    Dim cmdBuilder As MySqlCommandBuilder = Nothing
                    loDataAdapter = New MySqlDataAdapter("SELECT * FROM " & vsTabla, loConexion)
                    'numero de registros que se envian a grabar en cada llamada desde al cliente a la base de datos
                    loDataAdapter.UpdateBatchSize = 100
                    cmdBuilder = New MySqlCommandBuilder(loDataAdapter)
                    '
                    cmdBuilder.DataAdapter.Update(mdtMySql)
                End Using
                MiTransaccion.Complete()
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message, vsTabla, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub
End Class

P.D.
El resultado es el siguiente

Un saludo desde Bilbo
Carlos

Answer 6

Muchas gracias.

Ahora sólamente me falta saber cómo escribir la cadena de conexión para conectar con la base de datos SQL, teniendo en cuenta que la base de datos la tengo guardada en "C:\documents\basedatos.sql"

Dim cadena as string= "  " 

Gracias por todo.

Answer 7

Hola:

Ahora sólamente me falta saber cómo escribir la cadena de conexión para conectar con la base de datos SQL, teniendo en cuenta que la base de datos la tengo guardada en "C:\documents\basedatos.sql

Vamos a ver los ficheros que componen las diferentes bases de datos

access (Viene de la instalacion de Office)

SQL Server (Tiene instalacion propia)

MySql (Tiene instalacion propia)

Lo que tu comentas no se parece en nada a estas imagenes

En el ejemplo que te puesto anteriormente tienes las 3 cadenas de conexión

 Private msCadenaACCESS As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\bd.accdb"
 Private msCadenaSQLServer As String = "Data Source=.\SQLEXPRESS;Initial Catalog=TU_BASE_DE_DATOS;Integrated Security=True"
 Private msCadenaMySql As String = "Data source=127.0.0.1;userid=root;password='xxxxxx';database=TU_BASE_DE_DATOS;charset=utf8;"
   
Un saludo desde Bilbo
Carlos