Gii thiu vic iu khin d liu trong ASP.NET

1

M hnh ADO.NET S dng ADO.NET vi ASP.NET

2

CNG NGH ADO.NET

Khi pht trin cc ng dng trn nn web th cng vic ch yu phi gii quyt l x l cc nghip v, trong phn ln l x l C s d liu. Trong mi trng pht trin Microsoft.NET tt c cc ng dng webform hay winform u thng nht s dng chung mt b th vin truy xut v thao tc C s d liu gi l ADO.NET (Active Data Object).3

Mc tiu chnh ca ADO.NET

Cung cp cc lp thao tc CSDL trong c hai mi trng l phi kt ni (Disconected data) v kt ni (Connected data). Tch hp cht ch vi XML (Extensible Markup Language) Tng tc vi nhiu ngun d liu thng qua m t d liu chung. Ti u truy cp ngun d liu (OLE DB & SQL server). Lm vic trn mi trng Internet.4

data provider ADO.NET

provider SQL Server. Cho php kt ni vi CSDL SQL Server (version 7.0 hoc cao hn. provider OLE DB. Cho php kt ni vi CSDL s dng driver OLE DB. N bao gm c CSDL SQL Server trc version 7.0. provider Oracle. Cho php kt ni vi CSDL Oracle (version 8i hoc cao hn). provider ODBC. Cho php kt ni n ngun d liu s dng driver ODBC.5

-Connection, Command v DataReader: cho php lm vic ch Connected -Connection, DataAdapter, Command v Dataset lm vic ch Disconnected.

6

Lp Connection

+ Chc nng: L i tng c nhim v thc hin kt ni n C s d liu cc i tng nh Command thao tc vi CSDL thng qua Connection ny. + Khai bo (c nhiu cch): public OleDbConnection Cn1; public OleDbConnection Cn2 = new OleDbConnection (); public OleDbConnection Cn3 = new OleDbConnection ("Provider=Microsoft.jet.....");

7

Mt s phng thc ca lp ConnectionOpen: Dng m kt ni Cnn.Open(): M kt ni n CSDL do ta ch nh trong ConnectionString Lu : sau khi gi phng thc Open, c th xem kt ni thanh cng hay khng thng qua thuc tnh State ca Connection: Close(): Dng ng kt ni: Cnn.Close();8

Mt s thuc tnh: ca lp Connection+ State: Cho bit trng thi kt ni. (ConnectionState.Open - kt ni c m) + ConnectionString: Cha cc thng tin kt ni.

9

ConnectionString

ConnectionString to bi cc cp tn=gi tr v c cch nhau bi du ; v tr ca cc cp ny l khng quan trng. Mt s thng tin chnh trn ConnectionString:My ch lu tr CSDL: C th l a ch hoc tn CSDL: Tn CSDL s c s dng lm vic Phng thc kim tra. V d string connectionString = "Data Source=192.168.9.2;Initial Catalog=ITWEB;Persist Security Info=True;User ID=sa;Password=xxxxxxx"

string connectionString = "Data Source=localhost;Initial Catalog=ITWEB;Integrated Security=True"

10

Kim tra kt nistring connectionString = "Data Source=localhost;Initial Catalog=ITWEB;Integrated Security=True"; SqlConnection con = new SqlConnection(connectionString); try { con.Open(); lblInfo.Text = "Server Version: " + con.ServerVersion; lblInfo.Text += "
Connection Is: " + con.State.ToString(); } catch (Exception err) { lblInfo.Text = "Error reading the database. " + err.Message; } finally { con.Close(); lblInfo.Text += "
Now Connection Is: " + con.State.ToString(); }

11

Lp Command v DataReader

12

Lp Command

Chc nng: Thc hin cc thao tc i vi CSDL, nh Insert, Update, Delete, Select. Tuy nhin, thc hin c cc lnh ny th cn phi thng qua mt Connection no ang c m.

13

Lp Command- Cch to (chnh tc): OleDbCommand Cmd; Cmd = new OleDbCommand(); Cmd.CommandText = "Cu lnh SQL"; Cmd.Connection = OleDbConnection_Obj;- Hoc vit gn hn: OleDbCommand Cmd = new OleDbCommand("Lnh SQL", OleDbConnection_Obj);

14

V d

SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM Employees";

15

Mt s thuc tnhMt s thuc tnh CommandText: Cha cu lnh SQL cn thc thi, v d: "Select * from Employees, "Insert into Employees (.) values (.)", "Delete from Employees where " Connection: cho bit l i tng Command s dng kt ni no. CommandType: Cho bit CommandText cha StoreProcedure, tn bng hay l cu lnh SQL. Mc nh thuc tnh ny c gi tr l Text.

16

Mt s phng thc dng thc thi cu lnh SQLExecuteNonQuery(): S dng khi CommandText trn thuc dng Insert, Delete, Update. Hm ny tr v s bn ghi b tc ng (affected). ExecuteScalar(): S dng khi CommandText trn l cu lnh SQL ch tr v mt kt qu n, v d cu lnh m tng s bn ghi : Select Count(*) Hm ny tr v hng v ct u tin ca kt qu thc thi truy vn. Cc hng v ct khc b b qua. ExecuteReader(): Dng khi CommandText l mt cu lnh chn (Select). Hm tr v l mt i tng DataReader cha kt qu thc thi cu lnh (thng l cu lnh Select).17

V d// To i tng Connection v m kt ni n CSDL OleDbConnection Conn; Conn = new OleDbConnection(); Conn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; data source="; Conn.ConnectionString += Server.MapPath("../App_Data/nwind.mdb"); Conn.Open(); // To i tng Command v thc thi cu lnh m s bng ghi OleDbCommand Cmd; Cmd = new OleDbCommand(); Cmd.CommandText = "Select Count(*) from Products"; Cmd.Connection = Conn; // Hin th kt qu trn Label int SL = (int)Cmd.ExecuteScalar(); lblThongBao.Text = "S bn ghi trong bng Products: " + SL.ToString(); // Gii phng kt ni. Cmd.Dispose(); Conn.Close();

18

Lp DataReader

Chc nng: Dng n nhn kt qu tr v t phng thc ExecuteReader ca i tng Command. N tng t nh mt Recordset ca ADO, tuy nhin d liu nhn v l Readonly v ch c theo chiu tin.

19

V dstring connectionString = "Data Source=localhost;Initial Catalog=bookdb;Integrated Security=True"; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "Select TenSach from tblsach"; SqlDataReader dr; //khng c new con.Open(); dr = cmd.ExecuteReader(); ListBox lb = new ListBox(); while (dr.Read()) { lb.Items.Add(dr["TenSach"].ToString()); } cmd.Dispose(); con.Close(); form1.Controls.Add(lb);

20

V d (tip)string connectionString = "Data Source=localhost;Initial Catalog=bookdb;Integrated Security=True"; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; hp lm Trong trng cmd.CommandText = "sp_ViewSach"; vic vi th tc SqlDataReader dr; //khng c new con.Open(); dr = cmd.ExecuteReader(); ListBox lb = new ListBox(); while (dr.Read()) { lb.Items.Add(dr["TenSach"].ToString()); } cmd.Dispose(); con.Close(); form1.Controls.Add(lb);

21

V d (tip)

string connectionString = "Data Source=localhost;Initial Catalog=bookdb;Integrated Security=True"; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; Trong trng hp cmd.CommandText = "sp_ViewChiTietSach"; cmd.Parameters.AddWithValue("@MaSach", 2); vic vi th tc v SqlDataReader dr; //khng c new tham s con.Open(); dr = cmd.ExecuteReader(); ListBox lb = new ListBox(); while (dr.Read()) { lb.Items.Add(dr["TenSach"].ToString()); } cmd.Dispose(); con.Close(); form1.Controls.Add(lb);

22

Lp DataSet

Chc nng: L mt i tng cha cc DataTable. N l ni lu tr d liu tm thi cho ng dng trong qu trnh x l. Lp DataSet ny nm trong System.Data.

23

Lp DataSetKhai bo: DataSet Ds; DataSet Ds = new DataSet(); Mt s thuc tnh: Tables: Cha tt c cc bng cha trong Dataset. Tables[i] hoc Tables[Tn_Bng] : Tham chiu n mt bng c th trong Dataset.24

Lp DataAdapter

Chc nng: ng vai tr cu ni / Chuyn i d liu gia Ngun d liu (DataSource) v cc i tng thao tc d liu (nh DataSet chng hn). Mt s phng thc: Fill (DataSet, Tn_Cho_DataSet): in d liu ly c vo DataSet. Update(DataSet/DataTable) : Cp nht d liu trong DataSet, DataTable ngc tr v C s d liu. Mt s thuc tnh: SelectCommand, UpdateCommand, DeleteCommand, InsertCommand: tr v hoc cho php thit lp cc cu lnh SQL Chn (Select), Cp nht (Update), Delete, Insert vo C s d liu.25

protected void cmdEnableAllUser_Click (object sender, EventArgs e) { // To i tng Connection v m kt ni n CSDL OleDbConnection Conn; Conn = new OleDbConnection (); Conn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; data source="; Conn.ConnectionString += Server.MapPath ("../App_Data/QLCB.mdb"); Conn.Open (); // To i tng Command v select ton b bng tblUser OleDbCommand Cmd; Cmd = new OleDbCommand (); Cmd.CommandText = "Select * from tbluser"; Cmd.Connection = Conn; OleDbDataAdapter Da; Da = new OleDbDataAdapter (); Da.SelectCommand = Cmd; // in d liu vo DataSet

DataSet Ds=new DataSet(); Da.Fill(Ds,"DSND"); // Sa d liu ca bng tblUser trong Dataset for (int i=0; i < Ds.Tables["DSND"].Rows.Count; i++) { Ds.Tables["DSND"].Rows [i]["TrangThai"] = 1; } //Cp nht tr li C s d liu OleDbCommandBuilder CmdBuilder = new OleDbCommandBuilder (Da); Da.Update (Ds,"DSND"); /// Cp nht bng DSND trong Ds vo C s d liu //Gii phng cc i tng khng cn s dng Cmd.Dispose (); Da.Dispose (); Conn.Close (); }

26