ADO NET 1 Agenda ADO NET Why What

ADO. NET 1

Agenda • • • ADO. NET, Why ? What Is ADO. NET ? Disconnected Scenario – Data Providers. Disconnected Scenario – Data Cache. Disconnected Scenario – Working With DB. Data Relations. Data. View. Connected Scenario. 2

Agenda • • • ADO. NET Why? What Is ADO. NET? Disconnected Scenario – Data Providers. Disconnected Scenario – Data Cache. Disconnected Scenario – Working With DB. Data Relations. Data. View. Connected Scenario. 3

Agenda • • • ADO. NET Why? What Is ADO. NET? Disconnected Scenario – Data Providers. Disconnected Scenario – Data Cache. Disconnected Scenario – Working With DB. Data Relations. Data. View. Connected Scenario. 8

Agenda • • • ADO. NET Why? What Is ADO. NET? Disconnected Scenario – Data Providers. Disconnected Scenario – Data Cache. • Disconnected Scenario – Working With DB. • Data Relations. • Data. View. • Connected Scenario. 11

Agenda • • • ADO. NET Why? What Is ADO. NET? Disconnected Scenario – Data Providers. Disconnected Scenario – Data Cache. • Disconnected Scenario – Working With DB. • Data Relations. • Data. View. • Connected Scenario. 16

Disconnected Scenario – Data Providers : Sql. Connection : מחלקה זו מכילה שתי מתודות עיקריות Sql. Connection. Open() לצורך ביצוע שאילתא וקבלת תשובה Database - מתחברת ל (Scalar או Result set) Sql. Connection. Close(). בסיום העבודה עמו Database– ניתוק התוכנית מה 24

