Click here to Skip to main content
14,384,911 members
Rate this:
Please Sign up or sign in to vote.
See more:
i have c# application when user click on button it do two thing
first save data into table using stored procedure
second save the same data in another server using stored procedure but this server is on another location and connection alittle slow
i need to make second procdure run async i don't need a result from it
as when user click he must wait to the two procedures to be completed
i need the user interface activated as soon as the first procedure completed

What I have tried:

make a trigger on the first table that inserted data goto the second database and remove the 2nd proceudre but it take a long time also
Posted
Updated 2-Oct-19 2:44am
v2
Comments
Richard MacCutchan 2-Oct-19 5:52am
   
"no luck"
Do not rely on luck. You need to use logic, and your developer skill and experience.
ahmad_yossef 2-Oct-19 6:10am
   
so tell me what logic i should use?
Richard MacCutchan 2-Oct-19 6:16am
   
Are you serious? Look at your question, it tells us nothing that would help to explain what is going on in your application. Please edit your question and add some proper details.
ahmad_yossef 2-Oct-19 6:28am
   
ok my fault i will edit it right now
ahmad_yossef 2-Oct-19 6:39am
   
sorry i have updated the question
Rate this:
Please Sign up or sign in to vote.

Solution 1

Quote:
i need to make second procdure run async i don't need a result from it
Instead of making it run asynchronously, why not use this method in C# instead of SQL stored procedure. See here, SqlCommand Class (System.Data.SqlClient) | Microsoft Docs[^], all that needs to be done for the second query is, you execute the function and forget that it was ever called. You can do that using the Async counterparts of the query execution functions.

SqlCommand.ExecuteReaderAsync Method (System.Data.SqlClient) | Microsoft Docs[^] (Explore other options for other type of queries)

If you use the Async counterparts, then you can execute the first stored procedure, wait for the response (asynchronously, as it helps you write reactive-apps and doesn't block the threads unnecessarily) and for the second one, just call the function and do not wait for a response. The down side for this? The Task that gets created would be in the Thread Pool and underlying framework would still assume that this needs to be processed and waited for, thus causing memory leaks if your app exits prematurely. In this case, you can use SQL counterpart (see below).

There are some things that you need to know before diving into that.
c# - Horrible performance using SqlCommand Async methods with large data - Stack Overflow[^]

But if you still want to go with a SQL approach, then you might want to use SQL Jobs and create a job that runs in the background, sql server - Can I launch a stored procedure and immediately return without waiting for it to finish? - Database Administrators Stack Exchange[^].
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

As usual there are a variety of ways you may be able to get around this, depending on your needs, the situation, and your abilities.

Within your application, you could use one of the SqlCommand objects asynchronous methods; such as ExecuteNonQueryAsync(). This is a relatively easy go to

SQL Agent- This could be used in a variety of ways, such as using as inserting your new data into a staging table and then running an Agent Job every so often to execute the stored procedure with that data.
A more advanced way of using the SQL Agent would be to create an SSIS package to perform an ETL function; grabbing the data from SQL 1 and applying the changes to SQL 2

And naturally, these may just be bandages fixing a symptom of poor DB design; so going through the schemas involved is always on the table.

and then there is the query that you are running; or to be more precise, the data being attached as parameters. Are you trying to push through a huge BLOB data element?
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100