Demo of ADO.NET POCO with SQLite database

This demonstration shows how to prepare for and how to implement model-first ADO.NET POCO objects with a SQLite backing store.

C# (46.5 KB)
 
 
 
 
 
5 Star
(1)
3,375 times
Add to favorites
9/12/2011
E-mail Twitter del.icio.us Digg Facebook
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SQLite;
using System.Reflection;
using System.Diagnostics;

namespace POCOWithSQLiteDemo.Model
{

    public static class FileManager
    {
        private static String _createFileDBConnectionStringFormat =
        "Data Source={0};New=True;Compress=True;Synchronous=Off";

        private static String _modelContextConnectionStringFormat =
            "metadata=res://*/Model.DataModel.csdl|res://*/Model.DataModel.ssdl|res://*/Model.DataModel.msl;" +
            "provider=System.Data.SQLite;" +
            "provider connection string=\"data source={0}\"";

        public static DataModelContainer CreateNew(String fileName, bool overWrite = false)
        {
            // step 0: check if we can write to the file
            if (!overWrite)
            {
                FileInfo fi = new FileInfo(fileName);
                if (fi.Exists)
                    throw new ArgumentException("File exists. If you want to overwrite the existing file, call with overWrite=true");
            }

            // step 1: create database layout
            String connString = String.Format(_createFileDBConnectionStringFormat, fileName);
            using (SQLiteConnection conn = new SQLiteConnection(connString))
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(DatabaseScript, conn);
                cmd.ExecuteNonQuery();
            }

            // step 2: create model context attached to SQLite db 
            return OpenFile(fileName);
        }


        public static DataModelContainer OpenFile(string fileName)
        {
            FileInfo fi = new FileInfo(fileName);
            if (!fi.Exists)
            {
                throw new FileNotFoundException("Database file not found. Use CreateNew to create a database.", fileName);
            }

            return new DataModelContainer(String.Format(_modelContextConnectionStringFormat, fileName));

        }

        private static String _createDatabaseSQL;
        public static String DatabaseScript
        {
            get
            {
                if (null == _createDatabaseSQL)
                {
                    Assembly asm = Assembly.GetAssembly(typeof(DataModelContainer));

                    using (Stream stream = asm.GetManifestResourceStream("POCOWithSQLiteDemo.Model.DataModel.edmx.sql"))
                    {
                        Debug.Assert(null != stream, "Failed to embed DataModel.edmx.sql resource");
                        using (StreamReader reader = new StreamReader(stream))
                        {
                            _createDatabaseSQL = reader.ReadToEnd();
                        }
                    }
                }
                return _createDatabaseSQL;
            }
        }
    }
}