Docstoc

ADO.net

Document Sample
ADO.net Powered By Docstoc
					ADO.NET

ADO.NET is the strategic application-level interface for providing data access services in
the Microsoft .NET Platform. You can use ADO.NET to access data sources using new
.NET Data Providers, as well as existing OLE DB Data Providers using the OLE DB
.NET Data Provider.

ADO.NET Does Not Depend On Continuously Live Connections


In traditional client/server applications, components establish a connection to a database
and keep it open while the application is running. For a variety of reasons, this approach
is impractical in many applications:

      Open database connections take up valuable system resources. In most cases,
       databases can maintain only a small number of concurrent connections. The
       overhead of maintaining these connections detracts from overall application
       performance.



      In ASP.NET Web applications, the components are inherently disconnected from
       each other. The browser requests a page from the server; when the server has
       finished processing and sending the page, it has no further connection with the
       browser until the next request. Under these circumstances, maintaining open
       connections to a database is not viable, because there is no way to know whether
       the data consumer (the client) requires further data access.



      A model based on always-connected data can make it difficult and impractical to
       exchange data across application and organizational boundaries using a connected
       architecture. If two components need to share the same data, both have to be
       connected, or a way must be devised for the components to pass data back and
       forth.

Data Can Be Cached in Datasets
A dataset is a cache of records retrieved from a data source. It works like a virtual data
store: A dataset includes one or more tables based on the tables in the actual database,
and it can include information about the relationships between those tables and
constraints on what data the tables can contain.

Datasets Are Independent of Data Sources
Data Is Persisted as XML
Comparison of ADO.NET and ADO


In-memory Representations of Data
In ADO, the in-memory representation of data is the recordset. In ADO.NET, it is the
dataset. There are important differences between them.

Number of Tables

A recordset looks like a single table

If a recordset is to contain data from multiple database tables, it must use a JOIN query, which
assembles the data from the various database tables into a single result table.

In contrast, a dataset is a collection of one or more tables.

The tables within a dataset are called data tables; specifically, they are DataTable objects
If a dataset contains data from multiple database tables, it will typically contain multiple
DataTable objects.

That is, each DataTable object typically corresponds to a single database table or view.
In this way, a dataset can mimic the structure of the underlying database.

A dataset usually also contains relationships

A relationship within a dataset is analogous to a foreign-key relationship in a database —
that is, it associates rows of the tables with each other.


Because the dataset can hold multiple, separate tables and maintain information about
relationships between them, it can hold much richer data structures than a recordset,
including self-relating tables and tables with many-to-many relationships.



Data Navigation and Cursors

In ADO you scan sequentially through the rows of the recordset using the ADO
MoveNext method.

In ADO.NET, rows are represented as collections, so you can loop through a table as you
would through any collection, or access particular rows via ordinal or primary key index.

DataRelation objects maintain information about master and detail records and provide a
method that allows you to get records related to the one you are working with

A cursor is a database element that controls record navigation, the ability to update data,
and the visibility of changes made to the database by other users.

ADO.NET does not have an inherent cursor object, but instead includes data classes that
provide the functionality of a traditional cursor.

For example, the functionality of a forward-only, read-only cursor is available in the
ADO.NET DataReader object


Minimized Open Connections
In ADO.NET you open connections only long enough to perform a database operation,
such as a Select or Update. You can read rows into a dataset and then work with them
without staying connected to the data source.
In ADO the recordset can provide disconnected access, but ADO is designed primarily
for connected access.

There is one significant difference between disconnected processing in ADO and
ADO.NET

In ADO you communicate with the database by making calls to an OLE DB provider.
In ADO.NET you communicate with the database through a data adapter (an
OleDbDataAdapter or SqlDataAdapter object), which makes calls to an OLE DB
provider or the APIs provided by the underlying data source.

The important difference is that in ADO.NET the data adapter allows you to control how
the changes to the dataset are transmitted to the database — by optimizing for
performance, performing data validation checks, or adding any other extra processing.


Sharing Data Between Applications


Transmitting an ADO.NET dataset between applications is much easier than transmitting
an ADO disconnected recordset.

To transmit an ADO disconnected recordset from one component to another, you use
COM marshalling

To transmit data in ADO.NET, you use a dataset, which can transmit an XML stream.


The transmission of XML files offers the following advantages over COM
marshalling:

Richer data types

COM marshalling provides a limited set of data types — those defined by the COM
standard. Because the transmission of datasets in ADO.NET is based on an XML format,
there is no restriction on data types. Thus, the components sharing the dataset can use
whatever rich set of data types they would ordinarily use.

Performance

ADO.NET offers another performance advantage, in that ADO.NET does not require
data-type conversions. ADO, which requires COM marshalling to transmit records sets
among components, does require that ADO data types be converted to COM data types.
Penetrating Firewalls

