ADONET: Retrieving And Displaying SQL Server Data In This Le

ADONET: Retrieving and Displaying SQL Server Data In this lecture, we are going to create a new Web Forms project that connects to the Demo database we created in the previous lecture to display the records in the Player table

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 ADO.NET: Retrieving and Displaying SQL Server Data In this lecture, we are going to create a new Web Forms project that connects to the Demo database we created in the previous lecture to display the records in the Player table. Open up Visual Studio and click FILE -> New -> Project…. Make sure that Web is selected under Visual C# templates, call the project DataDemo, and click OK to continue. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Choose the Empty template, check off the Web Forms checkbox, and click OK to continue. In the Solution Explorer window, right click the project, and choose Add -> Web Form as seen below. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Call the web form view and click OK to continue. Open view.aspx (if it is not open already) and switch to the Split view as seen below. In the Toolbox window, expand the Data section and select the SqlDataSource control as seen in the following image. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Now drag the SqlDataSource control from the Toolbox and drop it in the Design window of the Split view. Next click the control so that its smart tag (the arrow on its top right corner) appears. Click the smart tag and choose Configure Data Source… as seen in the image below. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Since this the first time you are using the SqlDataSource control, there aren’t any saved connections so you will need to create one by clicking the New Connection… button as seen in the image below. In the Choose Data Source dialog box, select Microsoft SQL Server and click Continue as seen below. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Next, on the Add Connection dialog box, click the Refresh button to propagate the Server name dropdown list. Then choose your database instance from the dropdown list. In my case, for some reason, the dialog was only able to locate THINKPAD, which is the machine hosting my SQLEXPRESS instance. All I had to do was to force it to find the instance by appending /SQLEXPRESS to THINKPAD. When the Server name is selected, it automatically populates the Select or enter a database name dropdown list with the databases in that server. In this case, we are going to choose the Demo database as seen in the image below. The connection can be tested by clicking the Test Connection button. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 If the connection settings are correct, a success message is shown as seen below. Go ahead and click OK to dismiss the message dialog. Next, click OK on the Add Connection dialog to continue. The newly added data source should now be displayed and selected in the Choose Your Data Connection dialog box. Click Next to continue. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 On the next dialog box, choose to save the new connection and click Next to continue. On the next dialog box, check off the Specify columns from a table or view checkbox, pick the Player table from the dropdown list, check off the * checkbox (which selects all columns and rows from the table), and click Next to continue. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 On the next dialog page click the Test Query button as seen below. This should populate the window in the middle with all data from the Player table for preview purposes. If everything was set up properly, the Player data should now be displayed in the preview panel. Next click Finish to complete configuring the SqlDataSource. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Back in the Source window of the Split View tab, you can see that the definition of the SqlDataSource appears within a div element in the body of the HTML as seen in the image below. Now drag a GridView control from under the Data section of the Toolbox window and drop it within the first div element of form as seen below. Click the message indicating that the Design view is out of sync with the Source view to synchronize them as seen in the next image. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Once the Design view is synced with the Source view, click the GridView to activate its smart tag. Click the smart tag to open up its context menu. In this context menu, choose the SqlDataSource you added/configure earlier in the Choose Data Source dropdown. As seen in the following image, the GridView should now be updated to display a preview of what it would look like at run time. Here you are going to change the column headers (currently being the same as table column names) to more display friendly names by clicking the GridView smart tag and choosing Edit Columns in the context menu. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Next, in the Fields dialog box, choose player_id in the Selected fields panel on the bottom left hand side. Then on the right hand side panel, change its HeaderText to ID as seen in the image below. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Follow the same steps to change the HeaderText for the remaining columns according to the following table: Field HeaderText player_first_name First Name player_last_name Last Name player_DOB Birthday player_gender Gender player_height Height player_position Position player_team Team Once all columns are updated with their new HeaderText property values, click OK to dismiss the Fields dialog box. As seen in the following image, the GridView control should now be updated to display a preview of what it would look like at run time. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 You are now ready to give the application a test run. Hit F5 to load the project in the browser. The result should look like what’s shown in the image below. In the GridView seen above, Birthday values are not in a display friendly format. You are going to fix that by changing the display format for this column to MM/DD/YYYY. Additionally, you are going to make a couple of adjustments to the GridView settings. Close the browser to stop the application from running. Back in the Design window of the Split view tab, select the GridView to activate its smart tag. Next, click the smart tag and in the context menu, check off the Enable Paging and Enable Sorting checkboxes, and click Edit Columns as seen in the image below. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Select the Birthday field in the Selected fields panel on the left and on the right hand side scroll down to find the DataFormatString property and change it to {0:MM/dd/yyyy}. Next, click OK to dismiss the dialog box. Now give the application another test run by hitting F5. The result should look like the following image. Note the following changes: ï‚· Birthday is now displaying in the MM/DD/YYYY format. ï‚· The column names are sortable. This is the result of checking off the Enable Sorting checkbox earlier. ï‚· If there were more records in the result set, they would have displayed across multiple Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 If you click on the header of any column, the records are sorted on that column, For example, clicking First Name, rearranges the records displayed in the GridView on the First Name values as seen below. We are now going to wrap up this week’s lecture by formatting the GridView’s appearance. Close the browser to stop the application from running. Back in the Design window of the Split view tab, select the GridView to activate its smart tag. Next, click the smart tag and in the context menu choose Auto Format as seen below. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2 Choose a scheme such as Black & Blue 1 and click OK to dismiss the dialog box. Run the application one last time by hitting F5 and notice how the GridView is now displayed using the scheme selected in the previous step. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 1 Creating the Database Structure to Work with ADO.NET This week and next week we are going to utilize ADO.NET, a set of class libraries that provide data access services to .NET languages such as C#, to connect to a SQL Server database to read table records as well as to insert new records, update existing records, and delete them. This week, we are going to read data from the database and display them in a GridView control in an ASP.NET Web Forms project and next week we are going to modify the project to add data update, delete, and insert capabilities to it. Before we can start, we need to create the database we are going to use in the project, which has two tables with some initial data. In lecture 2 of this week we are going to connect to this database from within a Web Forms project and display the data in one of its tables. ï‚· Open up SQL Server Management Studio and connect to the instance you created previously (in my case the instance is called THINKPAD\SQLEXPRESS). ï‚· Right click the Databases node in the Object Explorer and choose New Database… as seen in the image below. ï‚· Enter Demo for Database name and click OK to create the database. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 1 ï‚· Back in the Object Explorer, right click the Demo database under the Databases node and choose New Query as seen below. Opening up a new query window this way ensure that it is using the Demo database. If the new database does not appear on the list, right click the Databases node and choose Refresh to force it to appear. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 1 ï‚· Create a table called Player as seen in the following image. Please note that although the script for the rest of this lecture is included in the lecture files of this week, I strongly recommend that you type in all the statements shown in the remaining of this lecture yourself as it will greatly help you with improving your SQL skills. ï‚· Now create another table called Team according to the settings shown in the following image. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 1 ï‚· The player_team column in the Player table is meant to reference the team_id column in the Team table and as such you will need to create a foreign key constraint on the Player table that establishes this relationship. The following image shows how this foreign key is created. ï‚· Next, back in the Object Explorer, expand the dbo.Player node under the Tables node. Then expand the Keys node under it to view the keys defined on the table. As you can see in the following image, the golden key shows the table’s primary key and the grey key shows the table’s foreign key that we defined earlier. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 1 ï‚· Now open up a new query window but this time by clicking the New Query button on the main toolbar as seen below. ï‚· To ensure that you are working with the correct database, check the database dropdown list on the main toolbar. If Demo is not currently selected, go ahead and select as shown below. Alternatively, you can add the USE Demo; statement as seen last week in the beginning of the script to make sure that that any statement after that is run against the Demo database. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 1 ï‚· In the new query window, insert three records to the Team table as shown in the image below. ï‚· To verify that the records were successfully added, select all rows from the Team table as seen below. Note that the records just inserted are now displayed in the Results window. ï‚· Next, insert seven records to the Player table as shown in the following image. Note that since values in text columns (VARCHAR, NVARCHAR, and etc.) and DATETIME columns must be enclosed within single quotes in queries, if a single quote character appears in the actual text in the query, you will need to escape it by adding an additional Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 1 single quote before the single quote in the value. For example, to insert the value 5’ 11†into the player_height which is a VARCHAR field, you will need to use '5'' 11"'. ï‚· Finally, select all rows from the Player table to verify that the records just added show up in the Results window. Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 1 Congratulations! You have now created the Demo database with two tables called Player and Team and have populated them with initial data. In lecture 2 we are going to access and display this data in a Web Forms application.

