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)
{
    string connectionString = "server=localhost\\SQLExpress;database=master;integrated Security=SSPI;";
    DataTable table = new DataTable("allPrograms");
    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        Console.WriteLine("connection created successfuly");
        string command = "SELECT * FROM Programs";
        using(SqlCommand 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?

Answer 1

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();
}

Answer 2

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();

 }

Marc

Answer 3

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 retreive objects to work with which are strongly typed and easier to work with IMHO.

Colin G