Xamarin.Forms
Trabajar con bases de datos locales
Buscar..
Usando SQLite.NET en un proyecto compartido
SQLite.NET es una biblioteca de código abierto que permite agregar soporte de bases de datos locales utilizando SQLite versión 3 en un proyecto Xamarin.Forms .
Los pasos a continuación Xamarin.Forms cómo incluir este componente en un Xamarin.Forms compartido de Xamarin.Forms :
Descargue la última versión de la clase SQLite.cs y agréguela al Proyecto Compartido.
Cada tabla que se incluirá en la base de datos debe modelarse como una clase en el Proyecto Compartido. Una tabla se define agregando al menos dos atributos en la clase:
Table(para la clase) yPrimaryKey(para una propiedad).
Para este ejemplo, se agrega una nueva clase llamada Song al proyecto compartido, que se define de la siguiente manera:
using System;
using SQLite;
namespace SongsApp
{
[Table("Song")]
public class Song
{
[PrimaryKey]
public string ID { get; set; }
public string SongName { get; set; }
public string SingerName { get; set; }
}
}
- A continuación, agregue una nueva clase llamada
Database, que hereda de la claseSQLiteConnection(incluida en SQLite.cs). En esta nueva clase, se define el código para el acceso a la base de datos, la creación de tablas y las operaciones de CRUD para cada tabla. El código de muestra se muestra a continuación:
using System;
using System.Linq;
using System.Collections.Generic;
using SQLite;
namespace SongsApp
{
public class BaseDatos : SQLiteConnection
{
public BaseDatos(string path) : base(path)
{
Initialize();
}
void Initialize()
{
CreateTable<Song>();
}
public List<Song> GetSongs()
{
return Table<Song>().ToList();
}
public Song GetSong(string id)
{
return Table<Song>().Where(t => t.ID == id).First();
}
public bool AddSong(Song song)
{
Insert(song);
}
public bool UpdateSong(Song song)
{
Update(song);
}
public void DeleteSong(Song song)
{
Delete(song);
}
}
}
- Como pudo ver en el paso anterior, el constructor de nuestra clase de
DatabasedeDatabaseincluye un parámetro depath, que representa la ubicación del archivo que almacena el archivo de base de datos SQLite. Un objeto deDatabaseestática se puede declarar enApp.csElpathes específico de la plataforma:
public class App : Application
{
public static Database DB;
public App ()
{
string dbFile = "SongsDB.db3";
#if __ANDROID__
string docPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
var dbPath = System.IO.Path.Combine(docPath, dbFile);
#else
#if __IOS__
string docPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
string libPath = System.IO.Path.Combine(docPath, "..", "Library");
var dbPath = System.IO.Path.Combine(libPath, dbFile);
#else
var dbPath = System.IO.Path.Combine(ApplicationData.Current.LocalFolder.Path, dbFile);
#endif
#endif
DB = new Database(dbPath);
// The root page of your application
MainPage = new SongsPage();
}
}
- Ahora simplemente llame al objeto
DBtravés de la clase deAppcualquier momento que necesite realizar una operación CRUD a la tabla deSongs. Por ejemplo, para insertar una nuevaSongdespués de que el usuario haya hecho clic en un botón, puede usar el siguiente código:
void AddNewSongButton_Click(object sender, EventArgs a)
{
Song s = new Song();
s.ID = Guid.NewGuid().ToString();
s.SongName = songNameEntry.Text;
s.SingerName = singerNameEntry.Text;
App.DB.AddSong(song);
}
Trabajar con bases de datos locales utilizando xamarin.forms en visual studio 2015
Ejemplo de SQlite Paso a paso Explicación
Los pasos a continuación muestran cómo incluir este componente en un proyecto compartido de Xamarin.Forms: para agregar paquetes en (pcl, Andriod, Windows, Ios) Agregar referencias Haga clic en Administrar paquetes de Nuget -> haga clic en Examinar para instalar SQLite.Net.Core- PCL , SQLite Net Extensions una vez completada la instalación, verifique una vez en las referencias y luego
Para agregar Class Employee.cs debajo del código
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; } } }Para agregar una interfaz ISQLite
using SQLite.Net;
//using SQLite.Net;
namespace DatabaseEmployeeCreation.SqlLite.ViewModel
{
public interface ISQLite
{
SQLiteConnection GetConnection();
}
}
- Cree una clase para lógica de base de datos y siga los métodos a continuación.
utilizando SQLite.Net; utilizando System.Collections.Generic; utilizando System.Linq; utilizando Xamarin.Forms; espacio de nombres DatabaseEmployeeCreation.SqlLite.ViewModel {clase pública DatabaseLogic {static object locker = new object (); Base de datos 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);
}
}
}
}
- crear un 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);
// }
}
}
para mostrar EmployeeDetails debajo del código detrás
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);
//}
}
- Implementar método en Android y 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();
}
}
}
Espero que este ejemplo de arriba sea muy fácil de explicar.
Modified text is an extract of the original Stack Overflow Documentation
Licenciado bajo CC BY-SA 3.0
No afiliado a Stack Overflow