sqlite 将数据从数据库表移动到另一个表

9w11ddsr  于 12个月前  发布在  SQLite
关注(0)|答案(2)|浏览(280)

我用SQLite DB创建了一个VB.Net应用程序,并添加了一些数据,我可以执行所有的CRUD函数现在我回到应用程序,并添加了一个新的表TxArchiveData,其中包含以下代码注意,我在新表中省略了这部分代码

NOT NULL PRIMARY KEY AUTOINCREMENT

字符串
创建以下TxArchiveData TABLE的代码

Private Sub btnNewTable_Click(sender As Object, e As EventArgs) Handles btnNewTable.Click
        'create table TxArchiveDataTable String for cmd
        Dim create_table As String = String.Empty
        create_table = "CREATE TABLE IF NOT EXISTS TxArchiveData(
                        TID INTEGER,
                        txSortDate TEXT,
                        txAmount TEXT,
                        txYear INTEGER,
                        txSearchMonth INTEGER)"

        Dim dbTable As String = "TxArchiveData"

        If Not My.Computer.FileSystem.FileExists(dbTable) Then
            Try
                Using conn As New SQLiteConnection(connStr)
                    conn.Open()
                    Using cmd As New SQLiteCommand(create_table, conn)
                        cmd.ExecuteNonQuery()
                    End Using
                End Using
                tbMessage.Text = "TABLE Created Select BACK"
            Catch ex As Exception
                tbMessage.Text = "TxArchiveData Table FAILED"
            End Try
        End If


下面是创建TxData TABLE NOTE共享命名的代码

Public Sub makeTxData()

        'create table TxDataTable String for cmd
        Dim create_table As String = String.Empty
        create_table = "CREATE TABLE IF NOT EXISTS TxData(
                        TID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                        txSortDate TEXT,
                        txAmount TEXT,
                        txYear INTEGER,
                        txSearchMonth INTEGER)"

        Dim dbTable As String = "TxDataTable"

        If Not My.Computer.FileSystem.FileExists(dbTable) Then
            Try
                Using conn As New SQLiteConnection(connStr)
                    conn.Open()
                    Using cmd As New SQLiteCommand(create_table, conn)
                        cmd.ExecuteNonQuery()
                    End Using
                End Using
                tbMessage.Text = "DB Created Select BACK"
            Catch ex As Exception
                tbMessage.Text = "TxData Table FAILED"
            End Try
        End If

    End Sub


在另一个我没有打开SQLite DB的表单上,我放置了以下代码

Private Sub btnMoveData_Click(sender As Object, e As EventArgs) Handles btnMoveData.Click
        Dim TxData As DataTable = New DataTable()
        Dim TxArchiveData As DataTable = New DataTable()
        For Each row As DataRow In TxData.Rows
            TxArchiveData.ImportRow(row)
        Next
    End Sub


问题是我是否需要连接到DB以使IMPORT ROW工作,如果需要,如何连接?
我还尝试只移动与txYear列匹配的数据,将此功能添加到答案中将非常有帮助**
我已经走了这么远,仍然迷失在这里是代码

Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim TxData As New DataTable
        Dim TxArchiveData As New DataTable

        Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
            conn.Open()
            Using cmd As New SQLiteCommand("", conn)

                cmd.CommandText = "SELECT * FROM TxData"
                For Each dr As DataRow In TxData.Rows
                    TxArchiveData.ImportRow(dr)
                Next

            End Using
        End Using

    End Sub

piok6c0g

piok6c0g1#

这里有许多问题需要解决。
第一:
我需要连接到数据库才能让导入行工作吗?如果需要,如何连接?
不,你当然不需要。ImportRow把一个DataRow从一个DataTable复制到另一个。就是这样,仅此而已。DataTables和数据库之间没有直接连接。如何填充源DataTable以及如何处理目标DataTable的内容与ImportRow的使用无关。如果源数据来自一个数据库,那么很明显你需要连接到那个数据库来检索数据,但这是不相关的。
其次,没有必要也没有指向使用ImportRow。你只需要一个DataTable。你也只需要一个数据适配器。当你调用Fill时,数据适配器的SelectCommand被执行,当你调用Update时,InsertCommand可以被执行。没有什么说他们需要引用同一个数据库表,甚至是同一个数据库。你可以从一个表中SELECT数据,在另一个表中INSERT数据,例如。

