SQLite بانک اطلاعاتی سریع، کم حجم و سورس بازی است که استفاده از آن در دات نت فریم ورک بسیار ساده است. فقط کافی است پروایدر مربوط به آن را دریافت کنید و در کدهای قدیمی خود هر جایی مثلا sqlconnection داشتید آن‌را تبدیل به sqliteconnection کنید و امثال آن (به بیان دیگر، پروایدر تهیه شده از معماری ADO.NET پیروی می‌کند و عملا دانش قبلی شما به سادگی قابل استفاده و ارتقاء است). علاوه بر آن پروایدر ADO.NET‌ تهیه شده برای آن، پشتیبانی از Entity framework را هم ارائه می‌دهد.
این دیتابیس تحت سیستم عامل‌های مختلف مهیا است و مهم‌ترین مزیت آن عدم نیاز به نصب آن می‌باشد.

مزایا:
- سورس باز و رایگان
- مهیا بودن آن در سایر پلتفرم‌ها (ویندوز، لینوکس و ...)
- نیازی به نصب ندارد و فقط یک DLL بومی است. این مورد برای کاربرانی که در مدیریت بانک‌های اطلاعاتی پیچیده مشکل دارند، یک مزیت مهم است.
- امکان تشیکل دیتابیس در حافظه. این نکته و توانایی، در آزمون‌های واحد بسیار جالب توجه است. می‌توانید با سرعت بالا دیتابیسی واقعی را در حافظه تشکیل داده، کلیه آزمون‌های واحد خود را اجرا کرده و پس از پایان کار، اثری از دیتابیس و تغییر داده‌ها و مشکلات بازگردانی اطلاعات به حالت اول وجود نخواهد داشت.


ملاحظات:
الف) مرتب سازی SQLite حساس به حروف کوچک و بزرگ است.
برای برگشت به عادت متداولی که وجود دارد می‌شود به صورت زیر عمل کرد:
select f1 from tbl1 order by f1 COLLATE NOCASE
یک COLLATE NOCASE اضافه شده است.

ب) رعایت نکات مرتبط با سیستم‌های 64 بیتی
در مورد سیستم‌های 64 بیتی و دات نت قبلا مطلبی را نوشته بودم : {+}. این مطلب دقیقا اینجا کاربرد پیدا می‌کند، از این لحاظ که SQLite یک بانک اطلاعاتی Native است. اگر برنامه‌ی دات نت شما برای حالت Any CPU تهیه شده است، در سیستم‌های 32 بیتی نیاز است تا DLL مرتبط SQLite را توزیع کنید و در سیستم‌های 64 بیتی DLL مرتبط 64 بیتی آن نیاز خواهد بود. در غیراینصورت برنامه‌ی شما در بدو امر کرش کرده و اجرا نخواهد شد.

مشکلات:
الف) کلید خارجی بی خاصیت!
SQLite از کلید خارجی پشتیبانی می‌کند اما آن‌را اعمال نمی‌کند! برای اینکه کلید خارجی را اعمال کنید باید خودتان تریگر بنویسید تا این‌کار را انجام دهد.

ب) پشتیبانی در حد صفر از مباحث همزمانی و تردینگ.
اگر برنامه شما مالتی ترد است، در بد مخمصه‌ای گرفتار شده‌اید. مدام با پیغام database is locked مواجه خواهید شد. (چه انتظاری داشتید؟ یک dll کمتر از 2 مگابایت که قرار نیست کار غول‌های دیتابیسی را انجام دهد)
بنابراین اصلا تصورش را هم نکنید که از این دیتابیس به عنوان بانک اطلاعاتی یک سایت (و محیط‌های چند کاربره) بتوان استفاده کرد و کاربران دچار مشکل نشوند.

ج)حجم بالای دیتابیس و عدم کش
از مباحث caching که در دیتابیس‌های معظم دیگر به صورت توکار وجود دارد خبری نیست. برای مثال اگر یک کوئری قرار است تعدادی را شمارش نماید، حاصلی کش نشده و اگر صدبار هم به صورت متوالی آن‌را فراخوانی کنید باز هم از نو محاسبات آن انجام خواهد شد.
این مورد در حجم بالای دیتابیس واقعا مهم است و نمودش را با دیتابیسی با حجم بالای یک گیگ به وضوح مشاهده خواهید. افت کارآیی و همچنین قرچ و قرچ مداوم هارد دیسک سیستم! (چون به کش رجوع نمی‌شود)

