|
I have a SQL table called Users with a DateTime field called ModifiedDT.
In this code...
results = (from u in dc.Users
select new UserEntity
{
.
.
.
ModifiedDT = u.ModifiedDT.GetValueOrDefault()
}).ToList();
The code above throws "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
if I change it to this, it works:
results = (from u in dc.Users
select new UserEntity
{
.
.
.
ModifiedDT = u.ModifiedDT.HasValue ? u.ModifiedDT : null
}).ToList();
I always thought that GetValueOrDefault() was doing this under the sheets:
public T GetValueOrDefault(T defaultValue)
{
return HasValue ? value : defaultValue;
}
Any ideas on why this doesn't work?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Kevin Marois wrote: I always thought that GetValueOrDefault() was doing this under the sheets:
Tis, according to the Reference Source[^].
Kevin Marois wrote: The code above throws "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." Sounds like it is not a DateTime type, but has been converted to a varchar datatype along the way. Cast it to a datetime in the query, see if it makes a difference.
--edit
Stupid question, but what culture is your app running under?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Tried this:
ModifiedDT = (DateTime)u.ModifiedDT.GetValueOrDefault()
Got the same error
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
So, what is the actual value of the field?
And does the Sql Server date format equal to the systems' format? If you're running a sproc to populate the query, SET the dateformat explicitly.
System.Diagnostics.Debug.WriteLine(default(DateTime).ToString()); Results in "01/01/0001 00:00:00", which would be out of range for Sql Server (see datetime (Transact-SQL) - SQL Server | Microsoft Docs[^] for valid range of DateTime).
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
It's a nullable SQL DateTime without a value. It's null
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Then I'm going to guess it is the date-format; if Sql Server has a different one than your system, switching days and months might lead to an out-of-range for the month-part.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Any luck yet? If no, I'll try to recreate the problem here
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
So a datetime , not a datetime2 ?
SQL's datetime type can't handle dates earlier than 1753/01/01 , whereas the default value for .NET's DateTime type is 0001/01/01 .
It sounds like Linq to SQL is passing in .NET's default value to the SQL query, which is then failing to convert it to a datetime .
If possible, try changing the column type to datetime2 . Otherwise, you'll need to provide a default value which is within the range of SQL's datetime type.
datetime (Transact-SQL) - SQL Server | Microsoft Docs[^]
datetime2 (Transact-SQL) - SQL Server | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
It's a DateTime.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
So you'll need to specify a default which is in range for the SQL type. Eg:
ModifiedDT = u.ModifiedDT.GetValueOrDefault(System.Data.SqlTypes.SqlDateTime.MinValue.Value)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That worked. Thanks!
Interesting though, you would think that in a Linq To SQL statement that the default for that would be SqlDateTime.MinValue.Value.
Also, any reason to use this syntax over
ModifiedDT = u.ModifiedDT.Value
Seems like they're both doing the same thing and it's less verbose.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
.Value will throw an exception if it's null , whereas .GetValueOrDefault() will return the default value instead.
I think it would be odd if .GetValueOrDefault() returned different values depending on whether it was executed by Linq to Objects or Linq to SQL.
You could always use the null-coalescing operator instead:
ModifiedDT = u.ModifiedDT ?? System.Data.SqlTypes.SqlDateTime.MinValue.Value
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
One thing I was just thinking.. I'd rather have this be null than min value, so that I can tell if it's been set
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Which is even simpler.
ModifiedDT = u.ModifiedDT
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Ya I guess this is all for nothing
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
I'm curious how you guys do exception handling in your DAL.
My Apps are usually structured like this... The Client (this case a WPF app) calls the BL, which then calls the DAL:
WPF App
private async void AddUser()
{
UserEntity user = new UserEntity
{
FirstName = "Jack",
LastName = "Stone",
UserName = "jstone",
Password = ""
};
await Task.Factory.StartNew(() =>
{
IBizObj bo = new BizObj(connString);
bo.AddUser(user);
}).ContinueWith(task =>
{
UserList.Add(user);
});
}
Bis Layer
public class BizObj : IBizObj
{
IRepository _repoisitory;
<pre>
public BizObj(string connectionString)
{
_repoisitory = new FalconRepository(connectionString);
}
public int AddUser(UserEntity entity)
{
return _repoisitory.AddUser(entity);
}
}
DAL
public int AddUser(UserEntity entity)
{
using (var dc = GetDataContext())
{
int results = 0;
try
{
User user = new User
{
FirstName = entity.FirstName,
LastName = entity.LastName,
UserName = entity.UserName,
Password = entity.Password,
CanLogIn = entity.CanLogIn,
PasswordChangeDate = entity.PasswordChangeDate,
CreatedByUserId = 1,
CreatedDT = DateTime.Now,
};
dc.Users.InsertOnSubmit(user);
dc.SubmitChanges();
results = user.Id;
}
catch (Exception e)
{
}
return results;
}
}
So, the question is wwhat is the correct way to handle the exception on the DAL? Would you handle the exception in the UI, the DAL, both?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
The general rule of thumb is that you should only handle exceptions at the point that you can actually do anything with them. In your example, for instance, what good does it do handling the exception in the DAL? What are you going to do with it? Are you going to log that there was an exception and then return a value of 0 to indicate that the request failed for some reason? In that case, you're going to have to have something in your business layer that knows that a result of 0 means that there was an exception internally. In which case, why did you bother handling it in the DAL at all?
|
|
|
|
|
I'd handle it in the DAL, log it in the DAL log with as much detail as possible, and then throw a new exception with the current exception as the inner exception. The BL can handle it or not as it choses, but since there may be multiple DAL layers, it's important (to me at least) to log it correctly for later analysis - and that means as early as possible before you lose potentially useful info, or debug opportunities.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
If my Try/Catch caught a "File Not Found" exception, the user could probably fix that - ensure the file is there.
But for something like this, an exception when adding a user, what can the user do?? Nothing really. Most likely, the exception is something beyond the ability of the user to fix.
So, again, aside from logging, what "handling" should take place? And where?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
That's up to the BL and / or PL - the DAL can't do anything about telling the user, so logging it is all it can and should do. Passing the exception up gives the higher levels a chance to report / recover / ignore the error.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Sorry, I'd have to disagree with you on this one. If you catch and log the exception in the DAL, then let this bubble up to be caught and logged again (this time as an inner exception), you're polluting your exception logs. Effectively you are double logging this exception. If the logging engine uses structured logging (and most do nowadays), then navigating to the DAL log and extracting useful information from it is straightforward. More importantly, you avoid a double exception penalty which could be costly in terms of performance.
|
|
|
|
|
What's the "user" doing while his "user record" is being added "asynchronously" and there is an error in the DAL?
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
That's the point of my question. I don't want to block the UI, but I also want to handle the exception.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Don't make it async then.
Every "transaction" should return a "return code" to the "caller"; the ui in this case.
Make a "synchronous" call with say a 5 sec. timeout. A timeout is a "no server response".
Don't leave the user hanging; you need to tell them success or failure.
In any event, if it's not a duplicate, etc. it's a "system error" and needs to be escalated.
"Graceful degeneration". If the user CANNOT proceed, these is nothing "graceful" you can do except inform the user.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
ContinueWith doesn't really belong in an async method.
private async void AddUser()
{
UserEntity user = new UserEntity
{
FirstName = "Jack",
LastName = "Stone",
UserName = "jstone",
Password = ""
};
await Task.Run(() =>
{
IBizObj bo = new BizObj(connString);
bo.AddUser(user);
});
UserList.Add(user);
} Also, you should try to avoid async void methods wherever possible.
Avoid async void methods | You’ve Been Haacked[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|