{"id":1393,"date":"2025-01-15T07:05:32","date_gmt":"2025-01-15T12:05:32","guid":{"rendered":"https:\/\/pressbooks.bccampus.ca\/nelson\/chapter\/chapter-13-programs-to-work-with-a-database\/"},"modified":"2025-01-16T05:42:21","modified_gmt":"2025-01-16T10:42:21","slug":"chapter13thirdedition","status":"publish","type":"chapter","link":"https:\/\/pressbooks.bccampus.ca\/nelson\/chapter\/chapter13thirdedition\/","title":{"raw":"Chapter 13 Programs to Work with a Database","rendered":"Chapter 13 Programs to Work with a Database"},"content":{"raw":"
\r\n

Original Material to the textbook: Fred Strickland<\/p>\r\n\r\n

Learning Outcomes<\/h2>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
\r\n

Computing Sub Discipline<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Document Code, Reference Code, and Page Number<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Text<\/strong><\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Computer Science<\/p>\r\n<\/td>\r\n

\r\n

CS2013<\/p>\r\n

IM\/Query Languages<\/p>\r\n

(Page 116)<\/p>\r\n<\/td>\r\n

\r\n

4. Create a non-procedural1<\/a><\/sup> query by filling in templates of relations to construct an example of the desired query result. [Usage]<\/p>\r\n

5. Embed object-oriented queries into a stand-alone language such as C++ or Java (e.g., SELECT Col.Method() FROM Object). [Usage]<\/p>\r\n

6. Write a stored procedure that deals with parameters and has some control flow2<\/a><\/sup> , to provide a given functionality. [Usage]<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

CS2023<\/p>\r\n

DM-Querying: Query Construction<\/p>\r\n

(Page 118)<\/p>\r\n<\/td>\r\n

\r\n

CS Core 1b. Programmatic execution of an SQL query<\/p>\r\n

Non-core 6. Different ways to invoke non-procedural queries in conventional languages.<\/p>\r\n

Non-core 8. Stored procedures<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

CS2023<\/p>\r\n

DM-Security: Data Security and Privacy<\/p>\r\n

(Page 121)<\/p>\r\n<\/td>\r\n

\r\n

CS Core 2. Protecting data and database systems from attacks, including injection attacks such as SQL injection<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Data Science<\/p>\r\n<\/td>\r\n

\r\n

DS2021<\/p>\r\n

Programming, Data Structures, and Algorithms (PDA) - Programming<\/p>\r\n

(Pages 112-113)<\/p>\r\n<\/td>\r\n

\r\n

Manipulate data from selected sources (e.g., databases, ... utilizing appropriate techniques (e.g., database queries, API calls, regular expressions).<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

DS2021<\/p>\r\n

SDM-Software Design and Development<\/p>\r\n

(Page 119)<\/p>\r\n<\/td>\r\n

\r\n

Students need to have T1 level of knowledge of<\/p>\r\n

\u2022Integration with Information Management\/Database systems<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Skills<\/p>\r\n

(Page 120)<\/p>\r\n<\/td>\r\n

\r\n

Describe how to integrate or interact with Information Management\/Database Systems.<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Information Systems<\/p>\r\n<\/td>\r\n

\r\n

IS2020<\/p>\r\n

A3.2.1 Competency Area \u2013 Data \/ Information Management<\/p>\r\n

(Page 101)<\/p>\r\n<\/td>\r\n

\r\n

3. Programming database systems using functions and triggers. (Page 103)<\/p>\r\n

4. Secure a database. (Page 103)<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Information Technology<\/p>\r\n<\/td>\r\n

\r\n

IT2017<\/p>\r\n

ITE-IMA Domain: Information Management<\/p>\r\n

(Page 56)<\/p>\r\n<\/td>\r\n

\r\n

E. Perform major database administration tasks such as create and manage database users, roles and privileges, ... to ensure organizational efficiency, continuity, and information security. (Testing and performance)<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

IT2017<\/p>\r\n

ITE-IMA-04 Database query languages [L3]<\/p>\r\n

(Page 92)<\/p>\r\n<\/td>\r\n

\r\n

d. Use embedded SQL queries.<\/p>\r\n

f. Create updatable and non-updatable views.<\/p>\r\n<\/td>\r\n<\/tr>\r\n

<\/td>\r\n<\/td>\r\n<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n

Introduction to Chapter 13<\/h2>\r\n

We have been looking at relational DBMS from a hands-on or application viewpoint. And we have touched upon the different DBMSs. We have looked at the theory for any relational DBMS. We looked under the hood to see how the DBMS functions. In this chapter, we will begin to explore how to create a front end to a DBMS. We will be installing Microsoft\u2019s Visual Studio. We will be using the C# programming language. We will create some simple front-end programs for accessing a DBMS. In the process, we will touch upon secure coding concepts. We will look at tools that are stored in the DBMS such as stored procedures, functions, and triggers. We will wrap up this chapter by exploring how to create views.<\/p>\r\n\r\n

The Third Edition Style Guide<\/h2>\r\n

This is the style guide that this book will follow for this chapter and for the other chapters.<\/p>\r\n\r\n\r\n\r\n\r\n\r\n
\r\n

Naming Convention:<\/p>\r\n\r\n

    \r\n \t
  • All English names<\/li>\r\n \t
  • Plural table names<\/li>\r\n \t
  • Singular column names<\/li>\r\n \t
  • Capitalized words<\/li>\r\n \t
  • Pascal Case for composite names<\/li>\r\n \t
  • No underscores or special characters<\/li>\r\n \t
  • No unique SQL commands<\/li>\r\n \t
  • No object prefixes<\/li>\r\n<\/ul>\r\n

    Using the IE\u2019s Notation with Crow\u2019s Foot Notation.<\/p>\r\n<\/td>\r\n<\/tr>\r\n

<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n

Figure 13.1 The database style guide. Adapted from https:\/\/vertabelo.com\/blog\/database-schema-naming-conventions\/<\/p>\r\n

The examples will conform to the style guide.<\/p>\r\n\r\n

Microsoft\u2019s Visual Studio<\/h2>\r\n

Installing Microsoft\u2019s Visual Studio<\/h3>\r\n

Search for \u201cMicrosoft Visual Studio.\u201d<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.2 Screenshot of a hit for the search phrase \u201cMicrosoft Visual Studio.\u201d<\/p>\r\n

This chapter will be working with Visual Studio instead of Visual Studio Code. In October 2024, the current edition is 20223<\/a><\/sup>. There are three versions.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.3 The Visual Studio download screen with the three versions shown.<\/p>\r\n

The Community edition is free. By clicking on the Community button, the download starts immediately. At the same time, you are taken to an information web page. This is a rich resource that you can explore on your own.<\/p>\r\n

Install the software. At one point, you will see the \u201cWorkloads\u201d screen.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.4 The Workloads screen.<\/p>\r\n

Select the data storage option.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.5 Selecting the data storage option.<\/p>\r\n

You can come back later and add more pieces to your Visual Studio.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.6 Screenshot of the installed items in Visual Studio.<\/p>\r\n

Now sign into Visual Studio. Or you could skip doing this action for later on. Microsoft has an interesting approach. You can sign into Visual Studio or into Hotmail. These are related in some fashion.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.7 The Visual Studio sign-in screen.<\/p>\r\n\r\n

Starting Up Visual Studio<\/h3>\r\n

On a Windows computer, the start button is in the \u201cV\u201d part of the listing.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.8 Location of the start button for Visual Studio.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.9 The splash screen during start up.<\/p>\r\n

The opening screen shows your previous work and provides some options.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.10 The Visual Studio opening screen.<\/p>\r\n\r\n

Testing Our Connection to the SQL Server<\/h3>\r\n

I used the information provided by the Programmingempire.<\/p>\r\n

Create a new console project<\/em> (a program that does not use a graphic interface (GUI)).<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.11 Creating a new project.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.12 Selecting a console project out of the list of possible project types.<\/p>\r\n

Select C#4<\/a><\/sup> as the desired programming language.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.13 The available programming languages.<\/p>\r\n

Fill out the floating dialog box. Figure 13.14 shows a screenshot from my personal computer:<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.14 Screenshot of the \u201cConfigure your new project\u201d floating dialog box on my personal computer.<\/p>\r\n

Note: The location line will be different for your computer. There is a checkbox toward the bottom. Do not use it.<\/p>\r\n

Most tutorial websites will recommend using \u201c.NET 7.0 (Standard Term Support) on the \u201cAdditional information\u201d floating dialog box.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.15 Selecting the \u201c.NET 7.0 (Standard Term Support)\u201d Framework5<\/a><\/sup>.<\/p>\r\n

Check the \u201cDo not use the top-level statements6<\/a><\/sup>.\u201d<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13. 16 The updated \u201cAdditional information\u201d floating dialog box.<\/p>\r\n

The Visual Studio has enough information to create a starting file.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.17 Screenshot of the generated C# lines.<\/p>\r\n

Look for the \u201cServer Explorer.\u201d It is under the \u201cView\u201d menu tab.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.18 Navigating to the \u201cServer Explorer.\u201d<\/p>\r\n

When the \u201cData Connection\u201d is present, then do a right mouse click and select \u201cAdd Connection.\u201d<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.19 Selecting the option for connecting to a current database.<\/p>\r\n

Select the \u201cMicrosoft SQL Server\u201d option from the \u201cChoose Data Source.\u201d<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.20 The opening screen of the \u201cChoose Data Source\u201d dialog box.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.21 Selecting the \u201cMicrosoft SQL Server\u201d option.<\/p>\r\n

Another floating dialog box will appear. This is the \u201cAdd Connection\u201d dialog box. It will look for SQL Servers and populate the drop-down menu.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.22 The \u201cAdd Connection\u201d floating dialog box.<\/p>\r\n

In additional, the floating dialog box will populate the names of the dialog box. The \u201cPlease Wait\u201d floating dialog box is blocking view of the \u201cConnect to a database\u201d section.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.23 The list of discovered databases on the SQL Server.<\/p>\r\n

If you have installed the AdventureWorks database, then you can select it.<\/p>\r\n

At the bottom of the floating dialog box is a button for testing the connection.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.24 The result of testing the connection.<\/p>\r\n

When you hit the \u201cOK\u201d button, the screen will update and display the details from the SQL Server.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.25 The Server Explorer shows the connection to the AdventureWorks2022 database.<\/p>\r\n

From inside Visual Studio, you can do some of the basic SQL Server Management Studio tasks.<\/p>\r\n\r\n

Using Microsoft\u2019s Visual Studio for Accessing a DBMS<\/h2>\r\n

It is beyond the scope of this chapter to provide a detailed description of C# coding with objects. And it is beyond the scope of this chapter to teach you how to use Microsoft\u2019s Visual Studio. You will be provided with enough information in order to create a successful program. You will not be writing raw lines. Instead you will be using an Application Programming Interface<\/em> (API) approach whereby you provide input to built-in classes and methods.<\/p>\r\n

In the code template, you need to type the following line as the first executable line:<\/p>\r\n

using Microsoft.Data.sqlClient;<\/p>\r\n\r\n

Adding the Microsoft.Data.sqlClient Package<\/h3>\r\n

If you receive an error message about this component not existing, then you will need to add the NuGet Package.<\/p>\r\n

Right mouse click on the project name and look for \u201cManage NuGet Packages.\u201d<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.26 First step for adding the missing Microsoft.Data.sqlClient package.<\/p>\r\n

Click on the \u201cBrowse\u201d button. Assuming your computer has an active Internet connection, the list will populate.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.27 Opening screen for NuGet.<\/p>\r\n

Once the list has populated, page down until you find \u201cMicrosoft.Data.SqlClient.\u201d<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.28 The populated NuGet list.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 1329 The \u201cMicrosoft.Data.SqlClient\u201d piece.<\/p>\r\n

Install the Microsoft.Data.SqlClient.<\/p>\r\n

\"image\"<\/p>\r\n

Figure 13.30 The installation screen for Microsoft.Data.SqlClient.<\/p>\r\n

Follow the prompts.<\/p>\r\n\r\n

Working with the SqlConnection Object<\/h3>\r\n

From Figure 12.24, we can view the connection string<\/em>, which is a series of value pairs that is needed by a program in order to access a database. We need to code this in our program. We need to work with the SqlConnection Object. Understand that the SqlConnection object is used to handle the actual communications between our C# program and the SQL Server database. The SqlConnection Object creates an instance<\/em> (a running example or session) and it is populated with a Connection String. This instance is passed (or shared) with another part of the program. Figure 12.30A shows the connection string template. Figure 12.25B shows an example based on my personal computer.<\/p>\r\n\r\n\r\n\r\n\r\n\r\n\r\n
\r\n

A<\/strong><\/p>\r\n<\/td>\r\n

\r\n

string<\/strong> connString = @\"Data Source=\" + datasource + \";Initial Catalog=\" + database + \";Persist Security Info=True;User ID=\" + username + \";Password=\" + password;<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

B<\/strong><\/p>\r\n<\/td>\r\n

\r\n

string <\/strong>connString = \"Data Source=FRED-WINDOWS-10;Initial Catalog=AdventureWorks2022; Integrated Security=True; TrustServerCertificate=True\u201d<\/p>\r\n<\/td>\r\n<\/tr>\r\n

<\/td>\r\n<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n

Figure 13.31 Connection string template and example.<\/p>\r\n

The connection string is passed to the SqlConnectionStringBuilder object:<\/p>\r\n

SqlConnectionStringBuilder builder= new SqlConnectionStringBuilder(connString)7<\/a><\/sup>;<\/p>\r\n

Then the created builder object is passed to the SqlConnection object:<\/p>\r\n

SqlConnection connection = new SqlConnection(builder);<\/p>\r\n

Now we can open the connection to the database.<\/p>\r\n

Connection.Open()<\/p>\r\n\r\n

Four Ways for a Program to Work with a Database<\/h2>\r\n

There are four ways of working with a database:<\/p>\r\n\r\n