将数据集插入 Oracle 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17172106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert Data Set into Oracle Table
提问by Bojan
I am trying to insert all the records from my data table into oracle table. I have figured out how to insert a single record, however how do I go about inserting multiple records. The biggest problem here is that the order of columns in dataset does not match the order of columns in oracle table.
我正在尝试将数据表中的所有记录插入到 oracle 表中。我已经弄清楚如何插入单个记录，但是如何插入多个记录。这里最大的问题是数据集中列的顺序与oracle表中的列顺序不匹配。
Here is the code I am using to insert a single record:
这是我用来插入单个记录的代码：
OdbcCommand cmd = new OdbcCommand();
try
{
cmd.Connection = getDBConnection(); //This calls another method that just gets the connection to database
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO MY_INSERT_TEST(NAME) VALUES(\'Test 1\')";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd.Dispose();
}
catch
{
cmd.Connection.Close();
cmd.Dispose();
}
so how do I modify these to add all records from the data set?
那么如何修改这些以添加数据集中的所有记录？
回答by Steve
Supposing you want to loop over a collection of rows and you want to insert all of them then I would try with a pseudocode like this.
假设你想遍历一组行并且你想插入所有行，那么我会尝试使用这样的伪代码。
string cmdText = "INSERT INTO MY_INSERT_TEST(Col1, Col2, Col3) VALUES(?, ?, ?)";
using(OdbcConnection cn = getDBConnection())
using(OdbcCommand cmd = new OdbcCommand(cmdText, cn))
{
cn.Open();
cmd.Parameters.AddWithValue("@p1", "");
cmd.Parameters.AddWithValue("@p2", "");
cmd.Parameters.AddWithValue("@p3", "");
foreach(DataRow r in dt.Rows)
{
cmd.Parameters["@p1"].Value = r["Column3"].ToString());
cmd.Parameters["@p2"].Value = r["Column1"].ToString());
cmd.Parameters["@p3"].Value = r["Column2"].ToString());
cmd.ExecuteNonQuery();
}
}
Build a parameterized query, define the parameters (here are all parameters of string type, need to be checked) and then loop over the rows of the datatable assigning the parameters value from the corresponding column. Notice that in the command text you don't write directly the values but you put a placeholder for the actual value that you will supply inside the loop.
构建一个参数化查询，定义参数（这里是所有字符串类型的参数，需要检查）然后遍历数据表的行，从相应的列中分配参数值。请注意，在命令文本中，您不会直接写入值，而是为将在循环内提供的实际值放置一个占位符。
