Skip to content

Instantly share code, notes, and snippets.

@cbilson
Created October 22, 2013 21:41
Show Gist options
  • Save cbilson/7108690 to your computer and use it in GitHub Desktop.
Save cbilson/7108690 to your computer and use it in GitHub Desktop.
Example of sidestepping NHibernate to bulk insert a bunch of test records into the database. This is lame but it's fast.
public static class BulkInsert
{
private static SqlConnection Connection
{
get { return NHibernateSession.NHibernateSessionManager.Instance.GetSession().Connection as SqlConnection; }
}
public static void Meters(IEnumerable<EisMeter> meters)
{
var metersArray = meters.ToArray();
var lastMeterId = (int) new SqlCommand("select max(id) from EIS_Meter", Connection).ExecuteScalar();
var meterTable = new DataTable();
meterTable.Columns.Add("Id");
meterTable.Columns.Add("MeterNumber");
meterTable.Columns.Add("ServiceMeterTypeId");
foreach (var meter in metersArray)
{
meterTable.Rows.Add(++lastMeterId, meter.MeterNumber, meter.EisServiceMeterType.Id);
}
var bulkInsertMeters = new SqlBulkCopy(Connection) {DestinationTableName = "EIS_Meter"};
bulkInsertMeters.ColumnMappings.Add("Id", "Id");
bulkInsertMeters.ColumnMappings.Add("MeterNumber", "MeterNumber");
bulkInsertMeters.ColumnMappings.Add("ServiceMeterTypeId", "ServiceMeterTypeId");
bulkInsertMeters.WriteToServer(meterTable);
Channels(Connection, metersArray.SelectMany(x => x.EisChannel));
}
public static void Channels(SqlConnection Connection, IEnumerable<EisChannel> channels)
{
var channelArray = channels.ToArray();
var lastChannelId = (int) new SqlCommand("select max(id) from EIS_Channel", Connection).ExecuteScalar();
var channelTable = new DataTable();
channelTable.Columns.Add("Id");
channelTable.Columns.Add("MeterId");
channelTable.Columns.Add("ChannelNumber");
channelTable.Columns.Add("ChannelTypeId");
channelTable.Columns.Add("IntervalTypeId");
channelTable.Columns.Add("PostivieFlow");
foreach (var channel in channelArray)
{
channelTable.Rows.Add(++lastChannelId, channel.EisMeter.Id, channel.ChannelNumber,
channel.EisMeterChannelType.Id, channel.EisIntervalType.Id,
true);
}
var bulkInsertChannels = new SqlBulkCopy(Connection) {DestinationTableName = "EIS_Channel"};
bulkInsertChannels.ColumnMappings.Add("Id", "Id");
bulkInsertChannels.ColumnMappings.Add("MeterId", "MeterId");
bulkInsertChannels.ColumnMappings.Add("ChannelNumber", "ChannelNumber");
bulkInsertChannels.ColumnMappings.Add("ChannelTypeId", "ChannelTypeId");
bulkInsertChannels.ColumnMappings.Add("IntervalTypeId", "IntervalTypeId");
bulkInsertChannels.ColumnMappings.Add("PostivieFlow", "PostivieFlow");
bulkInsertChannels.WriteToServer(channelTable);
Usage(Connection, channelArray.SelectMany(x => x.EisChannelUsage));
}
public static void Usage(SqlConnection Connection, IEnumerable<EisChannelUsage> usages)
{
var lastUsageId = (long) new SqlCommand("select max(id) from EIS_ChannelUsage", Connection).ExecuteScalar();
var usageTable = new DataTable();
usageTable.Columns.Add("Id");
usageTable.Columns.Add("ChannelId");
usageTable.Columns.Add("DataTimestamp");
usageTable.Columns.Add("DataTypeId", typeof(int));
usageTable.Columns.Add("Data");
usageTable.Columns.Add("IntervalTypeId", typeof(int));
usageTable.Columns.Add("Receivedtimestamp");
foreach (var usage in usages)
{
usageTable.Rows.Add(++lastUsageId, usage.EisChannel.Id, usage.DataTimestamp, usage.DataQuality,
usage.Data, usage.EisIntervalType.Id, usage.Receivedtimestamp);
}
var bulkInsertUsage = new SqlBulkCopy(Connection) {DestinationTableName = "EIS_ChannelUsage"};
bulkInsertUsage.ColumnMappings.Add("Id", "Id");
bulkInsertUsage.ColumnMappings.Add("ChannelId", "ChannelId");
bulkInsertUsage.ColumnMappings.Add("DataTimestamp", "DataTimestamp");
bulkInsertUsage.ColumnMappings.Add("DataTypeId", "DataTypeId");
bulkInsertUsage.ColumnMappings.Add("Data", "Data");
bulkInsertUsage.ColumnMappings.Add("IntervalTypeId", "IntervalTypeId");
bulkInsertUsage.ColumnMappings.Add("Receivedtimestamp", "Receivedtimestamp");
bulkInsertUsage.WriteToServer(usageTable);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment