What is the difference between DATASET and DATAREADER? datasetdatareader

What is the difference between DATASET and DATAREADER? datasetdatareader

The biggest difference between DataReader and DataSet is that DataReader always occupies SqlConnection (commonly known as non-disconnected connections) during use. When you operate a database online, any SqlConnection operation will cause DataReader exceptions. Because DataReader only loads one piece of data in the memory at a time, the occupied memory is very small. Because of the special nature and high performance of DataReader, DataReader is only input. After reading the first entry, you cannot read the first entry. DataSet loads data in the memory at a time and discards the database connection (commonly known as a disconnected connection ). After reading the data, the database connection is abandoned. Because DataSet loads all the data into the memory, memory consumption is high. However, it is more flexible than DataReader. You can dynamically Add rows, columns, data, and perform database return and update operations.

Both DataReader and DataSet can read data from the data source. DataReader itself is built through the IDbCommand. ExecuteReader () method, while DataSet is filled by the DbDataAdapter. Fill () method. In addition, the two work in a significantly different way: the execution process of DataReader cannot be separated from the database connection, that is, IDbConnection cannot be used when DataReader reads data. close () method to Close the database connection. When using DataSet to obtain data, you can disconnect the database because DbDataAdapter is already responsible for retrieving data to the application server.
Due to these differences, you must pay special attention when developing database-related programs. For example, after you use DataReader to obtain data, you should take the initiative to close the database connection; otherwise, an exception may occur when the database connection pool overflows.

The most fundamental difference between DataReader and DataSet lies in online processing and offline processing. When you are online, the real data of the database is obtained. If you are always online, the network communication burden is increased. Offline data is copied locally, which reduces the network burden and makes it easier for programs to process data. If the offline time is too long, the data you see may not be real data.

The following is a SQLHelper class of Oracle. It provides two methods to obtain data, including stored procedures ..

