Tuesday, December 27, 2011

sql bulk copy in .net

Title:How to use SQL bulk copy in Asp.net using C#.net

Description:
SQL bulk copy is a concept of copying records from source database to destination database in asp.net.Here the source database can be any data base.The destination should be SQL server.Because SQL bulk copy is compatible with sql server.The advantage of SQL bulk copy concept is copying records will be faster with less burden of of developer.This process requires three steps.Here i have taken one button to perform this operation
1.Creating data reader and write into source data-base table
2.Creating sqlbulkcopy component with destination database component
3.Providing data reader to bulk copy component for writing to destination database table.

Imports System.Data.Oledb
Imports System.Data.SqlClient
//button click event
private sub button1_Click
Dim cn As New OleDbConnection("provider=msdaora.1;user Id=scott;password=tiger")
Dim cmd As New OleDbCommand("select*from dept",cn)
Dim dr As OleDbDataReader
cn.Open()
dr=cmd.ExcuteReader()
//creating destination Database connection and bulk copy component
Dim Dcn As New OleDbConnection("user Id=sa;password=;Database=invoice")
Dcn.Open()
Dim Bcopy As New SqlBulkCopy(Dcn)
Bcopy.DestinationTableName="dept"
//provide data reader to bulk copy component for reading from source and copying destination
Dcopy.WriteToServe(dr)
//Writretoserver will read using Dr and write to sqlserver table
dr.Close()
Cn.Close()
Dcn.close()
MsgBox("Copied Succesfully")
End Sub
)
C#:
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.Oledb;
using System.Data.SqlClient;


//button click event

OleDbConnection cn = new OleDbConnection("provider=msdaora.1;user Id=scott;password=tiger");
OleDbCommand cmd = new OleDbCommand("select*from dept", cn);
OleDbDataReader dr = default(OleDbDataReader);
cn.Open();
dr = cmd.ExcuteReader();
//creating destination Database connection and bulk copy component
OleDbConnection Dcn = new OleDbConnection("user Id=sa;password=;Database=invoice");
Dcn.Open();
SqlBulkCopy Bcopy = new SqlBulkCopy(Dcn);
Bcopy.DestinationTableName = "dept";
//provide data reader to bulk copy component for reading from source and copying destination
Dcopy.WriteToServe(dr);
//Writretoserver will read using Dr and write to sqlserver table
dr.Close();
Cn.Close();
Dcn.close();
Interaction.MsgBox("Copied Succesfully");
Post a Comment

Bel