Download ASP.NET is a free web framework for building great websites and web applications using HT and more Lecture notes Computer Science in PDF only on Docsity!
Unit III
Architecture of ADO.Net – Connected and Disconnected Database – Create Database – Create
Connection using ADO.Net Object Model – Connection Class – Command Class – Data
Adapter Class – Dataset Class – Display Data on Data Bound Controls and Data Grid
Architecture of ADO.Net
ADO.NET is a set of classes that comes with the Microsoft .NET framework to facilitate
data access from managed languages. ADO.NET has been in existence for a long time and it
provides a comprehensive and complete set of libraries for data access. The strength of
ADO.NET is firstly that it lets applications access various types of data using the same
methodology. If you know how to use ADO.NET to access a SQL Server database then the same
methodology can be used to access any other type of database (like Oracle or MS Access) by just
using a different set of classes. Secondly, ADO.NET provides two models for data access: a
connected model where you can keep the connection with the database and perform data access,
and another way is to get all the data in ADO.NET objects that let us perform data access on
disconnected objects.
ADO.NET Components
The two main components of ADO.NET for accessing and manipulating data are the
.NET Framework data providers and the DataSet.
.NET Framework Data Providers
The .NET Framework Data Providers are components that have been explicitly designed
for data manipulation and fast, forward-only, read-only access to data. The Connection object
provides connectivity to a data source. The Command object enables access to database
commands to return data, modify data, run stored procedures, and send or retrieve parameter
information. The DataReader provides a high-performance stream of data from the data source.
Finally, the DataAdapter provides the bridge between the DataSet object and the data source.
The DataAdapter uses Command objects to execute SQL commands at the data source to both
load the DataSet with data and reconcile changes that were made to the data in the DataSet back
to the data source.
Data Provider contains the following:
Connection – To establish connectivity with database. An object in ADO.NET that allows you
to open and execute commands against a database.
Command Object – to access stored procedures of the database A Command object is used to
call a stored procedure or execute a dynamic SQL statements (insert, update, delete, and select).
The Command’s ExecuteNonQuery method is used to execute non result-returning queries
such as an INSERT or UPDATE command.
Data Adapter – To pass data fetched from the database to the dataset for caching and making
the data available even when the connection gets disconnected. A DataAdapte r can be used with a
DataSet to add, update, or delete records in a database.
Datareader – To read data from database ExecuteReader method Executes the CommandText
against the Connection and returns a DbDataReader. A DBDataReader object is a read-only,
forward-only cursor connected to the database.
Remote data between tiers or from an XML Web service.
Interact with data dynamically such as binding to a Windows Forms control or combining and relating data from multiple sources.
Perform extensive processing on data without requiring an open connection to the data source, which frees the connection to be used by other clients.
If you do not require the functionality provided by the DataSet, you can improve the
performance of your application by using the DataReader to return your data in a forward-only,
read-only manner. Although the DataAdapter uses the DataReader to fill the contents of a
DataSet (see Populating a DataSet from a DataAdapter), by using the DataReader, you can boost
performance because you will save memory that would be consumed by the DataSet, and avoid
the processing that is required to create and fill the contents of the DataSet.
In a typical scenario requiring data access, we need to perform four major tasks:
- Connecting to the database
- Passing the request to the database, i.e., a command like select, insert, or update.
- Getting back the results, i.e., rows and/or the number of rows effected.
- Storing the result and displaying it to the user.
CONNECTED AND DISCONNECTED DATABASE
ADO.NET (Active x Data Object) is a mediator between the front end and back end that interacts with the client-side application and server-side application which supports two types of Data Accessing models, one is Connection-oriented and Disconnected oriented.
ADO.NET leverages the power of XML for accessing data using disconnected architecture. It was completely designed with XML classes in .NET Framework
The connected environment provides forward-only, read-only access to data in the data
source and the ability to execute commands against the data source. The connected classes
provide a common way to work with connected data regardless of the underlying data source. It
includes Connection, Command, DataReader, Transaction, Parameter Collection, and
Parameter classes.
The disconnected environment allows data retrieved from the data source to be manipulated
and later reconciled with the data source. The disconnected classes provide a common way to
work with disconnected data regardless of the underlying data source. They include
the DataSet, DataTable, DataColumn, DataRow, Constraint, DataRelationship,
and DataView classes.
Finally, ADO.NET introduces the connected DataAdapter class to bridge the data source and
disconnected classes by way of the connected classes. The DataAdapter is an abstraction of the
connected classes that simplifies filling the disconnected DataSet or DataTable classes with data
from the data source and updating the data source to reflect any changes made to the
disconnected data.
Connection String
Connection string plays a very crucial role in connecting your front-end application and a
back-end application. It’s a normal string which contains the information to establish the
connection between the backend Database and application and secured information such as
user Id and password. Usually, Data Providers uses this connection string which contains
parameters that are needed for establishing the connection.
Parameter
Allows parameters for both parameterized queries and stored procedures to be defined and set to appropriate values. The Parameter class is accessed through the Parameters Collection object within a Command object. It supports input and output parameters as well as return values from stored procedures.
Transaction
Allows transactions to be created on a connection so that multiple changes to data in a data source are treated as a single unit of work and either all committed or cancelled.
DataAdapter
Bridges the data source and the disconnected DataSet or DataTable classes. The DataAdapter wraps the connected classes to provide this functionality. It provides a method to retrieve data into a disconnected object and a method to reconcile modified data in the disconnected object with the data source. The CommandBuilder class can generate the logic to reconcile changes in simple situations; custom logic can be supplied to deal with complex situations and optimize performance.
Disconnected Classes
The following ADO.NET classes allow data to be retrieved from the data set, examined and modified offline, and reconciled with the data source through the DataAdapter:
DataSet
Provides a consistent way to deal with disconnected data completely independently of the data source. The DataSet is essentially an in-memory relational database, serving as a container for the DataTable, DataColumn, DataRow, Constraint, and DataRelation objects. The XML format serializes and transports a DataSet. A DataSet can be accessed and manipulated either as XML or through the methods and properties of the DataSet interchangeably; the XmlDataDocument class represents and synchronizes the relational data within a DataSet object with the XML Document Object Model (DOM).
DataTable
Allows disconnected data to be examined and modified through a collection of DataColumn and DataRow classes. The DataTable allows constraints such as foreign keys and unique constraints to be defined using the Constraint class.
DataColumn
Corresponds to a column in a table. The DataColumn class stores metadata about the structure of the column that, together with constraints, defines the schema of the table. The DataColumn can also create expression columns based on other columns in the table.
DataRow
Corresponds to a row in a table and can examine and update data in the DataTable. The DataTable exposes DataRow objects through the DataRowCollection object it contains. The DataRow caches changes made to data contained in its columns, storing both original and current values. This allows changes to be cancelled or to be later reconciled with the data source. Constraints to be placed on data stored within a DataTable. Unique and foreign key constraints can be created to maintain data integrity.
DataRelation
Provides a way to indicate a relationship between different DataTable objects within a DataSet. The DataRelation relates columns in the parent and child tables allowing navigation between the parent and child tables and referential integrity to be enforced through cascading updates and deletes.
DataView
Allows data, once retrieved into a DataSet or DataTable, to be viewed in different ways. It allows data to be sorted based on column values and for a subset of the data to be filtered so that only rows matching specified criteria are displayed.
CREATE DATABASE
Creating a new database using the CREATE DATABASE statement
The CREATE DATABASE statement creates a new database. The following shows the minimal syntax of the CREATE DATABASE statement:
CREATE DATABASE database_name;
In this syntax, you specify the name of the database after the CREATE DATABASE keyword.
The database name must be unique within an instance of SQL Server. It must also comply with the SQL Server identifier’s rules. Typically, the database name has a maximum of 128 characters.
The following statement creates a new database named TestDb:
Creating a new database using SQL Server Management Studio
First, right-click the Database and choose New Database… menu item.
Second, enter the name of the database e.g., SampleDb and click the OK button.
Third, view the newly created database from the Object Explorer:
TABLE STRUCTURE
Example
- SqlConnection con;
- con = new SqlConnection("Server=Krushna;Database=Anagha;Uid=sa;Pwd=sa");
When working with databases is to create a connection object. There are 2 ways to create an
instance of SqlConnection class as shown below.
The ConnectionString parameter is a string made up of Key/Value pairs that has the
information required to create a connection object.
To create a connection object with windows authentication
string ConnectionString = "data source=.; database=SampleDB; integrated security=SSPI";
To create a connection object with SQL Server authentication
string ConnectionString = "data source=.; database=SampleDB; user id=MyUserName;
password=MyPassword";
The "data source" is the name or IP Address of the SQL Server that we want to connect to.
If you are working with a local instance of sql server, you can just specify DOT(. ). If the server
is on a network, then use Name or IP address.
ADO.NET Connection
1. Creates a connection 2. The created connection object is then passed to the command object, so that the command object knows on which sql server connection to execute this command. 3. Execute the command, and set the command results, as the data source for the gridview control. 4. Call the DataBind() method 5. Close the connection in the finally block. Connections are limited and are very
valuable. Connections must be closed properly, for better performance and scalability.
protected void Page_Load(object sender, EventArgs e)
{
//Create the connection object SqlConnection connection = new SqlConnection("data source=.;
database=Sample_Test_DB; integrated security=SSPI");;
try { // Pass the connection to the command object, so the command object knows on
which
// connection to execute the command SqlCommand cmd = new SqlCommand("Select * from tblProductInventory",
connection);
// Open the connection. Otherwise you get a runtime error. An open connection is // required to execute the command connection.Open(); GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind(); } catch (Exception ex) { // Handle Exceptions, if any } finally { // The finally block is guarenteed to execute even if there is an exception. // This ensures connections are always properly closed. connection.Close(); }
}
COMMAND CLASS
Commands are issued against databases to take actions against data stores. For example, you could execute a command that inserts or deletes data. For more information on moving data into and out of databases
To issue a command against a database, the Command object must have two basic things: a Connection and CommandText, both of which can be set in the constructor. To execute the command, the Connection has to be open and not in fetching state:
String InsertCmdString;
InsertCmdString = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
SqlCommand mySqlCommand = new SqlCommand(InsertCmdString, myConnection);
SqlCommand features the following methods for executing commands at a SQL Server database:
ExecuteReader Executes commands that return rows. For increased performance, ExecuteReader invokes commands using the Transact-SQL sp_executesql system stored procedure. Therefore, ExecuteReader might not have the effect that you want if used to execute commands such as Transact-SQL SET statements.
ExecuteNonQuery Executes commands such as Transact-SQL INSERT, DELETE, UPDATE, and SET statements.
ExecuteScalar Retrieves a single value (for example, an aggregate value) from a database.
SqlCommand class is used to prepare an SQL statement or StoredProcedure that we want to
execute on a SQL Server database. In this session, we will discuss about executing Transact-SQL
statements on a SQL Server. In a later session we will learn about executing stored procedures
using the SqlCommand class.
The following are the most commonly used methods of the SqlCommand class.
ExecuteReader - Use when the T-SQL statement returns more than a single value. For example,
if the query returns rows of data.
ExecuteNonQuery - Use when you want to perform an Insert, Update or Delete operation
ExecuteScalar - Use when the query returns a single(scalar) value. For example, queries that
return the total number of rows in a table.
We will be using tblProductInventory table for our examples. The table is shown below for
your reference.
tblProductInventory
The sample code below , executes a T-SQL statement, that returns multiple rows of data
using ExecuteReader () method. In this example, we are creating an instance of SqlCommand
class, in just one line, by passing in the command text, and the connection object. For this
purpose, we are using an overloaded constructor of the SqlCommand class that takes 2
parameters(cmdText, connection).
protected void Page_Load(object sender, EventArgs e)
{
string ConnectionString =
ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection("data source=.;
database=Sample_Test_DB; integrated security=SSPI"))
{ //Create an instance of SqlCommand class, specifying the T-SQL command that
string ConnectionString =
ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection("data source=.;
database=Sample_Test_DB; integrated security=SSPI"))
{ //Create an instance of SqlCommand class using the parameter less constructor SqlCommand cmd = new SqlCommand(); //Specify the command, we want to execute using the CommandText property cmd.CommandText = "Select Id,ProductName,QuantityAvailable from
tblProductInventory";
//Specify the connection, on which we want to execute the command //using the Connection property cmd.Connection = connection; connection.Open(); //As the T-SQL statement that we want to execute return multiple rows of data, //use ExecuteReader() method of the command object. GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind(); }
}
In the example below, we are using ExecuteScalar() method , as the T-SQL statement returns
a single value.
protected void Page_Load(object sender, EventArgs e)
{
string ConnectionString =
ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection("data source=.; database=Sample;
integrated security=SSPI"))
{ //Create an instance of SqlCommand class, specifying the T-SQL command //that we want to execute, and the connection object. SqlCommand cmd = new SqlCommand("Select Count(Id) from tblProductInventory",
connection);
connection.Open(); //As the T-SQL statement that we want to execute return a single value, //use ExecuteScalar() method of the command object. //Since the return type of ExecuteScalar() is object, we are type casting to int datatype int TotalRows = (int)cmd.ExecuteScalar(); Response.Write("Total Rows = " + TotalRows.ToString()); }
}
