Analysis of ADO. Net (III)

For the first articleArticleI am personally satisfied, at leastCodeAnnotations are easy to understand.

Below are some supplements to the previous article

About sqlcommand,

And then I made some supplements to this.

If you use SQL Server versions earlier than SQL Server 2005, when using sqldatareader, the associated sqlconnection will be busy serving sqldatareader. When it is in this status, you cannot perform any operations on it except to disable sqlconnection. Unless you call the close method of sqldatareader, it will remain in this status. From SQL Server 2005, the multi-activity result set (MARS) function allows multiple operations to use the same connection.

 

Some details about the executereader method:

1. Execute the command to return the line. To improve performance, executereader uses the Stored Procedure Call Command of the transact-SQL sp_executesql system. Therefore, if executereader is used to execute commands(For example, a Transact-SQL set statement), it may not produce the expected results.

 

MSSQL provides two types of commands for dynamic execution of SQL statements: exec and sp_executesql. Generally, sp_executesql is more advantageous. It provides an input and output interface, but exec does not. Another major benefit is that the execution plan can be reused using sp_executesql, which greatly provides the execution performance (for details in the following example ), you can also write safer code. Exec is more flexible in some cases. Unless you have a convincing reason to use exec, use sp_executesql whenever possible.

 

 

The following is an instance using datareader.

 

Public   Void Dbprovider ()
{
String DP = Configurationmanager. connectionstrings [ " Adwentities " ]. Providername; // Obtain the providername In the config file
String Connstr = Configurationmanager. connectionstrings [ " Adwentities " ]. Connectionstring; // Get connectionstring

Dbproviderfactory DF=Dbproviderfactories. getfactory (DP );//Create a provierfactory instance to generate instances of connection and command objects;

Dbconnection Conn=DF. createconnection ();

Conn. connectionstring=Connstr;

Conn. open ();

Dbcommand dbcmd = DF. createcommand ();
Dbcmd. Connection = Conn;
Dbcmd. commandtype = Commandtype. storedprocedure; // Set commandtype to Stored Procedure
Dbcmd. commandtext =   " Uspgetemployeemanagers " ; // Set the stored procedure name

Dbparameter DBP =   New Sqlparameter ( " @ Businessentityid " , Sqldbtype. INT );
DBP. Value =   " 2 " ; // Generate parameter object
Dbcmd. Parameters. Add (DBP );

Dbdatareader Rd = Dbcmd. executereader (commandbehavior. closeconnection ); // Commandbehavior. closeconnection parameter. When RD. Close (), close connection

While (RD. Read ())
{
Console. writeline ( " Empname {0} {1}, \ r \ nmanagername {2} {3} " , RD [ 2 ], RD [ 3 ], RD [ 5 ], RD [ 6 ]);
}
Rd. Close ();

}

 

The first step is to use the configuration file.

The configurationmanager. connectionstrings attribute can be used by the following nodes in the config file:

 

< Connectionstrings >
< Add Name = "Adwentities" Connectionstring = "Data Source = localhost; initial catalog = adventureworks2008; user id = sa; Password = pass ;" Providername = "System. Data. sqlclient" />
</ Connectionstrings >

 

in the factory mode, instances are generated using strings. datareader generation is irrelevant to the specific database and changes are well encapsulated. sqlparameter's use destroys this encapsulation, we will discuss the design model in the future.