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 SHARE SHARE