mono for android mysql_mono for android中使用dapper或petapoco对sqlite进行数据操作

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingAndroid.App;usingAndroid.Content;usingAndroid.OS;usingAndroid.Runtime;usingAndroid.Views;usingAndroid.Widget;usingSystem.IO;usingMono.Data.Sqlite;usingSystem.Data;usingAndroid.Util;usingMicrosoft.CSharp;usingMono.CSharp;usingDapper;usingBaseModel;usingPetaPocoForMobile;namespacemonoandroid1.Controllers

{

[Activity(Label= "启动页", MainLauncher = true, Icon = "@drawable/icon")]public classStart : Activity

{privateTextView tips;privateEditText keyword;protected override voidOnCreate(Bundle bundle)

{base.OnCreate(bundle);//Create your application here

SetContentView(Resource.Layout.Main);

tips= FindViewById(Resource.Id.Results);

Button BtCreatDatabase= FindViewById(Resource.Id.CreatDatabase);

BtCreatDatabase.Click+= newEventHandler(CreatDatabase);

Button BtImportData= FindViewById(Resource.Id.ImportData);

BtImportData.Click+= delegate{ ImportData(); };

Button BtDataDisplay= FindViewById(Resource.Id.DataDisplay);

BtDataDisplay.Click+= delegate{

keyword= FindViewById(Resource.Id.Input);

tips.Text= DataDisplay(keyword.Text) == null ? "查询不到": DataDisplay(keyword.Text).UserName;

};

Button BtCheckDatabase= FindViewById(Resource.Id.CheckDatabase);

BtCheckDatabase.Click+= delegate{

CheckDatabase("/data/data/monoandroid1.monoandroid1/files/UserData.db3");

};

}///

///创建数据库///

///

///

protected void CreatDatabase(objectsender, 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 incommands)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();

}

}///

///返回数据库完整路径///

/// 数据库名

/// 返回数据库完整路径

private string GetDatabaseFilePath(stringdatabaseName)

{string documents =System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);string dbFilePath =Path.Combine(documents, databaseName);returndbFilePath;

}///

///检查数据库是否存在///

///

/// true不存在,false已经存在

private bool CheckDatabase(stringdbFilePath)

{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;

}

}privateSqliteConnection OpenDatabase()

{string databaseName = "UserData.db3";string dbFilePath =GetDatabaseFilePath(databaseName);

string connectionstr = "Data Source=" +dbFilePath;

SqliteConnection connection= newSqliteConnection(connectionstr);if (connection.State ==ConnectionState.Broken)

connection.Close();if (connection.State ==ConnectionState.Closed)

{

connection.Open();

}returnconnection;

}///

///导入数据///

protected voidImportData()

{//dapper版//List Dogs = new List();//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 = newPetaPocoForMobile.Database(connection))

{

List Dogs = new List();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 inDogs)

{

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)

{throwex;

}finally{

timespan= DateTime.Now.Subtract(starttime);//获取就是开始时间很结束时间差

if (connection.State !=ConnectionState.Closed)

{

connection.Close();

}

connection.Dispose();

tips.Text= "数据导入完成" +timespan.ToString();

}

}

}

}

}///

///根据ID查询单条数据///

///

///

protected Dog DataDisplay(stringkeyword)

{

SqliteConnection connection=OpenDatabase();//获取单条记录//var edog = connection.Query("select * from dog where id = @Id", new { Id = 2 }).Single();//var edog = connection.Query("select * from dog where UserId = @UserId", new { UserId = keyword }).SingleOrDefault();

try{var edog = connection.Query("select * from dog where UserId = @UserId", new { UserId = keyword }).SingleOrDefault();returnedog;

}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();

}

}

}

}