Articles in this section
Category / Section

How to import and export image from SQL DB?

3 mins read

This section will explain how to import image from SQL database and then edit the image using SfImageEditor then save the image to DB

 

Step 1: Create image editor sample with all necessary assemblies and add desired image in sample. You need to include following NuGet and assemblies to access SQL command in Xamarin.Forms

 

NuGet for PCL:

 

SQLite.Net-PCL

 

Assemblies for UWP:

 

SQLite for Universal App Platform

 

Step 1: Create SQL connection and Get the path of SQL database as like in the below code snippet:

 

public interface IDBInterface
    {
        SQLiteConnection CreateConnection();
        String GetPath();
    }

 

 

Android

 

[assembly: Dependency(typeof(DatabaseService))]
 
public class DatabaseService : IDBInterface
    {
        public DatabaseService()
        {
        }
        string location;
        public SQLite.Net.SQLiteConnection CreateConnection()
        {
 
            var sqliteFilename = "image.db";
            var file =
               new Java.IO.File(
                       Android.OS.Environment.GetExternalStoragePublicDirectory(Android.OS.Environment.DirectoryPictures)
                           .ToString());
            if (!file.Exists())
                file.Mkdir();
            var path = System.IO.Path.Combine(file.ToString(),
                                                    sqliteFilename);
            location = path;
            var plat = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
            var conn = new SQLite.Net.SQLiteConnection(plat, path);
            return conn;
 
 
        }
 
 
        public string GetPath()
        {
 
            return location;
        }
    }
 

 

iOS

 

[assembly: Dependency(typeof(DatabaseService))]
 
public class DatabaseService : IDBInterface
    {
        public DatabaseService()
        {
        }
        string location;
        public SQLite.Net.SQLiteConnection CreateConnection()
        {
            var fileName = "Image.db";
            var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            var libraryPath = Path.Combine(documentsPath, "..", "Library");
            var path = Path.Combine(libraryPath, fileName);
            location = path;
            var platform = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();
            var connection = new SQLite.Net.SQLiteConnection(platform, path);
 
            return connection;
 
 
        }
        public string GetPath()
        {
            return location;
        }
    }
 
 

 

UWP

 

[assembly: Dependency(typeof(DatabaseService))]
 
public class DatabaseService : IDBInterface
    {
        public DatabaseService()
        {
        }
        string location;
        public SQLite.Net.SQLiteConnection CreateConnection()
        {
            
            var filename = "Image.db";
            var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, filename);
            location = path; 
            var platfrom = new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT();
            var connection = new SQLite.Net.SQLiteConnection(platfrom, path);
            return connection;
        }
        public string GetPath()
        {
            return location;
        }
    }

 

 

Step 2: To save the image into SQL table, you need to create SQLtable with the fields id and byte array of image as like following

 

 

public class SaveImage
    {
        [PrimaryKey, AutoIncrement]
        public int id { get; set; }
        public byte[] Array { get; set; }
        public SaveImage()
        {
        }
    }
 

 

 

Step 3: To call the DependencyService method to create SQL connection and create table in sql data base as like below code snippet

 

 

 
private SQLiteConnection _sqlconnection;  
_sqlconnection = DependencyService.Get<IDBInterface>().CreateConnection();
_sqlconnection.CreateTable<SaveImage>();
 
 

 

 

Step 4: To Save the image byte array into sql table, you need to get stream from image saving method and convert this stream into byte array as like below code snippet

 

 

editor.ImageSaving += ImageSaving;
 
void ImageSaving(Object sender, ImageSavingEventArgs args)
        {
            var stream = args.Stream;
            array = ConvertStreamtoByte(stream);
            InsertImage(array);
        }
 
public static byte[] ConvertStreamtoByte(Stream input)
        {
            using (var ms = new MemoryStream())
            {
                input.CopyTo(ms);
                return ms.ToArray();
            }
        }
 
public void InsertImage(byte[] array)
        {
            string query = "insert into SaveImage (Array) values (@Array)";
            var cmd = _sqlconnection.CreateCommand(query, array);
            cmd.CommandText = "INSERT INTO SaveImage(Array) VALUES (@Array)";
            cmd.ExecuteNonQuery();
            var path = DependencyService.Get<IDBInterface>().GetPath();
            DisplayAlert("Saved In Location", path, "OK");
 
        }
 
 
 

 

 

Step 5: To call the Dependency service method to get the path of image stored database as like below code snippet

 

 

          
            var path = DependencyService.Get<IDBInterface>().GetPath();
            DisplayAlert("Saved In Location", path, "OK");
 

 

 

Step 6:  To retrieve image from particular row in sql data base you need to execute sql query as like this

 

 
int id = 1;
var array= _sqlconnection.Table<SaveImage>().FirstOrDefault(t =>t.id == id).Array;
 

 

 

 

Sample Link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/SQLiteDBLoad-1979608714.zip 

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied