12 ado net
Download
1 / 26

12. ADO.NET - PowerPoint PPT Presentation


  • 90 Views
  • Uploaded on

12. ADO.NET. Architecture Connection factory Config file DataReader DataSet DataGrid Autogeneration of SQL. Data access. Data Providers. OLE DB: A COM object, that maps the OLE DB api to the DBMS' api Is Microsoft propritary.

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 ' 12. ADO.NET' - poppy


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
12 ado net
12. ADO.NET

  • Architecture

  • Connection factory

  • Config file

  • DataReader

  • DataSet

  • DataGrid

  • Autogeneration of SQL



Data providers
Data Providers

  • OLE DB:A COM object, that maps the OLE DB api to the DBMS' apiIs Microsoft propritary.

  • ODBC: Works similar to a printer driver undependent of DBMS and OS

  • .NET Data provider:A set of classes that implements a set of interfaces and abstract classes

  • Some are provided by Microsoft and are installed with the .Net framework.

  • Others are written by the DBMS provider


Microsoft data providers
Microsoft Data Providers

  • Data Provider Namespace Assembly

  • OLE DB System.Data.OleDb System.Data.dll

  • Microsoft SQL Server System.Data.SqlClient System.Data.dll

  • Microsoft SQL Server Mobile System.Data.SqlServerCe System.Data.SqlServerCe.dll

  • ODBC System.Data.Odbc System.Data.dll

  • Oracle System.Data.OracleClient System.Data.OracleClient.dll


Other providers
Other providers

  • Data Provider Website

  • Firebird Interbase http://www.mono-project.com/Firebird_Interbase

  • IBM DB2 Universal Database http://www-306.ibm.com/software/data/db2

  • MySQL http://dev.mysql.com/downloads/connector/net/1.0.html

  • PostgreSQL http://www.mono-project.com/PostgreSQL

  • Sybase http://www.mono-project.com/Sybase

  • Oracle (deres egen) http://www.oracle.com/technology/tech/windows/odpnet/index.html


Classes and interfaces
Classes and interfaces

  • Object Base Class Implemented Interfaces

  • Connection DbConnection IDbConnection Forbindelse

  • Command DbCommand IDbCommand SQL kommando

  • DataReader DbDataReader IDataReader Forward reader

  • DataAdapter DbDataAdapter IDataAdapter Sammen med Dataset

  • Parameter DbParameter IDataParameter Parametre i kommando

  • Transaction DbTransaction IDbTransaction En transaktion


Overview of using databases
Overview of using databases

  • 4 steps:

    • Open connection to database

    • Execute SQL for updating DB or fetching records

    • Handle data

    • Close connection


Step 1 open connection

connection

Step 1: Open Connection

  • Connection are opened according to connection string info

    • here is a connection to a MS Access database opened

    • The database is located here: @"c:\AnyPlace\MyDb.mdb"

    • @ means that escape characters are discarded

using System.Data;

using System.Data.OleDb;

string sConnection;

sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"

+ "Data Source="+@"c:\AnyPlace\MyDb.mdb");

OleDbConnection dbConn;

dbConn = new OleDbConnection(sConnection);

dbConn.Open();


Step 2 get fetch data
Step 2: Get / fetch data

  • Data are fetched by creating a Command object and use it to execute a SQL statement.

  • Data can be stored in either a DataReader object or a DataSet object.


Differences between datareader and dataset dataadapter
Differences between DataReader and DataSet/DataAdapter

  • DataReader can only be used for reading data.

  • It can only be traversed once (forward).

  • DataAdapter is the connection between DataSet and database.

  • Data are fetched to the DataSet, might be modified and sent back to the database.

  • Possible to traverse forward and backward.

  • A DataSet can contain multible tables.


Step 2 get records method 1 dataset

dataset

record

record

record

Step 2: Get records Method 1: DataSet

  • Get records by SQL Select query and DataAdapter

    • Data can be read and changed

