Nezir Zahirović

Sqlite & Asp.net C# Simple operation

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()
More about Sqlite  HERE


This  is  what  I need  for a long  time.  Working  with asp.net and database all the  time  I  needed something like this.  Before I had to use office access  but it is not  good  for the job I need to done.
After sometimes i found sqlite and  now  I start using it in my private, demo, test  projects . For now, I am very satisfied with it:))

In this  post  I  will  show  you simple operation  with sqlite and asp.net :
Simple front design:

First  we need to copy some line of code in our web config  project file:
Second thing  is  that  we need  to import Sqlite.dll into our project (after we download it) .
We do reference to Sqlite .dll  after that  we need to create one simple database which  we can do with one of many sqlite managers :sqlitemanager

Next  step is to import our database in project App_Data folder:
Then  we need to write connection string :
After that,  we  add some code instruction to add and delete  values from  our database :


That's all folk's :))

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SQLite;
using System.Data.Common;
using System.Data;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    private SQLiteConnection sql_con;
    private SQLiteCommand sql_cmd;
    private SQLiteDataAdapter DB;
    private DataSet DS = new DataSet();
    private DataTable DT = new DataTable();

    protected void Page_Load(object sender, EventArgs e)
    {

        LoadData();
    }
    private void SetConnection()
    {
    sql_con = new SQLiteConnection ("Data source="+HttpContext.Current.Server.MapPath("App_Data\\phone.sqlite")+";   
    Version=3;New=False;Compress=True;");
    }
    private void ExecuteQuery(string txtQuery)
    {
        SetConnection();
        sql_con.Open();
        sql_cmd = sql_con.CreateCommand();
        sql_cmd.CommandText = txtQuery;
        sql_cmd.ExecuteNonQuery();
        sql_con.Close();
    }

    private void LoadData()
    {
        SetConnection();
        sql_con.Open();
        sql_cmd = sql_con.CreateCommand();
        string CommandText = "SELECT * FROM phonebook";
        DB = new SQLiteDataAdapter(CommandText, sql_con);
        DS.Reset();
        DB.Fill(DS);
        DT = DS.Tables[0];
        GridView1.DataSource = DT;
        GridView1.DataBind();
        sql_con.Close();
    }
    private void Add(string name,string number)
    {
        string txtSQLQuery = "insert into  phonebook (name,number ) values ('" + name + "','"+number+"')";
        ExecuteQuery(txtSQLQuery);
        LoadData();
    }
    private void Delete(string name)
    {
        string txtSQLQuery = "delete from phonebook where name='" + name + "'";
        ExecuteQuery(txtSQLQuery);
        LoadData();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Add(TextBox1.Text, TextBox2.Text);
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        Delete(TextBox3.Text);
        LoadData();
    }
}




SHARE