System.Data.DataTable-klass

Klassen DataTable är ett centralt objekt i biblioteket ADO.NET. Andra objekt som använder DataTable är DataSet och DataView.

DataTable objektnamn är villkorligt skiftlägeskänsliga. Om en DataTable till exempel heter "mydatatable" och en annan heter "Mydatatable" betraktas en sträng som används för att söka efter en av tabellerna som skillnadskänslig. Men om "mydatatable" finns och "Mydatatable" inte gör det betraktas söksträngen som skiftlägesokänslig. En DataSet kan innehålla två DataTable objekt som har samma TableName egenskapsvärde men olika Namespace egenskapsvärden. Mer information om hur du arbetar med DataTable objekt finns i Skapa en datatabell.

Om du skapar ett DataTable programmatiskt måste du först definiera schemat genom att lägga DataColumn till objekt i DataColumnCollection (nås via Columns egenskapen). Mer information om hur du lägger till DataColumn objekt finns i Lägga till kolumner i en datatabell.

Om du vill lägga till rader i en DataTablemåste du först använda NewRow metoden för att returnera ett nytt DataRow objekt. Metoden NewRow returnerar en rad med schemat för DataTable, eftersom den definieras av tabellens DataColumnCollection. Det maximala antalet rader som en DataTable kan lagra är 16 777 216. Mer information finns i Lägga till data i en datatabell.

Innehåller DataTable också en samling Constraint objekt som kan användas för att säkerställa dataintegriteten. Mer information finns i DataTable-begränsningar.

Det finns många DataTable händelser som kan användas för att avgöra när ändringar görs i en tabell. Dessa inkluderar RowChanged, RowChanging, RowDeletingoch RowDeleted. Mer information om de händelser som kan användas med en DataTablefinns i Hantera DataTable-händelser.

När en instans av DataTable skapas anges några av läs-/skrivegenskaperna till initiala värden. En lista över dessa värden finns i DataTable konstruktorn.

Säkerhetsfrågor

Information om DataSet- och DataTable-säkerhet finns i Säkerhetsvägledning.

Exempel

Det här exemplet visar hur du skapar en DataTable manuellt med specifika schemadefinitioner:

• Skapa flera DataTables och definiera de inledande kolumnerna.
• Skapa tabellbegränsningarna.
• Infoga värdena och visa tabellerna.
• Skapa uttryckskolumnerna och visa tabellerna.

using System;
using System.Data;

