13 Chapter 13 Programs to Work with a Database

Original Material to the textbook: Fred Strickland

Learning Outcomes

Introduction to Chapter 13

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’s 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.

The Third Edition Style Guide

This is the style guide that this book will follow for this chapter and for the other chapters.

Figure 13.1 The database style guide. Adapted from https://vertabelo.com/blog/database-schema-naming-conventions/

The examples will conform to the style guide.

Microsoft’s Visual Studio

Installing Microsoft’s Visual Studio

Search for “Microsoft Visual Studio.”

Figure 13.2 Screenshot of a hit for the search phrase “Microsoft Visual Studio.”

This chapter will be working with Visual Studio instead of Visual Studio Code. In October 2024, the current edition is 2022³. There are three versions.

Figure 13.3 The Visual Studio download screen with the three versions shown.

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.

Install the software. At one point, you will see the “Workloads” screen.

Figure 13.4 The Workloads screen.

Select the data storage option.

Figure 13.5 Selecting the data storage option.

You can come back later and add more pieces to your Visual Studio.

Figure 13.6 Screenshot of the installed items in Visual Studio.

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.

Figure 13.7 The Visual Studio sign-in screen.

Starting Up Visual Studio

On a Windows computer, the start button is in the “V” part of the listing.

Figure 13.8 Location of the start button for Visual Studio.

Figure 13.9 The splash screen during start up.

The opening screen shows your previous work and provides some options.

Figure 13.10 The Visual Studio opening screen.

Testing Our Connection to the SQL Server

I used the information provided by the Programmingempire.

Create a new console project (a program that does not use a graphic interface (GUI)).

Figure 13.11 Creating a new project.

Figure 13.12 Selecting a console project out of the list of possible project types.

Select C#⁴ as the desired programming language.

Figure 13.13 The available programming languages.

Fill out the floating dialog box. Figure 13.14 shows a screenshot from my personal computer:

Figure 13.14 Screenshot of the “Configure your new project” floating dialog box on my personal computer.

Note: The location line will be different for your computer. There is a checkbox toward the bottom. Do not use it.

Most tutorial websites will recommend using “.NET 7.0 (Standard Term Support) on the “Additional information” floating dialog box.

Figure 13.15 Selecting the “.NET 7.0 (Standard Term Support)” Framework⁵.

Check the “Do not use the top-level statements⁶.”

Figure 13. 16 The updated “Additional information” floating dialog box.

The Visual Studio has enough information to create a starting file.

Figure 13.17 Screenshot of the generated C# lines.

Look for the “Server Explorer.” It is under the “View” menu tab.

Figure 13.18 Navigating to the “Server Explorer.”

When the “Data Connection” is present, then do a right mouse click and select “Add Connection.”

Figure 13.19 Selecting the option for connecting to a current database.

Select the “Microsoft SQL Server” option from the “Choose Data Source.”

Figure 13.20 The opening screen of the “Choose Data Source” dialog box.

Figure 13.21 Selecting the “Microsoft SQL Server” option.

Another floating dialog box will appear. This is the “Add Connection” dialog box. It will look for SQL Servers and populate the drop-down menu.

Figure 13.22 The “Add Connection” floating dialog box.

In additional, the floating dialog box will populate the names of the dialog box. The “Please Wait” floating dialog box is blocking view of the “Connect to a database” section.

Figure 13.23 The list of discovered databases on the SQL Server.

If you have installed the AdventureWorks database, then you can select it.

At the bottom of the floating dialog box is a button for testing the connection.

Figure 13.24 The result of testing the connection.

When you hit the “OK” button, the screen will update and display the details from the SQL Server.

Figure 13.25 The Server Explorer shows the connection to the AdventureWorks2022 database.

From inside Visual Studio, you can do some of the basic SQL Server Management Studio tasks.

Using Microsoft’s Visual Studio for Accessing a DBMS

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’s 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 (API) approach whereby you provide input to built-in classes and methods.

In the code template, you need to type the following line as the first executable line:

using Microsoft.Data.sqlClient;

Adding the Microsoft.Data.sqlClient Package

If you receive an error message about this component not existing, then you will need to add the NuGet Package.

