How to import and export image from SQL DB?
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
