Most of the developer use DataAdapter to fill the DataSet and then take the first table from the data set, which is quite slow, why can't we use DataReader to read the data and use datatable's Load method to load the reader into it, in this article we will see how we can load a table faster and easier way, even without using the DataSet and DataAdapter.
First of all set our connection string in web.config file to connect to the database.
<connectionStrings>
<add name="dbConString"
connectionString="data source=localhost;initial catalog=db_name;UID=sa;PWD=xxx"
providerName="System.Data.SqlClient" />
</connectionStrings>
We are going to create a method GetDataTable which will use datareader to load the data into data table.
public DataTable GetDataTable()
{
DataTable dataTable = new DataTable();
using (SqlConnection conn =
new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "procedure_name";
cmd.CommandType = CommandType.StoredProcedure;
// SqlDataReader need an open conncetion, so check and open it.
if (conn.State != ConnectionState.Open)
conn.Open();
// Read data by using Execute Reader
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//Use data table load method to load data from data reader
dataTable.Load(dr);
}
return dataTable;
}
Conclusion:
Is not it easy, fast and handy to load data from a data reader into data table, enjoy it.
![]() |
Started career with .Net, and not working on client side technologies like angular, React etc. since last 12 years. But C# is really amazing language and I like to learn everything about C# and want to share whatever I know.
|
By Alicia Gonzalez On 09 Jun, 13 Viewed: 14,035 |
Is it possible to perform a linq query on datatable, answer is no because datatable’s row collection, DataRowCollection does not implement IEnumerable<T> so we cannot perform linq query on it. But if there is any way to convert our datatable data row collection into IEnumerable, then surely we can.... By Charles Fuller On 25 Jun 2013 Viewed: 3,973
To convert a DataTable into JSON in C# is really very easy. Is there any class or method in .Net which can help us to directly do it, then answer is No. We need to write a small utility class or method which can help to convert any datatable into json easily.What we are going to do is, create a... By Ali Adravi On 09 Jun 2013 Viewed: 31,277
In C# there are different data type and they use some default value when we declare a variable. When we define a variable of type int or Int32 say int score; so what is the value of score, will it be null or zero, in the same way if we create a variable of type string/String what value it holds... By Ali Adravi On 27 Mar 2013 Viewed: 2,623
An enumeration type (also known an enumeration or an enum) provides an efficient way to define a set of named integral constants that may be assigned to a variable to make programming clear, understandable and manageable. There are ways of using, like how to use enum in switch and case statement,... By Hamden On 27 Mar 2013 Viewed: 4,586
When we have var which dynamically holds any type or value so why we need dynamic type, what are the differences and situations where we can use dynamic rather than var, there were some question in my mind so I explored more which I want to share here. There are two things, static type and... By Nathan Armour On 26 Mar 2013 Viewed: 3,223