Reply

steven.ye

Member

123 Points

ExcuteReader lost db connetion

Jan 06, 2013 11:38 PM|LINK

Hi All,

I have a problem of ExcuteReader to lost db connetion, follow the error message, this happens in my code of creating cahe, this cache is updated by SqlCacheDependency, when I met this problem, I have to recycle this web application pool or restart IIS, then it can work.

Do someone have any idea to fix this problem.

Thanks for any help.

Reply

ignatandrei

All-Star

134973 Points

21638 Posts

Moderator

MVP

Re: ExcuteReader lost db connetion

Jan 07, 2013 01:53 AM|LINK

create the connection local to ExecuteReader and close right after you are finishing .

And next time please show sample code.

My .NET blog : http://msprogrammer.serviciipeweb.ro/ |2012 Programmer tools

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: ExcuteReader lost db connetion

Jan 08, 2013 12:26 AM|LINK

steven.ye
I have a problem of ExcuteReader to lost db connetion

Hi,

Have you re-initialized the Connection?

Please show us your codes and point out where your error message is thrown out?

Reply

steven.ye

Member

123 Points

34 Posts

Re: ExcuteReader lost db connetion

Jan 09, 2013 02:45 AM|LINK

Thank everyone for all helps.

I got the returned error details:

Server Error in '/' Application.

ExecuteReader requires an open and available Connection. The connection's current state: Broken.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state: Broken.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state: Broken.]
   System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +5034177
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +87
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.ExecuteReader() +12
   System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +1266
   System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +113
   System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +344
   System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +34
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +315
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   Woodslane.Controllers.HomeController.loadLandingPage(String LandingPageCode) in C:\Inetpub\wwwroot\woodslane_mvc\Controllers\HomeController.cs:43
   Woodslane.Controllers.HomeController.Index() in C:\Inetpub\wwwroot\woodslane_mvc\Controllers\HomeController.cs:16
   lambda_method(Closure , ControllerBase , Object[] ) +62
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +208
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
   System.Web.Mvc.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12() +55
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +263
   System.Web.Mvc.<>c__DisplayClass17.<InvokeActionMethodWithFilters>b__14() +19
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +191
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +343
   System.Web.Mvc.Controller.ExecuteCore() +116
   System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +97
   System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +10
   System.Web.Mvc.<>c__DisplayClassb.<BeginProcessRequest>b__5() +37
   System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +21
   System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +12
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.<>c__DisplayClasse.<EndProcessRequest>b__d() +50
   System.Web.Mvc.SecurityUtil.<GetCallInAppTrustThunk>b__0(Action f) +7
   System.Web.Mvc.SecurityUtil.ProcessInApplicationTrust(Action action) +22
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +60
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +8836913
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +184

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

I have checked the above message, this error happened in my Home controller, code is:

                    List<string> lstISBN13s = qf.Select(m => m.isbn13).ToList();
                    System.Data.Linq.Table<vw_Product> vw_product_table = getCache.getProductsCache();
                    var qOnix = (from p in vw_product_table
                                 where lstISBN13s.Contains(p.ProductXRef)
                                 select p).ToList();

