First you need to import dll file to your App References:
- Open Project menu → Add Reference...
- Open Browse tab, click Browse... and find System.Data.SQLite.dll extracted from your downloaded file.
To use it, you might need to import it's Namespace:
using System.Data.SQLite;
OK Now we start.
Create a Connection to Database:
SQLiteConnection sql_con = new SQLiteConnection("Data Source=" + datafilepath + ";Version=3;");
If don't have database file yet:
SQLiteConnection.CreateFile(datafilepath);
Open the Connection:
sql_con.Open();
Create Table:
string command = @"CREATE TABLE Persons (ID int NOT NULL AUTO_INCREMENT UNIQUE,
Name varchar(255),
Age int,
Address varchar(255),
PRIMARY KEY (P_Id));";
using (SQLiteCommand sql_cmd = new SQLiteCommand(command, sql_con))
{
sql_cmd.ExecuteNonQuery;
}
//The NOT NULL constraint enforces a column to NOT accept NULL values.
//The UNIQUE constraint uniquely identifies each record in a database table.
//The PRIMARY KEY constraint uniquely identifies each record in a database table.
//Auto-increment allows a unique number to be generated when a new record is inserted into a table.
Insert Data to Database:
string command = @"INSERT INTO Persons (Name,Age,Address)
VALUES ('John','30','Skagen 21');";
using (SQLiteCommand sql_cmd = new SQLiteCommand(command, sql_con))
{
sql_cmd.ExecuteNonQuery;
}
Select Data from Database:
string command = @"SELECT Name,Address FROM Persons;"; //Get data from column Name and Address
using (SQLiteCommand sql_cmd = new SQLiteCommand(command, sql_con))
{
sql_cmd.ExecuteNonQuery;
}
or
string command = @"SELECT * FROM Persons;"; //Get data from all column
using (SQLiteCommand sql_cmd = new SQLiteCommand(command, sql_con))
{
sql_cmd.ExecuteNonQuery;
}
Update Data to Database:
string command = @"UPDATE Persons
SET Name='Alfred Schmidt', Address='Hamburg'
WHERE ID='1';";
using (SQLiteCommand sql_cmd = new SQLiteCommand(command, sql_con))
{
sql_cmd.ExecuteNonQuery;
}
Delete Data from Database:
string command = @"DELETE FROM Persons
WHERE Name='Alfreds Schmidt' AND Address='Hamburg';";
using (SQLiteCommand sql_cmd = new SQLiteCommand(command, sql_con))
{
sql_cmd.ExecuteNonQuery;
}
Check if record exists in Database:
sql_cmd.CommandText = "SELECT count(*) FROM Persons WHERE Name='ABC XYZ'";
int count = Convert.ToInt32(cmd.ExecuteScalar());
if(count == 0)
{
//No record exist.
}
After done everything, you need to Close your Connection:
sql_con.Close();
Goodluck!
