C＃数据集以Access数据库 [英] C# Dataset to Access DB

问题描述

我有一个动态地从一个CSV文件中创建一个数据集。我想要做的是插入行到我的MS Access表，但我想不出哪里开始与此有关。

在数据集中的数据的报头可以尽可能的顺序，但头名总是会访问数据库匹配而有所不同。我一定要静态地叫出头名在插入命令或我可以建立从数据集中的头？

我知道如何创建连接并打开它数据库，但我不知道如何在INSERT命令创建动态拉表头。

我非常绿色的，当涉及到C＃编程，所以如果你能拼一下！出了我，我真的很感激它。

下面是访问表头的一个例子：

ID ，项目，成本，零售

然后CSV这将填补数据集表。它可能有零售，也可能不是：

项目，成本

下面是我到目前为止的代码但它不写入访问表。如果我的VEW它dtAccess显示正确

  OleDbConnection的MyConnection的=新的OleDbConnection（供应商= Microsoft.ACE.OLEDB.12.0;数据源= \C：\\Database.accdb\;坚持安全信息=假;）; 
 myConnection.Open（）; 
 
字符串的queryString =SELECT * FROM+ lblTable.Text; 
 
 OleDbDataAdapter的适配器=新OleDbDataAdapter的（的queryString，MyConnection的）; 
 
的DataTable dtAccess =新的DataTable（）; 
 
的DataTable dtCSV =新的DataTable（）; 
 
 dtCSV = ds.Tables [0];使用
 
（新OleDbCommandBuilder（适配器））
 {
 adapter.Fill（dtAccess）; 
 dtAccess.Merge（dtCSV）; 
 adapter.Update（dtAccess）; 
} 
 
 myConnection.Close（）; 
  

解决方案

想通了。下面是我使用的代码：

  OleDbConnection的MyConnection的=新的OleDbConnection（供应商= Microsoft.ACE.OLEDB.12.0;数据源= \Database.accdb\;坚持安全信息=假;）; 
 
 //命令插入每个ASIN 
 OleDbCommand的CMD =新的OleDbCommand（）; 
 
 //命令更新每列（ASIN，零售......从CSV）
 OleDbCommand的CMD1 =新的OleDbCommand（）; 
 
 //加载CSV数据dtCSV datatabe 
的DataTable dtCSV =新的DataTable（）; 
 
 dtCSV = ds.Tables [0]; 
 
 //现在我们将从数据表收集数据和一个
将其插入到数据库中的一个//最初会有数据库中没有数据，所以我们将在插入前两列数据
 //之后，我们将更新同一行数据，其余列$ b​​ $ b //的逻辑很简单。 '我'代表行，而J代表列
 
 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;我++）
 {
 cmd.CommandText =INSERT INTO+ lblTable.Text +（ID，+ dtCSV.Columns [0] .ColumnName.Trim（）+）的值（+第（i + 1）+，+ dtCSV.Rows [I] .ItemArray.GetValue（0） +'）; 
 
 cmd.ExecuteNonQuery（）; 
 
为（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（）; 
  

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.

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, Item, Cost, Retail

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

Item, Cost

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＃数据集以Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！