when codes return qOnix result, it apears this problem, getCache.getProductsCache() code is below, it will generate cache.

    public static Table<vw_Product> getProductsCache()
    {
        Table<vw_Product> productTable = null;
        string CacheKey = ConfigurationManager.AppSettings["productskey"];
        if (HttpContext.Current.Cache[CacheKey] == null)
        {
            //SqlCacheDependency
            string DBEntryName = "OnixDB";

            SqlCacheDependency SqlCacheDependency1 = new SqlCacheDependency(DBEntryName, "ProductDetails");
            SqlCacheDependency SqlCacheDependency2 = new SqlCacheDependency(DBEntryName, "PublicationStatusDecode");
            SqlCacheDependency SqlCacheDependency3 = new SqlCacheDependency(DBEntryName, "ProductContributor");
            SqlCacheDependency SqlCacheDependency4 = new SqlCacheDependency(DBEntryName, "ProductSupply");
            SqlCacheDependency SqlCacheDependency5 = new SqlCacheDependency(DBEntryName, "ProductSupplier");
            SqlCacheDependency SqlCacheDependency6 = new SqlCacheDependency(DBEntryName, "Product");
            SqlCacheDependency SqlCacheDependency7 = new SqlCacheDependency(DBEntryName, "Publisher");
            SqlCacheDependency SqlCacheDependency8 = new SqlCacheDependency(DBEntryName, "BICSubjectDetailDecode");
            SqlCacheDependency SqlCacheDependency9 = new SqlCacheDependency(DBEntryName, "BICSubjectDecode");
            SqlCacheDependency SqlCacheDependency10 = new SqlCacheDependency(DBEntryName, "BICSubjectAreaDecode");
            SqlCacheDependency SqlCacheDependency11 = new SqlCacheDependency(DBEntryName, "ICPV");
            SqlCacheDependency SqlCacheDependency12 = new SqlCacheDependency(DBEntryName, "MarketStatusDecode");
            SqlCacheDependency SqlCacheDependency13 = new SqlCacheDependency(DBEntryName, "MSPAudience");
            SqlCacheDependency SqlCacheDependency14 = new SqlCacheDependency(DBEntryName, "ICINT1");
            SqlCacheDependency SqlCacheDependency15 = new SqlCacheDependency(DBEntryName, "ICINT2");
            SqlCacheDependency SqlCacheDependency16 = new SqlCacheDependency(DBEntryName, "Currency");
            SqlCacheDependency SqlCacheDependency17 = new SqlCacheDependency(DBEntryName, "Discount");
            SqlCacheDependency SqlCacheDependency18 = new SqlCacheDependency(DBEntryName, "ProductAvailabilityDecode");
            SqlCacheDependency SqlCacheDependency19 = new SqlCacheDependency(DBEntryName, "ProductBlurb");
            SqlCacheDependency SqlCacheDependency20 = new SqlCacheDependency(DBEntryName, "ProductFormDecode");
            SqlCacheDependency SqlCacheDependency21 = new SqlCacheDependency(DBEntryName, "ProductNotificationDecode");
            SqlCacheDependency SqlCacheDependency22 = new SqlCacheDependency(DBEntryName, "ICIllus");

            //AggregateCacheDependency
            CacheDependency[] CacheDependencys =
                {
                    SqlCacheDependency1,
                    SqlCacheDependency2,
                    SqlCacheDependency3,
                    SqlCacheDependency4,
                    SqlCacheDependency5,
                    SqlCacheDependency6,
                    SqlCacheDependency7,
                    SqlCacheDependency8,
                    SqlCacheDependency9,
                    SqlCacheDependency10,
                    SqlCacheDependency11,
                    SqlCacheDependency12,
                    SqlCacheDependency13,
                    SqlCacheDependency14,
                    SqlCacheDependency15,
                    SqlCacheDependency16,
                    SqlCacheDependency17,
                    SqlCacheDependency18,
                    SqlCacheDependency19,
                    SqlCacheDependency20,
                    SqlCacheDependency21,
                    SqlCacheDependency22
                };
            AggregateCacheDependency AggregateCacheDependency = new AggregateCacheDependency();
            AggregateCacheDependency.Add(CacheDependencys);

            productTable = OnixDC.vw_Products;
           //OnixDC.Connection.Close();
            HttpContext.Current.Cache.Insert(CacheKey,
                productTable,
                AggregateCacheDependency,
                Cache.NoAbsoluteExpiration,
                Cache.NoSlidingExpiration
                );
        }
        productTable = (Table<vw_Product>)HttpContext.Current.Cache[CacheKey];
        return productTable;
    }

if I met this problem , I have to recycle web application pool or restart iis (ver 6), do anybody have ideas?

Thank you very much

Reply

steven.ye

Member

123 Points

34 Posts

Re: ExcuteReader lost db connetion

Jan 09, 2013 10:10 PM|LINK

Hi Decker,

Thank you for your message, I have put some error message and my part code in thread, do you have any idea to solve that?

I am using Linq to get data, and save this result in cache, this error message pointed out when excuting filter data (Linq) with cache, it apears error, I think this step does not connect DB, isn't it strange?

Thnak you for your help

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: ExcuteReader lost db connetion

Jan 10, 2013 12:31 AM|LINK

steven.ye
steven.ye

Hi,

Can you debug your application by pressing F5 and then tell us what the matter?

Reply

steven.ye

Member

123 Points

34 Posts

Re: ExcuteReader lost db connetion

Jan 10, 2013 02:03 AM|LINK

HI,

The debugging is fine, I don't get any problem; actually it 's fine when it is running on our inner testing server, it often got this problem in our internet server.

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: ExcuteReader lost db connetion

Jan 11, 2013 03:32 AM|LINK

steven.ye
it often got this problem in our internet server.

Do you deploy it on IIS?

Reply

steven.ye

Member

123 Points

34 Posts

Re: ExcuteReader lost db connetion

Jan 15, 2013 12:57 AM|LINK

yes, it is working with iIS.

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: ExcuteReader lost db connetion

Jan 15, 2013 11:19 AM|LINK

steven.ye

yes, it is working with iIS.

Hi,

So it doesn't work properly on local machine?

ExcuteReader lost db connetion RSS

10 replies

steven.ye

ExcuteReader lost db connetion

ignatandrei

Re: ExcuteReader lost db connetion

Decker Dong ...

Re: ExcuteReader lost db connetion

steven.ye

steven.ye

Re: ExcuteReader lost db connetion

Server Error in '/' Application.

ExecuteReader requires an open and available Connection. The connection's current state: Broken.

steven.ye

Re: ExcuteReader lost db connetion

Decker Dong ...

Re: ExcuteReader lost db connetion

steven.ye

steven.ye

Re: ExcuteReader lost db connetion

Decker Dong ...

Re: ExcuteReader lost db connetion

steven.ye

steven.ye

Re: ExcuteReader lost db connetion

Decker Dong ...

Re: ExcuteReader lost db connetion

steven.ye

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft