Connected & Disconnected Mechanisms

Connected Architecture of ADO.NET

What is ADO.NET?

❑ 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 ADO.NET

► 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
ADO.NET Architecture
ADO.NET Core Objects

► Core namespace: System.Data

► .NET Framework data providers:
► The architecture of ADO.net, 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 ADO.NET

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

► Disconnected architecture of ADO.net 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# ADO.NET Connection

► The Connection Object is a part of ADO.NET 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 .

► cnn.Close();
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
{
cnn.Open();
MessageBox.Show ("Connection Open ! ");
cnn.Close();
}
catch (Exception ex)
{
MessageBox.Show("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)
{
MessageBox.Show("Can not open connection ! ");
}
try
{
connection.Open();
command = new SqlCommand(sql, connection);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
MessageBox.Show(dataReader.GetValue(0) + " - " + dataReader.GetValue(1) + " - " +
dataReader.GetValue(2));
}
dataReader.Close();
command.Dispose();
connection.Close();
}
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/Write1.Text (T-SQL Statement)
2.StoredProcedure (Stored Procedure Name)
3.TableDirect
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();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM EMP WHERE DEPTNO = 40";
cmd.CommandType = CommandType.Text;
conn.Open();
Int32 RowsAffected = cmd.ExecuteNonQuery();
MessageBox.Show(RowsAffected + " rows affected", "Message");
cmd.Dispose();
conn.Dispose();
} catch (Exception ex) { MessageBox.Show(ex.Message);
}}
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 = cmd.ExecuteReader(CommandBehavior.CloseConnection);

public void CallExecuteReader()
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
try
{ SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT EMPNO,ENAME FROM EMP";
cmd.CommandType = CommandType.Text;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.HasRows)
{
while (reader.Read())
{
MessageBox.Show("Employee No: " + reader["EMPNO"].ToString() + " Name :" + reader["ENAME"].
ToString());
} } cmd.Dispose(); conn.Dispose();
} catch (Exception ex) { MessageBox.Show(ex.Message); } }
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();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT SUM(SAL) SAL FROM EMP";
cmd.CommandType = CommandType.Text;
conn.Open();
Int32 TotalSalary = Convert.ToInt32(cmd.ExecuteScalar());
MessageBox.Show("Total Salary is : " + TotalSalary.ToString());
cmd.Dispose();
conn.Dispose();
} catch (Exception ex) { MessageBox.Show(ex.Message);
}
}
► The reader.HasRows property returns a boolean value indicating whether rows are returned
by the method.

► The reader.Read() 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# ADO.NET Data Provider and it resides in

the System.Data.SqlClient 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();\

adapter.Fill(ds);
 Dataset
► The ADO.NET 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 System.Drawing;
using System.Windows.Forms;
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 = { "image.png", "logo.jpg" };
var images = imageList1.Images;
foreach (string file in files)
{ // Use Image.FromFile to load the file.
images.Add(Image.FromFile(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 Image.FromFile
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 ImageList.ImageCollection, 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:
imageList1.Images.add(“pic1”,Image.FromFile(“”));
► Remove Image from collection:
imageList1.Images.RemoveAt(ListboxItem.index);
imageList1.Images.RemoveByKey(“pic1”);
► To access images, get image from the imagecollection
Panel1.BackgroundImage=imageList1.images[0];
Panel1.BackgroundImage=imageList1.images[“pic1”];