mono for android中使用dapper或petapoco对sqlite进行数据操作
在mono for android中使用dapper或petapoco,很简单,新建android 类库项目,直接把原来的文件复制过来,对Connection连接报错部分进行注释和修改就可以运行了.(用可移植类库PCL项目也可以的.)
如果需要源码可以联系我.10元收费哈..
以下内容包括
1.在安卓中创建sqlite数据库;
2.检查数据库是否存在;
3.返回数据库路径;
4.插入数据(前置知识,dapper的使用方法或petapoco的);
5.查询数据;
本人小菜鸟一枚,以下代码写得很烂,希望有好的想法可以指点下我.
知识点提示
1.安卓sqlite数据提交,默认每一次都是事务提交,所以如果不采用一次性全部事务提交,会造成很多事务的建立.也就是把提交部分,用一个事务包起来,不要不用事务.否则很慢.
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(); } } } }
