FINALS | Database Programming | Event-Driven Programming

ADO.NET

Its library is a rich framework that retrieves and updates information in various relational databases, which include Microsoft SQL Server, Microsoft Access, Oracle, and XML.

ADO.NET

It relies on the .NET Framework's various classes to process requests and transition between a database system and the user

Active Data Objects

ADO stands for what?

Data Provider

It is used for connecting in the database, recovering results, updating data, and executing commands.

Data Provider

Each database system that ADO.NET supports has a _____ ________ that implements mechanisms for connecting to a database, executing queries, and updating data.

System.Data.SqlClient

What is the namespace for; Data Provider for SQL Server

System.Data.OleDb

What is the namespace for; Data Provider for OLE DB

System.Data.Odbc

What is the namespace for; Data Provider for ODBC

System.Data.OracleClient

What is the namespace for; Data Provider for Oracle

System.Data.EntityClient

What is the namespace for; EntityClient Provider

DataSet

It characterizes a local copy of related data tables.

DataSet

It is composed of one or more DataTable objects that are manipulated and updated as per the requirements of the users or client.

XML (Extensible Markup Language)

The data from this language can also work with DataSet.

DataTable

It manages the data for any entity. It contains zero or more rows of data, and each value shown in the ____ _______ is identified by the DataColumn. It also consists of primary key, foreign key, constraint, and relation information of different tables.

DataColumn

It includes a data type declaration based on the kind of data saved.

DataRelation

It is used to link two (2) DataTable classes inside the DataSet class.

DataColumnMapping

It is used to map a table name from the database to a DataTable within a DataSet.

DataView

It is used to customize the view of the table or desired view of the rows in a DataTable.

Data Provider, Dataset

ADO.NET has two (2) components that are commonly used: HINT: DP DS

Command

It can run SQL commands and perform a stored procedure—accessing and manipulating data. Data management statements and SQL queries are wrapped in the _________ object.

Connection

From the data source, it allows users to connect and disconnect. Communications with the external data sources happen through the ___________ object.

DataAdapters

It transfers DataSet's between the data source and the caller.

DataAdapters

It also acts as a connection between the data source and DataSet, where ____________ contain a connection and set of Command objects that help in fetching and updating data.

DataReaders

It is a read-only access to data using a server-side cursor.

DataReaders

The _______________ object is gained by invoking the ExecuteReader method of the Command object. It requires closing the connection when using the ___________ object.

Command, Connection, DataAdapters, DataReaders

Data Provider contains the following objects that define the function to be used in a database management system (DBMS): HINT: Com Con DA DR

SqlCommand, OleDbCommand, and ODBCCommand

Database Classes: Used as wrappers for SQL statements or stored procedure calls

SqlCommandBuilder, OleDbCommandBuilder, and ODBCCommandBuilder

Database Classes: Generate SQL commands for updating database tables Automatically generate SQL commands from a SELECT statement

SqlConnection, OleDbConnection, and ODBCConnection

Database Classes: Used to connect to the database

SqlDataAdapter, OleDbDataAdapter, and ODBCDataAdapter

Database Classes: Handle interaction between DataSet and data source Help in executing different SQL commands to populate a DataSet and update the data source

SqlDataReader, OleDbDataReader, and ODBCDataReader

Database Classes: Used as a forward-only, read-only access to data using a cursor

SqlParameter, OleDbParameter, and ODBCParameter

Database Classes: Define a parameter to a stored procedure

SqlTransaction, OleDbTransaction, and ODBCTransaction

Database Classes: Represent transactions to be made in data source

System.Data

It includes all generic data access classes.

System.Data.Common

It includes classes that are shared or overridden by individual data providers.

System.Data.EntityClient

It includes Entity Framework classes.

System.Data.Linq.SqlClient

It includes LINQ to SQL provider classes.

System.Data.Odbc

It includes ODBC provider classes.

System.Data.OleDb

It includes OLE DB provider classes.

System.Data.ProviderBase

It includes new base classes and connection factory classes.

System.Data.Sql

It includes new generic interfaces and classes for SQL Server data access.

System.Data.SqlClient

It includes SQL Server provider classes.

System.Data.SqlTypes

It includes SQL Server data types.

Server Explorer

It is a server management console that is used for logging in to servers, opening data connections, and accessing databases.

Data Connections

It contains all the connections to the server and local databases.

tables, view, stored procedures, and functions

The data connection node displays the list of _______, _____, _________ ______________, and __________. It can also be used to connect to a SQL Server Database.

SQL Server Object Explorer

It is used to design, connect to the available database, browse the schema, and create a query on its object.

Data Connections, SQL Server Object Explorer

To connect or create a database using Visual Studio for C#, use any of the two (2) windows below: HINT: DC SQLSOE

SQL Server Management Studio (SSMS)

To have an SQL Server with an integrated environment for managing any SQL infrastructure, use ____ __________ _____________ ______. It can deploy, monitor, and upgrade the data-tier components used by applications. It can also build queries and scripts.

.mdf

To create a local database, right-click the project file in the Solution Explorer, click Add New Item, and choose Service-Based Database. The file will be saved as ___ file

Master Database File or MDF

This file is known as the main database file which contains data and schema

.ldf

