روش های بهتر برای ساخت بانک اطلاعاتی
- پس از خداحافظی با شرکتی که در آن کار میکردی، شخصی با پوزخند به شما میگوید که «میدونستی در برنامهی حق و دستمزد شما، بچههای ادمین شبکه، دیتابیس برنامه رو مستقیما دستکاری میکردند و تعداد ساعات کاری بیشتری رو وارد میکردند»؟!
- مسئول فروشی/مسئول پذیرشی که یاد گرفته چطور به صورت مستقیم به بانک اطلاعاتی دسترسی پیدا کند و آمار فروش/پذیرش روز خودش را در بانک اطلاعاتی، با دستکاری مستقیم و خارج از برنامه، کمتر از مقدار واقعی نمایش دهد.
- باز هم مدیر سیستمی/شبکهای که دسترسی مستقیم به بانک اطلاعاتی دارد، در ساعاتی مشخص، کلمهی عبور هش شدهی خودش را مستقیما، بجای کلمهی عبور ادمین برنامه در بانک اطلاعاتی وارد کرده و پس از آن ...
این موارد متاسفانه واقعی هستند! اکنون سؤال اینجا است که آیا برنامهی شما قادر است تشخیص دهد رکوردهایی که هم اکنون در بانک اطلاعاتی ثبت شدهاند، واقعا توسط برنامه و تمام سطوح دسترسی که برای آن طراحی کردهاید، به این شکل درآمدهاند، یا اینکه توسط اشخاصی به صورت مستقیم و با دور زدن کامل برنامه، از طریق management studioهای مختلف، در سیستم وارد و دستکاری شدهاند؟! در ادامه راه حلی را برای بررسی این مشکل مهم، مرور خواهیم کرد.
چگونه تغییرات رکوردها را در بانکهای اطلاعاتی ردیابی کنیم؟
روش متداولی که برای بررسی تغییرات رکوردها مورد استفاده قرار میگیرد، هش کردن تمام اطلاعات یک ردیف از جدول است و سپس مقایسهی این هشها با هم. علت استفادهی از الگوریتمهای هش نیز، حداقل به دو علت است:
- با تغییر حتی یک بیت از اطلاعات، مقدار هش تولید شده تغییر میکند.
- طول نهایی مقدار هش شدهی اطلاعاتی حجیم، بسیار کم است و به راحتی توسط بانکهای اطلاعاتی، قابل مدیریت و جستجو است.
اگر از SQL Server استفاده میکنید، یک چنین قابلیتی را به صورت توکار به همراه دارد:
SELECT [Id], (SELECT top 1 * FROM [AppUsers] FOR XML auto), HASHBYTES ('SHA2_256', (SELECT top 1 * FROM [AppUsers] FOR XML auto)) AS [hash] -- varbinary(n), since 2012 FROM [AppUsers]
کاری که این کوئری انجام میدهد شامل دو مرحله است:
الف) کوئری "SELECT top 1 * FROM [AppUsers] FOR XML auto" کاری شبیه به serialization را انجام میدهد. همانطور که مشاهده میکنید، نام و مقادیر تمام فیلدهای یک ردیف را به صورت یک خروجی XML در میآورد. بنابراین دیگر نیازی نیست تا کار تبدیل مقادیر تمام ستونهای یک ردیف را به عبارتی قابل هش، به صورت دستی انجام دهیم؛ رشتهی XML ای آن هم اکنون آمادهاست.
ب) متد HASHBYTES، این خروجی serialized را با الگوریتم SHA2_256، هش میکند. الگوریتمهای SHA2_256 و همچنین SHA2_512، از سال 2012 به بعد به SQL Server اضافه شدهاند.
اکنون اگر این هش را به نحوی ذخیره کنیم (برنامه باید این هش را ذخیره و یا به روز رسانی کند) و سپس شخصی به صورت مستقیم ردیف فوق را در بانک اطلاعاتی تغییر دهد، هش جدید این ردیف، با هش قبلی ذخیره شدهی توسط برنامه، یکی نخواهد بود که بیانگر دستکاری مستقیم این ردیف، خارج از برنامه و با دور زدن کامل تمام سطوح دسترسی آن است.
چگونه تغییرات رکوردها را در بانکهای اطلاعاتی، توسط EF Core ردیابی کنیم؟
مزیت روش فوق، توکار بودن آن است که کارآیی فوق العادهای را نیز به همراه دارد. اما چون در ادامه قصد داریم از یک ORM استفاده کنیم و ORMها نیز قرار است توانایی کار کردن با انواع و اقسام بانکهای اطلاعاتی را داشته باشند، دو مرحلهی serialization و هش کردن را در کدهای برنامه و با مدیریت EF Core، مستقل از بانک اطلاعاتی خاصی، انجام خواهیم داد.
معرفی موجودیتهای برنامه
در مثالی که بررسی خواهیم کرد، دو موجودیت Blog و Post تعریف شدهاند:
using System.Collections.Generic; namespace EFCoreRowIntegrity { public interface IAuditableEntity { string Hash { set; get; } } public static class AuditableShadowProperties { public static readonly string CreatedDateTime = nameof(CreatedDateTime); public static readonly string ModifiedDateTime = nameof(ModifiedDateTime); } public class Blog : IAuditableEntity { public int BlogId { get; set; } public string Url { get; set; } public List<Post> Posts { get; set; } public string Hash { get; set; } } public class Post : IAuditableEntity { public int PostId { get; set; } public string Title { get; set; } public string Content { get; set; } public int BlogId { get; set; } public Blog Blog { get; set; } public string Hash { get; set; } } }
- به علاوه جهت تکمیل بحث، دو خاصیت سایهای نیز تعریف شدهاند تا بررسی کنیم که آیا هش اینها نیز درست محاسبه میشود یا خیر.
- علت اینکه خاصیت Hash، سایهای تعریف نشد، سهولت دسترسی و بالا بردن کارآیی آن بود.
معرفی ظرفی برای نگهداری نام خواص و مقادیر متناظر با یک موجودیت
در ادامه دو کلاس AuditEntry و AuditProperty را مشاهده میکنید:
using System.Collections.Generic; using Microsoft.EntityFrameworkCore.ChangeTracking; namespace EFCoreRowIntegrity { public class AuditEntry { public EntityEntry EntityEntry { set; get; } public IList<AuditProperty> AuditProperties { set; get; } = new List<AuditProperty>(); public AuditEntry() { } public AuditEntry(EntityEntry entry) { EntityEntry = entry; } } public class AuditProperty { public string Name { set; get; } public object Value { set; get; } public bool IsTemporary { set; get; } public PropertyEntry PropertyEntry { set; get; } public AuditProperty() { } public AuditProperty(string name, object value, bool isTemporary, PropertyEntry property) { Name = name; Value = value; IsTemporary = isTemporary; PropertyEntry = property; } } }
معرفی روشی برای هش کردن مقادیر یک شیء
زمانیکه توسط سیستم Tracking، در حال کاربر بر روی موجودیتهای اضافه شده و یا ویرایش شده هستیم، میخواهیم فیلد هش آنها را نیز به صورت خودکار ویرایش و مقدار دهی کنیم. کلاس زیر، منطق ارائه دهندهی این مقدار هش را بیان میکند:
using System; using System.Collections.Generic; using System.Security.Cryptography; using System.Text; using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.ChangeTracking; using Newtonsoft.Json; namespace EFCoreRowIntegrity { public static class HashingExtensions { public static string GenerateObjectHash(this object @object) { if (@object == null) { return string.Empty; } var jsonData = JsonConvert.SerializeObject(@object, Formatting.Indented); using (var hashAlgorithm = new SHA256CryptoServiceProvider()) { var byteValue = Encoding.UTF8.GetBytes(jsonData); var byteHash = hashAlgorithm.ComputeHash(byteValue); return Convert.ToBase64String(byteHash); } } public static string GenerateEntityEntryHash(this EntityEntry entry, string propertyToIgnore) { var auditEntry = new Dictionary<string, object>(); foreach (var property in entry.Properties) { var propertyName = property.Metadata.Name; if (propertyName == propertyToIgnore) { continue; } auditEntry[propertyName] = property.CurrentValue; } return auditEntry.GenerateObjectHash(); } public static string GenerateEntityHash<TEntity>(this DbContext context, TEntity entity, string propertyToIgnore) { return context.Entry(entity).GenerateEntityEntryHash(propertyToIgnore); } } }
- نکتهی مهم: ما نمیخواهیم تمام خواص یک موجودیت را هش کنیم. برای مثال اگر موجودیتی دارای چندین رابطه با جداول دیگری بود، ما مقادیر اینها را هش نمیکنیم (چون رکوردهای متناظر با آنها در جداول خودشان میتوانند دارای فیلد هش مخصوصی باشند). بنابراین یک Dictionary را از خواص و مقادیر متناظر با آنها تشکیل داده و این Dictionary را تبدیل به JSON میکنیم.
- همچنین در این بین، مقدار خود فیلد Hash یک شیء نیز نباید در هش محاسبه شده، حضور داشته باشد. به همین جهت پارامتر propertyToIgnore را مشاهده میکنید.
معرفی Context برنامه که کار هش کردن خودکار موجودیتها را انجام میدهد
اکنون نوبت استفاده از تنظیمات انجام شدهی تا این مرحلهاست:
using System; using System.Collections.Generic; using System.IO; using System.Linq; using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.ChangeTracking; using Microsoft.Extensions.Logging; namespace EFCoreRowIntegrity { public class BloggingContext : DbContext { public BloggingContext() { } public BloggingContext(DbContextOptions options) : base(options) { } public DbSet<Blog> Blogs { get; set; } public DbSet<Post> Posts { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { optionsBuilder.EnableSensitiveDataLogging(); var path = Path.Combine(Directory.GetCurrentDirectory(), "app_data", "EFCore.RowIntegrity.mdf"); optionsBuilder.UseSqlServer($"Server=(localdb)\\mssqllocaldb;Database=EFCore.RowIntegrity;AttachDbFilename={path};Trusted_Connection=True;"); optionsBuilder.UseLoggerFactory(new LoggerFactory().AddConsole((message, logLevel) => logLevel == LogLevel.Debug && message.StartsWith("Microsoft.EntityFrameworkCore.Database.Command"))); } } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); foreach (var entityType in modelBuilder.Model .GetEntityTypes() .Where(e => typeof(IAuditableEntity) .IsAssignableFrom(e.ClrType))) { modelBuilder.Entity(entityType.ClrType) .Property<DateTimeOffset?>(AuditableShadowProperties.CreatedDateTime); modelBuilder.Entity(entityType.ClrType) .Property<DateTimeOffset?>(AuditableShadowProperties.ModifiedDateTime); } } public override int SaveChanges() { var auditEntries = OnBeforeSaveChanges(); var result = base.SaveChanges(); OnAfterSaveChanges(auditEntries); return result; } private IList<AuditEntry> OnBeforeSaveChanges() { var auditEntries = new List<AuditEntry>(); foreach (var entry in ChangeTracker.Entries<IAuditableEntity>()) { if (entry.State == EntityState.Detached || entry.State == EntityState.Unchanged) { continue; } var auditEntry = new AuditEntry(entry); auditEntries.Add(auditEntry); var now = DateTimeOffset.UtcNow; foreach (var property in entry.Properties) { var propertyName = property.Metadata.Name; if (propertyName == nameof(IAuditableEntity.Hash)) { continue; } if (property.IsTemporary) { // It's an auto-generated value and should be retrieved from the DB after calling the base.SaveChanges(). auditEntry.AuditProperties.Add(new AuditProperty(propertyName, null, true, property)); continue; } switch (entry.State) { case EntityState.Added: entry.Property(AuditableShadowProperties.CreatedDateTime).CurrentValue = now; auditEntry.AuditProperties.Add(new AuditProperty(propertyName, property.CurrentValue, false, property)); break; case EntityState.Modified: auditEntry.AuditProperties.Add(new AuditProperty(propertyName, property.CurrentValue, false, property)); entry.Property(AuditableShadowProperties.ModifiedDateTime).CurrentValue = now; break; } } } return auditEntries; } private void OnAfterSaveChanges(IList<AuditEntry> auditEntries) { foreach (var auditEntry in auditEntries) { foreach (var auditProperty in auditEntry.AuditProperties.Where(x => x.IsTemporary)) { // Now we have the auto-generated value from the DB. auditProperty.Value = auditProperty.PropertyEntry.CurrentValue; auditProperty.IsTemporary = false; } auditEntry.EntityEntry.Property(nameof(IAuditableEntity.Hash)).CurrentValue = auditEntry.AuditProperties.ToDictionary(x => x.Name, x => x.Value).GenerateObjectHash(); } base.SaveChanges(); } } }
public override int SaveChanges() { var auditEntries = OnBeforeSaveChanges(); var result = base.SaveChanges(); OnAfterSaveChanges(auditEntries); return result; }
if (property.IsTemporary) { // It's an auto-generated value and should be retrieved from the DB after calling the base.SaveChanges(). auditEntry.AuditProperties.Add(new AuditProperty(propertyName, null, true, property)); continue; }
همین مقدار تنظیم، برای محاسبه و به روز رسانی خودکار فیلد هش، کفایت میکند.
روش بررسی اصالت یک موجودیت
در متد زیر، روش محاسبهی هش واقعی یک موجودیت دریافت شدهی از بانک اطلاعاتی را توسط متد الحاقی GenerateEntityHash مشاهده میکنید. اگر این هش واقعی (بر اساس مقادیر فعلی این ردیف که حتی ممکن است به صورت دستی و خارج از برنامه تغییر کرده باشد)، با مقدار Hash ثبت شدهی پیشین در آن ردیف یکی بود، اصالت این ردیف تائید خواهد شد:
private static void CheckRow1IsAuthentic() { using (var context = new BloggingContext()) { var blog1 = context.Blogs.Single(x => x.BlogId == 1); var entityHash = context.GenerateEntityHash(blog1, propertyToIgnore: nameof(IAuditableEntity.Hash)); var dbRowHash = blog1.Hash; Console.WriteLine($"entityHash: {entityHash}\ndbRowHash: {dbRowHash}"); if (entityHash == dbRowHash) { Console.WriteLine("This row is authentic!"); } else { Console.WriteLine("This row is tampered outside of the application!"); } } }
entityHash: P110cYquWpoaZuTpCWaqBn6HPSGdoQdmaAN05s1zYqo= dbRowHash: P110cYquWpoaZuTpCWaqBn6HPSGdoQdmaAN05s1zYqo= This row is authentic!
اکنون بانک اطلاعاتی را خارج از برنامه، مستقیما دستکاری میکنیم و برای مثال Url اولین ردیف را تغییر میدهیم:
در ادامه یکبار دیگر برنامه را اجرا خواهیم کرد:
entityHash: tdiZhKMJRnROGLLam1WpldA0fy/CbjJaR2Y2jNU9izk= dbRowHash: P110cYquWpoaZuTpCWaqBn6HPSGdoQdmaAN05s1zYqo= This row is tampered outside of the application!
به علاوه باید درنظر داشت، محاسبهی این هش بدون خود برنامه، کار سادهای نیست. به همین جهت به روز رسانی دستی آن تقریبا غیرممکن است؛ خصوصا اگر متد GenerateObjectHash، کمی با پیچ و تاب بیشتری نیز تهیه شود.
چگونه وضعیت اصالت تعدادی ردیف را بررسی کنیم؟
مثال قبل، در مورد روش بررسی اصالت یک تک ردیف بود. کوئری زیر روش محاسبهی فیلد جدید IsAuthentic را در بین لیستی از ردیفها نمایش میدهد:
var blogs = (from blog in context.Blogs.ToList() // Note: this `ToList()` is necessary here for having Shadow properties values, otherwise they will considered `null`. let computedHash = context.GenerateEntityHash(blog, nameof(IAuditableEntity.Hash)) select new { blog.BlogId, blog.Url, RowHash = blog.Hash, ComputedHash = computedHash, IsAuthentic = blog.Hash == computedHash }).ToList();
کدهای کامل این مثال را از اینجا میتوانید دریافت کنید: EFCoreRowIntegrity.zip
معرفی #F
#F یک زبان برنامه نویسی تابع گرا است و گزینه ای بسیار مناسب برای حل مسایل کامپیوتری. اما استفاده از زبان برنامه نویسی تابعی محض برای نوشتن و تولید پروژههای نرم افزاری مناسب نمیباشد. به همین دلیل نیاز به استفاده از این زبانها در کنار سایر زبانهای شی گرا احساس میشود. #F یک زبان همه منظوره دات نت است که برای حالت اجرا به صورت همه منظوره استفاده میشود. برخی زبانهای تابع گرا دیگر نظیر Lisp و Haskel و OCaml (که #F بسیار نزدیک به این زبان میباشد) با دستورات زبان اجرای سفارشی کار میکنند و این مسئله باعث نبود زبان برنامه نویسی چند فعالیته میشود. شما میتوانید از برنامه نویسی توصیفی هم استفاده کنید و توابع را به راحتی با هم ترکیب کنید و یا روشهای شی گرایی و دستوری را در همان برنامه استفاده کنید.
تاریخچه
#F توسط دکتر دون سیم ابداع شد. در حال حاضر #F وابسته به تیمی کوچک ولی پیشرفته واقع در مرکز تحقیقات شرکت مایکروسافت میباشد. #Fمدل خود را از روی زبان برنامه نویسی OCAML انتخاب کرد و سپس با گسترش قابلیتهای فنی، خود را در دات نت گنجاند. #F در بسیاری از برنامههای بزرگ دنیای واقعی استفاده شده است که این خود نمایانگر آکادمیک نبودن محض این زبان است. با توجه به اینکه زبان تابع گرای دیگر به ندرت در دات نت توسعه پیدا کرده است #F به عنوان استاندارد در این مقوله در آمده است. زبان #F از نظر کیفیت و سازگار بودن با دات نت و VisualStudio بسیار وضعیت بهتری نسبت به رقبای خود دارد و این خود دلیلی دیگری است برای انتخاب این زبان.
استفاده در دات نت
#F کاملا از دات نت پشتیبانی میکند و این قابلیت را به برنامه نویسان میدهد که هر چیزی را که در سایر زبانهای دات نت استفاده میکنند در این زبان نیز قابل استفاده باشد. همچنین میتواند برای کد نویسی IL نیز استفاده شود.
#F به راحتی قابل اجرا در محیط لینوکس و مکینتاش نیز است.
استفاده کنندگان #F
#F در شرکت مایکرو سافت به شدت استفاده میشود. رالف هربریش که یکی از مدیران دوگانه گروه بازیهای مایکروسافت و از متخصصین آموزش ماشین است در این باره میگوید:
*اولین برنامه کاربردی برای انتقال 110 گیگا بایت از طریق 11000 فایل متنی در بیش از 300 دایرکتوری و وارد کردن آنها در دیتابیس بود. کل برنامه 90 خط بود و در کمتر از 18 ساعت توانست اطلاعات مربوطه را در SQL ذخیره کند. یعنی ده هزار خط برنامه متنی در هر ثانیه مورد پردازش قرار گرفت.همچنین توجه کنید که من برنامه را بهینه نکردم بلکه به صورت کاملا عادی نوشتم. این جواب بسیار قابل توجه بود زیرا من انتظار داشتم حداقل یک هفته زمان ببرد.
دومین برنامه، برنامه پردازش میلیونها Feekback مشتریان بود. ما روابط مدلی زیادی را توسعه دادیم و من این روابط را در #F قرار دادم و دادههای مربوط به SQL را در آن فراخوانی کردم و نتایج را در فایل داده ای MATLAB قرار دادم و کل پروژه در حد صد خط بود به همراه توضیحات. زمان اجرای پروژه برای دریافت خروجی ده دقیقه بود در حالی که همین کار را توسط برنامه #C قبلا توسعه داده بودیم که بیش از هزار خط بود و نزدیک به دو روز زمان میبرد.*
Derivative One که یک شرکت بزرگ در تولید نرم افزارهای شبیه ساز مالی است مدلهای مالی نرم افزارهای خود را در #F پیاده سازی کرده است.
چرا #F ؟
همیشه باید دلیلی برای انتخاب یک زبان باشد. در حال حاضر #F یکی از قدرتمندترین زبانهای برنامه نویسی است. در ذیل به چند تا از این دلایل اشاره خواهم کرد:
- #F یک زبان استنباطی است. برای مثال در هنگام تعریف متغیر و شناسه نیاز به ذکر نوع آن نیست. کامپایلر با توجه به مقدار اولیه تصمیم میگیرد که متغیر از چه نوعی است.
- بسیار راحت میتوان به کتابخانه قدرتمند دات نت دسترسی داشت و از آنها در پروژههای خود استفاده کنید.
- #F از انواع روشهای برنامه نویسی نظیر تابعی، موازی، شی گرا و دستوری پیشتیبانی میکند.
- برخلاف تصور بعضی افراد، در #F امکان تهیه و توسعه پروژههای وب و ویندوز و حتی WPF و Silverlight هم وجود دارد.
- نوع کدنویسی و syntax زبان #F به برنامه نویسان این اجازه را میدهد که الگوریتمهای پیچیده مورد نظر خود را بسیار راحتتر پیاده سازی کنند. به همین دلیل بعضی برنامه نویسان این زبان را با Paython مقایسه میکنند.
- #F به راحتی با زبان #C و VB تعامل دارد. یعنی میتونیم در طی روند تولید پروژه از قدرتهای هر سه زبان بهره بگیریم.
- طبق آمار گرفته شده از برنامه نویسان، #F به دلیل پشتیبانی از نوع داده ای قوی و مبحث Unit Measure، خطاها و Bugهای نرم افزار را کاهش میدهد.
- به دلیل پشتیبانی VS.Net از زبان #F و وجود ابزار قدرتمند برای توسعه نرم افزار به کمک این زبان (unitTesting و ابزارهای debuging و ..)این زبان تبدیل به قدرتهای دنیای برنامه نویسی شده است.
- #F یک زبان بسیار مناسب برای پیاده سازی الگوریتمهای data-mining است.
- #F از immutability در تعریف شناسهها پشتیبانی میکند.(در فصلهای مربوطه بحث خواهد شد)
- و.....
چرا #F نه ؟
#F هم مانند سایر زبان ها، علاوه بر قدرت بی همتای خود دارای معایبی نیز میباشد. (مواردی که در پایین ذکر میشود صرفا بر اساس تجربه است نه مستندات).
- نوع کدنویسی و syntax زبان #F برای برنامه نویسان دات بیگانه ( و البته کمی آزار دهنده) است. اما به مرور این مشکل، تبدیل به قدرت برای مانورهای مختلف در کد میشود.
- درست است که در #F امکان تعریف اینترفیس وجود دارد و یک کلاس میتواند اینترفیس مورد نظر را پیاده سازی کند ولی هنگام فراخوانی متدهای کلاس (اون هایی که مربوط به اینترفیس است) حتما باید instance کلاس مربوطه به اینترفیس cast شود و این کمی آزار دهنده است.(در فصل شی گرایی در این مورد شرح داده شده است).
- زبان #F در حال حاضر توسط VS.Net به صورت Visual پشتیبانی نمیشود.(امکاناتی نظیر drag drop کنترلها برای ساخت فرم و ....). البته برای حل این مشکل نیز افزونه هایی وجود دارد که در جای مناسب بحث خواهیم کرد.
آیا برای یادگیری #F نیاز به داشتن دانش در برنامه نویسی #C یا VBداریم؟
به طور قطع نه. نوع کد نویسی (نه مفاهیم)در #F کاملا متفاوت در #C است و این دو زبان از نظر کد نویسی شباهتشان در حد صفر است. برای یادگیری #F بیشتر نیاز به داشتن آگاهی اولیه در برنامه نویسی (آشنایی با تابع، حلقه تکرار، متغیر ها) و شی گرایی(مفاهیم کلاس، اینترفیس، خواص، متدها و...) دارید تا آشنایی با #C یا VB.
چگونه شروع کنیم؟
اولین گام برای یادگیری آشنایی با نحوه کد نویسی #F است. بدین منظور در طی فصول آموزش سعی بر این شده است از مثالهای بسیار زیاد برای درک بهتر مفاهیم استفاده کنم. تا جای ممکن برای اینکه تکرار مکررات نشود و شما خواننده عزیز به خاطر مطالب واضح و روشن خسته نشوید از تشریح مباحث واضح خودداری کردم و بیشتر به پیاده سازی مثال اکتفا نمودم.
Stored Procedure چیست ؟
Stored Procedure یا SP یا به زبان فارسی «رویههای ذخیره شده» اشیایی اجرا پذیر در بانک اطلاعاتی SQL Server هستند که شامل یک یا چندین دستور SQL میشوند. این رویهها میتوانند پارامترهای ورودی و خروجی داشته باشند؛ همچنین میتوانند لیستی از موجودیتها را نیز برگردانند و یا میتوان داخل این رویهها به زبان T-SQL برنامه نویسی کرد.
مهمترین کاربر این رویهها، ذخیره کردن دستورات Select , Insert , Update , Delete هست و یا ترکیبی از اینها .
اشکال راه حلهای پیش فرض مبتنی بر Context
برای استفاده از راه حلهای پیش فرض مبتنی بر Context، همانطور که در مقاله «استفاده از امکانات بومی بانکهای اطلاعاتی» به آن پرداخته شده، سه روش کلی برای استفاده از Stored Procedure پیشنهاد شدهاست:
- روش اول استفاده از متد fromsql است. اشکال این متد، محدودیت استفاده برای یک موجودیت برنامه است و به زبان ساده نمیتوان در کوئری پایگاه داده از join استفاده کرد.
- روش دوم استفاده از متد ExecuteSqlCommand موجود در context برنامه است . اشکال این متد void بودن آن است که باعث میشود بازگشتی از پایگاه داده حاصل نشود.
- روش سوم استفاده از متد ExecuteScalar موجود در Context برنامه است. اشکالی که به این متد گرفته میشود، Scalar بودن مقدار بازگشتی از آن است که باعث میشود نتوانیم لیستی از موجودیتها را به ViewModel مورد نظر نگاشت کنیم.
راه حل این مشکل
برای حل این مشکلات که بسیار هم مهم هستند، اول باید قطعه کد زیر را به Context برنامه اضافه نمود:
public void OpenConnection() { Database.OpenConnection(); } public DbCommand Command() { DbCommand cmd = Database.GetDbConnection().CreateCommand(); return cmd; }
void OpenConnection(); DbCommand Command();
public void ConfigureServices(IServiceCollection services) { services.AddScoped<IUnitOfWork, ApplicationDbContext>(); services.AddScoped<ISpReader, SpReader>(); }
public List<ViewModel> GetFromSp <ViewModel>(string[,] Parametr, string NameSp) where ViewModel : new()
{ _uow.OpenConnection(); DbCommand cmd = _uow.Command(); cmd.CommandText = NameSp; cmd.CommandType = CommandType.StoredProcedure; var countParametr = Parametr.GetLength(0); for (int i = 0; i < countParame tr; i++) { cmd.Parameters.Add(new SqlParameter { ParameterName = Parametr[i, 0], Value = Parametr[i, 1] }); } List<ViewModel> list = new List<ViewModel >(); using (var reader = cmd.ExecuteReader()) { if (reader != null && reader.HasRows) { var entity = typeof(ViewModel); var propDict = new Dictionary<string, PropertyInfo>(); var props = entity.GetProperties (BindingFlags.Instance | BindingFlags.Public); propDict = props.ToDictionary(p => p.Name.ToUpper(), p => p); while (reader.Read()) { ViewModel newobject = new ViewModel (); for (int index = 0; index < reader.FieldCount; index++) { if (propDict.ContainsKey(reader.GetName(index).ToUpper())) { var info = propDict[reader.GetName(index).ToUpper()]; if ((info != null) && info.CanWrite) { var val = reader.GetValue(index); info.SetValue(newobject, (val == DBNull.Value) ? null : val, null); } } } list.Add(newobject); } } return list; }
همچنین میتوان برای استفاده این متد برای رویههای بدون پارامتر ورودی، از OverLoad این متد، با حذف قطعات کد زیر:
var countParametr = Parametr.GetLength(0); for (int i = 0; i < countParametr; i++) { cmd.Parameters.Add(new SqlParameter { ParameterName = Parametr[i, 0], Value = Parametr[i, 1] }); }
روش استفاده از این متد
برای استفاده از این متد، لازم است چند نکته رعایت شوند:
1- خروجی Stored Procedure دقیقا منطبق بر ViewModel ارسالی به متد جهت تشکیل لیست باشد.
2- لیست پارامترها باید بصورت آرایه دوبعدی باشد که اندازه بعد اول، تعداد پارامترها و اندازه بعد دوم 2 باشد.
3- در ماتریسی که از این پارامترها ساخته میشود، ستون اول نام پارامتر و ستون دوم مقدار پارامتر ست میشود.
بطور مثال Stored Procedure زیر حاوی سه پارامتر است :
CREATE PROCEDURE [dbo].[isRelation]( @TableName as varchar(50), @FieldOfRelation as varchar(70), @ValueOfField as int)
public class EntityServices : IEntityService { private ISpreader _Reader; public EntityServices( ISpreader reader) { _Reader = reader; } public List<StoreProcedureResultViewModel> IsRelation(string tableName , int keyValue, string keyFieldName) { List<StoreProcedureResultViewModel> IsContact; try { string[,] Parametr = new string[3, 2]; Parametr[0, 0] = "@TableName"; Parametr[0, 1] = tableName ; Parametr[1, 0] = "@ValueOfField"; Parametr[1, 1] = keyValue.ToString().Trim(); Parametr[2, 0] = "@FieldOfRelation"; Parametr[2, 1] = keyFieldName.Trim(); IsContact = _Reader.GetSp<StoreProcedureResultViewModel>(Parametr, "IsRelation"); return IsContact; } catch (Exception ex) { } } }
Note Taking App .Net MAUI Blazor Hybrid and Blazor WASM - Single Codebase Complete Code & UI Sharing
A Note Taking App for Mobile and Web Browser both Platforms using Single Code base with almost 100% Code Sharing (Complete Code, Logic and UI Sharing)
.Net MAUI Blazor Hybrid for Mobile App and Blazor WebAssembly (Blazor WASM) for Web Browser App sharing code using Razor Class Libraries
A step by step RealWorld app tutorial from scratch