Chapter 4: .

NET Database Objects

Learning Objectives By the end of the lesson you should be able to:

 Understand and apply the basic backend development practices

 Define the similarities and differences of SQL Server with other
RDBMS
 Familiarize and apply the core concepts of [Link]
 Understand the advantages and features of [Link]

Microsoft SQL Server

MS SQL Server is a relational database management system (RDBMS)

developed by Microsoft. This product is built for the basic function of
storing retrieving data as required by other applications. It can be run
either on the same computer or on another across a network.

 It is a software, developed by Microsoft, which is implemented

from the specification of RDBMS.
 It is also an ORDBMS.
 It is platform dependent.
 It is both GUI and command based software.
 It supports SQL (SEQUEL) language which is an IBM product,
non-procedural, common database and case insensitive language.

Microsoft SQL Server Editions

 Enterprise - This is the top-end edition with a full feature set.

 Standard - This has less features than Enterprise, when there is
no requirement of advanced features.
 Developer - This is similar to Enterprise, but licensed to only one
user for development, testing and demo. It can be easily upgraded
to Enterprise without reinstallation.
 Express - This is free entry level database. It can utilize only 1
CPU and 1 GB memory, the maximum size of the database is 10
GB.
 Compact - This is free embedded database for mobile application
development. The maximum size of the database is 4 GB.

SQL Server Management Studio

SQL Server Management Studio is a workstation component/client tool
that will be installed if we select workstation component in installation
steps. This allows you to connect to and manage your SQL Server from a
graphical interface instead of having to use the command line.

SQL Server Basic Commands

Create Command:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns ));

Select Command:

SELECT column1, column2, columnN FROM table_name;

Or

SELECT * FROM table_name;

Insert Command:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)

VALUES (value1, value2, value3,...valueN);

Or

INSERT INTO TABLE_NAME VALUES

(value1,value2,value3,...valueN);

Update Command:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

Delete Command:

DELETE FROM table_name

WHERE [condition];
Microsoft [Link]

[Link] or Active Data Objects .NET is the data access component for
the .NET Framework. It is made of a set of classes that is used for
connecting to a database, providing access to application data and
retrieving results.

It’s a set of object-oriented classes that provide a rich set of data

components to create high-performance, reliable and scalable database
applications for client-server applications as well as distributed
environments over the Internet and intranets.

The [Link] model utilizes XML to store the data in cache and
transfer the data among applications. Datasets use XML schemas to
store and transfer data among applications. You can even use this XML file
from other applications without interacting with the actual dataset.

Advantages of [Link]

 Single Object-Oriented API

o The [Link] provides a single object-oriented set of
classes. There are different data providers to work with
different data sources, but the programming model for all
these data providers works in the same way.
o The [Link] classes are easy to use and to understand
because of their object-oriented nature.
 Managed Code
o The [Link] classes are managed classes. They take all
the advantages of .NET CLR, such as language
independency and automatic resource management.14 All
.NET languages access the same API.
 XML Support
o XML is an industry standard and the most widely used
method of sharing data among applications over the
Internet. All components and applications can share this data
and you can transfer data via different protocols such as
HTTP.
 Visual Data Components
o Visual Studio .NET offers [Link] components and
data–bound controls to work in visual form.15 That means
 you can use these components as you use any windows
controls.
o VS .NET also offers the data form wizard, which you can
use to write full-fledged database applications without
writing a single line of code.16

Features of [Link]

 Interoperability
o We know that XML documents are text-based formats.17
So, one can edit and edit XML documents using standard
text-editing tools. [Link] uses XML in all data
exchanges and for internal representation of data.18
 Maintainability
o [Link] is built around the idea of separation of data
logic and user interface.19 It means that we can create our
application in independent layers.
 Programmability
o It is a programming style in which user words are used to
construct statements or evaluate expressions. For example:
If we want to select the “Marks” column from “Kawal” from
the “Student” table, the following is the way to do so:
[Link]("John").Marks;
 Performance
o It uses disconnected data architecture which is easy to
scale as it reduces the load on the database. Everything is
handled on the client-side, so it improves performance.
 Scalability
o It means meeting the needs of the growing number of
clients, which degrading performance. As it uses
disconnected data access, applications do not retain
database lock connections for a longer time. Thus, it
accommodates scalability by encouraging programmers to
conserve limited resources and allow users to access data
simultaneously.

[Link] Architecture
[Link] Data Providers

A data provider is used for connecting to a database, executing