The ___ file under .mdf file contains logs for each transaction of the database. This allows SQL database to be recoverable in the case of data loss.

Log Database Files or LDF

This file contains logs for each transaction of the database.

Step 1

Steps to access data from the database: Create a connection to the specified server.

Step 2

Steps to access data from the database: Open a connection using the SqlConnection object.

Step 3

Steps to access data from the database: Create a command using the SqlCommand object.

Step 4

Steps to access data from the database: Use and create a DataReader or DataAdapter object.

Step 5

Steps to access data from the database: Call the Close() method to close the connection and release the resource

Connection

Step 1: Create a ____________ to the specified server.

SqlConnection

Step 2: Open a connection using the _____________ object.

SqlCommand

Step 3: Create a command using the _____________ object.

DataReader or DataAdapter

Step 4: Use and create a _____________ or ______________ object.

Close()

Step 5: Call the _______ method to close the connection and release the resources.

SqlConnection

This class is used to open a connection using the Open() method. The connection can be closed by calling the Close() method.

ConnectionString

a property that is used to read or assign the _________ ______ to be used by the SqlConnection class

DataSource

a read-only property that returns the name of the SQL Server instance

Database

a read-only property that returns the name of the database

State

a read-only property that returns the current state of the connection.

ConnectionString, DataSource, Database, State

SqlConnection Properties: HINT: CS DS D S

Server

This attribute is used to specify the computer name to which you want to connect.

SqlConnection conn = new SqlConnection("Server=computer_name;");

Basically, the computer name where the desired database is installed is specified in the following format:

SqlConnection conn = new SqlConnection("Server=(local);");

If the application is created on the same machine where the database is installed, the computer name can be specified as local:

SqlConnection conn = new SqlConnection("Data Source=(local);");

The attribute for specifying the computer name is Server, Data Source, Address, or Addr. For example, the preceding statement can also be written as shown below:

Database / Initial Catalog

The attribute is used to specify the database to access the specified server. The Database keyword can also be substituted for Initial Catalog.

Database / Initial Catalog

This attribute is optional. In other words, it can be ignored when you want to connect to a specific computer but not to any database.

SqlConnection conn = new SqlConnection("Server=(local); Initial Catalog=CSharpSampleDatabase;");

For example, the following statement establishes a connection with CSharpSampleDatabase on the local computer:

Trusted_Connection / Integrated Security

This attribute implements the secure connection with the database. The values true, false, yes, no, or SSPI can be specified for this attribute. After setting the security attribute to false or no, provide the login credentials.

Security Support Provider Interface

SSPI stands for what?

SqlConnection conn = new SqlConnection("Server=(local); Initial Catalog=CSharpSampleDatabase; Integrated Security=no");

While establishing a trusted connection or the connection that doesn't need to be verified, a value of true or SSPI (Security Support Provider Interface) can be assigned for this attribute as shown below:

Username

To specify the username used in a connection after assigning false or no to the Integrated Security attribute, use the User ID attribute and assign a valid username to it.

SqlConnection conn =new SqlConnection("Server=(local); Initial Catalog=CSharpSampleDatabase; Integrated Security=no; User ID = David");

To specify the username used in a connection after assigning false or no to the Integrated Security attribute, use the User ID attribute and assign a valid username to it. Here is an example:

Password/Pwd

When establishing a secure connection to a database, in addition to a valid username, a password is needed.

SqlConnection conn = new SqlConnection("Server=(local); Initial Catalog=CSharpSampleDatabase; Integrated Security=no; User ID=David; PWD=gold2019");

Password or PWD (not case sensitive) can be used, as shown in the following example:

Server, Database/Initial Catalog, Trusted_Connection/Integrated Security, User, Password

SqlConnection Attributes: HINT: S D/IC T_C/IS U P

SqlCommand

This class is used to perform desired processing on the database. It allows executing any direct T-SQL or stored procedure on the server.

ExecuteNonQuery

This method executes the command but does not return output.

ExecuteReader

This method executes the command and returns a DataReader object based on the SQL statement.

SqlDataReader

This object is forward-only and read-only cursor that can be iterated through to fetch the rows in it.

ExecuteRow

It executes the command and returns the SqlRecord object that contains a single returned row.

ExecuteScalar

It executes the command and returns the first column of the first row in the result set.

ExecureNonQuery, ExecuteReader, SQL Reader ExecuteRow, ExecuteScalar

Methods of the Command object: HINT: ENQ ER SQLDR ER ES

Connection

It gets or sets the SqlConnection class that can be used by the Command object.

CommandText

It gets or sets the stored procedure or the T-SQL statement.

CommandType

It indicates the way the CommandText property should be interpreted.

SqlParameterCollection

It refers to a collection of parameters that is connected with SqlCommand.

Add()

This method adds the specified SqlParameter object to the SqlParameterCollection and contains two (2) arguments: string paramaterName and SqlDbType.

string paramaterName and SqlDbType

The Add() method contains two (2) arguments:

SqlDbType

It specifies the SQL Server data type of a field and property.

public SqlParameter Add(string parameterName, SqlDbtype sqlDbType);

Add() method Syntax:

sqlCommand.Parameters.Add("@FirstName", SqlDbType);

Add() method Sample Code: