SqlAdapter generates DataSet or DataTable

Sqladapter generates dataset or datatable
1. Create a sqlcommand object to call the stored procedure and associate it with a sqlconnection object (displayed) or connection string (not displayed.
 
2. Create a New sqldataadapter object and associate it with the sqlcommand object.
 
3. Create a able (or a dataset) object. Use the constructor parameters to name the able.
 
4. Call the fill method of the sqldataadapter object and fill the dataset or able with the retrieved rows.

 

How to Use sqldatareader to retrieve multiple rows
The following code snippet clarifies the sqldatareader Method for retrieving multiple rows.

Using system. io;
2 using system. data;
3 using system. data. sqlclient;
4
5 public sqldatareader retrieverowswithdatareader ()
6 {
7 sqlconnection conn = new sqlconnection (
8 "server = (local); integrated security = sspi; database = northwind ");
9 sqlcommand cmd = new sqlcommand ("datretrieveproducts", conn );
10 cmd. commandtype = commandtype. storedprocedure;
11 try
12 {
13 conn. open ();
14 // generate the reader. commandbehavior. closeconnection causes
15 // the connection to be closed when the reader object is closed
16 return (cmd.exe cutereader (commandbehavior. closeconnection ));
17}
18 catch
19 {
20 conn. close ();
21 throw;
22}
23}
24
25 // display the product list using the console
26 private void displayproducts ()
27 {
28 sqldatareader reader = retrieverowswithdatareader ();
29 try
30 {
31 while (reader. read ())
32 {
33 console. writeline ("{0} {1} {2 }",
34 reader. getint32 (0). tostring (),
35 reader. getstring (1 ));
36}
37}
38 finally
39 {
40 reader. close (); // also closes the connection due to
41 // commandbehavior enum used when generating the reader
42}
43}