Sunday, 4 September 2016

Chapter-2 ADO.NET

Difference between ADO and ADO.NET

1) In Memory representation of Data:- ADO use a record set to represent data that is retrieved from table in memory whereas ADO.NETuse datasets. ADO use data from a single table to retrieve the data from multiple table we have to use Join . But in ADO.NET , we use dataset can store data from multiple table . Dataset represent the structure of a database .
ADO provides read only data on recordsets which allows you to navigate sequentially through the rows of recordset. In ADO.NET rows are represented as collections.

2) Minimized Open Connections:- ADO.NET ,you only connect to database to retrieve and update data records. In ADO.NET it retrieve the data from database and store in dataset and this disconnect from database it works in disconnected. In ADO it works in connected .

3) Sharing Data between Applications:- You can use COM marshaling in ADO to transfer the data from disconnected record sets to another components . In ADO.NET it use dataset using an XML .
Advantage of XML over COM marshaling:

i)                    Richer data type : COM marshaling can convert only one datatype whereas XML can transfer of any datatype
ii)                   ByPassing Firewalls :  A firewall does not allow system level in case of COM Marshaling therefore recordset cannot bypass a firewall. However using XML in ADO.NET you can send data though firewall.

Creating and Managing Connections
1)      To create and manage connections, you need to:
a)      Create a connection object.
b)      Create a command object.
c)       Open the connection object.
d)      Execute the SQL statement in the command object.
e)      Close the connection object.

Execute the following steps to create a connection to the database:

SqlConnection connection = new SqlConnection();
connection.ConnectionString ="Data Source=servername; Initial Catalog=databaseName; User ID=userid; Password=password";
//SqlConnection connection
 //Creating a SqlCommand object
SqlCommand cmd = new SqlCommand("select * from employee", connection);
//Creating SqlReader object
SqlDataReader myReader = cmd.ExecuteReader();
//SqlConnection connection

1.       Create a SqlConnection object. SqlConnection class is used to connect to a SQL Server.
2.       The ConnectionString property provides information, such as the data source and database name, that is used to establish a connection with a database.
3.       Name of the Server to be used when a connection is open
4.       Open():-It opens a database connection with the property settings specified by the
5.       ConnectionString property.
6.       Close ():-It closes the connection to the database.

Handling Connection Events:
The two key events for the SqlConnection class are:

StateChange event
1)      This event occurs when the state of the connection changes.
2)      It receives an argument of type StateChangeEventArgs.
3)      StateChangeEventArgs has the following properties:
i)         CurrentState
ii)        OriginalState

InfoMessage event
1)      This event occurs when an informational message or warning is returned from a data source.
2)      It receives an argument of type SqlInfoMessageEventArgs.
3)      SqlInfoMessageEventArgs has the following properties:
i)         Errors
ii)        Message
iii)      Source

Implementing Connection Pooling
1)      Connection pooling enables a data source to reuse connections for a particular user.
2)      Connection pooling is controlled by certain parameters that are placed into the connection string.
i)        Connection timeout : It is the time in seconds to wait while a connection to the data source is attempted. The default value is 15 seconds.
ii)       Min pool size : It is used to mention the minimum number of connections maintained in the pool. The default value is 0.
iii)     Max pool size : It is used to mention the maximum number of connections allowed in the pool. The default value is 100.
iv)     Pooling : When true, it causes the request for a new connection to be drawn from the pool.
v)      Connection reset : It indicates that the database connection will be reset when the connection is removed from the pool.
vi)     Load balancing , timeout, connection lifetime: It specifies the maximum time in seconds that a pooled connection should live.
vii)   Enlist : When the value is true, the connection is automatically enlisted into the creation thread’s current transaction context. 

1)      A request for a connection is made by the application using the Open() method.
2)      If the Pooling property  is set to true, the pooler attempts to acquire a connection from the pool otherwise a new connection is created.
3)      Close the connection by calling the close() method.

Using Data Adapters
A Data Adapters forms an interface between the application and the data sources from retrieving and saving data. Data Adapter represent a connection and set of command with the help of Data Adapter.

Data Adapter has four properties:-

SelectCommand  property: to set the SQL statement or store procedure that selects data from data source.
InsertCommand  property: to set the SQL statement or store procedure that Insert data in data source.
UpdateCommand  property: to set the SQL statement or store procedure that update in data source.
DeleteCommand  property: to set the SQL statement or store procedure that delete in data source.

string strSQL=”select * from employee”;
DataAdapter adt=new DataAdaper();
SqlConnection con=new SqlConnection(“database=FIRDB ; server=serverName;userid=username;password=password”);
SqlCommand cmd =new SqlCommand(con, strSQL);
DataSet ds=new DataSet();

Note: RecordsAffected Property return number of rows that are changed, inserted or deleted by current database operation performed by Transact SQL statements.
OleDbReader Class provides read only and forward only access to data from data source.

            a)      The two key events for the SqlConnection class are:
i)        StateChange event
ii)       InfoMessage event
b)      Connection pooling enables a data source to reuse connections for a particular user.
c)       A connected environment is one in which a user or an application is constantly connected to a data source.
d)      A disconnected environment is one in which a user or an application is not directly connected to a data source.
e)      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
f)       During synchronous operations, the command objects are linked to each other.
g)      Synchronous operations are performed with the help of the following command objects:
i)        DbCommand object
ii)       DbParameters object
iii)     DbDataReader object
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