/* Xcy was modified on 11.09. The original sqlserver DBHelper */using Oracle. managedDataAccess. client; using System. collections. generic; using System. configuration; using System. data; using System. linq; using System. web; namespace JGS. utility {public class SQLHelper {// connection string static string strConn = ConfigurationManager. connectionStrings ["xcyCon"]. toString (); # region executes the query and returns the DataTable object --------------------- pu Blic static DataTable GetTable (string strSQL) {return GetTable (strSQL, null);} public static DataTable GetTable (string strSQL, OracleParameter [] pas) {return GetTable (strSQL, pas, commandType. text);} // <summary> // execute the query, returns the/able object // </summary> /// <param name = "strSQL"> SQL statement </param> /// <param name = "pas"> parameter array. </param> /// <param name = "partition type"> 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, conn); da. selectCommand. commandType = primary type; if (pas! = Null) {da. selectCommand. parameters. addRange (pas);} da. fill (dt) ;}return dt ;}# endregion # region executes the query and returns the DataSet object ----------------------- public static DataSet GetDataSet (string strSQL) {return GetDataSet (strSQL, null );} public static DataSet GetDataSet (string strSQL, OracleParameter [] pas) {return GetDataSet (strSQL, pas, CommandType. text) ;}/// <summary> /// execute the query and return the DataSet object /// </summar Y> /// <param name = "strSQL"> SQL statement </param> /// <param name = "pas"> parameter array </param> /// <param name = "argument type"> Command type </param> // <returns> DataSet object </returns> public static DataSet GetDataSet (string strSQL, oracleParameter [] pas, CommandType primitive type) {DataSet dt = new DataSet (); using (OracleConnection conn = new OracleConnection (strConn) {OracleDataAdapter da = new OracleDataAdapter (strSQL, conn ); Da. SelectCommand. CommandType = partition type; if (pas! = Null) {da. selectCommand. parameters. addRange (pas);} da. fill (dt) ;}return dt ;}# endregion # region executes non-query stored procedures and SQL statements --------------------------- public static int ExcuteProc (string ProcName) {return ExcuteSQL (ProcName, null, commandType. storedProcedure);} public static int ExcuteProc (string ProcName, OracleParameter [] pars) {return ExcuteSQL (ProcName, pars, CommandType. storedProcedure);} publ Ic static int ExcuteSQL (string strSQL) {return ExcuteSQL (strSQL, null);} public static int ExcuteSQL (string strSQL, OracleParameter [] paras) {return ExcuteSQL (strSQL, paras, commandType. text );} /// execute non-query stored procedures and SQL statements /// add, delete, modify /// </summary> /// <param name = "strSQL"> SQL statement </param> /// <param name = "paras"> parameter list, null </param> /// <param name = "partition type"> Command type </param> /// <returns> indicates the number of affected rows. </r Eturns> public static int ExcuteSQL (string strSQL, OracleParameter [] paras, CommandType limit type) {int I = 0; using (OracleConnection conn = new OracleConnection (strConn )) {OracleCommand cmd = new OracleCommand (strSQL, conn); cmd. commandType = partition type; if (paras! = Null) {cmd. parameters. addRange (paras);} conn. open (); I = cmd. executeNonQuery (); conn. close ();} return I;} # endregion # region: the first row is returned for query. The first column is ------------------------------- public static int ExcuteScalarSQL (string strSQL) {return ExcuteScalarSQL (strSQL, null);} public static int ExcuteScalarSQL (string strSQL, OracleParameter [] paras) {return ExcuteScalarSQL (strSQL, paras, CommandType. text );} Public static int ExcuteScalarProc (string strSQL, OracleParameter [] paras) {return ExcuteScalarSQL (strSQL, paras, CommandType. storedProcedure);} // <summary> // execute the SQL statement and return the first line, first column /// </summary> /// <param name = "strSQL"> SQL statement to be executed </param> /// <param name = "paras"> parameter List, no parameter is set to null </param> /// <returns> Number of affected rows returned </returns> public static int ExcuteScalarSQL (string strSQL, OracleParameter [] paras, Comm AndType primitive type) {int I = 0; using (OracleConnection conn = new OracleConnection (strConn) {OracleCommand cmd = new OracleCommand (strSQL, conn); cmd. commandType = partition type; if (paras! = Null) {cmd. parameters. addRange (paras);} conn. open (); I = Convert. toInt32 (cmd. executeScalar (); conn. close ();} return I ;} # endregion # obtain a single value from the region Query -------------------------------- // <summary> // call a stored procedure without parameters to obtain a single value /// </summary> /// <param name = "ProcName"> </param> // <returns> </returns> public static object GetObjectByProc (string ProcName) {return GetObjectByProc (ProcName, null );}/// <Summary> // call a stored procedure with parameters to obtain a single value /// </summary> /// <param name = "ProcName"> </param> /// <param name = "paras"> </param> // <returns> </returns> public static object GetObjectByProc (string ProcName, oracleParameter [] paras) {return GetObject (ProcName, paras, CommandType. storedProcedure );} /// <summary> /// obtain a single value based on the SQL statement // </summary> /// <param name = "strSQL"> </param> /// <returns> </returns> public sta Tic object GetObject (string strSQL) {return GetObject (strSQL, null );} /// <summary> /// obtain 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 the SQL statement and return the first column of the First line /// </summary> /// <param Name = "strSQL"> SQL statement to be executed </param> // <param name = "paras"> parameter list, no parameter is specified. </param> // <returns> the first column of the returned first line </returns> public static object GetObject (string strSQL, OracleParameter [] paras, CommandType specify type) {object o = null; using (OracleConnection conn = new OracleConnection (strConn) {OracleCommand cmd = new OracleCommand (strSQL, conn); cmd. commandType = partition type; if (paras! = Null) {cmd. parameters. addRange (paras);} conn. open (); o = cmd. executeScalar (); conn. close ();} return o ;}# endregion # obtain the DataReader ------------------------------------------ // <summary> // call a stored procedure without parameters, return the DataReader object /// </summary> /// <param name = "procName"> stored procedure name </param> /// <returns> DataReader object </returns> public static OracleDataReader GetReaderByProc (string procName) {return GetReaderB YProc (procName, null) ;}/// <summary> /// call a stored procedure with parameters, return the DataReader object /// </summary> /// <param name = "procName"> stored procedure name </param> /// <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> // return the DataReader object based on the SQL statement /// </Summary> /// <param name = "strSQL"> SQL statement </param> /// <returns> DataReader object </returns> public static OracleDataReader GetReader (string strSQL) {return GetReader (strSQL, null );} /// <summary> /// return the DataReader object based on the SQL statement and parameters /// </summary> /// <param name = "strSQL"> SQL statement </param> /// <param name = "paras"> parameter array </param> /// <returns> DataReader object </returns> public static OracleDataReader GetReader (string strS QL, OracleParameter [] paras) {return GetReader (strSQL, paras, CommandType. text );} /// <summary> /// query the SQL statement to obtain the DataReader // </summary> /// <param name = "strSQL"> query the SQL statement </param> /// <param name = "paras"> parameter list, no parameter is set to null </param> // <returns> the queried DataReader (the connection is automatically closed when the object is closed) </returns> public static OracleDataReader GetReader (string strSQL, OracleParameter [] paras, CommandType publish type) {OracleDataRead Er sqldr = null; OracleConnection conn = new OracleConnection (strConn); OracleCommand cmd = new OracleCommand (strSQL, conn); cmd. CommandType = partition type; if (paras! = Null) {cmd. parameters. addRange (paras);} conn. open (); // CommandBehavior. closeConnection is used to disable sqldr = cmd if the associated DataReader object is closed. executeReader (CommandBehavior. closeConnection); return sqldr ;} # endregion/* # region batch insert data ------------------------------------------- // <summary> // insert data into the database in batches /// </summary> // <param name =" sourceDt "> data source table </param> // <param name =" targetTable "> target table on the server </param> Public static void BulkToDB (DataTable sourceDt, string targetTable) {OracleConnection conn = new OracleConnection (strConn); SqlBulkCopy bulkCopy = new SqlBulkCopy (conn ); // load bulkCopy in SQL server tables in batches with data from other sources. destinationTableName = targetTable; // name of the target table on the server bulkCopy. batchSize = sourceDt. rows. count; // The number of rows in each batch try {conn. open (); if (sourceDt! = Null & sourceDt. Rows. Count! = 0) bulkCopy. writeToServer (sourceDt); // copy 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 section describes the differences between DATASET and DATAREADER objects. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!