Published / updated29 Aug 2026

ADO.NET CRUD, Procedures and DataSets

Before you start

You need: readers and mapping (Article 04), and stored procedures (Track 06 Article 08).

Time: about 50 minutes, plus the practice.

Learning objective

Write a complete data-access class over one entity using stored procedures, and read DataTable code without adding more of it.

Topics

• Create, with the generated key
• Read: single, list, paged
• Update, with a concurrency check
• Soft delete
• Stored procedures with input and output parameters
• Table-valued parameters
• DataTable, DataSet and SqlDataAdapter
• When disconnected data is still the right answer

Create

public Guid Create(Student student)
{
    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand("dbo.usp_CreateStudent", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = student.SchoolId;
        command.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = student.Name;
        command.Parameters.Add("@RollNumber", SqlDbType.NVarChar, 20).Value = student.RollNumber;
        command.Parameters.Add("@ClassName", SqlDbType.NVarChar, 10).Value = student.ClassName;
        command.Parameters.Add("@Section", SqlDbType.NVarChar, 1).Value = student.Section;
        command.Parameters.Add("@DateOfBirth", SqlDbType.Date).Value = student.DateOfBirth;
        command.Parameters.Add("@ParentName", SqlDbType.NVarChar, 100).Value = student.ParentName;
        command.Parameters.Add("@ParentPhone", SqlDbType.NVarChar, 15).Value = student.ParentPhone;
        command.Parameters.Add("@Address", SqlDbType.NVarChar, 250).Value =
            (object?)student.Address ?? DBNull.Value;

        SqlParameter newId = command.Parameters.Add("@NewId", SqlDbType.Int);
        newId.Direction = ParameterDirection.Output;

        SqlParameter newPublicId = command.Parameters.Add("@NewPublicId", SqlDbType.UniqueIdentifier);
        newPublicId.Direction = ParameterDirection.Output;

        connection.Open();

        try
        {
            command.ExecuteNonQuery();
        }
        catch (SqlException ex) when (ex.Number == 2627 || ex.Number == 2601)
        {
            throw new DuplicateRollNumberException(student.RollNumber, ex);
        }

        student.Id = (int)newId.Value;
        student.PublicId = (Guid)newPublicId.Value;

        return student.PublicId;
    }
}

Two things to take from this.

Translate the SQL error into a domain exception. A SqlException with Number == 2627 reaching the UI layer means the UI must know about SQL Server error numbers. DuplicateRollNumberException is something a controller can catch and turn into a field-level message.

Catch by number, not by parsing the message. Message text is localised and changes between versions; the number does not.

public class DuplicateRollNumberException : Exception
{
    public string RollNumber { get; }

    public DuplicateRollNumberException(string rollNumber, Exception inner)
        : base($"Roll number '{rollNumber}' is already in use.", inner)
    {
        RollNumber = rollNumber;
    }
}

Read

public Student? GetByPublicId(int schoolId, Guid publicId)
{
    const string sql = @"
        SELECT s.Id, s.PublicId, s.SchoolId, s.Name, s.RollNumber, s.ClassName,
               s.Section, s.DateOfBirth, s.ParentName, s.ParentPhone, s.Address, s.Status
        FROM   dbo.Student AS s
        WHERE  s.SchoolId = @SchoolId AND s.PublicId = @PublicId;";

    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
        command.Parameters.Add("@PublicId", SqlDbType.UniqueIdentifier).Value = publicId;

        connection.Open();

        using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
        {
            if (!reader.Read())
            {
                return null;
            }

            return MapStudent(reader);
        }
    }
}

SchoolId is in the WHERE clause of every method in this class, not just the list. A PublicId is hard to guess, but "hard to guess" is not an access control — if one leaks through a shared URL, a log file, or browser history, the tenant filter is what stops it reaching another school's record.

Returning null rather than throwing lets the caller decide whether "not found" is an error. A controller turns it into a 404; a bulk import might skip it.

Update, with a concurrency check

