将NewRow添加到.mdb问题 [英] Add NewRow to .mdb problems

问题描述

大家好，

使用oledb连接将表加载到mdb文件时，出现内存不足异常.该表有超过60万条记录，文件大小不会超过150 mb.我们的服务器中有该空间.仍然给我们例外.

有人可以告诉我解决方案吗?请在下面找到代码.

Hi Guys,

I''m getting an Outofmemory Exception while loading a table into an mdb file using oledb connection. the table is having more than 6 lakh records and the file size will not 150 mb. we have that space in our server. still it is giving us exception.

Can any one tell me the solution for this? Please find the code below.

Dim SqlDs As New DataSet
        Dim Conn As New SqlConnection(SQLNativeConnStr)
        Conn.Open()
        Dim SQLStr As String = "SELECT * FROM " & TableName & ""
        Dim SqlAdp As SqlDataAdapter = New SqlDataAdapter(SQLStr, Conn)
        SqlAdp.Fill(SqlDs)
        Conn.Close()
        Dim dt As DataTable = SqlDs.Tables(0)

        'Dim OleDbCon As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileLoadPath & ";")
        'OleDbCon.Open()
        'Dim OleDbCmd As OleDbCommand = New OleDbCommand(MdbTableScript, OleDbCon)
        'OleDbCmd.ExecuteNonQuery()
        'OleDbCon.Close()

        Dim SqlQuery As String = "SELECT * FROM [" & TableName & "]"
        Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileLoadPath & ";"
        Dim Adapter As New OleDbDataAdapter(SqlQuery, ConnString)
        Dim CmdBuilder As New OleDbCommandBuilder(Adapter)
        CmdBuilder.QuotePrefix = "["
        CmdBuilder.QuoteSuffix = "]"
        Dim ds As New DataSet
        Adapter.FillSchema(ds, SchemaType.Source)
        ds.Tables(0).TableName = "Temp"

        For i As Integer = 0 To dt.Rows.Count - 1
            Dim RowValues(ds.Tables(0).Columns.Count - 1) As Object
            For j As Integer = 0 To ds.Tables(0).Columns.Count - 1
                If dt.Rows(i)(j).ToString().ToUpper() = "" Then
                    If ds.Tables(0).Columns(j).DataType.ToString() = "System.String" Then
                        RowValues(j) = ""
                    Else
                        RowValues(j) = 0
                    End If
                Else
                    RowValues(j) = dt.Rows(i)(j).ToString()
                End If
            Next
            ds.Tables(0).Rows.Add(RowValues)
        Next
        Adapter.Update(ds, "Temp")
        Adapter.Dispose()

在此先感谢

问候，
Srinivas.

Thanks in advance

Regards,
Srinivas.

推荐答案

您正在使用DataAdapter填充SQL数据库中的DataTable，并且您希望系统在内存中保留超过6,000,000条记录?单个记录有多大?做数学...

一种更合适的方法是创建一个SSIS(SQL Server集成服务)作业，为您导出数据.

或者，您可以重新编码您的应用程序以使用DataReaders而不是DataTables. DataReader将一次从数据库中读取一条记录，并允许您在读取它们时在新数据库中创建记录.除磁盘空间外，可以使用的记录数量没有限制.

You''re using a DataAdapter to fill a DataTable from the SQL database, and you expect the system to hold over 6,000,000 records IN MEMORY?? How big is a single record?? Do the math...

A more appropriate way to do this would be to create a SSIS (SQL Server Integration Services) job to export the data for you.

Or, you could recode your app to use DataReaders instead of DataTables. The DataReader will read one record at a time from the database and allow you to create the record in the new database, as you read them. There is no limit to the number of records you can do this with, except for disk space.

这篇关于将NewRow添加到.mdb问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！