How to define a VB.NET DataTable Column as primary key after creation

I am importing Tables from a Oracle DataBase, using a VB.NET dataAdapter. I use the "fill" command to add the imported data to a DataSet. Ho开发者_StackOverflow社区w is it possible to define a specific column of a DataTable as PrimaryKey, after the DataTable is already filled with data?

As long as the values in the column are unique

table.PrimaryKey = new DataColumn[] { table.Columns["Id"] };

adjust for your column names.

Here is a one-liner in VB (the question was with "using VB.NET"). This example is with 2 columns indexed:

table.PrimaryKey = New DataColumn() {table.Columns("column1"), _
                                     table.Columns("column2")}

Update: And here's another one-liner on how to use this 2 columns index to find a row:

table.Rows.Find(New Object() {value1, value2}) '<- DataRow returned

Update 2: And the complete example how to find a row using primary keys with a DataTable already filled with data:

'Define primary keys (you do this only once)
table.PrimaryKey = New DataColumn() {table.Columns("column1"), _
                                     table.Columns("column2")}
'Find a row:
Dim MyDataRow As DataRow
MyDataRow = table.Rows.Find(New Object() {value1, value2}) '<- DataRow returned
If MyDataRow IsNot Nothing Then 'If a row is found
    Return MyDataRow.Item("column3")
End If

You can set the primary key of a table by:

    Dim table As New DataTable()

    table.Columns.Add(New DataColumn("MyColumn"))

    Dim primaryKey(1) As DataColumn
    primaryKey(1) = table.Columns("MyColumn")
    table.PrimaryKey = primaryKey

To be able to use the primary key, you need to ensure that all values for the given column are unique.

I primarily work in C# and have a couple of Extension methods I use to "tidy" the calls I need to make, which you might want to consider translating to VB and using:

    public static void SetPrimaryKey(this DataTable value, string columnName)
    {
        value.PrimaryKey = new DataColumn[] { value.Columns[columnName] };
    }

    public static DataRow FindByPrimaryKey(this DataTable value, object key)
    {
        return value.Rows.Find(key);
    }

    // I can then do:

    DataTable table = CallToRoutineThatGetsMyDataTable();
    table.SetPrimaryKey("PKColumnName");
    DataRow result = table.FindByPrimaryKey("valueToFindWith");

Thanks for the answer Rob - there is a slight issue with the vb version though as the index should be zero-based:

Dim table As New DataTable()

table.Columns.Add(New DataColumn("MyColumn"))

Dim primaryKey(1) As DataColumn
primaryKey(0) = table.Columns("MyColumn")
table.PrimaryKey = primaryKey

This should be an elegant way:

table.PrimaryKey = {table.Columns("PrimaryKeyColumn")}

data As DataSet
//Fill the dataset 
Dim sqlCmd As New SqlCommand("GetTableByName", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Parameters.Add("@TableName", tableName)
adapter.SelectCommand = sqlCmd
adapter.Fill(data, tableName)
sqlCmd.Dispose()
data.Tables(tableName).PrimaryKey = New DataColumn() {data.Tables(tableName).Columns("ID")}