ado .net (1) notes/lecture notes 3403 week 07...ado .net (activex data objects) • ado.net is a...
TRANSCRIPT
![Page 1: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/1.jpg)
1
ADO .NET (1)
These slides are meant to be for teaching purposes only and only for the students that are registered in CSE3403 and should not be published as a
book or in any form of commercial product, unless written permission is obtained.
![Page 2: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/2.jpg)
2
ADO .NET (ActiveX Data Objects)
• ADO.NET is a component of .NET that allows access to relational databases from within C# (and other languages) programs.
• A database is – Integrated collection of data– Database management system (DBMS)
• Provides mechanisms for storing and organizing data in a way that is consistent with database’s format
• Allows storage and access to database without knowledge of internal representation
– Relational Databases most popular• Use Structured Query Language (SQL) to perform queries (search)
and manipulate data• Programming languages need an interface to interact with relational
databases
![Page 3: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/3.jpg)
3
Relational data bases overview
• Logical representation of data:– Date can be considered without concern for the
physical structure of data• Composed of tables
– Rows called records (or tuples)– Columns called fields (or attributes)– Primary key: field that contains unique data
• Each tuple can be identified by the value of primary key.
– New sets made from queries called result sets
![Page 4: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/4.jpg)
4
Example. A students data base in MS Access.
Table Student
![Page 5: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/5.jpg)
5
A table (Student table)
![Page 6: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/6.jpg)
6
Queries
• We can access and modify data stored in a relational database using a query language.
• The primary query language used nowadays is SQL (Structured Query Language).
• SQL allows – Extracting data from a database.– Modifying data in the database. – And other things.
![Page 7: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/7.jpg)
7
ADO .NET
• ADO .NET provides an API for accessing database systems from within programs written in one of the .NET languages (such as C#).
![Page 8: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/8.jpg)
8
ADO process and some related classes
• Establish connection to a desired DB– OleDbConnection
• Create a SQL query– OleDbCommand
• Send query to DB and retrieve data– OleDbAdapter
• Store retrieved data (in memory)– DataSet
• Display data on GUI– DataGrid.
• Work with individual values from retrieved data– DataTable, DataRow, DataColumn.
![Page 9: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/9.jpg)
9
How to connect to a database
![Page 10: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/10.jpg)
10
How to connect to a database
…
![Page 11: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/11.jpg)
11
How to connect to a database …
Navigate and find the desired DB
![Page 12: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/12.jpg)
12
How to connect to a database …/
Drag and drop this in your GUI … creates an OleDbConnection
that is capable to connecting to the DB as soon as you start
the program.
![Page 13: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/13.jpg)
13
![Page 14: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/14.jpg)
14
Data menu from ToolBox has related buttons …
![Page 15: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/15.jpg)
15
Next … need to add an OleDbDataAdapter.
• The OleDbDataAdapter allows to create a query (and submit it to the DB).
![Page 16: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/16.jpg)
16
Adding an OleDbDataAdapter
As soon as the data adapter is dropped, the Configuration Wizard opens.
![Page 17: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/17.jpg)
17
Setting up a query
![Page 18: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/18.jpg)
18
Setting up a query …
![Page 19: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/19.jpg)
19
Setting up a query … select tables involved.
![Page 20: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/20.jpg)
20
Setting up a query …write your query
![Page 21: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/21.jpg)
21
Once the data adapter wizard is finished, its properties reflect the set query.
![Page 22: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/22.jpg)
22
Next … need to add a DataSet. • The data adapter retrieves the data and the dataSet captures the retrieved
data and holds it in memory. (after that we can display this data on a DataGrid).
Attempting to drop a DataSetin the designer view, it opens
the “Add Dataset” wizard.
Select “Untyped” dataset”.
![Page 23: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/23.jpg)
23
DataSet added.
![Page 24: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/24.jpg)
24
Next … we need to add a DataGrid.
• A DataGrid is used to display, in the GUI, the data that is held in a DataSet.
![Page 25: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/25.jpg)
25
Finally … we need to write the code to “connect” the DataAdapter to the DataSet and the DataSet to the
dataGrid.
Add code here.
![Page 26: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/26.jpg)
26
Writing the code ..
![Page 27: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/27.jpg)
27
Writing the code …/
“execute the query that is defined in oleDbDataAdapter1, and put the retrieved data
into dataSet1”. Have a reference name “someStorage” for this data.
“Take a data that is held in dataSet1 and put it in dataGrid1.”
![Page 28: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/28.jpg)
28
Running ..
![Page 29: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/29.jpg)
29
Another example …
Entire table
Projection on table
Dept
Join of two tables (Student and Enroll)
![Page 30: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/30.jpg)
30
Tables Student, Dept, Enroll
![Page 31: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/31.jpg)
31
Designer view
Use 3 adapters and 3 datasets.
The format of the data grid is set by the DataGrid properties.
Right-click …
![Page 32: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/32.jpg)
32
Adapter 1 properties
![Page 33: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/33.jpg)
33
Adapter 1 query builder
![Page 34: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/34.jpg)
34
Adapter 2 properties
![Page 35: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/35.jpg)
35
Adapter 2 query builder
![Page 36: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/36.jpg)
36
Adapter 3 properties
![Page 37: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/37.jpg)
37
Adapter 3 query builder
![Page 38: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/38.jpg)
38
The code
![Page 39: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/39.jpg)
39
The related codepublic Form1(){
InitializeComponent();
oleDbDataAdapter1.Fill( dataSet1, "Student");dataGrid1.SetDataBinding( dataSet1, "Student");
oleDbDataAdapter2.Fill( dataSet2, "Dept");dataGrid2.SetDataBinding( dataSet2, "Dept");
oleDbDataAdapter3.Fill( dataSet3, "res");dataGrid3.SetDataBinding( dataSet3,"res");
}
![Page 40: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/40.jpg)
40
ADO .NET (2)
These slides are meant to be for teaching purposes only and only for the students that are registered in CSE3403 and should not be published as a
book or in any form of commercial product, unless written permission is obtained.
![Page 41: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/41.jpg)
41
Another example (modify the DB)
The Course table retrieved.
Upon entering a course number (e.g., 778) and clicking the button, the course
(“New course”, 778, “Computer Sciences”) is added to the Course
table.
![Page 42: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/42.jpg)
42
.../
![Page 43: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/43.jpg)
43
The related code.
The button event
dataSet1 holds the Course table before the insertion.
dataSet2 holds the Course table after the insertion.
![Page 44: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/44.jpg)
44
The code …
public Form1()
{InitializeComponent();
oleDbDataAdapter1.Fill( dataSet1, "res");
dataGrid1.SetDataBinding( dataSet1, "res");
}
Retrieve and display the table before the
insertion.
![Page 45: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/45.jpg)
45
The code …/private void button1_Click(object sender, System.EventArgs e){
string theInsertSQL;theInsertSQL = "INSERT INTO Course (cname, cno, dname) "
+ " VALUES ( 'New Course' , "+ textBox1.Text + " , 'Computer Sciences' )";
oleDbDataAdapter1.InsertCommand.CommandText = theInsertSQL;
oleDbConnection1.Open();
oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();
dataSet2.Clear(); // clear the accepting dataSet … just in case (no need here).
oleDbDataAdapter1.Fill( dataSet2, "res");dataGrid2.SetDataBinding( dataSet2, "res");
}
Load query results in dataSet and and display on
dataGrid.
Assemble query
Establish connection to DB
Execute query
![Page 46: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/46.jpg)
46
Extract individual values from a DataGrid
• Can extract individual values from a DataGrid(using classes DataTable, DataRow, DataColumn).
DataTable myTable = dataGrid1.DataSource;
private void PrintValues(DataTable myTable){
foreach( DataRow myRow in myTable.Rows){
foreach( DataColumn myCol in myTable.Columns){
Console.WriteLine(myRow[myCol]);
}
}
}
![Page 47: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/47.jpg)
47
ADO and XML
• Once the data is in the DataSet, it can be converted to XML. – The XML can be written to a file
• dataSet1.WriteXml( "Course.xml") or– Can be displayed in the GUI
• dataSet1.GetXml( )
![Page 48: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/48.jpg)
48
Example …
![Page 49: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/49.jpg)
49
XML file generated
![Page 50: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/50.jpg)
50
The design
Generates XML and writes it in file and also in TextBox
TextBox that displays XML
Retrieves Course table
Gets filled with Course table.
DataGrid
![Page 51: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/51.jpg)
51
using System;using System.Drawing;using System.Collections;using System.ComponentModel;using System.Windows.Forms;using System.Data;
namespace WindowsApplication2ADOXML{
/// <summary>/// Summary description for Form1./// </summary>public class Form1 : System.Windows.Forms.Form{
private System.Data.OleDb.OleDbConnection oleDbConnection1;private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;private System.Data.DataSet dataSet1;private System.Windows.Forms.DataGrid dataGrid1;private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;private System.Windows.Forms.Button button1;private System.Windows.Forms.TextBox textBox1;/// <summary>/// Required designer variable./// </summary>private System.ComponentModel.Container components = null;
public Form1(){
//// Required for Windows Form Designer support//InitializeComponent();
//// TODO: Add any constructor code after InitializeComponent call//oleDbDataAdapter1.Fill( dataSet1, "res");dataGrid1.SetDataBinding( dataSet1, "res");
}
The code …
![Page 52: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/52.jpg)
52
…protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
![Page 53: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/53.jpg)
53
…private void InitializeComponent(){
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();this.dataSet1 = new System.Data.DataSet();this.dataGrid1 = new System.Windows.Forms.DataGrid();this.button1 = new System.Windows.Forms.Button();this.textBox1 = new System.Windows.Forms.TextBox();((System.ComponentModel.ISupportInitialize)(this.dataSet1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();this.SuspendLayout();// // oleDbConnection1// this.oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=""E:\4413 -- e-commerce course\__WINTER 2006\MySlides\_Slides _3 ADO NET slides\ADO .NET My code tests\MyStudentsDB.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";
// // oleDbDataAdapter1// this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;this.oleDbDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {new System.Data.Common.DataTableMapping("Table", "Course", new
System.Data.Common.DataColumnMapping[] {
![Page 54: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/54.jpg)
54
…new System.Data.Common.DataColumnMapping("cname", "cname"),new System.Data.Common.DataColumnMapping("cno", "cno"),
new System.Data.Common.DataColumnMapping("dname", "dname")})});// // oleDbInsertCommand1//
this.oleDbInsertCommand1.CommandText = "INSERT INTO Course(cname, cno, dname) VALUES (?, ?, ?)";this.oleDbInsertCommand1.Connection = this.oleDbConnection1;this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("cname", System.Data.OleDb.OleDbType.VarWChar, 255, "cname"));this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("cno", System.Data.OleDb.OleDbType.Integer, 0, "cno"));this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("dname", System.Data.OleDb.OleDbType.VarWChar, 255, "dname"));// // oleDbSelectCommand1// this.oleDbSelectCommand1.CommandText = "SELECT cname, cno, dname FROM Course";this.oleDbSelectCommand1.Connection = this.oleDbConnection1;// // dataSet1// this.dataSet1.DataSetName = "NewDataSet";this.dataSet1.Locale = new System.Globalization.CultureInfo("en-US");// // dataGrid1//
this.dataGrid1.AlternatingBackColor = System.Drawing.Color.WhiteSmoke;
this.dataGrid1.BackColor = System.Drawing.Color.Gainsboro;this.dataGrid1.BackgroundColor = System.Drawing.Color.DarkGray;this.dataGrid1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;this.dataGrid1.CaptionBackColor = System.Drawing.Color.DarkKhaki;this.dataGrid1.CaptionFont = new System.Drawing.Font("Tahoma", 8F,
System.Drawing.FontStyle.Bold);this.dataGrid1.CaptionForeColor = System.Drawing.Color.Black;this.dataGrid1.CaptionText = "Course";this.dataGrid1.DataMember = "";this.dataGrid1.FlatMode = true;this.dataGrid1.Font = new System.Drawing.Font("Times New Roman", 9F);this.dataGrid1.ForeColor = System.Drawing.Color.Black;this.dataGrid1.GridLineColor = System.Drawing.Color.Silver;this.dataGrid1.HeaderBackColor = System.Drawing.Color.Black;
this.dataGrid1.HeaderFont = new System.Drawing.Font("Tahoma", 8F, System.Drawing.FontStyle.Bold);
![Page 55: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/55.jpg)
55
…this.dataGrid1.HeaderForeColor = System.Drawing.Color.White;this.dataGrid1.LinkColor = System.Drawing.Color.DarkSlateBlue;this.dataGrid1.Location = new System.Drawing.Point(16, 24);this.dataGrid1.Name = "dataGrid1";this.dataGrid1.ParentRowsBackColor = System.Drawing.Color.LightGray;this.dataGrid1.ParentRowsForeColor = System.Drawing.Color.Black;this.dataGrid1.SelectionBackColor = System.Drawing.Color.Firebrick;this.dataGrid1.SelectionForeColor = System.Drawing.Color.White;this.dataGrid1.Size = new System.Drawing.Size(400, 128);this.dataGrid1.TabIndex = 0;// // button1this.button1.Font = new System.Drawing.Font("Microsoft Sans Serif", 10F,
System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(161)));this.button1.Location = new System.Drawing.Point(16, 168);this.button1.Name = "button1";this.button1.Size = new System.Drawing.Size(136, 24);this.button1.TabIndex = 1;
this.button1.Text = "Make XML";this.button1.Click += new System.EventHandler(this.button1_Click);
// // textBox1this.textBox1.AcceptsReturn = true;this.textBox1.AcceptsTab = true;this.textBox1.AllowDrop = true;this.textBox1.Location = new System.Drawing.Point(24, 208);this.textBox1.Multiline = true;this.textBox1.Name = "textBox1";this.textBox1.ScrollBars = System.Windows.Forms.ScrollBars.Both;this.textBox1.Size = new System.Drawing.Size(392, 144);this.textBox1.TabIndex = 2;this.textBox1.Text = "textBox1";// // Form1this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);this.ClientSize = new System.Drawing.Size(432, 357);this.Controls.Add(this.textBox1);this.Controls.Add(this.button1);this.Controls.Add(this.dataGrid1);this.Name = "Form1";this.Text = "Form1";((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit();((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();this.ResumeLayout(false);
}#endregion
![Page 56: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/56.jpg)
56
…//// <summary>/// The main entry point for the application./// </summary>[STAThread]static void Main() {
Application.Run(new Form1());}
private void button1_Click(object sender, System.EventArgs e){
// dataSet1.WriteXml( "Course.xml");
textBox1.Text = "Writing the following XML: \r\n" + dataSet1.GetXml( )+ "\r\n";
}}
}
generate XML corresponding to data residing in dataSet1 and andwrite it in file Course.xml.
Generate XML that corresponds to data residing
in dataSet1.
![Page 57: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/57.jpg)
57
The generated Course.xml file is independently accessible …
![Page 58: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/58.jpg)
58
• The end
![Page 59: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/59.jpg)
59
ASP .NET (2)
These slides are meant to be for teaching purposes only and only for the students that are registered in CSE3403 and should not be published as a
book or in any form of commercial product, unless written permission is obtained.
![Page 60: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/60.jpg)
60
ASP and ADO (assumes knowledge of ADO)
• We can access a database from within a Web Application (ASP .NETprogram), by combining ASP.NET and ADO.NET.
• This results to a 3-Tier architecture. • 1st tier : client. See the GUI (front end) and interacts with it.• 2nd tier: (asp) server. Hosts the ASP.NET program.
– Hosts the .aspx and .cs files as well as the program(s) that perform ADO activities.
– Does not host the Database. – Receives requests from the 1st tier– Processes requests.– Becomes client to the 3rd tier, which hosts the data base.
• Poses DB queries to 3rd tier.• Receives responses (query results) from 3rd tier.• Processes responses of 3rd tier, assembles .html documents and sends results to 1st
tier.• 3rd tier: (db) server. Hosts the Database.
– Receives DB queries (typically in SQL) from 2nd tier. – Processes queries and generates results.– Sends results to 2nd tier.
![Page 61: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/61.jpg)
61
3rd tier (server to 2nd tier)
2nd tier (server to 1st tier; client to 3rd tier)
Client’s request receivedand a web page is generated.
3-tier architecture …1st tier (client to 2nd tier)
begin
![Page 62: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/62.jpg)
62
…2nd tier (server to 1st tier; client to 3rd tier)
3rd tier (server to 2nd tier)
2nd tier receives response and processes event:1. Creates query2. Submits query to 3rd tier
Receives query
Process Query
1st tier (client to 2nd tier)
User clicks “Show data” button.
![Page 63: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/63.jpg)
63
3rd tier (server to 2nd tier)
Generate results
2nd tier (server to 1st tier; client to 3rd tier)
Receive results (and places result
into DataSet and DataGrid).
1st tier (client to 2nd tier)
end
![Page 64: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/64.jpg)
64
Example
User is prompted for ID and password.
![Page 65: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/65.jpg)
65
Example …
1. User typed ID and password. 2. Request was sent to 2nd tier.3. 2nd tier checked … decided
that (ID,Pass) is not valid.
![Page 66: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/66.jpg)
66
Example …
1. User typed again ID and password. 2. Request was sent to 2nd tier.3. 2nd tier checked … decided that (ID,Pass) is valid.4. Responded back to 1st tier that can now proceed to
retrieve data.
User presses button.
![Page 67: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/67.jpg)
67
Example …
1. Server received button event, 2. Processed event
a) Create query, connection to DB, and sent query to 3rd tier.b) Received results of query from 3rd tier.c) Placed query results into DataGrid.d) Created web page shown here.e) Sent web page to 1st tier.
![Page 68: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/68.jpg)
68
The code This application has two
.aspx files and two .cs files.
This .cs file handles the login
process.
This .cs file handles the DB access
process.
![Page 69: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/69.jpg)
69
The code …using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;
namespace WebApplication3ASPwithADO{
/// <summary>/// Summary description for WebForm1./// </summary>public class WebForm1 : System.Web.UI.Page{
protected System.Web.UI.WebControls.Label Label1;protected System.Web.UI.WebControls.Label Label2;protected System.Web.UI.WebControls.TextBox TextBox1;protected System.Web.UI.WebControls.TextBox TextBox2;protected System.Web.UI.WebControls.Label Label3;protected System.Web.UI.WebControls.Button Button1;
private void Page_Load(object sender, System.EventArgs e){
}
![Page 70: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/70.jpg)
70
…#region Web Form Designer generated codeoverride protected void OnInit(EventArgs e){
//// CODEGEN: This call is required by the ASP.NET Web Form Designer.//InitializeComponent();base.OnInit(e);
}/// <summary>/// Required method for Designer support - do not modify/// the contents of this method with the code editor./// </summary>
private void InitializeComponent(){
this.TextBox1.TextChanged += new System.EventHandler(this.TextBox1_TextChanged);this.Button1.Click += new System.EventHandler(this.Button1_Click);this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
![Page 71: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/71.jpg)
71
…private void Button1_Click(object sender, System.EventArgs e){
string userID = TextBox1.Text;string userPassword = TextBox2.Text;string loginResult = ValidateUser(userID, userPassword);if (!loginResult.Equals( "Welcome") ){
Label3.Text = "INVALID LOGIN! Try again ... ";TextBox1.Text = "";TextBox2.Text = "";Page_Load(this, e); // reload page
}else {
// load and display the ADO related form
Response.Redirect( "WebForm2ForADOpartOfASPandADOApplication.aspx");}
}
private string ValidateUser(string userID, string userPassword) {
if (userID.Equals( "abc") && userPassword.Equals( "pass")){
return "Welcome";}else {
return "Invalid login.";}
}private void TextBox1_TextChanged(object sender, System.EventArgs e){}}}
If invalid login, clear textboxes and re-
prompt.
!!! Usage of a second Form here. In case of valid login,
open another Form that performs the database
access. This is not necessary, but it makes the code more modular and convenient to
write.
![Page 72: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/72.jpg)
72
WebForm2ForADOpartOfASPandADOApplication.aspx .cs
using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;
namespace WebApplication3ASPwithADO{
/// <summary>/// Summary description for WebForm2ForADOpartOfASPandADOApplication./// </summary>public class WebForm2ForADOpartOfASPandADOApplication : System.Web.UI.Page{
protected System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;protected System.Data.OleDb.OleDbCommand oleDbSelectCommand1;protected System.Data.OleDb.OleDbCommand oleDbInsertCommand1;protected System.Data.DataSet dataSet1;protected System.Web.UI.WebControls.DataGrid DataGrid1;protected System.Web.UI.WebControls.Button Button1;protected System.Web.UI.WebControls.Label Label1;protected System.Web.UI.WebControls.Label Label2;protected System.Data.OleDb.OleDbConnection oleDbConnection1;
private void Page_Load(object sender, System.EventArgs e){
// Put user code to initialize the page here}
![Page 73: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/73.jpg)
73
…#region Web Form Designer generated codeoverride protected void OnInit(EventArgs e){
//// CODEGEN: This call is required by the ASP.NET Web Form Designer.//InitializeComponent();base.OnInit(e);
}
/// <summary>/// Required method for Designer support - do not modify/// the contents of this method with the code editor./// </summary>
private void InitializeComponent(){
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();this.dataSet1 = new System.Data.DataSet();((System.ComponentModel.ISupportInitialize)(this.dataSet1)).BeginInit();
// // oleDbConnection1//
this.oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=""E:\4413 -- e-commerce course\__WINTER 2006\MySlides\_Slides _3 ADO NET slides\ADO .NET My code tests\MyStudentsDB.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";
The connection to DB.
![Page 74: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/74.jpg)
74
…// // oleDbDataAdapter1//
this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Enroll", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("cno", "cno"),new System.Data.Common.DataColumnMapping("dname", "dname"),new System.Data.Common.DataColumnMapping("grade", "grade"),new System.Data.Common.DataColumnMapping("secno", "secno"),new System.Data.Common.DataColumnMapping("sid", "sid")})});
// // oleDbSelectCommand1//
this.oleDbSelectCommand1.CommandText = "SELECT cno, dname, sid, grade FROM Enroll WHERE (sid = 104)";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;// // oleDbInsertCommand1//
this.oleDbInsertCommand1.CommandText = "INSERT INTO Enroll(cno, dname, grade, secno, sid) VALUES (?, ?, ?, ?, ?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("cno",
System.Data.OleDb.OleDbType.Integer, 0, "cno"));this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("dname",
System.Data.OleDb.OleDbType.VarWChar, 255, "dname"));this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("grade",
System.Data.OleDb.OleDbType.Double, 0, "grade"));this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("secno",
System.Data.OleDb.OleDbType.Integer, 0, "secno"));this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("sid",
System.Data.OleDb.OleDbType.Integer, 0, "sid"));
The query.
![Page 75: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/75.jpg)
75
…/// // dataSet1// this.dataSet1.DataSetName = "NewDataSet";this.dataSet1.Locale = new System.Globalization.CultureInfo("en-US");this.Button1.Click += new System.EventHandler(this.Button1_Click);this.Load += new System.EventHandler(this.Page_Load);((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit();
}#endregion
private void Button1_Click(object sender, System.EventArgs e){
Label1.Text = ".. connectiing to DB...";oleDbConnection1.Open(); // open DBLabel1.Text += "connected!";
// execute query oleDbDataAdapter1.Fill( dataSet1, "res");
Label1.Text += "... retrieved data!";
// display results to datagridDataGrid1.DataBind();DataGrid1.Visible = true;
}}}
Execute query, receive result (at 2nd tier) and
put result into dataSet1.
Populate DataGrid with results (of dataSet1) and make it visible. Note, this looks different from how it would be
done normally in a windows application. (oleDbDataAdapter1.Fill( dataSet1, "res");
DataGrid1.SetDataBinding( dataSet1, "res"); )
![Page 76: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/76.jpg)
76
The end
![Page 77: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/77.jpg)
77
Web Services in .NET (1)
These slides are meant to be for teaching purposes only and only for the students that are registered in CSE3403 and should not be published as a
book or in any form of commercial product, unless written permission is obtained.
![Page 78: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/78.jpg)
78
What is a Web Service?• A Web Service (WS) is a class (or many
classes) that resides in a computer and it is accessible from another computer, in the sense that clients can call methods of this class remotely.
• In .NET, any method of a class can be equipped to be accessed remotely. All it needs is to associate the attribute [WebMethod] with that method.
[ WebMethod ]
public void MyMethod( …) { …}
![Page 79: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/79.jpg)
79
Web Services vs “Local” Services …
System calls are written in some language (part of the OS).
SOAP is written in XML.
Access is done via local system calls (managed by the local operating system).
Access is done via “remote system calls” managed by HTTP and SOAP (Simple Object Access Protocol), a protocol that allows calling remote methods).
Class produces DLL file which is stored in local computer, and it is accessed from classes residing in same computer.
Class produces DLL file which is stored in local computer, and it is accessed from classes residing in some other computer
Local serviceWeb service
![Page 80: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/80.jpg)
80
Web Services vs “Local” Services …/
We learn about local methods via the API.
We learn about Web Services via UDDI (Universal Description Discovery and Integration) and DISCO. [ in practice, however, so far, we learn mostly through word-of-mouth ]
API may be written in XML.WSDL is written in XML.
Documentation for a local method is provided via a local API(Application Programming Interface).
Documentation for a web method is provided via WSDL(Web Service Description Language).
Local serviceWeb service
![Page 81: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/81.jpg)
81
Evolution of the internet and the WWW (0)[ “Web 0” , just internet]
Generation 0FTP, e-mail
HTML
![Page 82: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/82.jpg)
82
Evolution of the web (1) – [ “Web 1” ]
Generation 1Static HTML
HTML
![Page 83: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/83.jpg)
83
Evolution of the web (2) [ “Web 1+”, “Web 2.0” ]
Generation 2Web Applications
HTML
![Page 84: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/84.jpg)
84
Evolution of the web (3)[ “Web 2.0”, “Web 3.0” ]
HTML, XML
HTML, XML
Generation 3 (under construction)Web Services
![Page 85: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/85.jpg)
85
Evolution of the web (4)
empIDHTML, XML
HTML, XML, RDF
Generation 4 (under contemplation)Semantic Web
“Web 3.0”
Be able to realize that empID and Emp-idmean the same thing.
Emp-id
![Page 86: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/86.jpg)
86
Benefits of Web Services …• May facilitate significant boost for developing
distributed applications.• Use standard (SOAP) for message exchange.• Use standard for interface description (WSDL),
in XML.• Independent of programming languages and
operating systems.• Utilize existing Internet protocols (HTTP usually,
but also SMTP and FTP).
![Page 87: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/87.jpg)
87
Benefits of Web Services …/• Web Services allow you to interconnect:
– Different companies– Many/any devices– Applications– Different clients
• Not just browsers
• Distribution and integration of application logic• Enable the programmable Web
– Not just the purely interactive Web• Loosely coupled (scale well)
![Page 88: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/88.jpg)
88
Comparison of various distributed (Web Services and ancestors) technologies
SOAPORB/CDR.NET object serialization
Java object serialization
Message exchange format
HTTP, HTTPS, SMTP, FTP
GIOP/IIOPbinary or OAPRMI-IIOPTransport protocol
XML data IDL-specified objects
.NET objects Java objects (binary)
Data exchange format
WSDL (XML-based)
CORBA IDL.NET Interfaces Java Interfaces Interface definition
independentindependent.NET languages (C#, VB.NET, ..)
Java Programming language
Web services
CORBA.NET Remoting
Java RMI
![Page 89: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/89.jpg)
89
Web services related .NET namespaces
• System.Web.Services– for developing Web services (e.g.: WebService, WebMethod)
• System.Web.Services.Configuration– for extending SOAP
• System.Web.Services.Description– for creating and manipulating WSDL descriptions
• System.Web.Services.Discovery– for using DISCO
• System.Web.Services.Protocols– for implementation of communication protocols (e.g. SOAP-
HTTP)• System.Xml.Serialization
– for XML serialization
![Page 90: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/90.jpg)
90
Example
![Page 91: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/91.jpg)
91
A .NET web service has a .asmx file associated with it. This is the equivalent of the .aspx file of ASP.net
applications.
![Page 92: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/92.jpg)
92
Running …
The operation (method) exposed by this web service
The URL of the web service
The WSDL (web service description
language)
![Page 93: ADO .NET (1) notes/Lecture Notes 3403 week 07...ADO .NET (ActiveX Data Objects) • ADO.NET is a component of .NET that allows access to ... private void button1_Click(object sender,](https://reader031.vdocuments.us/reader031/viewer/2022021511/5ac653877f8b9a220b8dd83e/html5/thumbnails/93.jpg)
93
The name of the Web Service.
The result of invoking method
“Hello World”