Development Tip

DataTable에서 데이터를 추출하려면 어떻게합니까?

yourdevel 2020. 11. 19. 22:04
반응형

DataTable에서 데이터를 추출하려면 어떻게합니까?


나는이 DataTable로컬 데이터베이스에 대한 SQL 쿼리에서 채워집니다,하지만 나는 그것에서 데이터를 추출하는 방법을 모르겠어요. 주요 방법 (테스트 프로그램에서) :

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를 사용하기에 의미있는 형식으로 추출 하려면 어떻게해야합니까?


DataTable에는 .RowsDataRow 요소 컬렉션 이 있습니다.

각 DataRow는 데이터베이스의 한 행에 해당하며 열 컬렉션을 포함합니다.

단일 값에 액세스하려면 다음과 같이하십시오.

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

마크


데이터 테이블을 데이터 소스로 여러 요소에 설정할 수 있습니다.

예를 들어

gridView

연발총

데이터 목록

기타 등등

각 행에서 데이터를 추출해야하는 경우 다음을 사용할 수 있습니다.

table.rows[rowindex][columnindex]

또는

열 이름을 알고 있다면

table.rows[rowindex][columnname]

테이블을 반복해야하는 경우 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();
}

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

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


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.


  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.

참고URL : https://stackoverflow.com/questions/1346132/how-do-i-extract-data-from-a-datatable

반응형