14. LocalDB

LocalDB can work without having a full installation of SQL Server on our machine. LocalDB is a lightweight, on-demand version of SQL Server that is specifically designed to work in development environments. It does not require the full SQL Server installation, but it still allows you to run databases locally.

Key Points:

• LocalDB is part of the SQL Server Express edition, but it’s a minimal version. It runs as a process (not as a service), which makes it lighter and easier to use in development environments.
• You do not need a full SQL Server installation for LocalDB to work.
• Microsoft.Data.SqlClient is the NuGet package used to interact with SQL Server (including LocalDB) from .NET applications. This package should work with LocalDB just as it does with a full SQL Server instance.

Steps to Use LocalDB:

• Install Microsoft.Data.SqlClient nuget package into console project.

• Here is code for program.cs

static void Main(string[] args)
{
    string connectionString = @"Server=(LocalDB)\MSSQLLocalDB;Integrated Security=true;";
 
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
            connection.Open();
            Console.WriteLine("Connection to LocalDB successful!");
 
            string createDatabaseQuery = @"
                IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'TestDB')
                BEGIN
                    CREATE DATABASE TestDB
                END";
            using (SqlCommand command = new SqlCommand(createDatabaseQuery, connection))
            {
                command.ExecuteNonQuery();
                Console.WriteLine("Database 'TestDB' created successfully.");
            }
 
            string dbConnectionString = @"Server=(LocalDB)\MSSQLLocalDB;Database=TestDB;Integrated Security=true;";
            using (SqlConnection dbConnection = new SqlConnection(dbConnectionString))
            {
                dbConnection.Open();
                Console.WriteLine("Connected to 'TestDB'.");
 
                string createTableQuery = @"
                    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Users')
                    BEGIN
                        CREATE TABLE Users (
                            Id INT IDENTITY(1,1) PRIMARY KEY,
                            Name NVARCHAR(50),
                            Age INT
                        )
                    END";
                using (SqlCommand command = new SqlCommand(createTableQuery, dbConnection))
                {
                    command.ExecuteNonQuery();
                    Console.WriteLine("Table 'Users' created successfully.");
                }
 
                string insertQuery = "INSERT INTO Users (Name, Age) VALUES (@Name, @Age)";
                using (SqlCommand command = new SqlCommand(insertQuery, dbConnection))
                {
                    command.Parameters.AddWithValue("@Name", "John Doe 3");
                    command.Parameters.AddWithValue("@Age", 31);
 
                    command.ExecuteNonQuery();
                    Console.WriteLine("Record inserted into 'Users' table.");
                }
 
                string selectQuery = "SELECT Id, Name, Age FROM Users";
                using (SqlCommand command = new SqlCommand(selectQuery, dbConnection))
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    Console.WriteLine("User Records:");
                    while (reader.Read())
                    {
                        Console.WriteLine($"Id: {reader["Id"]}, Name: {reader["Name"]}, Age: {reader["Age"]}");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"An error occurred: {ex.Message}");
        }
    }
 
    Console.WriteLine("Press any key to exit...");
    Console.ReadKey();
}

• The database instance is created at "C:\Users\ASD\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances". You can also check all local database instances using the command sqllocaldb i. The database is stored in C:\Users\ASD, not in C:\Users\ASD\Desktop. The code below shows the database store location only:

//See Location
var dbLocationQuery = "SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('TestDB');";
using (SqlCommand command = new SqlCommand(dbLocationQuery, connection))
{
    using (SqlDataReader reader = command.ExecuteReader())
    {
        // Read the result and display the database file locations
        while (reader.Read())
        {
            string fileName = reader["name"].ToString();
            string physicalPath = reader["physical_name"].ToString();
            Console.WriteLine($"File Name: {fileName}");
            Console.WriteLine($"Physical Path: {physicalPath}");
        }
    }
}