SQL Server Database Web Applications

22 

Loading....

Loading....

Loading....

Loading....

Loading....

Full text

(1)

Microsoft Visual Studio (as well as Microsoft Visual Web Developer) uses a variety of built-in tools for creating a database-driven web application. In addition to the SQL Server database, there are programming elements which might be written in Visual C# or some other language, page content mixed with HTML-style and actual HTML tags, and in-line Structure Query Language which are SQL statements embedded into the code. Also Visual Studio is able to manipulate a wide variety of other database types including Microsoft Access, Oracle, etc.

Start by selecting NewWeb Site… from the File menu to create a new ASP.NET Web Site.

The “New Web Site” dialog window opens in which “Visual C#” should be selected from the “Installed Templates” and “ASP.NET Empty Web Site” should be selected as the project type. Browse… to find the “Save” location for the website and give it a name, e.g. “PremiereWebsite” which is the name given to the folder below that will contain the files for the site. (In this course you simply will download the existing "Premiere Products" website from the instructor's website and "Open" it.)

(2)

Master Pages

An ASP.NET Master Page is a single document that allows for a consistent layout for all the pages in an ASP.NET Web Site. When the individual content pages for the Web site are created, they contain the content you want to display. When users request an actual Web page, the Master Page is merged with the content page to produce output that combines the Master Page layout with the content.

To create a new Master Page:

1. Right-click the project name on the second line in the “Solution Explorer” window 2. Select Add New Item… from the drop-down menu

3. Select Master Page from the “Template” pane of the “Add New Item” dialog window

4. Enter a name for the new Master Page (if the “.master” extension is not entered, it will be added automatically by Visual Web Developer)

5. Click the <Add> button to create the new Master Page

The Master Page can be linked to a Web page when a new Web Form is created (this will be discussed later in this lesson). In this course we will not actually be learning to create Master Pages. You either will be given a Master Page file to copy into the website or code to copy and paste into the Master Page.

CSS (Cascading Style Sheets)

A Cascading Style Sheet (CSS) is a list of statements (or “rules”) that can format various HTML elements. It is a component that is common to all HTML documents and not unique to Visual Studio alone. Style “rules” can be specified for multiple Web pages all at once from a single Style Sheet document. The functioning of the Style Sheet is similar to that of master pages in that these “rules” are collected and merged thereby formatting the Web document when it is opened (this also will be discussed later in this lesson).

To create a new Style Sheet:

1. Right-click the project name on the second line in the “Solution Explorer” window 2. Select Add New Item… from the drop-down menu

3. Select Style Sheet from the “Template” pane of the “Add New Item” dialog window

4. Enter a name for the new Style Sheet (if the “.css” extension is not entered, it will be added automatically by Visual Web Developer)

5. Click the <Add> button to create the new Style Sheet

Again in this course we will not be learning to create Style Sheets. In this course you either will be given a Style Sheet file or code to copy and paste into the Style Sheet.

(3)

Web Forms

