Scribd
Upload
164 views33 pages

Dot Net Module-3 Notes

ADO.NET is a data access technology that allows applications to connect to and manipulate data from various data stores. It supports both connected and disconnected architectures. The connected architecture requires an open connection to access data, while the disconnected architecture allows data to be accessed even after the connection is closed by caching it in a dataset. Key ADO.NET classes include the connection, command, data adapter and dataset classes that allow interacting with databases in both architectures.

Uploaded by

Divya Joseph
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
164 views33 pages

Dot Net Module-3 Notes

ADO.NET is a data access technology that allows applications to connect to and manipulate data from various data stores. It supports both connected and disconnected architectures. The connected architecture requires an open connection to access data, while the disconnected architecture allows data to be accessed even after the connection is closed by caching it in a dataset. Key ADO.NET classes include the connection, command, data adapter and dataset classes that allow interacting with databases in both architectures.

Uploaded by

Divya Joseph
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 33

Connected & Disconnected Mechanisms

Connected Architecture of [Link]

What is [Link]?

❑ A data-access technology that enables applications to connect to data stores and manipulate
data contained in them in various ways

❑ Former version was ADO (ActiveX Data Object)

❑ An object oriented framework that allows you to interact with database systems
Objective of [Link]

► Support disconnected data architecture,


► Tight integration with XML,
► Common data representation
► Ability to combine data from multiple and varied data sources
► Optimized facilities for interacting with a database
[Link] Architecture
[Link] Core Objects

► Core namespace: [Link]


► .NET Framework data providers:
► The architecture of [Link], in which connection must be opened to access the data
retrieved from database is called as connected architecture.

► Connected architecture was built on the classes connection, command, datareader and
transaction.

► Connected architecture is when you constantly make trips to the database for any CRUD
(Create, Read, Update and Delete) operation you wish to do.

► This creates more traffic to the database but is normally much faster as you should be doing
smaller transactions.
Disconnected Architecture in [Link]

► The architecture of [Link] in which data retrieved from database can be accessed even when
connection to database was closed is called as disconnected architecture.

► Disconnected architecture of [Link] was built on classes connection, dataadapter,


commandbuilder and dataset and dataview.

► Disconnected architecture is a method of retrieving a record set from the database and storing it
giving you the ability to do many CRUD (Create, Read, Update and Delete) operations on the
data in memory, then it can be re-synchronized with the database when reconnecting.

► A method of using disconnected architecture is using a Dataset.


► DataReader is Connected Architecture since it keeps the connection open until all rows are
fetched one by one
► DataSet is DisConnected Architecture since all the records are brought at once and there is
no need to keep the connection alive
► Difference between Connected and disconnected architecture
C# [Link] Connection

► The Connection Object is a part of [Link] Data Provider and it is a unique


session with the Data Source.

► The Connection Object is Handling the part of physical communication


between the C# application and the Data Source

► The Connection Object connect to the specified Data Source and open a
connection between the C# application and the Data Source, depends on the
parameter specified in the Connection String .

► When the connection is established, SQL Commands will execute with the
help of the Connection Object and retrieve or manipulate data in the Data
Source.
C# SQL Server Connection

► The SqlConnection Object is Handling the part of physical communication between the
C# application and the SQL Server Database . An instance of the SqlConnection class in
C# is supported the Data Provider for SQL Server Database. The SqlConnection
instance takes Connection String as argument and pass the value to the Constructor
statement.

► Sql Server connection string

► connetionString="Data-Source=ServerName;Initial-Catalog=DatabaseName;User
ID=UserName;Password=Password"
► When the connection is established , SQL Commands will execute with the
help of the Connection Object and retrieve or manipulate the data in the
database. Once the Database activities is over , Connection should be closed
and release the Data Source resources .

