What is the difference between a DATASET and a DataReader object _mssql

The biggest difference between a DataReader and a dataset is that DataReader always occupies SqlConnection (commonly known as a disconnected connection), and when working on the database online, Any action against the SqlConnection throws a DataReader exception. Because DataReader only loads one piece of data in memory at a time, the memory footprint is small. Because of the specificity and high performance of DataReader, DataReader is only entered, you can not read the first after reading the first one. The dataset is a one-time load of data in memory, discarding the database connection (commonly known as: Disconnected connection). After reading, the database connection is discarded because the dataset loads the data in memory, so it consumes more memory. But it is more flexible than DataReader, can dynamically add rows, columns, data, to the database to return, update operations.

Using DataReader and Datasets, you can read data from a data source. The DataReader itself is built by the Idbcommand.executereader () method, and the dataset is populated by the Dbdataadapter.fill () method. In addition, there are significant differences in the way they work: the DataReader execution process cannot be separated from the database connection, which means that the Idbconnection.close () method cannot be used to close the database connection when DataReader reads the data , and when you use the dataset to get the data, you can disconnect the database because DbDataAdapter is already responsible for getting the data to the application server.
Because of this distinction, special attention is required when developing database-related programs. For example, when you use DataReader to get data, you should actively turn off the database connection, or you may receive an exception that overflows the database connection pool.

The fundamental difference between a DataReader and a dataset is that one is online processing and the other is offline. When online, you get the current real data of the database, but always online, increasing the network's communication burden. After off-line data copy in the local, can reduce the network burden, the program processing data more convenient, if the off-line time is too long, see the data is not necessarily the real data.

Below is a sharing of your Oracle's SqlHelper class, which provides two ways to get data, including stored procedures, and more.

