如何从 DataTable 提取数据？

我有一个从 SQL 查询到本地数据库的 DataTable，但我不知道如何从中提取数据。 主要方法(在测试程序中) :

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

我用来在数据库中创建表的命令:

create table programs
(
progid int primary key identity(1,1),
name nvarchar(255),
description nvarchar(500),
iconFile nvarchar(255),
installScript nvarchar(255)
)

如何将 DataTable中的数据提取到一个有意义的表单中使用？

433405 次浏览
小开

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();
}
小开
最佳答案

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

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.

小开

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


reader.Close();
小开 
  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);
}
}
小开

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.

小开

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.


提交答案