How to use SQL CE.mp4

 

Download Video : How to use SQL CE.mp4

SQL Server Compact Edition (SQL CE). I’ll assume you want to actually use it, not just read theory.

---

What is SQL Server Compact Edition (SQL CE)?

SQL CE is a lightweight, embedded database by Microsoft.

• No separate database server

• Runs inside your application

• Best for desktop apps, mobile apps, small tools

• File-based database: .sdf

⚠️ Heads-up: SQL CE is deprecated by Microsoft, but many legacy apps still use it.

---

When should you use SQL CE?

Good choice if:

• You need a local database

• App must work offline

• Small data size (up to ~4GB)

• No need for heavy concurrency

Not good for:

• Web apps

• High traffic systems

• Large enterprise databases

---

Step 1: Install SQL CE

You need SQL Server Compact installed.

Typical options:

• SQL Server Compact 4.0

• Comes as runtime + developer tools

After install, you’ll get:

• System.Data.SqlServerCe.dll

---

Step 2: Create a SQL CE Database

A SQL CE database is just a file (.sdf).

Create Database in C#

using System.Data.SqlServerCe;

string connectionString =
    "Data Source=MyDatabase.sdf; Persist Security Info=False;";

SqlCeEngine engine = new SqlCeEngine(connectionString);
engine.CreateDatabase();

That’s it — database created 🎉

---

Step 3: Connect to SQL CE

SqlCeConnection conn = new SqlCeConnection(
    "Data Source=MyDatabase.sdf;"
);
conn.Open();

---

Step 4: Create Tables

SQL syntax is very similar to SQL Server.

string query = @"
CREATE TABLE Users (
    Id INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
)";

SqlCeCommand cmd = new SqlCeCommand(query, conn);
cmd.ExecuteNonQuery();

---

Step 5: Insert Data

string insertQuery =
    "INSERT INTO Users (Name, Email) VALUES (@name, @email)";

SqlCeCommand cmd = new SqlCeCommand(insertQuery, conn);
cmd.Parameters.AddWithValue("@name", "Aman");
cmd.Parameters.AddWithValue("@email", "aman@example.com");
cmd.ExecuteNonQuery();

---

Step 6: Read Data

string selectQuery = "SELECT * FROM Users";
SqlCeCommand cmd = new SqlCeCommand(selectQuery, conn);

SqlCeDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine(reader["Name"]);
}

---

Step 7: Update Data

string updateQuery =
    "UPDATE Users SET Email=@email WHERE Id=@id";

SqlCeCommand cmd = new SqlCeCommand(updateQuery, conn);
cmd.Parameters.AddWithValue("@email", "new@mail.com");
cmd.Parameters.AddWithValue("@id", 1);
cmd.ExecuteNonQuery();

---

Step 8: Delete Data

string deleteQuery = "DELETE FROM Users WHERE Id=@id";

SqlCeCommand cmd = new SqlCeCommand(deleteQuery, conn);
cmd.Parameters.AddWithValue("@id", 1);
cmd.ExecuteNonQuery();

---

SQL CE Limitations (Important ⚠️)

• ❌ No stored procedures

• ❌ No views

• ❌ Limited SQL functions

• ❌ Single-user write access

• ✔️ Supports indexes & constraints

• ✔️ Supports transactions

---

Common Use Cases

• Desktop apps (WinForms, WPF)

• Offline-first apps

• POS systems

• Embedded tools

• Legacy mobile apps

---

Modern Alternatives (Recommended 🚀)

If starting a new project, consider:

• SQLite (most popular replacement)

• SQL Server Express

• LocalDB

SQLite is usually the best drop-in replacement.

---

Enjoy! Follow us for more...