Detect columns created as serial (auto increment) and setting column property

[chris901]

Hello,

i have a PgSqlDataTable and a table in my database that uses serial as auto increment columns.

Is there a way to automatically set the AutoIncrement property of DataColumn to true for serial columns?

Thanks.

[Pinturiccio]

We have reproduce the described behaviour. We will investigate the possibility to set AutoIncrement to true automatically for the serial datatype and post here about the results as soon as possible.

[chris901]

Hi,

any update on this? It would be super handy.

AFAIK you already have a Identity detection in EF.

[Pinturiccio]

This is the designed behaviour. The AutoIncrement property is a property of a column, and if a value is not assigned to such column, this value is generated on the client side, not on server, and the generation is different on the client side than on the server side.

Suppose you have Id values from 1 to 500 in the database. You have read these rows to PgSqlDataTable and set the AutoIncrement property to true for Id. Then you create a new row in your table:

Code: Select all

DataRow row = dt.NewRow();

In this case row["id"] will be set to 0. For another new row it will be set to 1 an so on. Values already present in the database are not checked for a column with AutoIncrement set to true when generating new values on the client side, as well as the value sequence for the Serial column. As the result, its own set of values is generated for the AutoIncrement DataColumn, that’s why we don’t set the AutoIncrement property to true for a Serial column.

Set the AutoIncrement property to true when you want to generate values for the column on the client side. If you don’t want to set values on the client side, but rather want to work with values, generated on the database side, it requires additional configuration.

[chris901]

Thanks Pinturiccio,

your explanation makes sense to not set the SERIAL type to auto increment.

However shouldn't the AllowDBNull value be set to true as default?

Because the SERIAL type is declared so you want the values to be set automatically set and not have to take care of determining the next value of sequence.

Maybe you could consider setting AllowDBNull value to True for SERIAL types?

I noticed whenever I try to change the AllowDBNull to True at DesignTime it automatically changes it back to False?!

As a workaround I set the specific Column to AllowDBNull at RunTime but I would appreciate if a SERIAL datatype column is set to AllowDBNull = True at default.

[Pinturiccio]

Serial columns are usually used for Primary keys, and setting the AllowDBNull property to true for them by default is wrong.

I noticed whenever I try to change the AllowDBNull to True at DesignTime it automatically changes it back to False?!

We could not reproduce such a behaviour. We can change AllowDBNull in design time for a Serial column to true, and it stays true. Please describe the steps you perform to reproduce the issue and provide us a DDL script of your object.

[chris901]

Could you please tell me how I can iterate through the columns of a PgSqlDataTable and see if they are of the SERIAL type so I can set the properties to my needs?

We could not reproduce such a behaviour. We can change AllowDBNull in design time for a Serial column to true, and it stays true. Please describe the steps you perform to reproduce the issue and provide us a DDL script of your object.

Sorry, that error was on my part. I was using a descendant class of PgSqlDataTable and had an error in it.

[Shalex]

Could you please tell me how I can iterate through the columns of a PgSqlDataTable and see if they are of the SERIAL type so I can set the properties to my needs?

You can do that via the connection object:

Code: Select all

    var conn = new Devart.Data.PostgreSql.PgSqlConnection("your_connection_string");
    conn.Open();
    var result = conn.GetSchema("Columns", new string[] { "your_schema_name", "your_table_name" });
    foreach (DataRow row in result.Rows) {
        if (!(row["sername"] is System.DBNull))
            Console.WriteLine(row["Name"]);
    }