ASP.NET Tutorial/ADO.net Database/SqlBulkCopy

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

Bulk loading data from one table to another database (C#)

   <source lang="csharp">

<%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %> <script runat="server">

   protected void btnBulkCopy_Click(object sender, EventArgs e)
   {
       String YourDatabaseConString;
       String NorthWindConString;
       SqlConnection YourDatabaseCon = new SqlConnection();
       SqlConnection NorthwindCon = new SqlConnection();
       SqlCommand YourDatabaseCom = new SqlCommand();
       SqlDataReader YourDatabaseReader;
       YourDatabaseConString = ConfigurationManager.ConnectionStrings["DSN_YourDatabase"].ConnectionString;
       
       NorthWindConString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
       YourDatabaseCon.ConnectionString = YourDatabaseConString;
       YourDatabaseCom.Connection = YourDatabaseCon;
       YourDatabaseCom.rumandText = " SELECT ID, First_Name, Last_Name, "YourDatabase" as Source FROM MailingList_Temp ";
       YourDatabaseCom.rumandType = CommandType.Text;
       YourDatabaseCom.Connection.Open();
       SqlBulkCopy NorthWindBulkOp;
       NorthWindBulkOp = new SqlBulkCopy(NorthWindConString, 
                               SqlBulkCopyOptions.UseInternalTransaction);
       NorthWindBulkOp.DestinationTableName = "Employees";
       NorthWindBulkOp.ColumnMappings.Add("Id", "EmployeeID");
       NorthWindBulkOp.ColumnMappings.Add("First_Name", "FirstName");
       NorthWindBulkOp.ColumnMappings.Add("Last_Name", "LastName");
       SqlBulkCopyColumnMapping JobTitleColMap;
       JobTitleColMap = new SqlBulkCopyColumnMapping("Source", "Title");
       NorthWindBulkOp.ColumnMappings.Add(JobTitleColMap);
       NorthWindBulkOp.BulkCopyTimeout = 500000000;
       NorthWindBulkOp.SqlRowsCopied += 
           new SqlRowsCopiedEventHandler(OnRowsCopied);
       NorthWindBulkOp.NotifyAfter = 1000;
       YourDatabaseReader = YourDatabaseCom.ExecuteReader();
       try
       {
           NorthWindBulkOp.WriteToServer(YourDatabaseReader);
       }
       catch (Exception ex)
       {
           lblResult.Text = ex.Message;
       }
       finally
       {
           YourDatabaseReader.Close();
       }
   }
   private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args)
   {
       lblCounter.Text += args.RowsCopied.ToString() + " rows are copied
"; }

</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">

   <title>Bulk Loading Large Volume Data</title>

</head> <body>

   <form id="form1" runat="server">
       <asp:Button ID="btnBulkCopy" Runat="server" Text="Start Bulk Copy" /> 
       

<asp:Label ID="lblResult" Runat="server"></asp:Label>

<asp:Label ID="lblCounter" Runat="server"></asp:Label>
   </form>

</body> </html> File: Web.config <configuration>

 <connectionStrings>
       <add name="DSN_Northwind" 
            connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
            providerName="System.Data.SqlClient" />
       <add name="DSN_YourDatabase" 
            connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=YourDatabase;Integrated Security=True"
            providerName="System.Data.SqlClient" />    </connectionStrings>

</configuration></source>


Bulk loading data from one table to another database (VB)

   <source lang="csharp">

<%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %> <script runat="server">

   Sub btnBulkCopy_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBulkCopy.Click
       Dim YourDatabaseConString As String
       Dim NorthWindConString As String
       Dim YourDatabaseCon As SqlConnection = New SqlConnection()
       Dim NorthwindCon As SqlConnection = New SqlConnection()
       Dim YourDatabaseCom As SqlCommand = New SqlCommand()
       Dim YourDatabaseReader As SqlDataReader
       YourDatabaseConString = ConfigurationManager.ConnectionStrings("DSN_YourDatabase").ConnectionString
       NorthWindConString = ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString
       YourDatabaseCon.ConnectionString = YourDatabaseConString
       YourDatabaseCom.Connection = YourDatabaseCon
       YourDatabaseCom.rumandText = " SELECT ID, First_Name, Last_Name, " & _
                               " "YourDatabase" as Source FROM MailingList_Temp "
       YourDatabaseCom.rumandType = CommandType.Text
       YourDatabaseCom.Connection.Open()
       Dim NorthWindBulkOp As SqlBulkCopy
       NorthWindBulkOp = New SqlBulkCopy(NorthWindConString, _
                               SqlBulkCopyOptions.UseInternalTransaction)
       NorthWindBulkOp.DestinationTableName = "Employees"
       NorthWindBulkOp.ColumnMappings.Add("Id", "EmployeeID")
       NorthWindBulkOp.ColumnMappings.Add("First_Name", "FirstName")
       NorthWindBulkOp.ColumnMappings.Add("Last_Name", "LastName")
       Dim JobTitleColMap As SqlBulkCopyColumnMapping
       JobTitleColMap = New SqlBulkCopyColumnMapping("Source", "Title")
       NorthWindBulkOp.ColumnMappings.Add(JobTitleColMap)
       NorthWindBulkOp.BulkCopyTimeout = 500000000
       AddHandler NorthWindBulkOp.SqlRowsCopied, _
                   New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
       NorthWindBulkOp.NotifyAfter = 1000
       YourDatabaseReader = YourDatabaseCom.ExecuteReader()
       Try
           NorthWindBulkOp.WriteToServer(YourDatabaseReader)
       Catch ex As Exception
           " Write error handling code here
           lblResult.Text = ex.Message
       Finally
           YourDatabaseReader.Close()
       End Try
   End Sub
   Private Sub OnSqlRowsCopied(ByVal sender As Object, _
           ByVal args As SqlRowsCopiedEventArgs)
       lblCounter.Text += args.RowsCopied.ToString() + " rows are copied
" End Sub

</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">

   <title>Bulk Loading Large Volume Data</title>

</head> <body>

   <form id="form1" runat="server">
       <asp:Button ID="btnBulkCopy" Runat="server" Text="Start Bulk Copy" /> 
       

<asp:Label ID="lblResult" Runat="server"></asp:Label>

<asp:Label ID="lblCounter" Runat="server"></asp:Label>
   </form>

</body> </html> File: Web.config <configuration>

 <connectionStrings>
       <add name="DSN_Northwind" 
            connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
            providerName="System.Data.SqlClient" />
       <add name="DSN_YourDatabase" 
            connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=YourDatabase;Integrated Security=True"
            providerName="System.Data.SqlClient" />    </connectionStrings>

</configuration></source>