将数据表记录更新到Access数据库中 [英] update datatable records into access database

问题描述

我已将数据动态添加到数据表中，如何将这些数据插入到访问数据源中.

该数据表将具有5条以上的记录，是否有可能一次将所有5条记录插入到访问数据库中，而不是使用ExecuteNonQuery()5次.

我尝试使用dataadapter，但是我猜我做错了.

有人可以指导我正确的方向吗?
这是我正在尝试的代码.

Hi,

I have data dynamically added into a datatable, how would i insert that data into an access datasource.

The datatable would have more than 5 records, is it possible to insert all the 5 records into the access database in one go rather than using ExecuteNonQuery() 5 times.

I tried using dataadapter, but guess im doing something wrong.

Could someone guide me in the right direction please?
This is the code i was trying.

 Dim da As New OleDbDataAdapter("SELECT * FROM tbl_marq", sqlcon)
Dim dt As DataTable = New DataTable
 Dim ds As DataSet = New DataSet
 For Each c As Control In contentPage.Controls
                Dim txt1 As TextBox = TryCast(contentPage.FindControl("txt_upd" & txt_no), TextBox)
                If txt1 IsNot Nothing Then
                    If Not txt1.Text = "" Then
                         row1(txt_no) = dt.NewRow()
                         row1(txt_no)(0) = txt1.Text
                         dt.Rows.Add(row1(txt_no))

                    End If
                End If
                txt1 = Nothing
                txt_no = txt_no + 1
            Next
            dt.TableName = "marq"
            ds.Tables.Add(dt)
            da.Update(ds, "tbl_marq")

问候
Joe

regards
Joe

推荐答案

有很多解决方案，从简陋的结构到优雅的结构.我发现，随着项目的发展，建立良好的实践已经带来了巨大的好处.编译器还会为您完成很多繁琐的工作.

我强烈建议您在 http://msdn.microsoft.com/zh-CN/上阅读您所选择的语言的教程1. library/aa581769 [ ^ ]和从那里建造.关于创建健壮的n-teir架构的整个文档都很好地组合在一起.

尽管它一定程度上是针对主要应用程序的，但是一旦您了解了原理，它实际上对于快速和简单的站点也确实非常有效.在VWD(或VS)中为简单表添加声明性数据集需要花费几秒钟的时间，并且您可以获得可用于访问表的完整CRUD方法.第一次弄清楚就花了一点时间，但没有痛苦，也没有收获，正如他们所说.;-)

我现在的习惯是:
所有写操作都是通过在构建器UI中以声明方式构建的DataSet完成的.这使初始TableAdapter的开发和维护(字面拖放)以及对这些表的增强查询变得更加容易.

几乎所有程序化读取操作都是相同的.但是我主要建立一个包装数据库访问的类的BLL(业务逻辑层)，因此页面开发仅是实例化相关对象的问题-数据连接性很好地与BLL隔离.我相当广泛地将DataSource控件用于页面构建，但仅用于只读访问.让用户进行行选择并添加更多有意义的控件，以通过BLL从那里进行插入或更新.

这是一种可靠的设计模式，意味着我一直都知道在哪里可以找到我的数据访问代码.同样，所有更改都会使用BLL类自动传播到所有页面，而不必为了适应少量更改(例如新列等)而对多个页面进行大量重构.

Alistair

There are many solutions from down and dirty to elegantly structured. I have found that establishing good practices has paid huge dividends as projects grow. Also a lot of the grunt work is done for you by the compiler.

I highly recommend reading Tutorial 1 for your language of choice at http://msdn.microsoft.com/en-us/library/aa581769[^] and building from there. The whole documentation around creating a robust n-teir architecture is very well put together.

Although it is somewhat targetted at major applications it actually works really well for quick and simple sites too once you understand the principles. To add a declarative dataset for a simple table in VWD (or VS) takes seconds and you get full CRUD methods that you can use to access your table. Figuring it out the first time takes a little longer but no pain, no gain as they say.;-)

My habit now is:
All write operations are done through DataSets built declaratively in the builder UI. This mnakes things much easier for both development and maintenance of the inital TableAdapters (literally drag and drop) plus enhanced queries on to those tables.

Nearly all programmatic read operations are the same. But I mainly build a BLL (business logic layer) of classes that wrap database access so page development is just a matter of instantiating the relevant objects - the data connectivity is nicely isolated to the BLL. I use the DataSource controls fairly extensvely for page building but only for READ ONLY access. Having the user do a row selection and adding more meaningful controls to handle inserting or updaing from there via the BLL.

It''s a reliable design pattern and means I always know where to look for my data access code. Also any changes are automatically propogated to ALL the pages using the BLL classes rather than having to do a lot of refactoring of multiple pages just to accommodate small changes (e.g. new columns and so on).

Alistair

如果使用的是SQL Server，则可以使用SqlBulkCopy.WriteToServer(DataTable).参考: http://msdn.microsoft.com/en-us/library/ex21zs8x.aspx [^ ]

If you are using SQL Server, SqlBulkCopy.WriteToServer(DataTable) is the solution. Ref: http://msdn.microsoft.com/en-us/library/ex21zs8x.aspx[^]

这篇关于将数据表记录更新到Access数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！