Update content:
1. Add large text import (originally only datareader is supported)
2. Data can be imported from row X.
3. Decide whether to save the data row number.
4. The column ing is case insensitive.
5. Constant columns can be imported.
6. Multi-target columns imported from the same-source columns are allocated only once, which saves more memory.
7. added the source column Automatic Detection Function
Call example:
1. Import datareader
View code
/// <Summary>
/// Quick import of datareader to Oracle
/// </Summary>
/// <Param name = "strconnectionstring"> Oracle connection string such as: "Data Source = (description = (address = (Protocol = TCP) (host = XXXX) (Port = 1521 )) (CONNECT_DATA = (SID = yyyy); User ID = sys; Password = sys ;" </Param>
/// <Param name = "inttimeout"> Maximum import timeout </Param>
/// <Param name = "datareader"> Datareader to be imported </Param>
/// <Param name = "strtablename"> Target table name </Param>
/// <Param name = "columnmapping"> Column ing </Param>
/// <Param name = "intbatchsize"> How many entries are imported each time before submission </Param>
/// <Param name = "intcommitrowcount"> Number of successfully imported rows </Param>
/// <Param name = "spenttime"> Time consumed </Param>
/// <Param name = "strerrmsg"> Error Message </Param>
/// <Returns> </returns>
Public Bool Fastimportdatareadertodb ( String Strconnectionstring, Int Intbatchsize, Int Inttimeout, idatareader datareader,
String Strtablename,
Dictionary < String , String > Columnmapping, Out Int Intcommitrowcount, Out Long Spenttime, Out String Strerrmsg)
{
Bool Blnflag = False ;
Intcommitrowcount = 0 ;
Spenttime = 0 ;
Strerrmsg = String . Empty;
Oraclebulkinsert= NewOraclebulkinsert (strconnectionstring, strtablename, columnmapping,Null,1,Null, Intbatchsize, inttimeout );
Try
{
Oraclebulkinsert. writetoserver (datareader );
Blnflag = True ;
}
Catch (Oraclebulkinsertexception ex)
{
Strerrmsg = Ex. tostring ();
}
Finally
{
Intcommitrowcount = Oraclebulkinsert. copiedrowcount;
Spenttime = Oraclebulkinsert. takemilliseconds;
}
ReturnBlnflag;
}
2. Import a text file
View code
/// <Summary>
/// Quick import of text files to database tables
/// </Summary>
/// <Param name = "strconnectionstring"> Target database connection string. The Oracle connection string is as follows: "Data Source = (description = (address = (Protocol = TCP) (host = XXXX) (Port = 1521 )) (CONNECT_DATA = (SID = yyyy); User ID = sys; Password = sys ;" </Param>
/// <Param name = "inttimeout"> Maximum import timeout </Param>
/// <Param name = "strtxtfile"> Path of the text file to be imported </Param>
/// <Param name = "strhead"> Column header format with delimiters </Param>
/// <Param name = "strdelimited"> Column Separator </Param>
/// <Param name = "strtablename"> Target table name </Param>
/// <Param name = "columnmapping"> Column ing. The first line of TXT provides the column name. </Param>
/// <Param name = "intbatchsize"> How many entries are imported each time before submission </Param>
/// <Param name = "extramapping"> Extra column ing </Param>
/// <Param name = "strrownumbercolumn"> Save the row number column name (null: Do not save the row number) </Param>
/// <Param name = "intcommitrowcount"> Number of successfully imported rows </Param>
/// <Param name = "lspenttime"> Time consumed </Param>
/// <Param name = "strerrmsg"> Error Message </Param>
/// <Param name = "intstartrow"> Import from row x </Param>
/// <Returns> </returns>
Public Bool Fastimporttexttodb ( String Strconnectionstring, Int Intbatchsize, Int Inttimeout, String Strtxtfile, String Strhead, String Strdelimited, String Strtablename, Dictionary < String , String > Columnmapping, Dictionary < String , Object > Extramapping, Uint Intstartrow, String Strrownumbercolumn, Out Int Intcommitrowcount, Out Long Lspenttime, Out String Strerrmsg)
{
Bool Blnflag = False ;
Intcommitrowcount = 0 ;
Lspenttime = 0 ;
Strerrmsg = String . Empty;
If ( ! " . Txt " . Equals (path. getextension (strtxtfile), stringcomparison. currentcultureignorecase ))
{
Throw New Exception ( " Only TXT files can be imported... " );
}
filestream = New filestream (strtxtfile, filemode. open, fileaccess. read, fileshare. none);
streamreader = New streamreader (filestream, encoding. default);
Oraclebulkinsert= NewOraclebulkinsert (strconnectionstring, strtablename, columnmapping, extramapping, intstartrow, strrownumbercolumn, intbatchsize, inttimeout );
Try
{
Oraclebulkinsert. writetoserver (streamreader, strhead, strdelimited );
Blnflag = True ;
}
Catch (Oraclebulkinsertexception ex)
{
Strerrmsg = Ex. tostring ();
}
Finally
{
Intcommitrowcount = Oraclebulkinsert. copiedrowcount;
Lspenttime = Oraclebulkinsert. takemilliseconds;
}
Return Blnflag;
}
Download the source code:/files/xiaobier/oraclebulkinsert.rar