/*xcy modified in 11.09, the original SQL Server dbhelper*/using Oracle.ManagedDataAccess.Client; 
Using System; 
Using System.Collections.Generic; 
Using System.Configuration; 
Using System.Data; 
Using System.Linq; 
Using System.Web; Namespace JGs. Utility {public class SQLHelper {//Connection strings static string strconn = Configurationmanager.connectionstrings[' Xcycon '].t 
Ostring (); #region executes the query, returning the DataTable object-----------------------public static DataTable gettable (string strSQL) {return gettable (str 
SQL, NULL); public static DataTable gettable (String strSQL, oracleparameter[] pas) {return gettable (strSQL, PAS, Commandtype.tex 
T); 
///<summary>///Execute Query, return DataTable object///</summary>///<param name= "strSQL" >sql statement </param> <param name= "pas" > Parameter array </param>///<param name= "Cmdtype" >command type </param>///< Returns>datatable object </returns> public static DataTable gettable (String strSQL, oracleparameter[] pas, CommandType cmdtype) {DatATable dt = new DataTable (); using (OracleConnection conn = new OracleConnection (strconn)) {OracleDataAdapter da = new OracleDataAdapter (strSQL, con 
n); 
Da.SelectCommand.CommandType = Cmdtype; if (pas!= null) {da. 
SelectCommand.Parameters.AddRange (PAS); } da. 
Fill (DT); 
} return DT; #endregion #region Execute the query, returning the DataSet object-------------------------public static DataSet GetDataSet (String strsql) {Retu 
RN GetDataSet (strSQL, NULL); public static DataSet GetDataSet (String strSQL, oracleparameter[] pas) {return getdataset (strSQL, PAS, commandtype.t 
EXT); ///<summary>///Executes the query, returning the DataSet object///</summary>///<param name= "strSQL" >sql statement </param>// /<param name= "pas" > Parameter array </param>///<param name= "Cmdtype" >command type </param>///<returns& Gt;dataset object </returns> public static DataSet GetDataSet (String strSQL, oracleparameter[] pas, commandtype cmdtype 
) {DataSet dt = new DataSet (); Using (OracleConnection Conn = new OracleConnection (strconn)) {OracleDataAdapter da = new OracleDataAdapter (strSQL, conn); 
Da.SelectCommand.CommandType = Cmdtype; if (pas!= null) {da. 
SelectCommand.Parameters.AddRange (PAS); } da. 
Fill (DT); 
} return DT; #endregion #region Execute non-query stored procedures and SQL statements-----------------------------public static int Excuteproc (string procname) {RET 
Urn Excutesql (procname, NULL, commandtype.storedprocedure); public static int Excuteproc (string procname, oracleparameter[] pars) {return excutesql (procname, pars, CommandType. 
StoredProcedure); 
public static int Excutesql (string strsql) {return excutesql (strSQL, NULL); public static int Excutesql (string strSQL, oracleparameter[] paras) {return excutesql (strSQL, paras, Commandtype.tex 
T); ///execute non-query stored procedures and SQL statements///Add, delete, change///</summary>///<param name= "strSQL" > SQL statements to execute </param>///<p Aram Name= "Paras" > Parameter list, no parameters filled in null</param>///<param name= "Cmdtype" >command type &LT;/PARAM&GT <returns> returns affect rows </returns> public static int Excutesql (string strSQL, Oracleparameter[] Paras, 
CommandType cmdtype) {int i = 0; 
using (OracleConnection conn = new OracleConnection (strconn)) {OracleCommand cmd = new OracleCommand (strSQL, conn); 
Cmd.commandtype = Cmdtype; if (paras!= null) {cmd. 
Parameters.addrange (paras); } conn. 
Open (); i = cmd. 
ExecuteNonQuery (); Conn. 
Close (); 
return i;  
#endregion #region Execute the query to return the first row, the first column---------------------------------public static int Excutescalarsql (string strsql) { 
Return Excutescalarsql (strSQL, NULL); public static int Excutescalarsql (string strSQL, oracleparameter[] paras) {return excutescalarsql (strSQL, paras, Com 
Mandtype.text); public static int Excutescalarproc (string strSQL, oracleparameter[] paras) {return excutescalarsql (strSQL, paras, Co 
Mmandtype.storedprocedure); ///<summary>///executes the SQL statement, returns the first row, the first column///</summary>///<param name= "strSQL" > The SQL statement to execute &LT;/PARam>///<param Name= "paras" > Parameter list, no parameters filled in null</param>///<returns> return affect rows </returns> public 
static int Excutescalarsql (string strSQL, Oracleparameter[] Paras, commandtype cmdtype) {int i = 0; 
using (OracleConnection conn = new OracleConnection (strconn)) {OracleCommand cmd = new OracleCommand (strSQL, conn); 
Cmd.commandtype = Cmdtype; if (paras!= null) {cmd. 
Parameters.addrange (paras); } conn. 
Open (); i = Convert.ToInt32 (cmd. 
ExecuteScalar ()); Conn. 
Close (); 
return i; #endregion #region Query Gets a single value------------------------------------///<summary>///invokes a stored procedure with no parameters to get a single value///</su mmary>///<param name= "procname" ></param>///<returns></returns> public static object Geto 
Bjectbyproc (String procname) {return getobjectbyproc (procname, NULL); ///<summary>///Call stored procedures with parameters to get a single value///</summary>///<param name= "procname" ></param>///&L T;param Name= "Paras" ></param>///<returns></returns> public static Object Getobjectbyproc (String procname, oracleparameter[] paras) {return 
GetObject (ProcName, paras, commandtype.storedprocedure); ///<summary>///Get a single value based on SQL statement///</summary>///<param name= "strSQL" ></param>///<ret 
urns></returns> public static Object GetObject (String strSQL) {return GetObject (strSQL, NULL); ///<summary>///Gets a single value based on the SQL statement and parameter array///</summary>///<param name= "strSQL" ></param>/// <param Name= "Paras" ></param>///<returns></returns> public static object GetObject (string 
strSQL, Oracleparameter[] paras) {return GetObject (strSQL, paras, commandtype.text); 
///<summary>///Execute SQL statement, return header column///</summary>///<param name= "strSQL" > SQL statement to execute </param> <param Name= "Paras" > Parameter list, no parameters filled in null</param>///<returns> return header column </returns> public static Object GetObject (StRing strSQL, Oracleparameter[] Paras, commandtype cmdtype) {object o = null; 
using (OracleConnection conn = new OracleConnection (strconn)) {OracleCommand cmd = new OracleCommand (strSQL, conn); 
Cmd.commandtype = Cmdtype; if (paras!= null) {cmd. 
Parameters.addrange (paras); } conn. 
Open (); o = cmd. 
ExecuteScalar (); Conn. 
Close (); 
return o; #endregion #region Query gets DataReader------------------------------------///<summary>///calls a stored procedure with no parameters, returns Datarea Der Object///</summary>///<param name= "procname" > Stored procedure name </param>///<returns>datareader Object </ 
returns> public static OracleDataReader Getreaderbyproc (string procname) {return getreaderbyproc (procname, NULL); ///<summary>///invokes stored procedures with parameters, returns DataReader object///</summary>///<param name= "procname" > Stored procedure name </p aram>///<param Name= "paras" > parameter array </param>///<returns>datareader object </returns> Public Static OracleDataReader Getreaderbyproc (StRing procname, Oracleparameter[] paras) {return getreader (PROCNAME, paras, commandtype.storedprocedure); ///<summary>///returns DataReader object///</summary>///<param name= "strSQL" >sql statement according to SQL statement </param&gt 
; <returns>datareader object </returns> public static OracleDataReader Getreader (string strSQL) {return Getrea 
Der (strSQL, null); ///<summary>///returns DataReader object///</summary>///<param name= "strSQL" >sql statements based on SQL statements and Parameters </param >///<param name= "paras" > parameter array </param>///<returns>datareader object </returns> public static Or 
Acledatareader Getreader (String strSQL, oracleparameter[] paras) {return getreader (strSQL, paras, commandtype.text); ///<summary>///Query SQL statements get SQL statements for DataReader///</summary>///<param name= "strSQL" > Queries &LT;/PARAM&G 
T <param Name= "Paras" > Parameter list, no parameters filled in null</param>///<returns> query to DataReader (when the object is closed, automatically closes the connection) < /returns> PubliC Static OracleDataReader Getreader (String strSQL, Oracleparameter[] Paras, commandtype cmdtype) {OracleDataReader sqld 
R = null; 
OracleConnection conn = new OracleConnection (strconn); 
OracleCommand cmd = new OracleCommand (strSQL, conn); 
Cmd.commandtype = Cmdtype; if (paras!= null) {cmd. 
Parameters.addrange (paras); } conn. 
Open (); The commandbehavior.closeconnection effect is that if the associated DataReader object is closed, the connection is automatically closed Sqldr = cmd. 
ExecuteReader (commandbehavior.closeconnection); 
return SQLDR; #endregion/* #region BULK INSERT data---------------------------------------------///<summary>///to BULK INSERT data into the database/// </summary>///<param name= "Sourcedt" > Data source table </param>///<param name= "targettable" > Server target table < /param> public static void Bulktodb (DataTable Sourcedt, String targettable) {OracleConnection conn = new Oracleconn 
Ection (strconn); SqlBulkCopy bulkcopy = new SqlBulkCopy (conn); Efficient bulk loading of SQL Server tables with data from other sources bulkcopy.destinationtablename = targettable; 
The name of the destination table on the serverBulkcopy.batchsize = SourceDt.Rows.Count; Number of rows in each batch try {Conn. 
Open (); if (Sourcedt!= null && sourceDt.Rows.Count!= 0) bulkcopy.writetoserver (Sourcedt); 
Copies all rows from the provided data source to the target table, catch (Exception ex) {throw ex; finally {Conn. 
Close (); 
if (bulkcopy!= null) bulkcopy.close ();  } #endregion */}}

The above is a small set to introduce the DataSet and DataReader objects have any difference, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!