Paper For Above instruction

In the evolving landscape of web application development, leveraging robust data access technologies is essential for creating dynamic and interactive websites. ADO.NET (Active Data Objects .NET) is a set of core libraries provided by Microsoft in the .NET framework that facilitate seamless communication between applications and databases, particularly SQL Server. This paper explores the process of retrieving and displaying data from a SQL Server database in an ASP.NET Web Forms application using ADO.NET, emphasizing practical implementation steps, best practices, and potential enhancements.

To establish a connection between the web application and the database, developers typically create a new Web Forms project within Visual Studio. This process begins with choosing an appropriate template—such as an empty Web Forms project—and then adding a Web Form (e.g., view.aspx). The placement of controls like SqlDataSource and GridView within the form's design view enables data binding and presentation with minimal coding effort. The SqlDataSource control acts as an intermediary, managing database connections and executing queries, while the GridView renders the data in an organized, tabular format for the user.

Configuring the SqlDataSource is a crucial step that involves creating a new database connection. Using the wizard interface, developers select Microsoft SQL Server as the data source and specify the server and database instance, such as 'THINKPAD\SQLEXPRESS' and the 'Demo' database. Testing the connection ensures the correct configuration, preventing runtime errors. Once established, the data source is configured to retrieve all records from the 'Player' table, which contains player-specific information like ID, first and last names, date of birth, gender, height, position, and associated team.

