modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
EF Code First #1
<connectionStrings> <clear/> <add name="ProductContext" connectionString="Data Source=(local);Initial Catalog=testdb2012;Integrated Security = true" providerName="System.Data.SqlClient" /> </connectionStrings>
public class ProductContext : DbContext
با این تعاریف باید برنامه کار کند (البته بر اساس نام کلاسهای برنامه شما).
ضمنا login failed به این معنا است که رشته اتصالی اشتباه تعریف شده است. رشته فوق به یک بانک اطلاعاتی sql server و به وهله پیش فرض آن اشاره میکند و از نوع windows authentication است. این موارد را باید بر اساس تنظیمات سیستم خودتون تغییر بدید.
ساخت یک Form Generator ساده در MVC
public IList<Value> GetValues(int formId) { return _values.Include(x => x.Field) .Where(value => value.FormId == formId) .ToList(); }
- اما از دیدگاه ذخیره سازی این اطلاعات پویا ... مشکل دارد. در طراحی بانک اطلاعاتی آن فرض شدهاست که برنامه مثلا فرم یک را دارد. این فرم یک، 10 فیلد پویا یا بیشتر را دارد. این 10 فیلد فقط یکبار توسط کاربر پر میشوند. اگر کاربر قرار باشد بار دوم این فرم یک را پر کند، امکان پذیر نیست. در کلاس Value آن که فقط محتوای یک فیلد را ذخیره میکند، مفهومی به نام ردیف سند جاری وجود ندارد. به ازای هر فیلد یک ردیف مجزا داریم؛ اما مشخص نیست این ردیفها متعلق به کدام سند هستند (منظور از سند، شمار منحصربفرد پر کردن فرم جاری است؛ هر کاربر یک فرم را بیش از یکبار میتواند پر کند).
برای حل این نوع مشکلات (برای اینکه به ازای مقدار هر فیلد فرم پویا، یک ردیف مجزا تولید نشود و schemaless کار کرد) یا باید از یک بانک اطلاعاتی NoSQL استفاده کرد که مثلا کل فرم را در قالب یک شیء JSON سریالایز کند و آنرا داخل یک فیلد از یک ردیف (یک سند) ذخیره کند. یا اگر با SQL Server کار میکنید، فیلد XML آن چنین قابلیتی را دارد. کل اطلاعات دریافتی فرم را تبدیل به XML کنید و سپس ذخیره. به این صورت امکان تهیه کوئری و گزارش گرفتنهای پیشرفته و بهینه را به همراه تعریف ایندکس و مسایل دیگر نیز خواهید داشت. اینبار هر ردیف بانک اطلاعاتی، مفهوم یک سند کامل را پیدا میکند؛ بجای اینکه هر ردیف فقط یک مقدار از یک فیلد باشد. همچنین در این حالت هر ردیف میتواند محتوای فرمی را ذخیره کند که با ردیف بعدی کاملا متفاوت است (بر اساس طراحی پویای متفاوت هر فرم).
برای قسمت backend، از همان برنامهی تکمیل شدهی قسمت قبل استفاده میکنیم که به آن تولید مقدماتی JWTها نیز اضافه شدهاست. البته این سری، مستقل از قسمت سمت سرور آن تهیه خواهد شد و صرفا در حد دریافت توکن از سرور و یا ارسال مشخصات کاربر جهت لاگین، نیاز بیشتری به قسمت سمت سرور آن ندارد و تاکید آن بر روی مباحث سمت کلاینت React است. بنابراین اینکه چگونه این توکن را تولید میکنید، در اینجا اهمیتی ندارد و کلیات آن با تمام روشهای پیاده سازی سمت سرور سازگار است (و مختص به فناوری خاصی نیست). پیشنیاز درک کدهای سمت سرور قسمت JWT آن، مطالب زیر هستند:
- «معرفی JSON Web Token»
- «اعتبارسنجی مبتنی بر JWT در ASP.NET Core 2.0 بدون استفاده از سیستم Identity»
- «پیاده سازی JSON Web Token با ASP.NET Web API 2.x»
- « آزمایش Web APIs توسط Postman - قسمت ششم - اعتبارسنجی مبتنی بر JWT»
ثبت یک کاربر جدید
فرم ثبت نام کاربران را در قسمت 21 این سری، در فایل src\components\registerForm.jsx، ایجاد و تکمیل کردیم. البته این فرم هنوز به backend server متصل نیست. برای کار با آن هم نیاز است شیءای را با ساختار زیر که ذکر سه خاصیت اول آن اجباری است، به endpoint ای با آدرس https://localhost:5001/api/Users به صورت یک HTTP Post ارسال کنیم:
{ "name": "string", "email": "string", "password": "string", "isAdmin": true, "id": 0 }
اکنون نوبت به اتصال کامپوننت registerForm.jsx، به سرویس backend است. تا اینجا دو سرویس src\services\genreService.js و src\services\movieService.js را در قسمت قبل، به برنامه جهت کار کردن با endpointهای backend server، اضافه کردیم. شبیه به همین روش را برای کار با سرویس سمت سرور api/Users نیز در پیش میگیریم. بنابراین فایل جدید src\services\userService.js را با محتوای زیر، به برنامهی frontend اضافه میکنیم:
import http from "./httpService"; import { apiUrl } from "../config.json"; const apiEndpoint = apiUrl + "/users"; export function register(user) { return http.post(apiEndpoint, { email: user.username, password: user.password, name: user.name }); }
اطلاعات شیء user ای که در اینجا دریافت میشود، از خاصیت data کامپوننت RegisterForm تامین میگردد:
class RegisterForm extends Form { state = { data: { username: "", password: "", name: "" }, errors: {} };
پس از تعریف userService.js، به registerForm.jsx بازگشته و ابتدا امکانات آنرا import میکنیم:
import * as userService from "../services/userService";
import { register } userService from "../services/userService";
doSubmit = async () => { try { await userService.register(this.state.data); } catch (ex) { if (ex.response && ex.response.status === 400) { const errors = { ...this.state.errors }; // clone an object errors.username = ex.response.data; this.setState({ errors }); } } };
در این حالت برای ارسال اطلاعات یک کاربر، در بار اول، یک چنین خروجی را از سمت سرور میتوان شاهد بود که id جدیدی را به این رکورد نسبت دادهاست:
اگر مجددا همین رکورد را به سمت سرور ارسال کنیم، اینبار خطای زیر را دریافت خواهیم کرد:
که از نوع 400 یا همان BadRequest است:
بنابراین نیاز است بدنهی response را در یک چنین مواردی که خطایی از سمت سرور صادر میشود، دریافت کرده و با به روز رسانی خاصیت errors در state فرم (همان قسمت بدنهی catch کدهای فوق)، سبب درج و نمایش خودکار این خطا شویم:
پیشتر در قسمت بررسی «کار با فرمها» آموختیم که برای مدیریت خطاهای پیش بینی شدهی دریافتی از سمت سرور، نیاز است قطعه کدهای مرتبط با سرویس http را در بدنهی try/catchها محصور کنیم. برای مثال در اینجا اگر ایمیل شخصی تکراری وارد شود، سرویس یک return BadRequest("Can't create the requested record.") را بازگشت میدهد که در اینجا status code معادل BadRequest، همان 400 است. بنابراین انتظار داریم که خطای 400 را از سمت سرور، تحت شرایط خاصی دریافت کنیم. به همین دلیل است که در اینجا تنها مدیریت status code=400 را در بدنهی catch نوشته شده ملاحظه میکنید.
سپس برای نمایش آن، نیاز است خاصیت متناظری را که این خطا به آن مرتبط میشود، با پیام دریافت شدهی از سمت سرور، مقدار دهی کنیم که در اینجا میدانیم مرتبط با username است. به همین جهت سطر errors.username = ex.response.data، کار انتساب بدنهی response را به خاصیت جدید errors.username انجام میدهد. در این حالت اگر به کمک متد setState، کار به روز رسانی خاصیت errors موجود در state را انجام دهیم، رندر مجدد فرم، در صف انجام قرار گرفته و در رندر بعدی آن، پیام موجود در errors.username، نمایش داده میشود.
پیاده سازی ورود به سیستم
فرم ورود به سیستم را در قسمت 18 این سری، در فایل src\components\loginForm.jsx، ایجاد و تکمیل کردیم که این فرم نیز هنوز به backend server متصل نیست. برای کار با آن نیاز است شیءای را با ساختار زیر که ذکر هر دو خاصیت آن اجباری است، به endpoint ای با آدرس https://localhost:5001/api/Auth/Login به صورت یک HTTP Post ارسال کنیم:
{ "email": "string", "password": "string" }
var jwt = _tokenFactoryService.CreateAccessToken(user); return Ok(new { access_token = jwt });
در ادامه برای تعامل با منبع api/Auth/Login سمت سرور، ابتدا یک سرویس مختص آنرا در فایل جدید src\services\authService.js، با محتوای زیر ایجاد میکنیم:
import { apiUrl } from "../config.json"; import http from "./httpService"; const apiEndpoint = apiUrl + "/auth"; export function login(email, password) { return http.post(apiEndpoint + "/login", { email, password }); }
import * as auth from "../services/authService"; class LoginForm extends Form { state = { data: { username: "", password: "" }, errors: {} }; // ... doSubmit = async () => { try { const { data } = this.state; const { data: { access_token } } = await auth.login(data.username, data.password); console.log("JWT", access_token); localStorage.setItem("token", access_token); this.props.history.push("/"); } catch (ex) { if (ex.response && ex.response.status === 400) { const errors = { ...this.state.errors }; errors.username = ex.response.data; this.setState({ errors }); } } };
- ابتدا تمام خروجیهای ماژول authService را با نام شیء auth دریافت کردهایم.
- سپس در متد doSubmit، اطلاعات خاصیت data موجود در state را که معادل فیلدهای فرم لاگین هستند، به متد auth.login برای انجام لاگین سمت سرور، ارسال کردهایم. این متد چون یک Promise را باز میگرداند، باید await شود و پس از آن متد جاری نیز باید به صورت async معرفی گردد.
- همانطور که عنوان شد، خروجی نهایی متد auth.login، یک شیء JSON دارای خاصیت access_token است که در اینجا از خاصیت data خروجی نهایی دریافت شدهاست.
- سپس نیاز است برای استفادههای آتی، این token دریافتی از سرور را در جایی ذخیره کرد. یکی از مکانهای متداول اینکار، local storage مرورگرها است (اطلاعات بیشتر).
- در آخر کاربر را توسط شیء history سیستم مسیریابی برنامه، به صفحهی اصلی آن هدایت میکنیم.
- در اینجا قسمت catch نیز ذکر شدهاست تا خطاهای حاصل از return BadRequestهای دریافتی از سمت سرور را بتوان ذیل فیلد نام کاربری نمایش داد. روش کار آن، دقیقا همانند روشی است که برای فرم ثبت یک کاربر جدید استفاده کردیم.
اکنون اگر برنامه را ذخیره کرده و اجرا کنیم، توکن دریافتی، در کنسول توسعه دهندگان مرورگر لاگ شده و سپس کاربر به صفحهی اصلی برنامه هدایت میشود. همچنین این token ذخیره شده را میتوان در ذیل قسمت application->storage آن نیز مشاهده کرد:
لاگین خودکار کاربر، پس از ثبت نام در سایت
پس از ثبت نام یک کاربر در سایت، بدنهی response بازگشت داده شدهی از سمت سرور، همان شیء user است که اکنون Id او مشخص شدهاست. بنابراین اینبار جهت ارائهی token از سمت سرور، بجای response body، از یک هدر سفارشی در فایل Controllers\UsersController.cs استفاده میکنیم (کدهای کامل آن در انتهای بحث پیوست شدهاست):
var jwt = _tokenFactoryService.CreateAccessToken(user); this.Response.Headers.Add("x-auth-token", jwt);
در ادامه در کدهای سمت کلاینت src\components\registerForm.jsx، برای استخراج این هدر سفارشی، اگر شیء response دریافتی از سرور را لاگ کنیم:
const response = await userService.register(this.state.data); console.log(response);
برای اینکه در کدهای سمت کلاینت بتوان این هدر سفارشی را خواند، نیاز است هدر مخصوص access-control-expose-headers را نیز به response اضافه کرد:
var jwt = _tokenFactoryService.CreateAccessToken(data); this.Response.Headers.Add("x-auth-token", jwt); this.Response.Headers.Add("access-control-expose-headers", "x-auth-token");
اکنون میتوان این هدر سفارشی را در متد doSubmit کامپوننت RegisterForm، از طریق شیء response.headers خواند و در localStorage ذخیره کرد. سپس کاربر را توسط شیء history سیستم مسیریابی، به ریشهی سایت هدایت نمود:
class RegisterForm extends Form { // ... doSubmit = async () => { try { const response = await userService.register(this.state.data); console.log(response); localStorage.setItem("token", response.headers["x-auth-token"]); this.props.history.push("/"); } catch (ex) { if (ex.response && ex.response.status === 400) { const errors = { ...this.state.errors }; // clone an object errors.username = ex.response.data; this.setState({ errors }); } } };
کدهای کامل این قسمت را از اینجا میتوانید دریافت کنید: sample-26-backend.zip و sample-26-frontend.zip
کار با کلیدهای اصلی و خارجی در EF Code first
بنابراین باید Context را بررسی کرد که Account در حال اضافه شدن، آیا قبلا در کش Local آن وجود خارجی داشته یا خیر. به این صورت:
using (var ctx = new MyContext()) { foreach (var dummyBill in DummyBills()) { var account = dummyBill.Account; var entry = ctx.Entry<Account>(account); if (entry.State == EntityState.Detached) { var attachedEntity = ctx.Accounts.Local.SingleOrDefault(e => e.Id == account.Id); if (attachedEntity != null) { // یعنی قبلا تحت نظر زمینه جاری قرار گرفته و نیازی به ثبت مجدد آن نیست account = attachedEntity; } } var bl = new Bill { Id = dummyBill.Id, Amount = dummyBill.Amount, Account = account }; ctx.Bills.Add(bl); } ctx.SaveChanges(); }
از SQL Server 2008 به بعد، نوع داده جدیدی به نام geography به نوعهای قابل تعریف ستونها اضافه شدهاست. در این نوع ستونها میتوان طول و عرض جغرافیایی یک نقطه را ذخیره کرد و سپس به کمک توابع توکاری از آنها کوئری گرفت.
در اینجا نمونهای از نحوهی تعریف و همچنین مقدار دهی این نوع ستونها را مشاهده میکنید:
CREATE TABLE [Geo]( [id] [int] IDENTITY(1,1) NOT NULL, [Location] [geography] NULL ) insert into Geo( Location , long, lat ) values ( geography::STGeomFromText ('POINT(-121.527200 45.712113)', 4326))
در اینجا متدهای توکار دیگری مانند geography::STDistance برای یافتن فاصله مستقیم بین نقاط نیز ارائه شدهاند. خروجی آن بر حسب متر است.
پشتیبانی از Spatial Data در Entity framework
پشتیبانی از نوع مخصوص geography، در EF 5 توسط نوع دادهای DbGeography ارائه شد. این نوع دادهای immutable است. به این معنا که پس از نمونه سازی، دیگر مقدار آن قابل تغییر نیست.
در اینجا برای نمونه مدلی را مشاهده میکنید که از نوع دادهای DbGeography استفاده میکند:
using System.Data.Entity.Spatial; namespace EFGeoTests.Models { public class GeoLocation { public int Id { get; set; } public DbGeography Location { get; set; } public string Name { get; set; } public string Type { get; set; } public override string ToString() { return string.Format("Name:{0}, Location:{1}", Name, Location); } } }
using System; using System.Data.Entity; using EFGeoTests.Models; namespace EFGeoTests.Config { public class MyContext : DbContext { public DbSet<GeoLocation> GeoLocations { get; set; } public MyContext() : base("Connection1") { this.Database.Log = sql => Console.Write(sql); } } }
private static DbGeography createPoint(double longitude, double latitude, int coordinateSystemId = 4326) { var text = string.Format(CultureInfo.InvariantCulture.NumberFormat,"POINT({0} {1})", longitude, latitude); return DbGeography.PointFromText(text, coordinateSystemId); }
تهیه منبع دادهی جغرافیایی
برای تدارک یک مثال واقعی جغرافیایی، نیاز به اطلاعاتی دقیق داریم. این نوع اطلاعات عموما توسط یک سری فایل مخصوص به نام Shapefiles که حاوی اطلاعات برداری جغرافیایی هستند ارائه میشوند. برای نمونه اطلاعات جغرافیایی به روز ایران را از آدرس ذیل میتوانید دریافت کنید:
http://download.geofabrik.de/asia/iran.html
http://download.geofabrik.de/asia/iran-latest.shp.zip
پس از دریافت این فایل، به تعدادی فایل با پسوندهای shp، shx و dbf خواهیم رسید.
فایلهای shp بیانگر فرمت اشکال ذخیره شده هستند. فایلهای shx یک سری ایندکس بوده و فایلهای dbf از نوع بانک اطلاعاتی dBase IV میباشند.
همچنین اگر فایلهای prj را باز کنید، یک چنین اطلاعاتی در آن موجودند:
GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]
خواندن فایلهای shp در دات نت
پس از دریافت فایلهای shp و بانکهای اطلاعاتی مرتبط با اطلاعات جغرافیایی ایران، اکنون نوبت به پردازش این فایلهای مخصوص با فرمت بانک اطلاعاتی فاکس پرو مانند، رسیدهاست. برای این منظور میتوان از پروژهی سورس باز ذیل استفاده کرد:
این پروژه در خواندن فایلهای shp بدون نقص عمل میکند اما توانایی خواندن نامهای فارسی وارد شده در این نوع بانکهای اطلاعاتی را ندارد. برای رفع این مشکل، سورس آن را از Codeplex دریافت کنید. سپس فایل Shapefile.cs را گشوده و ابتدای خاصیت Current آنرا به نحو ذیل تغییر دهید:
/// <summary> /// Gets the current shape in the collection /// </summary> public Shape Current { get { if (_disposed) throw new ObjectDisposedException("Shapefile"); if (!_opened) throw new InvalidOperationException("Shapefile not open."); // get the metadata StringDictionary metadata = null; if (!RawMetadataOnly) { metadata = new StringDictionary(); for (int i = 0; i < _dbReader.FieldCount; i++) { string value = _dbReader.GetValue(i).ToString(); if (_dbReader.GetDataTypeName(i) == "DBTYPE_WVARCHAR") { // برای نمایش متون فارسی نیاز است value = Encoding.UTF8.GetString(Encoding.GetEncoding(720).GetBytes(value)); } metadata.Add(_dbReader.GetName(i), value); } }
using System.Collections.Generic; using System.Linq; using Catfood.Shapefile; namespace EFGeoTests { public class MapPoint { public Dictionary<string, string> Metadata { set; get; } public double X { set; get; } public double Y { set; get; } } public static class ShapeReader { public static IList<MapPoint> ReadShapeFile(string path) { var results = new List<MapPoint>(); using (var shapefile = new Shapefile(path)) { foreach (var shape in shapefile) { if (shape.Type != ShapeType.Point) continue; var shapePoint = shape as ShapePoint; if (shapePoint == null) continue; var metadataNames = shape.GetMetadataNames(); if(!metadataNames.Any()) continue; var metadata = new Dictionary<string, string>(); foreach (var metadataName in metadataNames) { metadata.Add(metadataName,shape.GetMetadata(metadataName)); } results.Add(new MapPoint { Metadata = metadata, X = shapePoint.Point.X, Y = shapePoint.Point.Y }); } } return results; } } }
افزودن اطلاعات جغرافیایی به بانک اطلاعاتی SQL Server به کمک Entity framework
فایل places.shp را در مجموعه فایلهایی که در ابتدای بحث عنوان شدند، میتوانید مشاهده کنید. قصد داریم اطلاعات نقاط آنرا به مدل GeoLocation انتساب داده و سپس ذخیره کنیم:
var points = ShapeReader.ReadShapeFile("IranShapeFiles\\places.shp"); using (var context = new MyContext()) { context.Configuration.AutoDetectChangesEnabled = false; context.Configuration.ProxyCreationEnabled = false; context.Configuration.ValidateOnSaveEnabled = false; if (context.GeoLocations.Any()) return; foreach (var point in points) { context.GeoLocations.Add(new GeoLocation { Name = point.Metadata["name"], Type = point.Metadata["type"], Location = createPoint(point.X, point.Y) }); } context.SaveChanges(); }
در فایلهای مرتبط با places.shp، متادیتا name، معادل نام شهرهای ایران است و type آن بیانگر شهر، روستا و امثال آن میباشد.
پس از اینکه اطلاعات مکانهای ایران، در SQL Server ذخیره شدند، نمایش بصری آنها را در management studio نیز میتوان مشاهده کرد:
کوئری گرفتن از اطلاعات جغرافیایی
فرض کنید میخواهیم مکانهایی را با فاصله کمتر از 5 کیلومتر از تهران پیدا کنیم:
var tehran = createPoint(51.4179604, 35.6884243); using (var context = new MyContext()) { // find any locations within 5 kilometers ordered by distance var locations = context.GeoLocations .Where(loc => loc.Location.Distance(tehran) < 5000) .OrderBy(loc => loc.Location.Distance(tehran)) .ToList(); foreach (var location in locations) { Console.WriteLine(location.Name); } }
و یا اگر بخواهیم دقیقا بر اساس مختصات یک نقطه، مکانی را بیابیم، میتوان از متد SpatialEquals استفاده کرد:
var tehran = createPoint(51.4179604, 35.6884243); using (var context = new MyContext()) { // find any locations within 5 kilometers ordered by distance var tehranLocation = context.GeoLocations.FirstOrDefault(loc => loc.Location.SpatialEquals(tehran)); if (tehranLocation != null) { Console.WriteLine(tehranLocation.Type); } }
کدهای کامل این مثال را از اینجا میتوانید دریافت کنید:
EFGeoTests.zip
استفاده از چندین Context در EF 6 Code first
- استفاده از چند Context برای اینکه هر کدام قرار است در یک دیتابیس جدا ذخیره شوند؟ نمیشود FK بین اینها (جداول دو دیتابیس مختلف) تعریف کرد (SQL Server چنین کاری را پشتیبانی نمیکند).
- اگر برنامه ماژولار است، در EF میتوان به صورت خودکار تمام ماژولها را در طی یک Context یکپارچه بارگذاری کرد (^ و ^).
- هدف از ایجاد Schema در SQL Server، ایجاد ظروفی برای گروه بندی منطقی اشیاء است. مثلا عدهای به سه SP خاص دسترسی داشته باشند. عدهای فقط بتوانند با Viewها کار کنند. یا حتی عدهای به تمام موارد دسترسی داشته باشند. بنابراین یک نوع ایزوله سازی قسمتهای مختلف بانک اطلاعاتی مدنظر هست، در اصل. حالا اگر عدهای فقط به سه جدول خاص دسترسی دارند، آیا میتوانند ارجاعی را به جدول چهارمی که در یک schema دیگر تعریف شده داشته باشند؟ بله. البته فقط به این شرط که کاربران schema سه جدول فعلی به schema جدول چهارم، دسترسی و مجوز لازم را داشته باشد و برای این دسترسی دادنها هم باید مستقلا T-SQL بنویسید.
و ... ضمنا گاهی از اوقات از Schema برای مدیریت نامهای هم نام استفاده میشود. چیزی شبیه به namespace در سیشارپ مثلا. نمونهاش طراحی چند مستاجری است.
نتیجه گیری؟ برای سرگرمی Schema ایجاد نکنید؛ مگر اینکه واقعا قصد ایزوله سازی قسمتهای مختلف یک بانک اطلاعاتی را از کاربرانی خاص داشته باشید. به Schema به شکل یک Sandbox امنیتی (یک قرنطینه) نگاه کنید.
برای مطالعه بیشتر
Understanding the Difference between Owners and Schemas in SQL Server
Implementation of Database Object Schemas
ASP.NET Web API - قسمت اول
میخواهم بدانم برای امنیت web api در پروژه های web form چه کارهایی باید انجام دهیم بیشتر مطالب در مورد mvc هست مثلا Anti-Forgery Tokens برای mvc به راحتی میتوان استفاده کرد ولی برای web form چکار بهتر است انجام دهیم؟
در اینجا ما مستقیما با دستورات post put و delete کار داریم که اطلاعات بانک اطلاعاتی رو تغییر میدهند. حالا چطور میتوان امنیت رو کاملا تامین کرد ؟
مثلا کاربران شناسایی شده اطلاعات را وارد کنند و اینکه شخصی نتواند با یک دستور ای جکس توسط مرورگر اطلاعات اشتباه در سایت ثبت کند؟ و یا هر مشکل امنیتی دیگری که پیش بیاید؟
استفاده از چندین Context در EF 6 Code first
- ORMها صرفا کارهایی را قادر به انجام هستند که بانک اطلاعاتی مورد استفاده پشتیبانی میکند. برای نمونه در SQL Server امکان تهیه رابطه بین دو جدول از دو بانک اطلاعاتی مختلف وجود ندارد. منظور مثلا ایجاد کلید خارجی بین جداول دو بانک اطلاعاتی مختلف است و نه نوشتن کوئری بین آنها که از این لحاظ مشکلی نیست.
A FOREIGN KEY constraint can reference columns in tables in the same database or within the same table
- هدف از پشتیبانی چند Context در EF 6، تلفیق اینها با هم در قالب یک بانک اطلاعاتی است (مطلب فوق).
- همچنین در EF 6 میتوان چندین Context را به چندین بانک اطلاعاتی مختلف با رشتههای اتصالی متفاوت، انتساب داد. مباحث آغاز بانکهای اطلاعاتی هر کدام جداگانه عمل کرده و مستقل از هم عمل میکنند.
یک مثال جهت اجرا و آزمایش:
Sample25.cs
- اگر میخواهید به انعطاف پذیری Linked servers برسید (مثلا در طی یک کوئری و نه چند کوئری از جداول دو بانک اطلاعاتی مختلف در دو سرور متفاوت کوئری بگیرید)، نیاز خواهید داشت مثلا View یا SP تهیه کنید و سپس اینها را در برنامه مورد استفاده قرار دهید. Viewها با استفاده از T-SQL میتوانند کوئری واحدی از چند بانک اطلاعاتی تهیه کنند. اینبار برنامه هم نهایتا به یک بانک اطلاعاتی متصل میشود و برای آن اهمیتی ندارد که View یا SP به چه نحوی تهیه شده و با چند بانک اطلاعاتی کار میکند.
- تراکنشهای توزیع شده هم نیاز به تنظیمات خاصی در SQL Server دارند و باید MSDTC راه اندازی و تنظیم شود: (^). در غیراینصورت پیام خطای The underlying provider failed on Open. MSDTC on server is unavailable را دریافت خواهید کرد. بعد از آن باید از TransactionScope برای کار همزمان با چند Context استفاده کنید.
امکان خروجی اکسل از گزارشات سیستم، یکی از بایدهای بیشتر سیستمهای اطلاعاتی میباشد؛ یکی از چالشهای اصلی در تولید این نوع خروجی، افزایش مصرف حافظه متناسب با افزایش حجم دیتا میباشد. از آنجاییکه بیشتر راهکارهای موجود از جمله ClosedXml یا Epplus کل ساختار را ابتدا تولید کرده و اصطلاحا خروجی مورد نظر را بافر میکنند، برای حجم بالای اطلاعات مناسب نخواهند بود. راهکار برای خروجی CSV به عنوان مثال خیلی سرراست میباشد و میتوان با چند خط کد، به نتیجه دلخواه از طریق مکانیزم Streaming رسید؛ ولی ساختار Excel به سادگی فرمت CSV نیست و برای مثال فرمت Excel Workbook با پسوند xlsx یک بسته Zip شدهای از فایلهای XML میباشد.
معرفی MiniExcel
MiniExcel یک کتابخانه سورس باز با هدف به حداقل رساندن مصرف حافظه در زمان پردازش فایلهای Excel در دات نت میباشد. در مقایسه با Aspose از منظر امکانات شاید حرفی برای گفتن نداشته باشد، ولی از جهت خواندن اطلاعات فایلهای Excel با قابلیت پشتیبانی از LINQ و Deferred Execution در کنار مصرف کم حافظه و جلوگیری از مشکل OOM خیلی خوب عمل میکند. در تصویر زیر مشخص است که برای عمده عملیات پیادهسازی شده، از استریمها بهره برده شده است.
همچنین در زیر مقایسهای روی خروجی ۱ میلیون رکورد با تعداد ۱۰ ستون در هر ردیف انجام شدهاست که قابل توجه میباشد:
Logic : create a total of 10,000,000 "HelloWorld" excel
LibraryMethodMax Memory UsageMean | |||
MiniExcel | 'MiniExcel Create Xlsx' | 15 MB | 11.53181 sec |
Epplus | 'Epplus Create Xlsx' | 1,204 MB | 22.50971 sec |
OpenXmlSdk | 'OpenXmlSdk Create Xlsx' | 2,621 MB | 42.47399 sec |
ClosedXml | 'ClosedXml Create Xlsx' | 7,141 MB | 140.93992 sec |
به شدت API خوش دستی برای استفاده دارد و شاید مطالعه سورس کد آن از جهت طراحی نیز درس آموزی داشته باشد. در ادامه چند مثال از مستندات آن را میتوانید ملاحظه کنید:
var path = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.xlsx"); MiniExcel.SaveAs(path, new[] { new { Column1 = "MiniExcel", Column2 = 1 }, new { Column1 = "Github", Column2 = 2} });
// DataReader export multiple sheets (recommand by Dapper ExecuteReader) using (var cnn = Connection) { cnn.Open(); var sheets = new Dictionary<string,object>(); sheets.Add("sheet1", cnn.ExecuteReader("select 1 id")); sheets.Add("sheet2", cnn.ExecuteReader("select 2 id")); MiniExcel.SaveAs("Demo.xlsx", sheets); }
طراحی یک ActionResult سفارشی برای استفاده از MiniExcel
برای این منظور نیاز است تا Stream مربوط به Response درخواست جاری را در اختیار این کتابخانه قرار دهیم و از سمت دیگر دیتای مورد نیاز را به نحوی که بافر نشود و از طریق مکانیزم Streaming در EF (استفاده از Deferred Execution و Enumerableها) مهیا کنیم. برای امکان تعویض پذیری (این سناریو در پروژه واقعی و باتوجه به جهت وابستگیها میتواند ضروری باشد) از دو واسط زیر استفاده خواهیم کرد:
public interface IExcelDocumentFactory { ILargeExcelDocument CreateLargeDocument(IEnumerable<ExcelColumn> headers, Stream stream); } public interface ILargeExcelDocument : IAsyncDisposable, IDisposable { Task Write<T>( PaginatedEnumerable<T> items, int count, int sizeLimit, CancellationToken cancellationToken = default) where T : notnull; }
متد CreateLargeDocument یک وهله از ILargeExcelDocument را در اختیار مصرف کننده قرار میدهد که قابلیت نوشتن روی آن از طریق متد Write را خواهد داشت. روش واکشی دیتا از طریق Delegate تعریف شده با نام PaginatedEnumerable به مصرف کننده محول شدهاست که در ادامه امضای آن را میتوانید مشاهده کنید:
public delegate IEnumerable<T> PaginatedEnumerable<out T>(int page, int pageSize);
در ادامه پیادهسازی واسط ILargeExcelDocument برای MiniExcel به شکل زیر خواهد بود:
internal sealed class MiniExcelDocument(Stream stream, IEnumerable<ExcelColumn> columns) : ILargeExcelDocument { private const int SheetLimit = 1_048_576; private bool _disposedValue; public async Task Write<T>( PaginatedEnumerable<T> items, int count, int sizeLimit, CancellationToken cancellationToken = default) where T : notnull { ThrowIfDisposed(); // TODO: apply sizeLimit var properties = FastReflection.Instance.GetProperties(typeof(T)) .ToDictionary(p => p.Name, StringComparer.OrdinalIgnoreCase); var sheets = new Dictionary<string, object>(); var index = 1; while (count > 0) { cancellationToken.ThrowIfCancellationRequested(); IEnumerable<Dictionary<string, object>> reader = items(index, SheetLimit) .Select(item => { cancellationToken.ThrowIfCancellationRequested(); return columns.ToDictionary(h => h.Title, h => ValueOf(item, h.Name, properties)); }); sheets.Add($"sheet_{index}", reader); count -= SheetLimit; index++; } // This part is forward-only, and we are pretty sure that streaming will happen without buffering. await stream.SaveAsAsync(sheets, cancellationToken: cancellationToken); } private void Dispose(bool disposing) { if (!_disposedValue) { if (disposing) { // TODO: dispose managed state (managed objects) } // TODO: free unmanaged resources (unmanaged objects) and override finalizer // TODO: set large fields to null _disposedValue = true; } } ~MiniExcelDocument() { Dispose(disposing: false); } public void Dispose() { // Do not change this code. Put cleanup code in 'Dispose(bool disposing)' method Dispose(disposing: true); GC.SuppressFinalize(this); } public async ValueTask DisposeAsync() { Dispose(); await ValueTask.CompletedTask; } private void ThrowIfDisposed() { if (!_disposedValue) return; throw new ObjectDisposedException(nameof(MiniExcelDocument)); } private static object ValueOf<T>(T record, string prop, IDictionary<string, FastPropertyInfo> properties) where T : notnull { var property = properties[prop] ?? throw new InvalidOperationException($"There is no property with given name [{prop}]"); return NormalizeValue(property.GetValue?.Invoke(record)); } private static object NormalizeValue(object? value) { if (value == null) return null!; return value switch { DateTime dateTime => dateTime.ToShortPersianDateTimeString(), TimeSpan time => time.ToString(@"hh\:mm\:ss"), DateOnly dateTime => dateTime.ToShortPersianDateString(false), TimeOnly time => time.ToString(@"hh\:mm\:ss"), bool boolean => boolean ? "بلی" : "خیر", IEnumerable<object> values => string.Join(',', values.Select(NormalizeValue).ToList()), Enum enumField => enumField.GetEnumStringValue(), _ => value }; } }
در بدنه متد Write باتوجه به تعداد کل رکوردها، یک کوئری برای هر شیت از طریق فراخوانی متد منتسب به پارامتر items اجرا خواهد شد؛ توجه کنید که اجرای این کوئری مشخصا به تعویق افتاده و تا زمان اولین MoveNext، اجرایی صورت نخواهد گرفت (مفهوم Deferred Execution). به این ترتیب باقی کارها از جمله فرمت کردن مقادیر در سمت برنامه و از طریق Linq To Object انجام خواهد شد. همچنین پیادهسازی Factory مرتبط با آن به شکل زیر خواهد بود:
internal sealed class ExcelDocumentFactory : IExcelDocumentFactory { public ILargeExcelDocument CreateLargeDocument(IEnumerable<ExcelColumn> columns, Stream stream) { return new MiniExcelDocument(stream, columns); } }
در ادامه ActionResult سفارشی برای گرفتن خروجی اکسل را به شکل زیر می توان پیادهسازی کرد:
public class ExcelExportResult<T>(PaginatedEnumerable<T> items, int count, ExportMetadata metadata) : ActionResult where T : notnull { private const string ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; private const string Extension = ".xlsx"; private const int SizeLimit = int.MaxValue; private readonly IReadOnlyList<FastPropertyInfo> _properties = FastReflection.Instance.GetProperties(typeof(T)); public override async Task ExecuteResultAsync(ActionContext context) { var sp = context.HttpContext.RequestServices; var factory = sp.GetRequiredService<IExcelDocumentFactory>(); var disposition = new ContentDispositionHeaderValue(DispositionTypeNames.Attachment); disposition.SetHttpFileName(MakeFilename()); context.HttpContext.Response.Headers[HeaderNames.ContentDisposition] = disposition.ToString(); context.HttpContext.Response.Headers.Append(HeaderNames.ContentType, ContentType); context.HttpContext.Response.StatusCode = StatusCodes.Status200OK; //TODO: deal with exception, because our global exception handling cannot take into account while the response is started. await using var bodyStream = context.HttpContext.Response.BodyWriter.AsStream(); await context.HttpContext.Response.StartAsync(context.HttpContext.RequestAborted); await using (var document = factory.CreateLargeDocument(MakeColumns(), bodyStream)) { await document.Write(items, count, SizeLimit, context.HttpContext.RequestAborted); } await context.HttpContext.Response.CompleteAsync(); } private string MakeFilename() { return $"{metadata.Title} - {DateTime.UtcNow.ToEpochSeconds()}{Extension}"; } private IEnumerable<ExcelColumn> MakeColumns() { var types = _properties.ToDictionary(p => p.Name, p => p.PropertyType, StringComparer.OrdinalIgnoreCase); return metadata.Fields.Select(f => { var type = types[f.Name]; type = Nullable.GetUnderlyingType(type) ?? type; if (type.IsEnum || type == typeof(DateOnly) || type == typeof(TimeOnly) || type == typeof(bool) || type == typeof(TimeSpan) || type == typeof(DateTime)) { type = typeof(string); } return new ExcelColumn(f.Name, f.Title, type); }); } }
در اینجا از طریق ExportMetadata که از سمت کاربر تعیین میشود، مشخص خواهد شد که کدام فیلدها در فایل نهایی حضور داشته باشند. در بدنه متد ExecuteResultAsync یکسری هدر مرتبط با کار با فایلها تنظیم شدهاست و سپس از طریق BodyWriter و متد AsStream به استریم مورد نظر دست یافته و در اختیار متد Write مربوط به document ایجاد شده، قرار دادهایم. یک نمونه استفاده از آن برای موجودیت فرضی مشتری می تواند به شکل زیر باشد:
[ApiController, Route("api/customers")] public class CustomersController(IDbContext dbContext) : ControllerBase { [HttpGet("export")] public async Task<ActionResult> ExportCustomers([FromQuery] ExportMetadata metadata, CancellationToken cancellationToken) { var count = await dbContext.Set<Customer>().CountAsync(cancellationToken); return this.Export( (page, pageSize) => dbContext.Set<Customer>() .OrderBy(c => c.Id) .Skip((page - 1) * pageSize) .Take(pageSize) .AsNoTracking() .AsEnumerable(), // Enable streaming instead of buffering through deferred execution count, metadata); } }
در اینجا از طریق Extension Method مهیا شده روش کوئری کردن برای هر شیت را مشخص کردهایم؛ نکته مهم در ایجاد استفاده از متد AsEnumerable می باشد که در عمل یک Type Casting انجام می دهد که باقی متدهای استفاده شده روی خروجی، از طریق Linq To Object اعمال شود و همچنین نیاز به استفاده از ToList و یا موارد مشابه را نخواهیم داشت. نمونه درخواست GET برای این API می تواند به شکل زیر باشد:
http://localhost:5118/api/customers/export?Title=Test&Fields[0].Name=FirstName&Fields[0].Title=First name&Fields[1].Name=LastName&Fields[1].Title=Last name&Fields[2].Name=BirthDate&Fields[2].Title=BirthDate
سورس کد مثال قابل اجرا از طریق مخزن زیر قابل دسترس می باشد:
https://github.com/rabbal/large-excel-streaming
در این مثال در زمان آغاز برنامه، ۱۰ میلیون رکورد در جدول Customer ثبت خواهد شد که در ادامه می توان از آن خروجی Excel تهیه کرد.
نکته مهم: توجه داشته باشید که استفاده از این روش قابلیت از سرگیری مجدد برای دانلود را نخواهد داشت و شاید بهتر است این فرآیند را از طریق یک Job انجام داده و با استفاده از قابلیتهای Multipart Upload مربوط به یک BlobStroage مانند Minio، خروجی مورد نظر از قبل ذخیره کرده و لینک دانلودی را در اختیار کاربر قرار دهید.