如何将数据从DataTable插入Oracle数据库表：

I've data in DataTable with 2 rows and 3 columns. I want to insert that data into Oracle table.

我在DataTable中有2行3列的数据。我想将该数据插入Oracle表。

How can I insert? please give me with some example.

我怎么插入？请给我一些例子。

And also

并且

How can I pass datatable to storedprocedure in ORACLE...

如何将数据表传递给ORACLE中的storedprocedure ...

I pass datatable in below mensioned manner, but datatable type problem is comming. how can I solve this?

我以下面的方式传递数据表，但是数据表类型问题正在传递。我该怎么解决这个问题？

cmd.Parameters.Add("@Details",dtSupplier);   
(OR)  
cmd.Parameters.Add("Details", DbType.Single).Value = dtSupplier.ToString();

6 个解决方案

#1

want to insert dataset or a datatable into ORACLE,

想要将数据集或数据表插入ORACLE，

1. create an ORACLE data adapter.
2. 创建一个ORACLE数据适配器。
3. create a command object for insertion,
4. 创建一个用于插入的命令对象，
5. set the CommandType to StoredProcedure.
6. 将CommandType设置为StoredProcedure。
7. Update command of the data adapter,
8. 更新数据适配器的命令，
9. pass the dataset or datatable as parameter.
10. 将数据集或数据表作为参数传递。

like this:

喜欢这个：

OracleDataAdapter da = new OracleDataAdapter();
OracleCommand cmdOra = new OracleCommand(StoredProcedureName, Connection);
cmdOra.CommandType = CommandType.StoredProcedure;

da.InsertCommand = cmdOra;
da.Update(dsDataSet);

OR

要么

if above dont work than pass datatable as xml prameter than than process it

如果上面的工作不能比将数据表作为xml参数传递而不是处理它

For details check : ADO.NET DataTable as XML parameter to an Oracle/SQL Server Database Stored Procedure

有关详细信息，请检查：ADO.NET DataTable作为Oracle / SQL Server数据库存储过程的XML参数

OR

要么

Check this thread on Oracle site : Thread: Pass data table to Oracle stored procedure

在Oracle站点上检查此线程：Thread：将数据表传递给Oracle存储过程

Check existing answer : How to Pass datatable as input to procedure in C#?

检查现有答案：如何将数据表作为输入传递给C＃中的过程？

#2

The best idea would be follow the step mentioned below

最好的想法是遵循下面提到的步骤

1. Create a transaction
2. 创建一个事务
3. Begin the transaction
4. 开始交易
5. Loop through you data table
6. 遍历数据表
7. call your procedure
8. 打电话给你的程序
9. If no error occurred commit transaction
10. 如果没有发生错误提交事务
11. else roll back transaction
12. 否则回滚交易

#3

Regarding this part of your question:

关于你的这部分问题：

cmd.Parameters.Add("@Details",dtSupplier);
(OR)
cmd.Parameters.Add("Details", DbType.Single).Value = dtSupplier.ToString();

cmd.Parameters.Add（ “@详细信息”，dtSupplier）; （OR）cmd.Parameters.Add（“Details”，DbType.Single）.Value = dtSupplier.ToString（）;

What is the type of the "Details" parameter? Is it a Single? Then you would have to pick one (1) value from your DataTable and pass it to your parameter, something like dtSupplier.Rows[0]["col"].
If you use dtSupplier.ToString() you are just making a string of the entire DataTable (which i guess will always be the type name of DataTable).

“详细信息”参数的类型是什么？它是单身吗？然后你必须从你的DataTable中选择一（1）个值并将其传递给你的参数，比如dtSupplier.Rows [0] [“col”]。如果你使用dtSupplier.ToString（），你只需要创建一个整个DataTable的字符串（我猜这将永远是DataTable的类型名称）。

#4

First of all, you need to add Oracle.DataAccess.dll as reference in Visual Studio. In most cases, you can find this dll in the directory C:\ProgramData\Oracle11g\product\11.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll

首先，您需要在Visual Studio中添加Oracle.DataAccess.dll作为参考。在大多数情况下，您可以在目录C：\ ProgramData \ Oracle11g \ product \ 11.2.0 \ client_1 \ ODP.NET \ bin \ 2.x \ Oracle.DataAccess.dll中找到此dll

If just you need to insert the records from DataTable to Oracle table, then you can call the below function. Consider that your DataTable name is dt.

如果只需要将DataTable中的记录插入到Oracle表中，则可以调用以下函数。请考虑您的DataTable名称是dt。

string error = "";
int noOfInserts = DataTableToTable(dt,out error);

1. Without using Oracle Parameters(special character non-safe)

1.不使用Oracle参数（特殊字符不安全）

The definition of the function is given below. Here, we are just making the query dynamic for passing this as a sql statement to the InsertWithQuery function.

该功能的定义如下。在这里，我们只是将查询动态化，将其作为sql语句传递给InsertWithQuery函数。

public int DataTableToTable(DataTable dt,out string error)
{
    error = "";
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        finalSql = "INSERT INTO TABLENAME SELECT ";
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            colValue += "'" + dt.Rows[i][j].ToString() + "',"; 
        }
        colValue = colValue.Remove(colValue.Length - 1, 1);
        finalSql += colValue + " FROM DUAL";
        InsertWithQuery(finalSql, out error);
        if (error != "")
           return error;
        inserts++;
        colValue = "";
    }
}

The code for InsertWithQuery function is given below. Here, in the connection string you have to place you database details like Host,Username,Password etc.

