Data Transfer - Best Practice

  • Thread starter Thread starter Paul Say
  • Start date Start date
P

Paul Say

I have an SQL database and I want to import data from a pervasive database.
What would be the fastest way to read the records from the pervasive
database and write them to the SQL Database (I don't want to use the SQL
DTS). I would like to use .NET. As I will want to creat a similar import
interface to read from the pervasive database to an ACCESS database.


Thanks

Paul
 
Hi Paul,

In this case (if you do not want to use DTS) you could load data from the
Pervasive database into DataSet. Then you would need to *mark* all the rows
inside of this DataSet as added. You could do this only copying DataRow from
one DataTable to another one. Then you could create Insert command and use
DataAdapter with this command to insert all the rows into SQL Server. But
this way will work much slower than DTS.
Another way is, after loading DataSet from pervasive, pass XML
representation of the DataSet into SQL Server stored procedure and insert
all the data from XML in one shot. It should work faster (my tests showed
about 50% increase in performance), but this way will work with SQL Server
2000 and not with SQL Server 7
 
Where can I find an example of how to pass an XML representation of the
dataset and the SQL stored procedure to import the representation

Paul
 
Hi Paul,

Here is an example, which I have prepared some time ago

Dim lcConnectionString As String
Dim loConnection As SqlClient.SqlConnection
Dim loDataAdapter As SqlClient.SqlDataAdapter
Dim loInsertCommand As SqlClient.SqlCommand
Dim loParameter As SqlClient.SqlParameter
Dim loDataSet As DataSet
Dim lnResult As Int32


Try
loDataSet = New DataSet()
loDataSet.ReadXml("E:\NET_Projects\XML_Batch\Products_30000.XML")

lcConnectionString = "Data Source='MYSERVER';" & _
"Initial Catalog=MYDB;Integrated
Security=SSPI;Pooling=False;" & _
"Connection Reset=False;"

loConnection = New SqlClient.SqlConnection(lcConnectionString)
loConnection.Open()

loInsertCommand = New
SqlClient.SqlCommand("usp_Batch_Insert", loConnection)
loInsertCommand.CommandType = CommandType.StoredProcedure
loParameter = loInsertCommand.Parameters.Add("@BATCH",
SqlDbType.Text)

loParameter.Value = loDataSet.GetXml()

lnResult = loInsertCommand.ExecuteNonQuery()

loParameter = Nothing
loInsertCommand.Dispose()
loInsertCommand = Nothing

loDataSet.Dispose()
loDataSet = Nothing

loConnection.Dispose()
loConnection = Nothing

Catch loE As Exception

MessageBox.Show(loE.Message)

End Try


CREATE PROCEDURE usp_Batch_Insert
@BATCH TEXT

AS

DECLARE @HDOC INT

EXEC sp_xml_preparedocument @HDOC OUTPUT, @BATCH

INSERT INTO Products
(ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitPrice,
UnitsInStock,
UnitsOnOrder,
ReorderLevel,
Discontinued)
SELECT
ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitPrice,
UnitsInStock,
UnitsOnOrder,
ReorderLevel,
Discontinued

FROM OPENXML (@HDOC, '/Products/Row',2)
WITH
(ProductName nvarchar(40),
SupplierID int,
CategoryID int,
QuantityPerUnit nvarchar(20),
UnitPrice money,
UnitsInStock smallint,
UnitsOnOrder smallint,
ReorderLevel smallint,
Discontinued bit)

EXEC sp_xml_removedocument @HDOC

RETURN
 
Back
Top