using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using System.Diagnostics;
using System.Data;
namespace BulkInsert
{
class Program
{
static string cs = "Data Source=ORA;User Id=O;Password=P;";
//Table Schema =
//CREATE TABLE BIGONE (N DECIMAL(6,0), T NVARCHAR2(64))
const int COUNT = 50000;
static void Main(string[] args)
{
var data = new Dictionary<int, string>();
for (int i = 0; i < COUNT; i++)
data.Add(i, string.Format("INFO{0:00000000}", i));
CleanTable();
TestCommand(data);
CleanTable();
TestBulkCopy(data);
Console.Read();
}
static void CleanTable()
{
using (var cn = new OracleConnection(cs))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = "TRUNCATE TABLE BIGONE";
cmd.ExecuteNonQuery();
}
}
static void TestCommand(Dictionary<int, string> data)
{
Stopwatch sw = new Stopwatch();
sw.Start();
using (var cn = new OracleConnection(cs))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText =
"INSERT INTO BIGONE (N, T) VALUES (:n, :t)";
var pN = cmd.Parameters.Add("n", OracleDbType.Decimal);
var pT = cmd.Parameters.Add("t", OracleDbType.NVarchar2);
foreach (int k in data.Keys)
{
pN.Value = k;
pT.Value = data[k];
cmd.ExecuteNonQuery();
}
cn.Close();
}
sw.Stop();
Console.WriteLine("TestCommand: {0:N0}ms", sw.ElapsedMilliseconds);
}
static void TestBulkCopy(Dictionary<int, string> data)
{
Stopwatch sw = new Stopwatch();
sw.Start();
using (var cn = new OracleConnection(cs))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText="SELECT * FROM BIGONE WHERE 1=0";
DataTable t = new DataTable();
var dr = cmd.ExecuteReader();
//create the DataTable object according to Oracle table
t.Load(dr);
dr.Close();
//fill the DataTable
foreach (int k in data.Keys)
t.Rows.Add(k, data[k]);
//Bulk Copy!
OracleBulkCopy bc = new OracleBulkCopy(cn);
bc.DestinationTableName="BIGONE";
bc.WriteToServer(t);
bc.Close();
cn.Close();
}
sw.Stop();
Console.WriteLine("TestBulkCopy: {0:N0}ms", sw.ElapsedMilliseconds);
}
}
}