下面给出了InsertWithQuery函数的代码。在这里，在连接字符串中，您必须放置数据库详细信息，如主机，用户名，密码等。

public int InsertWithQuery(string query, out string error)
{
     error = "";
     int rowsInserted = 0;        
     if (error == "")
     {
          OracleConnection con = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=)));User Id=;Password="); 
          OracleTransaction trans = con.BeginTransaction();              
          try
          {
              error = "";
              OracleCommand cmd = new OracleCommand();
              cmd.Transaction = trans;
              cmd.Connection = con;
              cmd.CommandText = query;
              rowsInserted = cmd.ExecuteNonQuery();
              trans.Commit();
              con.Dispose();
              return rowsInserted;
          }
          catch (Exception ex)
          {
              trans.Rollback();
              error = ex.Message;
              rowsInserted = 0;
          }
          finally
          {
              con.Dispose();
          }
     }
     return rowsInserted;
}

2. With using Oracle Parameters(special character safe)
This can handle special characters like single quotes like scenarios in the column values.

2.使用Oracle参数（特殊字符安全）这可以处理特殊字符，如列值中的方案之类的单引号。

public int DataTableToTable(DataTable dt,out string error)
{
    error = "";
    string finalSql = "";
    List<string> colValue = new List<string>();
    List<string> cols = new List<string>() {"COLUMN1","COLUMN2","COLUMN3"};
    for (int i = 0; i < dt.Rows.Count; i++)
    {
         finalSql = "INSERT INTO TABLENAME(COLUMN1,COLUMN2,COLUMN3) VALUES(:COLUMN1,:COLUMN2,:COLUMN3) ";                    
         for (int j = 0; j < dt.Columns.Count; j++)
         {
             colValue.Add(dt.Rows[i][j].ToString());
         }                     
         objDAL.InsertWithParams(finalSql,colValue,cols, out error);
         if (error != "")
             return error;
         inserts++;
         colValue.Clear();
    }
}

And the InsertWithParams is given below

InsertWithParams如下所示

public string InsertWithParams(string sql, List<string> colValue, List<string> cols, out string error)
{
     error = "";
     try
     {
         OracleConnection con = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=)));User Id=;Password="); 
         OracleCommand command = new OracleCommand(sql, con);
         for (int i = 0; i < colValue.Count; i++)
         {
              command.Parameters.Add(new OracleParameter(cols[i], colValue[i]));
         }
         command.ExecuteNonQuery();
         command.Connection.Close();
    }
    catch (Exception ex)
    {
         error = ex.Message;
    }
    return null;
}

#5

              try  {

                    //Suppose you have DataTable dt
                    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                              @"Data Source='Give path of your access database file here';Persist Security Info=False";

                    OleDbConnection dbConn = new OleDbConnection(connectionString);
                    dbConn.Open();
                    using (dbConn)
                    {
                        int j = 0;
                        for (int i = 0; i < 2; i++)
                        {
                            OleDbCommand cmd = new OleDbCommand(
                            "INSERT INTO Participant_Profile ([column1], [column2] , [column3] ) VALUES (@c1 , @c2 , @c3 )", dbConn);
                            cmd.Parameters.AddWithValue("@c1", dt.rows[i][j].ToString());
                            cmd.Parameters.AddWithValue("@c2", dt.rows[i][j].ToString());
                            cmd.Parameters.AddWithValue("@c3", dt.rows[i][j].ToString());
                            cmd.ExecuteNonQuery();
                            j++;
                        }



                    }
                }
                catch (OleDbException exception)
                {
                    Console.WriteLine("SQL Error occured: " + exception);
                }

#6

I'm very late for this answer, but I elaborated a bit to have some more readable (I hope) code, and to avoid all those .ToString() for the values so nulls and other less common values can be handled; here it is:

我对这个答案来说已经很晚了，但是我详细说明了一些更具可读性（我希望）的代码，为了避免所有那些.ToString（）的值，所以可以处理空值和其他不太常见的值;这里是：

public void Copy(String tableName, DataTable dataTable)
{
    var insert = $"insert into {tableName} ({GetColumnNames(dataTable)}) values ({GetParamPlaceholders(dataTable)})";
    using (var connection = /*a method to get a new open connection*/)
    {       
        for (var row = 0; row < dataTable.Rows.Count; row++)
        {
            InsertRow(dataTable, insert, connection, row);
        }
    }
}

private static void InsertRow(DataTable dataTable, String insert, OracleConnection connection, Int32 row)
{
    using (var command = new OracleCommand(insert, connection))
    {
        AssembleParameters(dataTable, command, row);
        command.ExecuteNonQuery();
    }
}

private static void AssembleParameters(DataTable dataTable, OracleCommand command, Int32 row)
{
    for (var col = 0; col < dataTable.Columns.Count; col++)
    {
        command.Parameters.Add(ParameterFor(dataTable, row, col));
    }
}

private static OracleParameter ParameterFor(DataTable dataTable, Int32 row, Int32 col)
{
    return new OracleParameter(GetParamName(dataTable.Columns[col]), dataTable.Rows[row].ItemArray.GetValue(col));
}

private static String GetColumnNames(DataTable data) => (from DataColumn column in data.Columns select column.ColumnName).StringJoin(", ");

private static String GetParamPlaceholders(DataTable data) => (from DataColumn column in data.Columns select GetParamName(column)).StringJoin(", ");

private static String GetParamName(DataColumn column) => $":{column.ColumnName}_param";

Hope this can be still useful to somebody

希望这对某些人来说仍然有用