Disconnected Scenario – Data Providers : – דוגמה Sql. Connection cn =new Sql. Connection; () cn. Connection. String = "Data Source=localhost; Initial Catalog = pubs; Integrated Security = True; "; try { cn. Open; (). . . } catch )Exception exc( }. . . { finally { cn. Close; () } 25

Disconnected Scenario – Data Providers Sql. Command Stored ) ( או פרוצדורה מאוחסנת Query ) תפקיד מחלקה זו להריץ שאילתא . Database - ( על ה Procedure : מחלקה זו מכילה מספר מאפיינים חשובים Sql. Command. Connection Sql. Command. Text Sql. Command. Type : מחלקה זו מכילה מספר מתודות עיקריות Sql. Command. Execute. Reader() Sql. Command. Execute. Non. Query() Sql. Command. Execute. Scalar() 26

Disconnected Scenario – Data Providers : – דוגמה Sql. Command Sql. Connection cn = new Sql. Connection(); cn. Connection. String = "Data Source=localhost; Initial Catalog = pubs; Integrated Security = True; "; try { Sql. Command command = new Sql. Command(); command. Connection = cn; command. Command. Text = "SELECT au_lname, au_fname, phone FROM authors"; cn. Open(); Sql. Data. Reader reader = command. Execute. Reader(); . . . } catch (Exception exc) {. . } finally { cn. Close(); 29

Disconnected Scenario – Data Providers : – דוגמה Sql. Data. Adapter Sql. Connection cn = new Sql. Connection(); cn. Connection. String = "Data Source=localhost; Initial Catalog = pubs; Integrated Security = True; "; Data. Set ds = new Data. Set(); Sql. Command command = new Sql. Command(); command. Connection = cn; command. Command. Text = "SELECT au_lname, au_fname, phone FROM authors"; Sql. Data. Adapter adapter = new Sql. Data. Adapter(); adapter. Select. Command = command; try { cn. Open(); adapter. Fill(ds, "Authors"); } : בשקף הבא 32 המשך

Disconnected Scenario – Data Providers : – דוגמה Sql. Data. Adapter : המשך catch (Exception exc) {. . } finally { cn. Close(); } 33

Disconnected Scenario – Data Providers Sql. Data. Adapter : התהליך והמחלקות המשתתפות Sql. Command Sql. Data. Adapter Data. Set Sql. Connection SQL Server 2000 34

Agenda • • • ADO. NET Why? What Is ADO. NET? Disconnected Scenario – Data Providers. Disconnected Scenario – Data Cache. • Disconnected Scenario – Working With DB. • Data Relations. • Data. View. • Connected Scenario. 35

Data. Set Disconnected Scenario – Data Cache Tables Data. Table Columns Constraints Relations : Data. Set מבנה המחלקה Data. Colum n Data. Constrai nts Rows Data. Row Primary. Key Data. Row[] Data. Relatio n Object Collection 38

Agenda • • • ADO. NET Why? What Is ADO. NET? Disconnected Scenario – Data Providers. Disconnected Scenario – Data Cache. Disconnected Scenario – Working With DB. Data Relations. Data. View. Connected Scenario. 44

Disconnected Scenario – Working With DB Data. Adapter - מבנה ה Data. Adapter Select. Command. Update. Command. Insert. Command. Delete. Command Data. Reader Command Connection Database 48

Disconnected Scenario – Working With DB Data. Set - שלב א – בניית ה Data. Set ds = new Data. Set(); Sql. Connection cn = new Sql. Connection(); cn. Connection. String = "Data Source=localhost; Initial Catalog = pubs; Integrated Security =True; "; Sql. Command command = new Sql. Command(); command. Connection = cn; command. Command. Text = "SELECT au_lname, au_fname, phone FROM authors"; Sql. Data. Adapter adapter = new Sql. Data. Adapter(); adapter. Select. Command = command; . . . המשך 49

Disconnected Scenario – Working With DB שלב א – המשך . . . try { cn. Open(); adapter. Fill(ds, "Authors"); } catch (Sql. Exception exc ) { … } finally { cn. Close(); } 50

Disconnected Scenario – Working With DB ( )אפשרות נוספת Data. Set- שלב א – בניית ה Data. Set ds = new Data. Set(); String cn_str = "Data Source=localhost; Initial Catalog = pubs; Integrated Security =True; "; string query = "SELECT au_lname, au_fname, phone FROM authors"; Sql. Data. Adapter adapter = new Sql. Data. Adapter(query, cn_str); try { adapter. Fill(ds, "Authors"); } catch (Sql. Exception exc) { … } 51

Disconnected Scenario – Working With DB ( )הוספת קשרים Data. Set - שלב א – בניית ה . • לאחר שקראנו את הטבלאות ניתן ורצוי להוסיף את הקשרים ביניהם . Data. Relation • את הקשרים יוצרים באמצעות המחלקה : לדוגמה Data. Relation dr = new Data. Relation(“”Cities. Persons, ds. Tables[“Cities”]. Columns[“City. ID”], ds. Tables[“Persons”]. Columns[“City. ID”]); ds. Relations. Add(dr); 52

Disconnected Scenario – Working With DB ( שלב ב – העיבוד )שינוי הנתונים : לדוגמה ds. Tables[“Persons”]. Rows[0][“Last. Name”] = “Shoshana”; : או ds. Tables[0]. Rows[0][1] = “Shoshana”; 56

Disconnected Scenario – Working With DB ( שלב ב – העיבוד )הוספת נתונים חדשים : הקצאת רשומה חדשה Data. Row dr = ds. Tables["Persons"]. New. Row(); : הוספת הנתונים לרשומה החדשה dr["Last. Name"] = "Sonya"; dr["First. Name"] = "Is. The. Best"; . . . : Rows הוספת הרשומה החדשה לאוסף ds. Tables["Persons"]. Rows. Add(dr); 57

Disconnected Scenario – Working With DB Database - שלב ג – עידכון ה string cn_str = "Data Source="Phone. Book. mdb"; Provider="Microsoft. Jet. OLEDB. 4. 0"; "; string query = "SELECT * FROM Persons"; Ole. Db. Data. Adapter adapter = new Ole. Db. Data. Adapter(query, cn_str); Ole. Db. Command. Builder builder = new Ole. Db. Command. Builder(adapter); adapter. Insert. Command = builder. Get. Insert. Command(); adapter. Update. Command = builder. Get. Update. Command(); adapter. Delete. Command = builder. Get. Delete. Command(); adapter. Update(ds, "Persons"); 62

Agenda • • • ADO. NET Why? What Is ADO. NET? Disconnected Scenario – Data Providers. Disconnected Scenario – Data Cache. Disconnected Scenario – Working With DB. Data Relations. Data. View. Connected Scenario. 63

Data Relations : דוגמה m_Persons. Adapter. Fill(m_Data. Set, "Persons"); m_Cities. Adapter. Fill(m_Data. Set, "Cities"); m_Data. Set. Relations. Add("Cities. Persons“, m_Data. Set. Tables["Cities"]. Columns["City. ID"], m_Data. Set. Tables["Persons"]. Columns["City. ID"]); 70

Data Relations : על מנת לשמור על שלמות המידע Data. Relations - • ניתן לנצל את ה כאשר השדה Persons Data. Table - חדש ל Data. Row כל ניסיון להוסיף . יעורר חריגה Cities Data. Table - מציין עיר שלא קיימת ב City. ID : על מנת נווט בין הטבלאות Data. Relations - • ניתן לנצל את ה ds. Tables[“Cities”]. Rows(0). Get. Child. Rows(“Cities. Persons"); ds. Tables[“Persons”]. Rows(0). Get. Parent. Row (“Cities. Persons"); 71

Agenda • • • ADO. NET Why? What Is ADO. NET? Disconnected Scenario – Data Providers. Disconnected Scenario – Data Cache. Disconnected Scenario – Working With DB. Data Relations. Data. View. Connected Scenario. 72

Data View. מאפשר להציג את המידע שבטבלה בצורות שונות Data. View • . (Row. State • מאפשר לבצע מיון וסינון )על פי שדות או נוצר מטבלה אחת Data. View כל , Data. Views • מכל טבלה ניתן לייצר מספר . בודדה Data. View view = new Data. View(m_Data. Set. Tables["Persons"]); view. Row. Filter = "City. ID = 1"; data. Grid 1. Data. Source = view; Data. View view = new Data. View(m_Data. Set. Tables["Persons"]); view. Row. Filter = "City. ID = 1"; view. Sort = "Last. Name DESC"; data. Grid 1. Data. Source = view; 73

Data View 74

Agenda • • • ADO. NET Why? What Is ADO. NET? Disconnected Scenario – Data Providers. Disconnected Scenario – Data Cache. Disconnected Scenario – Working With DB. Data Relations. Data. View. Connected Scenario. 75

Connected Scenario • במערכות מחוברות התוכנית אינה מתנתקת . לכל אורך תהליך העבודה Database- מה Data Source Xxx. Data. Reader Xxx. Command Xxx. Connection 76

Connected Scenario Xxx. Data. Reader המחלקה . Full Connected Mode - • ניעזר בה על מנת לקרוא מידע ב . Read Only - ו Forward Only היא Data. Reader • המחלקה . Command. Execute. Reader() • נוצרת על ידי המתודה 79

Connected Scenario Xxx. Data. Reader המחלקה : דוגמה Sql. Data. Reader my. Reader; my. Reader = my. Command. Execute. Reader(); while (my. Reader. Read()) { Console. Write. Line(my. Reader[“Field. Name 1”] + ", " + my. Reader[“Field. Name 2”]); } my. Reader. Close(); my. Connection. Close(); 80