Ado net
Download
1 / 28

ADO.NET - PowerPoint PPT Presentation


  • 157 Views
  • Uploaded on

ADO.NET. Definition. ADO.NET - Active Data Objects. A set of object-based data access interfaces for .NET platform It provides consistent access to data sources including databases, flat files, XML etc. ADO.NET leverages the power of XML to provide disconnected access to data. ADO.NET Classes.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' ADO.NET' - emilie


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Ado net

ADO.NET

ADO.NET


Definition
Definition

  • ADO.NET - Active Data Objects. A set of object-based data access interfaces for .NET platform

  • It provides consistent access to data sources including databases, flat files, XML etc.

  • ADO.NET leverages the power of XML to provide disconnected access to data

ADO.NET



Two categories
Two Categories

  • ADO.NET and the XML classes in the .NET Framework converge in the DataSet object. DataSet represents an in-memory cache of data.

  • A .NET Framework data provider is used for connecting to a database, executing commands, and retrieving results.

ADO.NET


Platform requirements
Platform Requirements

  • MDAC 2.6

  • System.Data.dll

  • System.Xml.dll

  • Namespaces

    • System.Data

    • System.Data.SqlClient

    • System.Data.OleDb

ADO.NET




Datareader vs dataset
DataReader vs. DataSet

Use DataSet to:

  • Remote data access between tiers or from an XML Web service.

  • Interact with data dynamically such as binding to a Windows Forms/Web Forms

  • Cache data locally in your application.

ADO.NET


Datareader
DataReader

Since DataReader is streamed it is used for:

  • Fast read-only access

  • High performance

  • Reduced overhead (memory and processor resources)

ADO.NET


Dataset
DataSet

  • It is a memory-resident representation of data

  • It provides a consistent relational programming model regardless of the data source.

  • The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables

ADO.NET



Net data providers
.Net Data Providers

  • They nothing but database drivers

  • The Framework comes with following providers:

    • SQL Server

      • Native .Net driver

    • Oracle

      • Native .Net driver

    • OLE DB

      • To connect through OLE DB driver

    • ODBC

      • To connect through ODBC driver

ADO.NET


Ole db vs native provider
OLE DB vs. Native Provider

Additional Layers with OLE DB

ADO.NET


Making connection
Making Connection

SqlConnection myConnection = new SqlConnection();myConnection.ConnectionString =

"Persist Security Info=False;” +

“Integrated Security=SSPI; ” +

“database=northwind; ” +

“server=mySQLServer;Connect Timeout=30";

myConnection.Open();

ADO.NET


More connections
More Connections

OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB; +

“Data Source=localhost;" +                       "Integrated Security=SSPI;

“Initial Catalog=northwind");

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=c:\somepath\myDb.mdb;" +

"User Id=admin;" +

"Password=" +

OleDbConnection conn = new OleDbConnection(connString);

ADO.NET


Retrieving data
Retrieving Data

  • Create connection object

  • Open connection

  • Create command object

  • Get DataReader object

  • Read one row at a time until end of stream

  • Process the row

ADO.NET


SqlConnection nwindConn =

new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

nwindConn.Open();

SqlCommand catCMD = nwindConn.CreateCommand();

catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories";

SqlDataReader myReader = catCMD.ExecuteReader();

while (myReader.Read())

{

Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));

}

myReader.Close();

nwindConn.Close();

ADO.NET


Retrieve into dataset
Retrieve into DataSet

SqlConnection nwindConn = new SqlConnection(...)

SqlCommand selectCMD = new SqlCommand("SELECT ...");

SqlDataAdapter custDA = new SqlDataAdapter();

custDA.SelectCommand = selectCMD;

nwindConn.Open();

DataSet custDS = new DataSet();

custDA.Fill(custDS, "Customers");

nwindConn.Close();

ADO.NET


Updating dataset
Updating DataSet

  • Any changes to the DataSet can be saved back to Database using DataAdapter

  • DataAdapter has properties for

    • SelectCommand

    • InsertCommand

    • UpdateCommand

    • DeleteCommand

  • You need to create and set these commands, so that data is written back to database

ADO.NET


Updatecommand example
UpdateCommand Example

  • Fill a dataset

  • Modify one of the cells

  • Create UpdateCommand

  • Send Update to database

DataSet ds = new DataSet();

SqlDataAdapter custDA = new SqlDataAdapter();

custDA.SelectCommand = cmd;

custDA.Fill(ds,"customers");

DataRow cRow = ds.Tables["customers"].Rows[0];

cRow["ContactName"] = “XXXXXX"; //modify contactName

CONTINUED…

ADO.NET


// Create the UpdateCommand.

SqlCommand upd;

SqlParameter parm;

upd = new SqlCommand("UPDATE Customers " +

" SET ContactName = @ContactName" +

" WHERE CustomerID = @oldCustomerID", conn);

upd.Parameters.Add("@CustomerID", SqlDbType.NChar,

5, "CustomerID");

upd.Parameters.Add("@ContactName", SqlDbType.NVarChar,

40, "ContactName");

parm = upd.Parameters.Add("@oldCustomerID",

SqlDbType.NChar, 5, "CustomerID");

parm.SourceVersion = DataRowVersion.Original;

custDA.UpdateCommand = upd;

custDA.Update(ds,"customers");

ADO.NET


Calling stored procedure
Calling Stored Procedure

SqlCommand salesCMD =

new SqlCommand("SalesByCategory", nwindConn);

salesCMD.CommandType = CommandType.StoredProcedure;

SqlParameter myParm = salesCMD.Parameters.Add("@CategoryName",

SqlDbType.NVarChar, 15);

myParm.Value = "Beverages";

SqlDataReader myReader = salesCMD.ExecuteReader();

while (myReader.Read())

{

Console.WriteLine(...)

}

ADO.NET


Databinding
Databinding

  • Web Controls and windows controls such as ListBox, TextBox have DataSource property

  • DataSource can be set to a DataTable or a DataView. This is databinding

  • Databinding is the process of attaching “raw” data to GUI objects.

ADO.NET


Databinding samples
Databinding Samples

  • Go to http://www.asp.net/Tutorials/quickstart.aspx

  • Select “Data Binding Server Controls ” from the left hand menu

  • Use “#” syntax

    • See example in the next page

ADO.NET


<html><head>

<script language="C#" runat="server">

void Page_Load(Object sender, EventArgs e) {

Page.DataBind();

}

int orderCount{

get {

return 11;

}

}

</script>

</head>

<body>

<h3>DataBinding to a Property on the Page</font></h3>

<form runat=server>

Number of Orders: <b><%# orderCount %></b>

</form>

</body></html>

ADO.NET


Datagrid
DataGrid

  • Mother of all Server Controls

  • Displays ADO.NET data in a scrollable grid

  • The data source for DataGrid are:

    • A DataTable

    • A DataView

    • A DataSet

    • A DataViewManager

    • A single dimension array

    • Any component that implements the IListSource interface

    • Any component that implements the IList interface

ADO.NET


Datagrid control
DataGrid Control

<form runat=server>

<asp:DataGrid id="dataGrid1" runat="server"

BorderColor="black"

BorderWidth="1"

GridLines="Both"

CellPadding="3"

CellSpacing="0"

HeaderStyle-BackColor="#aaaadd"

/>

</form>

ADO.NET



ad