untitled

Transcription

1 1

2 .NET 料.NET 料 料來 類.NET Data Provider SQL.NET Data Provider System.Data.SqlClient 料 MS-SQL OLE DB.NET Data Provider System.Data.OleDb 料 Dbase FoxPro Excel Access Oracle Access ODBC.NET Data Provider 料 MySQL

3 Connection ADO.NET Connection 來連 料來 料來 連 利 連 串 來 連 料來 連 Access 料 連 串 Provider Microsoft.Jet.OLEDB.4.0 Data source 料 路 OleDbConnection 類 ConnectionString 連 串 ( ) Open 料 連 Close 連

4 例 sample3-a1 (1) 立 料 連 private void Page_Load(object sender, System.EventArgs e) { Response.Write(" 連..<br>"); string datasource = Server.MapPath("member.mdb"); string connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +datasource;

5 例 sample3-a1 (2) OleDbConnection conn = new OleDbConnection(connectionStr); Response.Write(" 行連..<br>"); conn.open(); Response.Write(" 連..<br>"); conn.close(); Response.Write(" 連..<br>"); }

6 Command Command 來 行 料 令 SQL 令 Connection ExecuteReader 來 行 Command SQL SELECT 料 DataReader 料 ExecuteNonQuery 來 行 Command SQL INSERT DELETE UPDATE

7 DataReader DataReader 來 料 料列 利 [" 欄 "] 來 欄 料 FieldCount 料 數 Read 讀 料 false true Close GetName 欄 GetValue 欄 料

8 SQL SQL 理 料 SQL DML 料 理 SELECT INSERT UPDATE DELETE DDL 料 CREATE DROP ALTER GRANT