د) امنیت
روی بانک‌های اطلاعاتی اکسس حداقل می‌توان یک کلمه‌ی عبور را قرار داد (که در کسری از ثانیه قابل شکستن است!). در SQLite استاندارد هیچ خبری از این مباحث نبوده و امنیت را باید خودتان تامین کنید. (البته یک نسخه‌ی تجاری هم از این بانک اطلاعاتی با پشتیبانی از رمزنگاری اطلاعات موجود است : +)

ه) مرتب سازی فارسی
هر چند SQLite هیچ مشکلی در ثبت اطلاعات یونیکد و خصوصا متون فارسی ندارد، اما با مرتب سازی کلمات یونیکد مشکل داشته و بر اساس کد اسکی آن‌ها عمل می‌کند. هر چند امکان تعریف Collation سفارشی در آن ممکن است : + (البته ممکن بودن با موجود بودن متفاوت است)


نتیجه گیری:
- SQLite برای دیتابیسی تا حدود یک گیگ که فقط یک نفر قرار باشد از آن استفاده کند انتخاب بسیار مناسبی است (برای مثال فایرفاکس از آن برای ذخیره سازی تنظیمات خودش استفاده می‌کند).

SQL Server CE برای اولین بار جهت استفاده در SmartPhones طراحی شد؛ جزو خانواده‌ی Embedded databases قرار می‌گیرد و این مزایا را دارد:
- نیازی به نصب ندارد و از چند DLL تشکیل شده است (برای مثال جهت استفاده در کارهای تک کاربره‌ی قابل حمل ایده‌آل است).
- رایگان است (جهت استفاده در کارهای تجاری و غیرتجاری).
- حجم کمی دارد (جمعا کمتر از دو مگابایت).
- پروایدر ADO.NET آن موجود است (توسط فضای نام System.Data.SqlServerCe که به کمک اسمبلی System.Data.SqlServerCe.dll قرار گرفته در مسیر C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop ارائه می‌شود).
- با کمک ORM هایی مانند Entity framework و یا NHibernate نیز می‌توان با آن کار کرد.
- نسخه‌ی 4 نهایی آن که قرار است در زمان ارائه‌ی SP1 مربوط به VS.NET 2010 ارائه شود، جهت استفاده در برنامه‌های ASP.NET (برنامه‌های چند کاربره) ایی که تعداد کاربر کمی دارند، بهینه سازی شده و این مورد یک مزیت مهم نسبت به SQLite است که اساسا با تردهای همزمان جهت کار با بانک اطلاعاتی مشکل دارد.
- امکان گذاشتن کلمه‌ی عبور بر روی بانک اطلاعاتی آن وجود دارد که سبب رمزنگاری خودکار آن نیز خواهد شد (این مورد به صورت پیش فرض در SQLite پیش بینی نشده و جزو مواردی که است که باید برای آن هزینه کرد). الگوریتم رمزنگاری آن به صورت رسمی معرفی نشده، ولی به احتمال زیاد AES می‌باشد.
- از ADO.NET Sync Framework پشتیبانی می‌کند.

ملاحظات:
- به آن می‌توان به صورت نسخه‌ی تعدیل شده‌ی SQL Server 2000 با توانایی‌های کاهش یافته نگاه کرد. در آن خبری از رویه‌های ذخیره شده، View ها ، Full text search ، CLR Procs، CLR Triggers و غیره نیست (سطح توقع را باید در حد همان 2 مگابایت پایین نگه داشت!). لیست کامل : (+)
- Management studio مربوط به SQL Server 2005 به هیچ عنوان از آن پشتیبانی نمی‌کند و تنها نسخه‌ی 2008 است که نگارش 3 و نیم آن‌را پشتیبانی می‌کند آن هم نه با توانایی‌هایی که جهت کار با SQL Server اصلی وجود دارد. مثلا امکان rename یک فیلد را ندارد و باید برای اینکار کوئری نوشت. خوشبختانه یک سری پروژه‌ی رایگان در سایت CodePlex این نقایص را پوشش داده‌اند؛ برای مثال : ExportSqlCe
- از آنجائیکه DLL های SQL CE از نوع Native هستند، باید دقت داشت که حین استفاده از آن‌ها در دات نت فریم ورک اگر platform target قسمت build برنامه بر روی ALL CPU تنظیم شده باشد، برنامه به احتمال زیاد در سیستم‌های 64 بیتی کرش خواهد کرد (اگر در حین توسعه برنامه از DLL‌های بومی 32 بیتی آن استفاده شده باشد). بنابراین نیاز است DLL های 64 بیتی را به صورت جداگانه جهت سیستم‌های 64 بیتی ارائه داد. اطلاعات بیشتر: (+) و (+) و (+)
- Entity framework یک سری از قابلیت‌های این بانک اطلاعاتی را پشتیبانی نمی‌کند. برای مثال اگر یک primary key از نوع identity را تعریف کردید، برنامه کار نخواهد کرد! لیست مواردی را که پشتیبانی نمی‌شوند، در این آدرس می‌توان مشاهده کرد.

