在 visual studio 2015 中使用 xamarin.forms 使用本地資料庫
SQlite 示例逐步說明
-
以下步驟演示瞭如何在 Xamarin.Forms 共享專案中包含此元件：在(pcl, Andriod, Windows, Ios)中新增包新增引用單擊 Manage Nuget packages - >單擊 Browse 以安裝 SQLite.Net.Core- 安裝完成後 PCL ， SQLite Net Extensions 在引用中檢查一次
-
在程式碼下面新增 Class Employee.cs
using SQLite.Net.Attributes; namespace DatabaseEmployeeCreation.SqlLite { public class Employee { [PrimaryKey,AutoIncrement] public int Eid { get; set; } public string Ename { get; set; } public string Address { get; set; } public string phonenumber { get; set; } public string email { get; set; } } } -
新增一個介面 ISQLite
using SQLite.Net;
//using SQLite.Net;
namespace DatabaseEmployeeCreation.SqlLite.ViewModel
{
public interface ISQLite
{
SQLiteConnection GetConnection();
}
}
- 為資料庫邏輯建立一個類，下面的程式碼方法如下。
使用 SQLite.Net; 使用 System.Collections.Generic; 使用 System.Linq; 使用 Xamarin.Forms; namespace DatabaseEmployeeCreation.SqlLite.ViewModel {public class DatabaseLogic {static object locker = new object（）; SQLiteConnection 資料庫;
public DatabaseLogic()
{
database = DependencyService.Get<ISQLite>().GetConnection();
// create the tables
database.CreateTable<Employee>();
}
public IEnumerable<Employee> GetItems()
{
lock (locker)
{
return (from i in database.Table<Employee>() select i).ToList();
}
}
public IEnumerable<Employee> GetItemsNotDone()
{
lock (locker)
{
return database.Query<Employee>("SELECT * FROM [Employee]");
}
}
public Employee GetItem(int id)
{
lock (locker)
{
return database.Table<Employee>().FirstOrDefault(x => x.Eid == id);
}
}
public int SaveItem(Employee item)
{
lock (locker)
{
if (item.Eid != 0)
{
database.Update(item);
return item.Eid;
}
else
{
return database.Insert(item);
}
}
}
public int DeleteItem(int Eid)
{
lock (locker)
{
return database.Delete<Employee>(Eid);
}
}
}
}
- 建立 xaml.forms EmployeeRegistration.xaml
<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
x:Class="DatabaseEmployeeCreation.SqlLite.EmployeeRegistration"
Title="{Binding Name}" >
<StackLayout VerticalOptions="StartAndExpand" Padding="20">
<Label Text="Ename" />
<Entry x:Name="nameEntry" Text="{Binding Ename}"/>
<Label Text="Address" />
<Editor x:Name="AddressEntry" Text="{Binding Address}"/>
<Label Text="phonenumber" />
<Entry x:Name="phonenumberEntry" Text="{Binding phonenumber}"/>
<Label Text="email" />
<Entry x:Name="emailEntry" Text="{Binding email}"/>
<Button Text="Add" Clicked="addClicked"/>
<!-- <Button Text="Delete" Clicked="deleteClicked"/>-->
<Button Text="Details" Clicked="DetailsClicked"/>
<!-- <Button Text="Edit" Clicked="speakClicked"/>-->
</StackLayout>
</ContentPage>
EmployeeRegistration.cs
using DatabaseEmployeeCreation.SqlLite.ViewModel;
using DatabaseEmployeeCreation.SqlLite.Views;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Xamarin.Forms;
namespace DatabaseEmployeeCreation.SqlLite
{
public partial class EmployeeRegistration : ContentPage
{
private int empid;
private Employee obj;
public EmployeeRegistration()
{
InitializeComponent();
}
public EmployeeRegistration(Employee obj)
{
this.obj = obj;
var eid = obj.Eid;
Navigation.PushModalAsync(new EmployeeRegistration());
var Address = obj.Address;
var email = obj.email;
var Ename = obj.Ename;
var phonenumber = obj.phonenumber;
AddressEntry. = Address;
emailEntry.Text = email;
nameEntry.Text = Ename;
//AddressEntry.Text = obj.Address;
//emailEntry.Text = obj.email;
//nameEntry.Text = obj.Ename;
//phonenumberEntry.Text = obj.phonenumber;
Employee empupdate = new Employee(); //updateing Values
empupdate.Address = AddressEntry.Text;
empupdate.Ename = nameEntry.Text;
empupdate.email = emailEntry.Text;
empupdate.Eid = obj.Eid;
App.Database.SaveItem(empupdate);
Navigation.PushModalAsync(new EmployeeRegistration());
}
public EmployeeRegistration(int empid)
{
this.empid = empid;
Employee lst = App.Database.GetItem(empid);
//var Address = lst.Address;
//var email = lst.email;
//var Ename = lst.Ename;
//var phonenumber = lst.phonenumber;
//AddressEntry.Text = Address;
//emailEntry.Text = email;
//nameEntry.Text = Ename;
//phonenumberEntry.Text = phonenumber;
// to retriva values based on id to
AddressEntry.Text = lst.Address;
emailEntry.Text = lst.email;
nameEntry.Text = lst.Ename;
phonenumberEntry.Text = lst.phonenumber;
Employee empupdate = new Employee(); //updateing Values
empupdate.Address = AddressEntry.Text;
empupdate.email = emailEntry.Text;
App.Database.SaveItem(empupdate);
Navigation.PushModalAsync(new EmployeeRegistration());
}
void addClicked(object sender, EventArgs e)
{
//var createEmp = (Employee)BindingContext;
Employee emp = new Employee();
emp.Address = AddressEntry.Text;
emp.email = emailEntry.Text;
emp.Ename = nameEntry.Text;
emp.phonenumber = phonenumberEntry.Text;
App.Database.SaveItem(emp);
this.Navigation.PushAsync(new EmployeeDetails());
}
//void deleteClicked(object sender, EventArgs e)
//{
// var emp = (Employee)BindingContext;
// App.Database.DeleteItem(emp.Eid);
// this.Navigation.PopAsync();
//}
void DetailsClicked(object sender, EventArgs e)
{
var empcancel = (Employee)BindingContext;
this.Navigation.PushAsync(new EmployeeDetails());
}
// void speakClicked(object sender, EventArgs e)
// {
// var empspek = (Employee)BindingContext;
// //DependencyService.Get<ITextSpeak>().Speak(empspek.Address + " " + empspek.Ename);
// }
}
}
-
在程式碼後面顯示 EmployeeDetails
using DatabaseEmployeeCreation; using DatabaseEmployeeCreation.SqlLite; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Xamarin.Forms; namespace DatabaseEmployeeCreation.SqlLite.Views { public partial class EmployeeDetails : ContentPage { ListView lv = new ListView(); IEnumerable<Employee> lst; public EmployeeDetails() { InitializeComponent(); displayemployee(); } private void displayemployee() { Button btn = new Button() { Text = "Details", BackgroundColor = Color.Blue, }; btn.Clicked += Btn_Clicked; //IEnumerable<Employee> lst = App.Database.GetItems(); //IEnumerable<Employee> lst1 = App.Database.GetItemsNotDone(); //IEnumerable<Employee> lst2 = App.Database.GetItemsNotDone(); Content = new StackLayout() { Children = { btn }, }; } private void Btn_Clicked(object sender, EventArgs e) { lst = App.Database.GetItems(); lv.ItemsSource = lst; lv.HasUnevenRows = true; lv.ItemTemplate = new DataTemplate(typeof(OptionsViewCell)); Content = new StackLayout() { Children = { lv }, }; } }
public class OptionsViewCell : ViewCell
{
int empid;
Button btnEdit;
public OptionsViewCell()
{
}
protected override void OnBindingContextChanged()
{
base.OnBindingContextChanged();
if (this.BindingContext == null)
return;
dynamic obj = BindingContext;
empid = Convert.ToInt32(obj.Eid);
var lblname = new Label
{
BackgroundColor = Color.Lime,
Text = obj.Ename,
};
var lblAddress = new Label
{
BackgroundColor = Color.Yellow,
Text = obj.Address,
};
var lblphonenumber = new Label
{
BackgroundColor = Color.Pink,
Text = obj.phonenumber,
};
var lblemail = new Label
{
BackgroundColor = Color.Purple,
Text = obj.email,
};
var lbleid = new Label
{
BackgroundColor = Color.Silver,
Text = (empid).ToString(),
};
//var lblname = new Label
//{
// BackgroundColor = Color.Lime,
// // HorizontalOptions = LayoutOptions.Start
//};
//lblname.SetBinding(Label.TextProperty, "Ename");
//var lblAddress = new Label
//{
// BackgroundColor = Color.Yellow,
// //HorizontalOptions = LayoutOptions.Center,
//};
//lblAddress.SetBinding(Label.TextProperty, "Address");
//var lblphonenumber = new Label
//{
// BackgroundColor = Color.Pink,
// //HorizontalOptions = LayoutOptions.CenterAndExpand,
//};
//lblphonenumber.SetBinding(Label.TextProperty, "phonenumber");
//var lblemail = new Label
//{
// BackgroundColor = Color.Purple,
// // HorizontalOptions = LayoutOptions.CenterAndExpand
//};
//lblemail.SetBinding(Label.TextProperty, "email");
//var lbleid = new Label
//{
// BackgroundColor = Color.Silver,
// // HorizontalOptions = LayoutOptions.CenterAndExpand
//};
//lbleid.SetBinding(Label.TextProperty, "Eid");
Button btnDelete = new Button
{
BackgroundColor = Color.Gray,
Text = "Delete",
//WidthRequest = 15,
//HeightRequest = 20,
TextColor = Color.Red,
HorizontalOptions = LayoutOptions.EndAndExpand,
};
btnDelete.Clicked += BtnDelete_Clicked;
//btnDelete.PropertyChanged += BtnDelete_PropertyChanged;
btnEdit = new Button
{
BackgroundColor = Color.Gray,
Text = "Edit",
TextColor = Color.Green,
};
// lbleid.SetBinding(Label.TextProperty, "Eid");
btnEdit.Clicked += BtnEdit_Clicked1; ;
//btnEdit.Clicked += async (s, e) =>{
// await App.Current.MainPage.Navigation.PushModalAsync(new EmployeeRegistration());
//};
View = new StackLayout()
{
Orientation = StackOrientation.Horizontal,
BackgroundColor = Color.White,
Children = { lbleid, lblname, lblAddress, lblemail, lblphonenumber, btnDelete, btnEdit },
};
//View = new StackLayout()
//{ HorizontalOptions = LayoutOptions.Center, WidthRequest = 10, BackgroundColor = Color.Yellow, Children = { lblAddress } };
//View = new StackLayout()
//{ HorizontalOptions = LayoutOptions.End, WidthRequest = 30, BackgroundColor = Color.Yellow, Children = { lblemail } };
//View = new StackLayout()
//{ HorizontalOptions = LayoutOptions.End, BackgroundColor = Color.Green, Children = { lblphonenumber } };
//string Empid =c.eid ;
}
private async void BtnEdit_Clicked1(object sender, EventArgs e)
{
Employee obj= App.Database.GetItem(empid);
if (empid > 0)
{
await App.Current.MainPage.Navigation.PushModalAsync(new EmployeeRegistration(obj));
}
else {
await App.Current.MainPage.Navigation.PushModalAsync(new EmployeeRegistration(empid));
}
}
private void BtnDelete_Clicked(object sender, EventArgs e)
{
// var eid = Convert.ToInt32(empid);
// var item = (Xamarin.Forms.Button)sender;
int eid = empid;
App.Database.DeleteItem(eid);
}
//private void BtnDelete_PropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
//{
// var ename= e.PropertyName;
//}
}
//private void BtnDelete_Clicked(object sender, EventArgs e)
//{
// var eid = 8;
// var item = (Xamarin.Forms.Button)sender;
// App.Database.DeleteItem(eid);
//}
}
- 在 Android 和 ios
GetConnection()方法中實現方法
using System;
using Xamarin.Forms;
using System.IO;
using DatabaseEmployeeCreation.Droid;
using DatabaseEmployeeCreation.SqlLite.ViewModel;
using SQLite;
using SQLite.Net;
[assembly: Dependency(typeof(SQLiteEmployee_Andriod))]
namespace DatabaseEmployeeCreation.Droid
{
public class SQLiteEmployee_Andriod : ISQLite
{
public SQLiteEmployee_Andriod()
{
}
#region ISQLite implementation
public SQLiteConnection GetConnection()
{
//var sqliteFilename = "EmployeeSQLite.db3";
//string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); // Documents folder
//var path = Path.Combine(documentsPath, sqliteFilename);
//// This is where we copy in the prepopulated database
//Console.WriteLine(path);
//if (!File.Exists(path))
//{
// var s = Forms.Context.Resources.OpenRawResource(Resource.Raw.EmployeeSQLite); // RESOURCE NAME ###
// // create a write stream
// FileStream writeStream = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
// // write to the stream
// ReadWriteStream(s, writeStream);
//}
//var conn = new SQLiteConnection(path);
//// Return the database connection
//return conn;
var filename = "DatabaseEmployeeCreationSQLite.db3";
var documentspath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
var path = Path.Combine(documentspath, filename);
var platform = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
var connection = new SQLite.Net.SQLiteConnection(platform, path);
return connection;
}
//public SQLiteConnection GetConnection()
//{
// var filename = "EmployeeSQLite.db3";
// var documentspath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
// var path = Path.Combine(documentspath, filename);
// var platform = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
// var connection = new SQLite.Net.SQLiteConnection(platform, path);
// return connection;
//}
#endregion
/// <summary>
/// helper method to get the database out of /raw/ and into the user filesystem
/// </summary>
void ReadWriteStream(Stream readStream, Stream writeStream)
{
int Length = 256;
Byte[] buffer = new Byte[Length];
int bytesRead = readStream.Read(buffer, 0, Length);
// write the required bytes
while (bytesRead > 0)
{
writeStream.Write(buffer, 0, bytesRead);
bytesRead = readStream.Read(buffer, 0, Length);
}
readStream.Close();
writeStream.Close();
}
}
}
我希望上面的例子是我解釋的非常簡單的方法