24 .Net programming ADO.NET NOEA / PQC 2009 A

townripeData Management

Jan 31, 2013 (4 years and 6 months ago)

359 views

1

.Net programming



ADO.NET


NOEA / PQC 2009



12. ADO.NET


Architecture


Connection factory


Config file


DataReader


DataSet


DataGrid


Autogeneration of SQL

2

.Net programming



ADO.NET


NOEA / PQC 2009



Data access

3

.Net programming



ADO.NET


NOEA / PQC 2009



Data Providers


OLE DB:

A COM object, that maps the OLE DB api to the DBMS' api

Is 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

4

.Net programming



ADO.NET


NOEA / PQC 2009



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


5

.Net programming



ADO.NET


NOEA / PQC 2009



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


6

.Net programming



ADO.NET


NOEA / PQC 2009



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

7

.Net programming



ADO.NET


NOEA / PQC 2009

Overview of using databases


4 steps:

1.
Open connection to database

2.
Execute SQL for updating DB or fetching records

3.
Handle data

4.
Close connection

8

.Net programming



ADO.NET


NOEA / PQC 2009

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();

connection

9

.Net programming



ADO.NET


NOEA / PQC 2009

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.


10

.Net programming



ADO.NET


NOEA / PQC 2009



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.


11

.Net programming



ADO.NET


NOEA / PQC 2009

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);


dataset

record

record

record

12

.Net programming



ADO.NET


NOEA / PQC 2009

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();


data reader

record

record

record

13

.Net programming



ADO.NET


NOEA / PQC 2009

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.

14

.Net programming



ADO.NET


NOEA / PQC 2009

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
>

15

.Net programming



ADO.NET


NOEA / PQC 2009

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
>

16

.Net programming



ADO.NET


NOEA / PQC 2009

Datagrid (2)


The script by using DataSet:

<
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
>

17

.Net programming



ADO.NET


NOEA / PQC 2009



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:

1.
Which .Net providers are available

2.
Use factory to create a Connection and a Command object

18

.Net programming



ADO.NET


NOEA / PQC 2009



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

19

.Net programming



ADO.NET


NOEA / PQC 2009



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]);

}

20

.Net programming



ADO.NET


NOEA / PQC 2009



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


21

.Net programming



ADO.NET


NOEA / PQC 2009



.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.

22

.Net programming



ADO.NET


NOEA / PQC 2009



.config fil and ADO.NET


Example of SQLExpress connection string:

<?
xml

version
="1.0"
encoding
="utf
-
8" ?>

<
configuration
>


<
configSections
>


</
configSections
>


<
connectionStrings
>


<
add

name
="
TestConnection
"


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


providerName
="System.Data.SqlClient" />


</
connectionStrings
>

</
configuration
>

Machine name

Nessesary for sqlexpress

Database

Internal name

23

.Net programming



ADO.NET


NOEA / PQC 2009



Get provider and connection string


From ConfigurationManager:

....


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


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

.....

24

.Net programming



ADO.NET


NOEA / PQC 2009



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();


}


}

25

.Net programming



ADO.NET


NOEA / PQC 2009



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
>

26

.Net programming



ADO.NET


NOEA / PQC 2009



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