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

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

Postby chris901 » Tue 16 Aug 2016 16:55

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.
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

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

Postby Pinturiccio » Wed 17 Aug 2016 13:36

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.
Pinturiccio
Devart Team
 
Posts: 2026
Joined: Wed 02 Nov 2011 09:44

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

Postby chris901 » Thu 08 Sep 2016 05:09

Hi,

any update on this? It would be super handy.

AFAIK you already have a Identity detection in EF.
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

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

Postby Pinturiccio » Thu 08 Sep 2016 14:05

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.
Pinturiccio
Devart Team
 
Posts: 2026
Joined: Wed 02 Nov 2011 09:44

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

Postby chris901 » Thu 08 Sep 2016 17:25

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.
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

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

Postby Pinturiccio » Fri 09 Sep 2016 16:06

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

chris901 wrote: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.
Pinturiccio
Devart Team
 
Posts: 2026
Joined: Wed 02 Nov 2011 09:44

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

Postby chris901 » Fri 09 Sep 2016 18:23

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?

Pinturiccio wrote: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.
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

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

Postby Shalex » Tue 13 Sep 2016 18:41

chris901 wrote: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"]);
    }
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL