I use stored procedure to get paged list and this is my method:
using(SqlConnection conn = new SqlConnection(_connectionString)) {
using(SqlCommand cmd = new SqlCommand("[GetPagedSP]", conn)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//Passing Parameters
**Update**
SqlParameter spCreationDate = new SqlParameter("@CreationDate_9", CreationDate);
spCreationDate.IsNullable = true;
cmd.Parameters.Add(spCreationDate);
// ........
//Finished Passing Parameters
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read()) {
//Get Values
}
conn.Close();
}
}
And this is my stored procedure command:
CREATE TABLE #PagingTemp (
[RowId] [bigint] IDENTITY(1,1) NOT NULL,
[RecordId] [bigint]
);
INSERT INTO [#PagingTemp] ([RecordId])
SELECT [CAR].[Id]
FROM [Article] AS [CAR] ;
SELECT [CAR].*
FROM [Collections].[Article] AS [CAR]
INNER JOIN [#PagingTemp] AS [PT] ON [CAR].[Id] = [PT].[RecordId]
WHERE [PT].[RowId] BETWEEN 1 AND 50;
When I run the query in SQL every thing is fine, But in .NET I have an exception on this line:
SqlDataReader dr = cmd.ExecuteReader();
and the exception is:
System.Data.SqlTypes.SqlTypeException was unhandled by user code
Message=SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Source=System.Data
Update
The example of run Query in SQL:
That's so weird I don't understand what happened?
I don't have any datetime
value that be bigger than 12/31/9999
or less than 1/1/1753
I just have some Nullable datetime values with null value in database.
what do you think? where is the problem?
nullable
columns that returns null, but in object side my types areDateTime?
, also before any mapping an exception throw in this line:SqlDataReader dr = cmd.ExecuteReader();
– Saeid Jul 30 '12 at 7:50CreationDate
is probably zero, not null but zero. Could you please verify that? – Nikola Markovinović Jul 30 '12 at 8:10