پس از آشنایی با نوشتن یک سری کوئریهای ساده در EF Core، در این قسمت به نحوهی گزارشگیری از اطلاعات چندین جدول مرتبط به هم توسط Joinها خواهیم پرداخت.
مثال 1: یافتن زمانهای شروع رزرو کردن امکانات مختلف، توسط یک کاربر مشخص.
چگونه میتوان زمانهای شروع رزروهای کاربری به نام «David Farrell» را یافت؟
همانطور که در دیاگرام فوق مشاهده میکنید، به ازای هر ID کاربری در جدول کاربران، به دنبال ردیفهایی در جدول Bookings هستیم که این ID در آنها درج شدهاست. اما ... در EF-Core برخلاف SQL نویسی معمولی، ما کاری به ذکر قسمت اتصالی ON [Bookings].[MemId] = [Members].[MemId] نداریم. همینقدر که در کوئری نوشته شده به یک سر دیگر رابطه و خاصیت راهبری (navigation property) دیگری اشاره شود، خود EF-Core جوینی را به صورت خودکار تشکیل خواهد داد و شرط یاد شده را نیز برقرار میکند.
در قسمت اول این سری، در حین طراحی موجودیت کاربر، برای تشکیل سر دیگر رابطهی one-to-many آن، به جدول Bookings، خاصیت Member را نیز که بیانگر کلید خارجی به جدول کاربران است، اضافه کردیم:
خاصیت عددی MemId، کلید خارجی است که در بانک اطلاعاتی رابطهای ثبت خواهد شد و خاصیت Member، خاصیت راهبری است که جوین نویسی به جدول کاربران را بدون ذکر صریح جوین میسر میکند:
در این کوئری همینقدر که در قسمت Where آن booking.Member ذکر شده، جوینی به جدول کاربران را به صورت خودکار تشکیل میدهد:
مثال 2: یافتن زمانهای شروع به رزرو شدن یک امکان خاص در مجموعه.
لیست زمانهای شروع به رزرو شدن زمین(های) تنیس را برای روز 2012-09-21 تولید کنید. خروجی آن باید به همراه ستونهای StartTime, FacilityName باشد.
طراحی موجودیت Booking، به همراه یک کلید خارجی به Facility نیز هست:
خاصیت عددی FacId، کلید خارجی Facility است که در بانک اطلاعاتی رابطهای ثبت خواهد شد و خاصیت Facility، خاصیت راهبری است که جوین نویسی به جدول Facilities را بدون ذکر صریح جوین میسر میکند:
- زمینهای تنیس این مجموعه، دارای دو Id مساوی 0 و 1 هستند که در اینجا به صورت صریحی مشخص شدهاند تا مانند مثال 6 قسمت قبل عمل شود. روش دیگر یافتن آنها میتواند مانند مثال 5 قسمت قبل باشد که به صورت «Name.Contains("Tennis")» نوشته شد.
- در قسمت Where این کوئری چون booking.Facility ذکر شده، سبب ایجاد جوین خودکاری به جدول Facilities خواهد شد.
- علت استفادهی از دو تاریخ در اینجا برای یافتن اطلاعات تنها یک روز، ثبت زمان، به همراه تاریخ رزرو است. ستون تاریخ شروع، به صورت «2012-09-21 18:00:00.0000000» مقدار دهی شدهاست و نه به صورت «2012-09-21». البته در EF-Core راه دیگری هم برای حل این مساله وجود دارد. هر خاصیت از نوع DateTime، به همراه خاصیت Date نیز هست. برای مثال اگر بجای booking.StartTime نوشته شود booking.StartTime.Date (به خاصیت Date اضافه شده دقت کنید)، کد SQL حاصل، به همراه «CONVERT(date, [b].[StartTime])» خواهد بود که سبب حذف خودکار قسمت زمان این ستون میشود.
مثال 3: تولید لیست کاربرانی که کاربر دیگری را توصیه کردهاند.
چگونه میتوان لیست کاربرانی را یافت که کاربر دیگری را توصیه کردهاند؟ این لیست نباید به همراه ردیفهای تکراری باشد و همچنین باید بر اساس surname, firstname مرتب شود.
در اینجا به مفهوم جوین کردن یک جدول با خودش رسیدهایم. جدول کاربران، یک جدول خود ارجاع دهندهاست:
که در اینجا RecommendedBy، یک کلید خارجی نال پذیر است که به Id همین جدول اشاره میکند. دو خاصیت دیگر تعریف شده، مکمل این خاصیت عددی، جهت سهولت کوئری نویسیهای EF-Core هستند. برای مثال اگر در کوئری Recommender != null ذکر شود، سبب تشکیل جوینی به همین جدول شده و لیست کاربرانی را ارائه میدهد که کاربر دیگری را توصیه کردهاند:
وجود Distinct سبب بازگشت ردیفهایی غیرتکراری میشود (چون دو خاصیت نام و نام خانوادگی انتخاب شدهاند، ردیف غیرتکراری، ردیفی خواهد بود که هر دوی این ستونها در آن وجود نداشته باشد) و روش مرتب سازی بر اساس دو خاصیت را نیز مشاهده میکنید. در اینجا نباید دوبار OrderBy را پشت سر هم ذکر کرد. بار اول OrderBy است و بار دوم ThenBy تعریف میشود:
مثال 4: تولید لیست کاربران به همراه توصیه کنندهی آنها.
چگونه میتوان لیست کاربران را به همراه توصیه کنندهی آنها تولید کرد؟ این لیست باید بر اساس surname, firstname مرتب شود.
در اینجا نیز میتوان با ذکر member.Recommender سبب تولید یک جوین خودکار شد. همچنین همانطور که در مثال 7 قسمت قبل نیز بررسی کردیم، میتوان بر روی خواص ذکر شدهی در Select، محاسباتی را نیز انجام داد. برای مثال در اینجا بجای درج مقدار null برای کاربرانی که کاربر دیگری را توصیه نکردهاند، ترجیح دادهایم که یک رشتهی خالی بازگشت داده شود که به صورت «COALESCE ([m0].[FirstName], N'')» ترجمه میشود:
همانطور که ملاحظه میکنید، نوع جوین خودکار تشکیل شده، Left join است و دیگر مانند جوینهای مثالهای ابتدای بحث، inner join نیست. در inner join، جدول سمت راست و چپ بر اساس شرط ON آنها با هم مقایسه شده و ردیفهای کاملا تطابق یافتهای بازگشت داده میشوند. کار Left join نیز مشابه است، با این تفاوت که در اینجا ممکن است برای جدول سمت چپ، هیچ ردیف تطابق یافتهای در جدول سمت راست وجود نداشته باشد (نوع آن بر اساس نال پذیری خاصیت RecommendedBy تشخیص داده شدهاست)؛ برای مثال یک کاربر ممکن است توسط کاربر دیگری توصیه نشده باشد (و RecommendedBy او نال باشد)، اما علاقمندیم که نام او در لیست نهایی حضور داشته باشد و حذف نشود.
یک نکته: در SQL Server تفاوتی بین left join و left outer join وجود ندارد و ذکر واژهی کلیدی outer کاملا اختیاری است. جدول موارد مشابهی در SQL Server که به یک معنا هستند، صورت زیر است:
مثال 5: تولید لیست کاربرانی که از زمین تنیس استفاده کردهاند.
چگونه میتوان لیست کاربرانی را تولید کرد که از زمین(های) تنیس استفاده کردهاند؟ خروجی این گزارش باید به همراه یک ستون جمع نام و نام خانوادگی و ستون نام زمین باشد. این گزارش نباید دارای ردیفهای تکراری باشد و همچنین باید بر اساس حاصل جمع نام و نام خانوادگی، مرتب شده باشد.
جدول Bookings به همراه دو کلید خارجی به جداول Facilities و Members است:
بنابراین برای تولید گزارشی که اطلاعات هر دوی اینها را به همراه دارد (اطلاعات کاربر و اطلاعات امکاناتی که استفاده کرده)، نیاز است دو جوین به دو جدول یاد شده نوشته شود. برای اینکار نیاز است در کوئری خود به booking.Member و booking.Facility برسیم. به همین جهت از جدول کاربران که دارای خاصیت از نوع ICollection اشاره کنندهی به Bookings کاربران است شروع میکنیم:
سپس بر روی این خاصیت مجموعهای، اینبار یک SelectMany را فراخوانی میکنیم تا خروجی آن، تک تک رکوردهای booking متناظر باشد. اکنون که به هر رکورد booking کاربران دسترسی یافتهایم، میتوانیم از طریق خواص راهبری booking.Member و booking.Facility هر ردیف، اطلاعات نهایی گزارش را تولید کنیم:
ID زمینهای تنیس مشخص هستند که توسط tennisCourts.Contains به FacIdهای موجود اعمال شدهاند. همچنین در قسمت Select نیز خاصیت Member آن به جمع دو خاصیت از booking.Member اشاره میکند و چون نتیجهی حاصل یک ستون از پیش تعریف شده نیست، نیاز است تا برای آن نام صریحی انتخاب شود.
پس از آن برای حذف ردیفهای تکراری حاصل از گزارش، از متد Distinct استفاده شده و OrderBy نیز بر اساس خاصیت جدید Member، قابل تعریف است:
مثال 6: تولید لیست رزروهای گران قیمت
لیست رزروهای روز 2012-09-14 را تولید کنید که هزینهی آنها بیشتر از 30 دلار باشد. باید بخاطر داشت که هزینههای کاربران با مهمانها متفاوت است و هزینهها بر اساس Slotهای نیم ساعته محاسبه میشوند و ID کاربر مهمان همیشه صفر است. خروجی این گزارش باید به همراه نام کامل کاربر، نام امکانات مورد استفاده و هزینهی نهایی باشد. همچنین باید بر اساس هزینههای نهایی به صورت نزولی مرتب شود.
در اینجا نیز چون نیاز است خروجی نهایی به همراه نام کاربر و نام امکانات مورد استفاده باشد، همانند مثال قبلی، به حداقل دو جوین نیاز است. به همین جهت از جدول Members به همراه SelectMany بر روی تک تک Bookings آن شروع میکنیم.
سپس بر اساس صفر بودن یا نبودن booking.MemId (کاربر مهمان بودن یا خیر)، شرط هزینهی بیشتر از 30 دلار اعمال شدهاست.
در آخر Select گزارش مورد نیاز، به همراه جمع نام و نام خانوادگی، نام امکانات استفاده شده و خاصیت محاسباتی Cost است که بر اساس مهمان بودن یا نبودن کاربر، متفاوت است.
متد Distinct ردیفهای تکراری حاصل از این گزارش را حذف میکند (محل درج آن مهم است) و متد OrderByDescending، مرتب سازی نزولی بر اساس خاصیت محاسباتی Cost را انجام میدهد.
مثال 7: تولید لیست کاربران به همراه توصیه کنندهی آنها، بدون استفاده از جوین.
در اینجا میخواهیم همان مثال 4 را بدون استفاده از جوین بررسی کنیم. بدون استفاده از جوین در اینجا به معنای استفاده از sub-query است (نوشتن یک کوئری داخل کوئری اصلی).
این کوئری به صورت متداولی بر روی جدول Members اعمال شدهاست، با این تفاوت که در حین Select نهایی آن، یکبار دیگر کوئری جدید شروع شدهی با context.Members را مشاهده میکنید که سبب تولید یک sub-query، زمانیکه ToList نهایی فراخوانی میشود، خواهد شد. این sub-query در حقیقت یک outer join را با ذکر recommender.MemId == member.RecommendedBy (بیان صریح روش اتصال IDهای دو سر رابطه) شبیه سازی میکند.
مثال 8: تولید لیست رزروهای گران قیمت با استفاده از یک sub-query.
هدف از این مثال، ارائهی روش حل دیگری برای مثال 6، به نحو تمیزتری است. در مثال 6، هزینهی رزرو را دوبار، یکبار در متد Where و یکبار در متد Select محاسبه کردیم. اینبار میخواهیم با استفاده از sub-queryها این محاسبه را یکبار انجام دهیم.
اینبار یک Select نوشته شده که در آن Cost، در ابتدا محاسبه شده و سپس Where دومی ذکر شده که از این Cost استفاده میکند.
هرچند کوئری SQL نهایی تولید شدهی توسط EF-Core آن، تفاوتی چندانی با نگارش قبلی ندارد:
کدهای کامل این قسمت را در اینجا میتوانید مشاهده کنید.
مثال 1: یافتن زمانهای شروع رزرو کردن امکانات مختلف، توسط یک کاربر مشخص.
چگونه میتوان زمانهای شروع رزروهای کاربری به نام «David Farrell» را یافت؟
همانطور که در دیاگرام فوق مشاهده میکنید، به ازای هر ID کاربری در جدول کاربران، به دنبال ردیفهایی در جدول Bookings هستیم که این ID در آنها درج شدهاست. اما ... در EF-Core برخلاف SQL نویسی معمولی، ما کاری به ذکر قسمت اتصالی ON [Bookings].[MemId] = [Members].[MemId] نداریم. همینقدر که در کوئری نوشته شده به یک سر دیگر رابطه و خاصیت راهبری (navigation property) دیگری اشاره شود، خود EF-Core جوینی را به صورت خودکار تشکیل خواهد داد و شرط یاد شده را نیز برقرار میکند.
در قسمت اول این سری، در حین طراحی موجودیت کاربر، برای تشکیل سر دیگر رابطهی one-to-many آن، به جدول Bookings، خاصیت Member را نیز که بیانگر کلید خارجی به جدول کاربران است، اضافه کردیم:
namespace EFCorePgExercises.Entities { public class Booking { // ... public int MemId { set; get; } public virtual Member Member { set; get; } // ... } }
var startTimes = context.Bookings .Where(booking => booking.Member.FirstName == "David" && booking.Member.Surname == "Farrell") .Select(booking => new { booking.StartTime }) .ToList();
مثال 2: یافتن زمانهای شروع به رزرو شدن یک امکان خاص در مجموعه.
لیست زمانهای شروع به رزرو شدن زمین(های) تنیس را برای روز 2012-09-21 تولید کنید. خروجی آن باید به همراه ستونهای StartTime, FacilityName باشد.
طراحی موجودیت Booking، به همراه یک کلید خارجی به Facility نیز هست:
namespace EFCorePgExercises.Entities { public class Booking { // ... public int FacId { set; get; } public virtual Facility Facility { set; get; } // ... } }
int[] tennisCourts = { 0, 1 }; var date1 = new DateTime(2012, 09, 21); var date2 = new DateTime(2012, 09, 22); var startTimes = context.Bookings .Where(booking => tennisCourts.Contains(booking.Facility.FacId) && booking.StartTime >= date1 && booking.StartTime < date2) .Select(booking => new { booking.StartTime, booking.Facility.Name }) .ToList();
- در قسمت Where این کوئری چون booking.Facility ذکر شده، سبب ایجاد جوین خودکاری به جدول Facilities خواهد شد.
- علت استفادهی از دو تاریخ در اینجا برای یافتن اطلاعات تنها یک روز، ثبت زمان، به همراه تاریخ رزرو است. ستون تاریخ شروع، به صورت «2012-09-21 18:00:00.0000000» مقدار دهی شدهاست و نه به صورت «2012-09-21». البته در EF-Core راه دیگری هم برای حل این مساله وجود دارد. هر خاصیت از نوع DateTime، به همراه خاصیت Date نیز هست. برای مثال اگر بجای booking.StartTime نوشته شود booking.StartTime.Date (به خاصیت Date اضافه شده دقت کنید)، کد SQL حاصل، به همراه «CONVERT(date, [b].[StartTime])» خواهد بود که سبب حذف خودکار قسمت زمان این ستون میشود.
مثال 3: تولید لیست کاربرانی که کاربر دیگری را توصیه کردهاند.
چگونه میتوان لیست کاربرانی را یافت که کاربر دیگری را توصیه کردهاند؟ این لیست نباید به همراه ردیفهای تکراری باشد و همچنین باید بر اساس surname, firstname مرتب شود.
در اینجا به مفهوم جوین کردن یک جدول با خودش رسیدهایم. جدول کاربران، یک جدول خود ارجاع دهندهاست:
namespace EFCorePgExercises.Entities { public class Member { // ... public virtual ICollection<Member> Children { get; set; } public virtual Member Recommender { set; get; } public int? RecommendedBy { set; get; } // ... } }
var members = context.Members .Where(member => member.Recommender != null) .Select(member => new { member.Recommender.FirstName, member.Recommender.Surname }) .Distinct() .OrderBy(member => member.Surname).ThenBy(member => member.FirstName) .ToList();
مثال 4: تولید لیست کاربران به همراه توصیه کنندهی آنها.
چگونه میتوان لیست کاربران را به همراه توصیه کنندهی آنها تولید کرد؟ این لیست باید بر اساس surname, firstname مرتب شود.
var members = context.Members .Select(member => new { memFName = member.FirstName, memSName = member.Surname, recFName = member.Recommender.FirstName ?? "", recSName = member.Recommender.Surname ?? "" }) .OrderBy(member => member.memSName).ThenBy(member => member.memFName) .ToList();
همانطور که ملاحظه میکنید، نوع جوین خودکار تشکیل شده، Left join است و دیگر مانند جوینهای مثالهای ابتدای بحث، inner join نیست. در inner join، جدول سمت راست و چپ بر اساس شرط ON آنها با هم مقایسه شده و ردیفهای کاملا تطابق یافتهای بازگشت داده میشوند. کار Left join نیز مشابه است، با این تفاوت که در اینجا ممکن است برای جدول سمت چپ، هیچ ردیف تطابق یافتهای در جدول سمت راست وجود نداشته باشد (نوع آن بر اساس نال پذیری خاصیت RecommendedBy تشخیص داده شدهاست)؛ برای مثال یک کاربر ممکن است توسط کاربر دیگری توصیه نشده باشد (و RecommendedBy او نال باشد)، اما علاقمندیم که نام او در لیست نهایی حضور داشته باشد و حذف نشود.
یک نکته: در SQL Server تفاوتی بین left join و left outer join وجود ندارد و ذکر واژهی کلیدی outer کاملا اختیاری است. جدول موارد مشابهی در SQL Server که به یک معنا هستند، صورت زیر است:
A LEFT JOIN B A LEFT OUTER JOIN B A RIGHT JOIN B A RIGHT OUTER JOIN B A FULL JOIN B A FULL OUTER JOIN B A INNER JOIN B A JOIN B
مثال 5: تولید لیست کاربرانی که از زمین تنیس استفاده کردهاند.
چگونه میتوان لیست کاربرانی را تولید کرد که از زمین(های) تنیس استفاده کردهاند؟ خروجی این گزارش باید به همراه یک ستون جمع نام و نام خانوادگی و ستون نام زمین باشد. این گزارش نباید دارای ردیفهای تکراری باشد و همچنین باید بر اساس حاصل جمع نام و نام خانوادگی، مرتب شده باشد.
جدول Bookings به همراه دو کلید خارجی به جداول Facilities و Members است:
namespace EFCorePgExercises.Entities { public class Booking { // ... public int FacId { set; get; } public virtual Facility Facility { set; get; } public int MemId { set; get; } public virtual Member Member { set; get; } // ... } }
namespace EFCorePgExercises.Entities { public class Member { // ... public virtual ICollection<Booking> Bookings { set; get; } } }
int[] tennisCourts = { 0, 1 }; var members = context.Members .SelectMany(x => x.Bookings) .Where(booking => tennisCourts.Contains(booking.Facility.FacId)) .Select(booking => new { Member = booking.Member.FirstName + " " + booking.Member.Surname, Facility = booking.Facility.Name }) .Distinct() .OrderBy(x => x.Member) .ToList();
پس از آن برای حذف ردیفهای تکراری حاصل از گزارش، از متد Distinct استفاده شده و OrderBy نیز بر اساس خاصیت جدید Member، قابل تعریف است:
مثال 6: تولید لیست رزروهای گران قیمت
لیست رزروهای روز 2012-09-14 را تولید کنید که هزینهی آنها بیشتر از 30 دلار باشد. باید بخاطر داشت که هزینههای کاربران با مهمانها متفاوت است و هزینهها بر اساس Slotهای نیم ساعته محاسبه میشوند و ID کاربر مهمان همیشه صفر است. خروجی این گزارش باید به همراه نام کامل کاربر، نام امکانات مورد استفاده و هزینهی نهایی باشد. همچنین باید بر اساس هزینههای نهایی به صورت نزولی مرتب شود.
var date1 = new DateTime(2012, 09, 14); var date2 = new DateTime(2012, 09, 15); var items = context.Members .SelectMany(x => x.Bookings) .Where(booking => booking.StartTime >= date1 && booking.StartTime < date2 && ( (((booking.Slots * booking.Facility.GuestCost) > 30) && (booking.MemId == 0)) || (((booking.Slots * booking.Facility.MemberCost) > 30) && (booking.MemId != 0)) )) .Select(booking => new { Member = booking.Member.FirstName + " " + booking.Member.Surname, Facility = booking.Facility.Name, Cost = booking.MemId == 0 ? booking.Slots * booking.Facility.GuestCost : booking.Slots * booking.Facility.MemberCost }) .Distinct() .OrderByDescending(x => x.Cost) .ToList();
سپس بر اساس صفر بودن یا نبودن booking.MemId (کاربر مهمان بودن یا خیر)، شرط هزینهی بیشتر از 30 دلار اعمال شدهاست.
در آخر Select گزارش مورد نیاز، به همراه جمع نام و نام خانوادگی، نام امکانات استفاده شده و خاصیت محاسباتی Cost است که بر اساس مهمان بودن یا نبودن کاربر، متفاوت است.
متد Distinct ردیفهای تکراری حاصل از این گزارش را حذف میکند (محل درج آن مهم است) و متد OrderByDescending، مرتب سازی نزولی بر اساس خاصیت محاسباتی Cost را انجام میدهد.
مثال 7: تولید لیست کاربران به همراه توصیه کنندهی آنها، بدون استفاده از جوین.
در اینجا میخواهیم همان مثال 4 را بدون استفاده از جوین بررسی کنیم. بدون استفاده از جوین در اینجا به معنای استفاده از sub-query است (نوشتن یک کوئری داخل کوئری اصلی).
var members = context.Members .Select(member => new { Member = member.FirstName + " " + member.Surname, Recommender = context.Members .Where(recommender => recommender.MemId == member.RecommendedBy) .Select(recommender => recommender.FirstName + " " + recommender.Surname) .FirstOrDefault() ?? "" }) .Distinct() .OrderBy(member => member.Member) .ToList();
مثال 8: تولید لیست رزروهای گران قیمت با استفاده از یک sub-query.
هدف از این مثال، ارائهی روش حل دیگری برای مثال 6، به نحو تمیزتری است. در مثال 6، هزینهی رزرو را دوبار، یکبار در متد Where و یکبار در متد Select محاسبه کردیم. اینبار میخواهیم با استفاده از sub-queryها این محاسبه را یکبار انجام دهیم.
var date1 = new DateTime(2012, 09, 14); var date2 = new DateTime(2012, 09, 15); var items = context.Members .SelectMany(x => x.Bookings) .Where(booking => booking.StartTime >= date1 && booking.StartTime < date2) .Select(booking => new { Member = booking.Member.FirstName + " " + booking.Member.Surname, Facility = booking.Facility.Name, Cost = booking.MemId == 0 ? booking.Slots * booking.Facility.GuestCost : booking.Slots * booking.Facility.MemberCost }) .Where(x => x.Cost > 30) .Distinct() .OrderByDescending(x => x.Cost) .ToList();
هرچند کوئری SQL نهایی تولید شدهی توسط EF-Core آن، تفاوتی چندانی با نگارش قبلی ندارد:
کدهای کامل این قسمت را در اینجا میتوانید مشاهده کنید.
پیش از هرچیز به شما پیشنهاد میکنم؛ بار دیگر کد سیشارپ درس نخست را در پروژهی خود کپی کنید و سپس Publish را بزنید. پس از ارسال آن مطلب، تغییراتی در جهت بهینهسازی کد دادم که به نظرم بهتر است شما نیز در پروژهی خود به کار برید.
چرا از این نوع داده استفاده کنیم؟
نخستین پرسشی که ممکن است برای شما پیش بیاید این است که چرا بهتر است از این نوع داده استفاده کنیم. برای پاسخ به این پرسش باید راهکارهای گذشته را بررسی کنیم. معمولاً طراحان پایگاه دادهها برای استفاده از تاریخ خورشیدی، زمان را به صورت میلادی ثبت میکنند؛ سپس با یک scalar-valued function زمان درج شده را به خورشیدی تبدیل میکنند. در این صورت میتوان با یک تابع کوچک دیگر بخش مربوط به ساعت را نیز از همان ستون به دست آورد. در این صورت میتوانیم از کلیهی متدهای مربوط به DateTime در SQL از جمله افزایش و کاهش و تفاضل دو تاریخ بهره برد. برخی دیگر از طراحان، ستونی از نوع (char(10 در نظر میگیرند و تاریخ خورشیدی را به صورت دهکاراکتری در آن ذخیره میکنند. این روش هرچند نیاز به تبدیل به خورشیدی را ندارد ولی کلیهی مزایایی که در استفاده از DateTime به آنها دسترسی داریم از دست میدهیم. افزون بر این جهت نگهداری زمان باید یک فیلد دیگر از نوع کاراکتری و یا در نگارشهای نوینتر از نوع time تعریف کنیم. برخی دیگر از هر دو را در کنار هم استفاده میکنند و در واقع جهت سرعت بالاتر نمایش و بررسی دادهها از طریق محیط SQL Server از فیلد کاراکتری تاریخ خورشیدی و برای مقایسه و بدست آوردن ساعت از فیلد نوع DateTime استفاده میکنند.
از نظر فضای اشغالشده نوع DataTime، هشت بایت، smalldatetime (در صورت استفاده) 4 بایت و فیلد 10 کاراکتری تاریخ 10 بایت فضا اشغال میکند در صورتی که نوع JalaliDate با درنظر گرفتن همهی مزایای انواع دادهی استفادهشده برای تاریخ، فقط 8 بایت فضا اشغال میکند. با استفاده از این نوع به راحتی دادهی تاریخ را بر اساس تقویم ایرانی اعتبارسنجی میکنید و بخشهای مختلف زمان از سال تا ثانیه را با یک متد به دست میآورید. میتوانید به راحتی به تاریخ خود زمانی را بیفزایید یا بکاهید و در گزارشها بدون نگرانی از تبدیل درست استفاده کنید. چون کدباز است میتوانید با کمی حوصله امکانات دیگر مد نظر خود را به آن بیفزایید و از آن در SQL بهره ببرید.
چگونه این نوع داده را حذف کنم!؟
شما میتوانید به سادگی نوع دادهی ایجادشده توسط CLR را در مسیر زیر بیابید و اقدام به حذف آن نمایید:
همانطور که مشاهده میشود؛ حتی نوع دادهی سیستمی hierarchyid که جهت ساختار سلسلهمراتبی مانند چارت سازمانی یا درخت تجهیزات استفاده میشود؛ نیز یک نوع دادهی CLR است.
آیا راه دیگری نیز برای افزودن این نوع داده به SQL به جز Publish کردن وجود دارد؟
مانند بسیاری دیگر از گونههای پروژه، در اینجا نیز شما یک فایل DLL خواهید داشت. این فایل برپایهی تنظیماتی که شما در قسمت Properties پروژهی خود انجام میدهید ساخته میشود. پس از تغییر مسیر فایل DLL در دستور زیر توسط یک New Query از Database خود، آن را اجرا کنید:
همچنین در صورت ویرایشهای دوباره پروژه از دستور زیر استفاده کنید:
با استفاده از دستورهای زیر میتوانید از چگونگی درج فایلهای افزوده شده آگاه شوید:
تا اینجا SQL Server، دیالال مربوط به پروژه را شناخته است. برای تعریف نوع داده از دستور زیر بهره ببرید:
این کار همانند استفاده از گزینهی Publish در Visual Studio است.
همچنین چنانچه در SQL Server 2012 از منوی راستکلیک پایگاه دادهها روی گزینه Tasks و سپس Generate Scripts را انتخاب کنیم، از مشاهدهی سند ساخته شده، درخواهیم یافت که حتی دستورهای مربوط به ساخت اسمبلی CLR با تبدیل فایل به کد در Scripts وجود دارد و با اجرای آن در سروری دیگر، انتقال مییابد.
دنباله دارد ...
چرا از این نوع داده استفاده کنیم؟
نخستین پرسشی که ممکن است برای شما پیش بیاید این است که چرا بهتر است از این نوع داده استفاده کنیم. برای پاسخ به این پرسش باید راهکارهای گذشته را بررسی کنیم. معمولاً طراحان پایگاه دادهها برای استفاده از تاریخ خورشیدی، زمان را به صورت میلادی ثبت میکنند؛ سپس با یک scalar-valued function زمان درج شده را به خورشیدی تبدیل میکنند. در این صورت میتوان با یک تابع کوچک دیگر بخش مربوط به ساعت را نیز از همان ستون به دست آورد. در این صورت میتوانیم از کلیهی متدهای مربوط به DateTime در SQL از جمله افزایش و کاهش و تفاضل دو تاریخ بهره برد. برخی دیگر از طراحان، ستونی از نوع (char(10 در نظر میگیرند و تاریخ خورشیدی را به صورت دهکاراکتری در آن ذخیره میکنند. این روش هرچند نیاز به تبدیل به خورشیدی را ندارد ولی کلیهی مزایایی که در استفاده از DateTime به آنها دسترسی داریم از دست میدهیم. افزون بر این جهت نگهداری زمان باید یک فیلد دیگر از نوع کاراکتری و یا در نگارشهای نوینتر از نوع time تعریف کنیم. برخی دیگر از هر دو را در کنار هم استفاده میکنند و در واقع جهت سرعت بالاتر نمایش و بررسی دادهها از طریق محیط SQL Server از فیلد کاراکتری تاریخ خورشیدی و برای مقایسه و بدست آوردن ساعت از فیلد نوع DateTime استفاده میکنند.
از نظر فضای اشغالشده نوع DataTime، هشت بایت، smalldatetime (در صورت استفاده) 4 بایت و فیلد 10 کاراکتری تاریخ 10 بایت فضا اشغال میکند در صورتی که نوع JalaliDate با درنظر گرفتن همهی مزایای انواع دادهی استفادهشده برای تاریخ، فقط 8 بایت فضا اشغال میکند. با استفاده از این نوع به راحتی دادهی تاریخ را بر اساس تقویم ایرانی اعتبارسنجی میکنید و بخشهای مختلف زمان از سال تا ثانیه را با یک متد به دست میآورید. میتوانید به راحتی به تاریخ خود زمانی را بیفزایید یا بکاهید و در گزارشها بدون نگرانی از تبدیل درست استفاده کنید. چون کدباز است میتوانید با کمی حوصله امکانات دیگر مد نظر خود را به آن بیفزایید و از آن در SQL بهره ببرید.
چگونه این نوع داده را حذف کنم!؟
شما میتوانید به سادگی نوع دادهی ایجادشده توسط CLR را در مسیر زیر بیابید و اقدام به حذف آن نمایید:
همانطور که مشاهده میشود؛ حتی نوع دادهی سیستمی hierarchyid که جهت ساختار سلسلهمراتبی مانند چارت سازمانی یا درخت تجهیزات استفاده میشود؛ نیز یک نوع دادهی CLR است.
آیا راه دیگری نیز برای افزودن این نوع داده به SQL به جز Publish کردن وجود دارد؟
مانند بسیاری دیگر از گونههای پروژه، در اینجا نیز شما یک فایل DLL خواهید داشت. این فایل برپایهی تنظیماتی که شما در قسمت Properties پروژهی خود انجام میدهید ساخته میشود. پس از تغییر مسیر فایل DLL در دستور زیر توسط یک New Query از Database خود، آن را اجرا کنید:
CREATE ASSEMBLY JalaliDate FROM 'F:\prgJalaliDate.dll' WITH PERMISSION_SET = SAFE;
ALTER ASSEMBLY JalaliDate FROM 'F:\prgJalaliDate.dll'
select * from sys.assemblies select * from sys.assembly_files
CREATE TYPE dbo.JalaliDate EXTERNAL NAME JalaliDate.[JalaliDate];
همچنین چنانچه در SQL Server 2012 از منوی راستکلیک پایگاه دادهها روی گزینه Tasks و سپس Generate Scripts را انتخاب کنیم، از مشاهدهی سند ساخته شده، درخواهیم یافت که حتی دستورهای مربوط به ساخت اسمبلی CLR با تبدیل فایل به کد در Scripts وجود دارد و با اجرای آن در سروری دیگر، انتقال مییابد.
GO /****** Object: SqlAssembly [prgJalaliDate] Script Date: 2013/04/30 08:27:00 ب.ظ ******/ CREATE ASSEMBLY [prgJalaliDate] FROM 0x4D5A90000300000004000000FFFF0000B8000000000000 ..... بقیهی کدها حذف شده WITH PERMISSION_SET = SAFE GO ALTER ASSEMBLY [prgJalaliDate] ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A44530..... بقیهی کدها حذف شده AS N'prgJalaliDate.pdb' GO /****** Object: UserDefinedType [dbo].[JalaliDate] Script Date: 2013/04/30 08:27:00 ب.ظ ******/ CREATE TYPE [dbo].[JalaliDate] EXTERNAL NAME [prgJalaliDate].[JalaliDate] GO
دنباله دارد ...
datatable js، کتابخانهای جهت ساخت جداول است و نسبت به رقیب اصلی خودش یعنی kendo telerik، از سادگی بیشتری برخوردار هست و امکانات خوبی هم دارد.
اگر برای جداول صفحات خود، از کتابخانهی جیکوئری datatable استفاده میکنید، بعد از مدتی که تعداد رکوردها زیاد میشوند، شاهد کند شدن صفحه خود خواهید شد. برای رفع این مشکل نیاز به پیاده سازی pagination دارید که به صورت خیلی سادهای قابل پیاده سازی هست و شما تغییر کمی را در سمت سرور اعمال میکنید و سایر موارد توسط خود کتابخانه انجام میشود.
در ابتدا به بررسی کدها و تغییرات سمت فرانتاند و صفحهی cshtml میپردازیم:
1- تابع Ajax ای که وظیفهی دریافت اطلاعات را دارد، به کل پاک کنید. چون Ajax به صورت یک آبجکت، به درون خود دیتاتیبل منتقل خواهد شد.
2- در صفحه خود، کد زیر را قرار دهید (جهت جلوگیری از 400 bad request) که این کار فقط برای هندلرهای razor page و یا controller نیاز است و اگر از API استفاده میکنید، مسلما نیازی به این مدل تنظیمات نیست.
@Html.AntiForgeryToken()
//Post in Ajax services.AddAntiforgery(o => o.HeaderName = "XSRF-TOKEN");
function initDataTables() { table.destroy(); table = $("#tblJs").DataTable({ processing: true, serverSide: true, filter: true, ajax: { url: '@Url.Page("yourPage","yourHandler")', beforeSend: function (xhr) { xhr.setRequestHeader("XSRF-TOKEN", $('input:hidden[name="__RequestVerificationToken"]').val()); }, type: "POST", datatype: "json" }, language: { url: "/Persian.json" }, responsive: true, select: true, columns: scheme, select: true, }); }
processing: true, serverSide: true, filter: true, ajax: { url: '@Url.Page("yourPage","yourHandler ")', beforeSend: function (xhr) { xhr.setRequestHeader("XSRF-TOKEN", $('input:hidden[name="__RequestVerificationToken"]').val()); }, type: "POST", datatype: "json" },
حالا باید کدهای سمت سرور را بنویسیم. برای این منظور باید ابتدا مقادیری را که دیتاتیبل برای ما ارسال میکند، از ریکوئست دریافت کنیم.
6- کل دیتایی که دیتا تیبل برای ما میفرستد، به مدل زیر خلاصه میشود:
public class FiltersFromRequestDataTable { public string length { get; set; } public string start { get; set; } public string sortColumn { get; set; } public string sortColumnDirection { get; set; } public string sortColumnIndex { get; set; } public string draw { get; set; } public string searchValue { get; set; } public int pageSize { get; set; } public int skip { get; set; } }
* (من از razor page استفاده میکنم؛ ولی مسلما در controller هم به همین شکل و راحتتر خواهد بود)
7- سپس دادههای ارسال شدهی توسط دیتاتیبل، به سمت سرور را با استفاده از متد زیر دریافت میکنیم:
public static void GetDataFromRequest(this HttpRequest Request, out FiltersFromRequestDataTable filtersFromRequest) { //TODO: Make Strings Safe String filtersFromRequest = new(); filtersFromRequest.draw = Request.Form["draw"].FirstOrDefault(); filtersFromRequest.start = Request.Form["start"].FirstOrDefault(); filtersFromRequest.length = Request.Form["length"].FirstOrDefault(); filtersFromRequest.sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault(); filtersFromRequest.sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault(); filtersFromRequest.searchValue = Request.Form["search[value]"].FirstOrDefault(); filtersFromRequest.pageSize = filtersFromRequest.length != null ? Convert.ToInt32(filtersFromRequest.length) : 0; filtersFromRequest.skip = filtersFromRequest.start != null ? Convert.ToInt32(filtersFromRequest.start) : 0; filtersFromRequest.sortColumnIndex = Request.Form["order[0][column]"].FirstOrDefault(); filtersFromRequest.searchValue = filtersFromRequest.searchValue?.ToLower(); }
Request.GetDataFromRequest(out FiltersFromRequestDataTable filtersFromRequest);
9- با استفاده از متد زیر، مقادیر مورد نیاز دیتاتیبل را به آن ارسال میکنیم:
public static PaginationDataTableResult<T> ToDataTableJs<T>(this IEnumerable<T> source, FiltersFromRequestDataTable filtersFromRequest) { int recordsTotal = source.Count(); CofingPaging(ref filtersFromRequest, recordsTotal); var result = new PaginationDataTableResult<T>() { draw = filtersFromRequest.draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = source.OrderByIndex(filtersFromRequest).Skip(filtersFromRequest.skip).Take(filtersFromRequest.pageSize).ToList() }; return result; } private static void CofingPaging(ref FiltersFromRequestDataTable filtersFromRequest, int recordsTotal) { if (filtersFromRequest.pageSize == -1) { filtersFromRequest.pageSize = recordsTotal; filtersFromRequest.skip = 0; } }
private static IEnumerable<T> OrderByIndex<T>(this IEnumerable<T> source, FiltersFromRequestDataTable filtersFromRequest) { var props = typeof(T).GetProperties(); string propertyName = ""; for (int i = 0; i < props.Length; i++) { if (i.ToString() == filtersFromRequest.sortColumnIndex) propertyName = props[i].Name; } System.Reflection.PropertyInfo propByName = typeof(T).GetProperty(propertyName); if (propByName is not null) { if (filtersFromRequest.sortColumnDirection == "desc") source = source.OrderByDescending(x => propByName.GetValue(x, null)); else source = source.OrderBy(x => propByName.GetValue(x, null)); } return source; }
10- نحوه استفاده در هندلر یا اکشن:
var result = query.ToDataTableJs(filtersFromRequest); return new JsonResult(result);
- برای سرچ هم در columnها هم میتوانید به شکل زیر عمل کنید.
ابتدا دو متد زیر را به یک کلاس static اضافه کنید:
public static IEnumerable<TSource> WhereSearchValue<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate) { return source.Where(predicate); } public static bool ContainsSearchValue(this string source, string toCheck) { return source != null && toCheck != null && source.IndexOf(toCheck, StringComparison.OrdinalIgnoreCase) >= 0; }
if (!string.IsNullOrEmpty(filtersFromRequest.searchValue)) query = query.WhereSearchValue(x => x.title.ContainsSearchValue(filtersFromRequest.searchValue) || x.id.ToString().ContainsSearchValue(filtersFromRequest.searchValue)).AsQueryable();
کد کامل هندلر یا action (که ترکیب کدهای بالا هستش):
public JsonResult OnPostList() { Request.GetDataFromRequest(out FiltersFromRequestDataTable filtersFromRequest); var query = _Repo.GetQueryable().Select(x => new VmAdminList() { title = x.Title, } ); if (!string.IsNullOrEmpty(filtersFromRequest.searchValue)) query = query.WhereSearchValue(x => x.title.ContainsSearchValue(filtersFromRequest.searchValue) || x.id.ToString().ContainsSearchValue(filtersFromRequest.searchValue)).AsQueryable(); var result = query.ToDataTableJs(filtersFromRequest); return new JsonResult(result); }
چند نکته:
1- ممکناست که بخواهید یکسری فیلتر را بجز مقادیر پیش فرض به سمت سرور ارسال کنید. برای اینکار کد زیر را به قسمت Ajax فرانتاند اضافه کنید:
data: function (d) { d.parentId = parentID; d.StartDateTime= StartDateTime; },
if (!int.TryParse(Request.Form["parentId"].FirstOrDefault(), out int parentId)) throw new NullReferenceException();
dataSrc: function (json) { $("#count").val(json.data.length); var sum = 0; json.data.forEach(function (item) { if (!isNullOrEmpty(item.credit)) sum += parseInt(item.credit); }) $("#sum").val(separate(sum)); return json.data; }
تا به اینجا کار کاملا تمام شده؛ ولی من برای داینامیک کردن schema و columnها هم کلاسی را نوشتهام که فکر میکنم کار را راحتتر کند. چون شما برای تعداد ستونها باید یک آبجکت را به شکل زیر تعریف کنید:
columns: [ { data: 'name' }, { data: 'position' }, { data: 'salary' }, { data: 'office' } ]
public class JsDataTblGeneretaor<T> { public readonly DataTableSchemaResult DataTableSchemaResult = new(); public JsDataTblGeneretaor<T> CreateTableSchema() { var props = typeof(T).GetProperties(); foreach (var prop in props) { DataTableSchemaResult.SchemaResult.Add(new() { data = prop.Name, sortable = (prop.PropertyType == typeof(int)) || (prop.PropertyType == typeof(bool)) || (prop.PropertyType == typeof(DateTime)), width = "", visible = (prop.PropertyType != typeof(DateTime)) }); } return this; } public JsDataTblGeneretaor<T> CreateTableColumns() { var props = typeof(T).GetProperties(); CustomAttributeData displayAttribute; foreach (var prop in props) { string displayName = prop.Name; displayAttribute = prop.CustomAttributes.FirstOrDefault(x => x.AttributeType.Name == "DisplayAttribute"); if (displayAttribute != null) { displayName = displayAttribute.NamedArguments.FirstOrDefault().TypedValue.Value.ToString(); } DataTableSchemaResult.Colums.Add(displayName); } return this; } public JsDataTblGeneretaor<T> AddCustomSchema(string data, bool? sortable = null, bool? visible = null, string width = null, string className = null) { if (DataTableSchemaResult.SchemaResult == null || !DataTableSchemaResult.SchemaResult.Any()) return this; foreach (var item in DataTableSchemaResult.SchemaResult.Where(x => x.data == data)) { if (sortable != null) item.sortable = sortable.Value; if (visible != null) item.visible = visible.Value; if (width != null) item.width = width; if (className != null) item.className = className; } return this; } public JsDataTblGeneretaor<T> SerializeSchema() { if (DataTableSchemaResult.SchemaResult == null || !DataTableSchemaResult.SchemaResult.Any()) return this; DataTableSchemaResult.SerializedSchemaResult = JsonSerializer.Serialize(DataTableSchemaResult.SchemaResult); return this; } } public class DataTableSchema { public string data { get; set; } public bool sortable { get; set; } public string width { get; set; } public bool visible { get; set; } public string className { get; set; } } public class DataTableSchemaResult { public readonly List<DataTableSchema> SchemaResult = new(); public readonly List<string> Colums = new(); public string SerializedSchemaResult = ""; }
متد CreateTableColumns خیلی ساده هست و فقط یک لیست از استرینگها را برمیگرداند.
SerializeSchema هم که لیست آبجکتهای مورد نیاز دیتاتیبل را سریالایز میکند.
نحوه استفاده:
در متد آغازین برنامه باید این کلاس را صدا بزنید و با هر روشی که دوست دارید، به view یا razor page ارسال کنید:
public void OnGet() { //Create Data Table Js Schema and Columns Dynamicly JsDataTblGeneretaor<yourVM> tblGeneretaor = new(); DataTableSchemaResult = tblGeneretaor.CreateTableColumns().CreateTableSchema().SerializeSchema().DataTableSchemaResult; }
نحوه سفارشی سازی:
.AddCustomSchema("yourProperty",visible:false)
var scheme = JSON.parse('@Html.Raw(Model.DataTableSchemaResult.SerializedSchemaResult)')
نحوه ساخت ستونها در view:
@foreach (var col in Model.DataTableSchemaResult.Colums) { <th>@col</th> }
Infrastructure as code پروسه تعریف کردن ساختار Infrastructure در قالب یک سری فایل است؛ بجای اینکه با ابزارهایی Interactive مانند Portalها به مدیریت Infra بپردازیم.
مزیت این روش در آن است که در صورت داشتن Stageهای مختلفی مانند Development, QA, Sandbox, Production و ...، ابتدا در تعدادی فایل، ساختار Infra مورد نیاز را نوشته و به صورت اتوماتیک Development را از روی آن میسازیم و بعد در صورت جواب گرفتن، QA و ... را نیز از روی همان میسازیم و از اینجا به بعد هر تغییری در Infra ابتدا در Development تست شده و در صورت جواب گرفتن، به QA و سپس Production میرود.
این روش به علت خودکار بودن، باعث میشود امکان اشتباه پایین بیاید و بسته به روش پیاده سازی، میتواند خیلی شبیه به Migrationها در EntityFramework باشد؛ چرا که در آنجا نیز Migrationها ایجاد و بر روی دیتابیس Development اعمال میشوند و در صورت جواب گرفتن در تستها، میتوان تغییرات را به صورت خودکار روی QA و ... نیز ارسال نمود و امکان فراموش کردن چیزی در این میان وجود ندارد.
یکی از بهترین ابزارهای Infra as a code، ابزاری به نام Pulumi است که هم با kubernetes و هم با Azure و AWS و Google Cloud سازگار است. البته برای مثال Kubernetes خود روشهایی را برای نگهداری ساختار Infra در قالب فایلهای کانفیگاش دارد، ولی Pulumi هم سادگی و آسانی را ارائه میدهد و هم در Cloud که شما عموما از Database Serviceها و App Service و Logging Systemهای مختص خود Cloud استفاده میکنید که زیر مجموعه kubernetes نیستند، میتوانید کنترل کل Cloud و Kubernetes را همزمان با یک ابزار انجام دهید.
برای مثال، افراد در Cloud به جای ساختن دیتابیس در Kubernetes، از Database as a service استفاده میکنند که به معنای رسیدن به کیفیت بالاتر و کاهش هزینههاست. یا درخواست سرویس DDos protection و CDN یا Media Services و ... نیز مثالهایی دیگر از این نوع هستند.
برای کار با Pulumi هم میتوانید از سایت آن، اکانت بگیرید که در این صورت Snapshotهای تغییرات Infra در کد، داخل سایت Pulumi نگهداری میشوند و هم میتوانید Snapshotها را مشابه Snapshotهای Entity Framework داخل خود سورس کنترلر نگه دارید که در این صورت وابستگی به سرورهای Pulumi نیز نخواهید داشت.
بعد از نصب Pulumi CLI میتوانید یک پروژه را با یکی از زبانهای برنامه نویسی Go - C# - JavaScript - Python ایجاد نموده و سپس داخل آن Resourceهای خود را بسازید و تنظیمات Firewall را ایجاد کنید و ...
سپس با دستور Pulumi up تغییرات شما روی Development یا هر Stage دیگری که انتخاب کردهاید، اعمال میشوند. در نهایت اگر باز Infra احتیاج به تغییری داشته باشد، ابتدا فایل پروژه را تغییر میدهید و بعد سایر روالهای لازم درون تیمی، اعم از Code Review و ... را میگذرانید و سپس مجدد Pulumi up را اجرا میکنید.
در ادامه یک نمونه کد را میبینیم، از راه اندازی App Service - Sql Server - Blob Storage - Application Insights
App Service ساخته شده که Backend ما را اجرا میکند، هم Connection String اتصال به دیتابیس را خواهد داشت و هم Connection String مربوط به Blob Storage را تا فایلهایش را درون آن ذخیره کند و در نهایت Application Insights هم وظیفه Monitoring را به عهده خواهد داشت.
var sqlDatabasePassword = pulumiConfig.RequireSecret("sql-server-nikola-dev-password"); var sqlDatabaseUserId = pulumiConfig.RequireSecret("sql-server-nikola-dev-user-id"); var resourceGroup = new ResourceGroup("rg-dds-nikola-dev", new ResourceGroupArgs { Name = "rg-dds-nikola-dev", Location = "WestUS" }); var storageAccount = new Account("storagenikoladev", new AccountArgs { Name = "storagenikoladev", ResourceGroupName = resourceGroup.Name, Location = resourceGroup.Location, AccountKind = "StorageV2", AccountReplicationType = "LRS", AccountTier = "Standard", }); var container = new Container("container-nikola-dev", new ContainerArgs { Name = "container-nikola-dev", ContainerAccessType = "blob", StorageAccountName = storageAccount.Name }); var blobStorage = new Blob("blob-nikola-dev", new BlobArgs { Name = "blob-nikola-dev", StorageAccountName = storageAccount.Name, StorageContainerName = container.Name, Type = "Block" }); var appInsights = new Insights("app-insights-nikola-dev", new InsightsArgs { Name = "app-insights-nikola-dev", ResourceGroupName = resourceGroup.Name, Location = resourceGroup.Location, ApplicationType = "web" // also general for mobile apps }); var sqlServer = new SqlServer("sql-server-nikola-dev", new SqlServerArgs { Name = "sql-server-nikola-dev", ResourceGroupName = resourceGroup.Name, Location = resourceGroup.Location, AdministratorLogin = sqlDatabaseUserId, AdministratorLoginPassword = sqlDatabasePassword, Version = "12.0" }); var sqlDatabase = new Database("sql-database-nikola-dev", new DatabaseArgs { Name = "sql-database-nikola-dev", ResourceGroupName = resourceGroup.Name, Location = resourceGroup.Location, ServerName = sqlServer.Name, RequestedServiceObjectiveName = "Basic" }); var appServicePlan = new Plan("app-plan-nikola-dev", new PlanArgs { Name = "app-plan-nikola-dev", ResourceGroupName = resourceGroup.Name, Location = resourceGroup.Location, Sku = new PlanSkuArgs { Tier = "Shared", Size = "D1" } }); var appService = new AppService("app-service-nikola-dev", new AppServiceArgs { Name = "app-service-nikola-dev", ResourceGroupName = resourceGroup.Name, Location = resourceGroup.Location, AppServicePlanId = appServicePlan.Id, SiteConfig = new AppServiceSiteConfigArgs { Use32BitWorkerProcess = true, // X64 not allowed in shared plan! AlwaysOn = false, // not allowed in shared plan! Http2Enabled = true }, AppSettings = { { "ApplicationInsights:InstrumentationKey", appInsights.InstrumentationKey }, { "APPINSIGHTS_INSTRUMENTATIONKEY", appInsights.InstrumentationKey } }, ConnectionStrings = new InputList<AppServiceConnectionStringArgs>() { new AppServiceConnectionStringArgs { Name = "AppDbConnectionString", Type = "SQLAzure", Value = Output.Tuple(sqlServer.Name, sqlDatabase.Name, sqlDatabaseUserId, sqlDatabasePassword).Apply(t => { (string _sqlServer, string _sqlDatabase, string _sqlDatabaseUserId, string _sqlDatabasePassword) = t; return $"Data Source=tcp:{_sqlServer}.database.windows.net;Initial Catalog={_sqlDatabase};User ID={_sqlDatabaseUserId};Password={_sqlDatabasePassword};Max Pool Size=1024;Persist Security Info=true;Application Name=Nikola"; }) }, new AppServiceConnectionStringArgs { Name = "AzureBlobStorageConnectionString", Type = "Custom", Value = Output.Tuple(storageAccount.PrimaryAccessKey, storageAccount.Name).Apply(t => { (string _primaryAccess, string _storageAccountName) = t; return $"DefaultEndpointsProtocol=https;AccountName={_storageAccountName};AccountKey={_primaryAccess};EndpointSuffix=core.windows.net"; }) } } }); appService.OutboundIpAddresses.Apply(ips => { foreach (string ip in ips.Split(',')) { new FirewallRule($"app-srv-{ip}", new FirewallRuleArgs { Name = $"app-srv-{ip}", EndIpAddress = ip, ResourceGroupName = resourceGroup.Name, ServerName = sqlServer.Name, StartIpAddress = ip }); } return (string?)null; });
سپس زمانیکه تغییرات قرار است روی QA برود، روال CI/CD میتواند به صورت خودکار ابتدا Infra مربوط به خودش را (یعنی QA) را تغییر دهد تا Redis دار شود و سپس پروژه را پابلیش کند و Migrationهای مربوط به Database را هم اجرا کند و اگر کل این فرآیند با موفقیت طی شود، مجدد در هنگام پابلیش به Production نیز بدون هر گونه کار دستی، تمامی این موارد به شکل خودکار اعمال میشوند و این خود یک بهبود اساسی را در روال DevOps پروژه ایجاد میکند.
پیشنیاز این بحث مطالعهی مطلب «صفحه بندی و مرتب سازی خودکار اطلاعات به کمک jqGrid در ASP.NET MVC» است و در اینجا جهت کوتاه شدن بحث، صرفا به تغییرات مورد نیاز جهت اعمال بر روی مثال اول اکتفاء خواهد شد.
تغییرات مورد نیاز سمت کلاینت جهت فعال سازی جستجو در jqGrid
در سمت کلاینت، در حین تعریف ستونها، ابتدا باید توسط مقدار دهی خاصیت search، ستونهای مشارکت کنندهی در حین جستجو را مشخص کرد:
- برای نمونه در اینجا search: true، جهت دو ستون نام محصول و نام تولید کننده، تنظیم شدهاند.
- stype، روش مقایسهی مقادیر را مشخص میکند. مقدار پیش فرض آن text است و مقادیری مانند int، integer، float، number، numeric، date و datetime را میپذیرد.
- searchoptions برای تنظیم جزئیات نحوهی جستجوی بر روی فیلدها بکار میرود. توسط sopt میتوان آرایهای با مقادیر ذیل را مقدار دهی کرد:
eq به معنای مساوی است، ne، مساوی نیست، lt کمتر و به همین ترتیب.
البته باید دقت داشت که آرایه فوق کاملترین حالت ممکن است و ضرورتی ندارد تمام حالات را برای یک فیلد تعریف کرد. چون برای مثال جستجو cn یا contains برای مقادیر رشتهای معنا دارد و نه سایر حالات.
- searchoptions گزینههای دیگری را نیز میتواند شامل شود. برای مثال در حین نمایش جستجوی داخل ردیفی یا صفحهی دیالوگ مخصوص آن، قصد داریم فیلد نام تولید کننده را توسط یک drop down نمایش دهیم و نه یک text box پیش فرض. برای این منظور dataUrl مقدار دهی شدهاست.
SuppliersSelect آن به اکشن متد ذیل اشاره میکند که لیست تولید کنندگان را با فرمت لیستی از SelectListItemها به یک partial view تولید کنندهی دراپ داون ارسال میکند:
و محتوای فایل _SelectPartial نیز به صورت ذیل است:
در این حالت با نمایش صفحهی جستجو و انتخاب فیلد نام تولید کننده، به صورت خودکار یک dorp down پویا نمایش داده خواهد شد.
- اگر دقت کرده باشید، نام فیلد تولید کننده با Supplier.Id مقدار دهی شدهاست. علت اینجا است که در زمان استفاده از drop down، مقدار Id آیتم انتخابی، به سرور ارسال میشود. به همین جهت کار کردن پویا با Supplier.Id سادهتر خواهد بود.
- برای نمایش دیالوگ و یا نوار ابزار توکار جستجو، میتوان دکمههایی را به فوتر گرید اضافه کرد:
در اینجا نکات ذیل قابل توجه هستند:
- با استفاده از متد jqGrid و پارامتر navGrid، در ناحیهی pager گرید، تنظیمات جستجو را فعال کردهایم.
multipleSearch به معنای امکان جستجوی همزمان بر روی بیش از یک فیلد است. closeOnEscape سبب بسته شدن صفحهی دیالوگ جستجو با فشردن دکمهی ESC میشود. اگر closeAfterSearch به true تنظیم نشود، صفحهی دیالوگ جستجو پس از جستجو، در صفحه باقی مانده و بسته نخواهد شد.
- این دکمهی جستجو، جزو موارد توکار jqGrid است. اگر قصد داشته باشیم یک دکمهی سفارشی دیگر را نیز اضافه کنیم، مجددا از متد jqGrid با پارامتر navButtonAdd در ناحیهی pager استفاده خواهیم کرد. کلیک بر روی آن سبب اجرای متد toolbarSearching میشود.
در اینجا حداقل سه نوع جستجو را میتوان فعال کرد:
- filterToolbar که سبب نمایش نوار ابزار جستجو، دقیقا بالای ستونهای جدول میشود.
- searchGrid که صفحهی دیالوگ مستقلی را جهت جستجو به صورت پویا تولید میکند. اگر خاصیت multipleSearch آن به true تنظیم نشود، این جستجو هربار تنها بر روی یک فیلد قابل انجام خواهد بود و برعکس.
- در حالت جستجوی نوار ابزاری، اگر خواص searchOperators و stringResult به true تنظیم شوند، مانند تصویر ذیل، به ازای هر ستون میتوان از عملگرهای مختلفی استفاده کرد. در غیراینصورت جستجوی انجام شده بر اساس groupOp و defaultSearch پیش فرض انجام میشود. یعنی And یا Or تمام موارد تنها در حالت مثلا contains یا تساوی و امثال آن و نه حالت پیشرفتهی انتخاب عملگرها توسط کاربر.
یک نکته
اگر میخواهید صفحهی جستجو در وسط صفحه ظاهر شود، میتوانید از تنظیمات CSS ذیل استفاده کنید:
پردازش سمت سرور جستجوی پویای jqGrid
کدهای سمت سرور، با کدهای استفاده از dynamic LINQ مایکروسافت یکی است. با این تفاوت که اینبار قسمت where این کوئری نیز پویا میباشد. پیشتر قسمت order by را پویا پردازش کرده بودیم. برای ساخت where پویا که در dynamic LINQ به خوبی پشتیبانی میشود، باید ابتدا ساختار اطلاعات ارسالی به سرور را آنالیز کنیم:
در اینجا ساختار ارسالی به سرور را در سه حالت مختلف جستجوی پویای jqGrid، ملاحظه میکنید:
- در تمام این حالات پارامتر _search مساوی true است (تفاوت آن با درخواست اطلاعات معمولی).
- در حالت جستجوی نوار ابزاری، اگر گزینههای searchOperators و stringResult به true تنظیم نشوند، حالت toolbar search فوق را شاهد خواهیم بود. در غیراینصورت به حالت multi-field search سوئیچ میشود.
- در حالت جستجوی تک فیلدی توسط صفحه دیالوگ جستجوی jqGrid، فیلد در حال جستجو توسط searchField و مقدار آن توسط searchString به سرور ارسال شدهاند. مابقی پارامترها نال هستند.
- در حالت جستجوی چند فیلدی توسط صفحه دیالوگ جستجوی jqGrid، اینبار filters مقدار دهی شدهاست و سایر پارامترها نال هستند. مقدار filters ارسالی، در حقیقت یک شیء JSON است با ساختار کلی ذیل:
که میتوان چنین ساختاری را برای آن متصور شد:
در اینجا AND و Or کلی مشخص میشود، به همراه فیلدهای ارسالی به سرور، عملگرهای اعمالی بر روی آنها و مقادیر مرتبط.
اگر این موارد را کنار هم قرار دهیم، به متدی عمومی ApplyFilter با امضای ذیل خواهیم رسید.
کدهای کامل آنرا به علت طولانی بودن پردازش سه حالت ذکر شدهی فوق، از پروژهی پیوست میتوانید دریافت کنید.
پس از آن، تغییراتی که در کدهای متد GetProducts باید اعمال شوند به صورت ذیل است:
- ابتدا چند پارامتر اضافهتر به امضای متد اضافه شدهاند، تا فیلدهای جستجو را نیز دریافت کنند.
- نوع متد به HttpPost تغییر کردهاست. این مورد برای ارسال اطلاعات حجیم جستجوها به سرور ضروری است و بهتر است از حالت Get استفاده نشود.
این حالت در سمت کلاینت نیز باید تنظیم شود:
- سایر سطرها مانند قبل است؛ فقط یک سطر ذیل جهت اعمال where پویا به عبارت LINQ ساخته شده، اضافه شدهاست:
برای مطالعه بیشتر
جستجوی تک فیلدی
جستجوی نوار ابزاری
جستجوی چند فیلدی
کدهای کامل این مثال را از اینجا میتوانید دریافت کنید
jqGrid03.zip
تغییرات مورد نیاز سمت کلاینت جهت فعال سازی جستجو در jqGrid
در سمت کلاینت، در حین تعریف ستونها، ابتدا باید توسط مقدار دهی خاصیت search، ستونهای مشارکت کنندهی در حین جستجو را مشخص کرد:
colModel: [ { name: 'Name', index: 'Name', align: 'right', width: 200, search: true, stype: 'text', searchoptions: { sopt: searchOptions } }, { name: 'Supplier.Id', index: 'Supplier.Id', align: 'right', width: 200, search: true, stype: 'select', edittype: 'select', searchOperators: true, searchoptions: { sopt: searchOptions, dataUrl: '@Url.Action("SuppliersSelect","Home")' } } ],
- stype، روش مقایسهی مقادیر را مشخص میکند. مقدار پیش فرض آن text است و مقادیری مانند int، integer، float، number، numeric، date و datetime را میپذیرد.
- searchoptions برای تنظیم جزئیات نحوهی جستجوی بر روی فیلدها بکار میرود. توسط sopt میتوان آرایهای با مقادیر ذیل را مقدار دهی کرد:
var searchOptions = ['eq', 'ne', 'lt', 'le', 'gt', 'ge', 'bw', 'bn', 'in', 'ni', 'ew', 'en', 'cn', 'nc'];
البته باید دقت داشت که آرایه فوق کاملترین حالت ممکن است و ضرورتی ندارد تمام حالات را برای یک فیلد تعریف کرد. چون برای مثال جستجو cn یا contains برای مقادیر رشتهای معنا دارد و نه سایر حالات.
- searchoptions گزینههای دیگری را نیز میتواند شامل شود. برای مثال در حین نمایش جستجوی داخل ردیفی یا صفحهی دیالوگ مخصوص آن، قصد داریم فیلد نام تولید کننده را توسط یک drop down نمایش دهیم و نه یک text box پیش فرض. برای این منظور dataUrl مقدار دهی شدهاست.
SuppliersSelect آن به اکشن متد ذیل اشاره میکند که لیست تولید کنندگان را با فرمت لیستی از SelectListItemها به یک partial view تولید کنندهی دراپ داون ارسال میکند:
public ActionResult SuppliersSelect() { var list = ProductDataSource.LatestProducts; var suppliers = list.Select(x => new SelectListItem { Text = x.Supplier.CompanyName, Value = x.Supplier.Id.ToString(CultureInfo.InvariantCulture) }).ToList(); return PartialView("_SelectPartial", suppliers); }
@model IList<SelectListItem> @Html.DropDownList("srch", Model)
- اگر دقت کرده باشید، نام فیلد تولید کننده با Supplier.Id مقدار دهی شدهاست. علت اینجا است که در زمان استفاده از drop down، مقدار Id آیتم انتخابی، به سرور ارسال میشود. به همین جهت کار کردن پویا با Supplier.Id سادهتر خواهد بود.
- برای نمایش دیالوگ و یا نوار ابزار توکار جستجو، میتوان دکمههایی را به فوتر گرید اضافه کرد:
$(document).ready(function () { $('#list').jqGrid({ // ... مانند قبل و توضیحات فوق }) .jqGrid('navGrid', '#pager', { add: false, edit: false, del: false }, {}, // default settings for edit {}, // default settings for add {}, // delete instead that del:false we need this { // search options multipleSearch: true, closeOnEscape: true, closeAfterSearch: true, ignoreCase: true }) .jqGrid('navButtonAdd', "#pager", { caption: "نوار ابزار جستجو", title: "Search Toolbar", buttonicon: 'ui-icon-search', onClickButton: function () { toolbarSearching(); } }); }); function toolbarSearching() { $('#list').filterToolbar({ groupOp: 'OR', defaultSearch: "cn", autosearch: true, searchOnEnter: true, searchOperators: true, // فعال سازی منوی اپراتورها stringResult : true // وجود این سطر سبب میشود تا اپراتورها به سرور ارسال شوند }); }; function singleSearching() { $('#list').searchGrid({ closeAfterSearch: true }); }; function advancedSearching() { $('#list').searchGrid({ multipleSearch: true, closeAfterSearch: true }); };
- با استفاده از متد jqGrid و پارامتر navGrid، در ناحیهی pager گرید، تنظیمات جستجو را فعال کردهایم.
multipleSearch به معنای امکان جستجوی همزمان بر روی بیش از یک فیلد است. closeOnEscape سبب بسته شدن صفحهی دیالوگ جستجو با فشردن دکمهی ESC میشود. اگر closeAfterSearch به true تنظیم نشود، صفحهی دیالوگ جستجو پس از جستجو، در صفحه باقی مانده و بسته نخواهد شد.
- این دکمهی جستجو، جزو موارد توکار jqGrid است. اگر قصد داشته باشیم یک دکمهی سفارشی دیگر را نیز اضافه کنیم، مجددا از متد jqGrid با پارامتر navButtonAdd در ناحیهی pager استفاده خواهیم کرد. کلیک بر روی آن سبب اجرای متد toolbarSearching میشود.
در اینجا حداقل سه نوع جستجو را میتوان فعال کرد:
- filterToolbar که سبب نمایش نوار ابزار جستجو، دقیقا بالای ستونهای جدول میشود.
- searchGrid که صفحهی دیالوگ مستقلی را جهت جستجو به صورت پویا تولید میکند. اگر خاصیت multipleSearch آن به true تنظیم نشود، این جستجو هربار تنها بر روی یک فیلد قابل انجام خواهد بود و برعکس.
- در حالت جستجوی نوار ابزاری، اگر خواص searchOperators و stringResult به true تنظیم شوند، مانند تصویر ذیل، به ازای هر ستون میتوان از عملگرهای مختلفی استفاده کرد. در غیراینصورت جستجوی انجام شده بر اساس groupOp و defaultSearch پیش فرض انجام میشود. یعنی And یا Or تمام موارد تنها در حالت مثلا contains یا تساوی و امثال آن و نه حالت پیشرفتهی انتخاب عملگرها توسط کاربر.
یک نکته
اگر میخواهید صفحهی جستجو در وسط صفحه ظاهر شود، میتوانید از تنظیمات CSS ذیل استفاده کنید:
/* align center search popup in jqgrid */ .ui-jqdialog { display: none; width: 300px; position: absolute; padding: .2em; font-size: 11px; overflow: visible; left: 30% !important; top: 40% !important; }
پردازش سمت سرور جستجوی پویای jqGrid
کدهای سمت سرور، با کدهای استفاده از dynamic LINQ مایکروسافت یکی است. با این تفاوت که اینبار قسمت where این کوئری نیز پویا میباشد. پیشتر قسمت order by را پویا پردازش کرده بودیم. برای ساخت where پویا که در dynamic LINQ به خوبی پشتیبانی میشود، باید ابتدا ساختار اطلاعات ارسالی به سرور را آنالیز کنیم:
//single field search //_search=true&nd=1403935889318&rows=10&page=1&sidx=Id&sord=asc&searchField=Id&searchString=4444&searchOper=eq&filters= //multi-field search //_search=true&nd=1403935941367&rows=10&page=1&sidx=Id&sord=asc&filters=%7B%22groupOp%22%3A%22AND%22%2C%22rules%22%3A%5B%7B%22field%22%3A%22Id%22%2C%22op%22%3A%22eq%22%2C%22data%22%3A%2244%22%7D%2C%7B%22field%22%3A%22SupplierID%22%2C%22op%22%3A%22eq%22%2C%22data%22%3A%221%22%7D%5D%7D&searchField=&searchString=&searchOper= // filters -> {"groupOp":"AND","rules":[{"field":"All","op":"cn","data":"fffff"},{"field":"Price","op":"bn","data":"ffff"}]} //toolbar search //_search=true&nd=1403935593036&rows=10&page=1&sidx=Id&sord=asc&Id=2&Name=333&SupplierID=1&CategoryID=1&Price=44
- در تمام این حالات پارامتر _search مساوی true است (تفاوت آن با درخواست اطلاعات معمولی).
- در حالت جستجوی نوار ابزاری، اگر گزینههای searchOperators و stringResult به true تنظیم نشوند، حالت toolbar search فوق را شاهد خواهیم بود. در غیراینصورت به حالت multi-field search سوئیچ میشود.
- در حالت جستجوی تک فیلدی توسط صفحه دیالوگ جستجوی jqGrid، فیلد در حال جستجو توسط searchField و مقدار آن توسط searchString به سرور ارسال شدهاند. مابقی پارامترها نال هستند.
- در حالت جستجوی چند فیلدی توسط صفحه دیالوگ جستجوی jqGrid، اینبار filters مقدار دهی شدهاست و سایر پارامترها نال هستند. مقدار filters ارسالی، در حقیقت یک شیء JSON است با ساختار کلی ذیل:
{ "groupOp": "AND", "groups" : [ { "groupOp": "OR", "rules": [ { "field": "name", "op": "eq", "data": "England" }, { "field": "id", "op": "le", "data": "5"} ] } ], "rules": [ { "field": "name", "op": "eq", "data": "Romania" }, { "field": "id", "op": "le", "data": "1"} ] }
public class SearchFilter { public string groupOp { set; get; } public List<SearchGroup> groups { set; get; } public List<SearchRule> rules { set; get; } } public class SearchRule { public string field { set; get; } public string op { set; get; } public string data { set; get; } public override string ToString() { return string.Format("'{0}' {1} '{2}'", field, op, data); } } public class SearchGroup { public string groupOp { set; get; } public List<SearchRule> rules { set; get; } }
اگر این موارد را کنار هم قرار دهیم، به متدی عمومی ApplyFilter با امضای ذیل خواهیم رسید.
public IQueryable<T> ApplyFilter<T>(IQueryable<T> query, bool _search, string searchField, string searchString, string searchOper, string filters, NameValueCollection form)
پس از آن، تغییراتی که در کدهای متد GetProducts باید اعمال شوند به صورت ذیل است:
[HttpPost] public ActionResult GetProducts(string sidx, string sord, int page, int rows, bool _search, string searchField, string searchString, string searchOper, string filters) { var list = ProductDataSource.LatestProducts; var pageIndex = page - 1; var pageSize = rows; var totalRecords = list.Count; var totalPages = (int)Math.Ceiling(totalRecords / (float)pageSize); var productsQuery = list.AsQueryable(); productsQuery = new JqGridSearch().ApplyFilter(productsQuery, _search, searchField, searchString, searchOper, filters, this.Request.Form); var productsList = productsQuery.OrderBy(sidx + " " + sord) .Skip(pageIndex * pageSize) .Take(pageSize) .ToList(); var productsData = new JqGridData { Total = totalPages, Page = page, Records = totalRecords, Rows = (productsList.Select(product => new JqGridRowData { Id = product.Id, RowCells = new List<string> { product.Id.ToString(CultureInfo.InvariantCulture), product.Name, product.Supplier.CompanyName, product.Category.Name, product.Price.ToString(CultureInfo.InvariantCulture) } })).ToArray() }; return Json(productsData, JsonRequestBehavior.AllowGet); }
- نوع متد به HttpPost تغییر کردهاست. این مورد برای ارسال اطلاعات حجیم جستجوها به سرور ضروری است و بهتر است از حالت Get استفاده نشود.
این حالت در سمت کلاینت نیز باید تنظیم شود:
$('#list').jqGrid({ //url access method type mtype: 'POST',
productsQuery = new JqGridSearch().ApplyFilter(productsQuery, _search, searchField, searchString, searchOper, filters, this.Request.Form);
برای مطالعه بیشتر
جستجوی تک فیلدی
جستجوی نوار ابزاری
جستجوی چند فیلدی
کدهای کامل این مثال را از اینجا میتوانید دریافت کنید
jqGrid03.zip
سلام
سپس دستور Truncate را روی جدول Table_3 اجرا کنید:
حال یک Sequence ایجاد کنید:
در ادامه محتویات جدول کپی را به جدول اصلی منتقل نمایید:
شما قادر نیستید یک فیلد Identity را بروز رسانی نمایید، دستور set insert_identity Tablename on به شما اجازه Insert به جدول بدون Identity را میدهد، برای اینکه بتوانید Gap مرتبط به فیلد Identity را در جدول برطرف کنید، در ابتدا از جدول مورد نظر خود یک کپی تهیه و جدول اصلی را Truncate کنید، سپس یک Sequencer ایجاد و محتویات جدول کپی را بوسیله Sequencer در جدول اصلی کپی نمایید.
فرض کنیم جدول اصلی Table_3 باشد، ابتدا آن را کپی میکنیم در جدولی به نام T
Select * into T from table_3
truncate table dbo.table_3
CREATE SEQUENCE testEventCounter AS int START WITH 1 INCREMENT BY 1 ;
SET IDENTITY_INSERT table_3 on INSERT INTO table_3 (ID, Descritp) SELECT NEXT VALUE FOR testEventCounter AS id , Descritp FROM T
راه دیگر این است که به جای استفاده از Identity از Sequence در فیلد خود استفاده نمایید، بصورت زیر :
CREATE TABLE Table3 ( ID int PRIMARY KEY CLUSTERED DEFAULT (NEXT VALUE FOR SequenceTest), De nvarchar(300) NULL ) ; GO
در هنگام ایجاد جدول Sequence را به فیلد ID ست کردیم.
حال هر زمانی که بخواهید میتوانید فیلد ID را مطابق Sequence خود بروز رسانی کنید:
Update table3 set id=(NEXT VALUE FOR testEventCounter )
موفق باشید و امیدوارم مفید واقع شده باشد
به لطف امکانات سیستمی اس کیوال سرورهای 2005 به بعد و DMV های آنها، آمارگیری از ریز اتفاقات رخ داده در یک اس کیوال سرور این روزها بسیار ساده شده است و نیازی به ابزارهای جانبی برای انجام این نوع عملیات نیست (یا کمتر هست). در ادامه مروری خواهیم داشت بر یک سری کوئری که اطلاعات جالبی را در مورد وضعیت رویههای ذخیره شدهی دیتابیسهای شما ارائه میدهند. لازم به ذکر است که اکثر این آمارها با هر بار ری استارت سرور، صفر خواهند شد.
آیا میدانید در یک دیتابیس خاص کدامیک از رویههای ذخیره شدهی شما بیش از سایرین مورد استفاده بود و آماری از این دست؟
use dbName;
SELECT TOP(100) qt.text AS 'SP Name',
qs.execution_count AS 'Execution Count',
qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS
'Calls/Second',
qs.total_worker_time / qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time / qs.execution_count AS 'AvgElapsedTime',
qs.max_logical_reads,
qs.max_logical_writes,
qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GETDATE()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = DB_ID() -- Filter by current database
ORDER BY
qs.execution_count DESC
البته مرتب سازی پیش فرض این کوئری بر اساس تعداد بار اجرا است (رویههای ذخیره شدهی محبوب!)، میشود آنرا بر اساس total_worker_time (فشار بر روی CPU سیستم)، total_logical_reads (فشار بر روی حافظه)، total_physical_reads (فشار I/O کوئریها)، total_logical_writes نیز مرتب کرد و نتایج جالب توجهی را بدست آورد.
آیا میدانید کدامیک از رویههای ذخیره شدهی شما بیش از سایرین کامپایل مجدد شده است؟
select top 50
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc
آیا میدانید آخرین باری که رویههای ذخیره شدهی شما ویرایش شدهاند چه زمانی بوده است؟
SELECT NAME,
create_date,
modify_date
FROM sys.objects
WHERE TYPE = 'P'
ORDER BY
Modify_Date DESC,
NAME
مطالب
امن سازی برنامههای ASP.NET Core توسط IdentityServer 4x - قسمت دوازدهم- یکپارچه سازی با اکانت گوگل
در مطلب قبلی «استفاده از تامین کنندههای هویت خارجی»، نحوهی استفاده از اکانتهای ویندوزی کاربران یک شبکه، به عنوان یک تامین کنندهی هویت خارجی بررسی شد. در ادامه میخواهیم از اطلاعات اکانت گوگل و IDP مبتنی بر OAuth 2.0 آن به عنوان یک تامین کنندهی هویت خارجی دیگر استفاده کنیم.
ثبت یک برنامهی جدید در گوگل
اگر بخواهیم از گوگل به عنوان یک IDP ثالث در IdentityServer استفاده کنیم، نیاز است در ابتدا برنامهی IDP خود را به آن معرفی و در آنجا ثبت کنیم. برای این منظور مراحل زیر را طی خواهیم کرد:
1- مراجعه به developer console گوگل و ایجاد یک پروژهی جدید
https://console.developers.google.com
در صفحهی باز شده، بر روی دکمهی select project در صفحه و یا لینک select a project در نوار ابزار آن کلیک کنید. در اینجا دکمهی new project و یا create را مشاهده خواهید کرد. هر دوی این مفاهیم به صفحهی زیر ختم میشوند:
در اینجا نامی دلخواه را وارد کرده و بر روی دکمهی create کلیک کنید.
2- فعالسازی API بر روی این پروژهی جدید
در ادامه بر روی لینک Enable APIs And Services کلیک کنید و سپس google+ api را جستجو نمائید.
پس از ظاهر شدن آن، این گزینه را انتخاب و در صفحهی بعدی، آنرا با کلیک بر روی دکمهی enable، فعال کنید.
3- ایجاد credentials
در اینجا بر روی دکمهی create credentials کلیک کرده و در صفحهی بعدی، این سه گزینه را با مقادیر مشخص شده، تکمیل کنید:
سپس در ذیل این صفحه بر روی دکمهی «What credentials do I need» کلیک کنید تا به صفحهی پس از آن هدایت شوید. اینجا است که مشخصات کلاینت OAuth 2.0 تکمیل میشوند. در این صفحه، سه گزینهی آنرا به صورت زیر تکمیل کنید:
• نام: همان مقدار پیشفرض آن
• Authorized JavaScript origins: آنرا خالی بگذارید.
• Authorized redirect URIs: این مورد همان callback address مربوط به IDP ما است که در اینجا آنرا با آدرس زیر مقدار دهی خواهیم کرد.
این آدرس، به آدرس IDP لوکال ما اشاره میکند و مسیر signin-google/ آن باید به همین نحو تنظیم شود تا توسط برنامه شناسایی شود.
سپس در ذیل این صفحه بر روی دکمهی «Create OAuth 2.0 Client ID» کلیک کنید تا به صفحهی «Set up the OAuth 2.0 consent screen» بعدی هدایت شوید. در اینجا دو گزینهی آنرا به صورت زیر تکمیل کنید:
- Email address: همان آدرس ایمیل واقعی شما است.
- Product name shown to users: یک نام دلخواه است. نام برنامهی خود را برای نمونه ImageGallery وارد کنید.
برای ادامه بر روی دکمهی Continue کلیک نمائید.
4- دریافت credentials
در پایان این گردش کاری، به صفحهی نهایی «Download credentials» میرسیم. در اینجا بر روی دکمهی download کلیک کنید تا ClientId و ClientSecret خود را توسط فایلی به نام client_id.json دریافت نمائید.
سپس بر روی دکمهی Done در ذیل صفحه کلیک کنید تا این پروسه خاتمه یابد.
تنظیم برنامهی IDP برای استفادهی از محتویات فایل client_id.json
پس از پایان عملیات ایجاد یک برنامهی جدید در گوگل و فعالسازی Google+ API در آن، یک فایل client_id.json را دریافت میکنیم که اطلاعات آن باید به صورت زیر به فایل آغازین برنامهی IDP اضافه شود:
الف) تکمیل فایل src\IDP\DNT.IDP\appsettings.json
در اینجا مقادیر خواص client_secret و client_id موجود در فایل client_id.json دریافت شدهی از گوگل را به صورت فوق به فایل appsettings.json اضافه میکنیم.
ب) تکمیل اطلاعات گوگل در کلاس آغازین برنامه
خود ASP.NET Core از تعریف اطلاعات اکانتهای Google, Facebook, Twitter, Microsoft Account و OpenID Connect پشتیبانی میکند که در اینجا نحوهی تنظیم اکانت گوگل آنرا مشاهده میکنید.
- authenticationScheme تنظیم شده باید یک عبارت منحصربفرد باشد.
- همچنین SignInScheme یک چنین مقداری را در اصل دارد:
از این نام برای تشکیل قسمتی از نام کوکی که اطلاعات اعتبارسنجی گوگل در آن ذخیره میشود، کمک گرفته خواهد شد.
آزمایش اعتبارسنجی کاربران توسط اکانت گوگل آنها
اکنون که تنظیمات اکانت گوگل به پایان رسید و همچنین به برنامه نیز معرفی شد، برنامهها را اجرا کنید. مشاهده خواهید کرد که امکان لاگین توسط اکانت گوگل نیز به صورت خودکار به صفحهی لاگین IDP ما اضافه شدهاست:
در اینجا با کلیک بر روی دکمهی گوگل، به صفحهی لاگین آن که به همراه نام برنامهی ما است و انتخاب اکانتی از آن هدایت میشویم:
پس از آن، از طرف گوگل به صورت خودکار به IDP (همان آدرسی که در فیلد Authorized redirect URIs وارد کردیم)، هدایت شده و callback رخداده، ما را به سمت صفحهی ثبت اطلاعات کاربر جدید هدایت میکند. این تنظیمات را در قسمت قبل ایجاد کردیم:
اگر خروجی متد FindUserFromExternalProvider آن null باشد، یعنی کاربری که از سمت تامین کنندهی هویت خارجی/گوگل به برنامهی ما وارد شدهاست، دارای اکانتی در سمت IDP نیست. به همین جهت او را به سمت صفحهی ثبت نام کاربر هدایت میکنیم.
در اینجا نحوهی اصلاح اکشن متد Callback را جهت هدایت یک کاربر جدید به صفحهی ثبت نام و تکمیل اطلاعات مورد نیاز IDP را مشاهده میکنید.
returnUrl ارسالی به اکشن متد RegisterUser، به همین اکشن متد جاری اشاره میکند. یعنی کاربر پس از تکمیل اطلاعات و اینبار نال نبودن user او، گردش کاری جاری را ادامه خواهد داد و به برنامه با این هویت جدید وارد میشود.
اتصال کاربر وارد شدهی از طریق یک IDP خارجی به اکانتی که هم اکنون در سطح IDP ما موجود است
تا اینجا اگر کاربری از طریق یک IDP خارجی به برنامه وارد شود، او را به صفحهی ثبت نام کاربر هدایت کرده و پس از دریافت اطلاعات او، اکانت خارجی او را به اکانتی جدید که در IDP خود ایجاد میکنیم، متصل خواهیم کرد. به همین جهت بار دومی که این کاربر به همین ترتیب وارد سایت میشود، دیگر صفحهی ثبت نام و تکمیل اطلاعات را مشاهده نمیکند. اما ممکن است کاربری که برای اولین بار از طریق یک IDP خارجی به سایت ما وارد شدهاست، هم اکنون دارای یک اکانت دیگری در سطح IDP ما باشد؛ در اینجا فقط اتصالی بین این دو صورت نگرفتهاست. بنابراین در این حالت بجای ایجاد یک اکانت جدید، بهتر است از همین اکانت موجود استفاده کرد و صرفا اتصال UserLogins او را تکمیل نمود.
به همین جهت ابتدا نیاز است لیست Claims بازگشتی از گوگل را بررسی کنیم:
در اینجا پس از فراخوانی FindUserFromExternalProvider، لیست Claims بازگشت داده شدهی توسط IDP خارجی را لاگ میکنیم که در حالت استفادهی از گوگل چنین خروجی را دارد:
بنابراین اگر بخواهیم بر اساس این claims بازگشتی از گوگل، کاربر جاری در بانک اطلاعاتی خود را بیابیم، فقط کافی است اطلاعات claim مخصوص emailaddress آنرا مورد استفاده قرار دهیم:
در اینجا ابتدا بررسی شدهاست که آیا کاربر جاری واکشی شدهی از بانک اطلاعاتی نال است؟ اگر بله، اینبار بجای هدایت مستقیم او به صفحهی ثبت کاربر و تکمیل مشخصات او، مقدار email این کاربر را از لیست claims بازگشتی او از طرف گوگل، استخراج میکنیم. سپس بر این اساس اگر کاربری در بانک اطلاعاتی وجود داشت، تنها اطلاعات تکمیلی UserLogin او را که در اینجا خالی است، به اکانت گوگل او متصل میکنیم. به این ترتیب دیگر کاربر نیازی نخواهد داشت تا به صفحهی ثبت اطلاعات تکمیلی هدایت شود و یا اینکه بیجهت رکورد User جدیدی را مخصوص او به بانک اطلاعاتی اضافه کنیم.
کدهای کامل این قسمت را از اینجا میتوانید دریافت کنید.
برای اجرای برنامه:
- ابتدا به پوشهی src\WebApi\ImageGallery.WebApi.WebApp وارد شده و dotnet_run.bat آنرا اجرا کنید تا WebAPI برنامه راه اندازی شود.
- سپس به پوشهی src\IDP\DNT.IDP مراجعه کرده و و dotnet_run.bat آنرا اجرا کنید تا برنامهی IDP راه اندازی شود.
- در آخر به پوشهی src\MvcClient\ImageGallery.MvcClient.WebApp وارد شده و dotnet_run.bat آنرا اجرا کنید تا MVC Client راه اندازی شود.
اکنون که هر سه برنامه در حال اجرا هستند، مرورگر را گشوده و مسیر https://localhost:5001 را درخواست کنید. در صفحهی login نام کاربری را User 1 و کلمهی عبور آنرا password وارد کنید.
ثبت یک برنامهی جدید در گوگل
اگر بخواهیم از گوگل به عنوان یک IDP ثالث در IdentityServer استفاده کنیم، نیاز است در ابتدا برنامهی IDP خود را به آن معرفی و در آنجا ثبت کنیم. برای این منظور مراحل زیر را طی خواهیم کرد:
1- مراجعه به developer console گوگل و ایجاد یک پروژهی جدید
https://console.developers.google.com
در صفحهی باز شده، بر روی دکمهی select project در صفحه و یا لینک select a project در نوار ابزار آن کلیک کنید. در اینجا دکمهی new project و یا create را مشاهده خواهید کرد. هر دوی این مفاهیم به صفحهی زیر ختم میشوند:
در اینجا نامی دلخواه را وارد کرده و بر روی دکمهی create کلیک کنید.
2- فعالسازی API بر روی این پروژهی جدید
در ادامه بر روی لینک Enable APIs And Services کلیک کنید و سپس google+ api را جستجو نمائید.
پس از ظاهر شدن آن، این گزینه را انتخاب و در صفحهی بعدی، آنرا با کلیک بر روی دکمهی enable، فعال کنید.
3- ایجاد credentials
در اینجا بر روی دکمهی create credentials کلیک کرده و در صفحهی بعدی، این سه گزینه را با مقادیر مشخص شده، تکمیل کنید:
• Which API are you using? – Google+ API • Where will you be calling the API from? – Web server (e.g. node.js, Tomcat) • What data will you be accessing? – User data
• نام: همان مقدار پیشفرض آن
• Authorized JavaScript origins: آنرا خالی بگذارید.
• Authorized redirect URIs: این مورد همان callback address مربوط به IDP ما است که در اینجا آنرا با آدرس زیر مقدار دهی خواهیم کرد.
https://localhost:6001/signin-google
سپس در ذیل این صفحه بر روی دکمهی «Create OAuth 2.0 Client ID» کلیک کنید تا به صفحهی «Set up the OAuth 2.0 consent screen» بعدی هدایت شوید. در اینجا دو گزینهی آنرا به صورت زیر تکمیل کنید:
- Email address: همان آدرس ایمیل واقعی شما است.
- Product name shown to users: یک نام دلخواه است. نام برنامهی خود را برای نمونه ImageGallery وارد کنید.
برای ادامه بر روی دکمهی Continue کلیک نمائید.
4- دریافت credentials
در پایان این گردش کاری، به صفحهی نهایی «Download credentials» میرسیم. در اینجا بر روی دکمهی download کلیک کنید تا ClientId و ClientSecret خود را توسط فایلی به نام client_id.json دریافت نمائید.
سپس بر روی دکمهی Done در ذیل صفحه کلیک کنید تا این پروسه خاتمه یابد.
تنظیم برنامهی IDP برای استفادهی از محتویات فایل client_id.json
پس از پایان عملیات ایجاد یک برنامهی جدید در گوگل و فعالسازی Google+ API در آن، یک فایل client_id.json را دریافت میکنیم که اطلاعات آن باید به صورت زیر به فایل آغازین برنامهی IDP اضافه شود:
الف) تکمیل فایل src\IDP\DNT.IDP\appsettings.json
{ "Authentication": { "Google": { "ClientId": "xxxx", "ClientSecret": "xxxx" } } }
ب) تکمیل اطلاعات گوگل در کلاس آغازین برنامه
namespace DNT.IDP { public class Startup { public void ConfigureServices(IServiceCollection services) { // ... services.AddAuthentication() .AddGoogle(authenticationScheme: "Google", configureOptions: options => { options.SignInScheme = IdentityServerConstants.ExternalCookieAuthenticationScheme; options.ClientId = Configuration["Authentication:Google:ClientId"]; options.ClientSecret = Configuration["Authentication:Google:ClientSecret"]; }); }
- authenticationScheme تنظیم شده باید یک عبارت منحصربفرد باشد.
- همچنین SignInScheme یک چنین مقداری را در اصل دارد:
public const string ExternalCookieAuthenticationScheme = "idsrv.external";
آزمایش اعتبارسنجی کاربران توسط اکانت گوگل آنها
اکنون که تنظیمات اکانت گوگل به پایان رسید و همچنین به برنامه نیز معرفی شد، برنامهها را اجرا کنید. مشاهده خواهید کرد که امکان لاگین توسط اکانت گوگل نیز به صورت خودکار به صفحهی لاگین IDP ما اضافه شدهاست:
در اینجا با کلیک بر روی دکمهی گوگل، به صفحهی لاگین آن که به همراه نام برنامهی ما است و انتخاب اکانتی از آن هدایت میشویم:
پس از آن، از طرف گوگل به صورت خودکار به IDP (همان آدرسی که در فیلد Authorized redirect URIs وارد کردیم)، هدایت شده و callback رخداده، ما را به سمت صفحهی ثبت اطلاعات کاربر جدید هدایت میکند. این تنظیمات را در قسمت قبل ایجاد کردیم:
namespace DNT.IDP.Controllers.Account { [SecurityHeaders] [AllowAnonymous] public class ExternalController : Controller { public async Task<IActionResult> Callback() { var result = await HttpContext.AuthenticateAsync(IdentityServer4.IdentityServerConstants.ExternalCookieAuthenticationScheme); var returnUrl = result.Properties.Items["returnUrl"] ?? "~/"; var (user, provider, providerUserId, claims) = await FindUserFromExternalProvider(result); if (user == null) { // user = AutoProvisionUser(provider, providerUserId, claims); var returnUrlAfterRegistration = Url.Action("Callback", new { returnUrl = returnUrl }); var continueWithUrl = Url.Action("RegisterUser", "UserRegistration" , new { returnUrl = returnUrlAfterRegistration, provider = provider, providerUserId = providerUserId }); return Redirect(continueWithUrl); }
در اینجا نحوهی اصلاح اکشن متد Callback را جهت هدایت یک کاربر جدید به صفحهی ثبت نام و تکمیل اطلاعات مورد نیاز IDP را مشاهده میکنید.
returnUrl ارسالی به اکشن متد RegisterUser، به همین اکشن متد جاری اشاره میکند. یعنی کاربر پس از تکمیل اطلاعات و اینبار نال نبودن user او، گردش کاری جاری را ادامه خواهد داد و به برنامه با این هویت جدید وارد میشود.
اتصال کاربر وارد شدهی از طریق یک IDP خارجی به اکانتی که هم اکنون در سطح IDP ما موجود است
تا اینجا اگر کاربری از طریق یک IDP خارجی به برنامه وارد شود، او را به صفحهی ثبت نام کاربر هدایت کرده و پس از دریافت اطلاعات او، اکانت خارجی او را به اکانتی جدید که در IDP خود ایجاد میکنیم، متصل خواهیم کرد. به همین جهت بار دومی که این کاربر به همین ترتیب وارد سایت میشود، دیگر صفحهی ثبت نام و تکمیل اطلاعات را مشاهده نمیکند. اما ممکن است کاربری که برای اولین بار از طریق یک IDP خارجی به سایت ما وارد شدهاست، هم اکنون دارای یک اکانت دیگری در سطح IDP ما باشد؛ در اینجا فقط اتصالی بین این دو صورت نگرفتهاست. بنابراین در این حالت بجای ایجاد یک اکانت جدید، بهتر است از همین اکانت موجود استفاده کرد و صرفا اتصال UserLogins او را تکمیل نمود.
به همین جهت ابتدا نیاز است لیست Claims بازگشتی از گوگل را بررسی کنیم:
var (user, provider, providerUserId, claims) = await FindUserFromExternalProvider(result); foreach (var claim in claims) { _logger.LogInformation($"External provider[{provider}] info-> claim:{claim.Type}, value:{claim.Value}"); }
External provider[Google] info-> claim:http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name, value:Vahid N. External provider[Google] info-> claim:http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname, value:Vahid External provider[Google] info-> claim:http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname, value:N. External provider[Google] info-> claim:urn:google:profile, value:https://plus.google.com/105013528531611201860 External provider[Google] info-> claim:http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress, value:my.name@gmail.com
[HttpGet] public async Task<IActionResult> Callback() { // ... var (user, provider, providerUserId, claims) = await FindUserFromExternalProvider(result); if (user == null) { // user wasn't found by provider, but maybe one exists with the same email address? if (provider == "Google") { // email claim from Google var email = claims.FirstOrDefault(c => c.Type == "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress"); if (email != null) { var userByEmail = await _usersService.GetUserByEmailAsync(email.Value); if (userByEmail != null) { // add Google as a provider for this user await _usersService.AddUserLoginAsync(userByEmail.SubjectId, provider, providerUserId); // redirect to ExternalLoginCallback var continueWithUrlAfterAddingUserLogin = Url.Action("Callback", new {returnUrl = returnUrl}); return Redirect(continueWithUrlAfterAddingUserLogin); } } } var returnUrlAfterRegistration = Url.Action("Callback", new {returnUrl = returnUrl}); var continueWithUrl = Url.Action("RegisterUser", "UserRegistration", new {returnUrl = returnUrlAfterRegistration, provider = provider, providerUserId = providerUserId}); return Redirect(continueWithUrl); }
کدهای کامل این قسمت را از اینجا میتوانید دریافت کنید.
برای اجرای برنامه:
- ابتدا به پوشهی src\WebApi\ImageGallery.WebApi.WebApp وارد شده و dotnet_run.bat آنرا اجرا کنید تا WebAPI برنامه راه اندازی شود.
- سپس به پوشهی src\IDP\DNT.IDP مراجعه کرده و و dotnet_run.bat آنرا اجرا کنید تا برنامهی IDP راه اندازی شود.
- در آخر به پوشهی src\MvcClient\ImageGallery.MvcClient.WebApp وارد شده و dotnet_run.bat آنرا اجرا کنید تا MVC Client راه اندازی شود.
اکنون که هر سه برنامه در حال اجرا هستند، مرورگر را گشوده و مسیر https://localhost:5001 را درخواست کنید. در صفحهی login نام کاربری را User 1 و کلمهی عبور آنرا password وارد کنید.