Xamarin.Forms copy local SQLite database

 

The three-day holiday on New Year's day in 2022 is free.

Think of browsing the two news collected by the blog Garden more than a month ago. First, on November 9, 2021, Microsoft released the fastest so far NET version - NET6, the second is the official version of Visual Studio 2022, which is known as the strongest IDE in the universe, was officially released on the same day. For a moment, I want to use xamarin Forms to make a mobile application.

Introduction: about Lao Li Tou who is over half a hundred years old

What is A temporary itch? I am A programming enthusiast over 50 years old (born in 1971). I began to contact computers in about 92. At that time, the computer was called 286 or 386, and most of the memory was 32K or 64K. The storage medium is A kind of thing called floppy disk. It seems that the reason why today's computer starts from disk C is that disk A and disk B were floppy disk drives in those years. The two floppy disk drives are one large and one small, the small three inch disk is 1.44M, and the large five inch disk has A capacity of 512K. The highlight of that year was to write A set of printable unit payroll in BASIC language and A tool called DBASIC III.

In the mid-1990s, he began to contact programming, using Pascal language, Delphi3 and SQL server 6 5 database to develop Windows desktop applications, and has won the third prize of provincial and ministerial scientific and technological achievements. By the early 1920s, ASP and other scripting languages were gradually popular, and the development methods were also divided into C/S mode and B/S mode. No one could have imagined that JavaScript and mysql, which were insignificant in those years, could be so popular today. Borland company also gradually lost in the war with Microsoft. The main reason is that it didn't follow up and learn for some personal reasons. Twenty years have passed. I read a book called the legend of Borland that year, which recorded many legendary stories of the technology giant in that year. I don't know how many developers today have read it. Taking history as a mirror, who will be Borland today? Who will be Microsoft tomorrow? Thirty years later, who is in Hedong? Who is in Hexi? This may be a sorrow for programmers.

Why do mobile applications? Because I have never done web development and can't use any front-end development language, I have always heard that Microsoft has a great ideal to use net unifies the Jianghu. For me, I have always paid more attention to being able to write web pages in c#. Not long ago, Microsoft launched a new UI framework blazor. Zhihu said: "blazor is based on C #. The main development tool is C # (without Javascript), which is very friendly to. net programmers. There is no need to learn different technologies for the front end. At the same time, the code based on C # can also communicate well with the original Javascript code." After the experiment, I was blinded by a variety of complex front-end technologies. Thinking that today's website development may be WinForm 20 years ago, I want to directly challenge cross platform mobile application development.

Start from scratch, Hello World.

 

Question: how do I copy the local database to the target environment

If you are a zero base mobile terminal developer like me, follow xamarin Forms quick start( Link address )The first Xamarin application will be generated in about 30 minutes. You can also download the examples directly( Link address ). The method of linking database and creating data table is as follows, isn't it very simple?

        public NoteDatabase(string dbPath)
        {
            database = new SQLiteAsyncConnection(dbPath);
            database.CreateTableAsync<Note>().Wait();
        }

 

But I hope App can provide query function to query the data in the database I have prepared. So the pit for zero foundation beginners like me came:

--------- try to copy the local database directly to the virtual machine to complete the query function. According to the traditional WinForm thinking, to directly copy the database to the target environment, you need to know the location of the database in the virtual machine. So the Android SDK was installed, but the DDMS tool was not found in the Android directory. Various ADB shell s hit several command lines in the console, but they are still not in / data / user / 0 / com companyname. In the notes / files / directory, you can see the notes created by App Db3 database. Finally, I understand the so-called application sandbox, the proprietary area that applications can use. By default, no application other than the operating system itself can access this area. So give up.

--------- try to insert the data directly into the created database. Write a database initialization class and insert the initialized data directly. A small amount of data may be a compromise (lazy) method, but for nearly 10000 initial data, even if the insert program can be started, it can't wait so long. So give up.

The above two attempts consumed two of the three-day vacation.

Continue to find Microsoft's Xamarin documents in the "Xamarin.Forms local database"( Link address )The page says: in many cases, you may need to copy the SQLite database: the database is provided with the application, but it must be copied or moved to the writable storage on the mobile device. It was quite clear, but no clear guidelines for follow-up operations were found.

Method: complete the copy operation through file stream

