修改记录

Size: px

Start display at page:

Download "修改记录"

送馨荀
8 years ago
Views:

1 ASP.NET ADO.NET ASP ADO ADO ADO.NET Managed Provider (API) OLEDB ODBC ADO.NET Managed Provider DataSet Managed Provider ADO ADO.NET ADO.NET Managed Provider Managed Provider DataSet MS SQL Managed Provider Connection Command Parameter DataSet DataSetCommand DataSet IdataReader DataSetCommand ADO.NET

2 DataSet DataSet DataSetCommand SQL Command SelectCommand InsertCommand UpdateCommand DeleteCommand Managed Provider OLEDB COM Interop OLEDB Managed Provider DataSet DataSet ADO.NET DataSet DataSet DataSet TablesCollection DataSet (Table) DataTable DataSet DataTable DataTable TablesCollection DataTable System.Data (Table)

3 ColumnsCollection RowsCollection DataTable DataTable DataTable RelationsCollection DataTable DataRelation DataRelation RelationsCollection DataSet DataRelation - DataTable DataRelation ASP DataShaping ExtendedProperties asp.net Managed Provider DataSet asp.net DataSet DataSet Managed Provider ADO.NET ADO.NET ADO.NET ADO ASP ADO 1 ADO.Connection 2 SQL Recordset 3 4 ADO.NET ADO.NET DataSet DataSet

4 DataSet ASP (Conne Recordset) DataSet DataSet ADO.NET DataSet 2 DataSet DataSet DataSet XML XML DataSet ADO.NET ADOConnection ADO.NET ADOConnection ADODB.Connection ADOConnection ADO.NET NameSpace System.Data System.Data.ADO ASP.NET Import <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.ADO" %> ADO Connection ADOConnection Open Close MS SQL Server Pubs <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.ADO" %> <% '... Dim strconnstring as String strconnstring = "Provider=SQLOLEDB; Data Source=(local); " & _ "Initial Catalog=pubs; User ID=sa" ' ADOConnection Dim objconn as ADOConnection objconn = New ADOConnection ' ADOCOnnection objconn.connectionstring = strconnstring

5 objconn.open() ' ' objconn.close() ' objconn = Nothing ' %> ADO ADO.NET ADO SQL ADO ADODatasetCommand ADO.NET ADODatasetCommand DataSet ADO.NET Dataview DataSet DataSet Dataview DataSet ADODatasetCommand DataSet ' SQL Dim strsql as String = "SELECT * FROM authors" ' ADODatasetCommand Dataset Dim objdscommand as ADODatasetCommand Dim objdataset as Dataset = New Dataset objdscommand = New ADODatasetCommand(strSQL, objconn) ' Dataset ' "Author Information" objdscommand.filldataset(objdataset, "Author Information") Dataset Dataset ASP.NET DataSet DataGrid ASP.NET HTML <asp:datagrid id="datagridname" runat="server"/> Dataset DataGrid ASP.NET Dataview DataGrid Dataset

6 Dataset Dataview DataGrid MyFirstDataGrid.DataSource = _ objdataset.tables("author Information").DefaultView MyFirstDataGrid.DataBind() (code\ aspx) <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.ADO" %> <% '... Dim strconnstring as String strconnstring = "Provider=SQLOLEDB; Data Source=(local); " & _ "Initial Catalog=pubs; User ID=sa" ' ADOConnection Dim objconn as ADOConnection objconn = New ADOConnection ' ADOCOnnection objconn.connectionstring = strconnstring objconn.open() ' ' SQL Dim strsql as String = "SELECT * FROM authors" ' ADODatasetCommand Dataset Dim objdscommand as ADODatasetCommand Dim objdataset as Dataset = New Dataset objdscommand = New ADODatasetCommand(strSQL, objconn) ' Dataset ' "Author Information" objdscommand.filldataset(objdataset, "Author Information") objconn.close() ' objconn = Nothing '

7 Authors.DataSource = _ Authors.DataBind() objdataset.tables("author Information").DefaultView %> <HTML> <BODY> <asp:datagrid id="authors" runat="server"/> </BODY> </HTML> ADO.NET pubs authors DataSet DataGrid DataSet

8 ADO.NET Web ASPX IP ASPX Application( strconn ) global.asa Application DbOpen ASP DbOpen ASP ASP.NET ASP.NET ASP.NET ASP.NET ASP ASP.NET Application config.web config.web (config.web) <configuration> <appsettings> <add key="strconn" value="server=localhost;uid=sa;pwd=;database=pubs"/> </appsettings> </configuration> aspx Dim MyConnection As SQLConnection Dim Config as HashTable config.web appsettings Config = Context.GetConfig("appsettings") Config MyConnection = New SQLConnection(Config("MyConn")) config.web

3.3.2 ASP.NET ADO.NET SQL Managed Provider ASP.NET 1 ADO.NET Managed Provider 2 SQL Managed Provider ODBC OLEDB MS SQL Server MS SQL Server 3.3.2.1 ADO.NET Managed Provider ODBC MS SQL Server pubs DSN >> >>(ODBC)>> MS SQL Server pubs <%@ Import Namespace="System.

9 3.3.2 ASP.NET ADO.NET SQL Managed Provider ASP.NET 1 ADO.NET Managed Provider 2 SQL Managed Provider ODBC OLEDB MS SQL Server MS SQL Server ADO.NET Managed Provider ODBC MS SQL Server pubs DSN >> >>(ODBC)>> MS SQL Server pubs <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.ADO" %> <script language= VB RunAt= Server > ' ADOConnection Dim objconn as ADOConnection=New ADOConnection( DSN=pubs )

10 objconn.open() ' </script> Import ADO.NET Namespace ADO.NET Managed Provider+ODBC MS SQL Server Access Excel mysql Oracle Page Language="vb" %> Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.ADO" %> <html> <head> <script runat=server> Sub Page_Load(ByVal Sender As Object, ByVal e As EventArgs) On Error Resume Next Dim cn As ADOConnection cn = New ADOConnection("DSN=NWind") cn.open() If cn.state = 1 Then lblreturncode.text = "The Connection State is: " & cn.state & " - Connection Succeeded" Else lblreturncode.text = "The Connection State is: " & cn.state & " - Connection Failed" End If End Sub </script> </head> <body> <asp:label id="lblreturncode" Runat=server /> </body> </html> ADO.NET Managed Provider OLEDB OLEDB Access

