SQLite数据库应用说明

以下内容全部或部分转自或参考于网络。

一、首先介绍查看AVD模拟器中的数据库文件方法

1.启动命令窗口（点击VS中下图所示图标）

2.执行adb shell命令

3.cd 进入相应目录，ls查看内容

4.利用sqlite3命令打开数据库

5.运行".help"可以获得操作数据库的指令帮助

二、操作SQLite数据库

在项目中添加两个引用System.Data , System.Data.SQLite

usingSystem;
using Android.App;
using Android.Content;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Android.OS;
using System.IO;
using Mono.Data.Sqlite;
namespace TestSQLite
{
    [Activity(Label = "TestSQLite", MainLauncher = true, Icon = "@drawable/icon")]
    public class MainActivity : Activity
    {
        int count = 1;
        private static string databaseName = "db_user.db";
        private static string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
        private static string db = Path.Combine(documents, databaseName);
        TextView tv;
        protected override void OnCreate(Bundle bundle)
        {
            base.OnCreate(bundle);
            // Set our view from the "main" layout resource
            SetContentView(Resource.Layout.Main);
            tv = FindViewById<TextView>(Resource.Id.tv);
            //创建数据库
            Button btnCreateDB = FindViewById<Button>(Resource.Id.btnCreateDB);
            btnCreateDB.Click += delegate
            {
                bool exists = File.Exists(db);
                if (!exists)
                {
                    SqliteConnection.CreateFile(db);
                }
            };
            //创建表
            Button btnCreateTB = FindViewById<Button>(Resource.Id.btnCreateTB);
            btnCreateTB.Click += delegate
            {
                var connStr = "Data Source=" + db;
                var sql = "CREATE TABLE Users (ID INTEGER PRIMARY KEY AUTOINCREMENT , Name VARCHAR(32), Addr VARCHAR(32))";
                using (var conn = new SqliteConnection(connStr))
                {
                    using (var cmd = new SqliteCommand(sql, conn))
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        Toast.MakeText(this, "建立成功", ToastLength.Long).Show();
                    }
                }
            };
            //添加一条记录
            Button btnAdd = FindViewById<Button>(Resource.Id.btnAdd);
            btnAdd.Click += delegate
            {
                var connStr = "Data Source=" + db;
                string sql = "INSERT INTO Users(Name,Addr) VALUES(@name,@addr)";
                using (var conn = new SqliteConnection(connStr))
                {
                    using (var cmd = new SqliteCommand(sql, conn))
                    {
                        cmd.Parameters.Add(new SqliteParameter("@name", "lisi"));
                        cmd.Parameters.Add(new SqliteParameter("@addr", "shanghai"));
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        Toast.MakeText(this, "数据写入完成", ToastLength.Long).Show();
                    }
                }
            };
            //查询
            Button btnSelect = FindViewById<Button>(Resource.Id.btnSelect);
            btnSelect.Click += (s,e)=>
            {
                var connStr = "Data Source=" + db;
                var sql = "select Addr from Users where Name = @Name";
                var conn = new SqliteConnection(connStr);
                try
                {
                    using (var cmd = new SqliteCommand(sql, conn))
                    {
                        cmd.Parameters.Add(new SqliteParameter("@Name", "lisi"));
                        conn.Open();
                        SqliteDataReader sdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                        while (sdr.Read())
                        {
                            tv.Text = Convert.ToString(sdr["Addr"]);
                        }
                    }
                }
                catch (System.Exception ex)
                {
                    tv.Text = ex.Message;
                }
            };
        }
    }

}