1 / 30

Chapter 8

Chapter 8. Working With Databases in ASP .NET. Listing 8.1 – ShowListControls. Uses The SqlDataSource control for estabishing database connectivity and query/update Includes all the list controls: BulletedList, CheckBoxList, DropDownList, ListBox, and RadioButtonList

kira
Download Presentation

Chapter 8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8 Working With Databases in ASP .NET

  2. Listing 8.1 – ShowListControls • Uses The SqlDataSource control for estabishing database connectivity and query/update • Includes all the list controls: • BulletedList, CheckBoxList, DropDownList, ListBox, and RadioButtonList • Each List control has these properties for data binding using an SqlDataSource • DataSourceId – the ID of the SqlDataSource object • DataTextField – the name of the column from the SQL command that should be displayed

  3. Listing 8.1 ShowListControls.aspx Each of these are subclasses of the ListControl class. ListControl is an abstract class.

  4. Listing 8.1 ShowListControls.aspx The SQLDataSource control allows the .aspx page to set both database connections and default queries.  With this approach, database connectivity and query is part of the markup, not the code-behind

  5. SqlDataSource Control • Represents an SQL database to data-bound controls • Used to establish connection to a data source and perform queries and updates to the data source. • Some Properties • ConnectionString • SelectCommand • InsertCommand • UpdateCommand • DeleteCommand

  6. ConnectionString Property <asp:SqlDataSource id="srcMovies" ConnectionString="Data Source=.\SQLExpress; AttachDbFilename=|DataDirectory|MyDatabase.mdf; Integrated Security=True;User Instance=True" SelectCommand="SELECT Title FROM Movies" Runat="server"/> The ConnectionString property’s value depends on the database server and host’s configurations. Above is the connection string from the book. For the Lab computers use the following: ConnectionString="Server=localhost;Database=MyDatabase;Trusted_Connection=Yes;"

  7. SelectCommand <asp:SqlDataSource id="srcMovies" ConnectionString="Data Source=.\SQLExpress; AttachDbFilename=|DataDirectory|MyDatabase.mdf; Integrated Security=True;User Instance=True" SelectCommand="SELECT Title FROM Movies" Runat="server"/> The SelectCommand property’s is a SQL SELECT statement. Other properties include InsertCommand, UpdateCommand, and DeleteCommand.

  8. List Control Data Binding Properties <asp:BulletedList id="BulletedList1" DataSourceId="srcMovies" DataTextField="Title" Runat="server"/> • All List controls (databound) include these two properties: • DataSourceId– the ID of the SqlDataSource object • DataTextField– the name of the column from the SQL SelectCommand that should be displayed

  9. Browser’s rendering of HTML code. Note, each list contains the result of the SQL SELECT query of the associated SqlDataSource, with the Title column being displayed.

  10. Listing 8.6 BoundGridView.aspx GridView is a databound control that renders an HTML table based on a query.

  11. Listing 8.2 ShowTabularDataBound.aspx These six Web controls are more complex and can display multiple separate columns from a query.

  12. Browser’s rendering of HTML code.

  13. Tabular DataBound Controls Subclasses of DataBoundControl (newer) • Multiple Data Records • GridView – HTML table, one record per displayed row • DataList – HTML table, multiple records can display on a row, requires templates • Repeater – does not render an HTML table, requires templates • ListView – displayed via template; allows sorting, paging, editing • Single Data Record • DetailsView – HTML Table of a single record • FormView – Single record displayed via templates, allows paging Not descended from DataBoundControl (older) Subclasses of CompositeDataBoundControl http://msdn.microsoft.com/en-us/library/ms228214.aspx

  14. Using Item Templates <asp:FormView id="FormView1" DataSourceId="srcMovies" AllowPaging="true" Runat="server"> <ItemTemplate> <%#Eval("Title")%> <i>directed by</i> <%#Eval("Director")%> </ItemTemplate> </asp:FormView> ItemTemplates allow you to specify, using html and “old ASP”, specific content to display. ItemTemplate is a sub-element of the data bound Web control. <asp:Repeater id="Repeater1" DataSourceId="srcMovies" Runat="server"> <ItemTemplate> <%#Eval("Title")%> <i>directed by</i> <%#Eval("Director")%> </ItemTemplate> </asp:Repeater>

  15. Old ASP <asp:FormView id="FormView1" DataSourceId="srcMovies" AllowPaging="true" Runat="server"> <ItemTemplate> <%#Eval("Title")%> <i>directed by</i> <%#Eval("Director")%> </ItemTemplate> </asp:FormView> Before .NET, ASP tags looked like this: <%# server-side code here %> The Eval function call returns the value of the field identified in its argument. <asp:Repeater id="Repeater1" DataSourceId="srcMovies" Runat="server"> <ItemTemplate> <%#Eval("Title")%> <i>directed by</i> <%#Eval("Director")%> </ItemTemplate> </asp:Repeater>

  16. Listing 8.10 ShowLinks.aspx ItemTemplates can contain other <asp:> tags as sub-elements.

  17. Listing 8.13 ShowFormView.aspx EditItemTemplates can contain other <asp:> tags as sub-elements, and allow for Buttons to submit data to the server. The UpdateCommand includes @FldName. This is called a placeholder. This allows you to retrieve the data from the associated TextBox controls in the form (and DataKeyName for the primary key).

  18. Listing 8.7 ShowControlParameter.aspx This example illustrates the use of control parameters to modify queries based on user selection from a DropDownList

  19. Database Tables for this Example Two tables, with one-to-many relationship.

  20. Listing 8.7 ShowControlParameter.aspx A DropDownList databound to a SqlDataSource for the dominant table in the relationship.

  21. Listing 8.7 ShowControlParameter.aspx Clicking the button simply performs the submit to the server.

  22. Listing 8.7 ShowControlParameter.aspx Based on the user-selected value from the DropDownList, the other SqlDataSource can customize a query. This is done through the control parameter.

  23. Listing 8.7 ShowControlParameter.aspx • A control parameter links a particular control to an item in a SelectCommand (or Update, Insert, Delete). • Note: there are other types of parameters. The SelectParameters sub-element represents a collection of parameters to be used for the SelectCommand. Each SelectParameter will be associated with a placeholder in the query.

  24. Listing 8.7 ShowControlParameter.aspx • ControlParameter Properties: • Name – identifies the placeholder to replace in the query • ControlID – identifies the control that contains the value to put into the placeholder

More Related