How do I … work with Guid in ADO.Net?

How do I … work with Guid in ADO.Net?
( in this case to support SQL Server replication using dataset )

26-Dec-2011
Hypothesis: That DataSet could act unaware of replication column (do not import it on datatable creation) (or delete it after). Then use default in SQL Server (newsequentialid())
Experiment: Worked out of the box – straight away

During creation of SQL Replication, SQL Server will add a rowGuid column which is a uniqueidentifier and places a default of (newsequentialid()) onto this column
Insert statements for replication must include all columns, except the rowGuid column.

ERROR
However if you then add a new row to this table in ADO.Net then program will give error message ‘Column rowGuid does not allow nulls’

SOLUTION
[MyDataTable].rowguidColumn.DefaultValue = Guid.NewGuid

EXPLANATION
SQL will have inserted a Unique index on rowGuid. However when the table is dropped onto the dataset the unique key is not there. Therefore although the above default set once will give each new row the same Guid, the ADO.net sees no errors. Because the insert statement does not insert the Guid, SQL does, then ADO.Net immediately returns the correct newly applied Guid from the database. This is because the Dataset insert statement also has a SELECT statement on it to get table updates immediately after the INSERT.

SQL Server Replication

Updating ERROR
Updating columns with the rowguidcol property is not allowed.
The transaction ended in the trigger. The batch has been aborted.

Removed the rowguid column from the dataset update sql and it worked. Found no online instruction for this in short search.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: