A Database is an organized collection of information, structured to store data
efficiently.
Data in a database is divided into tables, which are further organized into rows
(records) and columns (fields).
Each column stores a specific type of data, e.g., Name, Age, Salary.
You access a database using SQL (Structured Query Language), which is a
standardized query language supported by SQL Server, Oracle, MySQL, Access,
etc.
2. Introduction to [Link]
[Link] is a framework in the .NET environment for data access.
It allows you to connect to databases, retrieve, manipulate, and update data.
[Link] uses a multilayer architecture, divided into:
1. Connection objects – to establish connections with databases.
2. Command objects – to execute SQL statements.
3. DataReader objects – to read data in a forward-only, read-only mode.
4. DataAdapter objects – to bridge disconnected DataTables with databases.
5. DataSet objects – in-memory representation of data.
3. [Link] Architecture
Data Providers
Data providers are specialized classes that allow .NET applications to interact with
different databases.
Examples:
o SQL Server Provider – [Link]
o OLE DB Provider – [Link]
o Oracle Provider – [Link]
Key point: Data providers are extensible; you can create custom providers for other
databases.
Two types of objects in [Link]
1. Connection-based classes – directly interact with the database.
o Connection, Command, DataReader, DataAdapter.
2. Content-based classes – independent of database type.
o DataSet, DataTable, DataColumn, DataRow, DataRelation.
4. Namespaces
Namespace Description
Provides core classes like DataSet, DataColumn, DataRow,
[Link]
DataRelation.
Classes for SQL Server connectivity: SqlConnection, SqlCommand,
[Link]
SqlDataAdapter.
Classes for ODBC database connections: OdbcConnection,
[Link]
OdbcCommand.
Classes for Oracle connectivity: OracleConnection,
[Link]
OracleCommand.
5. Connection Class
Used to establish a connection to a database.
Requires a connection string:
o Data Source → location of the database server (localhost or IP).
o Initial Catalog → database name.
o Authentication:
Integrated security → use Windows login.
SQL login → specify username and password.
Example:
string conString = "Data Source=localhost;Initial Catalog=Master;Integrated
Security=SSPI";
SqlConnection Conn = new SqlConnection(conString);
If using username/password:
string conString = "Data Source=localhost;Database=Master;user
id=sa;password=sa";
SqlConnection Conn = new SqlConnection(conString);
Tips:
Always release connections with Close() or using block to prevent resource leaks.
SqlConnectionStringBuilder can programmatically build connection strings.
6. Command and DataReader Classes
Command Class
Executes SQL queries (SELECT, INSERT, UPDATE, DELETE).
Can execute:
o Inline SQL statements
o Stored Procedures
Properties to set:
o CommandType → Text or StoredProcedure
o CommandText → SQL query
o Connection → SqlConnection object
Example:
SqlCommand sc = new SqlCommand("SELECT * FROM Customer", Conn);
DataReader Class
Reads data returned by a SELECT command.
Forward-only and read-only cursor.
Requires a live connection.
Use ExecuteReader() to get the DataReader.
Always close the connection after reading.
Example:
SqlDataReader sdr = [Link]();
while([Link]()) {
[Link](sdr["FirstName"] + " " + sdr["LastName"]);
}
[Link]();
Tip: You can also use [Link] to automatically close
the connection when the DataReader is closed.
7. Execute Methods
1. ExecuteReader()
o Returns a DataReader for forward-only, read-only access.
2. ExecuteScalar()
o Returns first column of the first row.
o Often used for counts or sums.
3. int count = (int)[Link]();
4. ExecuteNonQuery()
o Executes commands that don’t return a result set, e.g., INSERT, UPDATE,
DELETE.
8. DataAdapter and DataTable
DataAdapter bridges disconnected DataTable objects with a database.
[Link](DataTable dt) → fills a DataTable with query results.
Can also execute InsertCommand, UpdateCommand, DeleteCommand.
Example:
SqlDataAdapter sda = new SqlDataAdapter(sc);
DataTable dt = new DataTable();
[Link](dt);
[Link] = [Link];
Properties of SqlDataAdapter:
o SelectCommand
o InsertCommand
o UpdateCommand
o DeleteCommand
9. Stored Procedures
A Stored Procedure is a precompiled SQL batch stored in the database.
Accepts input parameters and can return result sets or output parameters.
Advantages:
o Reusability – can be called multiple times.
o Performance – precompiled execution.
o Security – can limit direct table access.
Example SQL to create a stored procedure:
CREATE PROCEDURE GetCustomerByID
@CustomerID INT
AS
BEGIN
SELECT FirstName, LastName
FROM Customer
WHERE CustomerID = @CustomerID
END
Calling from C#:
SqlCommand cmd = new SqlCommand("GetCustomerByID", Conn);
[Link] = [Link];
[Link]("@CustomerID", 1);
SqlDataReader dr = [Link]();
10. Key Terms Explained
Term Explanation
Connection String Series of key/value pairs defining database access.
SqlConnection Class used to establish a connection with SQL Server.
SqlCommand Class used to execute SQL commands (SELECT, INSERT...).
SqlDataReader Forward-only, read-only reader for efficient data access.
Term Explanation
ExecuteScalar Returns a single value from a query (first column of first row).
ExecuteNonQuery Executes commands like INSERT, UPDATE, DELETE without returning data.
DataAdapter Bridges DataTable/DataSet with the database, allows disconnected operations.
DataTable In-memory table representation of data.
Stored Procedure Precompiled SQL batch stored in the database.