untitled

Transcription

1 1

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

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

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

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

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

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

8 例 sample9-a1 (1) 立 料 連 private void Form1_Load(object sender, System.EventArgs e) statetb.text = " 連.."; string connectionstr Data Source=d:\sample.mdb"; OleDbConnection conn = new OleDbConnection(connectionStr);

9 例 sample9-a1 (2) statetb.text += "\r\n 行連.."; try conn.open(); statetb.text += "\r\n 連.."; conn.close(); statetb.text += "\r\n 連.."; catch statetb.text += "\r\n 連..";

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

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

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

13 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

14 例 sample9-a2 (1) 讀 料 料 private void Form1_Load(object sender, System.EventArgs e) string connectionstr Data Source=d:\sample.mdb"; OleDbConnection conn = new OleDbConnection(connectionStr); string sqlstr = "select * from student"; OleDbCommand command = new OleDbCommand(sqlStr, conn);

15 例 sample9-a2 (2) studenttb.text = "ID\tNAME\r\n==================\r\n"; try conn.open(); OleDbDataReader datareader = command.executereader(); while(datareader.read()) studenttb.text += datareader["id"].tostring() + "\t" + datareader["name"].tostring() + "\r\n"; datareader.close();

16 例 sample9-a2 (3) catch finally conn.close();

17 練 sample9-b1 立 Exam 料 Midterm 料 Midterm 料 欄 利 Access 料 利 ListView Midterm 料 念 利 System.Data.OleDb OleDbConnection OleDbCommand 類 來連 料

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

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

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

21 例 sample9-a3 (1) 料 料 private string connectionstr; private OleDbConnection conn; private OleDbCommand command;

22 例 sample9-a3 (2) private void Form1_Load(object sender, System.EventArgs e) // 連 connectionstr Data Source=d:\sample.mdb"; conn = new OleDbConnection(connectionStr); command = new OleDbCommand("", conn); updatelb();

23 例 sample9-a3 (3) private void studentlb_selectedindexchanged(object sender, System.EventArgs e) string[] strtmp = studentlb.selecteditem.tostring().split('\t'); idtb.text = strtmp[0]; nametb.text = strtmp[1]; private void addbtn_click(object sender, System.EventArgs e) if(idtb.text!= "" && nametb.text!= "") adddb(idtb.text, nametb.text); updatelb();

24 例 sample9-a3 (4) private void delbtn_click(object sender, System.EventArgs e) if(idtb.text!= "" && nametb.text!= "") deldb(idtb.text, nametb.text); updatelb();

25 例 sample9-a3 (5) private void updatelb() command.commandtext = "select * from student"; try conn.open(); studentlb.items.clear(); OleDbDataReader datareader = command.executereader(); while(datareader.read()) studentlb.items.add(datareader["id"].tostring() + "\t" + datareader["name"].tostring());

26 例 sample9-a3 (6) datareader.close(); catch finally conn.close();

27 例 sample9-a3 (7) private void adddb(string id, string name) command.commandtext = "insert into student(id, name)values("+ id +",'"+ name +"')"; try conn.open(); command.executenonquery(); catch finally conn.close();

28 例 sample9-a3 (8) private void deldb(string id, string name) command.commandtext = "delete from student where id = "+ id + " and name = '" + name +"'"; try conn.open(); command.executenonquery(); catch finally conn.close();

29 練 sample9-b2 料 sample9-b1 利 ListView Midterm 料 料 念 利 System.Data.OleDb OleDbConnection OleDbCommand 類 來連 料