в 15:30, 26 мая 2010

2010-05-26T15:30:57Z

Admin: 1 версия

2010-05-26T11:57:00Z

1 версия

Новая страница

== 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<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" /> 
<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></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<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" /> 
<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></source>

← Предыдущая	Версия 15:30, 26 мая 2010
(нет различий)

ASP.NET Tutorial/ADO.net Database/SqlBulkCopy - История изменений

в 15:30, 26 мая 2010

Admin: 1 версия