Visual Basic Database Patterns ADO.NET and Entity Framework patterns for VB.NET with focus on connection management, parameterized queries, and async operations. ADO.NET Patterns Connection Management ' ✅ Good: Using statement ensures disposal Public Async Function GetCustomersAsync ( ) As Task ( Of List ( Of Customer ) ) Dim customers = New List ( Of Customer ) ( ) Using connection = New SqlConnection ( connectionString ) Await connection . OpenAsync ( ) Using command = New SqlCommand ( "SELECT * FROM Customers WHERE IsActive = @isActive" , connection ) command . Parameters . AddWithValue ( "@isActive" , True ) Using reader = Await command . ExecuteReaderAsync ( ) While Await reader . ReadAsync ( ) customers . Add ( New Customer With { . Id = reader . GetInt32 ( 0 ) , . Name = reader . GetString ( 1 ) , . Email = reader . GetString ( 2 ) } ) End While End Using End Using End Using Return customers End Function Parameterized Queries (Critical for SQL Injection Prevention) ' ✅ Good: Parameterized query Public Async Function FindCustomerAsync ( email As String ) As Task ( Of Customer ) Using connection = New SqlConnection ( connectionString ) Await connection . OpenAsync ( ) Dim sql = "SELECT * FROM Customers WHERE Email = @email" Using command = New SqlCommand ( sql , connection ) command . Parameters . Add ( "@email" , SqlDbType . NVarChar , 255 ) . Value = email Using reader = Await command . ExecuteReaderAsync ( ) If Await reader . ReadAsync ( ) Then Return MapCustomer ( reader ) End If End Using End Using End Using Return Nothing End Function ' ❌ BAD: String concatenation (SQL injection risk!) Dim sql = $"SELECT * FROM Customers WHERE Email = '{email}'" ' NEVER DO THIS! Transaction Management Public Async Function TransferFundsAsync ( fromAccountId As Integer , toAccountId As Integer , amount As Decimal ) As Task Using connection = New SqlConnection ( connectionString ) Await connection . OpenAsync ( ) Using transaction = connection . BeginTransaction ( ) Try ' Debit from account Using command = New SqlCommand ( "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id" , connection , transaction ) command . Parameters . AddWithValue ( "@amount" , amount ) command . Parameters . AddWithValue ( "@id" , fromAccountId ) Await command . ExecuteNonQueryAsync ( ) End Using ' Credit to account Using command = New SqlCommand ( "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id" , connection , transaction ) command . Parameters . AddWithValue ( "@amount" , amount ) command . Parameters . AddWithValue ( "@id" , toAccountId ) Await command . ExecuteNonQueryAsync ( ) End Using transaction . Commit ( ) Catch ex As Exception transaction . Rollback ( ) Throw End Try End Using End Using End Function Bulk Operations Public Async Function BulkInsertCustomersAsync ( customers As List ( Of Customer ) ) As Task Using connection = New SqlConnection ( connectionString ) Await connection . OpenAsync ( ) ' Create DataTable Dim table = New DataTable ( ) table . Columns . Add ( "Name" , GetType ( String ) ) table . Columns . Add ( "Email" , GetType ( String ) ) table . Columns . Add ( "CreatedAt" , GetType ( DateTime ) ) For Each customer In customers table . Rows . Add ( customer . Name , customer . Email , DateTime . Now ) Next ' Bulk copy Using bulkCopy = New SqlBulkCopy ( connection ) bulkCopy . DestinationTableName = "Customers" bulkCopy . BatchSize = 1000 Await bulkCopy . WriteToServerAsync ( table ) End Using End Using End Function Entity Framework Core Patterns DbContext Setup Public Class AppDbContext Inherits DbContext Public Property Customers As DbSet ( Of Customer ) Public Property Orders As DbSet ( Of Order ) Public Sub New ( options As DbContextOptions ( Of AppDbContext ) ) MyBase . New ( options ) End Sub Protected Overrides Sub OnModelCreating ( builder As ModelBuilder ) ' Configure entity builder . Entity ( Of Customer ) ( Sub ( entity ) entity . HasKey ( Function ( c ) c . Id ) entity . Property ( Function ( c ) c . Name ) . IsRequired ( ) . HasMaxLength ( 200 ) entity . Property ( Function ( c ) c . Email ) . IsRequired ( ) . HasMaxLength ( 255 ) entity . HasIndex ( Function ( c ) c . Email ) . IsUnique ( ) End Sub ) ' Configure relationship builder . Entity ( Of Order ) ( Sub ( entity ) entity . HasOne ( Function ( o ) o . Customer ) _ . WithMany ( Function ( c ) c . Orders ) _ . HasForeignKey ( Function ( o ) o . CustomerId ) End Sub ) End Sub End Class CRUD Operations Public Class CustomerRepository Private ReadOnly context As AppDbContext Public Sub New ( context As AppDbContext ) Me . context = context End Sub ' Create Public Async Function AddAsync ( customer As Customer ) As Task ( Of Customer ) context . Customers . Add ( customer ) Await context . SaveChangesAsync ( ) Return customer End Function ' Read Public Async Function GetByIdAsync ( id As Integer ) As Task ( Of Customer ) Return Await context . Customers _ . Include ( Function ( c ) c . Orders ) _ . FirstOrDefaultAsync ( Function ( c ) c . Id = id ) End Function ' Update Public Async Function UpdateAsync ( customer As Customer ) As Task context . Customers . Update ( customer ) Await context . SaveChangesAsync ( ) End Function ' Delete Public Async Function DeleteAsync ( id As Integer ) As Task Dim customer = Await context . Customers . FindAsync ( id ) If customer IsNot Nothing Then context . Customers . Remove ( customer ) Await context . SaveChangesAsync ( ) End If End Function ' Query with filtering Public Async Function GetActiveCustomersAsync ( ) As Task ( Of List ( Of Customer ) ) Return Await context . Customers _ . Where ( Function ( c ) c . IsActive ) _ . OrderBy ( Function ( c ) c . Name ) _ . ToListAsync ( ) End Function End Class Async Queries ' Single result Dim customer = Await context . Customers _ . FirstOrDefaultAsync ( Function ( c ) c . Email = email ) ' List results Dim customers = Await context . Customers _ . Where ( Function ( c ) c . IsActive ) _ . ToListAsync ( ) ' Count Dim count = Await context . Customers . CountAsync ( ) ' Any Dim exists = Await context . Customers _ . AnyAsync ( Function ( c ) c . Email = email ) ' Aggregate Dim totalOrders = Await context . Orders . SumAsync ( Function ( o ) o . Amount ) Change Tracking ' Detach entity context . Entry ( customer ) . State = EntityState . Detached ' Track changes Dim customer = Await context . Customers . FindAsync ( id ) customer . Name = "Updated Name" ' See what changed Dim entry = context . Entry ( customer ) For Each prop In entry . Properties If prop . IsModified Then Console . WriteLine ( $"{prop.Metadata.Name}: {prop.OriginalValue} -> {prop.CurrentValue}" ) End If Next Await context . SaveChangesAsync ( ) Database Migrations Code-First Migrations

