C# SqlConnection.Connect方法代码示例

        /// <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);

                    }
                }
            }
        }