commands and retrieving data, storing it in a dataset, reading the
retrieved data and updating the database. [Link] Data Providers
consists of the following four objects:

 Connection
 Command
 DataAdapter
 DataReader

Connection

 This component is used to set up a connection with a data source.

 SqlConnection and SqlCommand are classes of a connected
architecture and found in the [Link] namespace.
The SqlConnection class makes a connection with the database.
 The SqlConnection takes a connection string from the database
server which typically contains the server name, database name, and
security cedentials as parameter and is used like so:
  Two important methods of SqlConnection Class
o open(): The open() method is used to open the Database
connection.
o close(): The close() method is used to close the Database
connection.

Command

 The connection (database connection) is used by the SqlCommand

to work with that database. The SqlCommand class is used to
execute the SQL statements.
 SqlCommand Properties:
o Command Text - The commandText property is used to
set the SQL statement.
o Connection - This property is used to get connection to the
database source which is specified in SqlConnection.
 The SqlCommand takes two parameters, the command text
containing the SQL query, and the connection. The command can
also be executed through the use of prepared statements like so:

 SqlCommand Methods
o ExecuteNonQuery() - does not return any record. Which
means we use it in all operations with databases except
retrieving records from a database. It only returns the
number of affected rows.
o ExecuteScalar() - returns a single value from the
database. Generally, it is used with an aggregate function (a
function which returns a single value).
o ExecuteReader() - return a set of records from a
database.
DataAdapter

 Data is transferred to and from a database through a data

adapter. It performs the activities necessary to get the data from the
data source on the server into the database that's held in the DataSet.
To do that, the DataAdapter lets us specify the commands that
should be carried out to retrieve and update data.
 A DataAdapter can be used in conjunction with a DataTable then
setting the DataTable as a data source for objects such as a
DataGridView.
 The HeaderText property in the following example changes the
headers displayed in the DataGridView instead of displaying the
actual column names of the table.

DataSet

DataSet is an in-memory representation of data. It is a disconnected,

cached set of records that are retrieved from a database. When a
connection is established with the database, the data adapter creates a
dataset and stores data in it. After the data is retrieved and stored in a
dataset, the connection with the database is closed.

 DataTable - It represents a table in the DataTableCollection of a

dataset. It consists of the DataRow and DataColumn objects. The
DataTable objects are case-sensitive.
 DataRowCollection - It contains all the rows in a DataTable.
 DataView - It represents a fixed customized view of a DataTable
for sorting, filtering, searching, editing and navigation.
 PrimaryKey - It represents the column that uniquely identifies a
row in a DataTable.
Chapter 6: Introduction to Entity Framework

Object Relational Mapping (ORM)

An Object-Relational Mapping (ORM) framework is a software tool that
provides a way to map between a relational database and an object-
oriented programming language. It allows developers to interact with a
database using object-oriented programming techniques rather than using
SQL directly.

ORM frameworks typically provide an abstraction layer between the

application and the database, allowing developers to work with the database
using objects instead of tables and rows. This abstraction makes it easier to
manipulate data in the database without having to worry about the
underlying database schema or SQL queries.

Some popular ORM frameworks for C# include Entity Framework (by

Microsoft), NHibernate, and Dapper. These frameworks all provide
different levels of abstraction and flexibility, and each has its own strengths
and weaknesses.

ORM Example

Entity Framework, for example, provides a high level of abstraction and

allows developers to work with the database using LINQ queries and
DbSet objects. NHibernate is like Entity Framework but provides more
flexibility and customization options. Dapper, on the other hand, is a
lightweight ORM framework that provides a thin layer of abstraction over
SQL queries.

Benefits of ORM
In general, ORM frameworks can provide several benefits to developers,
including:

1. Reduced development time - By providing an abstraction layer

between the application and the database, ORM frameworks can
reduce the amount of boilerplate code that developers need to
write.
2. Increased productivity - Developers can work with the database
using object-oriented programming techniques, which can be more
intuitive and easier to understand than SQL queries.
3. Improved maintainability - ORM frameworks can make it easier to
modify the database schema or change the underlying database
engine, since developers only need to update the mapping between
the objects and the database.
4. Improved security - ORM frameworks can help prevent SQL
injection attacks by automatically parameterizing SQL queries
and sanitizing user input.

What is Entity Framework?

Entity Framework (EF) is an Object-Relational Mapping (ORM)

framework that enables developers to work with databases using object-
oriented programming concepts. It is a component of the Microsoft .NET
Framework and is used to interact with relational databases such as SQL
Server, MySQL, and Oracle in C# applications.