public void Update(Student student)
{
    const string sql = @"
        UPDATE dbo.Student
        SET    Name        = @Name,
               RollNumber  = @RollNumber,
               ClassName   = @ClassName,
               Section     = @Section,
               DateOfBirth = @DateOfBirth,
               ParentName  = @ParentName,
               ParentPhone = @ParentPhone,
               Address     = @Address,
               RowVersion  = @NewRowVersion
        WHERE  SchoolId   = @SchoolId
          AND  PublicId   = @PublicId
          AND  RowVersion = @ExpectedRowVersion;";

    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = student.SchoolId;
        command.Parameters.Add("@PublicId", SqlDbType.UniqueIdentifier).Value = student.PublicId;
        command.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = student.Name;
        command.Parameters.Add("@RollNumber", SqlDbType.NVarChar, 20).Value = student.RollNumber;
        command.Parameters.Add("@ClassName", SqlDbType.NVarChar, 10).Value = student.ClassName;
        command.Parameters.Add("@Section", SqlDbType.NVarChar, 1).Value = student.Section;
        command.Parameters.Add("@DateOfBirth", SqlDbType.Date).Value = student.DateOfBirth;
        command.Parameters.Add("@ParentName", SqlDbType.NVarChar, 100).Value = student.ParentName;
        command.Parameters.Add("@ParentPhone", SqlDbType.NVarChar, 15).Value = student.ParentPhone;
        command.Parameters.Add("@Address", SqlDbType.NVarChar, 250).Value =
            (object?)student.Address ?? DBNull.Value;
        command.Parameters.Add("@ExpectedRowVersion", SqlDbType.UniqueIdentifier).Value =
            student.RowVersion;
        command.Parameters.Add("@NewRowVersion", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();

        connection.Open();

        int affected = command.ExecuteNonQuery();

        if (affected == 0)
        {
            throw new ConcurrencyException(
                "This student was changed or removed by someone else. Reload and try again.");
        }
    }
}

Without the RowVersion check this is last write wins: two clerks open the same student, one changes the phone, the other changes the class, and whoever saves second silently discards the first change. Neither is told.

Including the expected version in the WHERE clause means a stale save matches zero rows, and @@ROWCOUNT of 0 tells you why. A ROWVERSION/TIMESTAMP column is the SQL Server-native alternative; a UNIQUEIDENTIFIER you rotate yourself works on any provider.

Note that affected == 0 now has two possible meanings — the row is gone, or the version moved. Re-read the row when you need to tell the user which.

Soft delete

public void Deactivate(int schoolId, Guid publicId)
{
    const string sql = @"
        UPDATE dbo.Student
        SET    Status = @Status
        WHERE  SchoolId = @SchoolId AND PublicId = @PublicId;";

    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
        command.Parameters.Add("@PublicId", SqlDbType.UniqueIdentifier).Value = publicId;
        command.Parameters.Add("@Status", SqlDbType.TinyInt).Value = (byte)StudentStatus.Inactive;

        connection.Open();

        if (command.ExecuteNonQuery() == 0)
        {
            throw new InvalidOperationException("Student not found.");
        }
    }
}

Soft delete because the student has exam results, attendance and payment history. The obligation it creates: every read must filter Status <> 1. Miss it in one report and removed students reappear.

Stored procedure with output parameters

public FeePaymentResult RecordPayment(FeePayment payment)
{
    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand("dbo.usp_RecordFeePayment", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = payment.SchoolId;
        command.Parameters.Add("@FeeAccountId", SqlDbType.Int).Value = payment.FeeAccountId;

        SqlParameter amount = command.Parameters.Add("@Amount", SqlDbType.Decimal);
        amount.Precision = 18;
        amount.Scale = 2;
        amount.Value = payment.Amount;

        command.Parameters.Add("@PaymentMode", SqlDbType.TinyInt).Value = (byte)payment.PaymentMode;
        command.Parameters.Add("@CollectedBy", SqlDbType.NVarChar, 100).Value = payment.CollectedBy;

        SqlParameter newPaymentId = command.Parameters.Add("@NewPaymentId", SqlDbType.Int);
        newPaymentId.Direction = ParameterDirection.Output;

        SqlParameter receiptNumber = command.Parameters.Add("@ReceiptNumber", SqlDbType.NVarChar, 30);
        receiptNumber.Direction = ParameterDirection.Output;

        connection.Open();

        try
        {
            command.ExecuteNonQuery();
        }
        catch (SqlException ex) when (ex.Number == 50012)
        {
            throw new PaymentExceedsBalanceException(ex.Message, ex);
        }

        return new FeePaymentResult
        {
            PaymentId = (int)newPaymentId.Value,
            ReceiptNumber = (string)receiptNumber.Value
        };
    }
}