class Program
{
    static void Main(string[] args)
    {
        // Create two tables and add them into the DataSet
        DataTable orderTable = CreateOrderTable();
        DataTable orderDetailTable = CreateOrderDetailTable();
        DataSet salesSet = new DataSet();
        salesSet.Tables.Add(orderTable);
        salesSet.Tables.Add(orderDetailTable);

        // Set the relations between the tables and create the related constraint.
        salesSet.Relations.Add("OrderOrderDetail", orderTable.Columns["OrderId"], orderDetailTable.Columns["OrderId"], true);

        Console.WriteLine("After creating the foreign key constriant, you will see the following error if inserting order detail with the wrong OrderId: ");
        try
        {
            DataRow errorRow = orderDetailTable.NewRow();
            errorRow[0] = 1;
            errorRow[1] = "O0007";
            orderDetailTable.Rows.Add(errorRow);
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        Console.WriteLine();

        // Insert the rows into the table
        InsertOrders(orderTable);
        InsertOrderDetails(orderDetailTable);

        Console.WriteLine("The initial Order table.");
        ShowTable(orderTable);

        Console.WriteLine("The OrderDetail table.");
        ShowTable(orderDetailTable);

        // Use the Aggregate-Sum on the child table column to get the result.
        DataColumn colSub = new DataColumn("SubTotal", typeof(Decimal), "Sum(Child.LineTotal)");
        orderTable.Columns.Add(colSub);

        // Compute the tax by referencing the SubTotal expression column.
        DataColumn colTax = new DataColumn("Tax", typeof(Decimal), "SubTotal*0.1");
        orderTable.Columns.Add(colTax);

        // If the OrderId is 'Total', compute the due on all orders; or compute the due on this order.
        DataColumn colTotal = new DataColumn("TotalDue", typeof(Decimal), "IIF(OrderId='Total',Sum(SubTotal)+Sum(Tax),SubTotal+Tax)");
        orderTable.Columns.Add(colTotal);

        DataRow row = orderTable.NewRow();
        row["OrderId"] = "Total";
        orderTable.Rows.Add(row);

        Console.WriteLine("The Order table with the expression columns.");
        ShowTable(orderTable);

        Console.WriteLine("Press any key to exit.....");
        Console.ReadKey();
    }

    private static DataTable CreateOrderTable()
    {
        DataTable orderTable = new DataTable("Order");

        // Define one column.
        DataColumn colId = new DataColumn("OrderId", typeof(String));
        orderTable.Columns.Add(colId);

        DataColumn colDate = new DataColumn("OrderDate", typeof(DateTime));
        orderTable.Columns.Add(colDate);

        // Set the OrderId column as the primary key.
        orderTable.PrimaryKey = new DataColumn[] { colId };

        return orderTable;
    }

    private static DataTable CreateOrderDetailTable()
    {
        DataTable orderDetailTable = new DataTable("OrderDetail");

        // Define all the columns once.
        DataColumn[] cols =
        {
            new DataColumn("OrderDetailId", typeof(Int32)),
            new DataColumn("OrderId", typeof(String)),
            new DataColumn("Product", typeof(String)),
            new DataColumn("UnitPrice", typeof(Decimal)),
            new DataColumn("OrderQty", typeof(Int32)),
            new DataColumn("LineTotal", typeof(Decimal), "UnitPrice*OrderQty")
        };

        orderDetailTable.Columns.AddRange(cols);
        orderDetailTable.PrimaryKey = new DataColumn[] { orderDetailTable.Columns["OrderDetailId"] };
        return orderDetailTable;
    }

    private static void InsertOrders(DataTable orderTable)
    {
        // Add one row once.
        DataRow row1 = orderTable.NewRow();
        row1["OrderId"] = "O0001";
        row1["OrderDate"] = new DateTime(2013, 3, 1);
        orderTable.Rows.Add(row1);

        DataRow row2 = orderTable.NewRow();
        row2["OrderId"] = "O0002";
        row2["OrderDate"] = new DateTime(2013, 3, 12);
        orderTable.Rows.Add(row2);

        DataRow row3 = orderTable.NewRow();
        row3["OrderId"] = "O0003";
        row3["OrderDate"] = new DateTime(2013, 3, 20);
        orderTable.Rows.Add(row3);
    }

    private static void InsertOrderDetails(DataTable orderDetailTable)
    {
        // Use an Object array to insert all the rows .
        // Values in the array are matched sequentially to the columns, based on the order in which they appear in the table.
        Object[] rows =
        {
            new Object[] { 1, "O0001", "Mountain Bike", 1419.5, 36 },
            new Object[] { 2, "O0001", "Road Bike", 1233.6, 16 },
            new Object[] { 3, "O0001", "Touring Bike", 1653.3, 32 },
            new Object[] { 4, "O0002", "Mountain Bike", 1419.5, 24 },
            new Object[] { 5, "O0002", "Road Bike", 1233.6, 12 },
            new Object[] { 6, "O0003", "Mountain Bike", 1419.5, 48 },
            new Object[] { 7, "O0003", "Touring Bike", 1653.3, 8 },
        };

        foreach (Object[] row in rows)
        {
            orderDetailTable.Rows.Add(row);
        }
    }

    private static void ShowTable(DataTable table)
    {
        foreach (DataColumn col in table.Columns)
        {
            Console.Write("{0,-14}", col.ColumnName);
        }
        Console.WriteLine();

        foreach (DataRow row in table.Rows)
        {
            foreach (DataColumn col in table.Columns)
            {
                if (col.DataType.Equals(typeof(DateTime)))
                    Console.Write("{0,-14:d}", row[col]);
                else if (col.DataType.Equals(typeof(Decimal)))
                    Console.Write("{0,-14:C}", row[col]);
                else
                    Console.Write("{0,-14}", row[col]);
            }
            Console.WriteLine();
        }
        Console.WriteLine();
    }
}

Imports System.Data

Class Program
   Public Shared Sub Main(args As String())
      ' Create two tables and add them into the DataSet
      Dim orderTable As DataTable = CreateOrderTable()
      Dim orderDetailTable As DataTable = CreateOrderDetailTable()
      Dim salesSet As New DataSet()
      salesSet.Tables.Add(orderTable)
      salesSet.Tables.Add(orderDetailTable)

      ' Set the relations between the tables and create the related constraint.
      salesSet.Relations.Add("OrderOrderDetail", orderTable.Columns("OrderId"), orderDetailTable.Columns("OrderId"), True)

      Console.WriteLine("After creating the foreign key constriant, you will see the following error if inserting order detail with the wrong OrderId: ")
      Try
         Dim errorRow As DataRow = orderDetailTable.NewRow()
         errorRow(0) = 1
         errorRow(1) = "O0007"
         orderDetailTable.Rows.Add(errorRow)
      Catch e As Exception
         Console.WriteLine(e.Message)
      End Try
      Console.WriteLine()

      ' Insert the rows into the table
      InsertOrders(orderTable)
      InsertOrderDetails(orderDetailTable)

      Console.WriteLine("The initial Order table.")
      ShowTable(orderTable)

