mono for androidでdapperまたはpetapocoを使用してsqliteをデータ操作

31656 ワード

mono for androidでdapperやpetapocoを使うのは簡単で、androidクラスライブラリプロジェクトを新規作成して、そのまま元のファイルをコピーして、Connection接続エラー部分に注釈や修正をすれば実行できます.(移植可能クラスライブラリPCLプロジェクトでもいいです.)
もしソースコードが必要なら、私に連絡してください.10元の料金がかかります.
 
以下の内容は次のとおりです.
1.アンドロイドでsqliteデータベースを作成する.
2.データベースが存在するかどうかを確認する.
3.データベースパスを返します.
4.データを挿入する(前置知識、dapperの使用方法またはpetapocoの);
5.データの照会
私のおかずの鸟は1枚、以下のコードはとてもぼろぼろで、良い考えが私を指导することができることを望みます.
 
ナレッジポイントヒント
1.Android sqliteデータの提出は、デフォルトでは毎回トランザクションの提出なので、一度にすべてのトランザクションの提出を採用しないと、多くのトランザクションの確立をもたらします.つまり、提出部分を1つのトランザクションで包んで、トランザクションを使わないでください.そうしないと遅いです.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;

using System.IO;
using Mono.Data.Sqlite;
using System.Data;
using Android.Util;
using Microsoft.CSharp;
using Mono.CSharp;
using Dapper;
using BaseModel;
using PetaPocoForMobile;

namespace monoandroid1.Controllers
{
    [Activity(Label = "   ", MainLauncher = true, Icon = "@drawable/icon")]
    public class Start : Activity
    {
        private TextView tips;
        private EditText keyword;

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

            // Create your application here
            SetContentView(Resource.Layout.Main);
            tips = FindViewById<TextView>(Resource.Id.Results);

            Button BtCreatDatabase = FindViewById<Button>(Resource.Id.CreatDatabase);
            BtCreatDatabase.Click += new EventHandler(CreatDatabase);

            Button BtImportData = FindViewById<Button>(Resource.Id.ImportData);
            BtImportData.Click += delegate { ImportData(); };

            Button BtDataDisplay = FindViewById<Button>(Resource.Id.DataDisplay);
            BtDataDisplay.Click += delegate
            {
                keyword = FindViewById<EditText>(Resource.Id.Input);
                tips.Text = DataDisplay(keyword.Text) == null ? "    " : DataDisplay(keyword.Text).UserName;
            };

