ado| Stored Procedures | data
6.1 Using Stored procedures
1: <%@ Import namespace= "System.Data"%>
2: <%@ Import namespace= "System.Data.SqlClient"%>
3:
4: <%
5:dim MyConnection as SqlConnection
6:dim MyCommand as SqlCommand
7:dim FirstName as String = "Robert"
8:dim LastName as String = "Johnson"
9:
10:myconnection = New SqlConnection ("Server=localhost;uid=sa;pwd=secret;database=mydata")
11:myconnection.open ()
12:mycommand = New SqlCommand ("Insertauthors", MyConnection)
13:mycommand.commandtype = CommandType.StoredProcedure
14:
15:mycommand.parameters.add (New SqlParameter ("@FirstName",
Ccc.gifSqlDbType.Varchar, 30))
16:mycommand.parameters ("@FirstName"). Value = FirstName
17:
18:mycommand.parameters.add (New SqlParameter ("@LastName",
Ccc.gifSqlDbType.Varchar, 30))
19:mycommand.parameters ("@LastName"). Value = LastName
20:
21:mycommand.executenonquery ()
22:myconnection.close
%>:
24:record inserted!
25:
26:
6.2 Re-return parameters and return values
1: <%@ Import namespace= "System.Data"%>
2: <%@ Import namespace= "System.Data.SqlClient"%>
3:listing 6.4.1 demonstrates
4: <%
5:dim MyConnection as SqlConnection
6:dim MyCommand as SqlCommand
7:dim Myparam as SqlParameter
8:
9:myconnection = New SqlConnection ("Server=localhost;uid=sa;database=pubs")
10:myconnection.open ()
11:
12:mycommand = New SqlCommand ("Getlastname", MyConnection)
13:mycommand.commandtype = CommandType.StoredProcedure
14:
15:myparam = MYCOMMAND.PARAMETERS.ADD (New
Ccc.gifsqlparameter ("Return VALUE", SqlDbType.Int))
16:myparam.direction = ParameterDirection.ReturnValue
17:
18:myparam = MYCOMMAND.PARAMETERS.ADD (New
Ccc.gifsqlparameter ("@FirstName", SqlDbType.VarChar, 50))
19:myparam.direction = ParameterDirection.Input
20:myparam.value = "Robert"
21st:
22:myparam = MYCOMMAND.PARAMETERS.ADD (New
Ccc.gifsqlparameter ("@LastName", SqlDbType.VarChar, 50))
23:myparam.direction = ParameterDirection.Output
24:
25:mycommand.executenonquery ()
26:if mycommand.parameters ("Return VALUE"). Value Then
27:response.write ("The Last Name is" &
Mycommand.parameters ("@LastName"). Value)
28:else
29:response.write ("No author found!")
30:end If
31:myconnection.close ()
%>:
33:
7.1 using DataTable (SQL Server)
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 Myrow as DataRow
10:
11:myconnection = New SqlConnection ("Server=localhost;uid=sa;database=pubs")
12:mydataadapter = New SqlDataAdapter ("select * from Authors", MyConnection)
13:mydataset = New DataSet ()
14:mydataadapter.fill (myDataSet, "Authors")
15:
16:for each myrow in Mydataset.tables ("Authors"). Rows
17:response.write (Myrow ("au_lname")) DataTable
18:next
19:
%>:
21st:
7.2 using DataTable (Access)
1: <%@ Import namespace= "System.Data"%>
2: <%@ Import namespace= "System.Data.OleDb"%>
3:
4: <%
5:dim MyConnection as OleDbConnection
6:dim MyDataAdapter as OleDbDataAdapter
7:dim myDataSet as DataSet
8:dim mydatatable as DataTable
9:dim Myrow as DataRow
10:
11:myconnection = New OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data
Ccc.gifsource=c:\authors.mdb ")
12:mydataadapter = New OleDbDataAdapter ("SELECT * from Authors", MyConnection)
13:mydataset = New DataSet ()
14:mydataadapter.fill (myDataSet, "Authors")
15:
16:for each myrow in Mydataset.tables ("Authors"). Rows
17:response.write (Myrow ("Author"))
18:next
19:
%>:
21st:
7.3 Automatic display of a table
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:
10:dim RowCount as Integer
11:dim ColCount as Integer
12:dim I, K as Integer
13:
14:myconnection = New SqlConnection ("Server=localhost;uid=sa;database=pubs")
15:mydataadapter = New SqlDataAdapter ("select * from Authors", MyConnection)
16:mydataset = New DataSet ()
17:mydataadapter.fill (myDataSet, "Authors")
18:
19:rowcount = Mydataset.tables ("Authors"). Rows.Count
20:colcount = Mydataset.tables ("Authors"). Columns.count
21st:
22:response.write ("<table border=1>")
23:for i = 0 to RowCount-1
24:response.write ("<tr>")
25:for k = 0 to ColCount-1
26:response.write ("<td>")
27:response.write (Mydataset.tables ("Authors"). Rows (i). Item (k,
datarowversion.current). toString ())
28:response.write ("</td>")
29:next
30:response.write ("</tr>")
31:next
32:response.write ("</table>")
%>:
34:
7.4 Building a DataTable
1: <%@ Import namespace= "System.Data"%>
2: <%
3:dim mydatatable as DataTable
4:dim MyColumn as DataColumn
5:dim Myrow as DataRow
6:dim I as Integer
7:dim Myrand as System.Random
8:dim ProductID as Integer
9:
: ' Create a DataTable
11:mydatatable = new DataTable ("ShoppingCart")
12:mydatatable.minimumcapacity = 50
13:mydatatable.casesensitive = False
14:
: ' Add an AutoIncrement (Identity) Column
16:mycolumn = MyDataTable.Columns.Add ("ID",
Ccc.gifSystem.Type.GetType ("System.Int32"))
17:mycolumn.autoincrement = TRUE
18:mycolumn.allowdbnull = False
19:
: ' Add an Integer Column
21:mycolumn = MyDataTable.Columns.Add ("UserID",
Ccc.gifSystem.Type.GetType ("System.Int32"))
22:mycolumn.allowdbnull = False
23:
: ' Add an Integer Column
25:mycolumn = MyDataTable.Columns.Add ("ProductID",
Ccc.gifSystem.Type.GetType ("System.Int32"))
26:mycolumn.allowdbnull = False
27:
: ' Add a String Column
29:mycolumn = MyDataTable.Columns.Add ("ProductName",
System.Type.GetType ("System.String"))
30:mycolumn.allowdbnull = False
31:
: ' Add a Decimal Column
33:mycolumn = MyDataTable.Columns.Add ("Productprice",
Ccc.gifSystem.Type.GetType ("System.Decimal"))
34:mycolumn.allowdbnull = False
35:
ADD: ' Some Data
37:myrand = New Random
38:for i = 0 to 20
39:productid = Myrand.next (5)
40:myrow = Mydatatable.newrow ()
41:myrow ("UserID") = Myrand.next (3)
42:myrow ("ProductID") = ProductID
43:myrow ("ProductName") = "Product" & Productid.tostring ()
44:myrow ("productprice") = 10.25
45:mydatatable.rows.add (Myrow)
46:next
47:
"Display all the Rows"
49:for each myrow in Mydatatable.rows
50:response.write ("51:for each mycolumn in Mydatatable.columns
52:response.write (Myrow.item (MyColumn). ToString () & "/")
53:next
54:next
%>:
56:
57:
7.5 Filtering data in a DataTable
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 Myrow as DataRow
10:dim selectrows () as DataRow
11:
12:myconnection = New SqlConnection ("Server=localhost;uid=sa;pwd=secret;database=pubs")
13:mydataadapter = New SqlDataAdapter ("select * from Titles", MyConnection)
14:mydataset = New DataSet ()
15:mydataadapter.fill (myDataSet, "Titles")
16:selectrows = Mydataset.tables ("Titles"). Select ("Type= ' Popular_comp '"),
Ccc.gif "title DESC", DataViewRowState.CurrentRows)
17:
18:for each myrow in Selectrows
19:response.write (Myrow.item ("title"))
20:next
:%>
7.6 DataView Filtration
1: <%@ Import namespace= "System.Data"%>
2: <%@ Import namespace= "System.Data.SqlClient"%>
3: <%
4:dim MyConnection as SqlConnection
5:dim MyDataAdapter as SqlDataAdapter
6:dim myDataSet as DataSet
7:dim mydatatable as DataTable
8:dim Mydataview as DataView
9:dim Myrow as DataRowView
10:
11:myconnection = New SqlConnection ("Server=localhost;uid=sa;pwd=secret;database=pubs")
12:mydataadapter = New SqlDataAdapter ("select * from Titles", MyConnection)
13:mydataset = New DataSet ()
14:mydataadapter.fill (myDataSet, "Titles")
15:mydataview = Mydataset.tables ("Titles"). DefaultView
16:mydataview.rowfilter = "type= ' Popular_comp '"
17:mydataview.sort = "title DESC"
18:
19:for each myrow in Mydataview
20:response.write (Myrow ("title"))
21:next
%>:
23:
