ثبت و نگهداری تاریخ خورشیدی در SQL Server از دیرباز یکی از نگرانی‌های برنامه‌نویسان و
طراحان پایگاه داده‌ها بوده است. در این نوشتار، راه‌کار تعریف یک DataType در SQL Server 2012 به روش CLR آموزش داده
خواهد شد.
در ویژوال استودیو یک پروژه‌ی جدید از نوع SQL Server Database Project
به شکل زیر ایجاد کنید:
نام پروژه را به یاد تقویم خیام، prgJalaliDate می‌گذارم. در Solution Explorer روی نام پروژه راست‌کلیک کرده، سپس روی Add New Item کلیک کنید. در پنجره‌ی بازشده مطابق شکل SQL CLR C# User Defined Type را برگزینید؛ سپس نام JalaliDateType را برای آن انتخاب کنید.
(در کد زیر همه‌ی توابع لازم برای مقداردهی به سال، ماه، روز، ساعت، دقیقه و ثانیه و البته گرفتن مقدار از آن‌ها، تبدیل تاریخ خورشیدی به میلادی، گرفتن تاریخ به تنهایی، گرفتن زمان به تنهایی، افزایش یا کاهش زمان برپایه‌ی یکی از متغیرهای زمان و بررسی و اعتبارسنجی انواع بخش‌های زمان گنجانده شده است. در صورت پرسش یا پیشنهاد روی هر کدام در قسمت نظرات، پیام خود را بنویسید.)
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable()]
[SqlUserDefinedType(Format.Native)]
public struct JalaliDate : INullable
{
private Int16 m_Year;
private byte m_Month;
private byte m_Day;
private byte m_Hour;
private byte m_Minute;
private byte m_Second;
private bool is_Null;
public Int16 Year
{
get
{
return (this.m_Year);
}
set
{
m_Year = value;
}
}
public byte Month
{
get
{
return (this.m_Month);
}
set
{
m_Month = value;
}
}
public byte Day
{
get
{
return (this.m_Day);
}
set
{
m_Day = value;
}
}
public byte Hour
{
get
{
return (this.m_Hour);
}
set
{
m_Hour = value;
}
}
public byte Minute
{
get
{
return (this.m_Minute);
}
set
{
m_Minute = value;
}
}
public byte Second
{
get
{
return (this.m_Second);
}
set
{
m_Second = value;
}
}
public bool IsNull
{
get
{
return is_Null;
}
}
public static JalaliDate Null
{
get
{
JalaliDate jl = new JalaliDate();
jl.is_Null = true;
return (jl);
}
}
public override string ToString()
{
if (this.IsNull)
{
return "NULL";
}
else
{
return this.m_Year.ToString("D4") + "/" + this.m_Month.ToString("D2") + "/" + this.m_Day.ToString("D2") + " " + this.Hour.ToString("D2") + ":" + this.Minute.ToString("D2") + ":" + this.Second.ToString("D2");
}
}
public static JalaliDate Parse(SqlString s)
{
if (s.IsNull)
{
return Null;
}
System.Globalization.PersianCalendar pers = new System.Globalization.PersianCalendar();
string str = Convert.ToString(s);
string[] JDate = str.Split(' ')[0].Split('/');
JalaliDate jl = new JalaliDate();
jl.Year = Convert.ToInt16(JDate[0]);
byte MonthsInYear = (byte)pers.GetMonthsInYear(jl.Year);
jl.Month = (byte.Parse(JDate[1]) <= MonthsInYear ? (byte.Parse(JDate[1]) > 0 ? byte.Parse(JDate[1]) : (byte)1) : MonthsInYear);
byte DaysInMonth = (byte)pers.GetDaysInMonth(jl.Year, jl.Month); ;
jl.Day = (byte.Parse(JDate[2]) <= DaysInMonth ? (byte.Parse(JDate[2]) > 0 ? byte.Parse(JDate[2]) : (byte)1) : DaysInMonth);
if (str.Split(' ').Length > 1)
{
string[] JTime = str.Split(' ')[1].Split(':');
jl.Hour = (JTime.Length >= 1 ? (byte.Parse(JTime[0]) < 23 && byte.Parse(JTime[0]) >= (byte)0 ? byte.Parse(JTime[0]) : (byte)0) : (byte)0);
jl.Minute = (JTime.Length >= 2 ? (byte.Parse(JTime[1]) < 59 && byte.Parse(JTime[1]) >= (byte)0 ? byte.Parse(JTime[1]) : (byte)0) : (byte)0);
jl.Second = (JTime.Length >= 3 ? (byte.Parse(JTime[2]) < 59 && byte.Parse(JTime[2]) >= (byte)0 ? byte.Parse(JTime[2]) : (byte)0) : (byte)0);
}
else { jl.Hour = 0; jl.Minute = 0; jl.Second = 0; }
return (jl);
}
public SqlString GetDate()
{
return this.m_Year.ToString("D4") + "/" + this.m_Month.ToString("D2") + "/" + this.m_Day.ToString("D2");
}
public SqlString GetTime()
{
return this.Hour.ToString("D2") + ":" + this.Minute.ToString("D2") + ":" + this.Second.ToString("D2");
}
public SqlDateTime ToGregorianTime()
{
System.Globalization.PersianCalendar pers = new System.Globalization.PersianCalendar();
return SqlDateTime.Parse(pers.ToDateTime(this.Year, this.Month, this.Day, this.Hour, this.Minute, this.Second, 0).ToString());
}
public SqlString JalaliDateAdd(SqlString interval, int increment)
{
System.Globalization.PersianCalendar pers = new System.Globalization.PersianCalendar();
DateTime dt = pers.ToDateTime(this.Year, this.Month, this.Day, this.Hour, this.Minute, this.Second, 0);
string CInterval = interval.ToString();
bool isConvert = true;
switch (CInterval)
{
case "Year":
dt = pers.AddYears(dt, increment);
break;
case "Month":
dt = pers.AddMonths(dt, increment);
break;
case "Day":
dt = pers.AddDays(dt, increment);
break;
case "Hour":
dt = pers.AddHours(dt, increment);
break;
case "Minute":
dt = pers.AddMinutes(dt, increment);
break;
case "Second":
dt = pers.AddSeconds(dt, increment);
break;
default:
isConvert = false;
break;
}
if (isConvert == true)
{
this.Year = (Int16)pers.GetYear(dt);
this.Month = (byte)pers.GetMonth(dt);
this.Day = (byte)pers.GetDayOfMonth(dt);
this.Hour = (byte)pers.GetHour(dt);
this.Minute = (byte)pers.GetMinute(dt);
this.Second = (byte)pers.GetSecond(dt);
}
return this.m_Year.ToString("D4") + "/" + this.m_Month.ToString("D2") + "/" + this.m_Day.ToString("D2") + " " + this.Hour.ToString("D2") + ":" + this.Minute.ToString("D2") + ":" + this.Second.ToString("D2");
}
}از منوهای بالا روی منوی Bulild و سپس گزینه‌ی Publish prgJalaliDate کلیک کتید:
در پنجره‌ی بازشده روی دکمه‌ی Edit کلیک کنید سپس تنظیمات مربوط به اتصال به پایگاه داده را انجام دهید.
روی دکمه‌ی OK کلیک کنید و سپس در پنجره‌ی اولیه، روی دکمه‌ی Publish کلیک کتید:
به همین سادگی، DataType مربوطه در SQL Server 2012 ساخته می‌شود. خبر خوش اینکه شما می‌توانید با راست‌کلیک روی نام پروژه و انتخاب گزینه‌ی Properties در قسمت Project Setting تنظیمات مربوط به نگارش SQL Server را انجام دهید. (از نگارش 2005 به بعد در VS 2012 پشتیبانی می‌شود.)
اکنون زمان آن رسیده است که DataType ایجادشده را در SQL Server 2012 بیازماییم. SQL Server را باز کنید و دستور زیر را در آن اجرا کتید.
USE Northwind GO CREATE TABLE dbo.TestTable ( Id int NOT NULL IDENTITY (1, 1), TestDate dbo.JalaliDate NULL ) ON [PRIMARY] GO
اکنون چند رکورد درون این جدول درج می‌کنیم:
Insert into TestTable (TestDate) Values ('1392/02/09'),('1392/02/09 22:40'),('1392/12/30 22:40')
sp_configure 'clr enabled', 1 Reconfigure
Insert into TestTable (TestDate) Values ('1392/02/09'),('1392/02/09 22:40'),('1392/12/30 22:40')
ملاحظه می‌کنید که داده‌ها در جدول مربوطه ذخیره شده است. در رکورد نخست چون ساعت، دقیقه و ثانیه تعریف نشده است؛ به طور هوشمند صفر درج شده است. در رکورد دوم، ساعت و دقیقه مقدار دارد ولی ثانیه صفر ثبت شده است. و در رکورد سوم چون سال 1392 کبیسه نیست؛ به صورت هوشمند آخرین روز ماه به جای روز ثبت شده است. هرچند می‌توان با دست‌کاری در توابع سی‌شارپ، این قوانین را عوض کرد.اکنون زمان آن رسیده است که توسط یک پرس‌وجو، همه‌ی توابعی که در سی‌شارپ برای این نوع داده نوشتیم، بیازماییم. پرس‌وجوی زیر را اجرا کنید:
Select TestDate.ToString() as JalaliDateTime,
TestDate.GetDate() as JalaliDate, TestDate.GetTime() as JalaliTime,
TestDate.ToGregorianTime() as GregorianTime,
TestDate.JalaliDateAdd('Day',1) JalaliTomorrow,
TestDate.Month as JalaliMonth from TestTable
البته درباره‌ی ستون پنجم و ششم شما می‌توانید روی همه‌ی اجزای تاریخ افزایش و کاهش داشته باشید و همچنین می‌توانید با تابع مربوطه هر کدام از اجزای زمان را جداگانه به دست بیاورید که در این مثال عدد ماه نشان داده شده است.
نیازی به گفتن نیست که می‌توانید به سادگی از توابع مربوط به DateTime در SQL Server بهره ببرید. برای مثال برای به دست آوردن فاصله‌ی میان دو روز از پرس‌وجوی زیر استفاده کنید:
نیازی به گفتن نیست که می‌توانید به سادگی از توابع مربوط به DateTime در SQL Server بهره ببرید. برای مثال برای به دست آوردن فاصله‌ی میان دو روز از پرس‌وجوی زیر استفاده کنید:
Declare @a JalaliDate = '1392/02/07 00:00:00'
Declare @b JalaliDate = '1392/02/05 00:00:00'
SELECT DATEDIFF("DAY",@b.ToGregorianTime(),@a.ToGregorianTime()) AS DiffDateشاد و پیروز باشید.
