一括追加データSqlBulkCopy
13067 ワード
1 using System.Data.SqlClient;
2
3 class Program
4 {
5 static void Main()
6 {
7 string connectionString = GetConnectionString();
8 // Open a sourceConnection to the AdventureWorks database.
9 using (SqlConnection sourceConnection =
10 new SqlConnection(connectionString))
11 {
12 sourceConnection.Open();
13
14 // Perform an initial count on the destination table.
15 SqlCommand commandRowCount = new SqlCommand(
16 "SELECT COUNT(*) FROM " +
17 "dbo.BulkCopyDemoMatchingColumns;",
18 sourceConnection);
19 long countStart = System.Convert.ToInt32(
20 commandRowCount.ExecuteScalar());
21 Console.WriteLine("Starting row count = {0}", countStart);
22
23 // Get data from the source table as a SqlDataReader.
24 SqlCommand commandSourceData = new SqlCommand(
25 "SELECT ProductID, Name, " +
26 "ProductNumber " +
27 "FROM Production.Product;", sourceConnection);
28 SqlDataReader reader =
29 commandSourceData.ExecuteReader();
30
31 // Open the destination connection. In the real world you would
32 // not use SqlBulkCopy to move data from one table to the other
33 // in the same database. This is for demonstration purposes only.
34 using (SqlConnection destinationConnection =
35 new SqlConnection(connectionString))
36 {
37 destinationConnection.Open();
38
39 // Set up the bulk copy object.
40 // Note that the column positions in the source
41 // data reader match the column positions in
42 // the destination table so there is no need to
43 // map columns.
44 using (SqlBulkCopy bulkCopy =
45 new SqlBulkCopy(destinationConnection))
46 {
47 bulkCopy.DestinationTableName =
48 "dbo.BulkCopyDemoMatchingColumns";
49
50 try
51 {
52 // Write from the source to the destination.
53 bulkCopy.WriteToServer(reader);
54 }
55 catch (Exception ex)
56 {
57 Console.WriteLine(ex.Message);
58 }
59 finally
60 {
61 // Close the SqlDataReader. The SqlBulkCopy
62 // object is automatically closed at the end
63 // of the using block.
64 reader.Close();
65 }
66 }
67
68 // Perform a final count on the destination
69 // table to see how many rows were added.
70 long countEnd = System.Convert.ToInt32(
71 commandRowCount.ExecuteScalar());
72 Console.WriteLine("Ending row count = {0}", countEnd);
73 Console.WriteLine("{0} rows were added.", countEnd - countStart);
74 Console.WriteLine("Press Enter to finish.");
75 Console.ReadLine();
76 }
77 }
78 }
79
80 private static string GetConnectionString()
81 // To avoid storing the sourceConnection string in your code,
82 // you can retrieve it from a configuration file.
83 {
84 return "Data Source=(local); " +
85 " Integrated Security=true;" +
86 "Initial Catalog=AdventureWorks;";
87 }
88 }