Mono for android 在Sqllite中存取图片示例

使用开发环境为VS2012

1、SqlLite辅助类

using System;
using System.Collections.Generic;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using Android.Content.Res;
using Android.Graphics;
using Mono.Data.Sqlite;
using Android.App;
using Android.Content;
using Java.IO;


namespace AndroidTest
{

    /// <summary>
    /// SqlLite辅助
    /// </summary>
    public class SqlLiteHelper
    {
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private static SqliteConnection _conn;

        /// <summary>
        /// 获取数据库访问连接
        /// </summary>
        /// <param name="db_fileName">数据库文件名</param>
        /// <returns>连接对象</returns>
        public static SqliteConnection GetConnection(string db_fileName)
        {
            string dbPath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), db_fileName);
            if (_conn == null)
                _conn = new SqliteConnection("Data Source=" + dbPath);
            return _conn;
        }
        
        /// <summary>
        /// 创建数据库文件
        /// </summary>
        /// <param name="db_fileName">数据库文件名</param>
        public static void CreateDataFile(string db_fileName)
        {
            string dbPath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), db_fileName);
            bool exists = System.IO.File.Exists(dbPath);
            if (!exists)
                SqliteConnection.CreateFile(dbPath);
        }

        /// <summary>
        /// 删除数据库文件
        /// </summary>
        /// <param name="db_fileName">数据库文件名</param>
        public static void DelDataFile(string db_fileName)
        {
            string dbPath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), db_fileName);
            bool exists = System.IO.File.Exists(dbPath);
            if (exists)
                System.IO.File.Delete(dbPath);
        }

        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <param name="conn">数据库连接对象</param>
        public static void CreateTable(SqliteConnection conn)
        {
            //Level地图层级
            //X索引
            //Y索引
            //Content描述
            //Modified时间
            //Img图像
            string sql = "CREATE TABLE PIC (Id INTEGER PRIMARY KEY AUTOINCREMENT, Level INTEGER, X DOUBLE, Y DOUBLE,  Content ntext, Modified datetime, Img BLOB);";
            conn.Open();
            using (SqliteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
            conn.Close();
        }

        

        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="conn">数据库连接对象</param>
        /// <param name="level">地图层级</param>
        /// <param name="x">X索引</param>
        /// <param name="y">Y索引</param>
        /// <param name="content">描述</param>
        /// <param name="bit">图像</param>
        public static void Insert(SqliteConnection conn, int level, double x, double y, string content, Bitmap bit)
        {
            string sql = "INSERT INTO PIC (Level, X, Y, Content, Modified, Img) VALUES (@Level, @X, @Y, @Content, @Modified, @Img);";

            conn.Open();
            using (SqliteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@Level", level);
                cmd.Parameters.AddWithValue("@X", x);
                cmd.Parameters.AddWithValue("@Y", y);
                cmd.Parameters.AddWithValue("@Content", content);
                cmd.Parameters.AddWithValue("@Modified", DateTime.Now);

                MemoryStream ms = new MemoryStream();
                bit.Compress(Bitmap.CompressFormat.Png, 100, ms);
                byte[] ba = ms.ToArray();
                cmd.Parameters.AddWithValue("@Img", ba);

                cmd.ExecuteNonQuery();
            }
            conn.Close();         
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="conn">数据库连接对象</param>
        /// <param name="level">地图层级</param>
        /// <param name="x">X索引</param>
        /// <param name="y">Y索引</param>
        /// <returns>图像</returns>
        public static Bitmap Select(SqliteConnection conn, int level, double x, double y)
        {
            string sql = "SELECT * FROM PIC where Level=@Level and X=@X and Y=@Y;";
            Bitmap bit = null;
            conn.Open();

            using (SqliteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@Level", level);
                cmd.Parameters.AddWithValue("@X", x);
                cmd.Parameters.AddWithValue("@Y", y);
                SqliteDataReader reader = cmd.ExecuteReader();
                
                while (reader.Read())
                {
                    byte[] ba = reader.GetValue(6) as byte[];  
                    bit = BitmapFactory.DecodeByteArray(ba, 0, ba.Length);
                    break;
                }
                conn.Close();           
            }
            return bit;
        }

    }
}

2、Activity类

<pre class="html" name="code">using System;

using Android.App;
using Android.Content;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Android.OS;
using Android.Graphics;

namespace AndroidTest
{
    [Activity(Label = "AndroidTest", MainLauncher = true, Icon = "@drawable/icon")]
    public class Activity1 : Activity
    {

        protected override void OnCreate(Bundle bundle)
        {
            base.OnCreate(bundle);

            // Set our view from the "main" layout resource
            SetContentView(Resource.Layout.Main);

            // Get our button from the layout resource,
            // and attach an event to it

            createBtn = FindViewById<Button>(Resource.Id.button5);
            createBtn.Click += createBtn_Click;

            delBtn = FindViewById<Button>(Resource.Id.button6);
            delBtn.Click += delBtn_Click;

            selectBtn = FindViewById<Button>(Resource.Id.button1);
            selectBtn.Click += selectBtn_Click;

            insertBtn = FindViewById<Button>(Resource.Id.button2);
            insertBtn.Click += insertBtn_Click;

            t1 = FindViewById<TextView>(Resource.Id.textView1);
            iv1 = FindViewById<ImageView>(Resource.Id.imageView1);
        }

        void insertBtn_Click(object sender, EventArgs e)
        {
            Bitmap bit = BitmapFactory.DecodeResource(Resources, Resource.Drawable.asteroid01);
            SqlLiteHelper.Insert(SqlLiteHelper.GetConnection(_db_dileName), 1, 2, 2, "test", bit);
            t1.Text = "数据插入完成";
        }