Errors raised by THROW 50012, '...', 1 in the procedure arrive as SqlException with that exact number, which is why choosing stable numbers for domain errors is worth doing.

Output parameters are only populated after execution completes, and if a reader was opened, only after it is closed. Reading them too early returns null, and the procedure is not at fault.

An output parameter the procedure never assigns comes back as DBNull.Value, so check before casting when the procedure has paths that skip the assignment.

Table-valued parameters

Saving forty exam results one at a time is forty round trips. A table-valued parameter makes it one.

CREATE TYPE dbo.ExamResultList AS TABLE
(
    StudentId     INT          NOT NULL,
    MarksObtained DECIMAL(5,2) NULL,
    IsAbsent      BIT          NOT NULL
);

public void SaveExamResults(int schoolId, int examId, List<ExamResult> results)
{
    DataTable table = new DataTable();
    table.Columns.Add("StudentId", typeof(int));
    table.Columns.Add("MarksObtained", typeof(decimal));
    table.Columns.Add("IsAbsent", typeof(bool));

    foreach (ExamResult result in results)
    {
        DataRow row = table.NewRow();
        row["StudentId"] = result.StudentId;
        row["MarksObtained"] = (object?)result.MarksObtained ?? DBNull.Value;
        row["IsAbsent"] = result.IsAbsent;

        table.Rows.Add(row);
    }

    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand("dbo.usp_SaveExamResults", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
        command.Parameters.Add("@ExamId", SqlDbType.Int).Value = examId;

        SqlParameter tvp = command.Parameters.AddWithValue("@Results", table);
        tvp.SqlDbType = SqlDbType.Structured;
        tvp.TypeName = "dbo.ExamResultList";

        connection.Open();
        command.ExecuteNonQuery();
    }
}

Three requirements: SqlDbType.Structured, TypeName matching the SQL type exactly, and column order in the DataTable matching the type definition — names are not used for matching, so a reordered DataTable silently maps values to the wrong columns.

AddWithValue is correct here; a TVP has no size or precision to specify.

DataTable, DataSet and SqlDataAdapter

The disconnected model: fetch everything into memory, close the connection, work offline.

public DataTable GetStudentsTable(int schoolId, string className)
{
    const string sql = @"
        SELECT   s.Id, s.RollNumber, s.Name, s.ClassName, s.Section, s.ParentPhone
        FROM     dbo.Student AS s
        WHERE    s.SchoolId = @SchoolId AND s.ClassName = @ClassName AND s.Status <> 1
        ORDER BY s.Section, s.Name;";

    DataTable table = new DataTable();

    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
        command.Parameters.Add("@ClassName", SqlDbType.NVarChar, 10).Value = className;

        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            adapter.Fill(table);
        }
    }

    return table;
}

Fill opens and closes the connection itself — an explicit Open() is unnecessary.

Reading a DataTable:

foreach (DataRow row in table.Rows)
{
    string name = (string)row["Name"];

    string? phone = null;

    if (row["ParentPhone"] != DBNull.Value)
    {
        phone = (string)row["ParentPhone"];
    }
}

A DataSet holds several DataTables plus relations between them:

DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Students");

Why not to add more of it

Problem	Detail
Untyped	row["Nmae"] compiles and throws at run time
Memory	Every value boxed; a large table costs several times a List<T>
DBNull everywhere	Every access needs a check
No compile-time safety	Renaming a column breaks nothing until it runs
Hard to test	Constructing a DataTable for a unit test is tedious

