c# - SQLite, check if Database Exist and have correct data, More efficient way

Question

Im making a Xamarin.Android application and i use SQLite for some images (300+). I store the images as Templates in a group of 5 images like this:

    [Table("Templates")]
    public class Template
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public string Name { get; set; }
        public int Category { get; set; }
        [OneToMany]
        public List<TemplateImage> TemplateImages { get; set; }
        public string ImagesHash { get; set; }
    }
    [Table("TemplateImages")]
    public class TemplateImage
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public int Category { get; set; }
        public int Image { get; set; }
        [ForeignKey(typeof(Template))]
        public int TemplateId { get; set; }
    }

And the first time the App is launched i use this code To check if the Database exist or Create the DB:

public static void AddTemplate(int category, List<int> images)
        {
            var tmpl = new Template()
            {
                Category = category,
            };
            if (images != null)
            {
                var img1 = new TemplateImage()
                {
                    Category = category,
                    Image = images[0],
                };
                var img2 = new TemplateImage()
                {
                    Category = category,
                    Image = images[1],
                };
                var img3 = new TemplateImage()
                {
                    Category = category,
                    Image = images[2],
                };
                var img4 = new TemplateImage()
                {
                    Category = category,
                    Image = images[3],
                };
                var img5 = new TemplateImage()
                {
                    Category = category,
                    Image = images[4],
                };
                tmpl.TemplateImages = new List<TemplateImage>() { img1, img2, img3, img4, img5 };
            }
            tmpl.ImagesHash = Guid.NewGuid().ToString();

            DatabaseHelper.Db().InsertAll(tmpl.TemplateImages);
            DatabaseHelper.Db().Insert(tmpl);
            DatabaseHelper.Db().UpdateWithChildren(tmpl);
        }

        public static List<Template> GetAllTemplates()
        {
            return DatabaseHelper.Db().GetAllWithChildren<Template>();
        }

        public static Template GetTemplate(int id)
        {
            var result = DatabaseHelper.Db().GetWithChildren<Template>(id);
            if (result == null)
            {
                result = DatabaseHelper.Db().GetAllWithChildren<Template>().Where(record => record.Category == (int)TemplateCategory.Emojis).OrderBy(record => record.Id).FirstOrDefault();
            }
            return result;
        }

public static void CreateDB()
        {
            DatabaseHelper.Db().RunInTransaction(() =>
            {
                DatabaseHelper.Db().CreateTable<Template>();
                DatabaseHelper.Db().CreateTable<TemplateImage>();
                if (DatabaseHelper.Db().Table<Template>().Count() != 0)
                {
                    return;
                }
                AddTemplate(

                    (int)TemplateCategory.Emojis,
                    new List<int>
                    {
                Resource.Drawable.ic_angry,
                Resource.Drawable.ic_sad,
                Resource.Drawable.ic_neutral,
                Resource.Drawable.ic_happy,
                Resource.Drawable.ic_smiling,
                    }
                );
                AddTemplate(

    (int)TemplateCategory.Emojis,
     new List<int>
                {
                Resource.Drawable.ic_emoji_01_1,
                Resource.Drawable.ic_emoji_01_2,
                Resource.Drawable.ic_emoji_01_3,
                Resource.Drawable.ic_emoji_01_4,
                Resource.Drawable.ic_emoji_01_5,
    }
    );

...//AND many more AddTemplate() functions like those 2.
}

This was working great until i saw one day in a very rare situation that sometimes the application was showing.. other images from those i have select it a random way. So i realised that the compiler for some reasons is deciding to change the ResourceId of the ImageDrawables and so, despite the fact that my DB exist, it is completely wrong,

So i have start thinking again what is the correct way of doing something like that, without recreating every time the database, i found the idea of recreating the DB every time a bad one, but if it is the only correct way and the only way that i can be 100% sure for the result i will keep it.

I have see from my tests that usually the first sets of images are getting changed (The first Templates) and i was thinking if it is a good idea to do something like this:

public static bool ExistingDBItemsFound(int id)
        {
            var returnValue = false;
            var result = GetTemplate(1);
            if (result.TemplateImages.Count == 5)
            {
                if (result.TemplateImages[0].Image == Resource.Drawable.ic_angry
                    && result.TemplateImages[1].Image == Resource.Drawable.ic_sad
                    && result.TemplateImages[1].Image == Resource.Drawable.ic_neutral
                    && result.TemplateImages[1].Image == Resource.Drawable.ic_happy
                    && result.TemplateImages[1].Image == Resource.Drawable.ic_smiling)
                    returnValue = true;
            }
            return returnValue;
        }

But if i do something like this how i can be sure that all the rest of the resource ids are correct? i only checking 5 of 300+.

Can i avoid recreate every time the database items of app creation? i yes how?

Is there a way to save a resource in the database in another format rather than resourceId?

标签： c#androidsqlitexamarinxamarin.android

Answer 1

永远不要将资源 ID 用作唯一标识符，因为编译器有时很容易更改它们，因此最好的方法是完全避免使用它们。

我的问题的一种解决方案可能是使用此处建议的答案：Best way to store Resource id in Database on Android

获取图标名称

var iconName = Context.Resources.GetResourceEntryName( Resource.Drawable.your_icon );

然后从名称中获取 id：

var id = Context.Resources.GetIdentifier( iconName, "drawable", Context.PackageName )

这样，即使 Resource Id 改变了，我们也会得到正确的图像，只要我们不重命名我们的资源。

如果您还想检查用户数据库是否具有正确的值作为您的 apk 数据库，您可以执行@martin venter 建议的操作并以某种方式创建哈希（例如：资源名称的哈希）并比较它们