        void selectBtn_Click(object sender, EventArgs e)
        {
            Bitmap bit = SqlLiteHelper.Select(SqlLiteHelper.GetConnection(_db_dileName), 1, 2, 2);
            iv1.SetImageBitmap(bit);
        }

        void delBtn_Click(object sender, EventArgs e)
        {
            SqlLiteHelper.DelDataFile(_db_dileName);
            t1.Text = "完成数据文件的删除";
        }

        void createBtn_Click(object sender, EventArgs e)
        {
            SqlLiteHelper.CreateDataFile(_db_dileName);
            SqlLiteHelper.CreateTable(SqlLiteHelper.GetConnection(_db_dileName));
            t1.Text = "完成数据文件及表的创建";
        }

        /// <summary>
        /// 创建按钮
        /// </summary>
        Button createBtn;
        /// <summary>
        /// 删除数据库按钮
        /// </summary>
        Button delBtn;
        /// <summary>
        /// 获取按钮
        /// </summary>
        Button selectBtn;
        /// <summary>
        /// 插入按钮
        /// </summary>
        Button insertBtn;
        /// <summary>
        /// 文本
        /// </summary>
        TextView t1;   
        /// <summary>
        /// 图片框
        /// </summary>
        ImageView iv1;
        /// <summary>
        /// 数据库文件名
        /// </summary>
        string _db_dileName = "test.db3";
    }
}

3、Main.axml

<pre class="html" name="code"><?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:p1="http://schemas.android.com/apk/res/android"
    p1:orientation="vertical"
    p1:minWidth="25px"
    p1:minHeight="25px"
    p1:layout_width="match_parent"
    p1:layout_height="match_parent"
    p1:id="@+id/linearLayout1">
    <LinearLayout
        p1:orientation="horizontal"
        p1:minWidth="25px"
        p1:minHeight="25px"
        p1:layout_width="match_parent"
        p1:layout_height="63.2dp"
        p1:id="@+id/linearLayout2">
        <LinearLayout
            p1:orientation="horizontal"
            p1:minWidth="25px"
            p1:minHeight="25px"
            p1:layout_width="317.5dp"
            p1:layout_height="match_parent"
            p1:id="@+id/linearLayout5"
            p1:layout_marginRight="0.0dp"
            p1:layout_gravity="fill">
            <TextView
                p1:text="Medium Text"
                p1:textAppearance="?android:attr/textAppearanceMedium"
                p1:layout_width="321.3dp"
                p1:layout_height="match_parent"
                p1:id="@+id/textView1"
                p1:layout_marginRight="0.0dp" />
        </LinearLayout>
    </LinearLayout>
    <LinearLayout
        p1:orientation="horizontal"
        p1:minWidth="35px"
        p1:minHeight="35px"
        p1:layout_width="match_parent"
        p1:layout_height="371.1dp"
        p1:id="@+id/linearLayout3">
        <ImageView
            p1:src="@android:drawable/ic_menu_gallery"
            p1:layout_width="320.2dp"
            p1:layout_height="match_parent"
            p1:id="@+id/imageView1"
            p1:layout_marginRight="0.0dp" />
    </LinearLayout>
    <LinearLayout
        p1:orientation="horizontal"
        p1:minWidth="25px"
        p1:minHeight="25px"
        p1:layout_width="match_parent"
        p1:layout_height="49.6dp"
        p1:id="@+id/linearLayout4"
        p1:layout_marginTop="0.0dp">
        <LinearLayout
            p1:orientation="horizontal"
            p1:minWidth="25px"
            p1:minHeight="25px"
            p1:layout_width="wrap_content"
            p1:layout_height="match_parent"
            p1:id="@+id/linearLayout7">
            <Button
                p1:text="获取"
                p1:layout_width="match_parent"
                p1:layout_height="match_parent"
                p1:id="@+id/button1"
                p1:clickable="true"
                p1:layout_marginRight="0.0dp" />
        </LinearLayout>
        <LinearLayout
            p1:orientation="horizontal"
            p1:minWidth="25px"
            p1:minHeight="25px"
            p1:layout_width="wrap_content"
            p1:layout_height="match_parent"
            p1:id="@+id/linearLayout8">
            <Button
                p1:text="插入"
                p1:layout_width="match_parent"
                p1:layout_height="match_parent"
                p1:id="@+id/button2"
                p1:layout_marginLeft="0.0dp" />
        </LinearLayout>
        <LinearLayout
            p1:orientation="horizontal"
            p1:minWidth="25px"
            p1:minHeight="25px"
            p1:layout_width="wrap_content"
            p1:layout_height="match_parent"
            p1:id="@+id/linearLayout9">
            <Button
                p1:text="更新"
                p1:layout_width="match_parent"
                p1:layout_height="match_parent"
                p1:id="@+id/button4"
                p1:layout_marginRight="0.0dp" />
        </LinearLayout>
        <LinearLayout
            p1:orientation="horizontal"
            p1:minWidth="25px"
            p1:minHeight="25px"
            p1:layout_width="wrap_content"
            p1:layout_height="match_parent"
            p1:id="@+id/linearLayout10">
            <Button
                p1:text="创建"
                p1:layout_width="match_parent"
                p1:layout_height="match_parent"
                p1:id="@+id/button5" />
        </LinearLayout>
        <Button
            p1:text="删除"
            p1:layout_width="wrap_content"
            p1:layout_height="match_parent"
            p1:id="@+id/button6" />
    </LinearLayout>
</LinearLayout>