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