Add migration

dotnet ef migrations add InitialCreate

Update database

dotnet ef database update

Rollback

dotnet ef database update PreviousMigration

Generate SQL script

dotnet ef migrations script Migration Class Public Class CreateCustomersTable Inherits Migration Protected Overrides Sub Up ( migrationBuilder As MigrationBuilder ) migrationBuilder . CreateTable ( name : = "Customers" , columns : = Function ( table ) New With { . Id = table . Column ( Of Integer ) ( nullable : = False ) . Annotation ( "SqlServer:Identity" , "1, 1" ) , . Name = table . Column ( Of String ) ( maxLength : = 200 , nullable : = False ) , . Email = table . Column ( Of String ) ( maxLength : = 255 , nullable : = False ) , . CreatedAt = table . Column ( Of DateTime ) ( nullable : = False ) } , constraints : = Sub ( table ) table . PrimaryKey ( "PK_Customers" , Function ( x ) x . Id ) table . UniqueConstraint ( "UK_Customers_Email" , Function ( x ) x . Email ) End Sub ) migrationBuilder . CreateIndex ( name : = "IX_Customers_Email" , table : = "Customers" , column : = "Email" , unique : = True ) End Sub Protected Overrides Sub Down ( migrationBuilder As MigrationBuilder ) migrationBuilder . DropTable ( name : = "Customers" ) End Sub End Class Connection String Management ' appsettings.json { "ConnectionStrings" : { "DefaultConnection" : "Server=(localdb)\mssqllocaldb;Database=MyApp;Trusted_Connection=True;" } } ' Startup configuration Public Class Startup Public Sub ConfigureServices ( services As IServiceCollection ) services . AddDbContext ( Of AppDbContext ) ( Sub ( options ) options . UseSqlServer ( Configuration . GetConnectionString ( "DefaultConnection" ) ) End Sub ) End Sub End Class Best Practices ✅ DO ' Use async methods Dim customers = Await context . Customers . ToListAsync ( ) ' Use parameterized queries command . Parameters . AddWithValue ( "@email" , email ) ' Dispose connections Using connection = New SqlConnection ( connectionString ) End Using ' Use transactions for multiple operations Using transaction = connection . BeginTransaction ( ) ' Use IQueryable for deferred execution Dim query As IQueryable ( Of Customer ) = context . Customers . Where ( Function ( c ) c . IsActive ) ❌ DON'T ' Don't concatenate SQL (SQL injection!) Dim sql = $"SELECT * FROM Users WHERE Email = '{email}'" ' Don't forget to dispose Dim connection = New SqlConnection ( connectionString ) ' No Using - leak! ' Don't block on async Dim result = GetDataAsync ( ) . Result ' Deadlock risk ' Don't load entire table when filtering Dim customers = context . Customers . ToList ( ) . Where ( Function ( c ) c . IsActive ) ' Loads all first! ' Better: context.Customers.Where(Function(c) c.IsActive).ToList()