      Console.WriteLine("The OrderDetail table.")
      ShowTable(orderDetailTable)

      ' Use the Aggregate-Sum on the child table column to get the result.
      Dim colSub As New DataColumn("SubTotal", GetType([Decimal]), "Sum(Child.LineTotal)")
      orderTable.Columns.Add(colSub)

      ' Compute the tax by referencing the SubTotal expression column.
      Dim colTax As New DataColumn("Tax", GetType([Decimal]), "SubTotal*0.1")
      orderTable.Columns.Add(colTax)

      ' If the OrderId is 'Total', compute the due on all orders; or compute the due on this order.
      Dim colTotal As New DataColumn("TotalDue", GetType([Decimal]), "IIF(OrderId='Total',Sum(SubTotal)+Sum(Tax),SubTotal+Tax)")
      orderTable.Columns.Add(colTotal)

      Dim row As DataRow = orderTable.NewRow()
      row("OrderId") = "Total"
      orderTable.Rows.Add(row)

      Console.WriteLine("The Order table with the expression columns.")
      ShowTable(orderTable)

      Console.WriteLine("Press any key to exit.....")
      Console.ReadKey()
   End Sub

   Private Shared Function CreateOrderTable() As DataTable
      Dim orderTable As New DataTable("Order")

      ' Define one column.
      Dim colId As New DataColumn("OrderId", GetType([String]))
      orderTable.Columns.Add(colId)

      Dim colDate As New DataColumn("OrderDate", GetType(DateTime))
      orderTable.Columns.Add(colDate)

      ' Set the OrderId column as the primary key.
      orderTable.PrimaryKey = New DataColumn() {colId}

      Return orderTable
   End Function

   Private Shared Function CreateOrderDetailTable() As DataTable
      Dim orderDetailTable As New DataTable("OrderDetail")

      ' Define all the columns once.
      Dim cols As DataColumn() = {New DataColumn("OrderDetailId", GetType(Int32)), New DataColumn("OrderId", GetType([String])), New DataColumn("Product", GetType([String])), New DataColumn("UnitPrice", GetType([Decimal])), New DataColumn("OrderQty", GetType(Int32)), New DataColumn("LineTotal", GetType([Decimal]), "UnitPrice*OrderQty")}

      orderDetailTable.Columns.AddRange(cols)
      orderDetailTable.PrimaryKey = New DataColumn() {orderDetailTable.Columns("OrderDetailId")}
      Return orderDetailTable
   End Function

   Private Shared Sub InsertOrders(orderTable As DataTable)
      ' Add one row once.
      Dim row1 As DataRow = orderTable.NewRow()
      row1("OrderId") = "O0001"
      row1("OrderDate") = New DateTime(2013, 3, 1)
      orderTable.Rows.Add(row1)

      Dim row2 As DataRow = orderTable.NewRow()
      row2("OrderId") = "O0002"
      row2("OrderDate") = New DateTime(2013, 3, 12)
      orderTable.Rows.Add(row2)

      Dim row3 As DataRow = orderTable.NewRow()
      row3("OrderId") = "O0003"
      row3("OrderDate") = New DateTime(2013, 3, 20)
      orderTable.Rows.Add(row3)
   End Sub

   Private Shared Sub InsertOrderDetails(orderDetailTable As DataTable)
      ' Use an Object array to insert all the rows .
      ' Values in the array are matched sequentially to the columns, based on the order in which they appear in the table.
      Dim rows As [Object]() = {New [Object]() {1, "O0001", "Mountain Bike", 1419.5, 36}, New [Object]() {2, "O0001", "Road Bike", 1233.6, 16}, New [Object]() {3, "O0001", "Touring Bike", 1653.3, 32}, New [Object]() {4, "O0002", "Mountain Bike", 1419.5, 24}, New [Object]() {5, "O0002", "Road Bike", 1233.6, 12}, New [Object]() {6, "O0003", "Mountain Bike", 1419.5, 48}, _
         New [Object]() {7, "O0003", "Touring Bike", 1653.3, 8}}

      For Each row As [Object]() In rows
         orderDetailTable.Rows.Add(row)
      Next
   End Sub

   Private Shared Sub ShowTable(table As DataTable)
      For Each col As DataColumn In table.Columns
         Console.Write("{0,-14}", col.ColumnName)
      Next
      Console.WriteLine()

      For Each row As DataRow In table.Rows
         For Each col As DataColumn In table.Columns
            If col.DataType.Equals(GetType(DateTime)) Then
               Console.Write("{0,-14:d}", row(col))
            ElseIf col.DataType.Equals(GetType([Decimal])) Then
               Console.Write("{0,-14:C}", row(col))
            Else
               Console.Write("{0,-14}", row(col))
            End If
         Next
         Console.WriteLine()
      Next
      Console.WriteLine()
   End Sub
End Class