Chapter 8 Working With Databases in ASP NET

Chapter 8 Working With Databases in ASP. NET

Listing 8. 1 – Show. List. Controls • Uses The Sql. Data. Source control for estabishing database connectivity and query/update • Includes all the list controls: – Bulleted. List, Check. Box. List, Drop. Down. List, List. Box, and Radio. Button. List • Each List control has these properties for data binding using an Sql. Data. Source – Data. Source. Id – the ID of the Sql. Data. Source object – Data. Text. Field – the name of the column from the SQL command that should be displayed

Listing 8. 1 Show. List. Controls. aspx Each of these are subclasses of the List. Control class. List. Control is an abstract class.

Listing 8. 1 Show. List. Controls. aspx The SQLData. Source 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

Sql. Data. Source 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 – – – Connection. String Select. Command Insert. Command Update. Command Delete. Command

Connection. String Property <asp: Sql. Data. Source id="src. Movies" Connection. String="Data Source=. SQLExpress; Attach. Db. Filename=|Data. Directory|My. Database. mdf; Integrated Security=True; User Instance=True" Select. Command="SELECT Title FROM Movies" Runat="server" /> The Connection. String 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: Connection. String="Server=localhost; Database=My. Databas e; Trusted_Connection=Yes; "

Select. Command <asp: Sql. Data. Source id="src. Movies" Connection. String="Data Source=. SQLExpress; Attach. Db. Filename=|Data. Directory|My. Database. mdf; Integrated Security=True; User Instance=True" Select. Command="SELECT Title FROM Movies" Runat="server" /> The Select. Command property’s is a SQL SELECT statement. Other properties include Insert. Command, Update. Command, and Delete. Command.

List Control Data Binding Properties <asp: Bulleted. List id="Bulleted. List 1" Data. Source. Id="src. Movies" Data. Text. Field="Title" Runat="server" /> All List controls (databound) include these two properties: • Data. Source. Id – the ID of the Sql. Data. Source object • Data. Text. Field – the name of the column from the SQL Select. Command that should be displayed

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

Listing 8. 6 Bound. Grid. View. aspx Grid. View is a databound control that renders an HTML table based on a query.

Listing 8. 2 Show. Tabular. Data. Bound. aspx These six Web controls are more complex and can display multiple separate columns from a query.

Browser’s rendering of HTML code.

Tabular Data. Bound Controls Subclasses of Data. Bound. Control (newer) – Grid. View – HTML table, one record per displayed row – Data. List – HTML table, multiple records can display on a row, requires templates – Repeater – does not render an HTML table, requires templates – List. View – displayed via template; allows sorting, paging, editing • Single Data Record – Details. View – HTML Table of a single record – Form. View – Single record displayed via templates, allows paging http: //msdn. microsoft. com/en-us/library/ms 228214. aspx Not descended from Data. Bound. Control (older) Subclasses of Composite. Data. Bound. Control • Multiple Data Records

Using Item Templates <asp: Form. View id="Form. View 1" Data. Source. Id="src. Movies" Allow. Paging="true" Runat="server"> <Item. Template> <%#Eval("Title")%> <i>directed by</i> <%#Eval("Director")%> </Item. Template> </asp: Form. View> <asp: Repeater id="Repeater 1" Data. Source. Id="src. Movies" Runat="server"> <Item. Template> <%#Eval("Title")%> <i>directed by</i> <%#Eval("Director")%> </Item. Template> </asp: Repeater> Item. Templates allow you to specify, using html and “old ASP”, specific content to display. Item. Template is a sub-element of the data bound Web control.

Old ASP <asp: Form. View id="Form. View 1" Data. Source. Id="src. Movies" Allow. Paging="true" Runat="server"> <Item. Template> <%#Eval("Title")%> <i>directed by</i> <%#Eval("Director")%> </Item. Template> </asp: Form. View> <asp: Repeater id="Repeater 1" Data. Source. Id="src. Movies" Runat="server"> <Item. Template> <%#Eval("Title")%> <i>directed by</i> <%#Eval("Director")%> </Item. Template> </asp: Repeater> 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.

Listing 8. 10 Show. Links. aspx Item. Templates can contain other <asp: > tags as subelements.

Listing 8. 13 Show. Form. View. aspx Edit. Item. Templates can contain other <asp: > tags as sub-elements, and allow for Buttons to submit data to the server. The Update. Command includes @Fld. Name. This is called a placeholder. This allows you to retrieve the data from the associated Text. Box controls in the form (and Data. Key. Name for the primary key).

Listing 8. 7 Show. Control. Parameter. aspx This example illustrates the use of control parameters to modify queries based on user selection from a Drop. Down. List

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

Listing 8. 7 Show. Control. Parameter. aspx A Drop. Down. List databound to a Sql. Data. Source for the dominant table in the relationship.

Listing 8. 7 Show. Control. Parameter. aspx Clicking the button simply performs the submit to the server.

Listing 8. 7 Show. Control. Parameter. aspx Based on the userselected value from the Drop. Down. List, the other Sql. Data. Source can customize a query. This is done through the control parameter.

Listing 8. 7 Show. Control. Parameter. aspx A control parameter links a particular control to an item in a Select. Command (or Update, Insert, Delete). Note: there are other types of parameters. The Select. Parameters subelement represents a collection of parameters to be used for the Select. Command. Each Select. Parameter will be associated with a placeholder in the query.

Listing 8. 7 Show. Control. Parameter. aspx Control. Parameter Properties: • Name – identifies the placeholder to replace in the query • Control. ID – identifies the control that contains the value to put into the placeholder