[SQL] Lying also in the shot datetime type

Write in front

Originally this thing, I do not want to summarize here, today, a beginner's friend asked me, then I have to say, you must have stepped on such a pit, did not meet, indicating that you have good luck, coding habits good. Let's get to the chase. Avoid being shot, or just a glance at this article.

An example

Test environment: sqlserver2012,vs2013

Here is a simple example, the example is very simple, no longer write the comment. A test data Sheet tb_userinfo:

A simple table, self-increasing ID, user name, registration time, from what you have seen, is allowed to empty.

Let's get a simple test program.

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Threading.Tasks;6 usingSystem.Data;7 usingSystem.Data.SqlClient;8 namespaceWolfy.sqldatetimedemo9 {Ten     class Program One     { A         Static voidMain (string[] args) -         { -             stringstrconn ="server=.; Database=test;uid=sa;pwd=sa;"; the             stringstrSQL ="INSERT into tb_userinfo values (@Name, @RegDateTime)"; -UserInfo user =NewUserInfo () {Name ="Wolfy" }; -             Try -             { +                 using(SqlConnection conn =NewSqlConnection (strconn)) -                 { +                     using(SqlCommand cmd =NewSqlCommand (strSQL, conn)) A                     { atCmd. Parameters.Add (NewSqlParameter ("@Name", user. Name)); -Cmd. Parameters.Add (NewSqlParameter ("@RegDateTime", user. Regdatetime)); - Conn. Open (); -                         if(CMD. ExecuteNonQuery () >0) -                         { -Console.WriteLine ("Registration Successful"); in                         } -                         Else to                         { +Console.WriteLine ("Registration Failed"); -                         } the                     } *                 } $             }Panax Notoginseng             Catch(Exception ex) -             { the                 Throwex; +             } A         } the     } +     /// <summary> -     ///User Information class $     /// </summary> $     classUserInfo -     { -         /// <summary> the         ///numbering -         /// </summary>Wuyi          Public intId {Set;Get; } the         /// <summary> -         ///name Wu         /// </summary> -          Public stringName {Set;Get; } About         /// <summary> $         ///Registration Time -         /// </summary> -          PublicDateTime Regdatetime {Set;Get; } -     } A}

We know that if you do not assign a value to a class when you use its properties, the default value is used.

It is visible that it has a default value.

So let's go down and see

Appeared, this anomaly, presumably a lot of people have encountered it.

As you can see, the default value of the DateTime type in VS does conflict with the range of datetime types in SQL Server. Suddenly there is such an idea, why are these two default values incompatible? Considering that the current software will be combined with the database, so design is not a bit unreasonable? This thing, we can not change, but also choose to receive. Try to standardize the design.

There are two ways to solve this problem:

Programme one:

When you add data, the value of the datetime type is assigned the current time.

1   New " Wolfy ", Regdatetime=datetime.now};

Scenario Two:

Add a default value constraint to a field of a time type when creating a data table

1 Syntax:2 ALTER TABLEtable_name3 ADD CONSTRAINTconstraint_name4 DEFAULTConstant_expression[For column_name]5 parameter Description:6 table_name: The name of the table to create the default constraint. 7 constraint_name: Default constraint name. 8Constant_expression: Default value.

1 Alter Table Add constraint default getdate  for [regdatetime]

This practice is more insurance. Recommended!

Summarize

This knowledge point is easy, but it is estimated that many beginners will step on such a pit, but fortunately, stepped on the long memory, but also has the advantage. Why is there such a problem? Maybe you trust your partner, and the subconscious thinks he'll definitely add a default constraint to the database. At the end of the gun is still himself. So in the design process, write code, must be rigorous!

[SQL] Lying also in the shot datetime type