The GetSchemaTable method returns a DataTable property that contains the column schema for a DataReader. The DataTable contains one row for each field in the resultset. Each column maps to a property of the field in the resultset. The ColumnName property of the DataTable column is the name of the field's property, such as the ColumnName, DataType, ColumnSize, IsKeyColumn, or IsAutoIncrement property. The value of the DataTable column is the value of the field's property, such as the FirstName value for the ColumnName property.
The default timeOut is 30 seconds.
If you want to increase the TimeOut see the below example code
SqlCommand cmd = new SqlCommand("select * from tablename", conn); cmd.CommandTimeout = 50; SqlDataReader dr = cmd.ExecuteReader();
To fill DataSet with data we have to use Fill() method of DataAdapter object.
Fill() has several overloads. But the simple one is
The first parameter will take the name of the dataset to be filled and the second parameter specifies the name of the DataTable in the DataSet which will contain the data.
DataReader is like a forward only recordset. It fetches one row at a time so very less network cost compare to DataSet(Fethces all the rows at a time). DataReader is readonly so we can't do any transaction on them. DataReader will be the best choice where we need to show the data to the user which requires no transaction. As DataReader is forward only so we can't fetch data randomly. .NET Data Providers optimizes the datareader to handle huge amount of data.
DataSet is an in memory representation of a collection of Database objects including tables of a relational database schemas.
DataSet is always a bulky object that requires a lot of memory space compare to DataReader. We can say that the DataSet is a small database because it stores the schema and data in the application memory area. DataSet fetches all data from the datasource at a time to its memory area. So we can traverse through the object to get the required data like querying database.
ADO relied on a connection based model. In the connected approach, the client had to be connected with the server and remain connected till the whole procedure or transaction was completed. Time resources and bandwidth became major constraints on such architecture.
To solve this problem the latter version of ADO used RecordSet. All the contents from the data source were copied into RecordSet. This allows clients to get disconnected from the server, work on the RecordSet and copy the changes back to the data source again. This approach did not succeed much because it requires COM marshalling to transmit disconnected data, it support only those datatypes that were defined by the COM standards and hence required type conversion.
ADO.NET can be used to access data sources using new .NET data providers as well as existing OLEDB data providers using the OLEDB.NET data provider.