11 Import Namespace="System.Data" %> Import Namespace="System.Data.ADO" %> <script language= VB RunAt= Server > Dim cn As ADOConnection cn = New ADOConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;") cn.open() </script> MS SQL Server <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.ADO" %> <script language= VB RunAt= Server > Dim cn As ADOConnection cn = New ADOConnection("Provider=SQLOLEDB.1;Data Source=(local);uid=sa;pwd=;Initial Catalog=pubs ) cn.open() </script> ADO.NET OLEDB OLEDB SQLOLEDB SQL OLE DB Provider MSDAORA Oracle OLE DB Provider JOLT Jet OLE DB Provider MSDASQL/SQLServer ODBC SQL Server ODBC Driver via OLE DB for ODBC Provider MSDASQL/Jet ODBC Jet ODBC Driver via OLE DB Provider for ODBC Provider ( thado.htm ) <%@ Page Language="vb" %> <%@ Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.ADO" %> <html> <head> <script runat=server>

12 Sub Page_Load(ByVal Sender As Object, ByVal e As EventArgs) On Error Resume Next Dim cn As ADOConnection cn = New ADOConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;") cn.open() If cn.state = 1 Then lblreturncode.text = "The Connection State is: " & cn.state & " - Connection Succeeded" Else lblreturncode.text = "The Connection State is: " & cn.state & " - Connection Failed" End If End Sub </script> </head> <body> <asp:label id="lblreturncode" Runat=server /> </body> </html> SQL Managed Provider Microsoft SQL Server SQL Managed Provider MS SQL Server <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQL" %> <script language= VB RunAt= Server > Dim objconn as SQLConnection = New ADOConnection("server=localhost;uid=sa;pwd=;database=pubs;") objconn.open() ' </script> 1 Import ADO.NET Managed Provider Import System.Data.ADO System.Data.SQL 2 ADO.NET Managed Provider ADO SQL

13 ADO.NET Managed Provider ADO.NET SQL Managed Provider System.Data.ADO System.Data.SQL Namespace Connection ADOConnection SQLConnection Command ADODatasetCommand SQLDatasetCommand Dataset Dataset Dataset DataReader ADODataReader SQLDataReader String sconnectionstring = "Provider= SQLOLEDB.1; Data Source=localhost; uid=sa; pwd=; Initial Catalog=pubs"; ADOConnection con = new ADOConnection(sConnectionStri ng); con.open(); SQL ADOCommand cmd = new ADOCommand("SELECT * FROM Authors", con); ADODataReader dr = new ADODataReader(); cmd.execute(out dr); ADOCommand cmd = new ADOCommand ("spgetauthorbyid", con); cmd.commandtype = CommandType.StoredProcedure; String sconnectionstring = "server=localhost;uid=sa;pwd=;database =pubs"; SQLConnection con = new SQLConnection(sConnectionString); con.open(); SQLCommand cmd = new SQLCommand(("SELECT * FROM Authors", con); SQLDataReader dr = new SQLDataReader(); cmd.execute(out dr); SQLCommand cmd = new SQLCommand("spGetAuthorByID", con); cmd.commandtype = CommandType.StoredProcedure; ADOParameter prmid = new ADOParameter("AuthID", ADODataType.VarChar, 11); prmid.value = " "; cmd.selectcommand.parameters. Add(prmID); ADODataReader dr; cmd.execute (out dr); SQLParameter prmid = new SQLParameter("@AuthID", SQLDataType.VarChar,11); prmid.value = " " cmd.selectcommand.parameters.add(pr mid); SQLDataReader dr; cmd.execute(out dr); <%@ Page Language="vb" %> <%@ Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.SQL" %> <html> <head> <script runat=server> Sub Page_Load(ByVal Sender As Object, ByVal e As EventArgs) 'On Error Resume Next Dim cn As SQLConnection

14 cn = New SQLConnection("server=localhost;uid=sa;pwd=;database=pubs;") cn.open() If cn.state = 1 Then lblreturncode.text = "The Connection State is: " & cn.state & " - Connection Succeeded" Else lblreturncode.text = "The Connection State is: " & cn.state & " - Connection Failed" End If End Sub </script> </head> <body> <asp:label id="lblreturncode" Runat=server /> </body> </html> SQL Managed Provider ADO.NET Managed Provider+OLEDB ADO.NET Managed Provider+ODBC PIII Access 2000 MS SQL Server 2000 ( ) ADO.NET Managed Provider+ODBC ADO.NET Managed Provider+OLEDB SQL Managed Provider ADO.NET Managed Provider+ODB C ADO.NET Managed Provider+OLE DB SQL Managed Provider SQL Managed Provider ADO.NET Managed ProviderODBC OLEDB OLEDB ODBC

15 ( aspx) Page EnableSessionState="False" %> Import Namespace="System.Data" %> Import Namespace="System.Data.ADO" %> Import Namespace="System.Data.SQL" %> <script language="vb" runat="server"> Sub Refresh(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim d1,d2 As DateTime Dim strconn if Page.IsValid then d1=now() Dim ichoice As Integer=CInt(Choices.SelectedItem.Value) select case ichoice case 1 strconn="dsn=pubs;" ADOBindData(strConn) case 2 strconn="provider=sqloledb.1;data Source=(local);uid=sa;pwd=;Initial Catalog=pubs" ADOBindData(strConn) case 3 strconn="server=localhost;uid=sa;pwd=;database=pubs" '"server=localhost;uid=sa;pwd=;database=northwind;" SQLBindData(strConn) Case Else end select d2=now() result.text = " (Ticks) "&d2.ticks-d1.ticks end if End Sub Sub ADOBindData(strConn) '... ' ADOConnection

16 Dim objconn as ADOConnection = New ADOConnection(strConn) objconn.open() ' ' SQL Dim strsql as String = "SELECT * FROM authors" ' ADODatasetCommand Dataset Dim objdscommand as ADODatasetCommand Dim objdataset as Dataset = New Dataset objdscommand = New ADODatasetCommand(strSQL, objconn) ' Dataset ' "Author Information" objdscommand.filldataset(objdataset, "Author Information") objconn.close() ' objconn = Nothing ' Authors.DataSource = _ objdataset.tables("author Information").DefaultView Authors.DataBind() End Sub Sub SQLBindData(strConn) '... ' ADOConnection Dim objconn as SQLConnection = New SQLConnection(strConn) objconn.open() ' ' SQL Dim strsql as String = "SELECT * FROM authors" ' SQLDatasetCommand Dataset Dim objdscommand as SQLDatasetCommand Dim objdataset as Dataset = New Dataset objdscommand = New SQLDatasetCommand(strSQL, objconn) ' Dataset ' "Author Information" objdscommand.filldataset(objdataset, "Author Information")

17 objconn.close() ' objconn = Nothing ' Authors.DataSource = _ objdataset.tables("author Information").DefaultView Authors.DataBind() End Sub </script> <HTML> <BODY> <H2> </H2> <Form Action=" aspx" Method="Post" RunAt="Server"> <asp:radiobuttonlist ID="choices" RunAt="Server"> <asp:listitem selected Text="ADO.NET Managed Provider+ODBC" Value=1/><br> <asp:listitem Text="ADO.NET Managed Provider+OLEDB" Value=2/><br> <asp:listitem Text="SQL Managed Provider" Value=3/> </asp:radiobuttonlist> <br> <asp:linkbutton runat="server" OnClick="Refresh" Text=""/> <br> <H2> </H2> <asp:label id="result" RunAt="Server" Text="No result"/> <br> <H2> </H2> <asp:datagrid id="authors" runat="server"/> </Form> </BODY> </HTML> DataSets DataSets DataSets DataSets DataSets DataSets DataSets DataSets

18 DataSets DataSets 1. SQLDataSetCommand (SQL ) ADODataSetCommand (ADO ) DataSet Ado Dim MyDsComm As New ADODataSetCommand Dim MyComm As ADOCommand Dim MyConn As ADOConnection MyConn = New ADOConnection _ ("Provider=SQLOLEDB.1;Initial Catalog=Northwind;" & _ "Data Source=(local);User ID=sa;") MyComm = New ADOCommand("SELECT * FROM Customers", MyConn) MyDsComm.SelectCommand = MyComm SQL Dim MyConn as SQLConnection Dim MyComm as SQLDataSetCommand Dim MyDs as New DataSet MyConn=New SQLConnection( server=localhost;uid=sa;pwd=;database=pubs ) MyComm=New SQLDataSetCommand( Select * from authoers,myconn) MyComm.FillDataSet(Myds, authers ) 2 DataSet DataTable DataRow 3 GetChanges DataSet DataSet Dim changeddataset As DataSet changeddataset = ds.getchanges(datarowstate.modified) 4 DataSet HasErrors DataSet

19 5 DataSet DataTable HasErrors GetErrors DataRow 6 DataRow RowError 7 8 DataSet Merge DataSet DataSet ds.merge(changeddataset) 9 DataSetCommand update DataSet MyDataSetCommand.Update(ds) 10 DataSet AcceptChanges RejectChanges, Ds.AcceptChanges DataSet 1 DataSet() DataSet() Dim ds1 as New DataSet Dim ds2 as New DataSet( MyDataSet ) 2 DataTable DataSet DataSet Tables ds.tables.add(new DataTable()) Columns ds.tables( ).Columns.Add( ) ds (Order) ORNO ORNM ds.tables.add(new DataTable( Order )) ds.tables( Order ).Columns.Add( CUNM,GetType(String))

20 ds.tables( Order ).Columns.Add( ORNO,GetType(String)) ds.tables( Order ).Columns.Add( ORNM,GetType(int32)) ds.tables( Order ).PrimaryKey= New DataColumn(ds.Tables( Order ).Columns( ORNO ) PrimaryKey 3 DataSet DataTable asp.net DataRelation DataRelation (Order) CUNO Asp.net Dim ds as DataSet ds.relations.add( CustomerOrder,ds.Tables( Customer ).Columns( CUNO ), ds.tables( Order ).Columns( CUNO )) 4 DataRelaiton DataSet dim orderrows() as DataRow orderrows=ds.tables( Customer ).ChildRelations( CustomerOrder ).GetChildRows( ds.tables( Customer ).Rows(0)) 5 asp.net ForeignKeyConstr UniqueConstraint ForeignKeyConstraint ForeignKeyConstraint Cascade

21 SetNull Null SetDefault None Default ForeignKeyConstraint Cascade ForeigKeyConstraint DeleteRule UpDateRule dim fk as New ForeignKeyConstraint(ds.Tables( Customer ).Columns( CUNO ), ds.tables( Order.Columns( CUNO )) Customer Order CUNO fk.deleterule=cascade fk.updaterule=setdefault Cascade SetDefault ds.tables( Customer ).Constraints.Add(fk) Customer UniqueConstraint dim uc as UniqueConstraint uc=new UniqueConstraint(ds.Tables( Customer ).Columns( CUNO )) Customer CUNO ds.tables( Customer ).Constraints.Add(uc) Customer 6 DataSet DataSet asp.net DataSet PropertyChange MergeFailed DataSet RemoveTable RemoveRelation Adding the event handler to the event

22 ds.addonpropertychange(new System.ComponentModel.PropertyChangeEventHandler _ (AddressOf me.datasetpropertychange)) DataSet PropertyChange DataSetPropertyChange ds.addonmergefailed(new System.Data.MergeFailedEventHandler _ (AddressOf me.datasetmergefailed)) DataSet MergeFailed DataSetMergeFailed PropertyChange Private Sub DataSetPropertyChange _ (ByVal sender As Object, ByVal e As System.PropertyChangeEventArgs) End Sub MergeFailed Private Sub DataSetMergeFailed _ (ByVal sender As Object, ByVal e As System.Data.MergeFaileedEventArgs) End Sub DataTable DataTable DataSet DataSet 1 DataTable DataTable DataSets dim MyTable as DataTable MyTable = New DataTable("Test") MyTable.CaseSensitive = False MyTable.MinimumCapacity = 100 CaseSensitive CaseSensitive MinimumCapacity TableName 1 dim MyTable as DataTable MyTable=New DataTable( test )

23 2 dim MyTable as New DataTable MyTable.TableName= test 2 DataTable Columns Columns Add.net GetType.net Customer CUNM CUNO IDNO Dim MyTable as DataTable Dim MyColumn as DataColumn MyTable = new DataTable("Customer") MyColumn = MyTable.Columns.Add("CUNM",GetType("String") ) MyColumn = MyTable.Columns.Add("CUNO",GetType("String") ) MyColumn = MyTable.Columns.Add("IDNO",GetType("int32") ) 3 asp.net DataType Expression 0.2 total rate tax Dim tax As DataColumn = New DataColumn tax.datatype = GetType("Currency") tax.expression = "total *rate*0.20" MyTable.Columns.Add( tax,gettype( Currency ), total*rate*0.20)

24 4 asp.net DataColumn AutoIncrement AutoIncrementSeed AutoIncrementStep AutoIncrement AutoIncrementSeed AutoIncrementStep dim MyTable as New DataTable dim MyColumn as DataColumn MyColumn=MyTable.Columns.Add( Sqno,GetType( int32 )) MyColumn.AutoIncrement=True MyColumn.AutoIncrementSeed=0 MyColumn.AutoIncrementStep=2 5 DataColumn AllowNull Unique DataColumn1.AllowNull=False DataColumn1.Unique=True DataTable PrimaryKey dim MyColumn as DataColumn dim MyTable as DataTable MyColumn=MyTable.Columns( CUNO ) MyColumn.AllowNull=False MyColumn.Unique=True MyTable.PrimaryKey=MyColumn dim MyColumn as DataColumn() MyColumn(0)=MyTable.Columns( col1 ) MyColumn(1)=MyTable.Columns( col2 )

25 MyTable.PrimaryKey=MyColumn DataRow DataRow Columns DataRow DataTable DataRows MyTable Sqno Name Sqno Name MyName + n MyTable Dim i as integer Dim n as integer Dim MyRow as DataRow For i = 0 to n MyRow = MyTable.NewRow() MyRow("Sqno") = I sqno MyRow("Name") = "MyName" & i.tostring() name MyTable.Rows.Add(MyRow) Next DataTable Rows Remove Delete MyTable MyTable.Rows.Remove(3) MyTable.Rows(3).Delete Delete Remove Remove DataRow Rows Delete DataRow Rows DataSet AcceptChanges RejectChanges Delete DataRow

26 DataTable Row, Original Current Preposed Original Original Row Preposed original Current Original Current Preposed, worktable Dim CurrRows() As DataRow = worktable.select(nothing, Nothing, _ System.Data.DataViewRowState.CurrentRows) worktable DataRows CurrRows Dim list As String = System.String.Empty list Dim RowNo As Integer Dim ColNo As Integer For RowNo = 0 to CurrRows.Count 1 For ColNo = 0 To worktable.columns.count 1 list = "" list &= worktable.columns(colno).columnname & " = " & _ CurrRows(RowNo)(ColNo).ToString Console.WriteLine(list) Next Next If CurrRows.Count < 1 Then Console.WriteLine("No CurrentRows Found") Rows DataTable Select Rows Rows.count columns.count DataReader

27 Read Recordset Dataset ADO.NET DataReader namespace="system.data.sql"%> <script language="vb" runat="server"> Sub Page_Load(sender As Object, e As EventArgs) Dim sqltext as String = "select * from authors" Dim cnstring as string = "server=localhost;uid=sa;pwd=;database=pubs;" Dim dbread AS SQLDataReader Dim sqlcmd AS SQLCommand sqlcmd = New SQLCommand(sqlText,cnString) sqlcmd.activeconnection.open() sqlcmd.execute(dbread) while dbread.read() response.write("<br>" & dbread.item("au_lname")) End while End Sub </script> Recordset MoveNext Recordset EOF Recordset MoveNext ADO.NET DataReder Read Iterator SQLConnection SQLCommand sqlcmd.activeconnection.open()

3.3.4.2 Read DataReader <%@import namespace="system.data.sql"%> <SCRIPT LANGUAGE="vb" RUNAT="server"> Sub Page_Load(myList AS Object,E as EventArgs) If Not Page.

28 Read DataReader namespace="system.data.sql"%> <SCRIPT LANGUAGE="vb" RUNAT="server"> Sub Page_Load(myList AS Object,E as EventArgs) If Not Page.IsPostBack() Dim dbread AS SQLDataReader Dim dbcomm AS SQLCommand Dim strconn AS String Dim SQL AS String strconn = "server=sql.database.com;uid=fooman;password=foopwd;" SQL = "Select * from Color ORDER BY Color" dbcomm = New SQLCommand(SQL,strConn) dbcomm.activeconnection.open() dbcomm.execute(dbread) While dbread.read() ShirtColorOptions.items.add(New ListItem(dbRead.Item("Color"))) End While SQL = "Select * from Size ORDER BY Size"

29 dbcomm = New SQLCommand(SQL,strConn) dbcomm.activeconnection.open() dbcomm.execute(dbread) While dbread.read() ShirtSizeOptions.items.add(New ListItem(dbRead.Item("Size"))) End While End IF End Sub </SCRIPT> <FORM RUNAT="server" method="get"> <asp:dropdownlist id="shirtcoloroptions" runat="server" DataTextField = "URL"/> <asp:dropdownlist id="shirtsizeoptions" runat="server" DataTextField = "Size"/> </FORM> DataReader DataGrid NET Framework Beta2 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQL" %> <% Dim myconn As SQLConnection = new SQLConnection("server=localhost;uid=sa;pwd=;database=NorthWind;") Dim mycommand As SQLCommand = new SQLCommand("select * from cusotmers",myconn) myconn.open() cusotmers.datasource = mycommand.execute() cusotmers.databind() myconn.close() %> <HTML> <BODY> <asp:datagrid id="cusotmers" runat="server"/>

30 </BODY> </HTML> DataReader <SCRIPT LANGUAGE="vb" RUNAT="server"> Sub showdb() namespace="system.data.sql"%> Dim dbread AS SQLDataReader Dim dbcomm AS SQLCommand Dim strconn AS String Dim strsql AS String strconn= "server=my.sql.database;uid=fooname;password=foofoo;" strsql = "INSERT INTO mydatabase (dbvalue1) VALUES('the thing')" dbcomm = New SQLCommand(strSQL, strconn) dbcomm.activeconnection.open() dbcomm.execute(dbread) End Sub </SCRIPT> ADO.NET ADO.NET 1 ODBC 2 OLEDB 3 SQL Server DataSet DataReader

31 ADO.NET SQLCommand SQL Insert Into ( 1 2) 1 Values ( 2) SQLCommand SQL SQL SQLCommand CommandText ExecuteNonQuery Dim strconn As String Dim strcomm As String Dim oconn As SQLConnection Dim ocomm As SQLCommand strconn = "server=localhost;uid=sa;pwd=;database=northwind" strcomm = "INSERT INTO CUSTOMERS (CustomerId, CompanyName, Contactname, ContactTitle, Address) Values ('DarkMan','Sino-ASP.COM', 'Mr. Li', 'CTO','')" oconn = new SQLConnection(strConn) ocomm = new SQLCommand(strComm, oconn) Try oconn.open() ocomm.executenonquery() Catch myexception as Exception // Finally oconn.close() End Try

32 SQLDataSetCommand SQLDataSetCommand Dim strconn,strcomm As String // strconn="server=localhost;uid=sa;pwd=;database=northwind" // strcomm="select * from region" Dim oconn As New SQLConnection(strConn) Dim odscomm As New SQLDataSetCommand(strComm,oConn) Dim oparam As SQLParameter odscomm.insertcommand = new SQLCommand("Insert into Region (RegionID, RegionDescription) VALUES oconn) oparam = odscomm.insertcommand.parameters.add(new SQLParameter("@RegionID", SQLDataType.Int)) oparam.sourceversion = DataRowVersion.Current oparam.sourcecolumn = "RegionID" oparam = odscomm.insertcommand.parameters.add(new SQLParameter("@RegionDescription", SQLDataType.NChar, 50)) oparam.sourceversion = DataRowVersion.Current oparam.sourcecolumn = "RegionDescription" Dim ods as New DataSet RegionID odscomm.missingschemaaction = MissingSchemaAction.AddWithKey odscomm.filldataset(ods, "Region") Dim index Dim orow as DataRow for index=0 to 20 orow = ods.tables("region").newrow() orow (0) = CStr(index) orow (1) = ""+CStr(index) ods.tables("region").rows.add(newrow)

33 next Try odscomm.update(ods,"region") Catch oexception As Exception // Finally oconn.close End Try sql UPDATE FORUM SET Notes= where [ID]=1 FORUM ID=1 Notes.NET? aspx code\database\update.aspx Import Namespace="System.Data" %> Import Namespace="System.Data.SQL" %> <html> <script language="vb" runat="server"> ' Dim UConn As SQLConnection ' Sub Page_Load(Sender As Object, E As EventArgs) ' UConn = New SQLConnection("server=localhost;uid=NetBBS;pwd=;database=NETBBS") If Not (IsPostBack)

34 BindGrid() End If End Sub ' Edit Sub UDG_Edit(Sender As Object, E As DataGridCommandEventArgs) UDG.EditItemIndex = CInt(E.Item.ItemIndex) BindGrid() End Sub ' Cancle Sub UDG_Cancel(Sender As Object, E As DataGridCommandEventArgs) UDG.EditItemIndex = -1 BindGrid() End Sub ' UpDate Sub UDG_Update(Sender As Object, E As DataGridCommandEventArgs) ' Dim DS As DataSet ' Dim UComm As SQLCommand ' sql Dim UpdateCmd As String = "UPDATE FORUM SET [ID]=@fid,[Name]=@fname,Notes=@Notes,FatherID=@FatherID,status=@status [ID]=@fid" where ' UComm = New SQLCommand(UpdateCmd, UConn) 4)) ' UComm.Parameters.Add(New SQLParameter("@fid", SQLDataType.VarChar, 4)) UComm.Parameters.Add(New SQLParameter("@fname", SQLDataType.VarChar, 50)) UComm.Parameters.Add(New SQLParameter("@Notes", SQLDataType.VarChar, 500)) UComm.Parameters.Add(New SQLParameter("@FatherID", SQLDataType.VarChar, UComm.Parameters.Add(New SQLParameter("@status", SQLDataType.VarChar, 1))

35 ' Dim Cols As String() = {"@fid","@fname","@notes","@fatherid","@status"} ' UComm.ActiveConnection.Open() Try ' UComm.ExecuteNonQuery() Message.InnerHtml = "" ' Edit UDG.EditItemIndex = -1 ' Catch Exp As SQLException If Exp.Number = 2627 Message.InnerHtml = "" Else Message.InnerHtml = "" End If Message.Style("color") = "red" End Try ' UComm.ActiveConnection.Close() ' BindGrid() BindGrid() End Sub ' BindGrid() Sub BindGrid() Dim DS As DataSet Dim UComm As SQLDataSetCommand ' forum UComm = new SQLDataSetCommand("select * from forum", UConn) ' DS = new DataSet()

36 UComm.FillDataSet(DS, "forum") ' UDG.DataSource=DS.Tables("forum").DefaultView UDG.DataBind() End Sub </script> <title> Update! </title> <body style="font: 10pt verdana"> <BR> <CENTER> <form runat="server"> <h3><font face="verdana">.net-></font></h3> <span id="message" MaintainState="false" style="font: arial 11pt;" runat="server"/><p>  <ASP:DataGrid id="udg" runat="server" Width="800" BackColor="#ffffff" BorderColor="black" ShowFooter="false" CellPadding=3 CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#ffffff" OnEditCommand="UDG_Edit" OnCancelCommand="UDG_Cancel" OnUpdateCommand="UDG_Update" > <property name="columns"> <asp:editcommandcolumn EditText="" CancelText=" " UpdateText="" ItemStyle-Wrap="false"/> </property> </ASP:DataGrid>

37 </form> </CENTER> </body> </html>

38 3.4.3 NetBBS forum delete from forum where [id]=11 id=11 aspx sql ID forum ID ' ID SComm.Parameters.Add(New SQLDataType.VarChar, 4)) = SDG DataKeys(CInt(E.Item.ItemIndex)) ID ID sql Try SComm.ExecuteNonQuery()

39 Message.InnerHtml = " " & DeleteCmd Catch Exp As SQLException Message.InnerHtml = " " Message.Style("color") = "red" End Try SComm.ExecuteNonQuery() code\database\del.aspx <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQL" %> <html> <script language="vb" runat="server"> ' Dim SConn As SQLConnection ' Sub Page_Load(Src As Object, E As EventArgs) ' SConn = New SQLConnection("server=localhost;uid=NetBBS;pwd=;database=NETBBS") If Not (IsPostBack) BindGrid() End If End Sub ' Sub SDel(Sender As Object, E As DataGridCommandEventArgs) ' Dim SComm As SQLCommand Dim DeleteCmd As String = "DELETE from FORUM where [ID] SComm = New SQLCommand(DeleteCmd, SConn) ' ID SComm.Parameters.Add(New SQLParameter("@fid", SQLDataType.VarChar, 4)) SComm.Parameters("@fid").Value = SDG.DataKeys(CInt(E.Item.ItemIndex))

40 ' SComm.ActiveConnection.Open() Try SComm.ExecuteNonQuery() Message.InnerHtml = " " & DeleteCmd Catch Exp As SQLException Message.InnerHtml = " " Message.Style("color") = "red" End Try ' SComm.ActiveConnection.Close() BindGrid() End Sub ': Sub BindGrid() ' Dim DS As DataSet Dim SComm As SQLDataSetCommand SComm = New SQLDataSetCommand("select * from FORUM", SConn) ' DS = new DataSet() SComm.FillDataSet(DS, "FORUM") ' SDG.DataSource=DS.Tables("FORUM").DefaultView SDG.DataBind() End Sub </script> <title> Delete!! <title> <body style="font: 10pt verdana"> <br><br><br> <center> <form runat="server">

41 <h3><font face="verdana">.net-> </font></h3><br><br> <span id="message" MaintainState="false" style="font: arial 11pt;" runat="server"/><p> <ASP:DataGrid id="sdg" runat="server" Width="800" BackColor="#ffffff" BorderColor="black" ShowFooter="false" CellPadding=3 CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#ffffff" DataKeyField="ID" OnDeleteCommand="SDel" > <property name="columns"> <asp:buttoncolumn Text="" CommandName="Delete"/> </property> </ASP:DataGrid> </form> </center> </body> </html>

42 ID=12 sql

43 3.4.4 SQL Server SQL aspx aspx SQL SQL SQL SQL SQL SQL CREATE PROCEDURE CREATE PROCEDURE CREATE PROCEDURE pro_book AS SELECT * FROM forum pro_book SQL SQL AS SQL forum EXECUTE pro_book

44 EXECUTE pro_book SQL forum EXECUTE ISQL/W pro_book NetBBS pro_book NetBBS2 pro_book NetBBS2 pro_book EXECUTE NetBBS.. pro_book sp_helptext SQL sp_helptext pro_book text CREATE PROCEDURE pro_book AS SELECT * FROM forum sp_helptext Master sp_helptext sp_helptext sp_helptext SQL DROP PROCEDURE pro_book DROP PROCEDURE pro_book sp_help sp_help sp_help sp_help SQL Enterprise Manager

45 .NET NetBBS create procedure pro_book as select * from forum pro_book forum StorePro.aspx <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQL" %> <html> <script language="vb" runat="server"> Sub Page_Load(Src As Object, E As EventArgs) ' Dim DS As DataSet ' Dim SConn As SQLConnection ' Dim SComm As SQLDataSetCommand ' Sconn=New SQLConnection("server=localhost;uid=NetBBS;pwd=;database=NETBBS") SComm = New SQLDataSetCommand("pro_book", SConn) ' SComm.SelectCommand.CommandType = CommandType.StoredProcedure ' DS = new DataSet() SComm.FillDataSet(DS, "forum") SDG.DataSource=DS.Tables("forum").DefaultView SDG.DataBind() End Sub

46 </script> <body> <center> <h3><font face="verdana"> <ASP:DataGrid id="sdg" runat="server" Width="360" BackColor="#ccccff" BorderColor="black" ShowFooter="false" CellPadding=3 CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" MaintainState="false" /> </center> </body> </html> SComm = New SQLDataSetCommand("pro_book", SConn) ' SComm.SelectCommand.CommandType = CommandType.StoredProcedure pro_book NetBBS aspx aspx

3.4.4.2 aspx @fname create procedure pro_outbook as ( @fid varchar(20), @fname

47 create procedure pro_outbook as varchar(1000) out ) [name] from forum ) select forum name

48 EXECUTE OUT VARCHAR(1000) varchar(20) EXECUTE pro_outbook OUT NET NetBBS create procedure varchar(4) as select [id] as 'ID',[name] as ' ',[notes] as ' ',[fatherid] as ' ID',status as '' from forum where pro_inputbook id name [] SQL Server aspx code\database\storeprowithinpara.aspx Import Namespace="System.Data" %> Import Namespace="System.Data.SQL" %> <html> <script language="vb" runat="server"> ' Sub Page_Load(Src As Object, E As EventArgs) ' Dim DS As DataSet

49 ' Dim IConn As SQLConnection ' Dim IComm As SQLDataSetCommand ' IConn=New SQLConnection("server=localhost;uid=NetBBS;pwd=;database=NETBBS") ' IComm = New SQLDataSetCommand("pro_inputbook", IConn) ' IComm.SelectCommand.CommandType = CommandType.StoredProcedure ' IComm.SelectCommand.Parameters.Add(New SQLParameter("@fid", SQLDataType.NVarChar, 15)) ' IComm.SelectCommand.Parameters("@fid").Value = Request.QueryString("forumid") ' DS = new DataSet() IComm.FillDataSet(DS, "forum") ' IDG.DataSource=DS.Tables("forum").DefaultView IDG.DataBind() End Sub </script> <body style="font: 10pt verdana"> <br><br><br> <center> <br><br><br> <ASP:DataGrid id="idg" runat="server" Width="650" BackColor="#ccccff" BorderColor="black"

50 ShowFooter="false" CellPadding=3 CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" MaintainState="false" /> </center> </body> </html> 1 ' IComm.SelectCommand.Parameters("@fid").Value = Request.QueryString("forumid") (forumid=1) ' IComm.SelectCommand.Parameters.Add(New SQLParameter("@fid", SQLDataType.NVarChar, 15)) pro_inputbook 1 ID=1

51 create procedure int output as (select count(*) from topic) code\database\storeprowithoutpara.aspx IComm = New SQLDataSetCommand("pro_outpara", IConn) IComm.SelectCommand.CommandType = CommandType.StoredProcedure IComm.SelectCommand.Parameters.Add(New SQLParameter("@title", SQLDataType.Int)) IComm.SelectCommand.Parameters("@title").Direction = ParameterDirection.Output System.Math.Ceil(IComm.SelectCommand.Parameters("@count").Value) <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQL" %> <html> <script language="vb" runat="server"> Sub Page_Load(Src As Object, E As EventArgs) ' Dim IConn As SQLConnection

52 ' Dim IComm As SQLDataSetCommand ' Dim DS As DataSet IConn = New SQLConnection("server=localhost;uid=sa;pwd=;database=NETBBS") IComm = New SQLDataSetCommand("pro_outpara", IConn) ' IComm.SelectCommand.CommandType = CommandType.StoredProcedure ' IComm.SelectCommand.Parameters.Add(New SQLParameter("@count", SQLDataType.Int)) IComm.SelectCommand.Parameters("@count").Direction = ParameterDirection.Output ' DS = new DataSet() IComm.FillDataSet(DS, "topic") ' 'IDG.DataSource=DS.Tables("topic").DefaultView 'IDG.DataBind() If Not Page.IsPostBack Then TotalPages.Text = System.Math.Ceil(IComm.SelectCommand.Parameters("@count").Value ) End If End Sub </script> <body bgcolor="#ccccff" style="font: 10pt verdana"> <br><br><br> <center>.net-> <br><br><br> <asp:label id="totalpages" runat="server" />

53 </center> </body> </html> Grid BBS ID ID ID ID ID Request.QueryString("id")

54 ID sql aspx code\database\rel01.aspx Import Namespace="System.Data" %> Import Namespace="System.Data.SQL" %> <html> <script language="vb" runat="server"> ' Sub Page_Load(Src As Object, E As EventArgs) ' Dim DS As DataSet ' Dim rconn As SQLConnection ' Dim rcomm As SQLDataSetCommand ' rconn = New SQLConnection("server=localhost;uid=NetBBS;pwd=;database=NETBBS") rcomm = New SQLDataSetCommand("select UserID,ForumID AS ' ID', title as ' ',contents as '',nview ',nreply as ' ' as ' from topic", rconn) ' DS = new DataSet() rcomm.filldataset(ds, "topic") ' rdg.datasource=ds.tables("topic").defaultview rdg.databind() End Sub </script> <title>

55 Relation!!! </title> <body style="font: 10pt verdana"> <center> <br> <form runat="server"> <h3><font face="verdana">.net-> </font></h3> <span id="message" MaintainState="false" style="font: arial 11pt;" runat="server"/><p> <ASP:DataGrid id="rdg" runat="server" Width="800" BackColor="#ffffff" BorderColor="black" ShowFooter="false" CellPadding=3 CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" DataField="UserID" > <property name="columns"> <asp:hyperlinkcolumn DataNavigateUrlField="UserID" DataNavigateUrlFormatString="Rel02.aspx?id={0}" Text=" " /> </property> </ASP:DataGrid> </form> </center> </body> </html> code\database\rel02.aspx sql sql

56 Import Namespace="System.Data" %> Import Namespace="System.Data.SQL" %> <html> <script language="vb" runat="server"> ' Sub Page_Load(Src As Object, E As EventArgs) ' Dim DS As DataSet ' Dim rconn As SQLConnection ' Dim rcomm As SQLDataSetCommand Dim SelectCmd As String = "select [ID],education, area, salary from [user] where [ID]=@uid" ' rconn = New SQLConnection("server=localhost;uid=NetBBS;pwd=;database=NETBBS") rcomm = New SQLDataSetCommand(SelectCmd, rconn) ' ID rcomm.selectcommand.parameters.add(new SQLParameter("@uid", SQLDataType.VarChar, 11)) rcomm.selectcommand.parameters("@uid").value = Request.QueryString("id") ' DS = new DataSet() rcomm.filldataset(ds, "user") ' rdg.datasource=ds.tables("user").defaultview

57 rdg.databind() End Sub </script> <title> Relation!!! </title> <center> <body style="font: 10pt verdana"> <form runat="server"> <h3><font face="verdana">.net-> </font></h3> <h4><font face="verdana"> <%=Request.QueryString("id")%> </font></h4> <ASP:DataGrid id="rdg" runat="server" Width="800" BackColor="#ffffff" BorderColor="black" ShowFooter="false" CellPadding=3 CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#ffffff" /> </form> </center> </body> </html>

58 3.4.6 sql area select * from area update area(address) values( ) select sql server begin {transaction tran}[transaction_name] sql server

59 3.4.7 ADO.NET Dataset Dataset Recordset D Recordset DataReader Dataset Dataset Table Dataset Table Dataset 1 SQLConnection con =new SQLConnection("server=localhost;uid=sa;pwd=;database=pubs"); 2 DataSetCommand SQL SQLDataSetCommand cmd =new SQLDataSetCommand("SELECT * FROM Authors", con); 3 Dataset DataSet ds = new DataSet();

60 4 DataSetCommand FillData Dataset FillData FillData FillData int irowcount = cmd.filldataset(ds, Authors ); 5 FillData Dataset for(int i=0; i< irowcount; i++){ DataRow dr = ds.tables[0].rows[i]; Console.WriteLine(dr["au_lname"]); } DataSet ASP.NET Repeater DataList and DataGrid HTML Repeater DataList DataGrid System.Web.UI.WebControls (Namespace) HTML (list-bound controls) Web HTML

61 HTML Web DataSource Repeater DataList DataGrid System.Collections.Icollection DataSource DataSource ASP.NET DataSource System.Data.DataView ArrayList HashTable ADO Recordset ICollection DataSource DataSource 1 SQL Server pubs Repeater DataList DataGrid (DataView) (DataSource) DropDownList 3 1   <%@ import namespace="system.data" %> <%@ import namespace="system.data.sql" %>  <html> <script language="vb" runat=server>

62 sub Page_Load(o as object,e as eventargs) dim MyConnection as SQLConnection dim MyStr as String dim MyDataSetCommand as SQLDataSetCommand dim MyDataSet as New DataSet If Not IsPostBack MyConnection=New SQLConnection("server=localhost;uid=sa;pwd=;database=pubs") ' MyStr="Select au_lname,au_fname from authors" ' author MyDataSetCommand=New SQLDataSetCommand(Mystr,MyConnection) MyDataSetCommand.FillDataSet(MyDataSet,"Authors") ' DataSet, Authors Session("MyDs")=MyDataSet ' DataSet MyDs Else MyDataSet=Session("MyDs") ' DataSet if MyDataSet is Nothing Response.Write("") else ', Select Case DpDnLst.SelectedItem.text case "Repeater" Response.write _ ("<center> <I>Repeater</I> </center>") db1.datasource=mydataset.tables("authors").defaultview db1.databind panel1.visible=true panel2.visible=false panel3.visible=false case "DataList" Response.write _ ("<center> <B>DataList</B> </center>") db2.datasource=mydataset.tables("authors").defaultview db2.databind

63 panel1.visible=false panel2.visible=true panel3.visible=false case "DataGrid" Response.write _ ("<center> <U>DataGrid</U> </center>") db3.datasource=mydataset.tables("authors").defaultview db3.databind panel1.visible=false panel2.visible=false panel3.visible=true case else End Select end if End If end sub </script> <head> <title> </title> </head> <body bgcolor=#ffffff> <center> <h2>datasource </h2> <hr> <form runat=server> <asp:dropdownlist id="dpdnlst" runat=server> <asp:listitem>repeater</asp:listitem> <asp:listitem>datalist</asp:listitem> <asp:listitem>datagrid</asp:listitem> </asp:dropdownlist> <asp:button text=" " runat=server/> <hr>

64  <asp:panel id="panel1" visible=false runat=server> <asp:repeater id="db1" runat=server>  <template name="headertemplate"> <table> <tr> <td> </td> <td> </td> </tr> </template>  <template name="itemtemplate"> <tr> <td> <%# databinder.eval(container.dataitem,"au_lname") %> </td> <td> <%# databinder.eval(container.dataitem,"au_fname") %> </td> </tr> </template>  <template name="footertemplate"> </table> </template> </asp:repeater> </asp:panel>  <asp:panel id="panel2" visible=false runat=server> <asp:datalist id="db2" runat=server> <!-- datalist > <template name="itemtemplate"> <%# databinder.eval(container.dataitem,"au_lname") %>

65 ---- <%# databinder.eval(container.dataitem,"au_fname") %> <br> </template> </asp:datalist> </asp:panel>  <asp:panel id="panel3" visible=false runat=server> <asp:datagrid id="db3" runat=server> </asp:datagrid> </asp:panel> </form> </center> </body> </html> 2 3 Repeater

66 4 DataList 5 DataGrid

2 ArrayList Repeater DataList DataGrid DataGrid 1 5 1 ArrayList <!-- FormDataSource01.

67 2 ArrayList Repeater DataList DataGrid DataGrid ArrayList  <html> <script language="vb" runat=server> ' Public Class PName private first_name as String private last_name as String Public Property Fname as String Get return first_name End Get Set first_name=value End Set

68 End Property Public Property Lname as String Get return last_name End Get Set last_name=value End Set End Property ' Public Sub New(f as String,l as String) MyBase.New first_name=f last_name=l End Sub End Class Sub Page_Load(o as object,e as eventargs) If Not IsPostBack ' datagrid dim Values as New ArrayList Values.add(New PName("Bennet","Abraham")) Values.add(New PName("Blotchet-Halls","Reginald")) Values.add(New PName("Carson","Cheryl")) Values.add(New PName("DeFrance","Michel")) Values.add(New PName("del Castillo","Innes")) dtgrd.datasource=values dtgrd.databind End If End Sub </script> <head> <title> </title> </head>

69 <body bgcolor=#ccccff> <center> <h2> (ArrayList)</h2> <hr> <form runat=server> <asp:datagrid id="dtgrd" runat=server/> </form> </center> </body> </html> 2 3 HashTable ArrayList MyHashTable.add(index,object) index hash object Values MyDataGrid.DataSource=MyHashTable.Values 1 HashTable  <html>

70 <script language="vb" runat=server> ' Public Class PName private first_name as String private last_name as String Public Property Fname as String Get return first_name End Get Set first_name=value End Set End Property Public Property Lname as String Get return last_name End Get Set last_name=value End Set End Property ' Public Sub New(f as String,l as String) MyBase.New first_name=f last_name=l End Sub End Class Sub Page_Load(o as object,e as eventargs) dim ht as New HashTable if Not IsPostBack 'hash Table Add : ht.add("1",new PName("Bennet","Abraham")) ht.add("2",new PName("Blotchet-Halls","Reginald")) ht.add("3",new PName("Carson","Cheryl"))

71 ht.add("4",new PName("DeFrance","Michel")) ht.add("5",new PName("del Castillo","Innes")) DtGrd.datasource=ht.values DtGrd.databind ' hashtable End if End Sub </script> <head> <title> </title> </head> <body bgcolor=#ccccff> <center> <h2>(hashtable)</h2> <hr> <form runat=server> <asp:datagrid id="dtgrd" runat=server/> </form> </center> </body> </html> 2 HashTable

72 4 Icolletion LoadData Icolletion LoadData LoadData 1 Icolletion  <%@ Import NameSpace="System.Data" %> <html> <script language="vb" runat=server> Function LoadData() As ICollection Dim dt As DataTable Dim dr As DataRow ' dt = New DataTable dt.columns.add(new DataColumn(" ", GetType(String))) dt.columns.add(new DataColumn(" ", GetType(String)))

73 ' dr = dt.newrow() dr(0) = "Bennet" dr(1) = "Abraham" dt.rows.add(dr) dr = dt.newrow() dr(0) = "Blotchet-Halls" dr(1) = "Reginald" dt.rows.add(dr) dr = dt.newrow() dr(0) = "Carson" dr(1) = "Cheryl" dt.rows.add(dr) dr = dt.newrow() dr(0) = "DeFrance" dr(1) = "Michel" dt.rows.add(dr) dr = dt.newrow() dr(0) = "del Castillo" dr(1) = "Innes" dt.rows.add(dr) ' LoadData = New DataView(dt) End Function Sub Page_Load(o as object,e as eventargs) If Not IsPostBack DtGrd.DataSource=LoadData DtGrd.DataBind End If End Sub </script> <head> <title>

74 </title> </head> <body bgcolor=#ccccff> <center> <h2> (ICollection)</h2> <hr> <form runat=server> <asp:datagrid id="dtgrd" runat=server/> </form> </center> </body> </html> 2Icollection Items Items Item DataSource DataSource Item

75 Item AlternatingItem SelectedItem EditItem Item Items Item Item Item DataSource Item Header Footer Separator Item Repeater DataList Pager DataGrid Item ASP.NET DataBind DataSource DataBind DataSource Items DataSource Items DataSource DataBind DataSource WEB Items DataBind DataBind Items DataBind Style Style DataList DataGrid ForeColor BackColor Font BorderStyle Item ItemStyle AlternatingItemStyle HeaderStyle Item DataGrid HeaderStyle FooterStyle ItemStyle

76 Template Style template Item Template HTML Item Repeater DataList ItemTemplate AlternatingItemTemplate HeaderTemplate DataGrid Columns TemplateColumns TemplateColumns TemplateColumn Repeater DataList DataGrid Template Item Item Item Template Container Container DataItem Template Container.DataItem 1 ASP.Net DataBind() TextBox DataBind   <html>

77 <script language="vb" runat=server> Public PubVar as New String("") Public PubInt as Integer=2222 Sub Page_Load(o as object,e as eventargs) DataBind End Sub Public ReadOnly Property PubPropStr as String Get Return " " End Get End Property Public ReadOnly Property PubPropInt as Integer Get Return 1111 End Get End Property </script> <head> <title> </title> </head> <body bgcolor=#ffffff> <center> <h2> </h2> <asp:textbox id="tb" text="textbox " visible=false runat=server/> <hr> </center> <form runat=server> 1 (<%# PubVar %>)<br> 2 (<%# PubInt %>)<br> 1 (<%# PubPropStr %>)<br> 2 (<%# PubPropInt %>)<br> 1 (<%# tb.text %>) </form> </body>

78 </html> 2 asp.net Icollection ArrayList HashTable DataView DataReader dropdownlist 4 ArrayList DataView HashTable DataReader ArrayList ArrayList Add DropDownList Dim values as New ArrayList Values.add( ) DropDownList1.DataSource=values DropDownList1.DataBind HashTable ArryList Add HashTable ArryList Add(keyValue,Object) DataSource HashTable Values

79 Dim ht as HashTable Ht=New HashTable Ht.add(KeyValue, ) KeyValue DropDownList1.DataSource=ht.values DropDownList1.DataBind DataView DataView Table DataSource DataValueField DataValueField DropDownList SQL Dim dt as DataTable Dim dr as DataRow Dt=New DataTable Dt.Columns.add(New DataColumn(,GetTypeString( )) dr=dt.newrow dr(0)= dr(1)= dt.rows.add(dr) DropDownList1.DataSource=New DataView(dt) DropDownList1.DataValueField= DropDownList1.DataBind SQL <%@ Import NameSpace= System..Data %> <%@ Import NameSpace= System..Data.SQL %> dim MyConn as SQLConnectoin dim MyStr as String

80 dim MyDataSetCommand as SQLDataSetCommand dim MyDataSet as DataSet MyConn=new SQLConnection( server= ;uid= ;sa= ;dataserver= ) MyStr= Select * from MyDataSetCommand=New SQLDataSetCommand(MyStr,MyConn) MyDataSetCommand.FillDataSet(MyDataSet, ) DataSet DataSet DropDownList1.DataSource=MyDataSet.tables( ).Defaultaview DropDownList1.DataValueField= DropDownList1.DataBind DataReader DataView DataReader DataView DataReader dim MyConn as SQLConnection dim Mystr as String dim MyComm as SQLCommand dim MyReader as SQLDataReader MyConn=New SQLConnection("server= ;uid= ;pwd= ;database= ") ' MyStr="select * from " MyComm=New SQLCommand(Mystr,MyConn) MyConn.Open MyComm.Execute(MyReader) DropDownList1.Datasource=MyReader DropDownList1.DataValueField= DropDownList1.DataBind MyConn.Close SQL test city test city, city_name 4

81 ,  import NameSpace="System.Data" %> import NameSpace="System.Data.SQL" %> <html> <script language="vb" runat=server> Sub Page_Load(o as object,e as eventargs) If Not IsPostBack ' Dim values as ArrayList values=new ArrayList() values.add("") values.add("") values.add("") values.add("") values.add("") values.add("") lstarray.datasource=values lstarray.databind ' ArrayList Dim dt as DataTable Dim dr as DataRow Dim i as Integer Dim ar as Array dt=new DataTable() dt.columns.add(new DataColumn("City",GetType(string))) ' city For i =0 to 5 dr=dt.newrow() dr(0)=values.item(i) dt.rows.add(dr) Next '

82 lstdataview.datasource=new DataView(dt) lstdataview.datavaluefield="city" lstdataview.databind ' DataView Dim ht as HashTable ht=new HashTable() ht.add("1","") ht.add("2","") ht.add("3","") ht.add("4","") ht.add("5","") ht.add("6","") lsthash.datasource=ht.values lsthash.databind ' HashTable dim MyConn as SQLConnection dim Mystr as String dim MyComm as SQLCommand dim MyReader as SQLDataReader MyConn=New SQLConnection("server=localhost;uid=sa;pwd=;database=test") ' Test MyStr="select city_name from city" ' city (city_name) MyComm=New SQLCommand(Mystr,MyConn) MyConn.Open MyComm.Execute(MyReader) lstdr.datasource=myreader lstdr.datavaluefield="city_name" lstdr.databind MyConn.Close End If End Sub </script> <head> <title> </title>

</head> <body bgcolor=#ccccff> <center> <h2> </h2> <hr> <form runat=server> <b>arraylist :</b> <asp:dropdownlist id="lstarray" runat=server /> <b>dataview :</b> <asp:dropdownlist

83 </head> <body bgcolor=#ccccff> <center> <h2> </h2> <hr> <form runat=server> <b>arraylist :</b> <asp:dropdownlist id="lstarray" runat=server /> <b>dataview :</b> <asp:dropdownlist id="lstdataview" runat=server /> <br> <p></p> <b>hashtable :</b> <asp:dropdownlist id="lsthash" runat=server /> <b>datareader :</b> <asp:dropdownlist id="lstdr" runat=server /> </form> </center> </body> </html>

84 3 asp.net  import namespace="system.data" %> import namespace="system.data.sql" %> <html> <script language="vb" runat=server> Public CLASS book private _name as string private _price as decimal public readonly property name as string Get return _name end Get end property public readonly property price as decimal Get return _price end Get end Property public sub New(n as string,p as decimal) MyBase.New _name=n _price=p end sub End Class Sub Page_Load(o as object,e as eventargs) if IsPostBack dim values as New ArrayList values.add(new book(" ",100.0)) values.add(new book("",90.0))

85 values.add(new book("",85.0)) values.add(new book(" ",60.0)) lbltxt.text="" dg1.datasource=values dg1.databind end if End Sub Public Function GetRealPrice(price as decimal) dim a as decimal a=cdbl(rate.selecteditem.value) GetRealPrice=price*a End Function </script> <head> <title> </title> </head> <body bgcolor=#ccccff> <center> <h2> </h2> <hr> <form runat=server> <asp:dropdownlist id="rate" runat=server> <asp:listitem>1.00</asp:listitem> <asp:listitem>0.95</asp:listitem> <asp:listitem>0.90</asp:listitem> <asp:listitem>0.80</asp:listitem> <asp:listitem>0.70</asp:listitem> <asp:listitem>0.60</asp:listitem> <asp:listitem>0.60</asp:listitem> </asp:dropdownlist> <asp:button text=" " runat=server/> <hr> <asp:label id="lbltxt" runat=server/> <br> <asp:datalist id="dg1" runat=server> <template name="headertemplate">

86 <table> <tr> <th> </th> <th> </th> </tr> </template> <template name="itemtemplate"> <tr> <td> <%# databinder.eval(container.dataitem,"name") %> </td> <td> $<%# GetRealPrice(databinder.eval(container.dataitem,"price")) %> </td> </tr> </template> <template name="footertemplate"> </table> </template> </asp:datalist> </form> </center> </body> </html>

88 4 databinder.eval

89 DataBinder.Eval, DataList DataGrid Reapter Container.DataItem  <html> <script language="vb" runat=server> Public CLASS book private _name as string private _price as decimal public readonly property name as string Get return _name end Get end property public readonly property price as decimal Get return _price end Get end Property public sub New(n as string,p as decimal) MyBase.New _name=n _price=p end sub End Class Sub Page_Load(o as object,e as eventargs) if Not IsPostBack dim ht as New ArrayList

90 end if End Sub </script> ht.add(new book(" ",100.0)) ht.add(new book("",90.0)) ht.add(new book("",85.0)) ht.add(new book(" ",60.0)) dl.datasource=ht dl.databind <head> <title> </title> </head> <body bgcolor=#ccccff> <center> <h2> DataBinder.Eval </h2> <hr> <form runat=server> <b> </b> <p></p> <asp:datalist id="dl" borderwith="1" girdlines="both" runat=server> <template name="itemtemplate"> <%# Databinder.eval(container.dataitem,"name") %> <%# Databinder.eval(container.dataitem,"price") %> </template> </asp:datalist> </form> </center> </body> </html>

3.6.3.1 Repeater Repeater DataSource <%@ Page language="c#" src="repeater1.cs" inherits="samples.

91 Repeater Repeater DataSource Page language="c#" src="repeater1.cs" inherits="samples.repeater1page"%> <asp:repeater runat=server id="linkslistrepeater" DataSource='<%# SiteLinks %>'> <template name="headertemplate"> <ul type="1"> </template> <template name="itemtemplate"> <li> <asp:hyperlink runat=server Text='<%# DataBinder.Eval(Container.DataItem, "SiteName") %>' NavigateUrl='<%# DataBinder.Eval(Container.DataItem, "SiteURL") %>'> </asp:hyperlink> </li> </template> <template name="footertemplate"> </ul> </template>

92 </asp:repeater> (<%# %>) DataBind DataSource DataLinks URL Repeater Template HTML <ul type="1"> HeaderTemplate </ul> FooterTemplate <li> SiteLinks ItemTemplate HeaderTemplate <table> FooterTemplate </Table> ItemTemplate <TR> </TR> ItemTemplate HeaderTemplate FooterTemplate ItemTemplate namespace Samples {... public class Repeater1Page : Page { protected Repeater linkslistrepeater; public ICollection SiteLinks { get { ArrayList sites = new ArrayList(); } } sites.add(new SiteInfo("Microsoft Home", " sites.add(new SiteInfo("MSDN Home", " sites.add(new SiteInfo("MSN Homepage", " sites.add(new SiteInfo("Hotmail", " return sites; protected override void OnLoad(EventArgs e) { base.onload(e);

93 } } if (!IsPostBack){ // DataBind the page the first time it is requested. // This recursively calls each control within the page's // control hierarchy. DataBind(); } public sealed class SiteInfo { private string sitename; private string siteurl; public SiteInfo(string sitename, string siteurl) { this.sitename = sitename; this.siteurl = siteurl; } } } public string SiteName { get { return sitename; } } public string SiteURL { get { return siteurl; } } Repeater1Page Page OnLoad DatBind Template Repeater DataBind DataSource ICollection SiteLinks Repeater DataSource ICollection SiteLinks ArrayList SiteLinks public public protected SiteInfo SiteName SiteURL ItemTemplate <asp:hyperlink runat=server Text='<%# DataBinder.Eval(Container.DataItem, "SiteName") %>' NavigateUrl='<%# DataBinder.Eval(Container.DataItem, "SiteURL") %>'> </asp:hyperlink>

94 DataList DataList Style Page language="c#" src="datalist1.cs" inherits="samples.datalist1page"%>... <asp:datalist runat=server id="peopledatalist" RepeatColumns="2" RepeatDirection="Vertical" RepeatMode="Table" Width="100%"> <property name="alternatingitemstyle"> <asp:tableitemstyle BackColor="#EEEEEE"/> </property> <template name="itemtemplate"> <asp:panel runat=server font-size="12pt" font-bold="true"> <%# ((Person)Container.DataItem).Name %> </asp:panel> <asp:label runat=server Width="20px" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" BackColor='<%# ((Person)Container.DataItem).FavoriteColor %>'> </asp:label> <asp:label runat=server Font-Size="10pt" Text='<%# GetColorName(((Person)Container.DataItem).FavoriteColor) %>'> </asp:label> </template> </asp:datalist> RepeatColumns="2" DataList RepeatDirection= Vertical RepeatDirection= Horizontal, DataList Style Width AlternatingItemStyle namespace Samples {...

95 public class DataList1Page : Page { protected DataList peopledatalist; protected string GetColorName(Color c) { return TypeDescriptor.GetConverter(typeof(Color)).ConvertToString(c); } private void LoadPeopleList() { // create the datasource Person[] people = new Person[] { new Person("Nikhil Kothari", Color.Green), new Person("Steve Millet", Color.Purple), new Person("Chris Anderson", Color.Blue), new Person("Mike Pope", Color.Orange), new Person("Anthony Moore", Color.Yellow), new Person("Jon Jung", Color.MediumAquamarine), new Person("Susan Warren", Color.SlateBlue), new Person("Izzy Gryko", Color.Red) }; // set the control's datasource peopledatalist.datasource = people; } // and have it build its items using the datasource peopledatalist.databind(); protected override void OnLoad(EventArgs e) { base.onload(e); } } if (!IsPostBack){ // first request for the page LoadPeopleList(); } public sealed class Person { private string name; private Color favoritecolor; public Person(string name, Color favoritecolor) { this.name = name;

96 } this.favoritecolor = favoritecolor; } } public Color FavoriteColor { get { return favoritecolor; } } public string Name { get { return name; } } DataSource aspx DataBind DataSource DataSource Person ICollection DataSource DataSource HTML DataSource DataSource DataGrid DataGrid Title Title ID Author Price Publication date titlesdb.xml titlesdb.xml

97 <root> <schema id="documentelement" targetnamespace="" xmlns= xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <element name="title"> <complextype content="elementonly"> <element name="title_id" type="string"></element> <element name="title" type="string"></element> <element name="au_name" type="string"></element> <element name="price" msdata:datatype="system.currency" minoccurs="0" type="string"></element> <element name="pubdate" type="timeinstant"></element> </complextype> <unique name="titleconstraint" msdata:primarykey="true"> <selector>.</selector> <field>title_id</field> </unique> </element> </schema> <DocumentElement> <Title> <title_id>bu1032</title_id> <title>the Busy Executive's Database Guide</title> <au_name>marjorie Green</au_name> <price>19.99</price> <pubdate> t07:00:00</pubdate> </Title>... </DocumentElement> </root> web web service global.asax application_onstart xml DataSet Application (global.asax) public void Application_OnStart() { FileStream fs = null; DataSet ds = null; try {

98 fs = new FileStream(Server.MapPath("TitlesDB.xml"), FileMode.Open, ds = new DataSet(); FileAccess.Read); // load the data in the xml file into the DataSet ds.readxml(fs); } finally { if (fs!= null) { fs.close(); fs = null; } } } // cache the dataset into application state for use in individual pages Application["TitlesDataSet"] = ds; dg01.aspx <%@ Page language="c#" src="datagrid.cs" inherits="samples.datagridpage"%>... <asp:datagrid runat=server id="titlesgrid"> </asp:datagrid> (DataGrid.cs) namespace Samples {... public class DataGridPage : Page { protected DataGrid titlesgrid; public ICollection GetTitlesList() { // Retrieve the list of titles from the DataSet cached in // the application state. DataSet titlesdataset = (DataSet)Application["TitlesDataSet"]; if (titlesdataset!= null) { return titlesdataset.tables["title"].defaultview; } else {

99 } } return null; private void LoadTitlesGrid() { // retrieve the data from the database ICollection titleslist = GetTitlesList(); // set the control's datasource titlesgrid.datasource = titleslist; } // and have it build its items using the datasource titlesgrid.databind(); protected override void OnLoad(EventArgs e) { base.onload(e); } } } if (!IsPostBack){ // first request for the page LoadTitlesGrid(); }.cs OnLoad DataGrid DataBind DataGrid DataSet DataTable DataGrid AutoGenerateColumns True AutoGenerateColumns True DataGrid DataGrid DataSet DataGrid DataGrid BoundColumn (dg02.aspx) <%@ Page language="c#" src="datagrid.cs" inherits="samples.datagridpage"%>

100 ... <asp:datagrid runat=server id="titlesgrid" AutoGenerateColumns="false"> <property name="columns"> <asp:boundcolumn HeaderText="Title" DataField="title"/> <asp:boundcolumn HeaderText="Author" DataField="au_name"/> <asp:boundcolumn HeaderText="Date Published" DataField="pubdate"/> <asp:boundcolumn HeaderText="Price" DataField="price"/> </property> </asp:datagrid> dg02.aspx code-behind DataGrid.cs DataGrid AutoGenerateColumns false DataGrid DataSet HeaderText dg01.aspx BoundColumn BoundColumn BoundColumn Dg03.aspx DataGrid (dg03.aspx) Page language="c#" src="datagrid.cs" inherits="samples.datagridpage"%>... <asp:datagrid runat=server id="titlesgrid" AutoGenerateColumns="false" Width="80%" BackColor="White" BorderWidth="1px" BorderStyle="Solid" CellPadding="2" CellSpacing="0" BorderColor="Tan" Font-Name="Verdana" Font-Size="8pt"> <property name="columns"> <asp:boundcolumn HeaderText="Title" DataField="title"/>

101 <asp:boundcolumn HeaderText="Author" DataField="au_name"/> <asp:boundcolumn HeaderText="Date Published" DataField="pubdate" DataFormatString="{0:MMM yyyy}"/> <asp:boundcolumn HeaderText="Price" DataField="price" DataFormatString="{0:c}"> <property name="itemstyle"> <asp:tableitemstyle HorizontalAlign="Right"/> </property> </asp:boundcolumn> </property> <property name="headerstyle"> <asp:tableitemstyle BackColor="DarkRed" ForeColor="White" Font-Bold="true"/> </property> <property name="itemstyle"> <asp:tableitemstyle ForeColor="DarkSlateBlue"/> </property> <property name="alternatingitemstyle"> <asp:tableitemstyle BackColor="Beige"/> </property> </asp:datagrid> DataGrid dg02.aspx DataGrid.cs DataGrid WebControl Width BackColor BorderStyle Font DataGrid CellPadding DataGrid HeaderStyle AlternatingItemStyle DataGrid Price DataGrid BoundColumn DataFormatString String.Format DataFormatString ToString Dg04.aspx (dg04.aspx) Page language="c#" src="datagrid4.cs" inherits="samples.datagrid4page"%>... <asp:datagrid runat=server id="titlesgrid"

102 AutoGenerateColumns="false" Width="80%" BackColor="White" BorderWidth="1px" BorderStyle="Solid" CellPadding="2" CellSpacing="0" BorderColor="Tan" Font-Name="Verdana" Font-Size="8pt" DataKeyField="title_id" OnSelectedIndexChanged="OnSelectedIndexChangedTitlesGrid"> <property name="columns"> <asp:buttoncolumn Text="Select" Command="Select"/> <asp:boundcolumn HeaderText="Title" DataField="title"/> <asp:boundcolumn HeaderText="Author" DataField="au_name"/> <asp:boundcolumn HeaderText="Date Published" DataField="pubdate" DataFormatString="{0:MMM yyyy}"/> <asp:boundcolumn HeaderText="Price" DataField="price" DataFormatString="{0:c}"> <property name="itemstyle"> <asp:tableitemstyle HorizontalAlign="Right"/> </property> </asp:boundcolumn> </property> <property name="headerstyle"> <asp:tableitemstyle BackColor="DarkRed" ForeColor="White" Font-Bold="true"/> </property> <property name="itemstyle"> <asp:tableitemstyle ForeColor="DarkSlateBlue"/> </property> <property name="alternatingitemstyle"> <asp:tableitemstyle BackColor="Beige"/> </property> <property name="selecteditemstyle"> <asp:tableitemstyle BackColor="PaleGoldenRod" Font-Bold="true"/> </property> </asp:datagrid>... <asp:label runat=server id="selectioninfolabel" Font-Name="Verdana" Font-Size="8pt"/> DataGrid SelectedIndexChanged.cs select DataGrid SelectedItemStyle DataKeyField code-behind DataKeys

103 code-behind (DataGrid4.cs) namespace Samples {... public class DataGrid4Page : Page { protected DataGrid titlesgrid; protected Label selectioninfolabel; public ICollection GetTitlesList() { // Retrieve the list of titles from the DataSet cached in // the application state. DataSet titlesdataset = (DataSet)Application["TitlesDataSet"]; } if (titlesdataset!= null) { return titlesdataset.tables["title"].defaultview; } else { return null; } private void LoadTitlesGrid() { // retrieve the data from the database ICollection titleslist = GetTitlesList(); // set the control's datasource and reset its selection titlesgrid.datasource = titleslist; titlesgrid.selectedindex = -1; // and have it build its items using the datasource titlesgrid.databind(); } // update the selected title info UpdateSelectedTitleInfo(); protected override void OnLoad(EventArgs e) { base.onload(e); if (!IsPostBack){ // first request for the page LoadTitlesGrid();

104 } } // Handles the OnSelectedIndexChanged event of the DataGrid protected void OnSelectedIndexChangedTitlesGrid(object sender, EventArgs e) { UpdateSelectedTitleInfo(); } private void UpdateSelectedTitleInfo() { // get the selected index int selindex = titlesgrid.selectedindex; string seltitleid = null; string selectioninfo; if (selindex!=-1){ // display the key field for the selected title seltitleid = (string)titlesgrid.datakeys[selindex]; selectioninfo = "ID of selected title: " + seltitleid; } else { selectioninfo = "No title is currently selected."; } } } } selectioninfolabel.text = selectioninfo;.cs SelectedIndexChanged ID SelectedIndexChanged DataGrid Select SelectIndex OnSelectedIndexChangedTitlesGrid OnSelectedIndexChangedTitlesGrid UpdateSelectedTitleInfo ID ID ID DataKeys ASPX DataKeyField DataGrid

105 Repeater, DataList, or DataGrid? Repeater, DataList, DataGrid Repeater ID DataList DataGrid WebControl repeater Repeater repeater DataList repeater WebControl DataList DataList DataGrid DataList DataGrid DataGrid DataList DataGrid TemplateColumn Repeater DataList DataGrid Yes ( ) Yes ( ) () No No Yes Yes Yes No No Yes No No Yes Yes No Yes Yes No Yes Yes No Yes Yes No No Yes No No Yes

106 3.6.4 DataSet DataSource DataBind() ItemTemplate

展开

2 ADO.NET Internet 1.2

2 ADO.NET Internet 1.2 C H A P T E R 1 ADO.NET XML ADO.NET ADO.NET.NET Framework.NET XML ADO.NET.NET Microsoft Visual Studio 200 ADO.NET ADO.NET 2 ADO.NET 1 1.1 1.1 1.1 1.2 Internet 1.2 1.1 Internet 2 1.2 (Internet) Web 1. ADO.NET