و اخبار مرتبط با SQL CE را در این بلاگ می‌توانید دنبال کنید.

EF Code first و بانک‌های اطلاعاتی متفاوت

در آخرین قسمت از سری EF Code first بد نیست نحوه استفاده از بانک‌های اطلاعاتی دیگری را بجز SQL Server نیز بررسی کنیم. در اینجا کلاس‌های مدل و کدهای مورد استفاده نیز همانند قسمت 14 است و تنها به ذکر تفاوت‌ها و نکات مرتبط اکتفاء خواهد شد.


حالت کلی پشتیبانی از بانک‌های اطلاعاتی مختلف توسط EF Code first

EF Code first با کلیه پروایدرهای تهیه شده برای ADO.NET 3.5 که پشتیبانی از EF را لحاظ کرده باشند،‌ به خوبی کار می‌کند. پروایدرهای مخصوص ADO.NET 4.0، تنها سه گزینه DeleteDatabase/CreateDatabase/DatabaseExists را نسبت به نگارش قبلی بیشتر دارند و EF Code first ویژگی‌های بیشتری را طلب نمی‌کند.
بنابراین اگر حین استفاده از پروایدر ADO.NET مخصوص بانک اطلاعاتی خاصی با پیغام «CreateDatabase is not supported by the provider» مواجه شدید، به این معنا است که این پروایدر برای دات نت 4 به روز نشده است. اما به این معنا نیست که با EF Code first کار نمی‌کند. فقط باید یک دیتابیس خالی از پیش تهیه شده را به برنامه معرفی کنید تا مباحث Database Migrations به خوبی کار کنند؛ یا اینکه کلا می‌توانید Database Migrations را خاموش کرده (متد Database.SetInitializer را با پارامتر نال فراخوانی کنید) و فیلدها و جداول را دستی ایجاد کنید.


استفاده از EF Code first با SQLite

برای استفاده از SQLite در دات نت ابتدا نیاز به پروایدر ADO.NET آن است: «مکان دریافت درایور‌های جدید SQLite مخصوص دات نت»
ضمن اینکه به نکته «استفاده از اسمبلی‌های دات نت 2 در یک پروژه دات نت 4» نیز باید دقت داشت.
و یکی از بهترین management studio هایی که برای آن تهیه شده: «SQLite Manager»
پس از دریافت پروایدر آن، ارجاعی را به اسمبلی System.Data.SQLite.dll به برنامه اضافه کنید.
سپس فایل کانفیگ برنامه را به نحو زیر تغییر دهید:

همانطور که ملاحظه می‌کنید، تفاوت آن با قبل، تغییر connectionString و providerName است.
اکنون اگر همان برنامه قسمت قبل را اجرا کنیم به خطای زیر برخواهیم خورد:
«The given key was not present in the dictionary»
در این مورد هم توضیح داده شد. سه گزینه DeleteDatabase/CreateDatabase/DatabaseExists در پروایدر جاری SQLite برای دات نت وجود ندارد. به همین جهت نیاز است فایل «CodeFirst.db» ذکر شده در کانکشن استرینگ را ابتدا دستی درست کرد.
برای مثال از افزونه SQLite Manager استفاده کنید. ابتدا یک بانک اطلاعاتی خالی را درست کرده و سپس دستورات زیر را بر روی بانک اطلاعاتی اجرا کنید تا دو جدول خالی را ایجاد کند (در برگه Execute sql افزونه SQLite Manager):


سپس سطر زیر را نیز به ابتدای برنامه اضافه کنید:


به این ترتیب database migrations خاموش می‌شود و اکنون برنامه بدون مشکل کار خواهد کرد.
فقط باید به یک سری نکات مانند نوع داده‌ها در بانک‌های اطلاعاتی مختلف دقت داشت. برای مثال integer در اینجا از نوع Int64 است؛ بنابراین در برنامه نیز باید به همین ترتیب تعریف شود تا نگاشت‌ها به درستی انجام شوند.

در کل تنها مشکل پروایدر فعلی SQLite عدم پشتیبانی از مباحث database migrations است. این مورد را خاموش کرده و تغییرات ساختار بانک اطلاعاتی را به صورت دستی به بانک اطلاعاتی اعمال کنید. بدون مشکل کار خواهد کرد.