Using connection As New SqlConnection("connection string here"),
      insertCommand As New SqlCommand("INSERT INTO Table2 (Col1, Col2) VALUES (@Col1, @Col2)", connection),
      adapter As New SqlDataAdapter("SELECT * FROM Table1", connection) With {.AcceptChangesDuringFill = False,
                                                                              .InsertCommand = insertCommand}
    With insertCommand.Parameters
        .Add("@Col1", SqlDbType.NVarChar, 50, "Col1")
        .Add("@Col1", SqlDbType.Int, 0, "Col2")
    End With

    connection.Open()

    Dim table As New DataTable

    adapter.Fill(table)
    adapter.Update(table)
End Using

字符串
需要注意的一点是AcceptChangesDuringFill属性的设置。当您将DataRow添加到DataTable时,其RowState默认为Added。当您调用Fill时,它将添加检索到的行,然后调用AcceptChanges,通过将AcceptChangesDuringFill设置为False,您可以确保所有行保持Added,准备插入。
话虽如此,你甚至不需要在应用中检索任何数据来填充新的数据库表。你可以使用SELECT INTO创建一个新表,从现有表中选择记录并将它们插入新表。如果目标表已经存在,你可以插入其中并使用查询作为源,而不是VALUES子句,例如:

INSERT INTO Table2 (Col1, Col2)
SELECT Col1, Col2 FROM Table1


您可以在命令对象上调用ExecuteNonQuery来执行这些选项中的任何一个,

nsc4cvqm

nsc4cvqm2#

试图使用ImportRow是错误的工具。
也就是说,我试图将数据存档在另一个表中,
只显示2年的数据。一些性能增强。
下面是移动数据的代码和防止
用户将相同的数据移动两次。

Private Sub DataPull()

    ' Represents a utility function for writing data from one table to another in the same database.
    '
    ' PARAMETERS:
    I()
    If count > 0 Then
        MessageBox.Show("That Year " + tbYear.Text + " Has Been Archived", "Warning", MessageBoxButtons.OK)
    End If
    Dim sourceTable As String = "TxData"
    Dim destinationTable As String = "TxArchiveData"

    ' EXCEPTION:
    ' Throws SqlException if there is an error executing the SQL query.

    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
        conn.Open()
        ' Create a SQL command to select all data from the source table.
        Using cmd As New SQLiteCommand("", conn)
            cmd.CommandText = $"SELECT * FROM {sourceTable} WHERE txYear =" & tbYear.Text
            ' Execute the select command and retrieve the data.
            Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
                ' Create a SQL command to insert the data into the destination table.
                Using cmd2 As New SQLiteCommand("", conn)
                    cmd2.CommandText = $"INSERT INTO {destinationTable} VALUES (@Column1, @Column2, @Column3,@Column4,@Column5)"
                    ' Prepare the insert command parameters.
                    cmd2.Parameters.Add("@Column1", DbType.Int64)
                    cmd2.Parameters.Add("@Column2", DbType.String)
                    cmd2.Parameters.Add("@Column3", DbType.String)
                    cmd2.Parameters.Add("@Column4", DbType.Int64)
                    cmd2.Parameters.Add("@Column5", DbType.Int64)

                    ' Loop through the data and insert it into the destination table.
                    While rdr.Read()
                        ' Set the parameter values.
                        cmd2.Parameters("@Column1").Value = rdr.GetInt64(0)
                        cmd2.Parameters("@Column2").Value = rdr.GetString(1)
                        cmd2.Parameters("@Column3").Value = rdr.GetString(2)
                        cmd2.Parameters("@Column4").Value = rdr.GetInt64(3)
                        cmd2.Parameters("@Column5").Value = rdr.GetInt64(4)

                        ' Execute the insert command.
                        cmd2.ExecuteNonQuery()
                        count = count + 1
                        tbInfo.Text = count.ToString
                    End While
                End Using
            End Using
            End Using
    End Using
End Sub
Private Sub I()
    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
        conn.Open()

        Using cmd As New SQLiteCommand("", conn)
            cmd.CommandText = "SELECT COUNT(*) FROM TxArchiveData WHERE txYear = " & tbYear.Text
            'Dim count As Integer Needs to be declard as toplevel variable
            'OR in a DataModule as gv_count it is used in DataPull() Sub
            cmd.Parameters.AddWithValue("@value", tbYear)
            count = CInt(cmd.ExecuteScalar())
        End Using
    End Using

End Sub

字符串

相关问题