使用程序集【Oracle.ManagedDataAccess.Client】
public int InsertBindParam(DataTable dt)
{
int recordCount = dt.Rows.Count, i = 0, count = 0;
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandText = "INSERT INTO TEST001(COL1,COL2,COL3) VALUES(:P_COL1,:P_COL2,:P_COL3)";
//指定单次需要处理的条数
cmd.ArrayBindCount = recordCount;
int[] p_col1 = new int[recordCount];
string[] p_col2 = new string[recordCount];
DateTime[] p_col3 = new DateTime[recordCount];
cmd.Parameters.Add(new OracleParameter("P_COL1", OracleDbType.Int32, p_col1, ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("P_COL2", OracleDbType.Varchar2, p_col2, ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("P_COL3", OracleDbType.Date, p_col3, ParameterDirection.Input));
foreach (DataRow dr in dt.Rows)
{
p_col1[i] = Convert.ToInt32(dr["COL1"].ToString());
p_col2[i] = dr["COL2"].ToString();
p_col3[i] = DateTime.Now;
}
using (var con = new OracleConnection())
{
con.ConnectionString = m_sConnection;
con.Open();
cmd.Connection = con;
//执行批处理操作,并返回影响行数
count = cmd.ExecuteNonQuery();
}
return count;
}
}