ADO. NET --- ExcuteScalar () method review,

ADO. NET --- ExcuteScalar () method review,

ExcuteScalar () returns the first column of the first row of the query result, and the return value is of the object type. It is generally used to query the number of data records in the table and obtain the maximum value.

Now we use ExcuteScalar () for a test. Requirement: I want to query the number of data entries in the student table.

Stored Procedure:

1   IF OBJECT_ID('GetStudentsCountNum','P') IS NOT NULL2   DROP PROCEDURE GetStudentsCountNum3   GO 4   CREATE PROCEDURE GetStudentsCountNum5   AS 6   SELECT COUNT (*) FROM dbo.T_USERS7   GO 8  

1 using System; 2 using System. collections. generic; 3 using System. linq; 4 using System. text; 5 using System. threading. tasks; 6 using System. data; 7 using System. data. sqlClient; 8 9 namespace ADO. NET query result set first row first column 10 {11 class Program12 {13 static void Main (string [] args) 14 {15 // connection string 16 string sqlConStr = "server = .; database = DB_USERS; uid = sa; pwd = Password_1 "; 17 18 // 1. create a connection object 19 SqlConnection scon = new S QlConnection (sqlConStr); 20 21 // 2. CREATE command object 22 SqlCommand scmd = new SqlCommand (); 23 scmd. commandText = "GetStudentsCountNum"; 24 scmd. commandType = CommandType. storedProcedure; 25 scmd. connection = scon; 26 27 // 3. open connection 28 scon. open (); 29 30 // 4, Execute Command 31 int result = (int) scmd. executeScalar (); 32 33 // 5. data processing 34 Console. writeLine ("the total number of queried data rows is {0}", result); 35 36 // 6. Close connection 37 scon. Close (); 38 39 40 Console. ReadKey (); 41 42 43 44} 45} 46}ExcuteScalar () method test

Program execution:

 

However, ExcuteScalar also has a special application.

For example, if I have a database table with a primary key increasing automatically, we all know that at this time, data is inserted into the table, and the Automatically increasing primary key field cannot be inserted, however, I have the following requirement:

I want to get the primary key value of the data inserted into the table each time .. At this time, it is the turn of ExcuteScalar. So at this time, we can draw a conclusion: we should not think that ExcuteScalar is used only when the select operation is performed. For example, this Insert operation outputs the values of one row and one column, you can also use the ExcuteScalar method.

The specific method is. Add output inserted. ID before the values keyword of the insert statement. The ID here refers to the primary key field.

Create a stored procedure: (let's create two here, one is an incorrect instance and the other is a correct instance)

1 IF OBJECT_ID ('insert _ users', 'P') is not null 2 drop procedure Insert_Users 3 GO 4 create procedure Insert_Users 5 @ name NVARCHAR (10 ), 6 @ pwd NVARCHAR (10), 7 @ age INT, 8 @ errorTimes INT 9 AS 10 insert into dbo. t_USERS11 (T_NAME, T_PWD, T_AGE, T_ErrorTimes) 12 OUTPUT Inserted. t_ID VALUES ('@ name', -- T_NAME-nvarchar (10) 13' @ pwd ', -- T_PWD-nvarchar (10) 14' @ age ', -- T_AGE-int15' @ errorTimes '-- T_ErrorTimes-int16) 17 GO18Error instance: the age and errorTimes fields are int type in the database. When creating a stored procedure, you cannot quote the parameters. Otherwise, an error is reported during programming.

Even if the int type parameter is declared separately in the program, an error is still returned. Pay special attention to this.

Correct Stored Procedure instance:

1 IF OBJECT_ID ('insert _ users', 'P') is not null 2 drop procedure Insert_Users 3 GO 4 create procedure Insert_Users 5 @ name NVARCHAR (10 ), 6 @ pwd NVARCHAR (10), 7 @ age INT, 8 @ errorTimes INT 9 AS 10 insert into dbo. t_USERS11 (T_NAME, T_PWD, T_AGE, T_ErrorTimes) 12 OUTPUT Inserted. t_ID VALUES (@ name, -- T_NAME-nvarchar (10) 13 @ pwd, -- T_PWD-nvarchar (10) 14 @ age, -- T_AGE-int15 @ errorTimes -- T_ErrorTimes-int16) 17 GO18Correct Stored Procedure instance

The code is implemented as follows:

1 using System; 2 using System. collections. generic; 3 using System. linq; 4 using System. text; 5 using System. threading. tasks; 6 using System. data; 7 using System. data. sqlClient; 8 9 namespace when the primary key increases automatically _ use ExcuteScalar10 {11 class Program12 {13 static void Main (string [] args) to query the primary key value during each insertion) 14 {15 string sqlConStr = "server = .; database = DB_USERS; uid = sa; pwd = Password_1 "; 16 17 // 1. create a connection object 18 SqlConnection scon = new SqlConnection (sqlConStr); 19 20 // 2. CREATE command object 21 SqlCommand scmd = new SqlCommand (); 22 scmd. commandText = "Insert_Users"; 23 scmd. commandType = CommandType. storedProcedure; 24 scmd. connection = scon; 25 26 27 // 3. open connection 28 scon. open (); 29 30 // set parameter 31 scmd. parameters. add (new SqlParameter ("@ name", "Test"); 32 scmd. parameters. add (new SqlParameter ("@ pwd", "123456"); 33 scmd. parameters. add (new SqlParameter ("@ errorTimes", 2); 34 35 36 SqlParameter parameters = new SqlParameter (); 37 parameters. value = 2; 38 parameters. dbType = DbType. int32; 39 parameters. parameterName = "@ age"; 40 scmd. parameters. add (parameters); 41 42 // 4 execute the command 43 int id = (int) scmd. executeScalar (); 44 45 Console. writeLine ("the primary key of the data you inserted this time is {0}", id); 46 Console. readKey (); 47 48 49 50} 51} 52}Coding implementation

Program running:

 

Note: If you want to restore the primary key field, you can use the truncate table name to clear the data. During execution, it starts from primary key 1.