public static void CreateTables() { string createTablesQuery = @" CREATE TABLE Users ( Id INT PRIMARY KEY IDENTITY(1,1), Username NVARCHAR(50) NOT NULL UNIQUE, Email NVARCHAR(100) NOT NULL, CreatedAt DATETIME2 DEFAULT GETDATE() ); CREATE TABLE Products ( Id INT PRIMARY KEY IDENTITY(1,1), Name NVARCHAR(100) NOT NULL, Price DECIMAL(18,2) NOT NULL, Stock INT DEFAULT 0 );"; using (var connection = new SqlConnection(@"Server=(localdb)\MSSQLLocalDB;Database=MyApp;Trusted_Connection=true;")) { connection.Open(); using (var command = new SqlCommand(createTablesQuery, connection)) { command.ExecuteNonQuery(); Console.WriteLine("Tables created successfully!"); } } } } // appsettings.json { "ConnectionStrings": { "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB;Database=MyAppDb;Trusted_Connection=true;MultipleActiveResultSets=true" } } // DbContext configuration in Program.cs (.NET 6+) using Microsoft.EntityFrameworkCore;
This LocalDB instance is perfect for development, testing, and lightweight applications without needing a full SQL Server installation!
// Attach a specific MDF file "Server=(localdb)\MSSQLLocalDB;AttachDbFileName=C:\Data\MyDatabase.mdf;Database=MyDatabase;Trusted_Connection=true;" # Check if LocalDB is installed sqllocaldb info Create a new LocalDB instance sqllocaldb create "MyInstance" Start the LocalDB instance sqllocaldb start "MyInstance" Get connection string for the instance sqllocaldb info "MyInstance" Stop the instance sqllocaldb stop "MyInstance" Delete the instance sqllocaldb delete "MyInstance" List all instances sqllocaldb i 3. C# - Create Database Programmatically using System.Data.SqlClient; public class LocalDBSetup { private const string ConnectionString = @"Server=(localdb)\MSSQLLocalDB;Trusted_Connection=true;"; localdb mssqllocaldb
public class User { public int Id { get; set; } public string Username { get; set; } public string Email { get; set; } public DateTime CreatedAt { get; set; } } // Check if LocalDB is running and accessible public static bool TestLocalDBConnection() { try { using var connection = new SqlConnection(@"Server=(localdb)\MSSQLLocalDB;Trusted_Connection=true;"); connection.Open(); Console.WriteLine("✓ LocalDB is accessible"); using var command = new SqlCommand("SELECT @@VERSION", connection); string version = command.ExecuteScalar().ToString(); Console.WriteLine($"✓ SQL Server Version: {version.Substring(0, Math.Min(50, version.Length))}..."); return true; } catch (Exception ex) { Console.WriteLine($"✗ Error connecting to LocalDB: {ex.Message}"); Console.WriteLine("\nTroubleshooting steps:"); Console.WriteLine("1. Run 'sqllocaldb start MSSQLLocalDB' in command prompt"); Console.WriteLine("2. Ensure SQL Server LocalDB is installed"); Console.WriteLine("3. Check if Windows Firewall is blocking the connection"); return false; } } Quick Start Summary # 1. Create and start LocalDB sqllocaldb create "MyApp" sqllocaldb start "MyApp" 2. Get connection string sqllocaldb info "MyApp" 3. Use in C# code "Server=(localdb)\MyApp;Database=MyDatabase;Trusted_Connection=true;" 4. Stop when done sqllocaldb stop "MyApp"
// In Package Manager Console or CLI: // Add-Migration InitialCreate // Update-Database # Create-DevDatabase.ps1 param( [string]$InstanceName = "MSSQLLocalDB", [string]$DatabaseName = "DevDatabase" ) Check if SQL LocalDB is installed $localdbPath = "C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe" if (-not (Test-Path $localdbPath)) { Write-Error "SQL LocalDB not found. Please install SQL Server LocalDB." exit 1 } Start LocalDB instance Write-Host "Starting LocalDB instance: $InstanceName" -ForegroundColor Green & $localdbPath start $InstanceName Get connection string $connectionString = "Server=(localdb)$InstanceName;Trusted_Connection=true;" Create database $query = "CREATE DATABASE [$DatabaseName]" Invoke-Sqlcmd -ConnectionString $connectionString -Query $query } } public async Task<
public async Task UpdateUserAsync(User user) { using var connection = new SqlConnection(_connectionString); var sql = "UPDATE Users SET Username = @Username, Email = @Email WHERE Id = @Id"; await connection.ExecuteAsync(sql, user); } }
public async Task<IEnumerable<User>> GetUsersAsync() { using var connection = new SqlConnection(_connectionString); return await connection.QueryAsync<User>("SELECT * FROM Users"); } return await connection.QueryAsync<
builder.Services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));