How do I extract data from a DataTable

Question

I have a DataTable that is filled in from an SQL query to a local database  but I don t know how to extract data from it  Main method  in test program    static void Main string   args        const string connectionString    server localhost  SQLExpress database master integrated Security SSPI        DataTable table   new DataTable  allPrograms         using  var conn   new SqlConnection connectionString                 Console WriteLine  connection created successfuly             string command    SELECT   FROM Programs            using  var cmd   new SqlCommand command  conn                         Console WriteLine  command created successfuly                 SqlDataAdapter adapt   new SqlDataAdapter cmd                conn Open                 Console WriteLine  connection opened successfuly                adapt Fill table               conn Close                Console WriteLine  connection closed successfuly                         Console Read        The command I used to create the tables in my database   create table programs       progid int primary key identity 1 1       name nvarchar 255       description nvarchar 500       iconFile nvarchar 255       installScript nvarchar 255      How can I extract data from the DataTable into a form meaningful to use

User · Answer

You can set the datatable as a datasource to many elements.

For eg

gridView

repeater

datalist

etc etc

If you need to extract data from each row then you can use

table.rows[rowindex][columnindex]

or

if you know the column name

table.rows[rowindex][columnname]

If you need to iterate the table then you can either use a for loop or a foreach loop like

for ( int i = 0; i < table.rows.length; i ++ )
{
    string name = table.rows[i]["columnname"].ToString();
}

foreach ( DataRow dr in table.Rows )
{
    string name = dr["columnname"].ToString();
}

User · Answer

Please consider using some code like this   SqlDataReader reader   command ExecuteReader    int numRows   0  DataTable dt   new DataTable     dt Load reader   numRows   dt Rows Count   string attended type        for  int index   0  index  lt  numRows  index          attended type   dt Rows indice2   columnname   ToString       reader Close

User · Answer

Unless you have a specific reason to do raw ado net I would have a look at using an ORM  object relational mapper  like nHibernate or LINQ to SQL  That way you can query the database and retrieve objects to work with which are strongly typed and easier to work with IMHO

User · Answer

The simplest way to extract data from a DataTable when you have multiple data types  not just strings  is to use the Field lt T gt  extension method available in the System Data DataSetExtensions assembly   var id   row Field lt int gt   ID               extract and parse int var name   row Field lt string gt   Name        extract string   From MSDN  the Field lt T gt  method      Provides strongly-typed access to each of the column values in the   DataRow    This means that when you specify the type it will validate and unbox the object   For example      iterate over the rows of the datatable foreach  var row in table AsEnumerable        AsEnumerable   returns IEnumerable lt DataRow gt        var id   row Field lt int gt   ID                                 int     var name   row Field lt string gt   Name                          string     var orderValue   row Field lt decimal gt   OrderValue             decimal     var interestRate   row Field lt double gt   InterestRate          double     var isActive   row Field lt bool gt   Active                      bool     var orderDate   row Field lt DateTime gt   OrderDate              DateTime     It also supports nullable types   DateTime  date   row Field lt DateTime  gt   DateColumn      This can simplify extracting data from DataTable as it removes the need to explicitly convert or parse the object into the correct types

User · Answer

The DataTable has a collection  Rows of DataRow elements  Each DataRow corresponds to one row in your database  and contains a collection of columns  In order to access a single value  do something like this   foreach DataRow row in YourDataTable Rows           string name   row  quot name quot   ToString         string description   row  quot description quot   ToString         string icoFileName   row  quot iconFile quot   ToString         string installScript   row  quot installScript quot   ToString

User · Answer

Please  note that Open and Close the connection is not necessary when using DataAdapter   So I suggest please update this code and remove the open and close of the connection           SqlDataAdapter adapt   new SqlDataAdapter cmd        conn Open       this line of code is uncessessary            Console WriteLine  connection opened successfuly            adapt Fill table        conn Close       this line of code is uncessessary            Console WriteLine  connection closed successfuly      Reference Documentation     The code shown in this example does not explicitly open and close the   Connection  The Fill method implicitly opens the Connection that the   DataAdapter is using if it finds that the connection is not already   open  If Fill opened the connection  it also closes the connection   when Fill is finished  This can simplify your code when you deal with   a single operation such as a Fill or an Update  However  if you are   performing multiple operations that require an open connection  you   can improve the performance of your application by explicitly calling   the Open method of the Connection  performing the operations against   the data source  and then calling the Close method of the Connection    You should try to keep connections to the data source open as briefly   as possible to free resources for use by other client applications

User · Answer

var table   Tables 0     get first table from Dataset   foreach  DataRow row in table Rows                foreach  var item in row ItemArray                         console Write  Value   item

[c#] How do I extract data from a DataTable?

Examples related to c#

Examples related to sql

Examples related to ado.net