ADO NET IMPLEMENTATION

 SqlConnection
 SqlDataAdapter
 DataSet / DataTable
 DataView
 Compute()
 Find()
 DataGridView

These things are the most used keywords around [Link] model.

Firslt we are going to need a connection string. If we have a db on a local machine, then we just
extract the server name and DB name just like this :
string connectionString =

"Data Source=DESKTOP-IVHRL9V;Initial Catalog=AdventureWorks2022;Integrated

Security=True;Encrypt=False;TrustServerCertificate=True";

Afterwards, we create a connection instance using SqlConnection class and pass a connection
string as an argument. When there is a necessity to interact with DB its necessary to open the
connection, and after finishing it , closing it. We can manually do that by [Link](),
[Link]() methods , but more conventional way would be the following :

Now it doesn’t need explicit opening and closing and is managed automatically.

Now lets tak about the SqlAdapter , which is basically a bridge between Db and memory
(DataTable/DataSet), it can be used for updates , inserts and deletes from C# side too. it is
mainly used to fill out a DataTable or a DataSet with data and also when u don’t want the
connection to be hanging open when manipulating DB , it stores changes in memory and
applies it once (similarly like local git repo and origin). So we open connection with Using
statement and fetch data shown in the picture :

The connection opens, a SqlDataAdapter object is created , and the query and connection string
are passed as an argument , instantiated DataTable object and used .Fill() method of
SqlDataAdapter class and fetched data inside that DataTable.

DataTable:

 Represents one table of data in memory.

 Can hold rows, columns, and primary keys.

 Can be bound directly to a DataGridView.

DataSet:

 Can hold multiple DataTables and relations between them.

 Useful when you need master-detail data or work with multiple tables in memory.

Next we go to DataViews, that’s basically a sql server view, allows filtering , searching , sorting
without changing data. Used for quick sorting and filtering. Now lets explain how does the
sorting and filtering happen. DataView is a class like the other names here mentioned , so we
start with instantiating it firstly and passing a table to which the filters and sorts should be
applied . .RowFilter() method is used for filtering data in that table and .Sort() will sort the
table based on what u assign to it. Lets see the code and implementation :

Each row of the DataView is type of DataRowView .

Compute (SUM, AVG, COUNT, MAX, MIN)
object [Link](string expression, string filter);

this is the main signature of writing Compute statement.

expression means an aggregate function expression like SUM, AVG, COUNT, MIN, MAX applied
to a column.
filter: A filter condition (like a SQL WHERE clause) that determines which rows are used in the
computation. Pass an empty string or [Link] to compute across all rows

Return value: Returns an Object, so you typically need to cast it to your expected data type.

The Compute() method supports Sum(), Avg(), Count(), Min(), and Max().

The implementation goes like this :

We just took already populated DataTable and computed it one time with no filter and second
time with a filter : “BusinessEntityId <110” . because these are aggregate processes , they return
a single value so they are stored in int , double , decimal or etc. but because the return type is a
generic object, we must cast it and match the implicit casting to the variable type like its shown
in the photo.
Search using Find()
The Find method is used to search for a particular row using a primary key value.

this is the protocol for searching for a row in a Datatable using a PrimaryKey or PrimaryKeys so
when we have composite keys we pass an object arrar instead of a single key.

Important Requirement is that before searching for data inside a table , as we already said we
need PK to use .Find() method , so we have to set it in our DataTable. This happens like this :

So now this primary key can be used in .Find() method as an argument .

Here we also added if statement to check , if null , we say that row is absent , if not return row
in a readable format.

DataSet ; DataGridView

DataSet is an In-memory disconnected representation of data , it’s basically a mini relational DB

in RAM. DataSet is a bit different than a DataTable, it not only stores columns and rows , but it
can house multiple tables , set up PKs and FKs, therefore form Connections between tables. It
can also store constraints.
Here is an example of fetching data from Db inside a DataSet and representing it on a
DataGriView. We took data and used SqlDataAdapter object for that , also instantiated DataSet
object too and then just filled Dataset with [Link]() method and passed the dataset and
Table name (table name is optional and totally based on preference) as an argument. Then we
just returned the data into a gridView.

ADDING ROWS TO A DATASET

here is an example of how a row can be added to a DB. First we open connection . then using
sql adapter we run query . SqlCommandBuilder automatically GENERATES INSERT, UPDATE and
DELETE SQL commands and assigns them to the SqlDataAdapter. and assign those capabilities to
adapter that is SqlAdapter object . then we create a dataset, we fill that DataSet with data from
adapter’s query and create a DataTable “Personali” inside that DataSet. Now we instantiate
DataRow object that is basically a new row , an extension of the tbale “Personali”. Now the
values are null until we assign values to row fields , so we access the row fields by square
brackets and use TextBox elements from WindowsForm to get data to assign to that row. Finally
we add that row to that table, and until we don’t update the DataSet the change wont come
up , so we Update :
[Link](dataset, “Personali”); adapter is eligible to update because before we used
SqlCommandBuilder object that analyzes the SELECT command and builds the adapter’s
InsertCommand, UpdateCommand, and DeleteCommand.
Afterwards we return the new table in GridView.

AI answer about Adding a new Row

ADDING ROWS TO A DATASET

First, a database connection is opened using SqlConnection.

Then a SqlDataAdapter is created with a SELECT query, which retrieves data from the database.

A SqlCommandBuilder is created to automatically generate SQL INSERT, UPDATE, and DELETE

commands and assign them to the adapter.

Next, a DataSet object is created. When [Link]() is called, a DataTable named Personali is
created inside the DataSet and populated with the result of the SELECT query.

A new DataRow is then created using [Link](). At this point, all column values in
the row are NULL. Values are assigned to the row by referencing column names and reading
data from Windows Forms controls such as TextBox and DateTimePicker.

After assigning values, the row is added to the DataTable using [Link]().

At this stage, the change exists only in memory. To persist the changes to the database,
[Link]() is called. This method uses the commands generated by
SqlCommandBuilder to execute the appropriate INSERT statement.

Finally, the updated DataTable is bound to a GridView for display.