Right mouse click on the project name and look for “Manage NuGet Packages.”

Figure 13.26 First step for adding the missing Microsoft.Data.sqlClient package.

Click on the “Browse” button. Assuming your computer has an active Internet connection, the list will populate.

Figure 13.27 Opening screen for NuGet.

Once the list has populated, page down until you find “Microsoft.Data.SqlClient.”

Figure 13.28 The populated NuGet list.

Figure 1329 The “Microsoft.Data.SqlClient” piece.

Install the Microsoft.Data.SqlClient.

Figure 13.30 The installation screen for Microsoft.Data.SqlClient.

Follow the prompts.

Working with the SqlConnection Object

From Figure 12.24, we can view the connection string, 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 (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.

Figure 13.31 Connection string template and example.

The connection string is passed to the SqlConnectionStringBuilder object:

SqlConnectionStringBuilder builder= new SqlConnectionStringBuilder(connString)⁷;

Then the created builder object is passed to the SqlConnection object:

SqlConnection connection = new SqlConnection(builder);

Now we can open the connection to the database.

Connection.Open()

Four Ways for a Program to Work with a Database

There are four ways of working with a database:

• A concatenated string
• Prepared statements with parameters
• Stored procedures
• Allow-list input validation

Using a Concatenated String

Imagine that we wish to select name and collation_name from the sys.databases table. Imagine that we have created a front end that looks similar to Figure 12.31.

Figure 13.32 A fictitious front-end.

Our user fills out the form as shown in Figure 12.32.

Figure 13.33 Example entries in the fictitious front-end.

Behind the front-end, the entries are concatenated or combined or added to a string:

Figure 13.34 The behind the front-end work.

This string is added to the SqlCommand object and this is sent out to the database for processing:

SqlCommand command new SqlCommand(sql, connection)

SqlDataReader reader = command.ExcuteReader();

This will return a result set (the desired rows).

This is bad coding, because a hacker could add another command at the end. Look at Figure 12.35.

Figure 13.35 Example of how a hacker could obtain more data from a database.

This is known as an SQL injection attack. There are three additional versions of this attack:

• Use the UNION keyword. This combines two SQL statements. This is similar to using a semicolon as shown in Figure 12.34.
• Malformed SQL statement. A SQL Server will return an error message with the desired data.
• Blind SQL attack. The hacker creates queries that return true or false. The hacker inspects the results. Then the hacker crafts a SQL query that will return the desired data.

A skillful hacker could access cookies⁸ on a person’s computer. The hacker could change the contents of the cookie file and cause the query to execute differently.

Some websites will put pieces of a query in the URL. A hacker could change the URL to execute differently.

And a hacker could execute commands that would change the data or add bogus data.

Some database administrators have attempted to stop hackers by requiring the authorized users to add their userid and passwords to the fill-in boxes. The login credentials would be added to the WHERE clause. Hackers have defeated this by adding the OR connector with a Boolean statement that is always true.

sql = “SELECT id FROM users WHERE username='” + user + “‘ AND password='” + pass + “‘”

After the pass variable, add: OR 5 = 5

The hacker can type anything for the login credentials and the query will still execute.

These are just some of the ways that a hacker can attack a concatenated string front end. Admir Dizdar’s article covered these examples in greater detail plus provided insights on other exploits.

Using Prepared Statements with Parameters

Instead of collecting the user’s entries, we define a parameter or variable for each item.

Instead of a very generic front-end, we will make it narrower.

Figure 13.36 Revised front-end for collecting a parameter.

Our user fills out the form as in Figure 13.37.

Figure 13.37 Example entry in the fictitious front-end.

Behind the front-end, the entries are fed into the parameters and we create a prepared statement.

Figure 13.38 The behind the front-end work with the variable customerName⁹ is connected to the front-end.

Suppose the hacker adds something extra to the name box.

IamAwonderfulUser; SELECT * FROM HumanResources;

The query would become:

SELECT AccountBalance FROM UserData WHERE UserName “IamAwonderfulUser; SELECT * FROM HumanResources;”

The system would look for a person with the name of “IamAwonderfulUser; SELECT * FROM HumanResources;” The search fails since there is no person in the database with that name!

Using Stored Procedures

Prepared statements work with the actual SQL command inside the programming language. Stored procedures work with the actual SQL command inside the database environment. The approach is similar in that data parameters are collected and are sent to the database. The difference is that you need to define the name of each parameter.

Figure 13.39 Java and C# examples using a stored procedure.

Using Allow-List Input Validation

The user inputs are validated in some fashion.

One approach is to use drop down lists. This is done for postal information (such the two-letter codes used in North America), for dates, for gender, and for other known lists of values.

A regular expression could be used. A regular expression does a pattern match against permitted or not permitted characters. For example, a regular expression could be used to handle all of the current forms of ten-digit telephone numbers as used in North America:

Figure 13.40 A regular expression for finding valid ten-digit telephone numbers.

Support for regular expressions is found in every programming language such as C++, C#, Java, and others. Many editing programs use regular expressions. There are many resources for learning more about this topic.

• “Regex Tutorial – How to write Regular Expressions?” Geeks for Geeks, April 12, 2024. (https://www.geeksforgeeks.org/write-regular-expressions/)
• Regular expressions 101 (https://regex101.com)
• Regex (https://regexr.com)

The various environments may have slightly different regular expression rules.

Validating user input should be included for prepared statements and for stored procedures.

Templates

Templates exist in the SQL Server Management Studio. These are found inside the View tab.

Figure 13.41 Screenshot of the path to the SQL Server Management Studio templates. Source of image: https://learn.microsoft.com/en-us/sql/ssms/template/templates-ssms?view=sql-server-ver16

Select a template and do a right mouse click to open it up.

Figure 13.42 Opening up the “Create Database” template. Source of image: https://learn.microsoft.com/en-us/sql/ssms/template/templates-ssms?view=sql-server-ver16

A query window will open with the T-SQL script present.

Figure 13.43 Test of the “Create Database” template. Source of image: https://learn.microsoft.com/en-us/sql/ssms/template/templates-ssms?view=sql-server-ver16

You can modify the template and then execute it.

If you wish to make the changes permanent, then you can edit the selected template.

Figure 13.44 The editing option. Source of image: https://learn.microsoft.com/en-us/sql/ssms/template/templates-ssms?view=sql-server-ver16

After the changes are made, then select File Save.

Reopening the template view will show the revised changes.

Creating a new template involves using the New folder steps. Look for “Custom Templates.”

Figure 13.45 The create new template option. Source of image: https://learn.microsoft.com/en-us/sql/ssms/template/templates-ssms?view=sql-server-ver16

Right mouse click on “Custom Templates” and select the New Template path. Enter a useful name.

Figure 13.46 Continuing the steps for creating a new template. Source of image: https://learn.microsoft.com/en-us/sql/ssms/template/templates-ssms?view=sql-server-ver16

Enter the T-SQL lines. When finished, hit File Save.

Functions

In the chapter on SQL commands, we worked with AVG (), with COUNT(), with MAX(), with MIN(), and with others. These were SQL functions. The W3 schools website has grouped the Microsoft SQL Server functions in this fashion:

• SQL Server String Functions
• SQL Server Math/Numeric Functions
• SQL Server Date Functions
• SQL Server Advanced Functions

The SQL Server Advanced functions provide solutions to challenging problems. CAST() and CONVERT() will change a value from one data type to another data type. For example, you could change a decimal to a whole number. Or you could change a string representing a number to a numeric that could be manipulated with mathematical operators.

When working with NULLs, the Math/Numeric functions will have strange results. The COALESCE function will solve this issue. The W3 schools COALESCE function web page does not explain this function fully. The following will render NULLs as zeros:

SELECT COALESCE(TotalAmount, 0) FROM Products;

Microsoft recommends using the following approach:

SELECT isnull(TotalAmount 0) AS TotalAmount FROM Products;

As the Microsoft writer explained in the 2008 article, COALESCE is based on the ANSI SQL standard whereas the ISNULL is a Microsoft proprietary T-SQL function.

Troels Arvin has put together a web page that compares the different SQL implementations against the SQL 2008 standard.

Triggers

Recall the Chapter 9 section about cascading updates and deletes (“DDL: CREATE TABLE with the Cascading Update and Cascading Delete”). These are examples of DML triggers. Again, these are invoked automatically in response to INSERT, UPDATE, and DELETE events against database tables.

There are two more types of triggers:

• DDL triggers. These react to CREATE, ALTER, and DROP statements.
• Some system defined stored procedures would cause a DDL trigger to fire.
• Logon triggers

Creating DML Triggers

Figure 13.47 shows the syntax for a create trigger statement:

Figure 13.47 Create trigger syntax. (The text was not changed to conform with our style guide.) Source of text: https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/

SQL Server has two predefined tables that support triggers. One table is for insert events and the other table is for delete events. These are used to capture the data before and after an action. Figure 13.48 explains how these work.

Figure 13.48 INSERTED and DELETED tables Source of table notes: https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/

SQLServer Tutorial.net has a nice example. The following is that example with changes to follow our style guide.

Figure 13.49 SQLServer Tutorial.net example table that has been revised based on our style guide. Source of text: https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/

We need a table for logging the changes to the Production.Products table. We are naming this table as Production.ProductAudits

Figure 13.50 SQLServer Tutorial.net example changes tracking table that has been revised based on our style guide. Source of text: https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/

Now, we need to create a trigger. Figure 12.51 has the trigger code with explanations.

Figure 13.51 SQLServer Tutorial.net example trigger creation lines that have been revised based on our style guide. Source of text: https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/

You must execute the code. Then it will be added to the list of triggers. Figure 12.52 shows the location of this new trigger.

Figure 13.52 SQLServer Tutorial.net example showing where it would be stored in a database.. Source of image: https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/

If something is added to this table, the trigger would fire. If something is deleted from this table, the trigger would fire. Here is the output from the ProductAudits table:

Figure 13.53 SQLServer Tutorial.net example showing the output for two actions.. Source of image: https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/

Creating DDL Triggers

SQL Server DDL triggers can react to database events such as CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS. DDL triggers are useful for recording changes to the database scheme. Instead of pulling from the Inserted system table or from the Deleted system table, you would execute the EVENTDATA() function and capture the output into the user created event tracking table. For a detailed example, see the reference for the URL.

Creating Logon Triggers

A logon trigger can be used for five purposes:

• Restrict users to only login at certain times.
• Restrict number of concurrent sessions for a specific login.
• Restrict total number of connections.
• Restrict login by host name or IP Address.
• Use a logon trigger for auditing.

The syntax is similar to the DML trigger.

Figure 13.54 MSSQL Tips showing the basic syntax for working with a logon trigger.. Source of T-SQL: https://www.mssqltips.com/sqlservertip/6103/sql-server-logon-trigger-examples/

If the logon trigger is built incorrectly, you could find yourself locked out of your own database. Bhavesh Patel has an example for each of the five use cases.

We did not address the fact that SQL Server has triggers in the Template Explorer. See Figure 13.41 for the location of the Database Trigger folder. See Figure 13.45 for the location of the general Trigger folder.

Stored Procedures

Stored procedures were mentioned earlier in the chapter as one of four ways for a program to work with a database. We did not explore the benefits and drawbacks of using a stored procedure. Nor did we explore how to create a stored procedure. We will address these points in this section.

The Big Debate

There are forums that have debated the question of which is better. There are well-written articles that attempt to show both sides. Some individuals point out that the advance of technology has rendered some reasons as moot. Do not expect this section to be the be-all-and-end-all on this topic. The following lists are some of the points made by individuals on different sides of this debate.

The Pros of using stored procedures.

• These support modular programming.
• These allow faster execution.
• These reduce the volume of network traffic.
• These add another layer of security.
• The application developer can concentrate on the code while the database programmer can create the stored procedures.
• Changes made to the stored procedures do not require the source code to be recompiled. This is assuming that the stored procedures have the same parameter list.
• Older versions of SQL Server could not optimize SQL statements from a program, but could do it for stored procedures.
  • New versions of SQL Server support statement-level optimization.
• Stored procedures can be invoked by numerous applications and programs.
• Query statements can be inspected without the need for the program to be executing.

The Cons of using stored procedures.

• Stored procedures tend to bloat. New stored procedures are added, but old ones rarely are deleted.
• There is no documentation that states who might use a specific stored procedure. There is the fear that removing a stored procedure might break something.
• If the stored procedure is using dynamic SQL, then parameterized queries can be used to launch an SQL injection attack.
  • A well-designed program block would support the generation of SQL code on the fly.
• Writers may not encapsulate pieces. This makes working with and debugging the stored procedures difficult.
• Fake data cannot be used in a stored procedure.
  • Testing out a stored procedure requires against some database tables. So a testing database is needed.
• A database administrator would need to conduct performance tuning.
• Stored procedures tend to be based on a specific DBMS. Changing to a different DBMS would require rewriting the stored procedures.
• If a function is deprecated (planned removal), then any stored procedures using that function will not work.
• Source control is difficult to do for stored procedures. (Some individuals disagree with this statement.)

There have been efforts to add an object relational model support (Entity Framework). This gets into Code-First and Database-First mappings of a database to programming classes. The Language Integrated Query (LINQ) that works with LAMBDA expressions generates lines that resemble SQL syntax. For a time, it looked like Microsoft was dropping LINQ. Then they changed and now it is being improved in every release of .NET. In a blog, there was a paragraph about a situation when LINQ could greatly increase the generated code size.

For this edition, we will not explore LINQ. When LINQ is more stable and more mainstream, then we will consider adding more information about LINQ.

Creating Stored Procedures

Microsoft has developed two ways of creating a stored procedure within the SQL Server Management Studio.

The first way is using the GUI support. Recall that the Template Explorer was at a high level above the individual databases. For creating a stored procedure, it is more linked to the current working database.

You would work down to the Programmability folder. Figure 13.55 is a screenshot of a database using VisualBasic. Our C# example would be similar.

Figure 13.55 A screenshot of the “Object Explorer” tree with the “Programmability” folder visible. Source of image: https://i.ytimg.com/vi/7ZwcfvUYkTg/maxresdefault.jpg

Expanding the Programmability folder will reveal the Stored Procedure folder. The first folder has the System Stored Procedures. See Figure 13.56.

Figure 13.56 Screenshot of part of the items in the Programmability folder. Source of image: https://i.sstatic.net/7piek.png

You would do a right mouse click on the Stored Procedures folder. You would select New Stored Procedure. A new query window would appear with boilerplate lines for the new stored procedure.

Figure 13.57 Screenshot of the boilerplate stored procedure text. Source of image: https://www.sqlshack.com/script-templates-in-sql-server-management-studio-ssms/

On the Query menu, select Specify Values for Template Parameters. Figure 12.58 shows the example entries.

Figure 13.58 The Specify Values for Template Parameters dialog box. Source of image: https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver16

When finished, hit “OK.”

You would be doing the actual editing inside the Query Editor. Figure 13.59 shows the example stored procedure.

Figure 13.59 The final version of the running example.

The Query menu has a Parse button that will check for syntax errors.

Executing these lines will create the actual stored procedure and will store it as an object in the database. Revisit the Stored Procedure and select Refresh.

To execute the new stored procedure, do a right mouse click on the stored procedure name and select Execute Stored Procedure. In the Execute Procedure window, you will need to enter some values for the blank parameters. Then hit the OK button.

If you were using the Microsoft’s AdventureWorksLT2022 database, then entering “Cannon” for the @LastName parameter and “Chris” for the @FirstName parameter would yield the following output:

Chris Cannon Outdoor Sporting Goods

The second way is using only the Transact-SQL lines. You would have the same text as Figure 13.59 without the comment lines. You would enter these lines manually.

Figure 13.60 The raw Transact-SQL lines.

Rajendra Gupta’s article is based on an older version of the SQL Server Management Studio.

The sources used for this section did not explain the actual lines. Also, the sources did not explain that the text of the created stored procedure is different from the creation command lines.

Figure 13.61 The raw Transact-SQL lines with explanations.

Another way of executing a stored procedure is with the EXEC command:

EXEC SalesLT.uspGetCustomerCompany @FirstName = “Chris” @LastName = “Cannon”

The EXEC is optional. You could execute the stored procedure by simply typing the name of the stored procedure:

SalesLT.uspGetCustomerCompany @FirstName = “Chris” @LastName = “Cannon”

Just like you can drop tables, you can drop stored procedures:

DROP PROCEDURE SalesLT.uspGetCustomerCompany

GO

If you need an output to be used elsewhere, then you would need to declare an OUT parameter. Figure 12.62 shows how to declare the OUT parameter and how to use it.

Figure 13.62 Creating a stored procedure that has an OUT parameter and showing an example execution. Source of example: https://www.mssqltips.com/sqlservertutorial/163/returning-sql-server-stored-procedure-parameter-values-to-a-calling-stored-procedure/

The OUT parameter could be passed to a calling program. This is how this would be done:

Figure 13.63 Extract for declaring and receiving output values from a SQL stored procedure. Source of extract: https://www.c-sharpcorner.com/Blogs/example-on-how-to-use-output-parameter-in-stored-procedure-in-c-sharp

This has been a simple, high-level coverage of stored procedures. The body of a stored procedure could have been deleted and drop lines. There could be calls to other stored procedures. And stored procedures can have many lines. We have avoided covering dynamic SQL commands, because this approach could result in an SQL injection attack.

Creating Views

A view is a virtual table that is the result of a query. There are three reasons for creating a view:

• To focus, simplify, and customize the perception each user has of the database.
• As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
• To provide a backward compatible interface to emulate a table whose schema has changed.

The syntax has a column list in the header and a select statement in the body.

Figure 13.64 Simple syntax of a create view command. Source of code: https://www.sqlservertutorial.net/sql-server-views/sql-server-create-view/

If the column list is not used in the header, then column names would come from the SELECT statement.

Imagine that your database has a Products table, a Sales.OrderItems table, and a Sales.Orders table. And imagine that upper management is interested in the total sales by date and by product. Upper management has no interest in the number of sold items. We could e-mail the SQL lines (Figure 13.65) and let them execute this query.

Figure 13.65 Extracting data from a combination of three tables. Unchanged source of code: https://www.sqlservertutorial.net/sql-server-views/sql-server-create-view/

The other option is to take the lines from Figure 13.65 and place in the body of the CREATE VIEW and create a view with the name of Sales.DailySales. Then we could e-mail the lines from Figure 12.66 to upper management.

Figure 13.66 The View lines and example output. Source of code: https://www.sqlservertutorial.net/sql-server-views/sql-server-create-view/

To make a change, the header line is “CREATE OR ALTER,” which is different from the syntax for changing a table. You would repeat the column names and add the new column name. The SELECT block would include the old lines plus the new line.

The SQLSERVER Tutorial.net has additional examples.

A Word About Prefixes

You may have noticed that system defined stored procedures use “sp_” at the beginning of the name. This is a standard naming convention and you must not use it for your own created stored procedures. If you do, then the SQL Server will search the master database for your stored procedure before visiting your user database.

The user defined examples used prefixes in order to make the examples clearer. As noted elsewhere in this textbook, prefixes are not needed.

Key Terms

Application Programming Interface (API): A programming language will have objects and classes. Programmers will use these instead of creating their own block of code for a task. An example would be the SqlConnection object.

concatenated: In programming, pieces are combined or added to form a string.

connection string: This is a series of value pairs that is needed by a program in order to access a database.

console project: This will return the rows that semi join had rejected.

deprecated: This means that the software feature is usable, but is obsolete. The practice is to state in a revision that a certain method or class or function will be removed in a future version. The word “deprecated” is used in the warning.

functions: These are built-in blocks of SQL lines for performing tasks such as obtaining the average of values in a column, counting the number of items in a column, and so on.

graphic user interface (GUI): The eye-pleasing front end to a program.

instance: This is a running example or session.

OUT parameter: This is used within the SQL Server environment for returning a value other than a result set. If this is being sent to the calling program, then that program would need to have an out variable declared.

prepared statement: This is a programming approach whereby values are collected and added to a specially created statement for routing to a database.

regular expression: This does a pattern match against permitted or not permitted characters.

result set: What comes back from a SQL query.

SQL injection attack: This is a technique whereby a hacker can craft a SQL query that can obtain more data than wanted by the database managers.

Stored procedures: These work with the actual SQL command inside the database environment.

triggers: These are SQL lines that are invoked when an action takes place.

view: This is a virtual table that is the result of a query.

Exercises

1. Explain the following terms:

1. Application Programming Interface (API)

2 concatenated

3. connection string

4. console project

5. deprecated

6. function

7. graphic user interface

8. instance

9, OUT parameter

10. prepared statement

11. regular expression

12. result set

13. SQL Injection Attack

14. stored procedures

15. triggers

16. view

2. What are the four ways of working with a database?

3. Explain how a hacker could trick a concatenated string into providing more data. [IS2020 A3.2.14]

4. Using an example from Java or from C# or from another programming language, show how you would embed a SELECT query. [CS2013 IM/Query Languages 5 and IT2017 ITE-IMA-04d.]

5. For the previous question, list and explain the API calls. [DS2021 PDA Programming]

6. You are collecting address information from a person. Which fields could be handled without using a text box?

7. Explain how a regular expression could be used for validating a user’s input.

8. Using the references in this chapter, come up with a regular expression for something. Explain why you made this string as your answer.

9. What is a SQL Server template? How is it used? [CS2013 IM/Query Languages 4.]

10. You know that a city uses a house numbering scheme whereby the house numbers differ by four. The database has all of the addresses stored in a database table. A dead-end street is being extended for a distance. You have been asked to add to the database the next 20 house numbers. Explain how this task could be completed by using functions.

11. If the task in the previous question took place frequently, then a trigger action could be done. Explain how this might be achieved.

12. What are the benefits of using functions and triggers. [IS2020 A3.2.1 3]

13. Write a stored procedure that uses at least two parameters. Explain how you could prevent unauthorized users from firing off your stored procedure. [CS2013 IM/Query Languages 6.]

14. What are some ways that a user could interact with a database. [DS2021 SDM-Software Design and Development Skills]

15. Provide an example of a view. State who is the user. State why the view would be a good approach for this user. [IT2017 ITE-IMA-04f.]

16. What are some ways of securing a database. I am looking for at least two ways of securing a database. [IS2020 A3.2.1 4]

17. This was implied in this chapter, but never explained. Explain why we would want to write a program to access a database. [DS2021 SDM-Software Design and Development]

18. Roles were mentioned in Chapter 11, but you were not asked to demonstrate an understanding of this topic. Describe how you might go about creating and managing database users. [IT2017 ITE-IMA Domain Information Management E.]

19. Write about three ways of providing security to the database. This is an open-ended question. [IT2017 ITE-IMA Domain Information Management E.]

A Running Project

You should have your project in great shape.

Some of the learning outcomes for this chapter are of an application nature. The following tasks will provide an opportunity to demonstrate your understanding.

1. Using your project, create a query by filling in a template. [CS2013 IM/Query Languages 4 and ITS2017 ITE-IMA-04 Database query languages d.]

2. Use the insights from this chapter to write a query that is requested from a programming language. This query should select at least one column from a table. [CS2013 IM/Query Languages 5 and ITS2017 ITE-IMA-04 Database query languages d.]

3. Use the insights from this chapter to create a stored procedure. The stored procedure needs to work with at least two parameters. [CS2013 IM/Query Languages 6 and ITS2017 ITE-IMA-04 Database query languages d.]

4. Use the insights from this chapter to create one trigger. [IS2020 A3.2.1 3.]

5. Using any of the foregoing tools, create something that would use a function. [IS2020 A3.2.1 3.]

6. Image the regular user of your project database. How would you limit what this person could see? [IT2017 ITE-IMA Domain Information Management E.]

7. Create two views:

A. A view that is fixed and will always produce the same result set.

B. A view that permits the user to select different parameters and thus have a different result set.

[IT2017 ITE-IMA-04 Database query languages f.]

Attribution

This chapter of Database Design is a brand-new addition.

This chapter drew from many sources.

Image Attributions

No second edition images were used.

References

Troels Arvin. “Comparison of different SQSL implementations,” Troels Arvin, December 29, 2018. https://troels.arvin.dk/db/rdbms/

Daniel Calbimonte. “SQL GO command in SQL Server,” SQLShack, May 18, 2021. https://www.sqlshack.com/sql-go-command-in-sql-server/

“Create a stored procedure,” Learn Microsoft, January 30, 2024. https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver16

“CREATE VIEW (Transact-SQL),” Learn Microsoft, May 23, 2023. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16

Admir Dizdar. “SQL Injection Payloads: How SQLi exploits work,” Bright, September 16, 2021 (Updated on November 14, 2023). https://brightsec.com/blog/sql-injection-payloads/

“Differences between ISNUL and COALESCE,” Learn Microsoft, June 26, 2008. https://learn.microsoft.com/en-us/archive/blogs/sqltips/differences-between-isnull-and-coalesce

The Stackoverflow forum discussed this topic. One writer cited the Microsoft article, but modified the example. I used the modified example, because it was clearer. See https://stackoverflow.com/questions/19115040/replace-null-with-blank-value-or-zero-in-sql-server

Rajendra Gupta. “SET QUOTED_IDENTIFIER settings in SQL Server,” SQLShack, October 17, 2019. https://www.sqlshack.com/set-quoted_identifier-settings-in-sql-server/

Rajendra Gupta. “Script templates in SQL Server Management Studio (SMS),” SQLShack, April 27, 2020. https://www.sqlshack.com/script-templates-in-sql-server-management-studio-ssms/

Bar Hofesh. “SQL Injection Attack: how It Works, Examples and Prevention,” Bright, April 8, 2022. https://brightsec.com/blog/sql-injection-attack/

Bar Hofesh referenced a cheat sheet in his article. See https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

“How to Setup a Connection with SQL Serer Database in Visual Studio,” Programmingempire, December 17, 2022 (updated on April 6, 2022). https://www.programmingempire.com/how-to-setup-a-connection-with-sql-server-database-in-visual-studio/

Amit Mohanty. “How To Use Output Parameter In Stored Procedure In Stored Procedure In C#,” C3 Corner, October 19, 2023. https://www.c-sharpcorner.com/Blogs/example-on-how-to-use-output-parameter-in-stored-procedure-in-c-sharp

Bhavesh Patel. “SQL Server Logon Trigger Examples,” MSSQL Tips, n.d. https://www.mssqltips.com/sqlservertip/6103/sql-server-logon-trigger-examples/

“SET ANSI_NULLS (Transact-SQL),” Learn Microsoft, April 5, 2024. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-ver16

“SET NOCOUNT (Transact-SQL),” Learn Microsoft, September 3, 2024. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-ver16

“SET QUOTED_IDENTIFIER (Transact-SQL),” Learn Microsoft, August 9, 2024. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-ver16

“SQL Server CREATE TRIGGER,” SQLSERVER Tutorial.net, n.d. https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/

“SQ; Server CREATE VIEW,” SQLSERVER Tutorial.net, n.d. https://www.sqlservertutorial.net/sql-server-views/sql-server-create-view/

“SQL Server DDL Trigger,” SQLSERVER Tutorial.net, n.d. https://www.sqlservertutorial.net/sql-server-triggers/sql-server-ddl-trigger/

“SQL Server Functions,” W3 schools, n.d. https://www.w3schools.com/sqL/sql_ref_sqlserver.asp

Stephen Toub. “Performance Improvements in .NET 9,” Dev Blogs, September 12, 2024. https://devblogs.microsoft.com/dotnet/performance-improvements-in-net-9/

“Use templates in SQL Server Management Studio,” Learn Microsoft, March 3, 2023. https://learn.microsoft.com/en-us/sql/ssms/template/templates-ssms?view=sql-server-ver16

Ahmad Yaseen. “INSERT INTO T-SQL Statement in SQL Server,” SQLShack, March 27, 2020. https://www.sqlshack.com/insert-into-t-sql-statement-in-sql-server/