Welcome to the cutting edge. In this comprehensive article, I show how I combine AI tools and prompt engineering techniques to create a fully functional, advanced C# database solution using EF Core, Dapper, and ADO.NET. This article covers not only the architecture and benchmarks but also provides the full C# repository class, SQL database scripts, and the exact prompts I used to generate each part.

This is the ultimate article for using AI as your coding partner, from zero to production-ready code.

Vibe Coding Workflow: Multi-Prompt Strategy

Instead of relying on a single prompt, I break the problem down into focused layers. Here’s how I explain the AI step by step.

Prompt 1. Generate Base Architecture

Prompt 2. Write Repository Layer

Full Example C# Class: AdvancedStudentRepository

using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;
using System.Collections.Generic;
using Dapper;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

public class AdvancedStudentRepository : IStudentRepository
{
    private readonly AppDbContext _context;
    private readonly IDbConnection _connection;
    private readonly ILogger<AdvancedStudentRepository> _logger;

    private static readonly Func<AppDbContext, int, Task<Student?>> _compiledGetById =
        EF.CompileAsyncQuery((AppDbContext ctx, int id) => ctx.Students.FirstOrDefault(s => s.Id == id));

    public AdvancedStudentRepository(AppDbContext context, string connectionString, ILogger<AdvancedStudentRepository> logger)
    {
        _context = context;
        _connection = new SqlConnection(connectionString);
        _logger = logger;
    }

    public async Task<Student?> GetStudentByIdEfCoreAsync(int id, CancellationToken cancellationToken)
    {
        try
        {
            return await _compiledGetById(_context, id);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "EF Core error fetching student with ID {Id}", id);
            throw;
        }
    }

    public async Task<IEnumerable<Student>> GetActiveStudentsDapperAsync(CancellationToken cancellationToken)
    {
        try
        {
            const string sql = "SELECT * FROM Students WHERE IsActive = 1";
            return await _connection.QueryAsync<Student>(sql);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Dapper error fetching active students");
            throw;
        }
    }

    public async Task<IEnumerable<Student>> GetStudentsByIdsAdoNetAsync(List<int> ids, CancellationToken cancellationToken)
    {
        try
        {
            var table = new DataTable();
            table.Columns.Add("Id", typeof(int));
            ids.ForEach(id => table.Rows.Add(id));

            using var cmd = new SqlCommand("GetStudentsByIds", (SqlConnection)_connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            cmd.Parameters.AddWithValue("@Ids", table);

            var students = new List<Student>();
            await _connection.OpenAsync(cancellationToken);
            using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
            while (await reader.ReadAsync(cancellationToken))
            {
                students.Add(new Student
                {
                    Id = reader.GetInt32(reader.GetOrdinal("Id")),
                    FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
                    LastName = reader.GetString(reader.GetOrdinal("LastName")),
                    IsActive = reader.GetBoolean(reader.GetOrdinal("IsActive"))
                });
            }
            await _connection.CloseAsync();

            return students;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "ADO.NET error fetching students by IDs");
            throw;
        }
    }
}

This class brings together EF Core, Dapper, and ADO.NET in one seamless, production-ready design.

Prompt 3. Provide SQL Database Scripts

Prompt: Write SQL Server scripts to create the Students table and define dbo.IdList table type, and create a stored procedure GetStudentsByIds that accepts a TVP and returns matching students.

AI Result

CREATE TABLE Students (
    Id INT PRIMARY KEY,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    IsActive BIT
);

CREATE TYPE dbo.IdList AS TABLE (Id INT);

CREATE PROCEDURE GetStudentsByIds @Ids dbo.IdList READONLY
AS
BEGIN
    SELECT * FROM Students WHERE Id IN (SELECT Id FROM @Ids);
END;

Now the ADO.NET TVP queries work end-to-end.

Prompt 4. Build Benchmark Harness

Prompt 5. Generate Documentation + Deployment Guide

Final Takeaway

By using smart, layered prompt engineering, I was able to generate a full-stack, production-grade C# database solution that includes.