Personnel Management System (I), personnel management system (

Personnel Management System (I), personnel management system (

As the first complete and simple. NET project, I am very happy to complete the initial exploration of the personnel system within one month. Follow instructor Yang zhongke's footsteps to learn about database. net. Below I will make a brief summary of the knowledge used by the entire system. If you have any questions, please correct them:

(1) Introduction to the three-tier architecture:

During the development of the personnel management system, we set up a three-tier architecture. In the vs project dependency diagram:

BLL separates USL from DAL and adds Business Rules (document from: http://www.cnblogs.com/gaoweipeng/archive/2009/01/18/1377855.html)

• Role of each layer
• 1: Data access layer: mainly refers to the operation layer for raw data (in the form of storing data such as databases or text files), rather than raw data, that is, it refers to data operations, instead of databases, it provides data services for the business logic layer or presentation layer.
  
  2: business logic layer: This layer is mainly used to address specific problems. It can also be understood as a pair of operations on the data layer. If the data layer is a building block, the logical layer is the construction of these blocks.
  
  3: Presentation Layer: It mainly indicates the WEB mode or WINFORM mode. The WEB mode can also be represented as aspx. If the logic layer is quite powerful and complete, the logic layer provides complete services regardless of how the presentation layer is defined and changed.

• Specific differentiation methods
  
  1: Data access layer: it mainly depends on whether your data layer contains logic processing. In fact, its functions mainly perform operations on data files. You do not have to worry about other operations.
  
  2: business logic layer: mainly responsible for operations on the data layer. That is to say, some data layer operations are combined.
  
  3: Presentation Layer: This layer mainly accepts user requests and returns data to provide client access to applications.

 

• Layer-3 structure explanation
  
  The layer-3 architecture adds an intermediate layer between the client and the database, also known as the component layer. The layer-3 system mentioned here does not refer to the layer-3 physical architecture, instead of simply placing three machines as the layer-3 architecture, or not just B/S applications as the layer-3 architecture, layer 3 refers to the logic layer 3, even if these three layers are placed on one machine. Applications in the layer-3 system put business rules, data access, and legality verification into the middle layer for processing. Normally, the client does not directly interact with the database, but establishes a connection with the middle layer through COM/DCOM communication, and then exchanges with the database through the middle layer.
  
  Developers can place the business logic of an application on the application server in the middle layer and separate the business logic of the application from the user interface. Provides a simple interface while ensuring the client functions. This means that if you need to modify the application code, you only need to modify the intermediate application server, instead of modifying thousands of client applications. This allows developers to focus on the analysis, design, and development of the core business logic of the application system, simplifying the development, update, and upgrade of the application system.

• So why should we apply the "intermediate business layer? For example:
  Assume that there is a piece of login code, you can process the Web program in this way. The appearance layer is responsible for receiving the data on the front-end page, and then passing it to the middle layer. The middle layer processes the data, such as formatting, anti-SQL injection and so on. Such data is then transmitted to the data access layer and then operated with the database, such as matching the database user name and password and other code.
• The "intermediate business layer" has many functions, such as verifying user input data and caching data read from the database ...... However, the actual purpose of the "intermediate business layer" is to combine the basic storage logic of the "data access layer" to form a business rule. For example, "a shopping website has such a rule: the system automatically registers users who shop for the first time on the website ". Such business logic is most suitable in the middle layer:

In the "data access layer", it is best not to see any "business logic "! That is to say, to ensure the atomicity of function functions in the "data access layer! That is, the minimum and no score is allowed. The "data access layer" only stores or reads data.
 

However, in our project, The BLL layer may be written to the USL layer because the BLL layer is rarely used, in this way, we have made a simplified three-tier architecture and filled in the MODEL layer for concatenation.

(2) Simple sorting of ADO. NET (Literature from: ADO. NET-comprehensive sorting)

2.1 sqlConnection connection object:

2.1.1. connection string

Basic Syntax: Data Source + Database Name (Initial Catalog) + User ID + Password (secure )!

Recommended article: SQL Server connection string and authentication, you must know the ADO. NET (3) connection string, are you down ?, SQL Server 2008 connects to the string writing Daquan, there are a lot of writing methods to connect to the string, the most safe way to write with the help of the "SqlConnectionStringBuilder" class, it provides a comprehensive connection string attributes, this reduces the error rate (related attribute query MSDN), and most connection strings are written in the configuration file!

2.1.2. Create a connection object

SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder() {     DataSource = "",     InitialCatalog = "",     UserID = "",     Password = "" };SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

Of course, you can configure the link object in config:

  <connectionStrings>    <add name="dbConnStr" connectionString ="Data Source=.;Initial Catalog=;User ID=;Password="/>  </connectionStrings>

Then use the code to connect:

private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;

2.1.3. Open and Close the connection object (use Using to close the connection)

 using(SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString())) {     connection.Open();     connection.Close(); }

 

2.2 SqlCommand (command object)

2.2.1. Four attributes initialized by default during instantiation

2.2.2. Create a command object

Use the "CreateCommand ()" method of the connection object to create a command object. You can also use new to instantiate the object!

1 SqlCommand command = connection. CreateCommand (); // This method is better. You can also instantiate an object yourself!

　2.2.3. Several Important attributes

① CommandText: gets or sets the Transact-SQL statement, table name, or stored procedure to be executed on the data source!

CommandType: Set whether the SQL statement you run is stored procedure or T-SQL (an enumeration )!

　　　　

3 Parameters: set the Parameters you need in your T-SQL (will be discussed later), is a "SqlParametersCollection" type, this attribute is very important, it is the way you pass parameters to SQL statements through code, so remember the syntax and remember some usage rules, which is very helpful for encoding!

2.2.4. Several important methods (I believe you are not familiar with any more)

① ExecuteNonQuery: number of rows affected (int) returned. Update, add, delete, and other operations are performed!

② ExecuteReader: Execute SQL or stored procedures. The returned data type is SqlDataReader, which is mainly used for query!

★Here, pay attention to the CommandBehaviour enumeration for this method. The members are as follows:

　　　　

1 command. ExecuteReader (CommandBehavior. CloseConnection); // The connection object is automatically closed after the read is executed.

③ ExecuteScalar: return the first column in the first row of the execution result set. If no data exists, NULL is returned!

Note: Because the return value may be "Null", You need to judge the result as follows:

View Code

1 object my = cmd. executeScalar (); 2 if (object. equals (my, null) // you can use Equals to determine the Null value, which is easy to use. writeLine ("Not Data"); 4 else5 Console. writeLine ("Yes ");

④ CreateParameter: Create a SqlParameter instance

1 SqlParameter para = cmd. CreateParameter () // This method is suitable for SQL statements with only one parameter!

Recommended article: ADO. NET (6) You must know about Command objects and data retrieval

You must know ADO. NET (7) Wow! Advanced Application of Command object

 

2.3 SqlParameter (SQL parameter) ----- if this parameter is not used, an SQL vulnerability injection attack will occur.

2.3.1. Several Important attributes

ParameterName: Set the parameter name.

Value: Set the parameter Value.

Size: sets the maximum Size of the Parameter bytes (in bytes)

SqlDbType: Type of a parameter in SQL

1 SqlParameter paras = new SqlParameter()2              {3                     ParameterName = "@name",4                     Value = 10,5                     SqlDbType = SqlDbType.Int,6                     Size = 47              };

2.3.2. Several Methods for adding a parameter set to a command object

① AddWithValue

② Add

③ AddRange

Recommended article: Functions and usage of SqlParameter, the Code is as follows:

1 using (SqlConnection connection = new SqlConnection ("") 2 {3 SqlCommand command = connection. createCommand (); 4 command. commandText = ""; 5 6 // you can use this method to add multiple parameters, but the method is not good enough. 7 command. parameters. add ("@ name", SqlDbType. NVarChar ). value = "yang"; // The first method is 8 command. parameters. add ("@ age", SqlDbType. int ). value = 888; 9 command. parameters. add ("@ address", SqlDbType. NVarChar, 100 ). value = "Jiang Su"; 10 11 // This method can be used to directly specify the parameter name and parameter. The operability is less than 12 commands. parameters. addWithValue ("@ name", "yang"); 13 command. parameters. addWithValue ("@ age", 888 ). sqlDbType = SqlDbType. int; 14 command. parameters. addWithValue ("@ address", "Jiang su "). sqlDbType = SqlDbType. NVarChar; 15 16 // Add the required parameters directly using the parameter set. We recommend that you write 17 SqlParameter [] parameters = new SqlParameter [] 18 {19 new SqlParameter ("@ name ", sqlDbType. NVarChar, 100) {Value = "yang"}, 20 new SqlParameter ("@ age", SqlDbType. int, 2) {Value = 888}, 21 new SqlParameter ("@ address", SqlDbType. NVarChar, 20) {Value = "Jiang Su"}, 22}; 23 command. parameters. addRange (parameters); // The parameter can also be an Array, if the array parameter code is used for readability and scalability, it is not as good as 24 25 // when we add all the parameters, a "SqlParameterCollection" set type will be generated, equivalent to the set of parameters 26 //, We can modify and remove these parameters 27 // The "SqlParameterCollection" is actually a set of List <SqlParameter>, however, the complexity in it is relatively high, with a comprehensive consideration of 28 commands. parameters [0]. value = "hot girl"; 29 command. parameters [0]. size = 200; 30}

　2.3.3. "SqlParameterCollection", parameter set

The "SqlParameter" parameter added above is added to the "SqlParameterCollection" set, so we can read and modify it!

2.4 SqlDataAdapter (data adapter)

2.4.1. Constructor

1. Four reloads: 2. no parameter 3 2. sqlDataAdapter (SqlCommand) → execution command object instance 4 3. sqlDataAdapter (String, SqlConnection) → ① only query statements can be specified ② connect object instance 5 4. sqlDataAdapter (String, ConnectionString) → use SelectCommand and a connection String to initialize a new instance of the SqlDataAdapter class. 6 Note: The fourth overload will include the connection object and command object!

2.4.2. Fill data (Fill)

Simplest data Filling

1 DataSet dataSet = new DataSet (); 2 using (SqlConnection conn = new SqlConnection ("") 3 {4 conn. open (); 5 SqlCommand command = conn. createCommand (); 6 command. commandText = "select name, age, address from MyInformation"; 7 SqlDataAdapter dataAdapter = new SqlDataAdapter (command); 8 dataAdapter. fill (dataSet); // Fill data 9}

2.4.3. Use "SqlCommandBuilder" to add, delete, modify, and query data

① Add data

1 using (SqlConnection conn = new SqlConnection (ConnectionString () 2 {3 conn. open (); 4 // construct a query statement. You can also specify SqlCommand. There are many conversion methods: 5 SqlDataAdapter da = new SqlDataAdapter ("select LastName, FirstName from dbo. employees ", conn); 6 DataSet ds = new DataSet (); 7 da. fill (ds); 8 // This sentence is very important. It will convert the data you added in DataSet into SQL statements to update database 9 SqlCommandBuilder cmdBuilder = new SqlCommandBuilder (da ); 10 // Add a row and instantiate a row object. Note that NewRow is used to create the 11 DataRow row = ds. tables [0]. newRow (); 12 row [0] = "Yang"; 13 row [1] = "ghost Header"; 14 ds. tables [0]. rows. add (row); // Add to Table 15 da. update (ds); // compare tables and databases in DataSet, and Update 16}

② Modify data

1 using (SqlConnection conn = new SqlConnection ("") 2 {3 SqlDataAdapter da = new SqlDataAdapter ("SQL statement or your own command object", conn ); 4 DataSet ds = new DataSet (); 5 da. fill (ds); 6 // very important sentence 7 SqlCommandBuilder cmdBuilder = new SqlCommandBuilder (da); 8 ds. tables [0]. rows [12] [1] = ""; // modify data 9 da. update (ds); 10 // call the Update method where the AcceptChanges method is implicitly called, and the data in the dataset is updated 11 // if you continue to use this dataset without calling this method, exception 12 ds will occur in subsequent use. acceptChanges (); // This sentence can be left blank 13}

③ Delete data

1 using (SqlConnection conn = new SqlConnection ("") 2 {3 SqlDataAdapter da = new SqlDataAdapter ("SQL statement or your own command object", conn ); 4 DataSet ds = new DataSet (); 5 da. fill (ds); 6 SqlCommandBuilder cmdBuilder = new SqlCommandBuilder (da); 7 // delete data 8 ds. tables [0]. rows [12]. delete (); 9 da. update (ds); // The AcceptChanges Method 10 of the DataTable is called implicitly}

Note (important Note): It is worth noting that the Update method has implicitly called AcceptChanges for me. It is no longer worried that an error will be reported when the status is changed to delete data. Microsoft has already done a good job for us. In fact, it is behind the Table. acceptChanges (). If it is in a common DataTable, It is submitted from the last call.AcceptChangesAll changes made to this row since the beginning. After this method is called, all changes in the table will be submitted, and all row statuses (RowState) will change to Unchanged, in DataSet, I will talk about this content!

2.4.4. Some discussions about the Fill method in "SqlDataAdapter"

It's easy to write. It's available on MSDN. Check it and you will find it!

1★Specify the number of data filled, for example, 4 // It should start from the sixth row, followed by 10 records 5 da. Fill (ds, 5, 10, "MyTable ")

2.5 DataSet, DataTable, DataRow, DataColumn

Indicates that the data is stored in the cache. DataSet can contain multiple datatables. DataTable has multiple datacolumns and multiple DataRow, including various DataTable operations and column and row operations, when you perform DataSet and DataTable operations, you should first determine whether they are Null. This is the most basic!

　　1. DataTable, DataRow, DataColumn

① Create your own able:

1 DataTable dt = new DataTable ("Table"); 2 DataColumn columnName = new DataColumn (); 3 columnName. columnName = "Name"; 4 // columnName. dataType = typeof (string); 5 // note the comparison with the above sentence 6 columnName. dataType = Type. getType ("System. string "); 7 columnName. defaultValue = "YangCaoGui"; 8 dt. columns. add (columnName); // set of DataColumnCollection 9 DataRow row = dt. newRow (); // use the NewRow method to instantiate a row 10 row [columnName] = "WangWei"; // There are many 11 dt indexes. rows. add (row); // set of DataRowCollection 12 Console. writeLine ("Type:" + dt. columns [0]. dataType. name );

② Use the object collection initiator to simplify the code and use "DataColumnCollection" and "DataRowCollection" to operate the added rows and columns

The access modifier of the constructor is Internal. You can use these two sets to Add, delete, modify, and query the Column and Row. For details, see MSDN, such as Remove, Add, removeAt etc!

1 dt. columns. add (new DataColumn ("Age", typeof (Int32); 2 dt. columns. add (new DataColumn () 3 {4 ColumnName = "Address", 5 DataType = typeof (string), 6 DefaultValue = "Jiangsu Hai 'an" 7 }); 8 // here we use the second overload of the Add Method 9 dt. rows. add (new object [] {"11", 44, "222", "yang cao gui "}); 10 // We can also read and modify the added rows and columns 11 dt. columns [0]. columnName = "wang wei"; 12 dt. rows [0] ["wang wei"] = "I modified the value of this row, haha ";

③ Use the expression tree to quickly build your own columns. For details, see the MSDN

Public static void DataTableExpression () {DataTable dt = new DataTable ("Table"); DataColumn price = new DataColumn ("price", typeof (Int32 )); dataColumn number = new DataColumn ("number", typeof (Int32); dt. columns. add (price); dt. columns. add (number); for (int I = 1; I <= 5; I ++) {DataRow newRow = dt. newRo (); newRow ["price"] = I; newRow ["number"] = I + 5; dt. rows. add (newRow);} // displays table data for (int I = 0; I <dt. rows. count; I ++) {Console. writeLine ("Price: {0}, Number: {1}", dt. rows [I] ["price"], dt. rows [I] ["number"]);} // use Expression to customize your Table Console. writeLine ("---------------------------------"); DataColumn total = new DataColumn ("total", typeof (Int32); dt. columns. add (total); // you can use this method to customize your own DataTable dt. columns ["total"]. expression = "price * number"; // display the customized data Console. writeLine ("show data"); for (int I = 0; I <dt. rows. count; I ++) {Console. writeLine ("Price: {0}, Number: {1}, Total: {2}", dt. rows [I] ["price"], dt. rows [I] ["number"], dt. rows [I] ["total"]);}




Some encapsulated database operation classes of sqlhelper will be added to the favorites next time (this is the essence)
Below are some links for recommendation:

Only by fully understanding the above knowledge can we better encapsulate and write robust object-oriented database operation class libraries. Due to my limited knowledge, simple encapsulation is acceptable, but not easy to understand, so we collected some good articles for us to learn:

1. SqlHelper-a double-edged sword with a high height (is it easy to use ~~) (Extract more robust code)

2. My DbHelper data operation class (custom operation class library)

3. Download The JSM SqlHelper 2.0 source code (more detailed encapsulation, the opportunity to study)

4. The original Microsoft SQLHelper class (there are too many codes, so we won't be able to use them, and there are too many reloads !)

Well, that's all. It's easy to think about it. It's hard to think about how complicated it is to enter the sky. Haha, I still want to look at my personal choices. There are also a lot of articles in this area in the garden, after a good study, it will be OK!