Skip to main content

How to use SQLite libarary in Windows 8 Apps

By

Hi Guys ...

SQLite is a software library that implements a self-contained, serverless, zero-configuration,transactional SQL database engine.

 Now SQLite’s branch for WinRT is available and we can start to use it to store the data of our applications, with the help of another library called sqlite-net, that provides LINQ based APIs to work with data, both with sync and async support.

Installing the SQLite for Windows Runtime package

  • First thing is we should do is install the "SQLite for Windows Runtime " package.
     From the Tools menu, choose     Extensions and Updates and then choose the Online section (on the left of the dialog) and search for ‘sqlite’ in the search term.  This will show you the SQLite for Windows Runtime package: Click Install




















  • Then add following two libraries to the Reference. Right click on reference and select Add Reference ..
    SQLite for Windows Runtime
    Microsoft Visual C++ Runtime Package







  •   
Create Database 
Now i am going to create a database name "School".  This school DB contain set of tables and we  have to create classes for each tables.
I am going to create Table name Student and it has three attributes(columns) .
  • Id
  • Name
  • Course 
This is the class for Student Table ...

 public class Student
 {  
   [PrimaryKey, AutoIncrement]  
   public int Id { get; set; }  
   [MaxLength(30)]  
   public string Name { get; set; }  
   [MaxLength(30)]  
   public string Course { get; set; }  
 }

Now you can create the Database 

 private async void CreateDatabase()  
 {  
   SQLiteAsyncConnection conn = new SQLiteAsyncConnection("School");  
   await conn.CreateTableAsync<Student>();  
 }

The first step is to create a SQLiteAsynConnection object, that identifies the connection to the database, like in the example: the parameter passed to the constructor is the name of the file that will be created in the local storage. 

 Adding And Reading Data

  • To insert data we use the InsertAsync method
 private async void Insert_Click_1(object sender, RoutedEventArgs e)  
 {  
   SQLiteAsyncConnection conn = new SQLiteAsyncConnection("School");  
   Student Student= new Student  
   {  
     Name = "Sanka",  
     Course = "BTech"  
   };  
   await conn.InsertAsync(Student);  
 }


  • We can access directly to the table using the Table<T>object: it supports LINQ queries, so we can simply use LINQ to search for the data we need. Then, we can call the ToListAsync method to get a List<T> of objects that matches the specified query
 private async void Read_Click_1(object sender, RoutedEventArgs e)  
 {  
   SQLiteAsyncConnection conn = new SQLiteAsyncConnection("School");  
   var query = conn.Table<Student>().Where(x => x.Name == "Sanka");  
   var result = await query.ToListAsync();  
   foreach (var item in result)  
   {  
     Debug.WriteLine(string.Format("{0}: {1} {2}", item.Id, item.Name, item.Course));  
   }  
 }


Thank You guys !!!!!!!!!!!!!!
I think this post will help you .......................

Comments

Post a Comment