More on ADO'Net 2'0 for Web Applications

1
More on ADO.Net 2.0for Web Applications
2
Using the DataList Control

• Like the GridView, the DataList is bound by
  setting its DataSource and DataMember properties
  and then calling the DataBind method. But unlike
  the GridView, the DataList has no built-in
  formatting. Rather, you need to supply formatting
  in a series of templates.
• See Page 236 for the DataList example.
• At a minimum, you need to supply an Item Template.

3
DataList (2)

• Data Binding commands set off by lt gt
  makers.
• lt Bind(PizzaToppings, Generously Topped with
  0) gt
• PizzaToppings, is the name of the field to be
  evaluated
• The 0 token in the format string is replaced
  with the results of the evaluation.

4
Repeater Control

• Repeater control depends on Templates to provides
  all of its content
• Repeater control does NOT have a design time
  interface. You need to specify the ItemTemplate
  in the Source viewltItemTemplategt
  ltEval(ItemSize) gt ltbr /gtlt/ItemTemplategt

5
Data View Controls

• For displaying multiple records using
• GridView
• DataList
• Repeater
• For displaying a single record
• DetailsView
• FormView

6
Stored Procedures

• A named precompiled query that is held
  permanently in the database.- known as prepared
  sql statements- most of the larger commercial
  databases also support stored procedures.-
  more efficient and faster- excellent for passing
  parameters

7
Stored Procedures (cont.)

• Example of a query-based stored procedure in SQL
• - create procedure ProductTypes As select
  CategoryID, CategoryName from Categories
• Example of a parameterized stored procedure-
  create procedure CountTreasureInRoom ( _at_room
  int) as select count() from treasure where
  roomID_at_room
• Also see the examples in pp.532-539

8
Stored Procedures and the Command Object

• The command object can call stored procedures
  directly
• set the Connection property of the command object
  to the connection you setup.
• set the CommandText property to the name of the
  stored procedure.
• Set the CommandType property to
  CommandType.StoredProcedure

9
Stored Procedures and the Command Object (cont.)

• Must use an appropriate Execute method of the
  command object
• ExecuteNonQuery() if stored procedure returns
  nothing
• ExecuteScalar() if the stored procedure computes
  a single result
• ExecuteReader() if a DataReader is to be returned

10
Invoking Stored Procedures

• Specify the connection string asdim cnn as New
  SqlConnection(ConfigurationManager.
  ConnectionStrings(StringName).ConnectionString)
  
• Stored Procedure for data being retrieved
• Dim MyCommand as New SqlCommandMyCommand.Conne
  ctioncnnMyCommand.CommandText
  "AddSurveyMyCommand.CommandType
  CommandType.StoredProcedure
• Execute the Stored Procedure
• cnn.open Dim I as integerMyCommand.ExecuteSc
  alar() Cnn.close Returen i

11
Parameterized Stored Procedures

• After defining the database connection string,
  specify the sqlcommand class
• Dim cmd1 as New SqlCommand cmd1.Connectioncnn
  cmd1.CommandText "AddSurvey cmd1.CommandType
  CommandType.StoredProcedure
• Providing a Value for the Parametercmd1.Paramete
  rs.Add("_at_gender", SqlDbType.Int).Value
  gender.SelectedValue

12
Lab7 Exercises