البته اگر به دنبال پروایدری تجاری با پشتیبانی از آخرین نگارش EF Code first هستید، گزینه زیر نیز مهیا است:
http://devart.com/dotconnect/sqlite/
برای مثال اگر علاقمند به استفاده از حالت تشکیل بانک اطلاعاتی SQLite در حافظه هستید (با رشته اتصالی ویژه Data Source=:memory:;Version=3;New=True;)،‌ فعلا تنها گزینه مهیا استفاده از پروایدر تجاری فوق است؛ زیرا مبحث Database Migrations را به خوبی پشتیبانی می‌کند.



استفاده از EF Code first با SQL Server CE

قبلا در مورد «استفاده از SQL-CE به کمک NHibernate» مطلبی را در این سایت مطالعه کرده‌اید. سه مورد اول آن با EF Code first یکی است و تفاوتی نمی‌کند (یک سری بحث عمومی مشترک است). البته با یک تفاوت؛ در اینجا EF Code first قادر است یک بانک اطلاعاتی خالی SQL Server CE را به صورت خودکار ایجاد کند و نیازی نیست تا آن‌را دستی ایجاد کرد. مباحث database migrations و به روز رسانی خودکار ساختار بانک اطلاعاتی نیز در اینجا پشتیبانی می‌شود.
برای استفاده از آن ابتدا ارجاعی را به اسمبلی System.Data.SqlServerCe.dll قرار گرفته در مسیر Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop اضافه کنید.
سپس رشته اتصالی به بانک اطلاعاتی و providerName را به نحو زیر تغییر دهید:



بدون نیاز به هیچگونه تغییری در کدهای برنامه، همین مقدار تغییر در تنظیمات ابتدایی برنامه برای کار با SQL Server CE کافی است.
ضمنا مشکلی هم با فیلد Identity در آخرین نگارش EF Code first وجود ندارد؛ برخلاف حالت database first آن که پیشتر این اجازه را نمی‌داد و خطای «Server-generated keys and server-generated values are not supported by SQL Server Compact» را ظاهر می‌کرد.



استفاده از EF Code first با MySQL

برای استفاده از EF Code first با MySQL (نگارش 5 به بعد البته) ابتدا نیاز است پروایدر مخصوص ADO.NET آن‌را دریافت کرد: (^)
که از EF نیز پشتیبانی می‌کند. پس از نصب آن، ارجاعی را به اسمبلی MySql.Data.dll قرار گرفته در مسیر Program Files\MySQL\MySQL Connector Net 6.5.4\Assemblies\v4.0 به پروژه اضافه نمائید.
سپس رشته اتصالی و providerName را به نحو زیر تغییر دهید:


همانطور که مشاهده می‌کنید در اینجا شماره نگارش دقیق پروایدر مورد استفاده نیز ذکر شده است. برای مثال اگر چندین پروایدر روی سیستم نصب است، با مقدار دهی DbProviderFactories می‌توان از نگارش مخصوصی استفاده کرد.

با این تغییرات پس از اجرای برنامه قسمت قبل، به خطای زیر برخواهیم خورد:
The given key was not present in the dictionary

توضیحات این مورد با قسمت SQLite یکی است؛ به عبارتی نیاز است بانک اطلاعاتی testdb را دستی درست کرد. همچنین جداول و فیلدها را نیز باید دستی ایجاد کرد و database migrations را نیز باید خاموش کرد (پارامتر Database.SetInitializer را به نال مقدار دهی کنید).
برای این منظور یک دیتابیس خالی را ایجاد کرده و سپس دو جدول زیر را به آن اضافه کنید:


پس از این تغییرات، برنامه بدون مشکل اجرا خواهد شد (ایجاد بانک اطلاعاتی خالی به همراه ایجاد ساختار جداول و خاموش کردن database migrations که توسط این پروایدر پشتیبانی نمی‌شود).

به علاوه پروایدر تجاری دیگری هم در سایت devart.com برای MySQL و EF Code first مهیا است که مباحث database migrations را به خوبی مدیریت می‌کند.


مشکل!
اگر به همین نحو برنامه را اجرا کنیم، فیلدهای یونیکد فارسی ثبت شده در MySQL با «??????? ?? ????» مقدار دهی خواهند شد و تنظیم CHARACTER SET utf8 COLLATE utf8_persian_ci نیز کافی نبوده است (این مورد با SQLite یا نگارش‌های مختلف SQL Server بدون مشکل کار می‌کند و نیاز به تنظیم اضافه‌تری ندارد):


