After the introduction in the above article, the entire system framework is basically complete. Below, we need to implement each layer. For the implementation of the data Access layer, I will discuss three implementation methods. This article discusses the first one: Access + dynamic generation of SQL. As the name suggests, this implementation uses Access as the background database, and
After the introduction in the above article, the entire system framework is basically complete. Below, we need to implement each layer. For the implementation of the data Access layer, I will discuss three implementation methods. This article discusses the first one: Access + dynamic generation of SQL. As the name suggests, this implementation uses Access as the background data library, while
After the introduction in the previous article, the framework of the entire system is basically complete. Below, we needImplementationEach layer. AboutDataAccessLayerImplementation, I am going to discuss three typesImplementationMethod, discussed in this articleFirstType: Access + dynamic generation of SQL.
As the name suggestsImplementationUse Access as the backgroundDataDatabase, and the operation method is also the most basic use of SQL commands.
WriteImplementationBefore the code, we need to do some preparation work:
FirstStep.DataAfter the library is set up, follow these steps.
Create a folder named AccessData in the Web Project, and create an mdb File (AccessDataLibrary files), according toDataLibrary Design architectureDataThe relationship between the table and the table is created.
Step 2: perform some configuration.
Open the Web. config file in the Web project, and add the following key values under the deleettings node:
FirstThe connection string is Access, and the second is Access.DataPath of the library file, where "~" Indicates the root directory of the website.
Step 3: Create a project.
We need to create a new project AccessDAL to store AccessDataAccessLayer Code.
Now the preparation is complete.ImplementationSpecific code.
1. WriteDataAccessHelper class
Because manyDataAccessThe operation process is very similar. Therefore, some reusable code is extracted here and written as helper classes to reduce the amount of code and improve code reusability.
This helper class is stored in AccessDAL and called AccessDALHelper. It is mainly responsible for AccessDataLibraryAccess. It includes three methods:
GetConnectionString: Read configuration items from the configuration file and combine them into connection strings.
ExecuteSQLNonQuery: executes the specified SQL statement without returning any value. It is generally used for the Insert, Delete, and Update commands.
ExecuteSQLDataReader: Execute an SQL statement to return the query result. It is generally used for the Select command.
The Code is as follows:
AccessDALHelper. cs:
AccessDALHelper
- 1 using System;
- 2 using System. Web;
- 3 using System. Web. Caching;
- 4 using System. Configuration;
- 5 using System. Data;
- 6 using System. Data. OleDb;
- 7 using NGuestBook. Utility;
- 8
- 9 namespace NGuestBook. AccessDAL
- 10 {
- 11 /**////
- 12 // AccessDataDatabase Operation Assistant
- 13 ///
- 14 public sealed class AccessDALHelper
- 15 {
- 16 /**////
- 17 // read AccessDataDatabase connection string
- 18 // read from the cache first, read from the configuration file if it does not exist, and put it in the cache
- 19 ///
- 20 /// Access DataDatabase connection string
- 21 private static string GetConnectionString ()
- 22 {
- 23 if (CacheAccess. GetFromCache ("AccessConnectionString ")! = Null)
- 24 {
- 25 return CacheAccess. GetFromCache ("AccessConnectionString"). ToString ();
- 26}
- 27 else
- 28 {
- 29 string dbPath = ConfigurationManager. receivettings ["AccessPath"];
- 30 string dbAbsolutePath = HttpContext. Current. Server. MapPath (dbPath );
- 31 string connectionString = ConfigurationManager. receivettings ["AccessConnectionString"];
- 32
- 33 CacheDependency fileDependency = new CacheDependency (HttpContext. Current. Server. MapPath ("Web. Config "));
- 34 CacheAccess. SaveToCache ("AccessConnectionString", connectionString. Replace ("{DBPath}", dbAbsolutePath), fileDependency );
- 35
- 36 return connectionString. Replace ("{DBPath}", dbAbsolutePath );
- 37}
- 38}
- 39
- 40 /**////
- 41 // execute the SQL statement without returning any value
- 42 ///
- 43 ///The executed SQL command
- 44 ///Parameter Set
- 45 public static void ExecuteSQLNonQuery (string SQLCommand, OleDbParameter [] parameters)
- 46 {
- 47 OleDbConnection connection = new OleDbConnection (GetConnectionString ());
- 48 OleDbCommand command = new OleDbCommand (SQLCommand, connection );
- 49
- 50 for (int I = 0; I <parameters. Length; I ++)
- 51 {
- 52 command. Parameters. Add (parameters );
- 53}
- 54
- 55 connection. Open ();
- 56 command. ExecuteNonQuery ();
- 57 connection. Close ();
- 58}
- 59
- 60 /**////
- 61 // execute the SQL statement and return the DataReader containing the query result
- 62 ///
- 63 ///The executed SQL command
- 64 ///Parameter Set
- 65 ///
- 66 public static OleDbDataReader ExecuteSQLDataReader (string SQLCommand, OleDbParameter [] parameters)
- 67 {
- 68 OleDbConnection connection = new OleDbConnection (GetConnectionString ());
- 69 OleDbCommand command = new OleDbCommand (SQLCommand, connection );
- 70
- 71 for (int I = 0; I <parameters. Length; I ++)
- 72 {
- 73 command. Parameters. Add (parameters );
- 74}
- 75
- 76 connection. Open ();
- 77 OleDbDataReader dataReader = command. ExecuteReader ();
- 78 // connection. Close ();
- 79
- 80 return dataReader;
- 81}
- 82}
- 83}
2.ImplementationSpecificDataAccessOperation
Because we have already definedDataAccessLayer interface, soImplementationDataAccessThe operation class is very mechanical. Only the AdminDataAccessOperation class:
AdminDAL:
AdminDAL
- 1 using System;
- 2 using System. Collections. Generic;
- 3 using System. Text;
- 4 using System. Data;
- 5 using System. Data. OleDb;
- 6 using NGuestBook. IDAL;
- 7 using NGuestBook. Entity;
- 8
- 9 namespace NGuestBook. AccessDAL
- 10 {
- 11 public class AdminDAL: IAdminDAL
- 12 {
- 13 /**////
- 14 // Insert the Administrator
- 15 ///
- 16 ///Administrator entity class
- 17 /// Successful or not
- 18 public bool Insert (AdminInfo admin)
- 19 {
- 20 string SQLCommand = "insert into [TAdmin] ([Name], [Password]) values (@ name, @ password )";
- 21 OleDbParameter [] parameters = {
- 22 new OleDbParameter ("name", admin. Name ),
- 23 new OleDbParameter ("password", admin. Password)
- 24 };
- 25
- 26 try
- 27 {
- 28 AccessDALHelper. ExecuteSQLNonQuery (SQLCommand, parameters );
- 29 return true;
- 30}
- 31 catch
- 32 {
- 33 return false;
- 34}
- 35}
- 36
- 37 /**////
- 38 // Delete the Administrator
- 39 ///
- 40 ///ID of the Administrator to delete
- 41 /// Successful or not
- 42 public bool Delete (int id)
- 43 {
- 44 string SQLCommand = "delete from [TAdmin] where [ID] = @ id ";
- 45 OleDbParameter [] parameters = {
- 46 new OleDbParameter ("id", id)
- 47 };
- 48
- 49 try
- 50 {
- 51 AccessDALHelper. ExecuteSQLNonQuery (SQLCommand, parameters );
- 52 return true;
- 53}
- 54 catch
- 55 {
- 56 return false;
- 57}
- 58}
- 59
- 60 /**////
- 61 // update administrator Information
- 62 ///
- 63 ///Administrator entity class
- 64 /// Successful or not
- 65 public bool Update (AdminInfo admin)
- 66 {
- 67 string SQLCommand = "update [TAdmin] set [Name] = @ name, [Password] = @ password where [ID] = @ id ";
- 68 OleDbParameter [] parameters = {
- 69 new OleDbParameter ("id", admin. ID ),
- 70 new OleDbParameter ("name", admin. Name ),
- 71 new OleDbParameter ("password", admin. Password)
- 72 };
- 73
- 74 try
- 75 {
- 76 AccessDALHelper. ExecuteSQLNonQuery (SQLCommand, parameters );
- 77 return true;
- 78}
- 79 catch
- 80 {
- 81 return false;
- 82}
- 83}
- 84
- 85 /**////
- 86 // obtain administrator information by ID
- 87 ///
- 88 ///Administrator ID
- 89 /// Administrator entity class
- 90 public AdminInfo GetByID (int id)
- 91 {
- 92 string SQLCommand = "select * from [TAdmin] where [ID] = @ id ";
- 93 OleDbParameter [] parameters = {
- 94 new OleDbParameter ("id", id)
- 95 };
- 96
- 97 try
- 98 {
- 99 OleDbDataReader dataReader = AccessDALHelper. ExecuteSQLDataReader (SQLCommand, parameters );
- 100 if (! DataReader. HasRows)
- 101 {
- 102 throw new Exception ();
- 103}
- 104
- 105 AdminInfo admin = new AdminInfo ();
- 106 dataReader. Read ();
- 107 admin. ID = (int) dataReader ["ID"];
- 108 admin. Name = (string) dataReader ["Name"];
- 109 admin. Password = (string) dataReader ["Password"];
- 110
- 111 return admin;
- 112}
- 113 catch
- 114 {
- 115 return null;
- 116}
- 117}
- 118
- 119 /**////
- 120 // obtain administrator information by user name and password
- 121 ///
- 122 ///User Name
- 123 ///Password
- 124 /// Administrator entity class. If the specified object does not exist, null is returned.
- 125 public AdminInfo GetByNameAndPassword (string name, string password)
- 126 {
- 127 string SQLCommand = "select * from [TAdmin] where [Name] = @ name and [Password] = @ password ";
- 128 OleDbParameter [] parameters = {
- 129 new OleDbParameter ("name", name ),
- 130 new OleDbParameter ("password", password ),
- 131 };
- 132
- 133 try
- 134 {
- 135 OleDbDataReader dataReader = AccessDALHelper. ExecuteSQLDataReader (SQLCommand, parameters );
- 136 if (! DataReader. HasRows)
- 137 {
- 138 throw new Exception ();
- 139}
- 140
- 141 AdminInfo admin = new AdminInfo ();
- 142 dataReader. Read ();
- 143 admin. ID = (int) dataReader ["ID"];
- 144 admin. Name = (string) dataReader ["Name"];
- 145 admin. Password = (string) dataReader ["Password"];
- 146
- 147 return admin;
- 148}
- 149 catch
- 150 {
- 151 return null;
- 152}
- 153}
- 154
- 155 /**////
- 156 // obtain administrator information by Administrator name
- 157 ///
- 158 ///Administrator name
- 159 /// Administrator entity class
- 160 public AdminInfo GetByName (string name)
- 161 {
- 162 string SQLCommand = "select * from [TAdmin] where [Name] = @ name ";
- 163 OleDbParameter [] parameters = {
- 164 new OleDbParameter ("name", name ),
- 165 };
- 166
- 167 try
- 168 {
- 169 OleDbDataReader dataReader = AccessDALHelper. ExecuteSQLDataReader (SQLCommand, parameters );
- 170 if (! DataReader. HasRows)
- 171 {
- 172 throw new Exception ();
- 173}
- 174
- 175 AdminInfo admin = new AdminInfo ();
- 176 dataReader. Read ();
- 177 admin. ID = (int) dataReader ["ID"];
- 178 admin. Name = (string) dataReader ["Name"];
- 179 admin. Password = (string) dataReader ["Password"];
- 180
- 181 return admin;
- 182}
- 183 catch
- 184 {
- 185 return null;
- 186}
- 187}
- 188
- 189 /**////
- 190 // obtain all administrator Information
- 191 ///
- 192 /// Administrator entity class set
- 193 public IList GetAll ()
- 194 {
- 195 string SQLCommand = "select * from [TAdmin]";
- 196 try
- 197 {
- 198 OleDbDataReader dataReader = AccessDALHelper. ExecuteSQLDataReader (SQLCommand, null );
- 199 if (! DataReader. HasRows)
- 200 {
- 201 throw new Exception ();
- 202}
- 203
- 204 IList adminCollection = new List ();
- 205 int I = 0;
- 206 while (dataReader. Read ())
- 207 {
- 208 AdminInfo admin = new AdminInfo ();
- 209 admin. ID = (int) dataReader ["ID"];
- 210 admin. Name = (string) dataReader ["Name"];
- 211 admin. Password = (string) dataReader ["Password"];
- 212
- 213 adminCollection. Add (admin );
- 214 I ++;
- 215}
- 216
- 217 return adminCollection;
- 218}
- 219 catch
- 220 {
- 221 return null;
- 222}
- 223}
- 224}
- 225}
It can be seen that there are three types of operations, one is the modified type, such as Insert; the other is to return a single object type, such as GetByID; and the other is the collection type of the returned object class, for example, GetAll.
MessageDAL and CommentDALImplementationVery similar. I will not go into details here.
