Using embedded resource database

[Marc V]

Hi,

I have a read-only database file which I like to embed in the assembly. Is it possible to access this database when it's embedded as as resource?

[Pinturiccio]

Yes, you can use a SQLite database as an embedded resource. To access this database, do the following:
1. Extract the database from the assembly.
2. Put the data into a temporary file.
3. Read the required information.
4. Delete the temporary file.

For example, suppose I have a "database.db" file with a SQLite database that contains the Dept table:

Code: Select all

CREATE TABLE DEPT(
  DEPTNO INTEGER PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);
Insert Into dept (deptno, dname, loc) values(10, 'Accounting', 'New York');

Then the database should be added to the project as an embedded resource. The code below illustrates how to use the database.db database which has been added to the project as an embedded resource.

Code: Select all

namespace SQLiteEmbeddedDB
{
    class Program
    {
        static void Main(string[] args)
        {
            Test();
        }


        public static void Test()
        {
            string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "database.db");
            using (var resourceStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("SQLiteEmbeddedDB.database.db"))
            {
                using (var fileStream = File.OpenWrite(path))
                {
                    CopyStream(resourceStream, fileStream);
                }
            }
            // now access database using 'path'
            SQLiteConnection conn = new SQLiteConnection("Pooling=false;Data Source="+path);
            conn.Open();
            SQLiteCommand comm = new SQLiteCommand("select * from dept", conn);
            SQLiteDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                    Console.Write(reader.GetValue(i) + "\t");
                Console.WriteLine();
            }
            reader.Close();
            conn.Close();
            File.Delete(path);
        }

        public static void CopyStream(Stream inputStream, Stream outputStream)
        {
            CopyStream(inputStream, outputStream, 4096);
        }

        public static void CopyStream(Stream inputStream, Stream outputStream, int bufferLength)
        {
            var buffer = new byte[bufferLength];
            int bytesRead;
            while ((bytesRead = inputStream.Read(buffer, 0, bufferLength)) > 0)
            {
                outputStream.Write(buffer, 0, bytesRead);
            }
        }
    }
}