برای رفع این مشکل توصیه شده است که CharSet=UTF8 را به رشته اتصالی به بانک اطلاعاتی اضافه کنیم. اما در این حالت خطای زیر ظاهر می‌شود:
The provider did not return a ProviderManifestToken string
این مورد فقط به اشتباه بودن تعاریف رشته اتصالی بر می‌گردد؛‌ یا عدم پشتیبانی از تنظیم اضافه‌ای که در رشته اتصالی ذکر شده است.
مقدار صحیح آن دقیقا مساوی CHARSET=utf8 است (با همین نگارش و رعایت کوچکی و بزرگی حروف؛ مهم!):



به این ترتیب، مشکل ثبت عبارات یونیکد فارسی برطرف می‌شود (البته جدول هم بهتر است به DEFAULT CHARACTER SET utf8 COLLATE utf8_persian_ci تغییر پیدا کند؛ مطابق دستور Alter ایی که در بالا ذکر شد).

 PM> Install-Package Lex.Db

    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string City { get; set; }
    }

    public class Order
    {
        public int Id { get; set; }
        public int? CustomerFK { get; set; }
        public int[] ProductsFK { get; set; }
    }

    public static class Database
    {
        public static DbInstance Instance { get; private set; }

        public static DbTable<Product> Products { get; private set; }
        public static DbTable<Order> Orders { get; private set; }
        public static DbTable<Customer> Customers { get; private set; }

        /// <summary>
        /// سازنده استاتیکی که در طول عمر برنامه فقط یکبار اجرا می‌شود
        /// </summary>
        static Database()
        {
            createDb();
            getTables();
        }

        private static void getTables()
        {
            Products = Instance.Table<Product>();
            Customers = Instance.Table<Customer>();
            Orders = Instance.Table<Order>();
        }

        private static void createDb()
        {
            Instance = new DbInstance(Path.Combine(Environment.CurrentDirectory, "LexDbTests"));

            Instance.Map<Product>()
                    .WithIndex("NameIdx", x => x.Name)
                    .Automap(i => i.Id, true);

            Instance.Map<Order>()
                    .Automap(i => i.Id, true);

            Instance.Map<Customer>()
                    .WithIndex("NameIdx", x => x.Name)
                    .WithIndex("CityIdx", x => x.City)
                    .Automap(i => i.Id, true);

            Instance.Initialize();
        }
    }

        private static void addData()
        {
            var customer1 = new Customer { Name = "customer1", City = "City1" };
            var customer2 = new Customer { Name = "customer2", City = "City2" };
            Database.Instance.Save(customer1, customer2); // automatic Id assignment after Save

            var product1 = new Product { Name = "product1" };
            var product2 = new Product { Name = "product2" };
            Database.Instance.Save(product1, product2); // automatic Id assignment after Save

            var order1 = new Order { CustomerFK = customer1.Id, ProductsFK = new[] { product1.Id } };
            var order2 = new Order { CustomerFK = customer2.Id, ProductsFK = new[] { product1.Id, product2.Id } };
            Database.Instance.Save(order1, order2); // automatic Id assignment after Save
        }

        private static void loadAll()
        {
            var orders = Database.Orders.LoadAll();
            foreach (var order in orders)
            {
                // نحوه دریافت اطلاعات مشتری بر اساس کلید خارجی ثبت شده
                var orderCustomer = Database.Customers.LoadByKey(order.CustomerFK.Value);
                Console.WriteLine("Order Id: {0}, Customer: {1} ({2}) {3}", order.Id, orderCustomer.Name, orderCustomer.Id, orderCustomer.City);

                // نحوه بازیابی لیستی از اشیاء مرتبط از طریق آرایه‌ای از کلیدهای خارجی ثبت شده
                var orderProducts = Database.Products.LoadByKeys(order.ProductsFK);
                foreach (var product in orderProducts)
                {
                    Console.WriteLine("  Product Id: {0}, Name: {1}", product.Id, product.Name);
                }
            }
        }

        private static void queryingByAnIndex()
        {
            var name = "customer1";
            var customersList = Database.Customers
                                        .IndexQueryByKey("NameIdx", name)
                                        .ToList();
            foreach (var person in customersList)
            {
                Console.WriteLine(person.Name);
            }
        }

            // Using Take and Skip
            var list1 = Database.Orders.Query<int>() // primary idx
                                       .Take(1).Skip(2).ToList();

            // Querying Between Ranges 
            var list2 = Database.Customers
                                .IndexQuery<string>("NameIdx")
                                .GreaterThan("a", orEqual: true).LessThan("d").ToList();

        private static void deletingRecords()
        {
            Database.Customers.DeleteByKey(key: 1);

            var customers = Database.Customers.LoadByKeys(new[] { 1, 2 });
            Database.Customers.Delete(customers);
        }