Created
October 22, 2013 21:41
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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