string sql;

sql = " Select lastname, firstname"

+ " From employee"

+ " Order By lastname Asc, firstname Asc;";

OleDbCommand dbCmd;

dbCmd = new OleDbCommand(dbConn, sql);

adapter.SelectCommand = dbCmd);

dataset =new DataSet("MyTable");

adapter.Fill(dataset);


Step 2 get records method 2 datareader

data reader

record

record

record

Step 2: Get records Method 2: DataReader

  • Get records via SQL Select query

    • read-only access to the database

string sql;

sql = " Select lastname, firstname"

+ " From employee"

+ " Order By lastname Asc, firstname Asc;";

OleDbCommand dbCmd;

dbCmd = new OleDbCommand(sql, dbConn);

OleDbDataReader dbReader;

dbReader = dbCmd.ExecuteReader();


What is achieved
What is achieved?

  • We have created a connection to a database.

  • The connection is placed in the connection object.

  • We have done a search by using a SQL-statement.

  • The search was executed by using a command object.

  • The result of the search was stored in a DataSet or as here a DataReader object.

  • Now it is possible to get the data from this object for viewing, passing on to client or handle in other ways.

  • The following example shows how it can be done in a aspx file.


Datagrid
DataGrid

  • A simple example:

<%@ Page Language="C#" AutoEventWireup="true" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

Script kommer ind her.......

<html

<head id="Head1" runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<asp:DataGrid ID="MyDataGrid" runat="server"></asp:DataGrid>

</form>

</body>

</html>


Datagrid 2
Datagrid (2)

  • The script by using DataReader:

<script runat="server">

OleDbDataReader data;

protected void Page_Load(Object Src, EventArgs E)

{

String sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"

+ "Data Source=" + Server.MapPath("firma.mdb");

//Step 1: Open connection

OleDbConnection dbConn = new OleDbConnection(sConnection);

dbConn.Open();

//Step 2: Get data

OleDbCommand dbCom = new OleDbCommand("select * from employee", dbConn);

data = dbCom.ExecuteReader();

MyDataGrid.DataSource = data;

MyDataGrid.DataBind();

}

</script>


Datagrid 21

<script runat="server">

OleDbDataReader data;

protected void Page_Load(Object Src, EventArgs E)

{

String sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"

+ "Data Source=" + Server.MapPath("firma.mdb");

//Step 1: Open connection

OleDbConnection dbConn = new OleDbConnection(sConnection);

dbConn.Open();

//Step 2: Get data

OleDbCommand dbCom = new OleDbCommand("select * from employee", dbConn);

OleDbDataAdapter adapter = new OleDbDataAdapter();

adapter.SelectCommand = dbCom;

DataSet data = new DataSet();

adapter.Fill(data);

MyDataGrid.DataSource = data;

MyDataGrid.DataBind();

}

</script>

Datagrid (2)

  • The script by using DataSet:


Factory
Factory

  • Problem: Unlike ODBC, knowledge of the implementation might be spread to many places in the application.That's because of the set of dedicated classes

  • The solution is to use a factory (from factory pattern)

  • In ADO.NET 2.0, it is implemented in the framework. Otherwise you have to implement it yourself (see Troelsen).

  • A coupIe of examples follows:

    • Which .Net providers are available

    • Use factory to create a Connection and a Command object


Which net data providers are installed
Which .NET data providers are installed?

  • The information is collected from machine.config

using System.Data;

using System.Data.Common;

.....

DataTable dt = DbProviderFactories.GetFactoryClasses();

foreach (DataRow dr in dt.Rows)

{

Console.WriteLine("{0}\t\t{1}\t\t{2}", dr[0], dr[1],dr[2]);

}

Odbc Data Provider .Net Framework Data Provider for Odbc System.Data.Odbc

OleDb Data Provider .Net Framework Data Provider for OleDb System.Data.OleDb

OracleClient Data Provider .Net Framework Data Provider for Oracle System.Data.OracleClient

