Multiple queries using same datacontext throws SqlException

Posted by Raj on Stack Overflow See other posts from Stack Overflow or by Raj
Published on 2009-11-24T06:38:30Z Indexed on 2010/05/17 11:50 UTC
Read the original article Hit count: 252

I've search control with which I'm trying to implement search as user types something. I'm using Linq to SQL to fire queries against the database. Though it works fine usually, when user types the queries really fast some random SqlException is thrown. These are the two different error message I stumbled across recently:

A severe error occurred on the current command. The results, if any, should be discarded.

Invalid attempt to call Read when reader is closed.

Edit: Included code

DataContextFactory class:

public DataContextFactory(IConnectionStringFactory connectionStringFactory)
{
    this.dataContext = new RegionDataContext(connectionStringFactory.ConnectionString);
}

public DataContext Context
{
    get { return this.dataContext; }
}

public void SaveAll()
{
    this.dataContext.SubmitChanges();
}

Registering IDataContextFactory with Unity

// Get connection string from Application.Current settings
ConnectionInfo connectionInfo = Application.Current.Properties["ConnectionInfo"] as ConnectionInfo;
// Register ConnectionStringFactory with Unity container as a Singleton
this.container.RegisterType<IConnectionStringFactory, ConnectionStringFactory>(new ContainerControlledLifetimeManager(),
    new InjectionConstructor(connectionInfo.ConnectionString));
// Register DataContextFactory with Unity container
this.container.RegisterType<IDataContextFactory, DataContextFactory>();

Connection string:

Data Source=.\SQLEXPRESS2008;User Instance=true;Integrated Security=true;AttachDbFilename=C:\client.mdf;MultipleActiveResultSets=true;

Using datacontext from a repository class:

// IDataContextFactory dependency is injected by Unity
public CompanyRepository(IDataContextFactory dataContextFactory)
{
    this.dataContextFactory = dataContextFactory;
}

// return List<T> of companies
var results = this.dataContextFactory.Context.GetTable<CompanyEntity>()
        .Join(this.dataContextFactory.Context.GetTable<RegionEntity>(),
            c => c.regioncode,
            r => r.regioncode,
            (c, r) => new { c = c, r = r })
        .Where(t => t.c.summary_region != null)
        .Select(t => new { Id = t.c.compcode, Company = t.c.compname, Region = t.r.regionname }).ToList();

What is the work around?

© Stack Overflow or respective owner

Related posts about sql-server-2008-express

Related posts about linq-to-sql