Home > DeveloperSection > Beginner > Crud Operation in ASP.NET Using SQLite Database

Crud Operation in ASP.NET Using SQLite Database


ASP.Net Sqlite  Sqlite3 
Ratings:
2 Comment(s)
 5329  View(s)
Rate this:

Crud Operation in ASP.NET Using SQLite Database

Hi everyone in this article I’m explaining about crud operation in asp.net using SQLite database.

Description:

SQLite is a free, public domain database engine. Unlike SQL Server, Oracle or other commercial database, it's not a stand alone program. It doesn't have a server. It's a very small code library written in C which other applications (yours) can call.

A SQLite database is a single file. The tables and data, triggers, foreign keys and constraints are all stored in this file. Your application reads and writes to the database by calling SQLite. When you do a SELECT or UPDATE, SQLite reads and writes to the file. If you do your updates inside a SQL transaction, SQLite makes sure you have exclusive access to the file. You can back up your database by making a copy of the database file. In short, you can use SQLite just like you would use any other database, but behind the scenes you are calling a small code library which is storing your data in a file. 

SQLite code is public domain. There's not even an open source license. It's in the public domain, which means "anyone is free to copy, modify, publish, use, compile, sell, or distribute the original SQLite code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means."There are still many volunteer developers working on SQLite, but the code is released to the public with no restrictions.

SQLite supports most of the standard SQL language, with some limitations. The most significant missing pieces are GRANT and REVOKE. SQLite writes to a file on a drive. The only permissions available are the read and write permissions for the file itself. The complete list of missing functionality can be found

SQLite transactions are ACID (Atomic, Consistent, Isolated, and Durable). That means that a transaction in SQLite will occur completely, or not at all. Transactions are enforced even when there are application errors, power outages and system failures.

SQLite may be the most commonly used database in the world. It's in every copy of Firefox. It's in the Apple iPhone and in many other cellphones and mp3 players. It's in Skype and Google Gears. It comes bundled with PHP and Python. You can use it too. 

 

How to work SQLite Database:

SQLite works very well as a small, embedded database. The entire SQLite library with all options enabled is around 300 kb. With optional features disabled, it's around 180 kb. It was designed for small, portable applications, especially applications that need to work offline. The data is stored in a local file, which means the database works without an internet connection. It also means the data remains even when the computer is turned off. 

In recent times, portable applications are becoming more and more popular. Many of these programs can be run from a portable flash drive. An important aspect of these programs is that they don't store their settings on the host computer. They don't store values in the registry. SQLite can help. Instead of storing your application settings and preferences in the registry, you can store them in a SQLite database. 

Lets start crud operation:

Open visual studio >> File >> New Project >> Select ASP.NET Web Forms Application

Give the application name and click ok

After create project install some dll from Manage NueGet Packages like this

 

After install these dlls add new webforms

 

After add webform design this form

WebForm.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>

 

<!DOCTYPE html>

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <link href="Content/bootstrap/css/bootstrap.min.css" rel="stylesheet" />

</head>

<body>

    <form id="form1" runat="server">

        <br />

        <br />

        <div class="container">

            <h1 class="text-capitalize text-center">Register</h1>

            <div class="table-responsive">

                <table class="table table-bordered table-striped">

                    <tr>

                        <th>User Name:</th>

                        <td>

                            <asp:TextBox ID="txt_Name" runat="server" CssClass="form-control"></asp:TextBox></td>

                    </tr>

                    <tr>

                        <th>EmailID:</th>

                        <td>

                            <asp:TextBox ID="txt_EmailID" runat="server" CssClass="form-control"></asp:TextBox></td>

                    </tr>

                    <tr>

                        <th>Address:</th>

                        <td>

                            <asp:TextBox ID="txt_Address" runat="server" CssClass="form-control"></asp:TextBox></td>

                    </tr>

                    <tr>

                        <th>Phone No:</th>

                        <td>

                            <asp:TextBox ID="txt_Phone" runat="server" CssClass="form-control"></asp:TextBox></td>

                    </tr>

                    <tr>

                        <td class="text-right" colspan="2"><asp:Button ID="btn_clear" runat="server" Text="Clear" CssClass="btn btn-primary" OnClick="btn_clear_Click" />

                        <asp:Button ID="btn_Save" runat="server" Text="Save" OnClick="btn_Save_Click" CssClass="btn btn-primary" />

                        </td>

                    </tr>

                </table>

            </div>

            <asp:GridView ID="Grid" runat="server" CssClass="table table-bordered table-striped" OnRowDeleting="Grid_RowDeleting" OnRowEditing="Grid_RowEditing" OnRowCancelingEdit="Grid_RowCancelingEdit" OnRowUpdating="Grid_RowUpdating">

                <Columns>

                    <asp:CommandField ButtonType="Link" HeaderText="Edit/Delete" ShowEditButton="true" ShowDeleteButton="true" ItemStyle-Width="100" />

                </Columns>

            </asp:GridView>

        </div>

    </form>

