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

The simplest way to extract data from a DataTable when you have multiple data types (not just strings) is to use the Field<T> extension method available in the System.Data.DataSetExtensions assembly.

var id = row.Field<int>("ID");         // extract and parse int
var name = row.Field<string>("Name");  // extract string

From MSDN, the Field<T> 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<DataRow>
{
    var id = row.Field<int>("ID");                           // int
    var name = row.Field<string>("Name");                    // string
    var orderValue = row.Field<decimal>("OrderValue");       // decimal
    var interestRate = row.Field<double>("InterestRate");    // double
    var isActive = row.Field<bool>("Active");                // bool
    var orderDate = row.Field<DateTime>("OrderDate");        // DateTime
}

It also supports nullable types:

DateTime? date = row.Field<DateTime?>("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

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

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

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  lt  table rows length  i            string name   table rows i   columnname   ToString       foreach   DataRow dr in table Rows         string name   dr  columnname   ToString

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

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

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

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

Examples related to c#

Examples related to sql

Examples related to ado.net