Need suggestion about CLR Trigger+ WCF + Windows Service

Question

0.00/5 (No votes)

See more:

Hi All,

Am trying to acheive a task like

Step1:

Any new record get insert in to my DB,i use clr trigger to pass the new record details (eg: Filename and Location)to WCF.I acheived so far here.

Now am planning to move to step 2 like:

write a ftpwebrequest inside WCF and download the file from destination (the filename and location i use to get from DB using CLR Trigger)

Now i got confused here,whats going to happend if there are more than 1 record inserted at a time

How the CLR trigger going to handle ? Once the information passed to WCF,for some reason the FTP failed what's going to happen here.

I was thinking rather than pass the DB info back to WCF,write it in a text file and then write some windows service to read the info and get the file thru FTP.once it done delete that info from the text file.

Please guide me which one is make sense or any other way to work around this issues.

Thanks in Advance

Posted 23-Jan-12 12:34pm

shan1395

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sriram Chitturi · Accepted Answer · 2012-01-23T14:11:00

Solution 1

Each CLR trigger call to the WCF service will be on a separate thread. So as long as your FTP request code is thread safe there should not be a problem.

What exactly are you trying to achieve using a Windows Service here?
If you are replacing the WCF service with a Windows Service, it is not going to make any difference.

Instead of that, see if this is possible -
a) for every new record, see if there is a primary key and send it along with FileName and Location to the service
b) add a try catch block and trap any error in getting the file through FTP
c) log the DB row primary key with the exception to any convenient location or even update the database, if it is reachable from the service.

Updating the status in the database, is a good idea.
This way you will be able to at least get the failed rows.
How you want to handle the failed cases is another story.

Posted 23-Jan-12 14:11pm

Sriram Chitturi

Comments

shan1395 23-Jan-12 20:48pm

Thanks Sriram, the only thing its hesitate me here is,if clr trigger send the file name and location to WCF then my FTP process start there,in some reason it takes long time or any failure to download the file thru FTP at the same time the CLR trigger the next record then its gonna queue isn't. for avoid that i thought write it in a text file then use window service to go and read the filename and location ,download the file from FTP then go to the next record.Anyway am update the status in DB for each success full download.

Sriram Chitturi 25-Jan-12 6:40am

Shan
why is the request to web service getting queued? If this is because of a restriction with the number of FTP calls you can make?
If you are implementing a windows service, then remove the CLR trigger itself and keep polling the table for pending rows at regular intervals based on status, that is much better in terms of design.
If there is a restriction on number of FTP calls you can make and the downloading is queued in the windows service also, then use multithreading and a semaphore to control the number of threads downloading from FTP.

shan1395 30-Jan-12 17:59pm

Thanks a lot Sriram ,Even i was thinking to write a windows to pool the table but i felt CLR is better than open DB connection quite often thru interval time period.please correct me if am wrong.Am not implementing any windows service,changed the design like CLR Trigger --->WCF--->FTPWebREquest-->FTP. FTPWebRequest is inside the WCF so,i don't need windows service in the present scenario.

Sriram Chitturi 30-Jan-12 19:03pm

Shan
The connections to the DB are pooled by ADO.NET as long as the connection string is constant. So pretty much you will be reusing the same connection every time you are polling the database.
Here are few problems with the WCF approach
a) there may be a restriction on the number of concurrent connections you can make from a process to FTP server, so the individual requests have to know how many requests are actively dowloading
b) the FTP server can restrict the number of connections you can me from a machine or for a login - the effect is same as (a) above
c) WCF request timeout, but you can control that

As long as you do not see the above issues, you are fine. Otherwise just go by the Windows service and it is easier to implement.