How i can save an Image in Sqlite database as blob?

Question

This is the code I use to save a picture as base64String TEXT in my database.cs .

public void CreateDatabase(string sqldb_name)
        {
            try
            {
                sqldb_message = "";
                string sqldb_location = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
                string sqldb_path = Path.Combine(sqldb_location, sqldb_name);
                bool sqldb_exists = File.Exists(sqldb_path);
                if(!sqldb_exists)
                {

                    //Adding new fields
                    //For adding new fields we have to define the field on the "create" query, in this case, we're adding "BirthDay" field which is VARCHAR
                    sqldb = SQLiteDatabase.OpenOrCreateDatabase(sqldb_path,null);
                    sqldb_query = "CREATE TABLE IF NOT EXISTS MyTable (_id INTEGER PRIMARY KEY AUTOINCREMENT, Art TEXT, Album VARCHAR, Artist VARCHAR, Country VARCHAR, Year INT, Genre VARCHAR, Style VARCHAR, Format VARCHAR);";

                    sqldb.ExecSQL(sqldb_query);
                    sqldb_message = "Database: " + sqldb_name + " created";
                }
                else
                {
                    sqldb = SQLiteDatabase.OpenDatabase(sqldb_path, null, DatabaseOpenFlags.OpenReadwrite);
                    sqldb_message = "Database: " + sqldb_name + " opened";
                }
                sqldb_available=true;
            }
            catch(SQLiteException ex) 
            {
                sqldb_message = ex.Message;
            }
        }
        //Adds a new record with the given parameters

        //Adding new fields
        //We have to modify the CRUD (Create, Read, Update and Delete) operations by adding the new "BirthDay" field, in this case, we're adding the new string parameter "sBirthDay"
        public void AddRecord(string @IMG, string sAlbum, string sArtist, string sCountry, int iYear, string sGenre, string sStyle, string sFormat)
        {
            try
            {
                //Adding new fields
                //We have to modify the CRUD (Create, Read, Update and Delete) operations by adding the new "BirthDay" field to the query and assign the value "sBirthDay"
                sqldb_query = "INSERT INTO MyTable (Art, Album, Artist, Country, Year, Genre, Style, Format) VALUES ('" + @IMG + "','" + sAlbum + "','" + sArtist + "','" + sCountry + "','" + iYear + "','" + sGenre + "','" + sStyle + "','" + sFormat + "');";

                sqldb.ExecSQL(sqldb_query);
                sqldb_message = "Record saved";
            }
            catch(SQLiteException ex) 
            {
                sqldb_message = ex.Message;
            }
        }

What changes should I make to the code to save an image as blob in database?

There are two possible ways for saving Image 1.You can Save it in File using Bitmap then get the Image path and Save it in Db as String — Rajan Kali, Aug 21 '15 at 09:20
You can refer this http://stackoverflow.com/questions/7331310/how-to-store-image-as-blob-in-sqlite-how-to-retrieve-it — zzas11, Aug 21 '15 at 09:23
SQLite is meant to contain around ~400KB data. storing images inside a SQLite Db would'nt be too fruitful. The image path method written above is better. — Niraj Adhikari, Aug 21 '15 at 09:27

score 2 · Answer 1 · answered Aug 21 '15 at 09:23

There are two possible ways for saving Image

1.You can Save it in File using Bitmap then get the Image path and Save it in Db as String

2.Convert it into byte array and save it as Blob

ByteArrayOutputStream stream = new ByteArrayOutputStream();
bmp.compress(Bitmap.CompressFormat.PNG, 100, stream);
byte[] byteArray = stream.toByteArray();

score 2 · Accepted Answer · answered Aug 21 '15 at 09:32

2

SQLite is meant to contain around ~400KB data. storing images inside a SQLite Db would'nt be nice. The image path method written above is better.

You will be better off using a image loading library as Picasso, Universal Image Loader etc if it suits your needs. This way you can just persist the image url. The image is cached by the library.

answered Aug 21 '15 at 09:32

Niraj Adhikari

1,678
2
14
28

Does Picasso cache it automatically? – mohsen_og Feb 27 '18 at 15:03

How i can save an Image in Sqlite database as blob?

2 Answers2