9 SQL SELECT SELECT 令 來 料 料 SELECT 欄 1, 欄 2, FROM 料 WHERE ORDER BY 欄 ( 串 ' ) select * from student select id,name from student order by id desc select id from student where id<10

10 例 sample3-a2 (1) 理 WebForm1.aspx private void loginbtn_click(object sender, System.EventArgs e) { if(idtb.text!= "" && pwtb.text!= "") { if(check(idtb.text, pwtb.text)) Response.Redirect("WebForm2.aspx"); else messagelb.text = "!!"; }

11 例 sample3-a2 (2) WebForm1.aspx else messagelb.text = " 不!!"; } private bool check(string id, string pw) { string datasource = Server.MapPath("member.mdb"); string connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +datasource; OleDbConnection conn = new OleDbConnection(connectionStr);

12 例 sample3-a2 (3) WebForm1.aspx string sqlstr = "select * from member " ; sqlstr+= "where id='" + id + "' and pw='"+pw+"'"; OleDbCommand command = new OleDbCommand(sqlStr, conn); conn.open(); OleDbDataReader datareader = command.executereader(); bool flag = datareader.read(); datareader.close(); conn.close(); } return flag;

13 例 sample3-a2 (4) WebForm2.aspx private void Page_Load(object sender, System.EventArgs e) { string datasource = Server.MapPath("member.mdb"); string connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +datasource; OleDbConnection conn = new OleDbConnection(connectionStr);

14 例 sample3-a2 (5) WebForm2.aspx string sqlstr = "select * from member "; OleDbCommand command = new OleDbCommand(sqlStr, conn); } conn.open(); OleDbDataReader datareader = command.executereader(); memberdg.datasource = datareader; memberdg.databind(); datareader.close(); conn.close();

15 練 sample3-b1 料 立 論 料 識 數 數六 欄 立 論 列 念 利 select 料 料 System.Data.OleDb

16 SQL INSERT INSERT 令 來 料 料 INSERT INTO 料 ( 欄 1, 欄 2, ) VALUES (' 料 1', ' 料 2', ) INSERT INTO student(id, name) VALUES(3, 'Ives')

17 例 sample3-a3 (1) WebForm1.aspx private string connectionstr; private OleDbConnection conn; private OleDbCommand command;

18 例 sample3-a3 (2) WebForm1.aspx private void Page_Load(object sender, System.EventArgs e) { string datasource = Server.MapPath("member.mdb"); connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +datasource; conn = new OleDbConnection(connectionStr); command = new OleDbCommand("", conn); updg(); }

19 例 sample3-a3 (3) WebForm1.aspx private void updg() { command.commandtext = "select * from member"; } conn.open(); OleDbDataReader datareader = command.executereader(); memberdg.datasource = datareader; memberdg.databind(); datareader.close(); conn.close();

20 例 sample3-a3 (4) WebForm1.aspx private void addbtn_click(object sender, System.EventArgs e) { if(idtb.text!="" && pwtb.text!="") { command.commandtext = "insert into member(id, pw)values('"+ idtb.text +"','"+ pwtb.text +"')"; conn.open(); command.executenonquery(); conn.close(); updg(); } }

21 練 sample3-b2 sample3-b1 連 WebForm2 WebForm2 念 利 insert 料

22 SQL DELETE DELETE 令 來 料 料 DELETE FROM 料 WHERE DELETE FROM student WHERE id = 3 AND name = 'Ives'

23 料行 料行 料 ( ) 欄 料 () 料行 DeleteCommand DeleteCommand 料列 參數 e 料 料

24 例 sample3-a4 (1) private string connectionstr; private OleDbConnection conn; private OleDbCommand command;

25 例 sample3-a4 (2) private void Page_Load(object sender, System.EventArgs e) { string datasource = Server.MapPath("member.mdb"); connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +datasource; conn = new OleDbConnection(connectionStr); command = new OleDbCommand("", conn); updg(); }

26 例 sample3-a4 (3) private void updg() { command.commandtext = "select * from member"; } conn.open(); OleDbDataReader datareader = command.executereader(); memberdg.datasource = datareader; memberdg.databind(); datareader.close(); conn.close();

27 例 sample3-a4 (4) private void memberdg_deletecommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { command.commandtext = "DELETE FROM member WHERE id ='" + e.item.cells[1].text + "'"; conn.open(); command.executenonquery(); conn.close(); } updg();

28 練 sample3-b3 論 料列 念 利 delete 料 利 料行 立

29 SQL UPDATE UPDATE 更 令 來 料 料 UPDATE 料 SET 欄 1 = 料 1, 欄 2 = 料 2, WHERE UPDATE student SET name = 'Bob' WHERE id = 2

30 更 料行 料行 料 更 更 EditCommand EditCommand 料列 參數 e DataGrid EditItemIndex 料列 索 e.item.itemindex 行 UpdateCommand CancelCommand 理 狀 料行 欄 TextBox 欄 料 欄 TextBox Text ((TextBox)e.Item.Cells[0].Controls[0]).Text

31 例 sample3-a5 (1) private string connectionstr; private OleDbConnection conn; private OleDbCommand command;

32 例 sample3-a5 (2) private void Page_Load(object sender, System.EventArgs e) { string datasource = Server.MapPath("member.mdb"); connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +datasource; conn = new OleDbConnection(connectionStr); command = new OleDbCommand("", conn); } if(!ispostback) updg();

33 例 sample3-a5 (3) private void updg() { command.commandtext = "select * from member"; } conn.open(); OleDbDataReader datareader = command.executereader(); memberdg.datasource = datareader; memberdg.databind(); datareader.close(); conn.close();

34 例 sample3-a5 (4) private void memberdg_editcommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { memberdg.edititemindex = e.item.itemindex; updg(); } private void memberdg_cancelcommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { memberdg.edititemindex = -1; updg(); }

35 例 sample3-a5 (5) private void memberdg_updatecommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { string id = e.item.cells[1].text; string pw = ((TextBox)e.Item.Cells[2].Controls[0]).Text; command.commandtext = "UPDATE member SET pw='" + pw + "' WHERE id='" + id +"'"; conn.open(); command.executenonquery(); conn.close(); memberdg.edititemindex = -1; updg(); }

36 練 sample3-b4 論 料列 念 利 Update 料 利 料行 立 利 LinkButton 欄

37 料行 (1) DataGrid 料行 料

38 料行 (2) DataGrid 料行

39 料行 (3) LinkButton ItemTemplate LinkButton CommandName select

40 料行 (4) LinkButton (DataBindings) DataBinder.Eval( Container.DataItem, name )

41 料行 (5) 料行 Image DataBinder.Eval (Container.DataI tem,"image")

42 例 sample3-a6 (1) 料 利 DataGrid 料行 LinkButton Image 來連 料 利 FindControl 料列 料行

43 例 sample3-a6 (2) WebForm1.aspx private void Page_Load(object sender, System.EventArgs e) { string datasource = Server.MapPath("member.mdb"); string connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +datasource; OleDbConnection conn = new OleDbConnection(connectionStr); string sqlstr = "select * from member " ; OleDbCommand command = new OleDbCommand(sqlStr, conn);

44 例 sample3-a6 (3) WebForm1.aspx conn.open(); OleDbDataReader datareader = command.executereader(); memberdg.datasource = datareader; memberdg.databind(); datareader.close(); conn.close(); } private void memberdg_selectedindexchanged(object sender, System.EventArgs e) { Response.Redirect("WebForm2.aspx?name="+((LinkButton)mem berdg.selecteditem.findcontrol("idlb")).text); }

45 例 sample3-a6 (4) WebForm2.aspx private void Page_Load(object sender, System.EventArgs e) { string name = Request.QueryString["name"]; string datasource = Server.MapPath("member.mdb"); string connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +datasource; OleDbConnection conn = new OleDbConnection(connectionStr); string sqlstr = "select * from member " ; sqlstr+= "where name='" + name + "'"; OleDbCommand command = new OleDbCommand(sqlStr, conn);

46 例 sample3-a6 (5) WebForm2.aspx conn.open(); OleDbDataReader datareader = command.executereader(); } if(datareader.read()) { namelb.text = datareader["name"].tostring(); countrylb.text = datareader["country"].tostring(); pictureimg.imageurl = datareader["image"].tostring(); } datareader.close(); conn.close();

47 練 sample3-b5 更 數 料 念 利 DataGrid 料行 LinkButton 連 料 更 數欄 料 利 datareader[" 欄 "] 料 料 列

48 DataList (1) DataList 來 料 DataSource 料來 DataBind 行 料

49 DataList (2) DataList

50 DataList (3) ItemTemplate 料 利 HTML Table 行 ( Table DataList ItemTemplate )

51 例 sample3-a7 (1) private void Page_Load(object sender, System.EventArgs e) { if(!ispostback) { string datasource = Server.MapPath("db1.mdb"); string connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +datasource; OleDbConnection conn = new OleDbConnection(connectionStr); string sqlstr = "select * from shopping " ; OleDbCommand command = new OleDbCommand(sqlStr, conn);

52 例 sample3-a7 (2) } conn.open(); OleDbDataReader datareader = command.executereader(); shoppingdl.datasource = datareader; shoppingdl.databind(); datareader.close(); conn.close(); }

53 例 sample3-a7 (3) private void totalbtn_click(object sender, System.EventArgs e) { int total = 0; foreach(datalistitem item in shoppingdl.items) { if(((checkbox)item.findcontrol("purchasecb")).checked) { total += int.parse(((label)item.findcontrol("costlb")).text); } } totallb.text = total.tostring(); }

54 練 sample3-b6 sample3-b5 利 DataList 念 利 HTML Table Table Web Label Label 料 料 料 Table DataList ItemTemplate DataList DataSource 行 DataBind

55 ItemTemplate 列 料 AlternatingItemTemplate 類 ItemTemplate 料列 EditItemTemplate 料列 SelectedItemTemplate 料列

56 例 sample3-a8 (1) private void Page_Load(object sender, System.EventArgs e) { string datasource = Server.MapPath("db1.mdb"); string connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +datasource; conn = new OleDbConnection(connectionStr); string sqlstr = "select * from shopping " ; command = new OleDbCommand(sqlStr, conn); if(!ispostback) updl(); }

57 例 sample3-a8 (2) private void updl() { conn.open(); OleDbDataReader datareader = command.executereader(); shoppingdl.datasource = datareader; shoppingdl.databind(); datareader.close(); conn.close(); } private void shoppingdl_selectedindexchanged(object sender, System.EventArgs e) { updl(); }

58 練 sample3-b7 利 DataList 行不 念 利 HTML Table DataList ItemTemplate AlternatingItemTemplate 不 EditItemTemplate

59 來 HTML GridLayoutPanel 來 行

60 (1)

61 (2) Web

62 (3) ascx

63 例 sample3-a9 WebUserControl1.ascx private void submitbtn_click(object sender, System.EventArgs e) { if(idtb.text == "alice" && pwtb.text == "alice") messagelb.text = ""; else messagelb.text = " 料 "; } messagelb.visible = true;

64 欄 public 類 ID; public WebUserControl1 mycontrol;

65 練 sample3-b8 念 利 UserControl 立 料 料 連 參數

66 ListItem Items 料 來 Items 料 DataSource 料來 DataTextField Text 欄 DataValueField Value 欄 DataBind 行 料

67 例 sample3-a10 (1) 料 private void Page_Load(object sender, System.EventArgs e) { string datasource = Server.MapPath("member.mdb"); connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +datasource; conn = new OleDbConnection(connectionStr); command = new OleDbCommand("", conn); } if(!ispostback) updg();

68 例 sample3-a10 (2) private void updg() { conn.open(); command.commandtext = "select * from member"; OleDbDataReader datareader = command.executereader(); } memberlb.datasource = datareader; memberlb.datatextfield = "account"; memberlb.databind(); datareader.close(); conn.close();

69 例 sample3-a10 (3) private void memberlb_selectedindexchanged(object sender, System.EventArgs e) { conn.open(); command.commandtext = "select * from member where account='" + memberlb.selecteditem.text + "'"; OleDbDataReader datareader = command.executereader(); if(datareader.read()) { idlb.text = datareader["id"].tostring(); accountlb.text = datareader["account"].tostring(); pwlb.text = datareader["pw"].tostring(); } datareader.close(); conn.close(); }

70 料來 料 AllowPaging AllowCustomPage true 料 數 VirtualItemCount 利 select top n 欄 from 料 where not 欄 in (select top m 欄 from 料 ) m 料 n 料

71 練 sample3-b9 論 列 念 e.newpageindex * PageSize 來 料 利 select 來 料