Thursday, 8 September 2016

Chapter-3 Introducing Connected and Disconnected Environment in ADO.NET





Introducing Connected and Disconnected Environment
1)      A connected environment is one in which a user or an application is constantly connected to a data source.
2)      A connected environment provides the following advantages:
a)      Data concurrency issues are easier to control.
b)      Data is current and updated.

3)      A connected environment has the following disadvantages:
a)      A constant network connection may, sometimes, lead to network traffic logging.
b)      Scalability and performance issues in applications.
4)      In a disconnected environment is one in which a user or an application is not directly connected to a data source.
5)      A disconnected environment provides the following advantages:
a)      Allows multiple applications to simultaneously interact with the data source.
b)      Improves the scalability and performance of applications.
6)      A disconnected environment has the following disadvantages:
a)      Data is not always up to date as no proper connection is established with the data source.
b)      Data concurrency issues can occur when multiple users are updating the data to the data source.
The following figure shows the connected and disconnected environment in ADO.NET.


Working with Command Objects

1)      A Command object is a specific command that is used to manipulate data in a data source in a connected environment.
2)      A Command object represents a DML statement or a stored procedure that is used to retrieve, insert, delete, or modify data in a data source.
3)      The two types of operations performed by a command object to retrieve and modify data in a data source are:
a)      Synchronous operations
b)      Asynchronous operations
4)      During synchronous operations, the command objects are linked to each other.
5)      Executing command objects synchronously results in a sequential execution, where each database command must complete before the next command is executed.
6)      Synchronous operations are performed with the help of the following command objects:
a)      DbCommand object: Executes a command in a data source against a valid open connection
b)      DbParameters object : Assigns parameterized values to stored procedures
c)       DbDataReader object : Retrieves a forward-only, read-only data from the data source

Executing Commands on Database
Database connection object provide three command methods:

ExecuteScalar: Perform query commands that return a single value such as counting number of data.

ExecuteNonQuery: Performs commands that change the database but do not returns a specific value including adding , deleting items from a database.It return the number of rows effective

ExecuteReader: Read record sequentially from the database.

Asynchronous execution of commands enhances the overall performance and responsiveness of the client application. 

ADO.NET supports asynchronous execution of commands to enable parallelism by allowing the client application to execute commands while waiting for the response from a previously issued command.

1)      The asynchronous execution of commands is carried out in the SQLCommand class.
2)      The SQLCommand class provides the following methods for asynchronous execution of commands:
a)      BeginExecuteNonQuery() : Starts the process of asynchronously executing a Transact-SQL statement or stored procedure that does not return rows
b)      BeginExecuteReader() : Starts the process of asynchronously executing a Transact-SQL statement or stored procedure that returns rows
c)       BeginExecuteXmlReader() : Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by the SqlCommand and returns the result as an XMLReader object
d)      EndExecuteNonQuery() : Completes the asynchronous execution of a Transact-SQL statement or stored procedure
e)      EndExecuteReader() : Completes the asynchronous execution of a Transact-SQL statement or a stored procedure, thereby, returning the requested SqlDataReader
f)       EndExecuteXmlReader() : Completes the asynchronous execution of a Transact-SQL statement or a stored procedure, thereby, returning XML data.
3)      Another way of retrieving data asynchronously is by using Multiple Active Result Sets (MARS).
4)      MARS allows the execution of multiple data readers on a single connection.
5)      By default, MARS feature is disabled in an application. You can enable MARS in the connection string, as shown in the following code snippet: 

String connectionString = "Data Source=SQLSERVER01;Initial Catalog=AdventureWorks;User id=sa;Password=Technotech#1234;
MultipleActiveResultSets=True"; //
Enabling MARS property to True

Demo How to connect to database using ADO.NET?

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Button1_Click(object sender, EventArgs e)
    {
        int  roll1 =Convert.ToInt32( TextBox1.Text);
        string name1 = TextBox2.Text;
        SqlConnection con = new SqlConnection("server=abc;database=xyz;Integrated Security=true;");
        con.Open();
        SqlCommand cmd = new SqlCommand("STD12", con);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter SQL = cmd.Parameters.Add("@roll1", SqlDbType.Int,4);
        SQL.Value = roll1;
       SqlParameter SQL1 = cmd.Parameters.Add("@name1", SqlDbType.Char,90);
        SQL1.Value = name1 ;
//        cmd.Parameters.Add(new SqlParameter("@ROLL1", ROLL1));
  //      cmd.Parameters.Add(new SqlParameter("@NAME1", NAME1));
        cmd.ExecuteNonQuery();
        con.Close();

    }
}

Output
a)      A connected environment is one in which a user or an application is constantly connected to a data source.
b)      A disconnected environment is one in which a user or an application is not directly connected to a data source.
c)       The two types of operations performed by a command object to retrieve and modify data in a data source are:
i)        Synchronous operations
ii)       Asynchronous operations
d)      During synchronous operations, the command objects are linked to each other.
e)      Synchronous operations are performed with the help of the following command objects:
i)        DbCommand object
ii)       DbParameters object
iii)     DbDataReader object
f)       ADO.NET supports asynchronous execution of commands to enable parallelism by allowing the client application to execute commands while waiting for the response from a previously issued command.
g)      The SqlCommand class provides the following methods for asynchronous operations:
i)        BeginExecuteNonQuery()
ii)       BeginExecuteReader()
iii)     BeginExecuteXmlReader()
iv)     EndExecuteNonQuery()
v)      EndExecuteReader()
vi)     EndExecuteXmlReader()
h)      MARS allows the execution of multiple data readers on a single connection.


I hope that the functionality of the above attribute is clear to you now. Now you can use this code in your application. If you have further question, just drop a line below and I will try to answer you as soon as possible.

Last but not least, connect with me on Twitter , Facebook , LinkedIn and Google+ for technical updates and articles news. We won’t spam or share your email address as we respect your privacy.




No comments:

Post a Comment