SQLDataAdapter and Remote Update

[Fred Chateau]

I have a Windows Forms program that use to import CSV data to a SQL database
on a remote server. Previously, I simply had to wipe the server database and
import the new data over the old data. Now, I'm going to be adding mixed
records, some of which will update existing data on the server and some of
which are new records that must be inserted into the database. The number of
records is considerable, on the order of 300,000 records.

In the past, I simply called DataAdapter.FillSchema, and started importing
the data. My understanding is now I'm going to need to call
DataAdapter.Fill, which will download the entire server table before
importing the local CSV file. Is this necessary? Is there anyway to avoid
downloading all the server data and then uploading it back to the server?

To make matters more complicated, I have a composite primary key consisting
of two table columns. How should I handle this situation?

 

[William \(Bill\) Vaughn]

Sure, you need to use one of the SSIS/bulk copy schemes. See
http://www.developer.com/article.php/10915_3702826_1 for an article that
discusses using the SqlBulkCopy class in ADO.NET 2.0. It does exactly what
you want to do. Remember, don't import directly into a production table.
Move the data from the CSV to a temp working table (with no indexes) and
then write a SP to insert the rows into the production table after they have
been filtered for valid data.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Zhi-Xin Ye [MSFT]]

Hi Fred,

This is Zhi-Xin Ye, I'm currently monitoring this issue.

Does Wiliam's suggestion make sense to you? You can try the SqlBulkCopy
class to bulk copy the data from client to server. What's more, we can bulk
copy rows from a DataTable based on their row states, for example, we alter
the content in a data table in the application, and then want to only copy
rows that have been modified to the server, we could simply call the
WriteToServer() method like this:

sqlBulkCopy.WriteToServer(sourceTable, DataRowState.Modified);

For more information about the SqlBulkCopy class, you can refer to these
links:

Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server
http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-you
r-client-to-sql-server

Bulk Copy Operations in SQL Server (ADO.NET)
http://msdn.microsoft.com/en-us/library/7ek5da1a.aspx

SqlBulkCopy.WriteToServer Method (DataTable, DataRowState)
http://msdn.microsoft.com/en-us/library/09esk03d.aspx

Please try the suggestion and let me know the result. If you encounter any
difficulty, please don't hesitate to let me know. I will be happy to be of
assistance.

Have a splendid day!

Sincerely,
Zhi-Xin Ye
Microsoft Managed Newsgroup Support Team

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Fred Chateau]

Thank you, Zhi-Xin, for your response.

I'll take a look at the articles and see if I can work some additional code
into the program that uses SQLBulkCopy when it's time to move the data back
and forth.

--
Regards,

Fred Chateau
fchateauAtComcastDotNet