How does ADO. Net obtain data from the database?

(1). Description

When I was a beginner, ADO. Net made me dizzy when accessing the database by ADO. net.
In this article, they are integrated, and the article does not involve too much deep knowledge, but I think they are integrated together,
In comparison, learning is faster and clearer:
This article lists all the methods for accessing the database using ADO. Net:
1. Access the database using SQL statements
2. Access the database using a stored procedure without Parameters
3. Access the database using a stored procedure with Parameters
4. Use commandbuilder. deriveparameters to fill the parameters set of the command object to access the database.
5. Use the sqldatareader object to output data.

(2). Sample Code

The stored procedure myprocedure SQL code used:
--- Parameters

Create procedure DBO. [myprocedure]
(
@ Start int,
@ End int
)
As
Select * from products
Where productid> = @ start and productid <= @ end

--- No Parameter
Create procedure DBO. [myprocedure]
As
Return 'hello'
Go

Private dataset getdataset (INT sign)
{

// ******** Custom connection string ********/
String connectionstr = "Server = (localhost); uid = sa; Pwd =; database = northwind ";

Sqlconnection myconnection = new sqlconnection (connectionstr );
Dataset mydataset = new dataset ();
String sqlprocedurename = "myprocedure"; // This stored procedure must be defined in advance.
If (Sign = 1) // execute the statement. (the most common case)

{
String querystr = "select * from products ";
Sqldataadapter myadapter = new sqldataadapter (querystr, connectionstr );

Myadapter. Fill (mydataset );
Return mydataset;
}

If (Sign = 2) // call the SQL stored procedure without parameters. This stored procedure returns the SELECT query result
{
Sqlcommand mycommand = new sqlcommand (sqlprocedurename, myconnection );
Mycommand. commandtype = commandtype. storedprocedure;
Sqldataadapter myadapter = new sqldataadapter (sqlprocedurename, myconnection );
Myadapter. Fill (mydataset );
Return mydataset;
}
If (Sign = 3) // call the SQL stored procedure with parameters. This stored procedure returns the SELECT query result and passes Parameters
{
Sqlcommand mycommand = new sqlcommand (sqlprocedurename, myconnection );
Mycommand. commandtype = commandtype. storedprocedure;

Sqlparameter start = new sqlparameter ("@ start", sqldbtype. Int, 4); // define the first parameter

Start. value = 1;
Mycommand. Parameters. Add (start );

Sqlparameter end = new sqlparameter ("@ end", sqldbtype. Int, 4); // define the second parameter

End. value = 5;
Mycommand. Parameters. Add (end );

Sqldataadapter myadapter = new sqldataadapter (sqlprocedurename, myconnection );
Myadapter. selectcommand = mycommand;

Myadapter. Fill (mydataset );
Return mydataset;
}
If (Sign = 4) // demonstrate how to use commandbuilder. deriveparameters to fill in the parameters set of the command object
{
Sqlcommand mycommand = new sqlcommand (sqlprocedurename, myconnection );
Mycommand. commandtype = commandtype. storedprocedure;
Sqldataadapter myadapter = new sqldataadapter (sqlprocedurename, myconnection );

Myconnection. open (); // If the connection is not enabled, the next sentence will not know where to fetch the required Stored Procedure
Sqlcommandbuilder. deriveparameters (mycommand );
Mycommand. Parameters ["@ start"]. value = 1;
Mycommand. Parameters ["@ end"]. value = 5;
Myadapter. selectcommand = mycommand;

Myadapter. Fill (mydataset );
Return mydataset;

}
If (Sign = 5) // exercise using sqldatareader, the result is output on the console
{
Sqlcommand mycommand = new sqlcommand (sqlprocedurename, myconnection );
Mycommand. commandtype = commandtype. storedprocedure;

Sqlparameter start = new sqlparameter ("@ start", sqldbtype. Int, 4); // change or remove 4
Start. value = 1;
Mycommand. Parameters. Add (start );

Sqlparameter end = new sqlparameter ("@ end", sqldbtype. Int, 4); // change or remove 4
End. value = 5;
Mycommand. Parameters. Add (end );

Sqldataadapter myadapter = new sqldataadapter (sqlprocedurename, myconnection );
Myadapter. selectcommand = mycommand;

Myconnection. open ();
Sqldatareader myreader = mycommand. executereader ();

Console. writeline ("{0}, {1}", myreader. getname (0), myreader. getname (1 ));

While (myreader. Read ())
{
// Console. writeline ("{0}, {1}", myreader. getstring (0), myreader. getstring (1 ));//? No
Console. writeline ("{0}, {1}", myreader. getvalue (0), myreader. getvalue (1 ));
}

Myconnection. Close ();
Datatable mytable = new datatable ();
Mydataset. Tables. Add (mytable );
Return mydataset;
}
Return new dataset ();
}