Geek asks and answers

Call a stored procedure inside a thread to update multiple records

I am trying to call a stored procedure for various unique objects. A stored procedure for a single object takes about 33 seconds. So I decided to call it using threads.

Here are some of my tests:

public bool ExecuteTaxRateLinkingParallel(int mapID, int createdBy)
{
    try
    {
        int snapshotID = (int)(HttpContext.Current.Session[GlobalConstant.snapShotID]);
        List<TaxEntity> taxEntities = new List<TaxEntity>();
        List<Task> tasks = new List<Task>();
        using (var ctx = new TopazDbContainer())
        {
            taxEntities = ctx.TaxEntities.AsParallel().Where(t => t.IsActive == true).ToList<TaxEntity>();
        }

        Parallel.ForEach<TaxEntity>(taxEntities, (entity) =>
        {
            //SqlConnection connection; SqlTransaction trans; SqlCommand command;
            // break this into pieces of 5
            var task = Task.Factory.StartNew(() =>
            {
                using (var pctx = new TopazDbContainer())
                {
                    try
                    {
                        int taxEntityID = entity.TaxEntityID;
                        pctx.CommandTimeout = 5000;
                        //string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["TOPAZDBConnectionStringParallel"].ConnectionString;
                        //connection = new SqlConnection(connectionString);
                        //command = new SqlCommand("dbo.[Usp_TaxRatesLinkingParallel]", connection);
                        //trans = connection.BeginTransaction();
                        //command.CommandType = CommandType.StoredProcedure;
                        //command.Parameters.AddWithValue("@MapID", mapID);
                        //command.Parameters.AddWithValue("@UserID", createdBy);
                        //command.Parameters.AddWithValue("@TaxEntityID", taxEntityID);
                        //command.Parameters.AddWithValue("@SnapshotID", snapshotID);
                        //connection.Open();
                        //command.CommandTimeout = 5000;
                        //command.ExecuteReader().AsParallel();

                        pctx.ContextOptions.LazyLoadingEnabled = true;

                        //pctx.ExecuteStoreCommand("Exec [Usp_TaxRatesLinkingParallel] @MapID={0},@UserID={1},@TaxEntityID={2},@SnapshotID{3}", new SqlParameter("MapID", mapID), new SqlParameter("UserID", createdBy), new SqlParameter("TaxEntityID", taxEntityID), new SqlParameter("SnapshotID", snapshotID));
                        var param = new DbParameter[] { new SqlParameter("UserID", createdBy), new SqlParameter("TaxEntityID", taxEntityID), new SqlParameter("SnapshotID", snapshotID) };
                        pctx.ExecuteStoreCommand("Exec [Usp_TaxRatesLinkingParallel] @MapID,@UserID,@TaxEntityID,@SnapshotID", param);

                        //var result = output.FirstOrDefault();
                    }
                    catch (TaskCanceledException tx)
                    {
                    }
                    catch (Exception e)
                    {
                    }
                    finally
                    {
                        pctx.SaveChanges();
                        pctx.Connection.Close();
                    }
                }
            }, TaskCreationOptions.PreferFairness);

            tasks.Add(task);

            try
            {
                Task.WaitAll(tasks.ToArray());
            }
            catch (AggregateException ae)
            {
                ae.Handle((x) =>
                {
                    if (x is UnauthorizedAccessException)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                });
            }
            catch (Exception ex)
            {
                throw ex;
            }
        });

        return true;
    }
    catch (Exception ex)
    {
        TopazErrorLogs.AddTopazErrorLogBL(ex, 1, 1);
        throw new TopazCustomException(GlobalConstant.errorMessage);
    }
}

For some of the above statements, the SP seems to work fine, but when I check from the application or from the backend, the records are not updated.

Help is needed!

+3
source share
2 answers

If you are not already using .NET 4.5, you can use these extension methods to execute your asynchronous commands.

using System.Diagnostics.Contracts;
using System.Threading.Tasks;
using System.Xml;

namespace System.Data.SqlClient
{
    public static class SqlCommandExtensions
    {
        public static Task<SqlDataReader> ExecuteReaderAsync(this SqlCommand command)
        {
            Contract.Requires(command != null);
            return ExecuteReaderAsync(command, null);
        }

        public static Task<SqlDataReader> ExecuteReaderAsync(this SqlCommand command, object state)
        {
            Contract.Requires(command != null);
            return Task.Factory.FromAsync<SqlDataReader>(command.BeginExecuteReader, command.EndExecuteReader, state);
        }

        public static Task<XmlReader> ExecuteReaderXmlAsync(this SqlCommand command)
        {
            Contract.Requires(command != null);
            return ExecuteReaderXmlAsync(command, null);
        }

        public static Task<XmlReader> ExecuteReaderXmlAsync(this SqlCommand command, object state)
        {
            Contract.Requires(command != null);
            return Task.Factory.FromAsync<XmlReader>(command.BeginExecuteXmlReader, command.EndExecuteXmlReader, state);
        }

        public static Task<int> ExecuteNonQueryAsync(this SqlCommand command)
        {
            Contract.Requires(command != null);
            return ExecuteNonQueryAsync(command, null);
        }

        public static Task<int> ExecuteNonQueryAsync(this SqlCommand command, object state)
        {
            Contract.Requires(command != null);
            return Task.Factory.FromAsync<int>(command.BeginExecuteNonQuery, command.EndExecuteNonQuery, state);
        }
    }
}
+1
source

, . , :

(TAP) ASP.NET MVC 4

async/await:

public async Task<IEnumerable<Car>> GetCarsAsync() {

    var connectionString = 
        ConfigurationManager.ConnectionStrings["CarGalleryConnStr"].ConnectionString;

    var asyncConnectionString = new SqlConnectionStringBuilder(connectionString) {
        AsynchronousProcessing = true
    }.ToString();

    using (var conn = new SqlConnection(asyncConnectionString)) {
        using (var cmd = new SqlCommand()) {

            cmd.Connection = conn;
            cmd.CommandText = selectStatement;
            cmd.CommandType = CommandType.Text;

            conn.Open();

            using (var reader = await cmd.ExecuteReaderAsync()) {

                return reader.Select(r => carBuilder(r)).ToList();
            }
        }
    }
}

.

0

More articles:


All Articles