Constructing a Date data type using C # _mssql2005

/***********************************
Author: trieagle (let you see the shadow of the Wall)
Date: 2009.8.14
NOTE: Reprint please keep this information
************************************/
To construct a date data type using C #
The date type is not implemented in SQL Server2005, but provides a good extensibility that can be constructed with the CLR to construct the date type. Some of it is written in reference to the FC code.
Steps:
1, new project in VS 2005, select C#-->> database-->>sql Server project, enter project name
2, select the database to connect
3. Right-click on project name, add-->> new Item-->> user-defined type-->> input type name
4, the code is as follows:

Copy Code code as follows:

Using System;
Using System.Data;
Using System.Data.SqlClient;
Using System.Data.SqlTypes;
Using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedTypeFormat.UserDefined, isbyteordered=true,maxbytesize = 20, Validationmethodname= "Validatedate")]
public struct date:inullable,ibinaryserialize
{
Private members
private bool M_null;
private string m_date;
public override string ToString ()
{
if (this.m_null)
return "NULL";
Else
{
return this.m_date;
}
}
public bool IsNull
{
Get
{
return m_null;
}
}
public static Date Null
{
Get
{
Date h = new Date ();
H.m_null = true;
return h;
}
}
public static date Parse (SqlString s)
{
if (S.isnull | | (!s.isnull && s.value.equals (""))
return Null;
Else
{
Date U = new Date ();
string[] xy = S.value.split ("". ToCharArray ());
U.m_date = xy[0];
if (!u.validatedate ())
throw new ArgumentException ("Invalid Time");
return u;
}
}
public string _date
{
Get
{
return this.m_date;
}
Set
{
M_null = true;
M_date = value;
if (! Validatedate ())
throw new ArgumentException ("Invalid Time");
}
}
public void Write (System.IO.BinaryWriter W)
{
The Byte header = (byte) (this. IsNull? 1:0);
W.write (header);
if (header = = 1)
{
Return
}
W.write (this.m_date);
}
public void Read (System.IO.BinaryReader R)
{
byte Header = R.readbyte ();
if (header = = 1)
{
This.m_null = true;
Return
}
This.m_null = false;
This.m_date = R.readstring ();
}
private bool Validatedate ()//Determine whether the time is valid
{
Try
{
DateTime dt = Convert.todatetime (m_date);
return true;
}
Catch
{
return false;
}
}
}

5, according to F5 for deployment
6, Testing:

Copy Code code as follows:

CREATE TABLE TB (ID INT,DT dbo. Date DEFAULT CONVERT (dbo. Date,convert (VARCHAR), GETDATE (), 120));
INSERT into TB (ID) VALUES (1)
SELECT Id,dt=dt. ToString () from TB;
/*
Results:
ID DT
1 2009-08-14
*/
DROP TABLE TB;

Note:
1. If you want to add and subtract dates for date types, you can call the ToString () method output as a string, then convert to a datetime type, and then calculate the date.
2, can not directly use SELECT * from TB to output the value of the DT column, so that the output is a series of binary number