访问数据库的 C# 数据集 [英] C# Dataset to Access DB

问题描述

我有一个从 csv 文件动态创建的数据集.我想要做的是将行插入到我的 MS Access 表中，但我不知道从哪里开始.

I have a dataset that is dynamically created from a csv file. What I want to do is insert the rows into my MS Access table but I cannot figure out where to start with this.

数据集中数据的标头可能会因顺序而异，但标头的名称将始终与访问数据库匹配.我必须在插入命令中静态调出标题名称还是可以从数据集中构建标题?

The headers of the data in the dataset can vary as far as the order but the name of the header will always match the access database. Do I have to statically call out the header name in the insert command or can I build the headers from the dataset?

我知道如何创建连接并将其打开到数据库，但不确定如何在插入命令中创建以动态拉取表头.

I know how to create the connection and open it to the database but am not sure how to create in insert command to dynamically pull the table headers.

在 C# 编程方面，我很年轻，所以如果你能帮我把它拼出来，我将不胜感激！

I am pretty green when it comes to C# programming so if you can spell it out for me I would really appreciate it!

以下是访问表头的示例:

Here is an example of the access table headers:

ID、项目、成本、零售

ID, Item, Cost, Retail

然后是填充数据集表的 CSV.它可能有零售，也可能没有:

Then the CSV which will fill the dataset table. It might have Retail or it might not:

项目，成本

这是我到目前为止的代码，但它没有写入访问表.如果我查看 dtAccess，它会正确显示.

Here is the code I have so far but it doesn't write to the access table. If I vew the dtAccess it shows correctly.

 OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\Database.accdb";Persist Security Info=False;");
                myConnection.Open();

                string queryString = "SELECT * from " + lblTable.Text;

                OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, myConnection);

                DataTable dtAccess = new DataTable();

                DataTable dtCSV = new DataTable();

                dtCSV = ds.Tables[0];

                using (new OleDbCommandBuilder(adapter))
                {
                    adapter.Fill(dtAccess);
                    dtAccess.Merge(dtCSV);
                    adapter.Update(dtAccess);
                }

                myConnection.Close();

推荐答案

想通了.这是我使用的代码:

Figured it out. Here is the code I used:

OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="Database.accdb";Persist Security Info=False;");

                //command to insert each ASIN
                OleDbCommand cmd = new OleDbCommand();

                //command to update each column (ASIN, Retail... from CSV)
                OleDbCommand cmd1 = new OleDbCommand();

                //load csv data to dtCSV datatabe
                DataTable dtCSV = new DataTable();

                dtCSV = ds.Tables[0];

                // Now we will collect data from data table and insert it into database one by one
                // Initially there will be no data in database so we will insert data in first two columns
                // and after that we will update data in same row for remaining columns
                // The logic is simple. 'i' represents rows while 'j' represents columns

                cmd.Connection = myConnection;
                cmd.CommandType = CommandType.Text;
                cmd1.Connection = myConnection;
                cmd1.CommandType = CommandType.Text;

                myConnection.Open();

                for (int i = 0; i <= dtCSV.Rows.Count - 1; i++)
                {
                    cmd.CommandText = "INSERT INTO " + lblTable.Text + "(ID, " + dtCSV.Columns[0].ColumnName.Trim() + ") VALUES (" + (i + 1) + ",'" + dtCSV.Rows[i].ItemArray.GetValue(0) + "')";

                    cmd.ExecuteNonQuery();

                    for (int j = 1; j <= dtCSV.Columns.Count - 1; j++)
                    {
                        cmd1.CommandText = "UPDATE " + lblTable.Text + " SET [" + dtCSV.Columns[j].ColumnName.Trim() + "] = '" + dtCSV.Rows[i].ItemArray.GetValue(j) + "' WHERE ID = " + (i + 1);

                        cmd1.ExecuteNonQuery();
                    }
                }

                myConnection.Close();

这篇关于访问数据库的 C# 数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！