using System; using System.IO; using System.Collections.Generic; using System.Windows.Forms; using System.Runtime.InteropServices; using System.Data.SQLite; namespace TestBlob { static class Program { private const string SQLITE_DLL = "System.Data.SQLite.DLL"; /// /// The main entry point for the application. /// [STAThread] static void Main() { SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder(); sb.DataSource = "test.db"; using (SQLiteConnection c = new SQLiteConnection(sb.ConnectionString)) { c.Open(); SQLiteCommand query = new SQLiteCommand("SELECT LENGTH(bfield) AS bfield FROM test1", c); using (SQLiteDataReader reader = query.ExecuteReader()) { while (reader.Read()) { if (reader["bfield"] == DBNull.Value) { } else { long bfield = (long)reader["bfield"]; } } // while } // using } // using IntPtr conn = IntPtr.Zero; int res = sqlite3_open("test.db", ref conn); if (res > 0) throw new Exception("failed to open connection: " + res); ReadBlobToFile(conn, "main", "test1", "bfield", 1L, "blob.dat"); //WriteBlobFromFile(conn, "main", "test1", "bfield", 1L, "Apollo_13.mp3"); sqlite3_close(conn); } public static void WriteBlobFromFile(IntPtr conn, string dbName, string tableName, string columnName, long rowid, string fpath) { int res; // Prepare an empty blob to which I can write the contents of the file. using (FileStream fs = File.Open(fpath, FileMode.Open, FileAccess.Read, FileShare.Read)) { long len = fs.Length; IntPtr stmt = IntPtr.Zero; IntPtr tail = IntPtr.Zero; res = sqlite3_prepare_v2(conn, "UPDATE " + tableName + " SET " + columnName + " = @blob WHERE RowID = @rowid", -1, ref stmt, ref tail); if (res > 0) throw new Exception("failed to prepare sql zeroblob statement: " + res); res = sqlite3_bind_zeroblob(stmt, 1, (int)len); if (res > 0) throw new Exception("failed to bind zero-blob: " + res); res = sqlite3_bind_int64(stmt, 2, rowid); if (res > 0) throw new Exception("failed to bind rowid: " + res); res = sqlite3_step(stmt); if (res > 0 && res < 100) throw new Exception("failed to execute zeroblob command: " + res); res = sqlite3_finalize(stmt); if (res > 0) throw new Exception("failed to finalize zeroblob command: " + res); IntPtr bhandle = IntPtr.Zero; res = sqlite3_blob_open(conn, dbName, tableName, columnName, rowid, 1, ref bhandle); if (res > 0) throw new Exception("failed to open blob handle: " + res); byte[] buffer = new byte[4096]; int offset = 0; int written = 0; while (written < len) { int count = (int)(len - written); if (count > 4096) count = 4096; int nbytes = fs.Read(buffer, 0, count); res = sqlite3_blob_write(bhandle, buffer, nbytes, offset); if (res > 0) throw new Exception("failed to write blob value: " + res); offset += nbytes; written += nbytes; } // while res = sqlite3_blob_close(bhandle); if (res > 0) throw new Exception("failed to close blob handle: " + res); } // using } public static void ReadBlobToFile(IntPtr conn, string dbName, string tableName, string columnName, long rowId, string fpath) { IntPtr bhandle = IntPtr.Zero; ; int res = sqlite3_blob_open(conn, dbName, tableName, columnName, rowId, 0, ref bhandle); if (res > 0) throw new Exception("failed to open blob handle: " + res); int count = sqlite3_blob_bytes(bhandle); if (File.Exists(fpath)) File.Delete(fpath); using (FileStream fs = File.Create(fpath, 4096)) { fs.SetLength(count); int offset = 0; byte[] buffer = new byte[4096]; while (count > 0) { int toread = (count > 4096 ? 4096 : count); res = sqlite3_blob_read(bhandle, buffer, toread, offset); if (res > 0) throw new Exception("failed to read from blob handle: " + res); fs.Write(buffer, 0, buffer.Length); offset += toread; count -= toread; } // while fs.Flush(); } // using sqlite3_blob_close(bhandle); } /* int sqlite3_open(const char *filename, sqlite3 **ppDb); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_open(string filename, ref IntPtr ppDb); /* int sqlite3_close(sqlite3 *); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_close(IntPtr ppDb); [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_blob_open(IntPtr stmt, string zDb, string zTable, string zColumn, long iRow, int flags, ref IntPtr blobHandle); /* int sqlite3_blob_bytes(sqlite3_blob *); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_blob_bytes(IntPtr blobHandle); /* int sqlite3_blob_close(sqlite3_blob *); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_blob_close(IntPtr blobHandle); /* int sqlite3_blob_read(sqlite3_blob *, void *Z, int N, int iOffset); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_blob_read(IntPtr blobHandle, byte[] data, int len, int offset); /* int sqlite3_blob_write(sqlite3_blob *, const void *z, int n, int iOffset); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_blob_write(IntPtr blobHandle, byte[] data, int len, int offset); /* int sqlite3_prepare_v2(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_prepare_v2(IntPtr ppDb, string sql, int maxLen, ref IntPtr stmt, ref IntPtr pzTail); /* int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_bind_zeroblob(IntPtr stmt, int prmIndex, int size); /* int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_bind_int64(IntPtr stmt, int prmIndex, long value); /* int sqlite3_step(sqlite3_stmt*); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_step(IntPtr stmt); /* int sqlite3_finalize(sqlite3_stmt *pStmt); */ [DllImport(SQLITE_DLL, CallingConvention = CallingConvention.Cdecl)] public static extern int sqlite3_finalize(IntPtr stmt); } }