ASP.NET Part III - WebPages (Continued) - Introduction to SQL

CSC 330 E-CommerceTeacher Ahmed Mumtaz Mustehsan GM-IT CIIT Islamabad Virtual Campus, CIIT COMSATS Institute of Information TechnologyT2-Lecture-12ASP.NETPart - IIIFor Lecture Material/Slides Thanks to: www.w3schools.comASP.NET Part IIIWebPages (Continued)Introduction to SQLThank YouObjectivesWebPages DatabasesWebPages HelpersWebPages WebGridWebPages ChartsWebPages EmailWebPages PHPWebPages Publish Introduction to SQLT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-4WebPages DatabasesDisplaying Data from DatabaseWith Web Pages, data can easily be displayed from a database.Connect to an existing database, or create a new database from scratch.The following example will demonstrate to connect to an existing SQL Server Compact database.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-6Displaying Data from Database@{var db = Database.Open("SmallBakery"); var query = "SELECT * FROM Product"; }  Small Bakery Products  Id Product Description Price ..T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-7@foreach(var row in db.Query(query)){ @row.Id @row.Name @row.Description @row.Price  }  In the "DemoWebPages" folder, create a new CSHTML file named "Products.cshtml".Replace the code in the file with the code from the example below:Output of the above ExampleIdProductDescriptionPrice1BreadBaked fresh every day2.992Strawberry CakeMade with organic strawberries9.993Apple PieSecond only to your mom's pie12.994Pecan PieIf you like pecans, this is for you10.995Lemon PieMade with the best lemons in the world11.996CupcakesYour kids will love these9.99T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-8Small Bakery ProductsExample ExplainedThe Database.Open(name) method will connect to a database in two steps:First, it searches the application's App_Data folder for a database that matches the name (Small bakery) parameter without the file-name extension.If no file is found, it looks for a "connection string" in the application's Web.config file.(A connection string contains information about how to connect to a database? It can include a file path, or the name of an SQL database, with full user name and password)This two-step search makes it possible to test the application with a local database, and run the application on a web host using a connection string. T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-9WebPages HelpersASP.NET Web Pages - HelpersWeb Helpers greatly simplifies web development and common programming tasks.ASP.NET HelpersASP.NET helpers are components that can be accessed by single lines of Razor code.You can build your own helpers using Razor syntax stored as .cshtml files, or use built-in ASP.NET helpers.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-11Razor helpers: The WebGrid HelperThe WebGrid helper simplifies the way to display data:Automatically sets up an HTML table to display dataSupports different options for formattingSupports paging (First, next, previous, last) through dataSupports sorting by clicking on column headingsT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-12The Chart HelperThe "Chart Helper" can display chart images of different types with many formatting options and labels.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-13The WebMail HelperThe WebMail helper provides functions for sending email messages using SMTP (Simple Mail Transfer Protocol).T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-14The WebImage HelperThe WebImage helper provides functionality to manage and manipulate images in a web page.Keywords: flip, rotate, resize, watermark.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-15WebPages WebGridASP.NET Web Pages - The WebGrid HelperWebGrid - One of many useful ASP.NET Web Helpers.We have already learnt to display database data by using razor code, and using the HTML markup.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-17ASP.NET Web Pages ( Displaying Data from Database previous Method)Database Example @{var db = Database.Open("SmallBakery"); var selectQueryString = "SELECT * FROM Product ORDER BY Name"; }  Small Bakery Products  Id Product Description Price T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-18@foreach(var row in db.Query(selectQueryString)){ @row.Id @row.Name @row.Description @row.Price  }  Output of the above ExampleIdProductDescriptionPrice1BreadBaked fresh every day2.992Strawberry CakeMade with organic strawberries9.993Apple PieSecond only to your mom's pie12.994Pecan PieIf you like pecans, this is for you10.995Lemon PieMade with the best lemons in the world11.996CupcakesYour kids will love these9.99T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-19Small Bakery ProductsUsing The WebGrid HelperUsing the WebGrid helper is an easier way to display data from Database.The WebGrid helper:Automatically sets up an HTML table to display dataSupports different options for formattingSupports paging through dataSupports Sorting by clicking on column headingsT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-20WebGrid Example@{ var db = Database.Open("SmallBakery") ; var selectQueryString = "SELECT * FROM Product ORDER BY Name"; var data = db.Query(selectQueryString); var grid = new WebGrid(data); }  Displaying Data Using the WebGrid Helper   Small Bakery Products  @grid.GetHtml()  T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-21WebGrid Example - OutputT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-22Small Bakery ProductsIdNameDescriptionPrice1BreadBaked fresh every day2,993Pecan PieIf you like pecans, this is for you12,992Strawberry CakeMade from organic strawberries9,994Lemon PieMade with the best lemons in the world11,995CupcakesYour kids will love these7,99The Chart HelperThe "Chart Helper" can display chart images of different types with many formatting options and labels.It can create standard charts like area charts, bar charts, column charts, line charts, and pie charts, along with more specialized charts like stock charts.The data you display in a chart can be from an array, from a database, or from data in a file.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-23The Chart Helper – Chart from an ArrayExample: code needed to display a chart from an array of values:@{ var myChart = new Chart(width: 600, height: 400)    .AddTitle("Employees")    .AddSeries(chartType: "column",      xValue: new[] { "Peter", "Andrew", "Julie", "Mary", "Dave" },       yValues: new[] { "2", "6", "4", "5", "3" })    .Write();} EmployeeT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-24The Chart HelperT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-25new Chart creates a new chart object and sets its width and heightthe AddTitle method specifies the chart titlethe AddSeries method adds data to the chartthe chartType parameter defines the type of chartthe xValue parameter defines x-axis namesthe yValues parameter defines the y-axis valuesthe Write() method displays the chart WebPages EmailASP.NET Web Pages The WebMail HelperThe WebMail Helper makes it easy to send an email from a web application using SMTP (Simple Mail transfer Protocol).Scenario: Email SupportTo demonstrate the use of email, we will create an input page for support, let the user submit the page to another page, and send an email about the support problem.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-27First: Edit Your AppStart Page Add in the page called _AppStart.cshtml (already learnt) contain the following contents:_AppStart.cshtml@{WebSecurity.InitializeDatabaseConnection("Users", "UserProfile", "UserId", "Email", true);}T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-28First: Edit Your _AppStart Page Add the following entries:SmtpServer: The name the SMTP server that will be used to send the emails.SmtpPort: The port the server will use to send SMTP transactions (emails).EnableSsl: True, if the server should use SSL (Secure Socket Layer) encryption. False otherwiseUserName: The name of the SMTP email account used to send the email.Password: The password of the SMTP email account.From: The email to appear in the from address (often the same as UserName).T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-29First: Edit Your AppStart Page How to add the WebMail properties to AppStart page:_AppStart.cshtml@{WebSecurity.InitializeDatabaseConnection("Users", "UserProfile", "UserId", "Email", true);WebMail.SmtpServer = "smtp.example.com";WebMail.SmtpPort = 25;WebMail.EnableSsl = false;WebMail.UserName = "support@vcomsats.edu.pk";WebMail.Password = “******************";WebMail.From = “user@vcomsats.edu.pk";}T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-30Second: Create an Email Input PageThen create an input page, and name it Email_Input:Email_Input.cshtml   Request for Assistance  Username:Details about the problem:     The purpose of the input page is to collect information, then submit the data to a new page that can send the information as an email.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-31Third: Create An Email Send PageThen create the page that will be used to send the email, and name it Email_Send:Email_Send.cshtml@{ // Read inputvar customerEmail = Request["customerEmail"];var customerRequest = Request["customerRequest"];try{// Send email WebMail.Send(to:" support@vcomsats.edu.pk ", subject: "Help request from - " + customerEmail, body: customerRequest ); }catch (Exception ex ){@ex }} T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-32WebPages PublishASP.NET Web Pages - Publishing the WebsitePublish Your Application Without Using WebMatrixAn ASP.NET Web Pages application can be published to a remote server by using the Publish commands in WebMatrix (or Visual Studio).This function copies all your application files, cshtml pages, images, and all the required DLL files for Web Pages, for Razor, for Helpers, and for SQL Server Compact (if a database is used).T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-34Publish Your Application Without Using WebMatrixTo perform a web copy, you need to know how to include the right files, what DDL files to copy, and where store them.Follow these steps:1. Use the Latest Version of ASP.NETBefore you continue, make sure your hosting computer runs the latest version of ASP.NET (4.0 or 4.5).2. Copy the Web FoldersCopy your website (all folders and content) from your development computer to an application folder on your remote hosting computer (server).T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-35Publish Your Application Without Using WebMatrix3. The DLL FilesMake sure the bin folder, on your remote hosting computer, contains the same dll files as on your development computer.After copying the bin folder, it should contain files like this: Microsoft.Web.Infrastructure.dllNuGet.Core.dllSystem.Web.Helpers.dllSystem.Web.Razor.dllSystem.Web.WebPages.Administration.dllSystem.Web.WebPages.Deployment.dllSystem.Web.WebPages.dllSystem.Web.WebPages.Razor.dllWebMatrix.Data.dllWebMatrix.WebDataT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-36Publish Your Application Without Using WebMatrix4. Copy Your DataIf your application contains data or a database. For instance an SQL Server Compact database (a .sdf file in App_Data folder), consider the following:Do you want to publish your test data to the remote server?Usually not. (We do testing locally before publishing)If you have test data on your development computer, it may overwrite production data on your remote hosting computer.If you have to copy an SQL database (.sdf file), you may delete everything in the database, and then copy the empty .sdf file from your development computer to the server. T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-37The End ASP.NETT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-38Introduction to SQLT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-39What is SQL?SQL stands for Structured Query LanguageSQL lets you access and manipulate databasesSQL is an ANSI (American National Standards Institute) standardT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-40What Can SQL do?SQL can execute queries against a databaseSQL can retrieve data from a databaseSQL can insert records in a databaseSQL can update records in a databaseSQL can delete records from a databaseSQL can create new databasesSQL can create new tables in a databaseSQL can create stored procedures in a databaseSQL can create views in a databaseSQL can set permissions on tables, procedures, and viewsT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-41Using SQL in Your Web SiteTo build a web site that shows data from a database, you will need:An RDBMS database program (i.e. MS Access, SQL Server, MySQL)To use a server-side scripting language, like PHP or ASPTo use SQL to get the data you wantTo use HTML / CSST2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-42RDBMSRDBMS stands for Relational Database Management System.RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.The data in RDBMS is stored in database objects called tables.A table is a collection of related data entries and it consists of columns and rows.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-43Database TableA database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.In this Lecture we will use the sample database picked up from W3schools Tutorial.Below is a selection from the "Customers" table:T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-44CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22SwedenSQL StatementsMost of the actions you need to perform on a database are done with SQL statements.The following SQL statement selects all the records in the "Customers" table:ExampleSELECT * FROM Customers;Keep in Mind That...SQL is NOT case sensitive: select is the same as SELECTT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-45The Most Important SQL CommandsSELECT - extracts data from a databaseUPDATE - updates data in a databaseDELETE - deletes data from a databaseINSERT INTO - inserts new data into a databaseCREATE DATABASE - creates a new databaseALTER DATABASE - modifies a databaseCREATE TABLE - creates a new tableALTER TABLE - modifies a tableDROP TABLE - deletes a tableCREATE INDEX - creates an index (search key)DROP INDEX - deletes an indexT2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-46The SQL SELECT StatementThe SELECT statement is used to select data from a database.The result is stored in a result table, called the result-set.SQL SELECT SyntaxSELECT column_name,column_name FROM table_name;SELECT * FROM table_name;T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-47Semicolon after SQL Statements?Some database systems require a semicolon at the end of each SQL statement.Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.Use semicolon at the end of each SQL statement.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-48Demo DatabaseBelow is a selection from the "Customers" table:A sample database.T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-49CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22SwedenSELECT Column ExampleThe following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:ExampleSELECT CustomerName,City FROM Customers;SELECT * The following SQL statement selects all the columns from the "Customers" table:SELECT * FROM Customers;T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-50The SQL SELECT DISTINCT StatementIn a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.The DISTINCT keyword can be used to return only distinct (different) values.SQL SELECT DISTINCT SyntaxSELECT DISTINCT column_name,column_nameFROM table_name;OrSELECT DISTINCT City FROM Customers;The above SQL statement selects only the distinct values from the "City" columns from the "Customers" table:T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-51The SQL WHERE Clause The WHERE clause is used to extract only those records that fulfill a specified criterion.SQL WHERE SyntaxSELECT column_name,column_name FROM table_name WHERE column_name operator value;ExampleSELECT * FROM Customers WHERE Country='Mexico';The above SQL statement selects all the customers from the country "Mexico", in the "Customers" table:T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-52Text Fields vs. Numeric FieldsSQL requires single quotes around text values (most database systems will also allow double quotes).However, numeric fields should not be enclosed in quotes:ExampleSELECT * FROM Customers WHERE CustomerID=1;T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-53Operators in The WHERE ClauseThe following operators can be used in the WHERE clause:T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-54OperatorDescription=Equal Not equal. Note: In some versions of SQL this operator may be written as !=> Greater than=Greater than or equal<=Less than or equalBETWEENBetween an inclusive rangeLIKESearch for a patternINTo specify multiple possible values for a columnThe SQL AND & OR OperatorsThe AND operator displays a record if both the first condition AND the second condition are true.The OR operator displays a record if either the first condition OR the second condition is true.AND Operator ExampleSELECT * FROM Customers WHERE Country='Germany‘ AND City='Berlin';The above SQL statement selects all customers from the country "Germany" AND the city "Berlin", in the "Customers" table:T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-55OR Operator ExampleExampleSELECT * FROM Customers WHERE City='Berlin‘ OR City='München';The above SQL statement selects all customers from the city "Berlin" OR "München", in the "Customers" table: T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-56Combining AND & ORYou can also combine AND and OR (use parenthesis to form complex expressions).ExampleSELECT * FROM Customers WHERE Country='Germany'AND (City='Berlin' OR City='München');The above SQL statement selects all customers from the country "Germany" AND the city must be equal to "Berlin" OR "München", in the "Customers" table:T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-57The SQL ORDER BY KeywordThe ORDER BY keyword is used to sort the result-set by one or more columns.The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.SQL ORDER BY SyntaxSELECT column_name,column_name FROM table_nameORDER BY column_name,column_name ASC|DESC;Example:SELECT * FROM Customers ORDER BY Country;The above SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-58ORDER BY DESC ExampleExample SELECT * FROM Customers ORDER BY Country DESC;The above SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:ORDER BY Several Columns ExampleExampleSELECT * FROM Customers ORDER BY Country,CustomerName;The above SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column:T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-59The SQL INSERT INTO StatementThe INSERT INTO statement is used to insert new records in a table.SQL INSERT INTO SyntaxIt is possible to write the INSERT INTO statement in two forms.The first form does not specify the column names where the data will be inserted, only their values:INSERT INTO table_nameVALUES (value1,value2,value3,...);The second form specifies both the column names and the values to be inserted:INSERT INTO table_name (column1,column2,column3,...)VALUES (value1,value2,value3,...);T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-60INSERT INTO ExampleAssume we wish to insert a new row in the "Customers" table.We can use the following SQL statement:ExampleINSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-61Insert Data Only in Specified ColumnsIt is also possible to only insert data in specific columns.The following SQL statement will insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns (and the CustomerID field will of course also be updated automatically):ExampleINSERT INTO Customers (CustomerName, City, Country)VALUES ('Cardinal', 'Stavanger', 'Norway');T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-62The SQL UPDATE StatementThe UPDATE statement is used to update existing records in a table.SQL UPDATE SyntaxUPDATE table_nameSET column1=value1,column2=value2,...WHERE some_column=some_value;SQL UPDATE ExampleAssume we wish to update the customer "Alfreds Futterkiste" with a new contact person and city.We use the following SQL statement: UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg‘ WHERE CustomerName='Alfreds Futterkiste';T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-63Update Warning!Be careful when updating records. If we had omitted the WHERE clause, in the example above, like this:UPDATE CustomersSET ContactName='Alfred Schmidt', City='Hamburg';T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-64The SQL DELETE StatementThe DELETE statement is used to delete rows in a table.SQL DELETE SyntaxDELETE FROM table_name WHERE some_column=some_value;SQL DELETE ExampleAssume we wish to delete the customer "Alfreds Futterkiste" from the "Customers" table.We use the following SQL statement:DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-65Delete All DataIt is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:DELETE FROM table_name;orDELETE * FROM table_name;Note: Be very careful when deleting records. You cannot undo this statement!T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-66EndASP.NET Part IIIIntroduction to SQLThank YouVisual Studio Express 2012/2010Visual Studio Express is a free version of Microsoft Visual Studio.Visual Studio Express is a development tool tailor made for Web Forms (and MVC).Visual Studio Express contains:MVC and Web FormsDrag-and-drop web controls and web componentsA web server language (Razor using VB or C#)A web server (IIS Express)A database server (SQL Server Compact)A full web development framework (ASP.NET)Vista or XP)T2-Lecture-12 Ahmed Mumtaz Mustehsan www.w3schools.com1-68