SqlClient Data Provider .Net Framework Data Provider for SqlServer System.Data.SqlClient


Use factory to create connection and command objects
Use factory to create Connection and Command objects

  • Here is no references in the source code to concrete classes

  • provider and connectionString may be placed in the config file

string provider = "System.Data.OleDb";

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=firma.mdb";

DbProviderFactory factory= DbProviderFactories.GetFactory(provider);

DbConnection conn=factory.CreateConnection();

conn.ConnectionString=connectionString;

conn.Open();

Console.WriteLine(conn.State);

DbCommand command = factory.CreateCommand();

command.CommandText = "SELECT * FROM employee";

command.Connection = conn;

DbDataReader dataReader = command.ExecuteReader();

while (dataReader.Read()){

Console.WriteLine(dataReader[1]);

}


Connection strings
Connection strings

  • Connection strings depends on the provider and are not well documented

  • Where to get help?

    • www.connectionstrings.com

    • www.carlprothman.net/Default.aspx?tabid=81

    • Providers documentation

  • Visual Studio can generate some of them


Config fil
.Config fil

  • Configuration files are used several places in .Net

  • i.e in ASP.NET for setting site properties and security

  • In Remoting for setting remote object properties

  • In ADO.NET to set provider and connection string

  • You might also use own application dependent properties

  • In a .Net application the config file should be called<full assemblyname.config>, i.e test.exe.config or test.dll.config

  • If you call it app.config in Visual Studio, it automatically copied and renamed when the application is built.

  • In ASP.NET it must be named web.config. You might define one in different sub folders, but the security part (authentification e.g.) must be in the root folder of the site.


Config fil and ado net
.config fil and ADO.NET

  • Example of SQLExpress connection string:

<?xmlversion="1.0" encoding="utf-8" ?>

<configuration>

<configSections>

</configSections>

<connectionStrings>

<addname="TestConnection"

connectionString="Data Source=pcm06463\sqlexpress;Initial Catalog=dbtest; User ID=test;Password="

providerName="System.Data.SqlClient" />

</connectionStrings>

</configuration>

Internal name

Database

Machine name

Nessesary for sqlexpress


Get provider and connection string
Get provider and connection string

  • From ConfigurationManager:

....

string provider = ConfigurationManager.ConnectionStrings["TestConnection"].ProviderName;

string connStr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;

.....


A nearly complete example in asp net
A (nearly) complete example in ASP.NET

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

string provider = ConfigurationManager.ConnectionStrings["TestConnection"].ProviderName;

string connStr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;

DbProviderFactory factory = DbProviderFactories.GetFactory(provider);

DbConnection conn = factory.CreateConnection();

conn.ConnectionString = connStr;

conn.Open();

DbCommand command = factory.CreateCommand();

command.CommandText = "SELECT * FROM datotest";

command.Connection = conn;

DataSet ds = new DataSet("TestDataSet");

DbDataAdapter da = factory.CreateDataAdapter();

da.SelectCommand = command;

da.Fill(ds);

MitGitter.DataSource = ds;

MitGitter.DataBind();

}

}


The aspx file
The Aspx file

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<body>

<form id="form1" runat="server">

<div>

<asp:GridView ID="MitGitter" runat="server"/>

</div>

</form>

</body>

</html>


Parameters in sql statements
Parameters in SQL statements

  • Makes it possible to use the same SQL-statement in multible situations.

  • But harder to debug

string sqlString = "INSERT INTO datotest Values (@dato)";

SqlParameter sqlParam = new SqlParameter("@dato", System.Data.SqlDbType.DateTime);

sqlParam.Value = DateTime.Now;

SqlCommand comm = _conn.CreateCommand();

comm.CommandText = sqlString;

comm.Parameters.Add(sqlParam);

int result = comm.ExecuteNonQuery();

ExecuteNonQuery is used for insert, delete and update


ad