Hi, I'm using Open Access for some years now and today I upgraded from 2012.Q2 to 2015.Q1 and now I have some major problems.
I retrieve some data from Sql Server 2008R2 and fill up an Excel sheet. No problems over 3 years.
This is the method I've been using all the time (some modifications now, because I wantetd to test)
01.
public
IEnumerable<TSCalc> LoadByCustomer(
int
calcversionId,
int
customerId,
int
typeId)
02.
{
03.
string
sql =
"Select a.TSCalcId As TSCalcId\n"
+
04.
"From dbo.tblTSCalc a\n"
+
05.
"Inner Join dbo.tblTSCalcHead b\n"
+
06.
" On b.TSCalcHeadId = a.TSCalcHeadId_FK\n"
+
07.
"Inner Join dbo.tblCustomerTSCalc c\n"
+
08.
" On c.TSCalcHeadId_FK = a.TSCalcHeadId_FK\n"
+
09.
"Where a.CalcversionId_FK = @CalcversionId\n"
+
10.
" And c.CustomerId_FK = @CustomerId\n"
+
11.
" And b.TSCalcTypeId_FK = @TypeId"
;
12.
var calcversion =
new
OAParameter
13.
{
14.
ParameterName =
"@CalcversionId"
,
15.
Value = calcversionId
16.
};
17.
18.
var customer =
new
OAParameter
19.
{
20.
ParameterName =
"@CustomerId"
,
21.
Value = customerId
22.
};
23.
var typ =
new
OAParameter
24.
{
25.
ParameterName =
"@TypeId"
,
26.
Value = typeId
27.
};
28.
29.
IEnumerable<TSCalc> timeseriesCalcList;
30.
using
(AbsDbContextNew ctx =
new
AbsDbContextNew())
31.
{
32.
var f =
new
FetchStrategy();
33.
f.LoadWith<TSCalc>(t => t.Data);
34.
f.LoadWith<TSCalc>(t => t.Head);
35.
ctx.FetchStrategy = f;
36.
//timeseriesCalcList = ctx.ExecuteQuery<TSCalc>(sql, CommandType.Text, calcversion, customer, typ);
37.
// 2. Retrieve the OAConnection instance.
38.
using
( IDbConnection oaConnection = ctx.Connection )
39.
{
40.
// 4. Create a new instance of the OACommand class.
41.
using
( IDbCommand oaCommand = oaConnection.CreateCommand() )
42.
{
43.
// 5. Initialize OAParameters
44.
//IDbDataParameter idParam = oaCommand.CreateParameter();
45.
//idParam.ParameterName = @"Id";
46.
//idParam.DbType = System.Data.DbType.Int32;
47.
//idParam.Value = 1;
48.
// 6. Set the CommandText property.
49.
oaCommand.CommandText = sql;
50.
// 7. Add parameters to the Parameters collection of the command.
51.
oaCommand.Parameters.Add(calcversion);
52.
oaCommand.Parameters.Add(customer);
53.
oaCommand.Parameters.Add(typ);
54.
// 8. Execute the command.
55.
using
( IDataReader reader = oaCommand.ExecuteReader() )
56.
{
57.
return
ctx.Translate<TSCalc>(reader
as
DbDataReader).ToList();
58.
}
59.
}
60.
}
61.
62.
63.
}
64.
65.
//using ( AbsDbContextNew ctx = new AbsDbContextNew() )
66.
//{
67.
// var f = new FetchStrategy();
68.
// f.LoadWith<TSCalc>(t => t.Data);
69.
// f.LoadWith<TSCalc>(t => t.Head);
70.
// return timeseriesCalcList = ctx.TSCalcs.Where(t => t.VersionId == calcversionId && t.Head.CustomerTSCalcs.);
71.
//}
72.
}
Originally I was using this statement
1.
var f =
new
FetchStrategy();
2.
f.LoadWith<TSCalc>(t => t.Data);
3.
f.LoadWith<TSCalc>(t => t.Head);
4.
f.FetchStrategy = f;
5.
timeseriesCalcList = ctx.ExecuteQuery<TSCalc>(sql, CommandType.Text, calcversion, customer, typ);
and returned the timeseriesCalcList object.
So the problem is now that the ActiveConnection is set to 10 (was always enough) and after a few requets I can't get any connections anymore.
The code is 100 % identicle with the old release, so please give me an advice how to solve it.
I can't do any exports anymore :-(
This are the settings from the working branch.
Hwere you can see the profiler views ... look there are all connections used but I have wrapped the context within using statement.
Thank you very much,
Norbert