Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I extract data from a DataTable?

Tags:

c#

sql

ado.net

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?

like image 464
RCIX Avatar asked Aug 28 '09 10:08

RCIX


People also ask

How do you read a DataTable?

A table can be read from left to right or from top to bottom. If you read a table across the row, you read the information from left to right. In the Cats and Dogs Table, the number of black animals is 2 + 2 = 4. You'll see that those are the numbers in the row directly to the right of the word 'Black.

What is the difference between DataSet and DataTable?

A DataTable object represents tabular data as an in-memory, tabular cache of rows, columns, and constraints. The DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects.


2 Answers

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["name"].ToString();      string description = row["description"].ToString();      string icoFileName = row["iconFile"].ToString();      string installScript = row["installScript"].ToString();  } 
like image 133
marc_s Avatar answered Sep 28 '22 02:09

marc_s


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(); } 
like image 38
rahul Avatar answered Sep 28 '22 02:09

rahul