/// <summary> /// Transfer data into a destination table. /// </summary> /// <param name="tableName"> /// The name of the table that will be written to. /// If a query is not provided this will also be the name of the table that data is read from.</param> /// <param name="sqlBulkCopyOptions">Options used by <see cref="SqlBulkCopy"/>.</param> /// <param name="query">An optional query that will be used as the source of data.</param> /// <param name="queryParams">Optional parameters that are used by the source data query.</param> /// <param name="sqlTransaction">Optional <see cref="SqlTransaction"/> object that will be used to construct the <see cref="SqlBulkCopy"/>.</param> /// <param name="sqlBulkCopyCustomizer">Optional delegate that can customize the <see cref="SqlBulkCopy"/> object.</param> /// <param name="smartColumnMapping"> /// Passing true will cause source columns to be mapped to destination columns by name and computed columns to be ignored. /// A value of false will result in ordinal based mapping of all columns. Defaults to true. /// </param> public void TransferData(string tableName, string query = null, SqlParameter[] queryParams = null, SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.Default, SqlTransaction sqlTransaction = null, Action<SqlBulkCopy> sqlBulkCopyCustomizer = null, bool smartColumnMapping = true) { if (tableName == null) throw new ArgumentNullException(nameof(tableName)); if (!tableName.Contains("[")) tableName = tableName.Split('.').Select(n => "[" + n + "]").Join("."); using (var source = new SqlConnection(_sourceConnString)) using (var dest = new SqlConnection(_destConnString)) using (var bcp = new SqlBulkCopy(dest, sqlBulkCopyOptions, null)) using (var sourceCmd = source.CreateCommand()) using (source.Connect()) using (dest.Connect()) { query = query.IsNullOrWhiteSpace() ? $"SELECT * FROM {tableName}" : query; bcp.EnableStreaming = true; bcp.DestinationTableName = tableName; bcp.BulkCopyTimeout = (int)TransferTimeout.TotalSeconds; sourceCmd.CommandType = CommandType.Text; sourceCmd.CommandText = query; sourceCmd.CommandTimeout = (int)TransferTimeout.TotalSeconds; if (queryParams != null && queryParams.Any()) sourceCmd.Parameters.AddRange(queryParams); using (var reader = sourceCmd.ExecuteReader()) { if (smartColumnMapping) { var schema = reader.GetSchemaTable(); MapColumns(schema, bcp, dest); } if (sqlBulkCopyCustomizer != null) sqlBulkCopyCustomizer(bcp); bcp.WriteToServer(reader); } } }
public void Test1() { const string table = "IP2.Location"; using (var conn = new SqlConnection("server=.;database=afsx;integrated security=true")) using(conn.Connect()) { const SqlBulkCopyOptions opts = SqlBulkCopyOptions.KeepNulls; using (var bi = new BulkInserter<Record>(conn, table, copyOptions: opts, bufferSize:20000)) { conn.ExecuteSql($"TRUNCATE TABLE {table}"); using ( var reader = new StreamReader( @"C:\dev\webbanking\download\IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE.CSV")) { var records = from rec in reader.ReadCsv() select new Record { IpFrom = Convert.ToInt64(rec[0]), IpTo = Convert.ToInt64(rec[1]), CountryCode = rec[2], CountryName = rec[3], Region = rec[4], City = rec[5], Latitude = rec[6].ConvertTo<decimal?>(), Longitude = rec[7].ConvertTo<decimal?>(), ZipCode = rec[8] }; bi.PostBulkInsert += (sender, args) => Console.WriteLine(args.Items.Length); bi.Insert(records); } } } }