ADO. NET Learning Series (4) --- form version of the login applet, ado.net form

ADO. NET Learning Series (4) --- form version of the login applet, ado.net form

1. Requirement Analysis: Create a login Applet based on Winform applets. Basic requirements: logon successful: the pop-up box displays logon successful. If logon fails, the pop-up box displays failure.

Extended Function: if the number of logon attempts exceeds 3, the user will be "locked" and the system prompts that there are too many logon errors and cannot log on. After the user clicks, the program is exited. After Successful Logon, the error field is cleared.

Here we add an error field in the database table. The type is int and the initial value is set to 0.

I believe that this small program is super simple for everyone. I am here to familiarize myself with the basic knowledge.

2. Technologies used: ADO. NET, stored procedure.

3. project implementation:

• First, we create a form application, and then put the corresponding control ,:

 

 

• Create a database and a stored procedure:
• 1 USE [DB_USERS] 2 GO 3 4/****** Object: Table [dbo]. [T_USERS] Script Date: 06/07/2015 17:48:33 *****/5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO10 11 create table [dbo]. [T_USERS] (12 [T_ID] [INT] IDENTITY (1, 1) not null, 13 [T_NAME] [NVARCHAR] (10) not null, 14 [T_PWD] [NVARCHAR] (10) not null, 15 [T_AGE] [INT] not null, 16 [T_ErrorTimes] [INT] not null, 17 primary key clustered 18 (19 [T_ID] ASC20) WITH (PAD_INDEX = OFF, rows = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 21) ON [PRIMARY] 22 23 GODatabase script

* I have created a database table before. Now I can directly compile it into a script. Write the Stored Procedure

* Query information based on the user name (because I wrote previously to query user information based on the user name, But now I change the name of the previously written stored procedure)

We can use: EXEC sp_rename 'old name', 'new name ',

Here, I will not post a stored procedure to query user information based on the user name. You can refer to my previous series of articles.

* If a user input error occurs, add 1 to the error field:

1  IF OBJECT_ID('Incre_errorTimes','P') IS NOT NULL2  DROP PROCEDURE Incre_errorTimes3  GO 4  CREATE PROCEDURE Incre_errorTimes5  @userName nvarchar(10)6  AS 7  UPDATE dbo.T_USERS SET T_ErrorTimes=T_ErrorTimes+1 WHERE T_NAME=@userName8  GO 9  

* The user input is correct. Set the error field value to 0.

1  IF OBJECT_ID('Reset_errorTimes','P') IS NOT NULL2  DROP PROCEDURE Reset_errorTimes3  GO 4  CREATE PROCEDURE Reset_errorTimes5  @userName nvarchar(10)6  AS 7  UPDATE dbo.T_USERS SET T_ErrorTimes=0 WHERE T_NAME=@userName8  GO 9  

 

After the database tables and stored procedures are completed, we can write the program.

The connection string here is stored in the configuration file. I will not go into details about the specific operation.

Encoding implementation:

 

1 using System; 2 using System. collections. generic; 3 using System. componentModel; 4 using System. data; 5 using System. drawing; 6 using System. linq; 7 using System. text; 8 using System. threading. tasks; 9 using System. windows. forms; 10 using System. data; 11 using System. data. sqlClient; 12 13 namespace Form Login program 14 {15 public partial class Login: Form 16 {17 public Login () 18 {19 InitializeComponen T (); 20} 21 // <summary> 22 // increase the number of errors 23 /// </summary> 24 public void IncerErrTimes (string SQL, params SqlParameter [] parmeters) 25 {26 // 1 create a connection object 27 SqlConnection scon = new SqlConnection (DBHelper. getConnectString (); 28 // 2 CREATE command object 29 SqlCommand scmd = new SqlCommand (); 30 scmd. commandText = SQL; 31 scmd. commandType = CommandType. storedProcedure; 32 scmd. connection = scon; 33 // 3 open Connection 34 scon. O Pen (); 35 36 // configure parameter 37 foreach (SqlParameter item in parmeters) 38 {39 scmd. parameters. add (item); 40 41} 42 43 // 4 Execute Command 44 // 5. processing Data 45 scmd. executeNonQuery (); 46 47 // 6 close connection 48 scon. close (); 49 50 51} 52 53 // <summary> 54 // The number of record errors 55 /// </summary> 56 public void ResetErrTimes (string SQL, params SqlParameter [] parmeters) 57 {58 // 1 create a connection object 59 SqlConnection scon = new SqlConnection (DBHelper. get ConnectString (); 60 // 2 CREATE command object 61 SqlCommand scmd = new SqlCommand (); 62 scmd. commandText = SQL; 63 scmd. commandType = CommandType. storedProcedure; 64 scmd. connection = scon; 65 // 3 open Connection 66 scon. open (); 67 68 // configure the parameter 69 foreach (SqlParameter item in parmeters) 70 {71 scmd. parameters. add (item); 72 73} 74 // 4 Execute Command 75 // 5. process Data 76 scmd. executeNonQuery (); 77 78 // 6 close connection 79 scon. close (); 80 81} 82 83 pri Vate void btnLogin_Click (object sender, EventArgs e) 84 {85 // obtain the user name and password entered by the user 86 string usernName = txtLoginName. text. trim (); 87 string password = txtPassword. text. trim (); 88 89 // connection method for database access 90 91 // 1. create a connection object (connection string) 92 SqlConnection scon = new SqlConnection (DBHelper. getConnectString (); 93 94 // 2 CREATE command object 95 SqlCommand scmd = new SqlCommand (); 96 scmd. commandText = "GetUserInfoByName"; 97 scmd. commandTy Pe = CommandType. storedProcedure; 98 scmd. connection = scon; 99 100 // 3 open Connection 101 scon. open (); 102 103 // Configuration parameter 104 SqlParameter parameter = new SqlParameter (); 105 parameter. parameterName = "@ name"; 106 parameter. dbType = DbType. string; 107 parameter. value = usernName; 108 parameter. direction = ParameterDirection. input; 109 scmd. parameters. add (parameter); 110 111 // 4 Execute Command 112 SqlDataReader reader = scmd. execute Reader (CommandBehavior. CloseConnection); 113 114 115 116 // 5. process data. 117 if (reader. read () 118 {119 int errorTimes = (int) reader ["T_ErrorTimes"]; 120 if (errorTimes> 3) 121 {122 MessageBox. show ("too many logon errors", "friendly tips", MessageBoxButtons. OK, MessageBoxIcon. warning); 123 return; 124} 125 126 if (password = reader. getString (reader. getOrdinal ("T_PWD") 127 {128 MessageBox. show ("Logon successful", "friendly prompt", MessageBoxButtons. OK, MessageBoxIcon. information); 129 ResetErrTimes ("Reset_errorTimes", new SqlParameter ("@ userName", usernName); 130 131} 132 elseeter {134 MessageBox. show ("Password error", "friendly prompt", MessageBoxButtons. OK, MessageBoxIcon. error); 135 IncerErrTimes ("Incre_errorTimes", new SqlParameter ("@ userName", usernName); 136 137} 138 else140 {139 MessageBox. show ("user name does not exist", "friendly prompt", MessageBoxButtons. OK, MessageBoxIcon. warning); 142} 143 144 // 6. close the connection to 145 reader. close (); 146 147 148} 149} 150}Coding implementation

Several problems were encountered during the implementation of this function:

1. When creating a stored procedure, the parameter @ userName is used. The stored procedure for querying user information by user name only uses the @ name parameter. The two parameters should be separated ..

2. when you create a public void ResetErrTimes (string SQL, params SqlParameter [] parmeters) and public void IncerErrTimes (string SQL, params SqlParameter [] parmeters) method to encapsulate the query, written as public void ResetErrTimes (string SQL, params string [] parmeters). As a result, an error is returned when this method is called later.

3. When adding parameters cyclically, I wrote the following error:

Foreach (var item in parmeters)
{
Scmd. Parameters. Add (parmeters); // This is incorrect.

}

4. Rename the stored procedure: EXEC sp_rename 'old name', 'new name'

Which of the following statements is true:

Foreach (SqlParameter item in parmeters)
{
Scmd. Parameters. Add (item );

}

Program execution:

In general, I have gained a lot through this example. What about everyone?