► [Link]();
private void button1_Click(object sender, EventArgs e)
{
string connetionString = null;
SqlConnection cnn ;
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User
ID=UserName;Password=Password"
cnn = new SqlConnection(connetionString);
try
{
[Link]();
[Link] ("Connection Open ! ");
[Link]();
}
catch (Exception ex)
{
[Link]("Can not open connection ! ");
}
}
}
}
A sample c# program that demonstrate how to execute sql statement and read data from SQL server.

private void button1_Click(object sender, EventArgs e)


{
string connetionString = null;
SqlConnection connection ;
SqlCommand command ;
string sql = null;
SqlDataReader dataReader ;
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User
ID=UserName;Password=Password";
sql = "Your SQL Statement Here , like Select * from product";
connection = new SqlConnection(connetionString);
catch (Exception ex)
{
[Link]("Can not open connection ! ");
}
try
{
[Link]();
command = new SqlCommand(sql, connection);
dataReader = [Link]();
while ([Link]())
{
[Link]([Link](0) + " - " + [Link](1) + " - " +
[Link](2));
}
[Link]();
[Link]();
[Link]();
}
Command Object

► The command object is one of the basic components of ADO .NET.

► The Command Object uses the connection object to execute SQL queries.

► The queries can be in the Form of Inline text, Stored Procedures or direct Table access.

► An important feature of Command object is that it can be used to execute queries and
Stored Procedures with Parameters.

► If a select query is issued, the result set it returns is usually stored in either a DataSet or
a DataReader object.
► Associated Properties of SqlCommand class
 
Property Type of Access Description

The SqlConnection object that is used by the command object


Connection Read/Write
to execute SQL queries or Stored Procedure.
Represents the T-SQL Statement or the name of the Stored
CommandText Read/Write
Procedure.
This property indicates how the CommandText property
should be interpreted. The possible values are:
CommandType Read/[Link] (T-SQL Statement)
[Link] (Stored Procedure Name)
[Link]
This property indicates the time to wait when executing a
particular command.

CommandTimeout Read/Write Default Time for Execution of Command is 30 Seconds.

The Command is aborted after it times out and an exception is


thrown.
Property Description

This method executes the command specifies and returns the


ExecuteNonQuery
number of rows affected.

The ExecuteReader method executes the command specified


ExecuteReader
and returns an instance of SqlDataReader class.

This method executes the command specified and returns the


ExecuteScalar first column of first row of the result set. The remaining rows
and column are ignored.

This method executes the command specified and returns an


ExecuteXMLReader instance of XmlReader class. This method can be used to return
the result set in the form of an XML document
ExecuteNonQuery
► The ExecuteNonQuery method is used to execute the command and return the
number of rows affected.

► The ExecuteNonQuery method cannot be used to return the result set


working with ExecuteNonQuery
public void CallExecuteNonQuery()
{
SqlConnection conn = new SqlConnection();
[Link] = [Link]["connString"].ConnectionString;
try
{
SqlCommand cmd = new SqlCommand();
[Link] = conn;
[Link] = "DELETE FROM EMP WHERE DEPTNO = 40";
[Link] = [Link];
[Link]();
Int32 RowsAffected = [Link]();
[Link](RowsAffected + " rows affected", "Message");
[Link]();
[Link]();
} catch (Exception ex) { [Link]([Link]);
}}
ExecuteReader Method
► The DataReader object is a forward-only and read-only cursor.

► It requires a live connection to the Data Source.

► The DataReader object cannot be directly instantiated. Instead, we must call the ExecuteReader()
Method of the command object to obtain a valid DataReader object.

► SqlDataReader reader = [Link]([Link]);


public void CallExecuteReader()
{
SqlConnection conn = new SqlConnection();
[Link] = [Link]["connString"].ConnectionString;
try
{ SqlCommand cmd = new SqlCommand();
[Link] = conn;
[Link] = "SELECT EMPNO,ENAME FROM EMP";
[Link] = [Link];
[Link]();
SqlDataReader reader = [Link]([Link]);
if ([Link])
{
while ([Link]())
{
[Link]("Employee No: " + reader["EMPNO"].ToString() + " Name :" + reader["ENAME"].
ToString());
} } [Link](); [Link]();
} catch (Exception ex) { [Link]([Link]); } }
ExecuteScalar Method
► The ExecuteScalar Method in SqlCommandObject returns the first column of the first row
after executing the query against the Data Source.

► If the result set contains more than one column or rows, it takes only the first column of the
first row. All other values are ignored.

► If the result set is empty it will return null.


public void CallExecuteScalar()
{
SqlConnection conn = new SqlConnection();
[Link] = [Link]["connString"].ConnectionString;

try
{
SqlCommand cmd = new SqlCommand();
[Link] = conn;
[Link] = "SELECT SUM(SAL) SAL FROM EMP";
[Link] = [Link];
[Link]();
Int32 TotalSalary = Convert.ToInt32([Link]());
[Link]("Total Salary is : " + [Link]());
[Link]();
[Link]();
} catch (Exception ex) { [Link]([Link]);
}
}
► The [Link] property returns a boolean value indicating whether rows are returned
by the method.

► The [Link]() is used to loop through the result set that is returned by the ExecuteReader
met

► ExecuteScalar Method when we use functions like SUM(),COUNT() etc. since it uses fewer
resources than the ExecuteReader method.
SqlDataAdapter

► SqlDataAdapter Class is a part of the C# [Link] Data Provider and it resides in


the [Link] namespace.

► SqlDataAdapter provides the communication between the Dataset and the SQL database.

► We can use SqlDataAdapter Object in combination with Dataset Object.

► DataAdapter provides this combination by mapping Fill method, which changes the data in
the DataSet to match the data in the data source, and Update, which changes the data in the
data source to match the data in the DataSet.
► The SqlDataAdapter Object and DataSet objects are combine to perform both data access

and data manipulation operations in the SQL Server Database.

► When the user perform the SQL operations like Select , Insert etc. in the data containing in

the Dataset Object , it won't directly affect the Database, until the user invoke the Update

method in the SqlDataAdapter.

SqlDataAdapter adapter = new SqlDataAdapter();\

[Link](ds);
Dataset
► The [Link] DataSet contains DataTableCollection and their DataRelationCollection .

► It represents a complete set of data including the tables that contain, order, and constrain the
data, as well as the relationships between the tables.

► We can use Dataset in combination with DataAdapter Class .

► Build and fill each DataTable in a DataSet with data from a data source using a DataAdapter.
The DataSet object offers a disconnected data source architecture.

► The DataSet contains the copy of the data we requested through the SQL statement. The
DataSet is a memory-resident representation of data that provides a consistent relational
programming model regardless of the data source.
► Data Binding with control Like textbox,listbox,gridview
ImageList
► This C# tutorial uses the ImageList control in Windows Forms.
► ImageList stores images for other controls.
► ImageList provides a container for image data.
► The control is not visible directly. It is instead referenced from other controls such as
ListView, which acquire the images from index values into the ImageList.

► We add images manually or dynamically.


► Add ImageList

add an ImageList control to your Windows Forms program in Visual Studio by double-clicking on the
ImageList entry in the Toolbox. The ImageList will appear in the tray of the designer at the bottom.
Next: Try right-clicking on the ImageList instance and select Properties. From there, you can add images
manually in the dialog.
ListView
First, create the ImageList. Then, find the LargeImageList and SmallImageList properties on the ListView.

Instance: Please select the ImageList instance you created as the value of these properties.
Then: You can use the ListView to specify the index of the images inside your ImageList.
using System;
using [Link];
using [Link];
namespace WindowsFormsApplication1
{ public partial class Form1 :
Form {
public Form1(){
InitializeComponent(); }
private void Form1_Load(object sender, EventArgs e) {
// Add these file names to the ImageList on load.
string[] files = { "[Link]", "[Link]" };
var images = [Link];
foreach (string file in files)
{ // Use [Link] to load the file.
[Link]([Link](file)); } } } }
► Add images
You can add any image to the ImageList dynamically by invoking the Add method.

We have a list of file names, and then add each as an Image object using the [Link]
method to read the data.

The Form1_Load event handler is used to make sure the code is run at startup of the
application.
► Images that you added in Image list are added to the [Link], so it is
collection type then you can use most of the collection methods.

► Use the Images property to add, remove and access the image to display in background of
panel.

► Add(key,image)
Remove()
RemoveAt()
RemoveByKey()
► Add Image:
[Link](“pic1”,[Link](“”));
► Remove Image from collection:
[Link]([Link]);
[Link](“pic1”);
► To access images, get image from the imagecollection
[Link]=[Link][0];
[Link]=[Link][“pic1”];

Common questions

Powered by AI

Using a disconnected architecture with a Dataset provides several advantages, including reduced load on database connections, as a continuous database connection isn't necessary for CRUD operations on local data. This allows for scalability across larger applications by minimizing connection time and can lead to more efficient resource use as operations are performed in memory. Additionally, it enables easier handling of large data sets and supports complex operations without constant server interaction .

The ImageList control in Windows Forms applications serves as a container for image data, which can be utilized by other controls, such as ListView, to display images. It allows images to be stored, retrieved, and indexed for use within forms, facilitating the integration and management of image resources in an application .

Using ExecuteScalar for aggregate operations such as SUM or COUNT in ADO.NET is beneficial because it retrieves only the first column of the first row from the result set, making it highly efficient in terms of resource usage. Unlike ExecuteReader, which would return a larger set of data unnecessarily, ExecuteScalar minimizes overhead by delivering only the essential result .

The primary architectural difference between connected and disconnected architectures in ADO.NET lies in how data is accessed and maintained during database operations. In connected architecture, which utilizes classes such as connection, command, and datareader, the application maintains an open connection to the database for performing CRUD operations, resulting in more database traffic but potentially faster transactions due to smaller data loads . In contrast, disconnected architecture uses classes like connection, dataadapter, commandbuilder, dataset, and dataview, where data is retrieved and stored locally even when the database connection is closed, allowing operations on the data in memory and later re-synchronization with the database .

The ExecuteReader method in ADO.NET is used to execute SQL commands and return a SqlDataReader, a forward-only, read-only cursor over the result set. It requires an active and open database connection to fetch data row by row. A key limitation is that it cannot be directly instantiated or used asynchronously, and it can only process one command at a time on the given connection .

The CommandTimeout property in the SqlCommand class specifies how long (in seconds) the system should wait when executing a command before aborting and throwing an exception. This is crucial for managing long-running queries and ensuring that applications do not hang indefinitely during database operations. The default time for execution is 30 seconds .

To use the SqlConnection class in ADO.NET for database interaction, first define a connection string that specifies the data source, initial catalog, user ID, and password. Then, instantiate the SqlConnection object with this connection string and open the connection using the Open method. Perform SQL operations such as command execution. Finally, ensure the connection is closed to release resources after operations are complete .

To update a database using SqlDataAdapter and a Dataset in disconnected mode, first fetch data from the database and populate the Dataset using the Fill method of SqlDataAdapter. Perform desired data manipulations locally on the Dataset. Finally, call the Update method on the SqlDataAdapter to apply changes back to the database, thus synchronizing the in-memory state with the database .

SqlDataAdapter facilitates communication between a Dataset and an SQL database by acting as a bridge for data retrieval and updates. It uses methods such as Fill, which populates the Dataset with data from the SQL database, and Update, which applies changes in the Dataset back to the database. This allows for data access and manipulation operations within the Dataset without directly affecting the database until synchronization is performed .

In ADO.NET's SqlCommand object, the command type determines how the CommandText property should be interpreted. The CommandType can be set to Text, indicating a T-SQL statement, StoredProcedure for executing stored procedures by name, or TableDirect for directly accessing a table. This setting is critical as it defines how SQL commands are executed and processed within the application .

You might also like