public void RemoveMediaItemAtIndex(int index)
{
if (PlaylistId == 0)
{
return;
}
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
conn.ExecuteLogged("DELETE FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, index);
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition - 1 WHERE PlaylistId = ? AND ItemPosition > ?", PlaylistId, index);
conn.Commit();
}
catch (Exception e)
{
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
private Task <string> RunTaskInTransaction(Func <Task <string> > action)
{
return(Task.Run(async() =>
{
_sqliteConnection.BeginTransaction();
try
{
var result = await Task.Run(action);
if (!string.IsNullOrEmpty(result))
{
_sqliteConnection.Rollback();
}
else
{
_sqliteConnection.Commit();
}
return result;
}
catch (Exception ex)
{
_sqliteConnection.Rollback();
throw;
}
}));
}
/// <summary>
/// Get Cloud rows updated since lastSync and store them in LocalDB
/// </summary>
/// <typeparam name="T">Object type</typeparam>
/// <param name="lastSync">Date limit</param>
/// <param name="getID">Function to get Template ID</param>
async Task SyncLocalTable <T>(DateTime lastSync, Func <T, int> getID) where T : new()
{
// Trying to get all rows in the table T
//LastSynt Funcionality not Working YET------ Reason: Delete needs extra coding
var result = await MobileService.GetTable <T>().WithParameters(new Dictionary <string, string> {
{ "lastsync", lastSync.ToString("yyyy-MM-ddTHH:mm:sszzz", DateTimeFormatInfo.InvariantInfo) }
}).LoadAllAsync();
// var result = await MobileService.GetTable<T>().Take(_takeNRows).WithParameters(new Dictionary<string, string> { { "lastsync", lastSync.ToString("yyyy-MM-ddTHH:mm:sszzz", DateTimeFormatInfo.InvariantInfo) } }).ToListAsync();
string classname = typeof(T).Name;
_liteConnection.DeleteAll <T>();
/* //If id exists in local db delete
* foreach (var item in result)
* {
* _liteConnection.Delete(item);
* }*/
//Insert all
var storage = Mvx.Resolve <IMvxFileStore>();
// _liteConnection.Execute(string.Format("PRAGMA temp_store_directory = '{0}';", storage.NativePath(string.Empty)));
_liteConnection.BeginTransaction();
foreach (var tuple in result)
{
_liteConnection.Insert(tuple);
}
_liteConnection.Commit();
//Update sync table
var table_s = _liteConnection.Table <table_sync>().Where(t => t.table_name == classname).FirstOrDefault();
if (table_s != null)
{
table_s.synced_at = DateTime.UtcNow;
_liteConnection.Update(table_s);
}
else
{
Mvx.Trace("Table name not found in table_sync. Check SyncLocalTable Method line on WAMSRepositoryService");
throw new NullReferenceException();
}
}
public void MoveMediaItem(int fromIndex, int toIndex)
{
// make sure the input is within bounds and is not null
if (fromIndex >= PlaylistCount || fromIndex < 0 ||
toIndex < 0 || toIndex == fromIndex)
{
return;
}
logger.IfInfo("Moving media item");
ISQLiteConnection conn = null;
try {
// to do - better way of knowing whether or not a query has been successfully completed.
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
// Get the item out of the way to prevent constraint violations
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = " + Int32.MaxValue + " WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, fromIndex);
if (fromIndex > toIndex)
{
// Do this as a reversed loop because a single update statement can have a constraint violation
for (int position = fromIndex - 1; position >= toIndex; position--)
{
logger.IfInfo("Updating position " + position + " to " + (position + 1));
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition + 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position);
}
// conditional rollback here
}
else
{
// Do this as a reversed loop because a single update statement can have a constraint violation
for (int position = fromIndex + 1; position <= toIndex; position++)
{
logger.IfInfo("Updating position " + position + " to " + (position + 1));
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition - 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position);
}
// conditional rollback here
}
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ? WHERE PlaylistId = ? AND ItemPosition = ?", toIndex, PlaylistId, Int32.MaxValue);
// conditional rollback here
conn.Commit();
} catch (Exception e) {
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
} finally {
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
public CustomListAdapter(Activity context) //We need a context to inflate our row view from
: base()
{
this.context = context;
//For demo purposes we hard code some data here
//sqlite save
var myanimallist = new List <Animal>();
ISQLiteConnection conn = null;
ISQLiteConnection connactiond = null;
ISQLiteConnectionFactory factory = new MvxDroidSQLiteConnectionFactory();
ISQLiteConnectionFactory factoryd = new MvxDroidSQLiteConnectionFactory();
var sqlitedir = new Java.IO.File(global::Android.OS.Environment.GetExternalStoragePublicDirectory(global::Android.OS.Environment.DirectoryPictures), "Boruto");
string filenameaction = sqlitedir.Path + "/mysqliteaction.db";
connactiond = factoryd.Create(filenameaction);
connactiond.CreateTable <MyCheckbox>();
connactiond.CreateCommand("DELETE FROM MyCheckbox").ExecuteNonQuery();
connactiond.Dispose();
connactiond.Commit();
connactiond.Close();
if (File.Exists(filenameaction))
{
File.Delete(filenameaction);
}
string filename = sqlitedir.Path + "/mysqliteimage.db";
//Toast.MakeText(Application.Context, filename, ToastLength.Long).Show();
conn = factory.Create(filename);
conn.CreateTable <Myimage>();
var countidx = 0;
foreach (var e in conn.Table <Myimage>().Where(e => e.Date == "30-12-2016"))
{
var mystrarray = e.Imagepath.Split('/');
var myeleidx = mystrarray.Length - 1;
var newanialele = new Animal()
{
Name = mystrarray[myeleidx], Description = e.Imagepath, Image = e.Imagepath, Mycheckbox = countidx
};
myanimallist.Add(newanialele);
countidx++;
}
//Toast.MakeText(this, mycount.ToString(), ToastLength.Short).Show();
conn.Close();
this.items = myanimallist;
//sqlite save end
}
public void RemoveMediaItemAtIndexes(IList <int> indices)
{
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
// delete the items at the indicated indices
foreach (int index in indices)
{
logger.IfInfo("Deleting row at ItemPosition: " + index);
conn.ExecuteLogged("DELETE FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, index);
}
// select the id of all members of the playlist
var result = conn.Query <PlaylistItem>("SELECT * FROM PlaylistItem WHERE PlaylistId = ? ORDER BY ItemPosition", PlaylistId);
// update the values of each index in the array to be the new index
for (int i = 0; i < result.Count; i++)
{
var item = result[i];
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ? WHERE PlaylistItemId = ? AND PlaylistId = ?", i, item.PlaylistItemId, PlaylistId);
}
conn.Commit();
}
catch (Exception e)
{
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
public void InsertMediaItem(IMediaItem item, int index)
{
// make sure the input is within bounds and is not null
if (ReferenceEquals(item, null) || index > PlaylistCount || index < 0 || ReferenceEquals(PlaylistId, null))
{
return;
}
ISQLiteConnection conn = null;
try
{
int?id = Injection.Kernel.Get <IItemRepository>().GenerateItemId(ItemType.PlaylistItem);
if (!ReferenceEquals(id, null))
{
// to do - better way of knowing whether or not a query has been successfully completed.
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
for (int position = (int)PlaylistCount - 1; position >= index; position--)
{
logger.IfInfo("Updating position " + position + " to " + (position + 1));
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition + 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position);
}
// conditional rollback here
// Insert the new item
var playlistItem = new PlaylistItem();
playlistItem.PlaylistItemId = id;
playlistItem.PlaylistId = PlaylistId;
playlistItem.ItemType = item.ItemType;
playlistItem.ItemId = item.ItemId;
playlistItem.ItemPosition = index;
int affected = conn.Insert(playlistItem);
// conditional rollback here
if (affected > 0)
{
PlaylistCount++;
PlaylistDuration += (int)item.Duration;
LastUpdateTime = DateTime.UtcNow.ToUnixTime();
Md5Hash = CalculateHash();
conn.ExecuteLogged("UPDATE Playlist SET PlaylistName = ?, PlaylistCount = ?, PlaylistDuration = ?, Md5Hash = ?, LastUpdateTime = ? " +
"WHERE PlaylistId = ?", PlaylistName == null ? "" : PlaylistName, PlaylistCount, PlaylistDuration, Md5Hash, LastUpdateTime, PlaylistId);
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
catch (Exception e)
{
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
//Set the Activity's view to our list layout
SetContentView(Resource.Layout.ListActivity);
//sqlite for sas azure
ISQLiteConnection conn = null;
ISQLiteConnectionFactory factory = new MvxDroidSQLiteConnectionFactory();
var sqlitedir = new Java.IO.File(global::Android.OS.Environment.GetExternalStoragePublicDirectory(global::Android.OS.Environment.DirectoryPictures), "Boruto");
string filenameaction = sqlitedir.Path + "/mysqlitesas.db";
string filenameactionazure = sqlitedir.Path + "/mysqlitesasazure.db";
conn = factory.Create(filenameaction);
conn.CreateTable <Azurecon>();
var myuserid = "";
foreach (var e in conn.Table <Azurecon>())
{
myuserid = e.UserId;
}
conn.Close();
//sqlite for sas azure end
//Create our adapter
listAdapter = new CustomListAdapter(this);
//Find the listview reference
var listView = FindViewById <ListView>(Resource.Id.listView);
//Hook up our adapter to our ListView
listView.Adapter = listAdapter;
//Wire up the click event
//listView.ItemClick += new EventHandler<ItemEventArgs>(listView_ItemClick);
Button deleteselectedfiles = FindViewById <Button>(Resource.Id.deleteselectfiles);
deleteselectedfiles.Click += async delegate
{
//Delete files in sqlite table MyImage and in sdcard
var myfiledir = new Java.IO.File(global::Android.OS.Environment.GetExternalStoragePublicDirectory(global::Android.OS.Environment.DirectoryPictures), "Boruto");
ISQLiteConnection conncf = null;
ISQLiteConnection connactiondf = null;
ISQLiteConnectionFactory factorydf = new MvxDroidSQLiteConnectionFactory();
string sqlfilenamed = sqlitedir.Path + "/mysqliteaction.db";
//Toast.MakeText(Application.Context, filename, ToastLength.Long).Show();
connactiondf = factorydf.Create(sqlfilenamed);
connactiondf.CreateTable <MyCheckbox>();
List <string> myCollection = new List <string>();
var countidx = 0;
HashSet <string> myimgspath = new HashSet <string>();
foreach (var e in connactiondf.Table <MyCheckbox>())
{
string imgfilename = "file://" + sqlitedir.Path + "/" + e.Name;
myimgspath.Add(imgfilename);
var myfilepath = myfiledir + "/" + e.Name;
if (File.Exists(myfilepath))
{
File.Delete(myfilepath);
}
}
connactiondf.Close();
List <string> myimglistdeletecmd = new List <string>();
var myvaridx = 0;
foreach (string permyimg in myimgspath)
{
var myquerycmd = "Delete from Myimage where Imagepath = '" + permyimg + "'";
myimglistdeletecmd.Add(myquerycmd);
}
ISQLiteConnection connactioncf = null;
ISQLiteConnectionFactory factorycf = new MvxDroidSQLiteConnectionFactory();
var sqlitedirc = new Java.IO.File(global::Android.OS.Environment.GetExternalStoragePublicDirectory(global::Android.OS.Environment.DirectoryPictures), "Boruto");
string filenameactionc = sqlitedirc.Path + "/mysqliteimage.db";
connactioncf = factorycf.Create(filenameactionc);
connactioncf.CreateTable <Myimage>();
//var permyimgfile = new Myimage(){Date = "30-12-2016",Imagepath = myimgfile};
//myconn.CreateCommand("Delete from Myimage where Imagepath ='" + myimgfile + "'");
//myconn.Dispose();
//myconn.Commit();
//connactioncf.CreateCommand("Delete from Myimage where Imagepath ='" + "file:///storage/emulated/0/Pictures/Boruto/myPhoto_69d38ce2-0a96-41ed-884d-021a24890f88.jpg" + "'").ExecuteNonQuery();
foreach (var cmd in myimglistdeletecmd)
{
connactioncf.CreateCommand(cmd).ExecuteNonQuery();
}
connactioncf.Commit();
connactioncf.Close();
};
Button uploadtoazure = FindViewById <Button>(Resource.Id.uploadtoazure);
uploadtoazure.Click += async delegate
{
uploadtoazure.Text = string.Format("{0} clicks!", count++);
//Get userid from sqlite db file
ISQLiteConnection connacc = null;
ISQLiteConnectionFactory factoryacc = new MvxDroidSQLiteConnectionFactory();
var sqlitediracc = new Java.IO.File(global::Android.OS.Environment.GetExternalStoragePublicDirectory(global::Android.OS.Environment.DirectoryPictures), "Boruto");
string filenameactionacc = sqlitediracc.Path + "/mysqlitesas.db";
connacc = factoryacc.Create(filenameactionacc);
connacc.CreateTable <Azurecon>();
var useridconnc = "";
foreach (var e in connacc.Table <Azurecon>())
{
useridconnc = e.UserId;
}
connacc.Close();
//myuserid = "115708452302383620142";
var myurl = "http://93.118.34.239:8888/" + useridconnc;
Uri azureuri = new Uri(myurl);
HttpWebRequest request = new HttpWebRequest(azureuri);
request.Method = "GET";
HttpWebResponse response = request.GetResponse() as HttpWebResponse;
using (StreamReader sr = new StreamReader(response.GetResponseStream()))
{
string responseString = sr.ReadToEnd();
Toast.MakeText(this, "Connect SAS String:" + responseString, ToastLength.Short).Show();
try
{
await UseContainerSAS(responseString);
}
catch {
}
}
};
}