            Button BtCheckDatabase = FindViewById<Button>(Resource.Id.CheckDatabase);
            BtCheckDatabase.Click += delegate
            {
                CheckDatabase("/data/data/monoandroid1.monoandroid1/files/UserData.db3");
            };
        }
        /// <summary>
        ///      
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void CreatDatabase(object sender, EventArgs e)
        {
            string databaseName = "UserData.db3";
            string dbFilePath = GetDatabaseFilePath(databaseName);
            bool existsDB = CheckDatabase(dbFilePath);
            if (existsDB == true)
            {
                SqliteConnection.CreateFile(dbFilePath);
            }

            var conn = new SqliteConnection("Data Source=" + dbFilePath);
            var commands = new[]
            {
                "drop table if exists dog",
                "create table if not exists dog (UserId integer primary key autoincrement, UserName varchar(20),Age int,Address varchar(50))",
            };
            try
            {
                if (conn.State == ConnectionState.Broken)
                    conn.Close();
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                foreach (string cmd in commands)
                    using (SqliteCommand sqlitecmd = conn.CreateCommand())
                    {
                        sqlitecmd.Connection = conn;
                        sqlitecmd.CommandText = cmd;
                        sqlitecmd.CommandType = CommandType.Text;
                        //conn.Open();
                        sqlitecmd.ExecuteNonQuery();
                        //conn.Close();
                    }

                tips.Text = "       ";
            }
            catch (System.Exception sysExc)
            {
                tips.Text = "Exception: " + sysExc.Message;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
        }

        /// <summary>
        ///          
        /// </summary>
        /// <param name="databaseName">    </param>
        /// <returns>         </returns>
        private string GetDatabaseFilePath(string databaseName)
        {
            string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            string dbFilePath = Path.Combine(documents, databaseName);
            return dbFilePath;
        }

        /// <summary>
        ///          
        /// </summary>
        /// <param name="dbFilePath"></param>
        /// <returns>true   ,false    </returns>
        private bool CheckDatabase(string dbFilePath)
        {
            bool exists = File.Exists(dbFilePath);
            if (!exists)
            {
                //SqliteConnection.CreateFile(dbFilePath);
                tips.Text = "      ";
                Log.Info("CreateFile", "        ,      ");
                return true;
            }
            else
            {
                tips.Text = "       ";
                Log.Info("CreatFile", "       ,    ");
                return false;
            }
        }
        private SqliteConnection OpenDatabase()
        {
            string databaseName = "UserData.db3";
            string dbFilePath = GetDatabaseFilePath(databaseName);
            //<add name="ConnectionStringName" connectionString="sever=    ;database=    ;User ID=  ;Password=  "  providerName="System.Data.SqlClient" />
            //<add name="ConnectionStringName" connectionString="Data Source=    ;Initial Catalog=    ;User ID=  ;Password=  "  providerName="System.Data.SqlClient" /> 
            string connectionstr = "Data Source=" + dbFilePath;
            SqliteConnection connection = new SqliteConnection(connectionstr);
            if (connection.State == ConnectionState.Broken)
                connection.Close();
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            return connection;
        }
        /// <summary>
        ///     
        /// </summary>
        protected void ImportData()
        {
            //dapper 
            //            List<Dog> Dogs = new List<Dog>();
            //            for (int i = 0; i < 100000; i++)
            //            {

            //                Dogs.Add(new Dog { UserId = i, UserName = "DogName" + i.ToString(), Age = i, Address = "ADD" + i.ToString()  });

            //            }
            //            string sql ="INSERT INTO dog (UserId,UserName,Age,Address) VALUES (@UserId,@UserName,@Age,@Address)";
            //            using (SqliteConnection connection = OpenDatabase())
            //            {
            //                //SqlConnection connection = GetOpenConnection(sqlconnectionString);
            //                //  100000       
            //                DateTime starttime = DateTime.Now;
            //                TimeSpan timespan;
            //                int records = 0;

            //                using (var trans = connection.BeginTransaction())
            //                {
            //                    try
            //                    {
            //                        records += connection.Execute(sql, Dogs, trans, 30, CommandType.Text);
            ////                         for (int i = 0; i < 100000; i++)
            ////                         {
            ////                             connection.Execute("INSERT INTO dog (UserId,UserName,Age,Address) VALUES (@UserId,@UserName,@Age,@Address)",
            ////                                 new { );//System.Guid.NewGuid().ToString()        (GUID)           ,            。
            ////                         }
            //                    }
            //                    catch (DataException ex)
            //                    {
            //                        trans.Rollback();
            //                        throw ex;
            //                    }
            //                    finally
            //                    {
            //                        trans.Commit();
            //                        timespan = DateTime.Now.Subtract(starttime);//              
            //                        if (connection.State != ConnectionState.Closed)
            //                        {
            //                            connection.Close();
            //                        }
            //                        connection.Dispose();
            //                        tips.Text = "      " + timespan.ToString();
            //                    }
            //                }
            //            }

            //petapoco 

            //var db = new PetaPocoForMobile.Database(OpenDatabase());
            //SqliteConnection connection = OpenDatabase()
            using (SqliteConnection connection = OpenDatabase())
            {
                int records = 0;
                using (var db = new PetaPocoForMobile.Database(connection))
                {
                    List<Dog> Dogs = new List<Dog>();
                    for (int i = 0; i < 10000; i++)
                    {
                        Dogs.Add(new Dog { UserId = i, UserName = "DogName" + i.ToString(), Age = i, Address = "ADD" + i.ToString() });
                    }
                    DateTime starttime = DateTime.Now;
                    TimeSpan timespan;
                    using (var ts = db.GetTransaction())
                    {
                        try
                        {
                            //  100000       
                            foreach (Dog item in Dogs)
                            {
                                db.Insert("dog", "UserId", item);//       ,     
                                //db.Execute("INSERT INTO dog (UserId,UserName,Age,Address) VALUES (@0,@1,@2,@3)", item.UserId, item.UserName, item.Age, item.Address);
                            }
                            ts.Complete();
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        finally
                        {
                            timespan = DateTime.Now.Subtract(starttime);//              
                            if (connection.State != ConnectionState.Closed)
                            {
                                connection.Close();
                            }
                            connection.Dispose();
                            tips.Text = "      " + timespan.ToString();
                        }
                    }
                }

            }

        }



        /// <summary>
        ///   ID      
        /// </summary>
        /// <param name="keyword"></param>
        /// <returns></returns>
        protected Dog DataDisplay(string keyword)
        {
            SqliteConnection connection = OpenDatabase();
            //      
            //var edog = connection.Query<Dog>("select * from dog where id = @Id", new { Id = 2 }).Single<Dog>();
            //var edog = connection.Query<Dog>("select * from dog where UserId = @UserId", new { UserId = keyword }).SingleOrDefault<Dog>();
            try
            {
                var edog = connection.Query<Dog>("select * from dog where UserId = @UserId", new { UserId = keyword }).SingleOrDefault<Dog>();
                return edog;
            }
            catch (System.Exception sysExc)
            {
                //tips.Text = "Exception: " + sysExc.Message;
                //Log.Info("e", "Exception: " + sysExc.Message);

                return null;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
                connection.Dispose();
            }
        }


    }
}