Omarsoft For IT Solutions (Java Codes ,C#.NET Codes , ASP.NET Codes ,VB.NET Codes ,Oracle Database Administration, Real Application Cluster , Remote Support, Cloud Services , Networks ,Virtualization....
  • الأنظــمــة المكتبية        Windows App Programming
  • أنظــمـةالويــب        Web based systems programming
  • تطبيقات الهواتف الذكية     smartphones programming
  • إدارة قواعــــــد البيــــــــــــــــانات        Database Administration
  • إدارة الشبكـــــــــــــــــــــــــــــــــات        Networks Administration
  • إدارة الســـيــرفرات (ويب - محلية)  Servers Administration
  • إدارة مخـــــــــــــــــازن البيــــــــــــانات     Storage Administration
  •             N Computing & 2X Application services

    Social Icons

Loading...

SQLite Tutorial I



 SQLite Tutorial

This Post helps you understand what is SQLite, how it differs from SQL, why it is needed and the way in which it handles the applications Database.
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines around, but that's growth in terms of popularity, not anything to do with its size. The source code for SQLite is in the public domain.

What is SQLite?

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a database, which is zero-configured, which means like other databases you do not need to configure it in your system.
SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly.

Why SQLite?

  • SQLite does not require a separate server process or system to operate (serverless).
  • SQLite comes with zero-configuration, which means no setup or administration needed.
  • A complete SQLite database is stored in a single cross-platform disk file.
  • SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted.
  • SQLite is self-contained, which means no external dependencies.
  • SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.
  • SQLite supports most of the query language features found in SQL92 (SQL2) standard.
  • SQLite is written in ANSI-C and provides simple and easy-to-use API.
  • SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT).

SQLite A Brief History

  • 2000 - D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program.
  • 2000 - In August, SQLite 1.0 released with GNU Database Manager.
  • 2011 - Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database).

SQLite Limitations

There are few unsupported features of SQL92 in SQLite which are listed in the following table.
Sr.No.Feature & Description
1
RIGHT OUTER JOIN
Only LEFT OUTER JOIN is implemented.
2
FULL OUTER JOIN
Only LEFT OUTER JOIN is implemented.
3
ALTER TABLE
The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported.
4
Trigger support
FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
5
VIEWs
VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view.
6
GRANT and REVOKE
The only access permissions that can be applied are the normal file access permissions of the underlying operating system.

SQLite Commands

The standard SQLite commands to interact with relational databases are similar to SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature −

DDL - Data Definition Language

Sr.No.Command & Description
1
CREATE
Creates a new table, a view of a table, or other object in database.
2
ALTER
Modifies an existing database object, such as a table.
3
DROP
Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language

CommandDescription
INSERTCreates a record
UPDATEModifies records
DELETEDeletes records

DQL - Data Query Language

Sr.No.Command & Description
1
SELECT
Retrieves certain records from one or more tables


SQLite is famous for its great feature zero-configuration, which means no complex setup or administration is needed. This chapter will take you through the process of setting up SQLite on Windows, Linux and Mac OS X.

Install SQLite on Windows

  • Step 1 − Go to SQLite download page, and download precompiled binaries from Windows section.
  • Step 2 − Download sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files.
  • Step 3 − Create a folder C:\>sqlite and unzip above two zipped files in this folder, which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files.
  • Step 4 − Add C:\>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command, which should display the following result.
C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Install SQLite on Linux

Today, almost all the flavours of Linux OS are being shipped with SQLite. So you just issue the following command to check if you already have SQLite installed on your machine.
$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
If you do not see the above result, then it means you do not have SQLite installed on your Linux machine. Following are the following steps to install SQLite −
  • Step 1 − Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section.
  • Step 2 − Run the following command −
$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix = /usr/local
$make
$make install
The above command will end with SQLite installation on your Linux machine. Which you can verify as explained above.

Install SQLite on Mac OS X

Though the latest version of Mac OS X comes pre-installed with SQLite but if you do not have installation available then just follow these following steps −
  • Step 1 − Go to SQLite download page, and download sqlite-autoconf-*.tar.gz from source code section.
  • Step 2 − Run the following command −
$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local
$make
$make install
The above procedure will end with SQLite installation on your Mac OS X machine. Which you can verify by issuing the following command −
$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
Finally, you have SQLite command prompt where you can issue SQLite commands for your exercises.

Dapper



Dapper

What’s Dapper?

Dapper is a simple object mapper for .NET and own the title of King of Micro ORM in terms of speed and is virtually as fast as using a raw ADO.NET data reader. An ORM is an Object Relational Mapper, which is responsible for mapping between database and programming language.
Dapper extend the IDbConnection by providing useful extension methods to query your database.

How Dapper Works?

It is a three step process.
  • Create an IDbConnection object.
  • Write a query to perform CRUD operations.
  • Pass query as a parameter in Execute method.

Installation

Dapper is installed through NuGet: https://www.nuget.org/packages/Dapper
PM> Install-Package Dapper

Requirement

Dapper work with any database provider since there is no DB specific implementation.

Methods

Dapper will extend your IDbConnection interface with multiple methods:
  • Execute
  • Query
  • QueryFirst
  • QueryFirstOrDefault
  • QuerySingle
  • QuerySingleOrDefault
  • QueryMultiple


string sqlInvoices = "SELECT * FROM Invoice;";
string sqlInvoice = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;";
string sp = "EXEC Invoice_Insert";
using (var connection = My.ConnectionFactory())
{
var invoices = connection.Query<Invoice>(sqlInvoices).ToList();
var invoice = connection.QueryFirstOrDefault(sqlInvoice, new {InvoiceID = 1});
var affectedRows = connection.Execute(sp, new { Param1 = "Single_Insert_1" }, commandType: CommandType.StoredProcedure);
}

Parameter

Execute and queries method can use parameters from multiple different ways:
  • Anonymous
  • Dynamic
  • List
  • String


// Anonymous
var affectedRows = connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure);
// Dynamic
DynamicParameters parameter = new DynamicParameters();
parameter.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input);
parameter.Add("@Code", "Many_Insert_0", DbType.String, ParameterDirection.Input);
parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure);
// List
connection.Query<Invoice>(sql, new {Kind = new[] {InvoiceKind.StoreInvoice, InvoiceKind.WebInvoice}}).ToList();
// String
connection.Query<Invoice>(sql, new {Code = new DbString {Value = "Invoice_1", IsFixedLength = false, Length = 9, IsAnsi = true}}).ToList();

Result

The result returned by queries method can be mapped to multiple types:
  • Anonymous
  • Strongly Typed
  • Multi-Mapping
  • Multi-Result
  • Multi-Type


string sql = "SELECT * FROM Invoice;";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var anonymousList = connection.Query(sql).ToList();
var invoices = connection.Query<Invoice>(sql).ToList();
}

Utilities

  • Async
  • Buffered
  • Transaction
  • Stored Procedure


// Async
connection.QueryAsync<Invoice>(sql)
// Buffered
connection.Query<Invoice>(sql, buffered: false)
// Transaction
using (var transaction = connection.BeginTransaction())
{
var affectedRows = connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure,
transaction: transaction);
transaction.Commit();
}
// Stored Procedure
var affectedRows = connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure);

Sana'a Yemen - 50th st.

+967 738166685

omar.soft2000@gmail.com

للاتصال بنا CONTACT US

الاسم Name

بريد إلكتروني Email *

رسالة Message *

2015-2023 © All Rights Reserved Omarsoft
Back To Top