.NET 7 Design Patterns In-Depth by Vahid Farahmandian

Author:Vahid Farahmandian [Farahmandian, Vahid]
Language: eng
Format: epub
ISBN: 9789355518729
Publisher: BPB Publications
Published: 2023-02-15T00:00:00+00:00

Table data gateway

Name:

Table data gateway

Classification:

Data source architecture design patterns

Also known as:

---

Intent:

Using this design pattern, an object plays the role of a gateway to a database table or view, and communication with all the records of that table or view happens through this object.

Motivation, Structure, Implementation, and Sample code:

Suppose a requirement is raised, and we need to connect to the database and perform Create, Read, Update, Delete (CRUD) operations on the user table. For this purpose, functions such as adding a new user, changing the password, deleting a user, finding a specific user, and receiving a list of users should be implemented.

There are different ways to implement this requirement. One of the ways is to connect to the database using raw queries and do the desired work. Using this method, the placement of the queries is important. If these codes are placed among the business logic code of the application, they will create a significant problem. The problem is that programmers will need to write queries. Most programmers either do not have enough knowledge to write queries or if they do have enough knowledge, database administrators will have difficulty finding and improving the performance of these queries.

However, a better option is placing the queries outside the business logic location. Table data gateway design patterns can be important in achieving this goal. In this way, all the required queries are collected and defined in a separate business logic section:

public class UserTableDataGateway

{

private SqlConnection connection;

public UserTableDataGateway()

{

connection = new SqlConnection("...");

}

public async Task<IDataReader> GetAllAsync()

{

return await new SqlCommand("" +

"SELECT * " +

"FROM Users", connection).ExecuteReaderAsync();

}

public async Task<IDataReader> FindByUserNameAsync(string username)

{

return await new SqlCommand($"" +

$"SELECT * " +

$"FROM Users " +

$"WHERE UserName = N'{username}'", connection).ExecuteReaderAsync();

}

public async Task<bool> ChangePasswordAsync(

string username, string newPassword)

{

return (await new SqlCommand($"" +

$"UPDATE Users " +

$"SET [Password] = N'{newPassword}' " +

$"WHERE UserName = N'{username}'"

, connection).ExecuteNonQueryAsync()) > 0;

}

}

As seen in the preceding code, the GetAllAsync method is used to get the list of all users. To find a specific user based on username, the FindByUsernameAsync method is used, and the ChangePasswordAsync method is used to change the password. Other operations, such as inserting a new user and deleting a user, can be defined similarly. Another noteworthy point in the preceding implementation is that for the sake of simplicity, in the definition of queries, things like SQL Injection and opening and closing the connection to the database have not been paid attention to. Still, in the real environment, it will be necessary to do such things. In the business logic layer, you can use this gateway if you need to work with the Users table. Also, database administrators must only check the gateways to monitor and improve queries.

Notes:

By using this design pattern, database queries are encapsulated in the form of a series of methods.

To implement this design pattern, you can also benefit from a Data set. While using this method, most of the operations will be similar, and you can also benefit from a public gateway and send things like the table name in the form of input parameters.

In implementing this design pattern, there is no requirement to communicate with the database tables.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.