How to extract data from a DataTable?

Question

I have a DataTable that populates from an SQL query into a local database, but I don't know how to extract data from it. The main method (in the 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 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 a DataTable into a form that is meaningful to use?

Answer 1

DataTable has a collection of .Rows of DataRow elements.

Each DataRow corresponds to one row in your database and contains a collection of columns.

To access a single value, do the following:

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

Mark

Answer 2

You can set datatable as a data source for many elements.

For example,

Gridview

Repeater

Datalist

etc.

If you need to extract data from each row, 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, you can use a for loop or foreach loop, for example

 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 3

Please consider how to use the following code:

 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 < numRows; index++) { attended_type = dt.Rows[indice2]["columnname"].ToString(); } reader.Close(); 

Answer 4

Unless you have a specific reason to do raw ado.net, I would look at using an ORM (Object Relational Mapper) such as nhibernate or Linq to Sql. Thus, you can query the database and return objects to work with, which are strongly typed and easier to work with IMHO.

Colin g

Answer 5

The easiest way to extract data from a DataTable when you have several types of data (not just rows) 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 , Field<T> Method:

Provides strongly typed access to each of the column values ​​in the DataRow.

This means that when specifying the type, it will check and unpack 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 the extraction of data from the DataTable , since it eliminates the need to explicitly convert or parse the object into the correct types.

Answer 6

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

Answer 7

Note that when using the DataAdapter, you do not need to open and close the connection.

Therefore, I suggest updating this code and removing open and closed connections:

  SqlDataAdapter adapt = new SqlDataAdapter(cmd); 

conn.Open (); // this line of code is incomplete

  Console.WriteLine("connection opened successfuly"); adapt.Fill(table); 

conn.Close (); // this line of code is incomplete

  Console.WriteLine("connection closed successfuly"); 

Reference documentation

The code shown in this example does not explicitly open or close the Connection. The Fill method implicitly opens a connection, the DataAdapter uses it if it detects that the connection has not yet been established open. If Fill opened a connection, it also closes the connection when Fill is finished. This can simplify your code when you are dealing with a single operation, such as populating or updating. However, if you are performing several operations that require an open connection, you can improve the performance of your application by explicitly invoking the Open connection method, which performs operations against the data source, and then calling the Close method to connect. You should try to keep the connection to the data source open as brief as to free up resources for use by other client applications.