I receive this error for each connection:
System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.May I ask what you're doing to consume all the connections?
i have looked at several guides, and have come up with some code, although for some reason i am unable to get it to work, and receive an error:
Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[]'.
System.InvalidCastException:
at UserDefinedFunctions.FillRow(Object obj, String& PID)
.
Below is my code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
public partial class UserDefinedFunctions
{
const string SELECT =
@."SELECT pointData.PID
FROM pointData
INNER JOIN pointDevices ON pointData.PID = pointDevices.PID
WHERE pointDevices.UUID = @.UUID AND
DATEADDED >= @.DATE_START AND
DATEADDED <= @.DATE_STOP
ORDER BY DATEADDED";
[SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.Read, TableDefinition="PID nvarchar(50)")]
public static IEnumerable createJourney(SqlString UUID, DateTime DATE_START,DateTime DATE_STOP, SqlDouble JOURNEY_DELAY, SqlDouble JOURNEY_DISTANCE)
{
ArrayList RAW_PID_LIST = new ArrayList();
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand command = new SqlCommand(SELECT,conn);
command.Parameters.AddWithValue("@.UUID",UUID);
command.Parameters.AddWithValue("@.DATE_START",DATE_START);
command.Parameters.AddWithValue("@.DATE_STOP",DATE_STOP);
SqlDataReader reader = command.ExecuteReader();
using (reader)
{
while (reader.Read())
{
RAW_PID_LIST.Add(reader[0]);
}
}
}
return RAW_PID_LIST;
}
private static void FillRow(Object obj, out string PID)
{
object[] row = (object[])obj;
PID = (string)row[0];
}
};
could someone give me a clue as to why i might be getting this error please.
thank you.
FillRow gets only one object at a time. So, you should try this one:
private static void FillRow(Object obj, out string PID)
{
PID = (string)obj;
}
