Introduction to Database and SQL

In today’s software development landscape, managing data efficiently is crucial, even for simple applications. Whether you’re building a small tool or a complex system, having a reliable database to store and retrieve data is essential. For developers looking to quickly integrate a database into their .NET console applications, SQLite offers a fast and straightforward solution.

What is a Database?

A database is a structured collection of data that allows for easy access, management, and updating of information. It acts as a repository where data can be stored and retrieved efficiently, enabling applications to handle large amounts of information seamlessly.

What is SQL?

SQL, or Structured Query Language, is the standard language used to communicate with databases. It allows developers to create, read, update, and delete data within the database through a series of commands and queries. In the future, based on your feedback, I might add a section about different types of databases and SQL. For now, you can check out these links:

• Database
• SQL

What is SQLite?

SQLite is an in-process library that provides a self-contained, serverless, zero-configuration, transactional SQL database engine. It is public domain software, meaning it is free to use for any purpose, whether commercial or private. Due to its simplicity and efficiency, SQLite is widely used, especially in mobile applications.

Unlike most SQL databases, SQLite does not have a separate server process. Instead, it reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indexes, triggers, and views is stored in a single file. This cross-platform format allows you to easily transfer databases between different systems.

SQLite is very compact, with a library size of less than 750KiB with all features enabled. It balances memory usage and speed well, often running faster than direct filesystem I/O, even in low-memory environments.

SQLite’s simplicity, efficiency, and robustness make it a popular choice for many applications, particularly in mobile development.

Example: Using SQLite with C# in VSCode

Disclaimer: This example is purely for educational purposes. There are better ways to write code and applications that can optimize this example. Use this as a starting point for learning, but always strive to follow best practices and improve your implementation.

Installing Necessary Package

Microsoft.Data.Sqlite is a lightweight ADO.NET provider for SQLite and you can install it with:

dotnet add package Microsoft.Data.Sqlite

using Microsoft.Data.Sqlite;

class Program
{
 private static void Main(string[] args)
 {
  const string databaseFile = "crm.db";
  var connectionString = $"Data Source={databaseFile}";

  using(var connection = new SqliteConnection(connectionString))
  {
   connection.Open();
   CreateTable(connection);
   InsertUsers(connection);
   DisplayAllUsers(connection);
   DeleteUserByName(connection, "Otto");
   DisplayAllUsers(connection);
  }

  File.Delete(databaseFile);
  System.Console.WriteLine("Database file deleted!");
 }

 private static void DeleteUserByName(SqliteConnection connection, string name)
 {
  var command = connection.CreateCommand();
  command.CommandText = "DELETE FROM user WHERE name = $name";
  command.Parameters.AddWithValue("$name", name);
  command.ExecuteNonQuery();
  Console.WriteLine($"User with name '{name}' deleted.");
 }

 private static void DisplayAllUsers(SqliteConnection connection)
 {
  var command = connection.CreateCommand();
  command.CommandText = "SELECT id, name, age FROM user";

  using (var reader = command.ExecuteReader())
  {
   Console.WriteLine("Current users in the database:");
   while (reader.Read())
   {
    var id = reader.GetInt32(0);
    var name = reader.GetString(1);
    var age = reader.GetInt32(2);
    Console.WriteLine($"ID: {id}, Name: {name}, Age: {age}");
   }
  }
 }

 private static void InsertUsers(SqliteConnection connection)
 {
  var command = connection.CreateCommand();
  command.CommandText =
    @"
    INSERT INTO user (name, age)
    VALUES ('Otto', 30),
    ('Tim', 25),
    ('Steve', 28),
    ('Robert', 35);
    ";

  command.ExecuteNonQuery();
  Console.WriteLine("Users inserted.");
 }

 private static void CreateTable(SqliteConnection connection)
 {
  var command = connection.CreateCommand();
  command.CommandText =
   @"
   CREATE TABLE IF NOT EXISTS user (
   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   name TEXT NOT NULL,
   age INTEGER NOT NULL);
   ";

  command.ExecuteNonQuery();
  Console.WriteLine("Table created.");
 }
}

Breakdown of the Example

1. Main Method:
   • Establishes a connection to the SQLite database.
   • Calls methods to create the table, insert users, display all users, delete a user by name, and display all users again.
   • Deletes the database file at the end.
2. CreateTable Method:
   • Creates a user table with id, name, and age columns.
3. InsertUsers Method:
   • Inserts five users with random names and ages.
4. DisplayAllUsers Method:
   • Queries and displays all users in the database.
5. DeleteUserByName Method:
   • Deletes a user by name using a parameterized query to prevent SQL injection.

This structure ensures that the example is well-organized and easy to follow, demonstrating the key operations of creating, inserting, querying, and deleting data in an SQLite database using C# in a .NET console application.

Run of the Example

Understanding SQL Queries

Structured Query Language (SQL) is the standard language for managing and manipulating databases. It allows developers to perform various operations on data stored in relational database management systems (RDBMS).

Basic SQL Operations

1. SELECT Statement: Retrieves data from a database.
   SELECT column1, column2, ...FROM table_name WHERE condition;


   Example: SELECT* FROM users;

2. INSERT INTO Statement: Adds new records to a table.
   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);


   Example: INSERT INTO users (name, age) VALUES (‘John’, 25);

3. UPDATE Statement: Modifies existing records in a table.
   UPDATE table_name SET column1 = value1, column2 = value2, ...WHERE condition;


   Example: UPDATE users SET age = 30 WHERE name = ‘John’;

4. DELETE Statement: Deletes records from a table.
   DELETE FROM table_name WHERE condition;


   Example: DELETE FROM users WHERE age > 40;

SQL Query Breakdown

• SELECT: Retrieves specific columns or all columns from a table based on specified criteria.
• INSERT INTO: Adds new rows of data into a table.
• UPDATE: Modifies existing records in a table based on specified conditions.
• DELETE: Removes one or more rows from a table based on specified conditions.

Additional SQL Concepts

• Joins: Combines rows from two or more tables based on a related column.
• Functions: Built-in functions for manipulating data (e.g., SUM, COUNT, AVG).
• Transactions: Ensures that all operations within a transaction are completed successfully or rolled back.

SQL provides a powerful and standardized way to interact with databases, making it essential for developers working with relational databases like SQLite.

Conclusion

Creating our first database, setting up a table, and performing operations on it with SQLite in a .NET console application was surprisingly straightforward. This simplicity demonstrates how accessible and powerful SQLite is, making it an excellent choice for beginners and small to medium-sized applications.

In software development, there are various types of databases to choose from, including relational databases, NoSQL databases, and cache solutions, which can be deployed locally or in the cloud. However, understanding the basics of SQL using SQLite reveals the underlying power and flexibility of SQL databases.

SQLite’s ease of use, combined with its robust features, makes it a valuable tool for learning and development. By mastering SQLite, developers can gain a solid foundation in database management, which will serve them well as they progress to more complex systems and environments.

Learning and experimenting with SQLite not only helps you grasp fundamental database concepts but also prepares you for more advanced database systems and real-world applications. As you continue your journey in software development, remember that the key to mastering databases lies in continuous learning and practice.

If you think your friends or network would find this article useful, please consider sharing it with them. Your support is greatly appreciated.