A firewall can interfere with two components trying to transmit disconnected ADO
recordsets. Remember, firewalls are typically configured to allow HTML text to pass, but
to prevent system-level requests (such as COM marshalling) from passing.

Because components exchange ADO.NET datasets using XML, firewalls can allow
datasets to pass.



ADO.NET Overview


Namespaces to work with Data

System.Data

The System.Data namespace consists mostly of the classes that constitute the ADO.NET
architecture

Classes
                   Class                                     Description
Constraint                                  Represents a constraint that can be enforced
                                            on one or more DataColumn objects.
ConstraintCollection                        Represents a collection of constraints for a
                                            DataTable.
ConstraintException                         Represents the exception that is thrown
                                            when attempting an action that violates a
                                            constraint.
DataColumn                                  Represents the schema of a column in a
                                            DataTable.
DataColumnChangeEventArgs                   Provides data for the ColumnChanging
                                            event.
DataColumnCollection                        Represents a collection of DataColumn
                                            objects for a DataTable.
DataException                               Represents the exception that is thrown
                                            when errors are generated using ADO.NET
                                            components.
DataRelation                                Represents a parent/child relationship
                                            between two DataTable objects.
DataRelationCollection                      Represents the collection of DataRelation
                                            objects for this DataSet.
DataRow                                               Represents a row of data in a DataTable.
                                                      Provides data for the RowChanged,
DataRowChangeEventArgs                                RowChanging, OnRowDeleting, and
                                                      OnRowDeleted events.
DataRowCollection                                     Represents a collection of rows for a
                                                      DataTable.
DataRowView                                           Represents a customized view of a DataRow
                                                      exposed as a fully featured Windows Forms
                                                      control.
DataSet                                               Represents an in-memory cache of data.

DataTable                                             Represents one table of in-memory data.

DataTableCollection                                   Represents the collection of tables for the
                                                      DataSet.
DataView                                              Represents a databindable, customized view
                                                      of a DataTable for sorting, filtering,
                                                      searching, editing, and navigation.



System.Data.Oledb

       OleDbConnection
       OleDbCommand
       OleDbCommandBuilder
       OleDbDataAdapter
       OleDbParameter
       OleDbTransaction
       OleDbType-- Specifies the data type of a field, a property, or an System.Data.OleDb.OleDbParameter


System.Data.SQLClient

System.Data.Common – Common shared (or overridden ) by individual data providers

System.Data.SqlTypes – sqlsever datatypes
Working with Data Readers

Demonstration 1: Working with Connection,Command,Datareaders
(ExecuteReader,ExecuteScalar,ExecuteNonQuery)

Demonstration2 : Working with DML Operations

Demonstration3: Working with Procedures

using System;
using System.Data.OleDb;

public class a
{
  public static void Main()
        {
        OleDbConnection cn;
     cn = new OleDbConnection("Provider=MSDAORA.1;Password=tiger;User
ID=scott");

        OleDbCommand com=new OleDbCommand();
        com.Connection=cn;
        com.CommandText = "in1";
        com.CommandType = System.Data.CommandType.StoredProcedure;
//Text,StoredProcedure,TableDirect

      com.Parameters.Add("@eno",1111);
      com.Parameters.Add("@ename","hello");

    try
       {

             cn.Open();
             com.ExecuteNonQuery();
             Console.WriteLine("successful");
       }
    catch(Exception e)
       {
          Console.WriteLine(e);
       }
    finally
       {

                cn.Close();
      }
      }
}

Retrieving Return Values and Output Parameters

Ex:

      Create procedure getauthorcount as
      Return (select count(*) from authors)

      Getauthorcount.cs

      1) Open the Connection to the database- cn
      Dim c As SqlCommand = New SqlCommand("getauthorcount", cn)
        c.CommandType = CommandType.StoredProcedure
        Dim p As SqlParameter = c.Parameters.Add("ReturnValue",
SqlDbType.Int)
        p.Direction = ParameterDirection.ReturnValue
        cn.Open()
        c.ExecuteNonQuery()
        Dim i As Integer = c.Parameters("ReturnValue").Value
        MsgBox(i)
               cn.Close()

      Directions:
      Input,Output, InputOutput, returnvalue


      Improving Performance with Connection Pooling:

      Connection pooling is enabled for both oledb and sqlclient connections by default.

      Oledbconnection pooling is handled by the oledb .net provider
      Sqlclient connection pooling is handled by windows 2000 services

      Close() method

      Properties:

      Connection LifeTime – default 0 that is conn should never get destroyed

      Connection Reset - Indicates whether connections should be reset when they are
      returned to the pool(true)

      Enlist -- Indicates whether a connection should be automatically enlisted in the
      current transaction context. The default value is true

      Max Pool Size – 100
      Min Pool Size – 0

      Pooling - true
      Ex:

