DATABASE PROGRAMMING IN C#
CONTENTS
1. Introduction to ADO.NET
2. Database Namespace and Classes
3. Connecting to a Database
4. Architecture of ADO.NET
5. Managing database connection
6. Execute a SQL Command or Stored Procedure
INTRODUCTION TO ADO.NET
C# and .Net can work with a majority of databases, the most common being Microsoft SQL
Server and Oracle. But with every database, the logic behind working with all of them is
mostly the same. in the Microsoft .NET Framework, access to a wide variety of data sources
is enabled through a group of classes collectively named Microsoft ADO.NET.
ADO.NET (ActiveX Data Objects) is a data access technology from Microsoft .Net
Framework, which provides communication between relational and non-relational systems
through a common set of components. ADO.NET consist of a set of Objects that expose data
access services to the .NET environment. ADO.NET is designed to be easy to use, and Visual
Studio provides several wizards and other features that you can use to generate ADO.NET
data access code.
Database Access
Three steps:
1. Open connection to database
2. Execute SQL to retrieve records / update DB
3. Close connection
2.DATABASE NAMESPACE AND CLASSES
The namespaces in the following table expose the classes and interfaces used in .NET data
access
System.Data.SqlClient
This namespace of .NET Framework contains all of the classes required to connect to the
databases, read/write data to the databases.
SQL Connection class properties
3. Connecting to a Database
Connection to a database requires a connection string.
This string has the information about the server you're going to connect, the database you will
require and the credentials that you can use to connect. Each database has its own properties,
its own server, name and type of login information, using which you can connect to the
database to read/write the data from it.The connection string may include attributes such as
the name of the driver, server and database, as well as security information such as user name
and password
You configure a connection object using a connection string. A connection string is a set of
semicolon-separated name/value pairs. You can supply a connection string either as a
constructor argument or by setting a connection object’s Connection String property before
opening the connection. Each connection class implementation requires that you provide
different information in the connection string.
Connected Environment (Scenario)
1. Open connection
2. Execute command
3. Process rows in reader
4. Close reader
5. Close connection
Working with data directly via open connection
Advantages
Simple security realization
Work with real data
Simple organization of distributed work
Disadvantages
Continual connection
Not available via Internet
Disconnected Environment (Scenario)
1. Open connection
2. Fill the DataSet
3. Close connection
4. Process the DataSet
5. Open connection
6. Update the data source
7. Close connection
Advantages
▪Economy of server resources
▪Does not require continual connection
Disadvantages
▪Demands conflict resolution while data update
▪Data is not always up to date
4.ARCHITECTURE OF ADO.NET
The two key components of ADO.NET are Data Providers and Dataset. .Net Framework
includes mainly three Data Providers for ADO.NET. They are the Microsoft SQL Server Data
Provider , OLEDB Data Provider and ODBC Data Provider + Oracle .NET Data Provider
SQL Server uses the SqlConnection object ,
OLEDB (Object Linking and Embedding Database) uses the OleDbConnection
Object and
ODBC (Open Database Connectivity) uses OdbcConnection Object respectively.
DataSet provides a disconnected representation of result sets from the Data Source,
and it is completely independent from the Data Source. Dataset provides much greater
flexibility when dealing with related Result Sets.
DataSet consists of
1.DataTable : a collection of rows and columns that represents structured data in a tabular
format.
2. DataRelation: defines a relationship between two DataTables within a Dataset
DATA PROVIDER COMPONENTS
Each .NET data provider consists of four main components
Connection – used to connect to the data source
Command – used to execute a command against the data source and retrieve a
DataReader or Dataset, or to execute an INSERT, UPDATE, or DELETE command
against the data source
DataReader – a forward-only, read-only connected resultset
DataAdapter – used to populate a Dataset with data from the data source, and to
update the data source
5. MANAGING DATABASE CONNECTION (CREATING AND CLOSING)
Creating a connection: Creating connection object using constructor and arguments
Example: (using integrated Windows security)
ConnectionString Parameters
Provider
Data Source
Initial Catalog
Integrated Security
UserID/Password
Closing connection: When you’re finished with a connection, you should always call its
Close method to free the underlying database connection and system resources.
Eg: conn.Close()
Alternatively, using statement makes very clean and efficient way of using connection
objects in your code. When using statement is used, the method Close() of connection
object is automatically called to close the opened connection and to release any resource.
EXAMPLE
6. EXECUTE A SQL COMMAND OR STORED PROCEDURE
SqlCommand object: A SqlCommand object allows you to specify what type of
interaction you want to perform with a database. For example, you can do select, insert,
modify, and delete commands on rows of data in a database table.
Properties
Connection
CommandType
CommandText
Parameters
Methods
ExecuteNonQuery
ExecuteReader
ExecuteScalar
SqlCommand. CommandText Property
Gets or sets the SQL statement, table name or stored procedure to execute at the data
source.
Eg: SqlCommand command = new SqlCommand (); command. CommandText =
"SELECT * FROM Categories ORDER BY CategoryID";
SqlCommand. CommandType Property
Gets or sets a value indicating how the CommandText property is to be interpreted.
Possible values are:
CommandType. Text
CommandType.StoredProcedure
SqlCommand.CommandTimeout Property
The time in seconds to wait for the command to execute. The default is 30 seconds. Gets or
sets the wait time before terminating the attempt to execute a command and generating an
error.
Creating Sqlcommand Object
Constructors
1. SqlCommand ()
2. SqlCommand(cmdText)
3. SqlCommand (cmdText, connection)
Example
SqlCommand command = new SqlCommand ();
command. CommandText = "SELECT * FROM Categories";
command.CommandTimeout = 15;
command. CommandType = CommandType. Text;
When the CommandType property is set to StoredProcedure, the
CommandTextproperty should be set to the name of the stored procedure.
HOW TO ADD PARAMETERS TO COMMANDS?
Use Parameters property of Command Object
command.Parameters.Add("@i", SqlDbType.Int);
command.Parameters["@i"].Value = idno;
command.Parameters.Add("@f", SqlDbType.Text);
command.Parameters["@f"].Value =fn; OR
command.Parameters.AddWithValue(“i", idno);
command.Parameters.AddWithValue("f", fn);
SqlCommand cmd = new SqlCommand( "select * from Customers where city =
@City", conn);
//define parameters used in command object
SqlParameter param = new SqlParameter(); param.ParameterName = "@City";
param.Value = inputCity;
// add new parameter to command object cmd.Parameters.Add(param); Or
command.Parameters.Add(new SqlParameter("@i", idno));
//command.Parameters.Add(new SqlParameter("@f", fn));
EXECUTING COMMANDS
The SqlCommand class provides the following methods for executing commands
against the SQL Server database:
1.ExecuteReader(): Executes the command and returns a typed IDataReader
Executes commands that return rows.
Sends the CommandText to the Connection and builds a SqlDataReader
ExecuteReader() - Returns DataReader
Example
SqlCommand command = new SqlCommand(queryString, connection);
SqlDataReader reader = command.ExecuteReader();
Retrieving Records with the SqlDataReader Class
▪DataReader object: used to retrieve read-only, forward-only data from a data source
▪Forward-only: the program can only move forward sequentially through the records in
the returned data from the first to the last
▪Use a DataReader object when you want to read data but not add, delete, or modify
records
▪SqlDataReader class: used to retrieve data from SQL Server
ExecuteReader() method of the SqlCommand class: creates a SqlDataReader
object. Must assign the SqlDataReader object to a variable.
Read() method of the SqlDataReader class: advances the SqlDataReader object
to the next record
Cursor: your position within the recordset
▪Initially placed before the first row in the recordset
▪First use of the Read() method places the cursor in the first row of the recordset
Read() Method
o Use the Read() method to determine if a next record is available.
o Returns true if there is another row in the recordset.
Field Names as Variables
o Field names in a database table are assigned as variables in a SqlDataReader
object collection.
o The content of each variable changes when the cursor position moves to a new
row.
Closing the SqlDataReader
o Use the Close() method of the SqlDataReader class to close it when you are
finished working with it.
Exclusive Access
o SqlDataReader has exclusive access to the connection object.
o You cannot access any other commands until the SqlDataReader object is
closed.
EXAMPLE
2.ExecuteNonQuery(): Executes the command but does not return any output
DISPLAY SELECT QUERY RESULT USING DATADGRIDVIEW CONTROL
Executing action queries
▪Use action queries when you need to modify a DB
▪Updates
▪Inserts
▪Deletes
ExecuteNonQuery :
To execute operations where database is changed
▪Example: insert, update, delete, create database, create table, etc.
▪Insert, update, delete: Returns number of rows affected by the operation and returns -1
for others
▪Example SqlCommand cmd = new SqlCommand ("insert into titles (title_id, title, type,
pubdate)"+ "values ('CS150','C++ Programming'," + " 'computer science', 'May
2006'), conn); cmd.ExecuteNonQuery();
Insert Query
Delete Query
Update Query
3.ExecuteScalar(): Executes the command and returns the value from the first column
of the first row of any result set
Example
Displaying column names of returned record set
Using FieldCount property and GetName() method