Objects are better in every respect for new code. List<Student> is typed, cheap, and refactor-safe.

Where it is still correct

Do not convert working DataTable code as an incidental change. A Web Forms GridView bound to a DataTable depends on the column names for its bound fields — replacing it with List<Student> is a UI change, not a data-access change, and it belongs in its own ticket.

Two genuine remaining uses: table-valued parameters, which take a DataTable by design, and bulk copy:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
    bulkCopy.DestinationTableName = "dbo.StudentImport";
    bulkCopy.BatchSize = 5000;
    bulkCopy.ColumnMappings.Add("RollNumber", "RollNumber");
    bulkCopy.ColumnMappings.Add("Name", "Name");

    bulkCopy.WriteToServer(table);
}

SqlBulkCopy inserts hundreds of thousands of rows far faster than individual statements. Always set ColumnMappings explicitly — without them, mapping is by ordinal position and a reordered source silently loads data into the wrong columns.

Errors you will hit

Message	Cause	Fix
Procedure or function expects parameter '@SchoolId'	Not supplied by the caller	Add it
Procedure or function has too many arguments specified	Passed one the procedure does not declare	Check the signature
Output parameter is null after execution	Read before the reader was closed	Close the reader, then read output parameters
SCOPE_IDENTITY() returns null	Read from a different scope or connection	Return it from the same batch
The insert works but the id is 0	Never read the returned identity	ExecuteScalar on the SELECT SCOPE_IDENTITY()

Output parameters are only populated after the reader is closed. Read them last, not while you are still iterating rows.

Common mistakes

• Omitting SchoolId from one method's WHERE clause
• Letting SqlException reach the UI instead of translating it
• Matching on exception message text rather than Number
• No concurrency check, so saves silently overwrite each other
• Forgetting Status <> 1 on a read
• Reading output parameters before the reader is closed
• Casting an unassigned output parameter without checking DBNull.Value
• TVP DataTable columns in a different order from the SQL type
• Missing TypeName or SqlDbType.Structured on a TVP
• SqlBulkCopy without explicit ColumnMappings
• Writing new DataSet code
• Converting working DataTable UI code as a side change

Practice

The course exercises are execute parameterized CRUD and call procedures with output parameters.

1. Write a StudentRepository with Create, GetByPublicId, Search, Update and Deactivate, all through stored procedures.
2. Confirm SchoolId appears in every WHERE clause. Remove it from one method and verify you can now read another school's record.
3. Insert a duplicate roll number. Confirm SqlException.Number is 2627 and that your repository throws DuplicateRollNumberException instead.
4. Add the RowVersion concurrency check. Load one student into two variables, save the first, then save the second. Confirm the second throws.
5. Remove the concurrency check and repeat. Confirm the first change is silently lost — this is what the check prevents.
6. Call usp_RecordFeePayment with output parameters. Read @NewPaymentId before closing a reader on the same command and record the result.
7. Trigger THROW 50012 by overpaying. Confirm the number arrives intact in SqlException.Number.
8. Create the ExamResultList type and save 40 results in one call. Time it against 40 individual inserts.
9. Reorder two columns in the TVP DataTable and confirm the data lands in the wrong columns with no error.
10. Load 100,000 rows with SqlBulkCopy, once with ColumnMappings and once without. Compare correctness and speed.

Then run the course debugging exercise — trace a failed SQL command. For each, record the exception number and message: a missing required parameter, a stored procedure called without CommandType, a string longer than the declared size, a null where DBNull.Value was needed, and a TVP with the wrong TypeName.

You can now

• Write a complete repository over one entity using stored procedures
• Pass input and read output parameters correctly
• Return and read a newly inserted identity
• Take schoolId as a parameter on every method
• Say why output parameters must be read after the reader closes

Review questions

1. Why translate SqlException into a domain exception at the repository boundary?
2. What does a concurrency check in the WHERE clause prevent, and how do you detect it fired?
3. Why must a table-valued parameter's DataTable column order match the SQL type?
4. When is a DataTable still the correct choice?

Next: Transactions in ADO.NET