After reading a lot of articles in the blog Park (most of them were read when trying to directly copy the database to the Android virtual machine for the first time), I saw the ideas mentioned by Dennis Wang in the article "the difference between asset folder and raw folder in Android" (link address) on October 18, 2011.

Although I feel that this article ten years ago should be developed using the native Android of DSK, there are two clear implications: first, the files under Resource/Raw in Android will be saved intact in apk package after packaging and will not be compiled into binary. The second is to read the file resources under Resource/Raw and write by obtaining the input stream. The dawn is dawning and there is still one day left for the holiday. Continue to challenge. The final specific steps are as follows:

I. prepare local SQLite database files. First, in notes Create a new folder raw under the Resources directory of the Android project, and then send the notes containing the initial data to the local machine Db3 is copied to notes. Net in VS Raw and notes under Android project The Resources directory of the IOS project. Then rebuild the solution so that the Resources copied into the project can be compiled.

 

II. Build ISQLite interfaces and implement them respectively. 1. Create a new class ISQLite under Notes project cs.

using SQLite;

namespace Notes
{
    public interface ISQLite
    {
        SQLiteAsyncConnection GetConnection();
    }
}

2. In notes Open mainactivity.com under Android project cs. Add the following attributes: internal static mainactivity instance {get; private set;}

3. In notes Create a new class SQLite under Android project_ Android. cs

using System;
using Notes.Droid;
using Xamarin.Forms;
using System.IO;
using SQLite;

[assembly: Dependency(typeof(SQLite_Android))]

namespace Notes.Droid
{
    public class SQLite_Android : ISQLite
    { 
        public SQLiteAsyncConnection GetConnection()
        {
            var sqliteFilename = "Notes.db3";
            string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); 
            var path = Path.Combine(documentsPath, sqliteFilename);
            Console.WriteLine(path);
            if (!File.Exists(path))
            {
                var readStream = MainActivity.Instance.Resources.OpenRawResource(Resource.Raw.Notes);  
                FileStream writeStream = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
                int Length = 256;
                Byte[] buffer = new Byte[Length];
                int bytesRead = readStream.Read(buffer, 0, Length);
                while (bytesRead > 0)
                {
                    writeStream.Write(buffer, 0, bytesRead);
                    bytesRead = readStream.Read(buffer, 0, Length);
                }
                readStream.Close();
                writeStream.Close();
            }
            var conn = new SQLiteAsyncConnection(path);
            return conn;
        }
    }
}

4. In notes Create a new class SQLite under IOS project_ iOS. cs

using System;
using Notes.iOS;
using Xamarin.Forms;
using System.IO;
using SQLite;

[assembly: Dependency(typeof(SQLite_iOS))]

namespace Notes.iOS
{
    public class SQLite_iOS : ISQLite
    {
        public SQLite_iOS()
        {
        }
        public SQLiteAsyncConnection GetConnection()
        {
            var sqliteFilename = "Notes.db3";
            string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); 
            string libraryPath = Path.Combine(documentsPath, "..", "Library"); 
            var path = Path.Combine(libraryPath, sqliteFilename);

            // This is where we copy in the prepopulated database
            Console.WriteLine(path);
            if (!File.Exists(path))
            {
                File.Copy(sqliteFilename, path);
            }
            var conn = new SQLiteAsyncConnection(path);
            return conn;
        }
    }
}

III. modify notedatabase.under Notes item The constructor of the CS class is public NoteDatabase(string dbPath).

        readonly SQLiteAsyncConnection database;

        //public NoteDatabase(string dbPath)
        //{
        //    database = new SQLiteAsyncConnection(dbPath);
        //    database.CreateTableAsync<Note>().Wait();
        //}
        public NoteDatabase()
        {
            database = Xamarin.Forms.DependencyService.Get<ISQLite>().GetConnection();
            database.CreateTableAsync<Note>().Wait();
        }

IV. modify app. Under Notes item xaml. cs

        public static NoteDatabase Database
        {
            get
            {
                if (database == null)
                {
                    //database = new NoteDatabase(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "Notes.db3"));
                    database = new NoteDatabase();
                }
                return database;
            }
        }

During the three-day holiday, I did such a thing. It doesn't matter good or bad or success or failure. It's just a sentiment, which is recorded here. I hope it can give some reference to developers with the same problems.

-------------------The picture comes from the screenshot of the web page. In case of infringement, inform and delete it immediately-------------------