Arba Minch University
Sawla Campus
Department of BAIS
Windows Programming (BAIS 3033)
Target Group: BAIS 4th Year 1 st Semester
Chapter 5: Database Programming C#
January, 2025
Database Connectivity
• Database connection is a facility that allows client software to communicate
with database server software, whether on the same machine or not.
• Connection is required to send commands and receive answers.
• Database driver is a piece of software for accessing a database.
• There are various DB and language specific database drivers.
Different ways to connect to a db
• For C# there are alternative ways to connect to a database (different data
access technologies):
• ODBC (Open DB Connectivity) – older open standard, was designed for
Basic, C, languages
• OLE -
• [Link] - .NET data access technology
[Link]
• API designed for Visual Basic .NET and C#, providing database access
facilities similar to JDBC/ODBC
• [Link] provides an architecture for communicating between an application
and a data source.
• “data source” can be anything that has the required API, but usually it is a DB
server
• Architecture:
Data Connection Data Dataset
Object Application
Source Adaptor (Local)
([Link])
[Link] (contd…)
• Partial example of [Link] code in C#
using System, [Link], [Link];
SqlConnection conn = new SqlConnection(“Data Source=<IPaddr>, Initial
Catalog=<Catalog>”);
[Link]();
SqlCommand cmd = new SqlCommand(“select * from students”, conn);
SqlDataReader rdr = [Link]();
while([Link]())
{
[Link](rdr[0], rdr[1]); /* Prints result attributes 1 & 2 */
}
[Link](); [Link]();
Adding Data Source in Visual Studio
• Visual Studio provides tools to connect applications to data
from many different sources, such as databases, Web services,
and objects.
• In Visual Studio connection objects can be created as a result
of completing one of the data wizards or of dragging data
objects onto your form.
• To connect an application to data in a database, Web service,
or object, use the Data Source Configuration Wizard.
• From the main menu choose, Add New Data Source from the
Data Sources Window.
Connect SQL Server Databases
• To connect an Application from Visual Studio 2010 to SQL Server by
using wizard:
• Click on Tools menu from menu
• Click on "Connect to Database" it will open "Choose Data Source" dialog
• Select your SQL server as "Microsoft SQL Server" out of all installed Data
Source on your System
• Press Continue button, after that it will give you option to put your Server
name, user ID and password. You should not need a password on your own
machine.
[Link] Namespace
Namespaces Description
Contains the definition for columns, relations, tables, database,
[Link]
rows, views and constraints.
Final Contains the classes that used to connect to a Microsoft SQL Server
[Link] database such as SqlCommand, SqlConnection, SqlDataAdapter.
Contains classes required to connect to most ODBC drivers. These
[Link]
classes include OdbcCommand,OdbcConnection.
Contains classes such as OracleConnection,OracleCommand
[Link]
required to connect to an Oracle database.
[Link]
• ADO- Stands for ActiveX Data Object.
• Final It is a data access technology that is primarily disconnected and
designed to provide efficient and scalable data access.
• [Link] implements a disconnected data access model.
• In this model data connections are established and left open only long
enough to perform the requested action.
• Example: if an application requests data from a db, the connection opens just
long enough to load the data into the application and then it closes.
[Link] Data Architecture
–Data access in [Link] relies on two entities.
[Link] Data Architecture
1. Data Set
–Stores the data from the database on the local machine.
–Represents a subset of the database
–It is a disconnected, in memory representation of data.
–to update the database a reconnection is required
–Data can be loaded to a data set from any valid data source such as
SQL Server db, MS-Access db or xml.
– Dataset object contains:
» DataTable objects and DataRelation objects
o DataTable is defined by dataColumns, dataRows and Constraints,
o DataRelations: allows you to create associations between rows in one
table and rows in another table.
– The following table shows some important properties of the Dataset class:
[Link] Data Architecture (Cont’d)
Properties Description
CaseSensitive Indicates whether string comparisons within the data tables are
case-sensitive.
Container Gets the container for the component.
DataSetName Gets or sets the name of the current data set.
DefaultViewManager Returns a view of data in the data set.
DesignMode Indicates whether the component is currently in design mode.
EnforceConstraints Indicates whether constraint rules are followed when
attempting any update operation.
Events Gets the list of event handlers that are attached to this
component.
[Link] Data Architecture (Cont’d)
Properties Description
ExtendedProperties Gets the collection of customized user information associated
with the DataSet.
HasErrors Indicates if there are any errors.
IsInitialized Indicates whether the DataSet is initialized.
Locale Gets or sets the locale information used to compare strings
within the table.
Namespace Gets or sets the namespace of the DataSet.
Prefix Gets or sets an XML prefix that aliases the namespace of the
DataSet.
Relations Returns the collection of DataRelation objects.
Tables Returns the collection of DataTable objects.
[Link] Data Architecture (Cont’d)
Methods Description
AcceptChanges Accepts all changes made since the DataSet was loaded or this method was called.
BeginInit Begins the initialization of the DataSet. The initialization occurs at run time.
Clear Clears data.
Clone Copies the structure of the DataSet, including all DataTable schemas, relations, and
constraints. Does not copy any data.
Copy Copies both structure and data.
CreateDataReader() Returns a DataTableReader with one result set per DataTable, in the same sequence as the
tables appear in the Tables collection.
CreateDataReader(DataTa Returns a DataTableReader with one result set per DataTable.
ble[])
EndInit Ends the initialization of the data set.
Equals(Object) Determines whether the specified Object is equal to the current Object.
Finalize Free resources and perform other cleanups.
[Link] Data Architecture (Cont’d)
Methods Description
GetChanges Returns a copy of the DataSet with all changes made since it was loaded or the AcceptChanges
method was called.
GetChanges(DataRowState) Gets a copy of DataSet with all changes made since it was loaded or the AcceptChanges method
was called, filtered by DataRowState.
GetDataSetSchema Gets a copy of XmlSchemaSet for the DataSet.
GetObjectData Populates a serialization information object with the data needed to serialize the DataSet.
GetType Gets the type of the current instance.
GetXML Returns the XML representation of the data.
GetXMLSchema Returns the XSD schema for the XML representation of the data.
HasChanges() Gets a value indicating whether the DataSet has changes, including new, deleted, or modified
rows.
HasChanges(DataRowState) Gets a value indicating whether the DataSet has changes, including new, deleted, or modified
rows, filtered by DataRowState.
IsBinarySerialized Inspects the format of the serialized representation of the DataSet.
[Link] Data Architecture (Cont’d)
Methods Description
Load(IDataReader, Fills a DataSet with values from a data source using the supplied IDataReader, using an array of
LoadOption, DataTable[]) DataTable instances to supply the schema and namespace information.
Load(IDataReader, Fills a DataSet with values from a data source using the supplied IDataReader, using an array of
LoadOption, String[]) strings to supply the names for the tables within the DataSet.
Merge() Merges the data with data from another DataSet. This method has different overloaded forms.
ReadXML() Reads an XML schema and data into the DataSet. This method has different overloaded forms.
ReadXMLSchema(0) Reads an XML schema into the DataSet. This method has different overloaded forms.
RejectChanges Rolls back all changes made since the last call to AcceptChanges.
WriteXML() Writes an XML schema and data from the DataSet. This method has different overloaded forms.
WriteXMLSchema() Writes the structure of the DataSet as an XML schema. This method has different overloaded
forms.
[Link] Data Architecture (Cont’d)
2. Data Provider
– Is a set of components that mediates interaction between the program and the
database.
– It is used to create and maintain link to a database.
– Data Provider is not a single component, rather it is a set of related component
that work together to provide data in an efficient performance driven manner.
– Data Provider consists of the following generic components:
❑Connection Object
– Represents actual connection to a database;
– It contains all the information required to open a channel to the db in the connection
string property.
– Connection classes include:
» SqlConnection
» OleDbConnection Class
» OdbcConnection Class
[Link] Data Architecture (Cont’d)
❑Command Object
–Used to execute commands to a db across a data connection;
–It is represented by two corresponding classes; SqlCommand and
OleDBCommand
–It provides three methods that are used to execute commands on
the db:
» ExecuteNonQuery: execute commands that return no records, such as
INSERT, UPDATE & DELETE.
» ExecuteScalar: returns a single value from a database query.
» ExecuteReader: returns a result set by way of data reader object.
❑DataReader Object
–Provides a forward-only, read-only connected stream record set
from a database.
[Link] Data Architecture (Cont’d)
❑DataAdapter Object
– It is the middleman, facilitating all communication between database and
dataset
– It fills a data table or dataset with data from the db whenever the fill
command is called and can transmit changes to the db by calling the
update method.
– It provides four properties:
» SelectCommand: contains the command text that selects data from a db.
» InsertCommand: contains the command text that inserts a row into a table.
» DeleteCommand: contains the command text that deletes a row from a table.
» UpdateCommand: contains the command text that updates the value of a db
Database Connectivity
• Following are important to get connected to the database and perform
operations
• Connection Object
• Command Object
• Operation on the Command Object
• Using Dataset and Data adapter
• Using Data Reader
• If we use data adapter, it is called as disconnected architecture.
• If we use data reader, it is called as connected architecture.
Connection Object
SqlConnection conn = new SqlConnection(conString);
• Where conString is a string that contains details about the server
where the database is located, name of the database, user id and
password required for getting connected and the driver details.
• To open a connection:
[Link]()
• To Close a connection:
[Link]()
Command Object
SqlCommand comm = new SqlCommand(query,Conn);
• query – is the select/insert/update/delete statement or exec
<<storedProc>> command
• Conn is the connection object created in the first step
• Properties – [Link]
• This can be either Text or StoredProcedure
Command Methods
• [Link]() – Returns one or more table row(s) – for select
statement
• [Link]() – Returns a single value – for select statement
with aggregate function
• [Link]() – Used for executing stored procedure or
insert/update/delete statements
Data Reader and Data Set
• Data Reader
DataTableReader reader = [Link]()
• dt holds the result set (data table) present in datareader object
• Data Set
DataSet ds = new DataSet();
• ds holds the result of select statement
• Data adapter is used to fill the data set
DataSet vs. DataReader
DataSet: DataReader: (standard)
• Like 2D array: a[r][c] • 1 row at a time
• Full dataset read from DB at query execution time • No local memory storage
• Dataset cached locally in memory • Fetch each row from DB on demand
• “cursor” = current row
• Can disconnect from DB
• Must stay connected to DB
• Implemented using DataReader (Adapter) • + scalability
• + random data access • - each row slow
• - Limited scalability • - random access difficult
• - initial read very slow
Data Adapter
• Data adapter fills in the data set with the result of the select query
SqlDataAdapter sda = new SqlDataAdapter();
[Link] = comm;
• Comm is the command object created
[Link](dataset) //fills the dataset
• data set can be set as a data source for various controls in the web
form or windows form
LINQ Architecture
– LINQ - Language Integrated Query: is an addition to the .NET framework 3.5.
– Developers across the world have always encountered problems in querying
data because of the lack of a defined path and need to master a multiple of
technologies
– It provides a simple and powerful capabilities when you work with all kinds
of data.
– It used to query Microsoft SQL Server databases
– It introduces queries as part of the programming language.
– It uses unified syntax regardless of the type of data.
– It enables you to query data from: SQL Serve db, XML, in-memory arrays &
collections, [Link] datasets and any other remote or local data sources
that support LINQ.
LINQ Architecture
LINQ Providers
–A LINQ provider maps your LINQ queries to the data source being
provided.
–It translates the queries into commands that the data source will be
able to execute.
–It also converts data from the source to the objects that makeup your
query result.
–LINQ providers include:
» LINQ to Objects
» LINQ to SQL
» LINQ to XML
» LINQ to DataSet
Structure of LINQ Queries
–Is referred to as a query expression.
–Consists of a combination of query clauses that identify the data
sources and iteration variables fro the query.
–It can also include instructions for sorting, filtering, grouping, and
joining or calculation to apply to the source data.
–Example:
• var queryAllCustomers = from cust in customers
• select cust;
Where: - cust is a range variable
- customer is the table name
Query a DB using LINQ
– Create a connection to a db.
– Add a project that contains a LINQ to SQL File.
» Add a window form application.
» On Project menu click Add New Item.
» Select LINQ to SQL classes
» Name the file, click Add.
– Add tables to query to the Object Relational Designer.
» Expand the connection to the db on the server explorer.
» Expand the tables folder.
» Click and drag the table to O R Designer
» Save your changes and close the designer. Save your project.
– Add code to query the db and display result.
» Add data grid view control to your form.
» Double click your form to add code to the load event.
» Add codes to the load event to query the tables.
Advantages of LINQ
– LINQ offers a host of advantages and among them the foremost is its powerful
expressiveness which enables developers to express declaratively. Some of the other
advantages of LINQ are given below.
» LINQ offers syntax highlighting that proves helpful to find out mistakes during design time.
» LINQ offers IntelliSense which means writing more accurate queries easily.
» Writing codes is quite faster in LINQ and thus development time also gets reduced
significantly.
» LINQ makes easy debugging due to its integration in the C# language.
» Viewing relationship between two tables is easy with LINQ due to its hierarchical feature and
this enables composing queries joining multiple tables in less time.
» LINQ allows usage of a single LINQ syntax while querying many diverse data sources and this
is mainly because of its unitive foundation.
» LINQ is extensible that means it is possible to use knowledge of LINQ to querying new data
source types.
» LINQ offers the facility of joining several data sources in a single query as well as breaking
complex problems into a set of short queries easy to debug.
» LINQ offers easy transformation for conversion of one data type to another like transforming
SQL data to XML data.
F i v e !
o f C hapter
The End
Question ?
Suggestion ?
Comment ?