articles

Crud Operation in ASP.NET Using SQLite Database

Anonymous User20650 21-Jan-2015

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

Crud Operation in ASP.NET Using SQLite Database

Give the application name and click ok

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

Crud Operation in ASP.NET Using SQLite Database

 

Crud Operation in ASP.NET Using SQLite Database

After install these dlls add new webforms

Crud Operation in ASP.NET Using SQLite Database

 

After add webform design this form

WebForm.aspx:

<%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="WebForm1.aspx.cs"Inherits="WebApplication1.WebForm1"%>
 
<!DOCTYPEhtml>
 
<htmlxmlns="http://www.w3.org/1999/xhtml">
<headrunat="server">
    <title></title>
    <linkhref="Content/bootstrap/css/bootstrap.min.css"rel="stylesheet"/>
</head>
<body>
    <formid="form1"runat="server">
        <br/>
        <br/>
        <divclass="container">
            <h1class="text-capitalize text-center">Register</h1>
            <divclass="table-responsive">
                <tableclass="table table-bordered table-striped">
                    <tr>
                        <th>User Name:</th>
                        <td>
                            <asp:TextBoxID="txt_Name"runat="server"CssClass="form-control"></asp:TextBox></td>
                    </tr>
                    <tr>
                        <th>EmailID:</th>
                        <td>
                            <asp:TextBoxID="txt_EmailID"runat="server"CssClass="form-control"></asp:TextBox></td>
                    </tr>
                    <tr>
                        <th>Address:</th>
                        <td>
                            <asp:TextBoxID="txt_Address"runat="server"CssClass="form-control"></asp:TextBox></td>
                    </tr>
                    <tr>
                        <th>Phone No:</th>
                        <td>
                            <asp:TextBoxID="txt_Phone"runat="server"CssClass="form-control"></asp:TextBox></td>
                    </tr>
                    <tr>
                        <tdclass="text-right"colspan="2"><asp:ButtonID="btn_clear"runat="server"Text="Clear"CssClass="btn btn-primary"OnClick="btn_clear_Click"/>
                        <asp:ButtonID="btn_Save"runat="server"Text="Save"OnClick="btn_Save_Click"CssClass="btn btn-primary"/>
                        </td>
                    </tr>
                </table>
            </div>
            <asp:GridViewID="Grid"runat="server"CssClass="table table-bordered table-striped"OnRowDeleting="Grid_RowDeleting"OnRowEditing="Grid_RowEditing"OnRowCancelingEdit="Grid_RowCancelingEdit"OnRowUpdating="Grid_RowUpdating">
                <Columns>
                    <asp:CommandFieldButtonType="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
{
    publicpartialclassWebForm1 : System.Web.UI.Page
    {
        SQLiteConnection con;
        SQLiteCommand cmd;
        SQLiteDataAdapter DB;
        DataSet DS = newDataSet();
        DataTable DT = newDataTable();
        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))";
        protectedvoid Page_Load(object sender, EventArgs e)
        {
            con = newSQLiteConnection(@"Data Source=E:MySQLiteDB.s3db");
            con.Open();
            //cmd.CommandText = createTableQuery;
            cmd = newSQLiteCommand(createTableQuery, con);
            cmd.ExecuteNonQuery();
            con.Close();
            if (!Page.IsPostBack)
            {
                LoadData();
            }
        }
 
        protectedvoid 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();
        }
 
        protectedvoid btn_clear_Click(object sender, EventArgs e)
        {
            txt_Name.Text = "";
            txt_EmailID.Text = "";
            txt_Address.Text = "";
            txt_Phone.Text = "";
        }
 
        privatevoid LoadData()
        {
            con.Open();
            cmd = con.CreateCommand();
            string CommandText = "select * from student";
            DB = newSQLiteDataAdapter(CommandText, con);
            DS.Reset();
            DB.Fill(DS);
            DT = DS.Tables[0];
            Grid.DataSource = DT;
            Grid.DataBind();
            con.Close();
        }
 
        protectedvoid 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 = newSQLiteCommand("delete from student where id=" + id + "", con);
                cmd.ExecuteNonQuery();
                con.Close();
                LoadData();
            }
        }
 
        protectedvoid Grid_RowEditing(object sender, GridViewEditEventArgs e)
        {
            var id = Grid.Rows[e.NewEditIndex].Cells[1].Text.ToString();
            LoadData();
           
        }
 
        protectedvoid Grid_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            GridViewRow row = Grid.Rows[e.RowIndex];
            con.Open();
            cmd = newSQLiteCommand("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();
        }
 
        protectedvoid Grid_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            Grid.EditIndex = -1;
            LoadData();
        }
 
    }
}

 

Output:

1.       When application run first time

Crud Operation in ASP.NET Using SQLite Database

 2.       When add some record in database 

Crud Operation in ASP.NET Using SQLite Database

Click save button

Crud Operation in ASP.NET Using SQLite Database

3. When delete a record from database


Crud Operation in ASP.NET Using SQLite Database

After click delete button one record is delete from database.

Crud Operation in ASP.NET Using SQLite Database

 1.       After Click edit button

Crud Operation in ASP.NET Using SQLite Database

Click update button record is updated

 

Crud Operation in ASP.NET Using SQLite Database


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


Updated 03-Feb-2020
I am a content writter !

Leave Comment

Comments

Liked By