1 / 38

Introduction

Beginning Web Site Development Module 2 – Web Data Building Data-Driven Sites With ASP.NET and C# Version. Introduction. Target audience New to programming but want to build a dynamic data-backed Web site Prerequisites Basic understanding of HTML, familiarity with Web interfaces

natan
Download Presentation

Introduction

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. Beginning Web Site DevelopmentModule 2 – Web DataBuilding Data-Driven SitesWith ASP.NET andC# Version

  2. Introduction • Target audience • New to programming but want to build a dynamic data-backed Web site • Prerequisites • Basic understanding of HTML, familiarity with Web interfaces • Completed “Beginning Web Site Development Module 1” • Expectations • Learn the basics of building dynamic data-backed Web sites with Visual Web Developer Express and ASP.NET 2.0

  3. Agenda • Dynamic, data-driven sites • Using SQL Server 2005 Express • Creating and using local databases • Structured Query Language (SQL) • Talking to a database with SQL • Data binding • Binding ASP.NET controls to data

  4. Dynamic Web Sites • Many sites today are dynamic and interactive • Display content that is updated frequently • News sites, sports sites, stock analysis sites • Allow clients to interact with site • Shopping sites, internet portals, auction sites • Interaction requires storage…

  5. Saving client-entered data • Where should user-entered data go? Web Server Client Browser AddPoem.aspx AddPoem.aspx.cs ?

  6. Saving client-entered data • Problem: interactive Web pages need a place to store data • Solution: relational database • Store data in tabular format (think spreadsheet) • Handles multiple users simultaneously • Standard language (SQL) and many tools for manipulating data

  7. SQL Server 2005 Express • VWD Express installs SQL 2005 Express • Local file-based database engine • Full SQL support, databases up to 4GB • Easy to deploy – copy database file (.mdf) to deployment server

  8. Creating a database • Add SQL 2005 Express database • Stores data in tables you define Right click

  9. Database tables • Databases store data in tables • Conceptually similar to spreadsheets Each table is described as a collection of columns, each storing a specific type of data A table contains rows of data that conform to the table description

  10. Defining a table • Create table(s) to store data • Separate column for each attribute • Unique ID column Right click

  11. Identity columns • Identity column • Column storing a unique number for each row • Good practice to have an identity column • Improves performance, adds flexibility • Unique ID generated for each new entry

  12. Filling tables with data • You can add data to tables you create • Initial seed data or complete data for tables that store read-only data (like a list of items and their prices) Right click

  13. Creating a SQL 2005 Express Database Creating the initial database Adding tables Specifying identity columns Populating table data

  14. SQL • Structured Query Language (SQL) • Standard way to retrieve data from and modify data in a database • Basic understanding necessary to use ASP.NET data controls • Four standard operations for manipulating and retrieving data SELECT UPDATE INSERT DELETE

  15. Testing queries • VWD Express Database Explorer provides query builder Run Query Right click Tables in query listed here Columns in query listed here Query text Query results

  16. SELECTing data • SQL method for retrieving data is SELECT SELECT ID, Author, Quote FROM Quotes Column names Table name =

  17. Constraining with WHERE • Can add criteria to constrain results of SELECT with WHERE clause SELECT ID, Author, Quote FROM Quotes WHERE (Author = 'William Shakespeare') = SELECT ID, Author, Quote FROM Quotes WHERE (Author LIKE '%ea%') =

  18. INSERTing rows • SQL method for inserting rows is INSERT Column names Table name INSERT (Author, Quote) INTO Quotes VALUES ('Me', 'Life is good!') Values to insert

  19. UPDATEing rows • SQL method for updating rows is UPDATE Table name New column value(s) UPDATE Quotes SET Author='Joe' WHERE ID=3 Constraint (which row(s) )

  20. DELETEing rows • SQL method for deleting rows is DELETE Table name DELETE FROM Quotes WHERE ID=3 Constraint (which row(s) )

  21. Interacting with a database using SQL Selecting data Inserting data Updating data Deleting data

  22. Data binding • Data binding • A mechanism for displaying and modifying dynamic data from a Web page • ASP.NET supports data binding • Can point controls to database table as source for data • Displayed content drawn from table • Interface for modifying data built into some controls (update, delete, insert)

  23. Data binding Client Browser Web Server Default.aspx Quotes Database Data-binding

  24. Binding controls to data • Several ASP.NET controls designed to bind to data • GridView • Display/edit a database table as a grid • DetailsView • Display one table row at a time, insert new items • BulletedList • Display a list of items from a table • Many more …

  25. SqlDataSource control • SqlDataSource control handles data retrieval, inserts, updates, and deletes • Acts as bridge between database and data-bound control • Contains SQL statements to perform database calls <asp:SqlDataSourceID="QuotesDataSource" runat="server" SelectCommand="SELECT ID, Author, Quote FROM Quotes" ConnectionString="…"/>

  26. Adding a GridView 2. Open Database Explorer 3. Drag table onto page 4. Select desired options 5. Optionally Auto Format… 6. Edit Columns and setID column Visible=false 1. Set page to Design mode

  27. Generated source <asp:GridViewID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" EmptyDataText=“No records."> <Columns> <asp:BoundFieldDataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" Visible="False" /> <asp:BoundFieldDataField="Author" HeaderText="Author" SortExpression="Author" /> <asp:BoundFieldDataField="Quote" HeaderText="Quote" SortExpression="Quote" /> </Columns> </asp:GridView> <asp:SqlDataSourceID="SqlDataSource1" runat="server" SelectCommand= "SELECT ID, Author, Quote FROM Quotes" ConnectionString= "<%$ ConnectionStrings:QuotesConnectionString1 %>"/> GridView declaration BoundFields for columns SqlDataSource declaration SQL query to retrieve data Connection string pointing to local Quotes.mdf file web.config Default.aspx <configuration> <connectionStrings> <addname="QuotesConnectionString1" connectionString="Data Source=… " providerName="System.Data.SqlClient" /> </connectionStrings> </configuration>

  28. Adding a GridView Adding a GridView to display table data SqlDataSource control Enabling sorting and paging Formatting the GridView

  29. SQL parameters • SQL supports parameters to fill values in dynamically • SQL Server syntax is @varname • Before executing statement, parameters must be associated with values UPDATE Quotes SET Author=@Author WHERE ID=@ID parameters

  30. SqlDataSource parameters • How to associate parameters with SqlDataSource Add Update and Delete commands with parameters <asp:SqlDataSourceID="SqlDataSource1" runat="server" ConnectionString="<%$ConnectionStrings:QuotesConnectionString1 %>" SelectCommand="SELECT [ID], [Author], [Quote] FROM [Quotes]" UpdateCommand="UPDATE Quotes SET Author=@Author, Quote=@Quote WHERE ID=@ID" DeleteCommand="DELETE FROM Quotes WHERE ID=@ID"> <UpdateParameters> <asp:ParameterName="Author" Type="String" /> <asp:ParameterName="Quote" Type="String" /> <asp:ParameterName="ID" Type="Int32" /> </UpdateParameters> <DeleteParameters> <asp:ParameterName="ID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> List parameter names and types Note: Parameters should always be named the same as the corresponding column names to work properly with the GridView and DetailsView controls

  31. GridView updates and deletes • Once your SqlDataSource has Update and Delete commands, the GridView can be enabled with Update and Delete features Must set DataKeyNames to identity column name to support Update/Delete

  32. GridView updating

  33. Inserting with a DetailsView • DetailsView provides insert feature • Can also be used to display/update/delete one row at a time Configure new data source

  34. Inserting with a DetailsView • Add InsertCommand to data source • Enable Inserting and set DefaultMode=Insert <asp:SqlDataSourceID="SqlDataSource2" runat="server" ConnectionString="<%$ConnectionStrings:QuotesConnectionString1 %>" SelectCommand="SELECT [ID], [Author], [Quote] FROM [Quotes]" InsertCommand="INSERT INTO Quotes (Author, Quote) VALUES (@Author, @Quote)"> <InsertParameters> <asp:ParameterName="Author" Type="string" /> <asp:ParameterName="Quote" Type="string" /> </InsertParameters> </asp:SqlDataSource>

  35. Inserting with a DetailsView

  36. Updating, Deleting, and Inserting Enabling GridView Updating and Deleting Using a DetailsView for inserting

  37. Summary • Interactive sites make the internet what it is today • VWD Express includes database engine • SQL Server 2005 Express • ASP.NET supports data binding • Display database content • Update, insert, delete

  38. Resources • VWD 2005 Express home • http://msdn.microsoft.com/vstudio/express/vwd/default.aspx • SQL 2005 Express home • http://msdn.microsoft.com/vstudio/express/sql/default.aspx • Writing Transact-SQL Statements Tutorial • http://msdn2.microsoft.com/en-us/library/ms365303.aspx • ASP.NET 2.0 QuickStart Tutorials • http://quickstarts.asp.net/QuickStartv20/aspnet/Default.aspx

More Related