Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Insert list into a database table using Oracle array binding (a type of bulk insert)
/// <summary>
/// Insert list into a database table using Oracle array binding (a type of bulk insert).
/// </summary>
/// <typeparam name="T">Type of data in list.</typeparam>
/// <param name="listOfData">List to insert.</param>
/// <param name="tableName">Table name.</param>
/// <returns>Rows affected.</returns>
public int Insert<T>(List<T> listOfData, string tableName) where T : class, new()
{
try
{
var fieldNamesAndValues = new Dictionary<string, object[]>();
var parameterNames = new List<string>();
var properties = TypeDescriptor.GetProperties(typeof(T));
// Build list of field names in dictionary
for (int i = 0; i < properties.Count; i++)
{
PropertyDescriptor prop = properties[i];
fieldNamesAndValues.Add(prop.Name, new object[listOfData.Count]);
parameterNames.Add($":{prop.Name}"); // Build list of parameters
}
// Populate field values in dictionary
for (int i = 0; i < listOfData.Count; i++)
{
T item = listOfData[i];
for (int p = 0; p < properties.Count; p++)
{
PropertyDescriptor prop = properties[p];
fieldNamesAndValues[prop.Name][i] = properties[p].GetValue(item);
}
}
using (OracleConnection connection = new OracleConnection(new Crypto().decrypt(FiduciaryConnString)))
{
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = $"INSERT INTO {tableName}({string.Join(",", fieldNamesAndValues.Keys)}) VALUES({string.Join(",", parameterNames)})";
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
cmd.ArrayBindCount = listOfData.Count; // Enables bulk insert
// Generate command parameters
foreach (var pair in fieldNamesAndValues)
{
PropertyDescriptor prop = properties.Find(pair.Key, false);
Type type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
OracleDbType dbType;
if (type == typeof(Int16))
dbType = OracleDbType.Int16;
else if (type == typeof(Int32))
dbType = OracleDbType.Int32;
else if (type == typeof(Int64))
dbType = OracleDbType.Int64;
else if (type == typeof(double))
dbType = OracleDbType.Double;
else if (type == typeof(decimal))
dbType = OracleDbType.Decimal;
else if (type == typeof(DateTime))
dbType = OracleDbType.Date;
else if (type == typeof(Char))
dbType = OracleDbType.Char;
else if (type == typeof(string))
dbType = OracleDbType.Varchar2;
else if (type == typeof(bool))
dbType = OracleDbType.Boolean;
else
throw new Exception($"Cannot handle mapping property type {type.Name} to an OracleDbType for property '{prop.Name}' of type {typeof(T).FullName}.");
cmd.Parameters.Add($":{pair.Key}", dbType, pair.Value, ParameterDirection.Input); // Note that the value is an array
}
var rowsAffected = cmd.ExecuteNonQuery();
connection.Close();
return rowsAffected;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.