Language: C#.NET 2.0
Technology: ASP.NET 2.0
Database: Oracle 10g
Hi All,
Could any one of you please suggest the BEST method to:
1. Fetch data from a very large .csv file (around 8 MB) and Inert the same
into Oracle Table using a Bulk Insert.
Any suggestions on the directions to execute the above mentioned task will
be highly appreciated.
Thanks in advance,
Kuldeep
Hello:
I handled this exact situation. I will list here exactly what I did.
You can just take what you want from it.
1) I created a IDataReader base class specifically for reading
the .CVS. I would recommend that you skip this step and just pull the
data directly from the file and convert the data to the appropriate
type. I would also recommend BufferedStream class to minimize hits to
the hard drive. If it will fit, I would also recommend trying to get
the entire file into memory prior (depending on your situation).
2) Use OracleCommand (I assume you're using Oracle). Instead of
passing a value to a parameter, pass an array of values. Set the
ArrayBindSize to the size of the array. I would recommend tweeking
with the max array size to see what performs the best. In my
situation, there was an obvious grouping among data; so I made my
array size the size of groups.
If you need to make the parameters array dynamic, create a List<type>
for each parameter. Add to the lists until you are ready to dump to
the database. Then convert the List<type>s to arrays using the
ToArray() method.
Send me an email if you need a more detailed example. Furthermore, I
have a small BulkInserter class I wrote a many ages ago to make this a
little more simple. Here it is:
public class BulkInserter
{
private readonly Dictionary<string, List<object>> values
= new Dictionary<string, List<object>>();
public void AddValue(string parameterName, object value)
{
List<object> paramValues;
if (!values.TryGetValue(parameterName, out paramValues))
{
values[parameterName] = paramValues = new
List<object>();
}
paramValues.Add(value);
}
public int ExecuteNonQuery(OracleCommand command)
{
int paramArrayCount = 0;
foreach (KeyValuePair<string, List<object>> paramData in
values)
{
paramArrayCount = Math.Max(paramArrayCount,
paramData.Value.Count);
command.Parameters[paramData.Key].Value =
paramData.Value.ToArray();
}
command.ArrayBindCount = paramArrayCount;
return command.ExecuteNonQuery();
}
}