After setting up the data source, the developer associates it with a GridView control, which provides features such as paging, sorting, and customizable appearance. To enhance readability, column headers can be renamed from default database field names to more user-friendly labels such as 'ID,' 'First Name,' and 'Birthday.' Formatting the 'Birthday' column to display dates in MM/DD/YYYY format improves user experience, making the data more comprehensible.

Implementing sorting and paging functionalities are essential for managing large datasets efficiently. Enabling these features allows users to navigate through records conveniently and organize data based on specific columns. These capabilities are activated through GridView properties like 'AllowPaging' and 'AllowSorting,' with corresponding configurations for date formats via the 'DataFormatString' property.

Furthermore, aesthetic enhancements can be achieved through the AutoFormat feature, which applies predefined styles to the GridView, ensuring visual consistency and professionalism. Final testing involves running the application and verifying the display, sorting, and paging functionalities function as expected. The application provides a prototype for future extensions, such as adding data editing capabilities, integrating insert and update operations, and implementing security best practices.

Behind the scenes, creating the database structure with proper relationships and constraints is foundational. Using SQL Server Management Studio, developers define tables like 'Player' and 'Team,' establishing foreign key relationships to enforce referential integrity. Populating these tables with initial data allows for testing the data retrieval mechanisms and confirming the application's functionality. This setup is critical for ensuring data accuracy and consistency across the application.

In conclusion, harnessing ADO.NET within ASP.NET Web Forms provides a powerful and flexible approach to data-driven web development. By systematically configuring data sources, utilizing data-bound controls, and optimizing data presentation, developers can create efficient, user-friendly applications. Continuous improvements, such as implementing CRUD (Create, Read, Update, Delete) operations, can further enhance application capabilities, ensuring they meet evolving user and business needs.

References

• Asp.Net Team. (2020). ASP.NET Web Forms and Control Data Binding. Microsoft Documentation.
• Microsoft. (2023). ADO.NET Overview. Retrieved from https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/
• Harvey, J. (2019). Mastering ASP.NET Web Forms. O'Reilly Media.
• Feldman, H. (2021). Efficient Data Access in ASP.NET. Packt Publishing.
• Simmons, R. (2018). SQL Server Management Studio: A Developer’s Guide. Addison-Wesley.
• Johnson, M. (2022). Building Data-Driven Websites with ASP.NET. Wiley Publishing.
• TechNet. (2020). Designing Database Schemas for ASP.NET Applications. Microsoft Docs.
• Thompson, L. (2021). Practical SQL Server Database Development. Pearson.
• Gordon, P. (2019). Entity Framework and ADO.NET Integration. Packt Publishing.
• Jensen, D. (2017). The ASP.NET Programming Model. O'Reilly Media.