untitled

1

Access 料 (1) 立 料 [] [] [ 料 ]

立 料 Access 料 (2) 料 [ 立 料 ]

Access 料 (3) 料 料 料 料 料 料 欄

ADO.NET ADO.NET.NET Framework 類 來 料 料 料 料 料 Ex MSSQL Access Excel XML ADO.NET 連

.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

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

例 sample6-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;

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

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

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

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

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

例 sample6-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 = "!!"; }

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

例 sample6-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;

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

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

練 sample6-b1 立 論 列 念 立 料 數 數 欄 利 Access 料 利 select 料 料 System.Data.OleDb

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

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

例 sample6-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(); }

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

例 sample6-a3 (4) WebForm1.aspx private void adddb(string id, string pw) { command.commandtext = "insert into member(id, pw)values('"+ id +"','"+ pw +"')"; conn.open(); command.executenonquery(); conn.close(); } updg();

練 sample6-b2 sample6-b1 連 WebForm2 WebForm2 念 利 insert 料

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

例 sample6-a4 (1) 列 利 料行來 CheckBox DataGrid

例 sample6-a4 (2) DataGrid 料行 料行

例 sample6-a4 (3) DataGrid 料行

例 sample6-a4 (4) CheckBox ItemTemplate

例 sample6-a4 (5) private void Page_Load(object sender, System.EventArgs e) { string datasource = Server.MapPath("item.mdb"); string connectionstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +datasource; OleDbConnection conn = new OleDbConnection(connectionStr); OleDbCommand command = new OleDbCommand("", conn);

例 sample6-a4 (6) command.commandtext = "select * from item"; } conn.open(); OleDbDataReader datareader = command.executereader(); itemdg.datasource = datareader; itemdg.databind(); datareader.close(); conn.close();

例 sample6-a4 (7) private void purchasebtn_click(object sender, System.EventArgs e) { int total = 0; foreach(datagriditem item in itemdg.items) { if(((checkbox)item.cells[0].findcontrol("deletecb")).checked) { total+= int.parse(item.cells[2].text); } } totallb.text = total.tostring(); }

練 sample6-b3 sample6-b2 論 念 利 delete 料 DataGrid CheckBox 欄 DataGrid

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

例 sample6-a5 (1) 料 利 DataGrid 料行 LinkButton Image 來連 料

例 sample6-a5 (2) DataGrid 料行 料行

例 sample6-a5 (3) DataGrid 料行

例 sample6-a5 (4) LinkButton ItemTemplate LinkButton CommandName select

例 sample6-a5 (5) LinkButton (DataBindings) DataBinder.Eval( Container.DataItem, name )

例 sample6-a5 (6) 料行 Image DataBinder.Eval (Container.DataI tem,"image")

例 sample6-a5 (7) 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);

例 sample6-a5 (8) WebForm1.aspx } conn.open(); OleDbDataReader datareader = command.executereader(); memberdg.datasource = datareader; memberdg.databind(); datareader.close(); conn.close();

例 sample6-a5 (9) WebForm1.aspx private void memberdg_selectedindexchanged(object sender, System.EventArgs e) { Response.Redirect("WebForm2.aspx?name="+ ((LinkButton)memberDG.SelectedItem.FindControl("idLB")).Text ); }

例 sample6-a5 (10) 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);

例 sample6-a5 (11) WebForm2.aspx string sqlstr = "select * from member " ; sqlstr+= "where name='" + name + "'"; OleDbCommand command = new OleDbCommand(sqlStr, conn);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(); }

練 sample6-b4 sample6-b3 更 數 料 念 利 DataGrid 料行 LinkButton 連 料 更 數欄 料 利 datareader[" 欄 "] 料 料 列

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

DataList (2) DataList

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

練 sample6-b5 sample6-b4 利 DataList 念 利 HTML Table Table Web Label Label 料 料 料 Table DataList ItemTemplate DataList DataSource 行 DataBind