ado| data
8.1 Create a (master/Xiang) Table Association
1: <%@ Import namespace= "System.Data"%>
2: <%@ Import namespace= "System.Data.SqlClient"%>
3:
4: <%
5:dim MyConnection as SqlConnection
6:dim MyDataAdapter as SqlDataAdapter
7:dim myDataSet as DataSet
8:dim mydatatable as DataTable
9:dim Publisher as DataRow
10:dim Title as DataRow
11:
12:myconnection = New SqlConnection ("Server=localhost;uid=sa;pwd=secret;database=pubs")
13:mydataset = New DataSet ()
14:mydataadapter = New SqlDataAdapter ("select * From Publishers",
Ccc.gifmyconnection)
15:mydataadapter.fill (myDataSet, "publishers")
16:mydataadapter.selectcommand = New SqlCommand ("SELECT * from Titles",
Ccc.gifmyconnection)
17:mydataadapter.fill (myDataSet, "Titles")
18:
19:mydataset.relations.add ("Pubtitles",
Ccc.gifmyDataSet.Tables ("publishers"). Columns ("pub_id"),
Ccc.gifmyDataSet.Tables ("Titles"). Columns ("pub_id"))
20:
21:for each Publisher in Mydataset.tables ("publishers"). Rows
22:response.write ("<p>" & Publisher ("Pub_name") & ":")
23:for each Title in Publisher.getchildrows ("Pubtitles")
24:response.write ("<li>" & Title ("title"))
25:next
26:next
27:
:%>
29:
9.1 Using DataAdapter Update mode
1: <%@ Import namespace= "System.Data"%>
2: <%@ Import namespace= "System.Data.SqlClient"%>
3:
4: <%
5:dim MyConnection as SqlConnection
6:dim MyDataAdapter as SqlDataAdapter
7:dim Mybuilder as SqlCommandBuilder
8:dim myDataSet as DataSet
9:dim mydatatable as DataTable
10:dim Author as DataRow
11:
The "Create" DataSet and DataAdapter
13:myconnection = New SqlConnection ("Server=localhost;uid=sa;pwd=secret;database=pubs")
14:mydataset = New DataSet ()
15:mydataadapter = New SqlDataAdapter ("select * from Author", MyConnection)
16:mydataadapter.fill (myDataSet, "Authors")
17:
' Change value of ' A-a-row
19:mydataset.tables ("Authors"). Rows (0). Item ("au_fname") = "Jane"
20:
: ' Update the Database Table
22:mybuilder = New SqlCommandBuilder (mydataadapter)
23:mydataadapter.update (myDataSet, "Authors")
24:
: ' Display the Records
26:for each Author in Mydataset.tables ("Authors"). Rows
27:response.write ("<p>" & Author ("au_fname") & ""
ccc.gif& Author ("au_lname"))
28:next
:%>
30:
9.2 Using DataAdapter Update mode (C #)
1: <%@ Page language= "C #"%>
2: <%@ Import namespace= "System.Data"%>
3: <%@ Import namespace= "System.Data.SqlClient"%>
4:
5: <%
6://Create the DataSet and DataAdapter
7:sqlconnection myconnection = new
Ccc.gifsqlconnection ("Server=localhost;uid=sa;pwd=secret;database=pubs");
8:dataset myDataSet = new DataSet ();
9:sqldataadapter MyDataAdapter = New SqlDataAdapter (
Ccc.gif "SELECT * from Authors3", MyConnection);
10:mydataadapter.fill (myDataSet, "Authors");
11:
(a)//change value of the A-row
13:mydataset.tables["Authors"]. rows[0] ["au_fname"] = "Jane";
14:
://Update the Database Table
16:sqlcommandbuilder Mybuilder = new SqlCommandBuilder (MyDataAdapter);
17:mydataadapter.update (myDataSet, "Authors");
18:
Records//Display the "
20:foreach (DataRow Author in mydataset.tables["Authors"). Rows)
21: {
22:response.write ("<p>" + author["au_fname"] + ""
ccc.gif+ author["au_lname"]);
23:}
%>:
25:
