数据表关系和更新数据表 [英] Data tables relationships and updating datatables

问题描述

我正在使用VS2013，我对类型化的数据集，数据表，表适配器和数据视图有一点了解，但无法让他们访问以执行我的工作.

I am using VS2013 I have a little understanding of typed datasets, datatables, tableadapters and dataviews but just can't get them accessed to do what I wand.

我通过导入csv文件创建了一个数据表，并且在类型化的数据集中有一个表，该表我理解是一个数据表，并且我想根据导入的数据表中的某些行与数据表的关系来更新它们在类型化数据集中 但是只是把它们放在一起.

I have created a datatable from importing a csv file and I have a table in a typed dataset that I understand to be a datatable and I want to update some of the rows in the imported data table based on their relationship to the datatable in the typed dataset but just getting stuck putting them together.

imported csv table

  WriteAllText("C:\Network Files\AAATrash\Testing1234.csv", """Account"",""eTagNo"",""BinSize"",""MaterialType"",""Lat"",""Long"",""ScanDate"",""BinIdentifier""" & vbCrLf & ReadAllText("C:\Network Files\AAATrash\Testing123.csv"))

            Dim strOLEDB As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Network Files\AAATrash\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
            Dim strThisSQL As String = "Select * From Testing1234.csv"
            Dim daImport As New OleDbDataAdapter(strThisSQL, strOLEDB)
            Dim dtImport As New DataTable()
            dtImport.Locale = System.Globalization.CultureInfo.InvariantCulture
            daImport.Fill(dtImport)
            dtImport.Columns.Add("Import", GetType(Boolean))
            dtImport.Columns.Add("Imported", GetType(Boolean))
            dtImport.Columns.Add("ScanType", Type.GetType("System.String"))       'very longwinded
            For i As Integer = 0 To dtImport.Rows.Count - 1
                dtImport.Rows(i).Item("Import") = True
            Next

类型化的数据集

            Customerds.CustomerSitesDataTable()

            Customerds.CustomerSitesDataTable()

我试图在将csv导入文件引入数据库之前，并确保在CustomerSites表中存在相应的现有相关记录.  理想情况下，它可能像#

I am trying to validate the csv import file before bringing into the database and making sure that there are corresponding existing related records in the CustomerSites table.  Ideally it might be something like#

    strSQL = "UPDATE dtImport SET ScanType = 'Invalid SiteID' WHERE (NOT EXISTS (SELECT SiteID FROM CustomerSites WHERE (dtImport.Account = SiteID)))"
            cmdTemp = New SqlCommand(strSQL, connString)
            cmdTemp.ExecuteNonQuery()
            cmdTemp.Dispose()

我接受可以逐行完成数据表的更新，但是这些行将在同一选择查询中进行过滤.

I accept that updating the datatable may be done row by row but these rows would be filtered on the same select query.

然后必须将某些dtImport数据表附加到另一个类型化的数据集，但是我还没有研究过，因此可以在不必启动另一个线程的情况下弄清楚这一点.  如果您能帮助我理解这些内容 不同的对象类型及其相互影响，以便解决问题.

Then some of the dtImport datatable would then Have to be appended to another typed dataset but I haven't looked at that yet so may be able to figure that out without having to start another thread.  If you could help me with my understanding of these different object types and their interplay so that I can solve my problem.

非常感谢

推荐答案

也许您应该通读其中一些，请注意，第1和第4个结果提到了在其中使用Ini文件的情况.如果需要将C#示例转换为VB.Net，则可以复制代码并将其粘贴到在线转换器中 像 Telerik 一样，将其从C#转换为VB.Net.

Perhaps you should read through some of these Google search results for "Use OLEdb to import CSV file to DataTable vb.net" and note that 1st and 4th result mention the use of an Ini file in them. If you need to convert C# examples to VB.Net you can copy code and paste it into an online converter like Telerik to convert it from C# to VB.Net.

您是否需要使用OLEdb的原因?

Is there a reason you need to use OLEdb?

CSV通常是用逗号分隔的文本文件，它实际上没有字段，而是用逗号分隔的字符串，它们是每行或每行中的列.实际的数据库通常可以包含可能包含特定类型对象的字段，例如 作为DateTime或数字值(例如Double或Integer或Long)或其他一些可能的数字，尽管我并不精通DataBase.

A CSV is a comma delimited text file typically and does not have fields really but strings separated by commas which are columns in each line or row. Where as an actual DataBase typically could have fields that may contain specific types of objects such as a DateTime or a numeric value such as Double or Integer or Long or any number of other possibilities I suppose although I am not DataBase savvy.

但是 DataTable 当然可以具有与某些对象类型有关的字段，而不仅仅是字符串.

But a DataTable definitely can have fields that can pertain to certain object types other than just strings.

A 对于Windows.Forms项目，DataGridView 控件可以将DataTable设置为其数据源，以显示DataTable中的内容.不用使用.CSV文件，而是可以将DataTables数据保存，序列化到XML文件，以便使用不同的对象类型. 可从XML文件中获取，以从序列化的XML文件中重新加载DataTable.不需要OLEdb.

A DataGridView control, for a Windows.Forms project, can set a DataTable as its DataSource to display whatever is in a DataTable. And instead of using .CSV files a DataTables data can be saved, serialized, to an XML file such that different object types are available from the XML file for reloading a DataTable from a serialized XML file. No OLEdb is required for any of that.

尽管如果您出于某种目的要学习OLEdb，我还是建议您使用实际的数据库，而不要真正打扰.CSV文件.

Although if you want to learn OLEdb for some purpose I would suggest you use an actual DataBase rather than bothering with .CSV files really.

您可以将.CSV文件加载到DataTable中，该数据表最初是为列中的不同字段类型设置的，但您必须将每行的.CSV列转换为适当的类型，才能将每行的.CSV添加到数据表.之后，你 可以将DataTable输出到XML文件，以备将来使用.

You could load a .CSV file to a DataTable where the DataTable is initally set for different field types in columns but you would have to convert the .CSV columns for each row to an appropriate type to add them for each row into a DataTable. After that you could output the DataTable to XML file for future use of that data.

DataGridView和DataTables也可以具有事件.例如，如果您在DataGridView将DataTable作为DataSource的同时更改了DataGridView单元格中的某些内容，或者在DataGridView中添加新行并用适当的值填充单元格，则可以保存数据 到DataTable，以便DataTable更新.并使用事件执行此操作，这样就可以自动处理导致事件发生的事件中的代码.

DataGridView and DataTables can have events also. For example if you alter something in DataGridView cell while DataGridView has DataTable as DataSource, or add new row in DataGridView and fill cells in with appropriate values you can then save the data to the DataTable so the DataTable updates. And do that using an event so it is automatic with regard to code within the event which causes that to occur.

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