using System;
using System.Data.SqlClient;

public class connectionpooling
{
  public static void Main()
  {
    connectionpooling myconnectionpooling = new connectionpooling();
    myconnectionpooling.Run();
  }

 public void Run()
  {
    try
    {
      String connString;

      connString =
"server=(local)\\NetSDK;Trusted_Connection=yes;database=northwind;" +
                   "connection reset=false;" +
                   "connection lifetime=5;" +
                   "min pool size=1;" +
                   "max pool size=50";

      SqlConnection myConnection1 = new SqlConnection(connString);
      SqlConnection myConnection2 = new SqlConnection(connString);
      SqlConnection myConnection3 = new SqlConnection(connString);

      // Open two connections.
      Console.WriteLine ("Open two connections.");
      myConnection1.Open();
      myConnection2.Open();

      // Now there are two connections in the pool that matches the
connection string.
      // Return the both connections to the pool.
      Console.WriteLine ("Return both of the connections to the
pool.");
      myConnection1.Close();
      myConnection2.Close();

      // Get a connection out of the pool.
      Console.WriteLine ("Open a connection from the pool.");
      myConnection1.Open();

      // Get a second connection out of the pool.
      Console.WriteLine ("Open a second connection from the pool.");
      myConnection2.Open();

      // Open a third connection.
      Console.WriteLine ("Open a third connection.");
      myConnection3.Open();

      // Return the all connections to the pool.
      Console.WriteLine ("Return all three connections to the pool.");
          myConnection1.Close();
          myConnection2.Close();
          myConnection3.Close();
        }
        catch (Exception e)
        {
          // Display the error.
          Console.WriteLine(e.ToString());
        }
    }
}




Transaction Programming

3 levels – Database, ado.net, page

ex:
SqlTransaction tr;
SqlConnection cn=new SqlConnection(“constring”);
SqlCommand cm1=new SqlCommand(“update1”);
SqlCommand cm2=new SqlCommand(“update2”);

Cn.open();

Tr=cn.begintransaction;

Cm1.transaction=tr;
Cm2.transaction=tr;

Try
{
Cm1.executeNonQuery();
Cm2.executeNonQuery();

Tr.commit();
}
Catch(Exception e)
{
Tr.rollback();
}

        Transactions
            <%@ transaction=”RequiresNew” %>

        Disabled – no trans
   Supported – if there supports
   Required – if not there create a new
   RequiresNew – always new

   After you enable transactions for an ASP.NET page, the page
   can use two methods from the ContextUtil Class to explicitly
   commit or rollback a transaction: the SetComplete and SetAbort




      Specifying a Command Behavior:
              When you call the ExecuteReader() method of the command object
you can pass an optional CommandBehavior Parameter.

             The command Behavior enumeration has the following values:

      1)CloseConnection :- Automatically closes an open database connection after
      the datareader is closed

      2)KeyInfo :- Retrieves column and primary key with the data

      3) SchemaOnly :- Retrieves column and table schema info without retrieving
      the data

      4) SingleRow :- Optimizes command to retrieve only a single row. If multiple
      rows are returned, additional rows are discarded


             The CloseConnection command behavior is useful when you want to
      return a DataReader from a function

             Ex:
                  Public SqlDataReader GetAuthors()
                  {
                         cn.open();
                         cm=new SqlCommand(“select * from authors”, cn);
                         return
      cm.ExecuteReader(CommandBehavior.CloseConnection);
                  }




      Retrieving Table Schema Info

    Dr=cm.ExecuteReader(CommandBehavior.KeyInfo or
CommandBehavior.SchemaOnly)
          Miscellaneous:

      ExecuteXmlReader (SQL Provider Only)
            As its name implies this method will execute the command and return
an XmlReader object to the caller.

                SQL Server permits a SQL clause to be extended with a FORXML
clause.

          Ex:

Dim c As SqlCommand = New SqlCommand("select * from authors FOR XML
AUTO", cn)
        cn.Open()
        Dim xr As XmlReader = c.ExecuteXmlReader()
        While (xr.Read())
            MsgBox(xr.ReadOuterXml())
        End While
              cn.Close()
     DataSets:




DataSets and DataReaders:
     The two sets of classes are useful in different applications.

     1)If you simply need to grab some database records and quickly display them
     on a webpage, you should use the command and DataReader classes

     If, On the other hand you need to work with a disconnected and memory
     resident representation of database records, you need to use DataSets

     2)Further more it to work, the DataReader must remain conncted to a
     database table. A datareader is tied down to its underlying data source. This
     means for example that you cannot cache a DataReader in your server’s
     memory so that the same datareader can be used on multiple pages or over
     multiple requests to the same page.

     A dataset enables you to represent the results of a database query in yours
     server’s memory.
     Because a DataSet provides you with a memory-resident representation of
     data, you can work with the result of a database query as a whole. For ex a
     dataset includes methods for sorting,filtering and returning a count of the
     records from a database query.

     3)DataReader is a forward only recordset

     DataSets are very useful if you want to represent the same set of records over
     and over again in multiple pages or across multiple requests to the same
     page.(Caching)

     If you need to display the diff. Set of records whenever you request a page,
     use a datareader.
     If you need to display the same set of records when u reques a page, you
     should use a Dataset
