如何从数据表中提取数据?

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):

我有一个从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();
}

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?

如何将数据表中的数据提取到有意义的表单中?

6 个解决方案

#1

116

The DataTable has a collection .Rows of DataRow elements.

DataTable有一个集合。row of DataRow元素。

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

每个DataRow对应于数据库中的一行，并包含一组列。

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

马克

#2

You can set the datatable as a datasource to many elements.

您可以将datatable设置为多个元素的数据源。

For eg

对如

gridView

显示数据表格

repeater

中继器

datalist

etc etc

等等

If you need to extract data from each row then you can use

如果需要从每一行中提取数据，那么可以使用

table.rows[rowindex][columnindex]

或

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循环或foreach循环

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

#3

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

#4

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.

除非您有特定的理由去做raw ado.net，否则我将考虑使用ORM(对象关系映射器)，比如nhibernate或Linq to Sql。这样，您就可以查询数据库和retreive对象了，这些对象是强类型的，使用IMHO更容易。

Colin G

科林·G

#5

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

#6

Please, note that Open and Close the connection is not necessary when using DataAdapter.

请注意，在使用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

conn.Open();//这一行代码不重要

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

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

conn.Close();//这一行代码不重要

        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.

本例中所示的代码没有显式地打开和关闭连接。填充方法隐式地打开DataAdapter正在使用的连接，如果它发现连接尚未打开。如果Fill打开了连接，那么当Fill完成时，它也会关闭连接。当您处理一个操作(如填充或更新)时，这可以简化您的代码。但是，如果您正在执行多个需要开放连接的操作，您可以通过显式地调用连接的open方法、对数据源执行操作，然后调用连接的Close方法来提高应用程序的性能。您应该尽可能简单地保持对数据源的连接，以释放资源供其他客户机应用程序使用。

#1

116