ASP.NET Tutorial/ADO.net Database/SqlBulkCopy — различия между версиями

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

Текущая версия на 11:57, 26 мая 2010

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

<%@ 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<Br>";
    }
</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">
    <div>
        <asp:Button ID="btnBulkCopy" Runat="server" Text="Start Bulk Copy" />&nbsp;
        <br />
        <br />
        <asp:Label ID="lblResult" Runat="server"></asp:Label>
        <br />
        <br />
        <asp:Label ID="lblCounter" Runat="server"></asp:Label>    
    </div>
    </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>


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

<%@ 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<Br>"
    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">
    <div>
        <asp:Button ID="btnBulkCopy" Runat="server" Text="Start Bulk Copy" />&nbsp;
        <br />
        <br />
        <asp:Label ID="lblResult" Runat="server"></asp:Label>
        <br />
        <br />
        <asp:Label ID="lblCounter" Runat="server"></asp:Label>    
    </div>
    </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>
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/SqlBulkCopy&oldid=2748»