-1

I am trying to mark a column in my DataTable _Table as the primary key but it don't always works.

DataTable _Table will be populated with one row, that is copied from another DataTable (Table)
Therefor, DataTable _Table will either hold a row fetched from a table from SQL Server,
or it will hold a new row that does not yet exists in SQL Server.
After copying the row from Table into _Table I call this code

_Table.PrimaryKey = new DataColumn[] { Table.Columns[PrimaryKeyName] };

Now, when the row came from an existing row in the database, there is no exception when I run this line of code. That is because there is an unique value in the primary key column.

But when it holds a new row, the value in the primary key column is still empty (identity column) and that will probably be the reason I now get this error.

These columns don't currently have unique values.'

Currently I am catching the exception, and then I just eat the exception, and all is working well.
But, it feels like some dirty hack, I want to do this properly.

So, is there a way I can mark a column as primary key in a datatable, that only has one row in it, and where this column currently holds a null value ?

Cleptus
  • 3,446
  • 4
  • 28
  • 34
GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • No. By definition, a Primary Key can't have any nullable columns. `NULL` isn't a value, it means there's no value at all. If you compare nothing with nothing the only sensible result is nothing, not yes or false. This means a nullable column can't be used to identify a record, which is what PKs are for – Panagiotis Kanavos Apr 20 '21 at 11:50
  • If you only want that DataTable to hold a single row there's no need for a primary key – Panagiotis Kanavos Apr 20 '21 at 11:50
  • "_Currently I am catching the exception, and then I just eat the exception, and all is working well._" <-- That is not fine! – Cleptus Apr 20 '21 at 11:52
  • @PanagiotisKanavos That is also what I thought, but when I call `adapter.Update(Table)` VS complains it I have no primary key set for an update – GuidoG Apr 20 '21 at 11:53
  • @Cleptus Well, why do you think I am asking this question. I know it's not fine, I need some help on doing it better. So if you know a better way... – GuidoG Apr 20 '21 at 11:54
  • The better way is to check an ADO.NET tutorial. This isn't the first time someone tries to add a row to a database table. They don't do that by creating a generic DataTable in memory and setting constraints *after* adding the data. You didn't post the code that created `_Table` or any calls to `Update` so people can only guess – Panagiotis Kanavos Apr 20 '21 at 11:58
  • @GuidoG you didn't post any code. I didn't downvote, but you're asking for help with your code without posting that code. It also looks like you haven't checked any tutorials – Panagiotis Kanavos Apr 20 '21 at 12:00
  • Check [Retrieving Identity or Autonumber Values](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-identity-or-autonumber-values#retrieving-sql-server-identity-column-values) in the ADO.NET docs. – Panagiotis Kanavos Apr 20 '21 at 12:05
  • @PanagiotisKanavos I certainly will read those, thank you for your time – GuidoG Apr 20 '21 at 12:08
  • To have a more comprehensive example of the mathematics of NULLs, which represents the absence of value or emptiness, let's take two amnestics and ask ourselves what memories do they have in common? – SQLpro Apr 20 '21 at 12:11
  • The problem with old, out-of-fashion classes like ADO.NET's DataTables is that once there were *hundreds* of books, tutorials and courses. These were removed, replaced with tutorials for newer libraries like EF. Some were never updated and simply lost as eg MSDN was reorganized and eventually replaced by learn.microsoft.com. – Panagiotis Kanavos Apr 20 '21 at 12:20
  • In 2021 there are few reasons to use DataTable, mainly for reading. For writing to simple tables it's easier to use eg Dapper to insert a bunch of data objects to the database. For inserting complex graphs, ORMs are usually simpler. DataSets, DataTables and Adapters were built for data-centric *desktop* applications too – Panagiotis Kanavos Apr 20 '21 at 12:23
  • @GuidoG Sorry if I was harsh, didnt mean to. I have added an answer I hope it helps you, you could also check the related question for further reading on the primary keys [What's the best practice for primary keys in tables?](https://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables?rq=1) – Cleptus Apr 20 '21 at 12:58
  • @Cleptus No problem. Some thingsI are misunderstood also. I know what a `null` value is and it cannot be in a primary key. I have digged in the source more. It seems that when you want to use a bindingsource binded to a datatable, and you alter an exiting row, the `adapter.Update(Table)` fails when no column is set as primary key. I can understand that. But when you add a new row to this DataTable, it has no value in that column, regardless if you set the autonumber property true. There is no way it can set a value because it needs to come from the database after the `adapter.Update(Table)` – GuidoG Apr 20 '21 at 13:29
  • The autonumber property tells the SqlDataAdapter the database handles its value. It is provided by the database, and the SqlDataAdapter know it and raises no error because of that when you call `adapter.Update()`. That is why I told in my answer to use it in the primary key. – Cleptus Apr 21 '21 at 07:09
  • @Cleptus In my VS it does raises an error. I seems to me this property is completely ignored. That is the reason this code was written in this project, so it seems – GuidoG Apr 21 '21 at 07:13

1 Answers1

0

No, tables from DataAdapters do need primary keys and actual data in those columns to work correctly.

You mention in the comments that when you call .Update() you get an error. The cause is that it needs to have the PrimaryKey created so it knows how to identify the row to change. Yeah, you may have only one row in the datatable (and in the database) but because the table potentially can have several rows, and usually they do, the SQLDataAdapter does need the PrimaryKey.

For obvious reasons PrimaryKeys can not have NULL values, this is the root of your problem, but from a design perspective you can have an Identity column in your database (iirc the DataTable equivalent would be Autonumber) and configure it as a PrimaryKey. If you do so, the DataTable would never have a NULL value in the primary key because new rows would use the identity value provided from the database.

You can check the ADO.NET Identity documentation.

Note: If you are creating new software it is worth considering Panagiotis advice (using more recent data access frameworks)

Cleptus
  • 3,446
  • 4
  • 28
  • 34
  • Thank you for this answer. Unfortunate it is not a new project so I have little choice here. Also please read my last comment below my question about the primary key issue I am having – GuidoG Apr 20 '21 at 13:30