C# data access: from ADO.NET to EF and Dapper

Learn to access databases from dot net apps using ADO.NET concepts, including connected and disconnected objects, perform insert, select, joins, update, delete, and manage transactions with sqlite and postgres.

Connects to a SQLite database with ADO.NET, opens a connection, creates a command for 'select count(*) from customers', executes scalar, and prints the result to the console.

Execute a simple select in PostgreSQL by building a connection string and running a command; refactor to a single db connection method supporting PostgreSQL and SQL Server providers via adapters.

Explore using SqlDataReader for select and join queries, reading forward-only results with execute reader inside a using, then iterate with Read() and access fields by name to print each row.

Learn to use parameters with a generic parameter method to safely build command parameters, read data, and prepare for insert, update, and delete operations.

Insert a new artist record using a reflected method with ADO.NET, executing ExecuteNonQuery to return the rows affected. Verify the insertion by querying the artist table on both databases.

Explore update and delete statements with ADO.NET, verify affected rows, and synchronize Postgres databases using targeted selects.

Use a data adapter to execute a select against the customer table and fill a data set in memory. Iterate over the resulting data rows to print last names.

Explore transactions with ADO.NET by handling exceptions, deliberately triggering a foreign key constraint, and using begin transaction, commit, and rollback to ensure updates and inserts are atomic.

Explore .NET data access with connected objects like connection, command, and data adapter, and an in-memory dataset; perform crud with parameterized sql and transactions, and preview Entity Framework and database-first.

Learn to use Entity Framework Core in a database-first workflow by scaffolding a DbContext and entity models from a PostgreSQL database, then perform basic CRUD operations.

Use Entity Framework to perform select and inner join queries with a database context, apply where filters, and include related orders, ensuring queries execute only when iterated.

Insert, update, and delete with Entity Framework by creating a customer entity, setting name and last name, and persisting changes through a tracking context with save changes and implicit transactions.

Use raw SQL with Entity Framework to execute custom queries and map results to your entity properties, enabling direct object filling and performance optimizations.

Explore Dapper, a micro ORM layered on IDbConnection that uses raw SQL, offers fast performance and simple mappings, and compare it with Entity Framework for data access decisions.

Use dapper to run a select on customers through an aDO.NET connection and map results to a customer class. Compare dapper's lightweight mapping to EF-style entities and extension methods.

Learn to perform an inner join with Dapper to map a one-to-many relationship between customers and orders, using a map keyed by customer id and split on id.

Learn insert, update, and delete operations with Dapper using connection extension methods and anonymous objects. Practice insert many and update with where clauses, then delete by last name.

Compare ADO.NET, Entity Framework, and Dapper for dot net access; learn CRUD operations, transactions, and mapping; evaluate when to use each style, with emphasis on migrations and unit of work.

Delve into Dapper by showing how data readouts map to strongly typed properties, align column names with your fields, and explore parameter types, query types, and projection.

Practice single-value queries, including first, single, and single-or-default, and compare easier joins with navigation properties, foreign keys, and query multiple for multiple results.

Learn to use lists as parameters in Dapper with anonymous objects for single and bulk inserts, execute insert statements, project and update items, and verify results across databases.

Explore how dynamic parameters in Dapper control input and output directions, declare names and types, and apply extension methods for select, insert, and store procedures.

Introduce NoSQL database types, with a focus on Mongo as a document database and key-value store, including collections, CRUD, and a JavaScript-based query language.

Explore mongodb basics, including collections of documents stored as bison, a javascript object representation, and querying with javascript expressions, plus connecting with the dotnet driver to perform operations.

connect to mongodb using the local host connection string and port 27017, create the udemy database and a customers collection, then insert and read documents.

Learn to read documents from a MongoDB collection and insert many documents using the C# MongoDB driver, including using BsonDocument and dictionary representations for schema-less data.

Learn to update multiple MongoDB documents with update many, build updates with the updates builder, and apply set operations to order fields using a test document.

Connect to a MongoDB database and delete documents with a delete many filter, noting case-sensitive text matching; then drop the customers collection and the database.

Discover Redis as a NoSQL key-value store optimized for caching, supporting strings, lists, sets, hashes, and pub/sub, with key expiry, clustering, and master-slave replication.

Connect to a Redis docker container and perform create, read, update, and delete operations using dot net, exploring strings, lists, sets, hashes, and JSON values with expire and key queries.

Connect to Redis, create a customer object with orders, and store it as a JSON string using string set; retrieve it with string get and print the result.

Learn to update and delete Redis data using a customer key and JSON objects: retrieve, modify orders, save with set, verify updates, and remove keys.

Explore NoSQL databases like MongoDB, featuring collections, documents, keys, and data types, designed for non-relational, aggregated data. Learn core operations—insert, update, and delete—and key-based queries using .NET drivers.

Define the data access layer as the abstraction that separates database access from other layers, showing how to increase testability and maintainability.

Encapsulate queries with the query object pattern to return entities and hide database access from the application. Implement a generic query interface with backends and a factory for swapping implementations.

Explore the query object pattern implemented with dapper and entity framework. Build a generic factory and query interface to execute customer queries across postgres and mongo.

Explore the repository pattern as a data access point that encapsulates create, read, update, and delete operations and enables unit of work, testing, and command query separation with store substitution.

Implement a simple repository with async get all and get by specification, and apply the unit of work pattern to insert and commit changes using Entity Framework Core.

Explore how entity framework and the repository pattern complement each other, enabling a flexible data access strategy with unit of work, testing, and database independence.

Set up a SQLite database for the course by creating customers and orders tables with a foreign key, inserting sample data, and illustrating an inner join to show table relationships.

Set up a postgres database in a docker container, exposing ports, and configuring a container name, user, and database, then connect with a local host connection string.

Master essential SQL statements for data access, including select, insert, update, delete, inner join, and count, with customers and orders and basic where clauses.

Learn to add the ADO.NET provider NuGet packages in Visual Studio, using the Package Manager Console or the wizard, then verify references through a build.

Connect to a local MongoDB NoSQL server, create the customers collection, insert documents, update and delete records, and explore implicit schema and flexible document structure.

Explore the .net basics of io through streams, covering backing stores like file, memory, network streams, and adapters for reading and writing bytes sequentially with encryption and compression.

Explore serialization and deserialization, converting an in-memory object graph to bytes for storage or transmission and back. Cover implicit and explicit serialization in .NET and json and xml formats.