DataTable:

       A DataTable is a memory resident representation of a database
table in a dataset.
       Typically, you create a DataTable from an existing database
table.
       However, you also have the option of creating a DataTable and
add it to DataSet.
       A DataTable is collection of rows, represented by its Rows
property, and a collection of columns represented by columns property.

Ex:Shopping cart


     Dim t As DataTable
         Dim c As DataColumn
         Dim r As DataRow

         t = New DataTable()

         c = New DataColumn("id",GetType(Int32))
         t.Columns.Add(c)

         c = New DataColumn("name")
         t.Columns.Add(c)

         c = New DataColumn("age")
         t.Columns.Add(c)

         c = New DataColumn("sal")
         t.Columns.Add(c)

         r = t.NewRow
         r("id") = 1
         r("name") = "sss"
         r("age") = 20
         r("sal") = 10000
         t.Rows.Add(r)

         r = t.NewRow
         r("id") = 2
         r("name") = "sss2"
           r("age") = 23
           r("sal") = 20000
           t.Rows.Add(r)

           For Each r In t.Rows
             For Each c In t.Columns
                TextBox1.Text += r(c) & " "
             Next
             TextBox1.Text += vbCrLf
           Next


      Setting DataTable Properties:

        CaseSensitive-Determines whether comparisions when searching,sorting or
filtering records are case sensitive

       MinimumCapacity:- specifies the initial number of rows that the DataTable
accepts (default is 25)

      PrimaryKey-specifies the array of columns to use as the primarykeys for the
DataTable
      Ex:
            DataColumn c=dt.columns(“productid”);
            Dt.primarykey=c;

      TableName-name of the DataTable


      Setting DataColumn properties in DataTable:

     AllowDBNull – enables or disables null values in the
column(default true)

     AutoIncrement – creates a column that increments its values
automatically

      AutoIncrementSeed – a Long value that represents the initial
value for an autoincrement column

      AutoIncrementStep- represents long increment value

      ColumnName
      DataType- specifies the .NET type of the column

      DefaultValue

     Expression – creates calculated or aggregate functions
     MaxLength
     ReadOnly – A Boolean value which indicates the column allows
updates
     Unique



      Creating calculated and aggregate columns

      1)connection cn
      2)dad-select * from products
      3)ds
      4)dad.fill(ds..)
      5)c=new DataColumn(“salePrice”)
      6)c.Expression=”unitprice*0.10”
      7)ds.tables(0).columns.add(c)


      aggregate functions
      Max,Min,Avg,Count,Sum,Var…..




      --Demonstration on Multiple Tables

      --Demonstration on Data Grid

      --Demonstraion on XML Integration


DataRelation:
       If a DataSet contains multiple DataTables , you can define
relationships.
       The most common type is parent/child relation
      To define the relationship both tables must share a common key

     A DataSet has a Relations Property that represents a collection of
DataRelations class

Ex:
      1)assume that u have two tables with the names products and
categories and have a common column categoryid

      2)ds.relations.add(“relation1”,ds.tables(“categories”).columns(“ca
tegoryid”), ds.tables(“products”).columns(“categoryid”))

      3)dp,dc – datarows

      4)for each dp in ds.tables(“catgories”). Rows
            dp(“categoryname”)
            for each dc in dp.getchildrows(“relation1”)
                  dc(“productname”)
      next
      next




DataViews:
     Represents a particular view on a DataTable. You can use
Dataview to display a filtered or sorted view of the rows in a data table

Filtering:
       SqlDataAdapter dad= new SqlDataAdapter(“select * from
products”,cn);
       Dad.fill(ds,”products”);
       DataView dv=ds.tables(“products”).DefaultView;
             Dv.RowFilter=”categoryid=4”;
             Dg.datasource=dv;


      Sort property:
            dv.sort=”price,discount”
     Finding rows in DataView:
            dv.Find(“smith”) – if it finds return the row no
     -------Cached DataSets


     DataConstraint

           To check constraint for a table u can iterate through
ConstraintsCollection class

           Applying constraints

           DataColumn[] pk=new DataColumn[1];
           Pk[0]=dt.Columns[“productsID”];
           Dt.Constraints.Add(new UniqueConstraint(“c1”,pk[0]));
           Dt.PrimaryKey=pk;

     ForienKeyConstraint and UniqueConstraint

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:16
posted:10/29/2011
language:English
pages:18