Have Geek Brain

Updating SQL database table using webservice

I have a problem and I have been looking at the internet for a long and long time, but I couldn't find anything.

I have a small application that sucks in a web service. It then passes the results of other applications to these web services through its own web service and stores the query in a table.

What I'm trying to do is quickly import the results into a table from a dataset.

Option 1 is that I loop through all the rows in the dataset and use every insert. The problem with this is that the web service of small applications is slow and will slow down.

Option 2 is bulk loading the dataset into sql. Bad news for me - I don't know how! Can anyone please help?

+1


a source to share


4 answers


You can use SqlBulkCopy . A simple SqlBulkCopy might look like this:

DataTable dtMyData = ... retrieve records from WebService
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString)) {
    bulkCopy.BulkCopyTimeout = 120; // timeout in seconds, default is 30
    bulkCopy.DestinationTableName = "MyTable"; 
    bulkCopy.WriteToServer(dtMyData);
}


If you are handling a large amount of data, you can also set the BatchSize property .

+2


a source


This sounds like a powerful crowded web service.



Instead, you might (or even additionally) consider whether it's asynchronous or multithreading. Otherwise, you've just created a link between two web applications that you may have split to avoid them in the first place.

0


a source


Using bulk loading can be a pain for a variety of reasons, starting with how you do it is specific to your DBMS. (The next thing that usually gets you is where the data file should go.)

However, you can significantly increase performance in portable mode by simply executing all of yours INSERT

in one transaction:

BEGIN TRANSACTION
INSERT ...
INSERT ...
...
COMMIT TRANSACTION
0


a source


If time is on your side, you can learn to use SQL Server Service Broker technology, but the learning curve is quite steep initially.

http://msdn.microsoft.com/en-us/library/ms166043(SQL.90).aspx

You can create a web service that runs a stored procedure function to insert the results of your process.

0


a source






More articles:


All Articles