Web Forms are the central component of an ASP.NET application. They provide all the content of a Web page. Additionally through the program code (e.g. Visual C#) they provide the dynamic content that allows the Web document to connect to and interact with a database. When the Web Form is requested, it is converted into an HTML document (a Web page) to be viewed in a browser (e.g. Microsoft Internet Explorer, Mozilla Foxfire, Opera, etc.).

To create a new Web Form:

1. Right-click the project name on the top line in the “Solution Explorer” window 2. Select Add from the drop-down menu

3. Select Add New Item… from the "Add" sub-menu

4. Select Web Form from the “Template” pane of the “Add New Item” dialog window

5. Enter a name for the new Web Form (if the “.aspx” extension is not entered, it will be added automatically by Visual Studio)

6. Set any other options for the Web Form by clicking the CheckBoxes “on” or “off” 7. Click the <Add> button to create the new Web Form

The CheckBox “Place code in separate file” allows the developer to place the Visual C# code into a separate file with the extension “aspx.cs”. In simpler Web applications like those we are doing for this course, we will keep the code in the same file to make it easier to find, so leave this CheckBox “unchecked”.

(4)

As mentioned before, the Master Page can be linked to the new Web Form when it first is created. If the “Select master page” CheckBox was set “on” (see “Add New Item” dialog window image on previous page) prior to clicking the <Add> button, the “Select a Master Page” dialog window opens. Click the Master Page that is to be linked to the Web Form and then click the <OK> button.

This also might be the time to link the Web Form to a Style Sheet. To do this select the

command Attach Style Sheet… from the Format menu. In the “Select Style Sheet” dialog window select the “Project folder” that contains the style sheet. The from the “Contents of folder” window select the name of the style sheet and click the <OK> button.

(5)

The GridView Control

One way to insert content onto the page might be by coding. However because the Visual Studio IDE (integrated development environment) interface is a GUI (graphical user interface) application, there are alternate ways content can be placed on the page more easily. Notice that there are three tabs at the lower-left of the IDE window, [Design], [Split], and [Source]. Make certain that the [Design] tab is clicked to work with the GUI interface.

The GridView control is a powerful tool for displaying data from a DataSource. For purposes of this course a lDataSource (which actually is also a control in Visual Studio) is a table or SQL query (View) from a SQL Server database. The data from the database is displayed in a grid of rows and columns similar to a spreadsheet. Each row in the grid is one of the records from the table. Each column is data from one of the fields with the fieldname displayed in the first row.

The simplest way to create a new GridView on a Web Form is to drag the table or query icon from “Server Explorer” directly onto the Web Form document. When a Web Form with a GridView control is executed, the table data is displayed without the need to perform any other configuration operations other than what was just specified.

There are additional GridView elements that are configurable. Click on the control and click the “smart tag” button ( ) at the upper-right corner of the control. The “GridView Tasks” dialog window opens. Click “on” the following checkboxes to:

1. “Enable Paging” which creates numbered button in the GridView to advance to other pages for longer tables (e.g. click the page number to go to a specific page)

2. “Enable Sorting” which enables sorting by any field in the table (click the column name heading at the top of any column to sort on that field; click a second time on the same column name to sort in descending sequence)

(6)

3. “Enable Editing” which creates an <Edit> button that allows users to update records directly in the GridView control (after clicking <Edit>, click either the displayed <Update> button to make the changes to a record or the <Cancel> button to cancel any updates)

4. “Editing Deleting” which creates a <Delete> button that allows users to delete records directly from the GridView control

To configure the visual appearance of the GridView, click on the Auto Format… command in the “GridView Tasks” dialog window. From the “Auto Format” dialog window “Select a scheme”, “Preview” it and click the <OK> button.

(7)

Another characteristic of this control that can be set is its horizontal alignment (it can be centered on the Web Form). Click on the GridView, scroll to HorizontalAlign in the “Properties” window, click the arrow for the property and select “Center” from the dropdown list.

One of the advantages of creating a GridView control this way is that a number of related processes are completed automatically including the configuration of the SQLDataSource control (which also appears on the Web Form in design view but is not in the rendered Web page) and the creation of the ConnectionString. The ConnectionString is needed when trying to connect to the database when writing programming code in Visual C# in the Code Editor (more about this later).

Running a Visual Studio ASP.NET Application

“Running” an application created in Visual Studio means to view it in a Web browser (e.g. Internet Explorer, Firefox, Chrome, etc.) and to interact with its functionality. To view and test a Web Form application, begin by clicking the “Start Debugging” button on the “Standard toolbar”.

The “Debugging Not Enabled” dialog window will display the first time a user “runs” a new Web application. Accept the default answer “Modify the Web.config file to enable debugging” since you “are” debugging the application and click the <OK> button.

(8)

At this point if there are no errors in the Web Form, a new Web browser window will open and the page will be rendered so that it can be viewed. Test the elements of the page and when finished close the browser window.

Designing Web Forms

Now create another Web Form named “NewRep.aspx”. Make certain that the [Design] tab is clicked to work with the GUI interface.

To develop Web Forms actual text simply can be typed onto the page. Additionally other visual elements such as TextBoxes, Buttons and Labels can be inserted by selecting them from the Toolbox (see the example below). Use the buttons on the Formatting toolbar to format elements on the Web Form using font, text color, alignment, etc.

Unlike standard Visual C# desktop projects, controls from the Toolbox cannot be placed on the Web Form wherever you want them. The elements on the page “flow” from left to right, and top to bottom, as they are inserted much like when using a word processor. Pressing the <Enter> key double-spaces to the beginning of the next line. “Absolute” positioning of elements on the page requires more know-how than we have time for this course.

(9)

Visual Controls, the Toolbox and the Properties Window

The Visual Web Developer Toolbox, usually “docked” inside the left edge of the IDE window, displays icons which represent the visual elements that can be inserted onto the Web Form. Click the mouse on the Toolbox tab and it “pops out” from the edge of the window. Each icon can be dragged and dropped onto the development page.

The TextBox is a control that lets users viewing your Web page type information into the page. Click on the control in the Toolbox (found in the “Standard” group or section) and drag it onto the Web Form. There are “handles” at the right and bottom of the control that can be dragged to resize it. In a Web application the text typed into the TextBox might be used as field information in a new or updated record.

The Button control is used to display a push button that is clicked by the user. It may either be a submit button that submits page information back to the server or a command button. If it is a command button, it will be associated with a specific event such as the Click event. In this case, when the user “clicks” it, it executes the statements written by the developer in an Event handler coded in either Visual Basic or Visual C# (more on this later).

A Label control allows the developer to place “unattached” text on the Web Form. Its Text property specifies the text that is displayed.

Characteristics of the visual controls are known as its properties. The two most important properties for most controls are the ID and Text properties. The ID property actually is a “name” (its identifier) given to the control (in fact in Visual Studio “Windows” desktop applications it is called the Name property). Whenever referring to the control in the Code Editor, the developer must use its ID property. The Text property is a text label that appears on, in, or near the control.

(10)

A control’s properties are updated in a window known as the “Properties” window located in the lower right area of the Visual Studio IDE. Properties may be arranged in “alphabetical” ( ) order or “categorized” ( ) together in related groups (there are two buttons near the top of the Properties window to choose between these two options). Because of its importance, the ID property is the first one in the list when they are listed alphabetically.

Type the updated property value in the textbox to the right of the property name to update it. There are some properties in which the property value instead may be selected from a drop-down list of valid values. Also there are other properties that have an ellipse […] button at the end of the line; when the button is clicked, a dialog window opens with a number of options for selecting the property value.

The ID properties for the TextBoxes in the “NewRep” Web Form are:  RepNumTextBox

 LastNameTextBox  FirstNameTextBox  AddressTextBox  CityTextBox  StateTextBox  ZipTextBox

 CommissionTextBox  RateTextBox

The properties for the Button control in the “NewRep” Web Form are:  ID: SubmitTextBox

 Text: Submit

Finally the properties for the Label control below the Button in the “NewRep” Web Form are:  ID: ErrorMessageLabel

(11)

Another useful property for TextBoxes is the MaxLength property. The MaxLength controls the number of characters that can be typed into a TextBox. By default the value for this property is set to zero (0) which means there is no limit to the number of characters typed. For SQL Server VARCHAR fields, it can be useful to limit the maximum number of characters entered into a TextBox to match the length of the VARCHAR field in SQL Server. For each TextBox type this value on the MaxLength line in the “Properties” window.

The MaxLength properties for the TextBoxes in the “NewRep” Web Form are:  RepNumTextBox: 2

 LastNameTextBox: 15  FirstNameTextBox: 15  AddressTextBox: 30  CityTextBox: 15  StateTextBox: 2

ZipTextBox: 5

Validation Controls

ASP.NET validation controls are used to validate user input. If the data entered into an input control on the Web page is invalid according to a “validation rule” specified in a validation control, an error message will be displayed. A variety of these controls are found in the Toolbox in the “Validation” group.

The RegularExpressionValidator validates that user input for a field matches a specified pattern. The developer can create a string that represents the pattern, although these strings can be rather complex and confusing. However the cool thing about this control is that many common patterns already are built into it. Some of these patterns include Internet e-mail addresses and URL’s, phone numbers, ZIP codes and Social Security numbers.

(12)

To select a built-in pattern, add a RegularExpressionValidator control to the Web Form; it usually will be positioned just to the right of the control it is validating such as a TextBox. Select the control and find the ValidationExpression property in the Properties window. Click the ellipse […] button and the dialog window opens. Select one of the “Standard expressions” and click the <OK> button. Or a ValidationExpression string can be typed directly into the textbox (in this course any string that needs to be typed will be given to you by the instructor).

Two other properties also should be set for all validation controls. The ErrorMessage property is a text message that will display if the validation fails. The ControlToValidate property is the ID (name) of the control that is being validated by this validation control. In the Properties window click the arrow at the end of the line for this property and select the valid control ID from the drop-down list.

Another useful validation control is the RequiredFieldValidator. Validation for this control fails if the input value does not change from its initial value, usually an empty string. The ErrorMessage and ControlToValidate properties are they same for this as for all validation controls.

A third validation control is the RangeValidator control which lets the developer specify minimum and maximum allowable values. The additional properties for this validator are MinimumValue and MaximumValue (the range), as well as Type which specifies the data type being validated, e.g. String, Integer, Double, Date or Currency.

The ValidationSummary control displays a “summary” of the error messages generated for all the validation controls when the page is submitted. In this case the individual messages do not display within the separate validation controls but rather are collected and displayed in this single location, often at the top or bottom of the Web Form.

The validation controls and properties for the “NewRep” Web Form are: ID: RepNumRequiredFieldValidator

 ControlToValidate: RepNumTextBox  Display: Dynamic

 ErrorMessage: ID number may not be blank  ForeColor: Red

(13)

ID: RepNumRegularExpressionValidator  ControlToValidate: RepNumTextBox  ErrorMessage: ID number must be 2 digits  ForeColor: Red

 ValidationExpression: \d{2} ID: LastNameRequiredFieldValidator  ControlToValidate: LastNameTextBox  ErrorMessage: Last name may not be blank  ForeColor: Red

ID: FirstNameRequiredFieldValidator  ControlToValidate: FirstNameTextBox  ErrorMessage: First name may not be blank  ForeColor: Red

ID: StreetRequiredFieldValidator  ControlToValidate: StreetTextBox

 ErrorMessage: Street address may not be blank  ForeColor: Red

ID: CityRequiredFieldValidator  ControlToValidate: CityTextBox  ErrorMessage: City may not be blank  ForeColor: Red

ID: StateRequiredFieldValidator  ControlToValidate: StateTextBox  Display: Dynamic

 ErrorMessage: State may not be blank  ForeColor: Red

ID: StateRegularExpressionValidator  ControlToValidate: StateTextBox

 ErrorMessage: State must be two uppercase letters  ForeColor: Red

 ValidationExpression: ^[A-Z]{2} ID: ZipRequiredFieldValidator  ControlToValidate: ZipTextBox  Display: Dynamic

 ErrorMessage: Zip code may not be blank  ForeColor: Red

(14)

ID: ZipRegularExpressionValidator  ControlToValidate: ZipTextBox

 ErrorMessage: Zip code must be 5 digits  ForeColor: Red

 ValidationExpression: \d{5} (the same as a French postal code) ID: CommissionRegularExpressionValidator

 ControlToValidate: CommissionTextBox  ErrorMessage: Commission must be numeric  ForeColor: Red

 ValidationExpression: ^\d+(\.\d+)?$ ID: RateRangeValidator

 ControlToValidate: RateTextBox

 ErrorMessage: Rate must be between 0.00 and 0.20  ForeColor: Red

 MaximumValue: 0.20  MinimumValue: 0.20  Type: Double

(15)

The Button Control and Event Handling

An event handler is a method (sometimes called a function) that executes when it receives a message (the event) from a device such as the mouse or keyboard. The default event for a Button control is the Click event which occurs when the Button is clicked. The method is a block of code that executes whenever the event (the “click”) occurs.

Format:

protected void ControlName_Event (object sender, EventArgs e) {

(The code that executes when the event occurs goes here) }

To create an event handler for a Button located on a Web Form, double-click the Button. A new method is created automatically in the “Source” window (you can always click the <Design> tab to get back to that window). The method name consists of the name of the object (e.g. “SubmitButton” in the example below) and the event (e.g. “Click” in this example) that results in its execution. It is positioned inside an HTML <script> block, where both server-side and client-side program code is located in an HTML document.

The image above shows a new (but yet to be completed) method that has just been inserted into the “Source” window. An example of the completed method appears on the next page. The method executes whenever the Button named “SubmitButton” located on the “NewRep” Web Form is clicked by a user. The various commands within the example method will be the topic of discussion for the remainder of this tutorial.

(16)

Sample Button Click Event Method: SubmitButton_Click

<script runat="server">

protected void SubmitButton_Click(object sender, EventArgs e)

{

SqlDataSource PremiereDataSource = new SqlDataSource();

PremiereDataSource.ConnectionString =

ConfigurationManager.ConnectionStrings["PremiereConnectionString1"]. ConnectionString;

PremiereDataSource.InsertCommandType =

SqlDataSourceCommandType.StoredProcedure;

PremiereDataSource.InsertCommand = "InsertRep";

PremiereDataSource.InsertParameters.Add("RepNum", RepNumTextBox.Text);

PremiereDataSource.InsertParameters.Add("LastName", LastNameTextBox.Text);

PremiereDataSource.InsertParameters.Add("FirstName", FirstNameTextBox.Text);

PremiereDataSource.InsertParameters.Add("Street", StreetTextBox.Text);

PremiereDataSource.InsertParameters.Add("City", CityTextBox.Text);

PremiereDataSource.InsertParameters.Add("State", StateTextBox.Text);

PremiereDataSource.InsertParameters.Add("Zip", ZipTextBox.Text);

PremiereDataSource.InsertParameters.Add("Commission",

CommissionTextBox.Text);

PremiereDataSource.InsertParameters.Add("Rate", RateTextBox.Text);

int rowsEffected = 0; try

{

rowsEffected = PremiereDataSource.Insert(); }

catch (Exception ex)

{

ErrorMessageLabel.Text = ex.Message; ; }

finally {

PremiereDataSource = null; }

if (rowsEffected != 0) {

Server.Transfer("Rep.aspx");

} } </script>

(Whenever the symbol appears in the code above, it is an indication that a statement is continued on the next line. Most importantly in Visual C# programming never press the <Enter> key in the middle of a string.)

(17)

SqlDataSource Class

The SqlDataSource control provides the functionality for accessing and managing the data in relational databases. When a GridView control is created by dragging a table from a SQL Server database onto a Web Form, a related SqlDataSource control is created automatically and placed onto the form. Although the control appears on the Web Form in “Design View” in Visual Web Developer, the SqlDataSource is not visible in the rendered Web page when it is viewed in a browser window.

A new SqlDataSource object variable is declared (“instantiated”) in one of the first statements in a method that inserts a record into the SQL Server database table. This is a coded object, not visual one.

Format:

SqlDataSourceSqlDataSourceObjectVariableName = new SqlDataSource();

 In the statement above the name SqlDataSource is used in C# is the “data type” (or more properly “type” because it is an object variable) of the variable created

 The keyword new “instantiates” the new variable and makes it an object so that the object variable name “PremiereDataSource” below can be used throughout the rest of the “SubmitButton_Click” method

Example:

(18)

The ConnectionString Property and the “Web.config” File

Additionally when the GridView control is created by dragging a table from a SQL Server database onto the Web Form, another benefit was the automatic creation of a ConnectionString. The connection string stores the path and filename information for the database. Additionally it indicates which type of database the data source control accesses (e.g. SQL Server, Oracle, etc.). This connection string was inserted automatically into the “Web.config” file which appears in the “Solution Explorer” window (double-click on the filename to view the file).

The ConnectionString name given in the “Web.config” file provides an easy and flexible way of assigning the SqlDataSource.ConnectionString property to provide access to a relational database.

Format:

SqlDataSourceObjectVariableName.ConnectionString =

ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString();  ConfigurationManager.ConnectionStrings class returns a “connection object” from

the“connectionString” name

 The ConnectionString() method returns the specific connection string from the “Web.config” file

Example:

PremiereDataSource.ConnectionString =

ConfigurationManager.ConnectionStrings["PremiereConnectionString1"]. ConnectionString();

 Note that the string “PremiereConnectionString1” is the same as the connectionString name in the “Web.config” file.

(19)

InsertCommand and InsertCommandType Properties

The InsertCommandType property is a method of class SqlDataSource. The two valid values for the property, which comes from the SqlDataSourceCommandType class, indicate that the type for the subsequent InsertCommand is one of the following:

1. Text—text contained in the corresponding command is an actual SQL INSERT command string 2. StoredProcedure—text contained in the corresponding command comes from a previously

created SQL Server stored procedure Format:

SqlDataSourceObjectVariableName.InsertCommandType = SqlDataSourceCommandType.CommandType;

 The valid CommandType values are “Text” and “StoredProcedure”, both members of the SqlDataSourceCommandType class

Example:

PremiereDataSource.InsertCommandType = SqlDataSourceCommandType.Text;

The InsertCommand property, also a member of class SqlDataSource, contains a SQL INSERT command (either a text string or a stored procedure name) that will be executed by the Insert() method.

Format:

SqlDataSourceObjectVariableName.InsertCommand = "INSERT INTO tableName

(fieldNameList) VALUES (parameterList)"

 The tableName is the name of the table in the SQL Server database

 The fieldNameList are the names of the fields in the SQL Server database table

 The parameterList is a list of “variables” that hold the values to be written into the new record (this topic will be explained in the next section)

Example:

PremiereDataSource.InsertCommand = "INSERT INTO Rep (RepNum, LastName, FirstName, Street, City, State, Zip, Commission, Rate) VALUES (@RepNum, @LastName, @FirstName, @Street, @City, @State, @Zip, @Commission, @Rate)";

(20)

Alternately i

f the InsertCommandType is set to “StoredProcedure” as per the example below ... PremiereDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure; ... the developer can name a SQL Server database StoredProcedure that previously created that exists in “Server Explorer” e.g. ...

... as the InsertCommand.

PremiereDataSource.InsertCommand = "InsertRep";

Parameters

Parameter objects are “variables” that hold the data that will be inserted into a “parameterized SQL statement”. The InsertParameters.Add() method for a parameter collection creates the parameter variable and associates it with the source from where the data will be retrieved such as the Text property of a TextBox.

Format:

SqlDataSourceObjectVariableName.InsertParameters.Add("parameterName", dataSource);  The parameterName is a name given to the parameter variable within the parameter

collection (customarily the same name as the fieldname although not mandatory)  The dataSource is the location within the application or the system from which the data

to be stored in the parameter variable will be retrieved; it could be the Text property of a TextBox or some other element identified from within the system (e.g. the date and time as represented by the expression DateTime.Now() or the IP address of the server that submitted the data represented by the expression Request.UserHostAddress). Example:

(21)

The Insert() Method

The Insert() method, a member of the class SqlDataSource, executes the InsertCommand and inserts the new record into the SQL Server database table. It returns an integer which indicates how many records were added to the table (should be just one record).

Format:

SqlDataSourceObjectVariableName.Insert(); Example:

rowsEffected = PremiereDataSource.Insert();

 The variable rowsEffected is assigned an integer which is the number of records added to the table (should always be just one in all our examples in this class)

Try … Catch Exception Processing

The C# try … catch structure is a cleaner and more elegant way of handling run-time errors that occur during program execution. This is especially important for the type of errors that could result in a program “crash.” Such a situation technically is known as “throwing an exception” (do not be overly concerned about that terminology in this course) and often results in a rather “ugly error message” that appears in the browser window from which the average user has no way of recovering.

A simple try … catch structure consists of three parts:

1. The try block which contains the executable statements that have the possibility of generating a run-time error

2. The catch block which executes only when a run-time error occurs; it might contain a statement that displays an error message or it could transfer to a Web page that contains the error message or some instructions on how to proceed next

3. The finally block which contains statements that should execute after try … catch processing completes whether or not the run-time error occurs

Format:

try {

(Executable statements that could result in a run-time error) }

catch (Exception exceptionVariable) {

(Statements that execute if a run-time error does occur) }

finally {

(Statements that execute if a run-time error occurs or not) }

(22)

Example: try {

rowsAffected = PremiereDataSource.Insert(); }

catch (Exception ex) {

ErrorMessageLabel.Text = ex.Message; }

finally {

PremiereDataSource = null; }

The Server.Transfer Method

The Server.Tranfer() method opens a new Web page in the browser window. Format:

Server.Transfer("URL");

 The URL is the Web address of the new Web page Example:

Server.Transfer("Rep.aspx");

The null Object

Assigning the object value null to an object destroys the object and makes it available for handling by the operating system’s “garbage collector”. It is a good habit to destroy objects that no longer are needed in an application

Format:

SqlDataSourceObjectVariableName = null; Example:

Figure

Updating...

References

Updating...