</body>

</html>

 

 

WebForm.aspx.cs:

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;

 

namespace WebApplication1

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        SQLiteConnection con;

        SQLiteCommand cmd;

        SQLiteDataAdapter DB;

        DataSet DS = new DataSet();

        DataTable DT = new DataTable();

        string createTableQuery = "CREATE TABLE IF NOT EXISTS student (ID integer primary key AUTOINCREMENT, Name VARCHAR(200),EmailID VARCHAR(200),Address VARCHAR(200), PhoneNO VARCHAR(200))";

        protected void Page_Load(object sender, EventArgs e)

        {

            con = new SQLiteConnection(@"Data Source=E:MySQLiteDB.s3db");

            con.Open();

            //cmd.CommandText = createTableQuery;

            cmd = new SQLiteCommand(createTableQuery, con);

            cmd.ExecuteNonQuery();

            con.Close();

            if (!Page.IsPostBack)

            {

                LoadData();

            }

        }

 

        protected void btn_Save_Click(object sender, EventArgs e)

        {

            con.Open();

            cmd = con.CreateCommand();

            cmd.CommandText = "insert into student (Name,EmailID,Address,PhoneNO) values('" + txt_Name.Text.Trim() + "','" + txt_EmailID.Text.Trim() + "','" + txt_Address.Text.Trim() + "','" + txt_Phone.Text.Trim() + "')";

            cmd.ExecuteNonQuery();

            txt_Name.Text = "";

            txt_EmailID.Text = "";

            txt_Address.Text = "";

            txt_Phone.Text = "";

            con.Close();

            LoadData();

        }

 

        protected void btn_clear_Click(object sender, EventArgs e)

        {

            txt_Name.Text = "";

            txt_EmailID.Text = "";

            txt_Address.Text = "";

            txt_Phone.Text = "";

        }

 

        private void LoadData()

        {

            con.Open();

            cmd = con.CreateCommand();

            string CommandText = "select * from student";

            DB = new SQLiteDataAdapter(CommandText, con);

            DS.Reset();

            DB.Fill(DS);

            DT = DS.Tables[0];

            Grid.DataSource = DT;

            Grid.DataBind();

            con.Close();

        }

 

        protected void Grid_RowDeleting(object sender, GridViewDeleteEventArgs e)

        {

            if (e.RowIndex >= 0)

            {

                GridViewRow row = (GridViewRow)Grid.Rows[e.RowIndex];

                var id = row.Cells[1].Text.ToString();

                con.Open();

                cmd = new SQLiteCommand("delete from student where id=" + id + "", con);

                cmd.ExecuteNonQuery();

                con.Close();

                LoadData();

            }

        }

 

        protected void Grid_RowEditing(object sender, GridViewEditEventArgs e)

        {

            var id = Grid.Rows[e.NewEditIndex].Cells[1].Text.ToString();

            LoadData();

           

        }

 

        protected void Grid_RowUpdating(object sender, GridViewUpdateEventArgs e)

        {

            GridViewRow row = Grid.Rows[e.RowIndex];

            con.Open();

            cmd = new SQLiteCommand("update student set name = '" + ((TextBox)(row.Cells[2].Controls[0])).Text + "', emailid = '" + ((TextBox)(row.Cells[3].Controls[0])).Text + "',address = '" + ((TextBox)(row.Cells[4].Controls[0])).Text + "', phoneno = '" + ((TextBox)(row.Cells[5].Controls[0])).Text + "' where id = '" + ((TextBox)(row.Cells[1].Controls[0])).Text + "'", con);

            cmd.ExecuteNonQuery();

            con.Close();

            Grid.EditIndex = -1;

            LoadData();

        }

 

        protected void Grid_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

        {

            Grid.EditIndex = -1;

            LoadData();

        }

 

    }

}

 

Output:

1.       When application run first time

 

2.       When add some record in database

 

Click save button

3.       When delete a record from database

After click delete button one record is delete from database.

 

1.       After Click edit button

Click update button record is updated

 


In my previous post i'll explain about Image viewer using bootstrap carousel


Thank you.

By leranzo Campbell on   one year ago
Thanks for your work, Looks GREAT.  I had a small problem with the
con = new SQLiteConnection(@"Data Source=e:MySQLiteDB.s3db");
But, I corrected that with this: 
con = new SQLiteConnection(@"Data Source=|DataDirectory|MySQLiteDB.s3db");

Thank you

By Kamlakar Singh on   one year ago

 leranzo Campbell,

Thanks Buddy!! I will update my article


Don't want to miss updates? Please click the below button!

Follow MindStick