CodePudding user response:

 
Public static int BatchInsert (string tableName, DataTable dt, string conStr, string [] strColumns) 
{

Int dtRowsCount=dt. Rows. Count; 
Int iResult=0; 
The StringBuilder sbCmdText=new StringBuilder (); 
If (dtRowsCount & gt; 0) 
{
//ready to insert SQL 
SbCmdText. Append (" BEGIN "); 

for (int i=0; I & lt; Dt. Rows. Count; I++) 
{
SbCmdText. AppendFormat (" INSERT INTO {0} (" tableName); 
SbCmdText. Append (string. Join (", ", strColumns)); 
SbCmdText. Append (") VALUES ("); 
SbCmdText. Append (" : a00:1450:8006 "+ I + string. Join (", : a00:1450:8006" + I, strColumns)); 
SbCmdText. Append (); "" ); 
} 

SbCmdText. Append (" END." ); 

Using (OracleConnection conn=new OracleConnection (conStr)) 
{
Using (OracleCommand CMD=conn. CreateCommand ()) 
{
//bind the batch number of rows 
CMD. ArrayBindCount=dtRowsCount; 
CMD. BindByName=true; 
Cmd.Com mandType=CommandType. Text; 
Cmd.Com mandText=sbCmdText. ToString (); 
Cmd.Com mandTimeout=600;//10 minutes 

//create the parameter 
OracleParameter oraParam; 
List Cacher=new List (a); 
OracleDbType dbType=OracleDbType. Object; 
For (int j=0; j {
for (int i=0; I & lt; StrColumns. The Count (); I++) 
{
DbType=GetOracleDbType (dt. Rows [j] [I]); 
OraParam=new OracleParameter (" a00:1450:8006 "+ j + strColumns [I], dbType); 
OraParam. Direction=ParameterDirection. Input; 
OraParam. OracleDbTypeEx=dbType; 
OraParam. Value=https://bbs.csdn.net/topics/dt.Rows [j] [I]; 
CMD. The Parameters. The Add (oraParam); 
} 
} 
//open the connection 
Conn. The Open (); 

Execute the batch *//* 
Var trans=conn. BeginTransaction (); 
Try 
{
CMD. Transaction=trans; 
IResult=CMD. ExecuteNonQuery (); 
Trans.Com MIT (); 
} 
The catch (Exception ex) 
{
Trans. The Rollback (); 
Throw the ex. 
} 
The finally 
{
if (conn !=null) conn. Close (); 
} 

} 
} 
} 
Return iResult; 
} 

/* * 
* depending on the type of data access OracleDbType 
*/
Private static OracleDbType GetOracleDbType (object value) 
{
OracleDbType dataType=OracleDbType. Object; 
If value is a string () 
{
DataType=OracleDbType. Varchar2; 
} 
Else if (the value is a DateTime) 
{
DataType=OracleDbType. TimeStamp; 
} 
Else if (the value is an int | | value is) 
{
DataType=OracleDbType. Int32; 
} 
Else if (value is long) 
{
DataType=OracleDbType. Int64; 
} 
Else if (the value is a decimal | | value is double | | value is a float) 
{
DataType=OracleDbType. A Decimal; 
} 
Else if (value is Guid) 
{
DataType=OracleDbType. Varchar2; 
} 
Else if (value is bool | | value is a Boolean) 
{
DataType=OracleDbType. Byte; 
} 
Else if (value is byte) 
{
DataType=OracleDbType. Blob; 
} 
Else if (value is char) 
{
DataType=OracleDbType. Char; 
} 
Return dataType. 
} 

CodePudding user response: