accessing database programmaticaly in ado.net
Post on 02-Jun-2018
219 Views
Preview:
TRANSCRIPT
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
1/32
Programmatic DatabaseAccess with ADO.NET
http://www.apextgi.in/DotNet/AboutDotNet.aspx -
8/10/2019 Accessing Database Programmaticaly In ADO.NET
2/32
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
3/32
11-%Microsoft
Introducing CS using .NET
J# in Visual Studio .NET
Part 1
Architecture of ADO.NET&
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
4/32
11-'Microsoft
Introducing CS using .NET
J# in Visual Studio .NET
(e!atio"a! tech"o!og)
ADO.NET is desig"ed to access re!atio"a! databases
E*am!e+
Sales database with customers, orders, and products
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
5/32
11-,Microsoft
Introducing CS using .NET
J# in Visual Studio .NET
Architecture
ADO.NET architecture based o" data providers data providers encapsulate DB-specific details
Data Provider
ADO.!"
.!" Pro#rammer
DB
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
6/32
11-Microsoft
Introducing CS using .NET
J# in Visual Studio .NET
E*isti"g data roviders
.NET curre"t!) shis with ' data roviders+ one for $icrosoft S%& Server
one for Oracle
one for older O&!DB technolo#' (used for ADO, )B*+
one for older ODB (Open Database onnectivit'+ technolo#'
ore third-art) roviders are avai!ab!e&
Oracles own provider http//otn.oracle.com/tech/windows/odpnet/
DB0 http//www1b.software.ibm.com/dmdd/downloads/dotnetbeta/ $'S%& http//www.m's2l.com/, http//crlab.com/m's2lnet/
etc.
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
7/32
11-/MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
ADO.NET object mode!
ADO.NET is a" object-orie"ted aroach 0!asses are sread across a "umber of 0 "amesaces
some are provider-neutral, others are provider-specific
S%& Server
System.Data.Common
System.Data
System.Data.SqlClient
System.Data.OleDbother DBs, e.#. $S Access
provider-neutral
ODB (Open Database onnectivit'+
System.Data.Odbc
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
8/32
11-3MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Part 2
Basic database access&
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
9/32
11-4MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Overview of database access
Three stes+
3. open connection to database
0. e4ecute S%& to retrieve records / update DB
5. close connection
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
10/32
11-15MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
import System.Data.*;
import System.Data.OleDb.*;
String sConnection;sConnection = "Provider=Microsoft.et.O!D#.$.%;" &
"Data So'rce=Sales.mdb";
OleDbConnection dbConn;
dbConn = ne( OleDbConnection)sConnection;dbConn.Open);
Message#o+.S,o()dbConn.get-State).toString);
import System.Data.*;
import System.Data.OleDb.*;
String sConnection;sConnection = "Provider=Microsoft.et.O!D#.$.%;" &
"Data So'rce=Sales.mdb";
OleDbConnection dbConn;
dbConn = ne( OleDbConnection)sConnection;dbConn.Open);
Message#o+.S,o()dbConn.get-State).toString);
617 Oe" co""ectio"
0o""ectio"s are oe"ed based o" connection stringi"fo
here we open a connection to a $S Access 0666 database
7Sales.mdb7 must e4ist in same dir as .!8! (e.#. bin9Debu#+
connection
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
11/32
11-11MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Bui!di"g co""ectio" stri"gs
0o""ectio" stri"gs are ve"dor-secific
0o""ectio" stri"gs are "ot we!!-docume"ted
8here to tur" for he!9
www.connectionstrin#s.com
www.able-consultin#.com/ADO:conn.htm
http://www.connectionstrings.com/http://www.able-consulting.com/ADO_conn.htmhttp://www.able-consulting.com/ADO_conn.htmhttp://www.connectionstrings.com/ -
8/10/2019 Accessing Database Programmaticaly In ADO.NET
12/32
11-12MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
627 (etrieve records
(etrieve records via $: $e!ect ;uer)
read-onl' access viaDataReader; field names
String sql fn ln; C'stomer c;sql = "Select * /rom C'stomers Order #y !ast0ame 1sc /irst0ame 1sc;";
OleDbCommand dbCmd;OleDbData2eader db2eader;
dbCmd = ne( OleDbCommand)sql dbConn;db2eader = dbCmd.+ec'te2eader);
(,ile ) db2eader.2ead) // retrieve records one-by-one
3 fn = String.val'eOf)db2eader.get-4tem)"/irst0ame"; ln = String.val'eOf)db2eader.get-4tem)"!ast0ame"; c = ne( C'stomer)fn ln; t,is.list#o+5.get-4tems).1dd)c;
6
String sql fn ln; C'stomer c;sql = "Select * /rom C'stomers Order #y !ast0ame 1sc /irst0ame 1sc;";
OleDbCommand dbCmd;OleDbData2eader db2eader;
dbCmd = ne( OleDbCommand)sql dbConn;db2eader = dbCmd.+ec'te2eader);
(,ile ) db2eader.2ead) // retrieve records one-by-one
3 fn = String.val'eOf)db2eader.get-4tem)"/irst0ame"; ln = String.val'eOf)db2eader.get-4tem)"!ast0ame"; c = ne( C'stomer)fn ln; t,is.list#o+5.get-4tems).1dd)c;
6
data readerrecord
recordrecord
field name of data value in current record
command connection
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
13/32
11-1%MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
6%7 0!ose co""ectio"
Be sure to c!ose reader a"d co""ectio"&
to flush pendin# updates (in #eneral+
so others can access DB (connections are limited resources+
db2eader.Close);
dbConn.Close);
db2eader.Close);
dbConn.Close);
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
14/32
11-1'MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Part %
Data-drive" a!icatio" desig"&
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
15/32
11-1,MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Data Tier desig"
(eca!! N-Tier desig"&
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
16/32
11-1MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
DB c!osed via try-catch-finallyatter"
and
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
17/32
11-1/MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Part '
#dati"g a database&
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
18/32
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
19/32
11-14MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Basic idea
Oe"? e*ecute? a"d c!ose+
String sConnection sql;sConnection = "...";
sql = "...";
OleDbConnection dbConn;
OleDbCommand dbCmd;dbConn = ne( OleDbConnection)sConnection;dbCmd = ne( OleDbCommand)sql dbConn;
int ro(s;
dbConn.Open);
ro(s = dbCmd.+ec'te0on
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
20/32
11-25MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
D)"amic $:
ost of the time )ou@!! "eed to bui!d $: d)"amica!!)
i.e. based on input values from the user
E*am!e+
delete the selected customer?
String fn ln;
C'stomer c;
c = t,is.list#o+5.get-Selected4tem);
fn = c.first0ame;ln = c.last0ame;
sql = "...";
String fn ln;
C'stomer c;
c = t,is.list#o+5.get-Selected4tem);
fn = c.first0ame;ln = c.last0ame;
sql = "...";
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
21/32
11-21MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
D)"amic stri"g bui!di"g
D)"amic $: re;uires d)"amic stri"g bui!di"g
E*am!e+
build d'namic S%& to delete selected customer?
dont for#et the delimiters for strin#s ; dates@
String sql;
sql = String./ormat)"Delete /rom C'stomers 9,ere " & "/irst0ame=>3%6> 1nd !ast0ame=>356>;"
fn ln;
String sql;
sql = String./ormat)"Delete /rom C'stomers 9,ere " & "/irst0ame=>3%6> 1nd !ast0ame=>356>;"
fn ln;
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
22/32
11-22MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
E*am!e
De!ete se!ected customer&
private void list#o+5-Selected4nde+C,anged)83 String fn ln sql; int ro(s; C'stomer c; c = )C'stomer t,is.list#o+5.get-Selected4tem); if )c == n'll ret'rn; // nothing selected
fn = c.first0ame; ln = c.last0ame; sql = String./ormat)"Delete /rom C'stomers 9,ere " & "/irst0ame=>3%6> 1nd !ast0ame=>356>;" fn ln; . . . dbConn.Open); ro(s = dbCmd.+ec'te0on3%6> 1nd !ast0ame=>356>;" fn ln; . . . dbConn.Open); ro(s = dbCmd.+ec'te0on
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
23/32
11-2%MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Oos
Tr) to de!ete O@Dah!? Cathie&
8hat hae"s9
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
24/32
11-2'MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Escai"g de!imiters
ost commo" DB rogrammi"g mistae+
for#ettin# to escape delimiter characters?
$o!utio"9
=ust replace with 0 in a row, i.e.
private void list#o+5-Selected4nde+C,anged)83 String fn ln sql; int ro(s; C'stomer c; c = )C'stomer t,is.list#o+5.get-Selected4tem); if )c == n'll ret'rn; // nothing selected
fn = c.first0ame;
ln = c.last0ame;
fn = fn.2eplace)">" ">>"; ln = ln.2eplace)">" ">>"; sql = String./ormat)"Delete /rom C'stomers 9,ere " & "/irst0ame=>3%6> 1nd !ast0ame=>356>;" fn ln;
private void list#o+5-Selected4nde+C,anged)83 String fn ln sql; int ro(s; C'stomer c; c = )C'stomer t,is.list#o+5.get-Selected4tem); if )c == n'll ret'rn; // nothing selected
fn = c.first0ame;
ln = c.last0ame;
fn = fn.2eplace)">" ">>"; ln = ln.2eplace)">" ">>"; sql = String./ormat)"Delete /rom C'stomers 9,ere " & "/irst0ame=>3%6> 1nd !ast0ame=>356>;" fn ln;
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
25/32
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
26/32
11-2MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Data$ets
Data$ets are a" i"-memor) data structure
easil' filled with data from a database
easil' passed around
easil' displa'ed in a C app
Data$et
Name Price $toc
Ants 6.EF G666
Birds E.EF G66
ats 0F.FG 366
Do#s 1F.FG 06
DBommand onnectionDataAdapter
Data$et mirrors the database
data forms a temporar' table called7"able7 within DataSet
Tab!e
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
27/32
11-2/MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
i!!i"g a Data$et
DataAdater object is used to fi!! a Data$et&
E*am!e+
fill DataSet with all product data
sql = "Select * /rom Prod'cts Order #y 0ame 1sc;";
...
DataSet ds;
OleDbData1dapter adapter;
ds = ne( DataSet);
adapter = ne( OleDbData1dapter)dbCmd;
dbConn.Open);
adapter./ill)ds;
dbConn.Close);
sql = "Select * /rom Prod'cts Order #y 0ame 1sc;";
...
DataSet ds;
OleDbData1dapter adapter;
ds = ne( DataSet);
adapter = ne( OleDbData1dapter)dbCmd;
dbConn.Open);
adapter./ill)ds;
dbConn.Close);
Tab!e
Name Price $toc
Ants 6.EF G666
Birds E.EF G66
ats 0F.FG 366
Do#s 1F.FG 06
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
28/32
11-23MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Data
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
29/32
11-24MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
Data
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
30/32
11-%5MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
!ushi"g cha"ges bac to database
(eco""ect? a"d a!) adater@s %pdate'(method
use )ommand*uilderob=ect to #enerate necessar' S%& for 'ou
// retrieve existing data set from gridds = )DataSet t,is.data?rid5.get-DataSo'rce);
...
OleDbCommand#'ilder cmd#'ilder;cmd#'ilder = ne( OleDbCommand#'ilder)adapter;
dbConn.Open);
adapter.Apdate)ds; // this ill thro exception if update's( conflictdbConn.Close);
// retrieve existing data set from gridds = )DataSet t,is.data?rid5.get-DataSo'rce);
...
OleDbCommand#'ilder cmd#'ilder;cmd#'ilder = ne( OleDbCommand#'ilder)adapter;
dbConn.Open);
adapter.Apdate)ds; // this ill thro exception if update's( conflictdbConn.Close);
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
31/32
11-%1MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET
$ummar)
Databases are a critica! como"e"t of most busi"ess as
$: is the sta"dard rogrammi"g !a"guage for databases
Database rogrammi"g is based o" framewor c!asses
in .!", those classes are called ADO.!"
the more 'ou Hnow about S%& the better
-
8/10/2019 Accessing Database Programmaticaly In ADO.NET
32/32
11-%2MicrosoftIntroducing CS using NETJ# in Visual Studio NET
top related