In an EF-based application, developers work with entities, which are C#

classes that represent database tables or views. EF provides a set of APIs
to perform CRUD (Create, Read, Update, Delete) operations on these
entities, without requiring developers to write SQL queries directly.

Benefits of Entity Framework

Entity Framework (EF) is a powerful ORM framework for C# applications

that provides several benefits for developers, including:

1. Object-oriented data access - EF allows developers to work with

the database using object-oriented programming techniques, which
can be more intuitive and easier to understand than SQL queries.
This abstraction makes it easier to manipulate data in the database
 without having to worry about the underlying database schema or
SQL queries.
2. Automatic query generation - EF generates SQL queries
automatically based on the LINQ expressions used in the
application code, which can reduce the amount of boilerplate code
that developers need to write.
3. Schema management - EF provides a simple way to manage
database schema changes and migrations, making it easier to
modify the database schema or change the underlying database
engine. EF can automatically generate SQL scripts to create or
modify database tables, columns, and relationships.
4. Performance optimizations - EF includes several performance
optimizations, such as caching and lazy loading, which can
improve the performance of database queries and reduce the
number of database round trips required.
5. Cross-platform support - EF supports multiple database engines
and platforms, including SQL Server, MySQL, Oracle, and
PostgreSQL. This makes it easy to switch between different
database engines or platforms without changing the application code.

EF Models

In this example, we're retrieving an existing Student object with an ID of 1

using the Find() method, updating the Name property, and then calling the
SaveChanges() method to save the changes to the database.

To define this relationship in our model, we can add a collection

navigation property to each entity that references the other entity. Entity
Framework will automatically generate a join table in the database to
represent this many-to-many relationship.
In this example, we have defined a DbContext class called SchoolContext
that contains DbSet properties for the Student and Course entities. We
have also overridden the OnModelCreating method to define the many-
to-many relationship between the entities.
We use the ModelBuilder object to define the relationship using the
HasMany, WithMany, and UsingEntity methods. The ToTable method is
used to specify the name of the join table in the database.

Data Manipulation (Read)

We use the modelBuilder object to define the relationship using the
HasMany, WithMany, and UsingEntity methods. The ToTable method is
used to specify the name of the join table in the database.

We're using the SchoolContext class that we defined earlier to retrieve a list
of students whose name starts with "J". We're using LINQ queries to
filter and sort the data, and we're using the ToList() method to execute the
query and retrieve the results as a list of Student objects.
Data Manipulation (Update)

In this example, we're retrieving an existing Student object with an ID of 1

using the Find() method, updating the Name property, and then calling the
SaveChanges() method to save the changes to the database.

Data Manipulation (Delete)

In this example, we're retrieving an existing Student object with an ID of 1

using the Find() method, removing it from the Students DbSet using the
Remove() method, and then calling the SaveChanges() method to save the
changes to the database.

Migrations

In real world projects, data models change as features get implemented:

new entities or properties are added and removed, and database schemas
need to be changed accordingly to be kept in sync with the application.
The migrations feature in EF Core provides a way to incrementally
update the database schema to keep it in sync with the application's data
model while preserving existing data in the database.

Entity Framework Migrations is a feature that allows you to manage

database schema changes over time. With Migrations, you can create new
tables, modify existing tables, and perform other schema changes
without losing any data.

Here's how you can use Migrations to manage database updates:

1. Install the Entity Framework Tools - Migrations requires the

Entity Framework Tools to be installed. You can install them using
the following command in the terminal:

2. Enable Migrations - Once you have the Entity Framework Tools

installed, you can enable Migrations in your project using the
following command :

This will create a new Migrations folder in your project and create
an initial migration named "InitialCreate".
3. Modify the Model - After enabling Migrations, you can modify
your model classes to add or modify tables, columns, and
relationships.
4. Generate a New Migration - After modifying your model classes,
you can generate a new migration using the following command:

5. Apply the Migration - After generating a new migration, you can

apply it to the database using the following command:

6. Repeat as Needed - You can repeat the steps as needed to make

additional changes to the model classes and apply them to the
database.

Overall, Entity Framework Migrations provides a powerful set of features

for managing database schema changes over time. With Migrations, you can
modify your model classes to add or modify tables, columns, and
relationships, and then generate and apply Migrations to update the database
schema to match the changes. This allows you to manage database updates
in a structured and controlled way, without losing any data or breaking
any existing functionality.