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

Question

I am importing tables from an Oracle database using the VB.NET dataAdapter. I use the "fill" command to add imported data to a DataSet. How can I define a specific DataTable column as PrimaryKey after the DataTable is already populated with data?

Answer 1

You can set the primary key of the table:

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 make sure that all values ​​for this column are unique.

I mainly work in C # and have a couple of extension methods that I use to "organize" the calls I need to make, which you can consider by 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"); 

Answer 2

While the values ​​in the column are unique

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

adjust column names.

Answer 3

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

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

Update: And here is another single-line font on how to use this 2-column index to find the row:

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

Answer 4

Thanks for Rob's answer - there is a small problem with the vb version, though, since the index should be based on a zero value:

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