How to set datestyle in postgres db in c#










0















I want to change the datetype to th "DMY" Format in a postgres sql database. I use the Npgsql library as follows:



 public void SetDateSytleDB()

string sqlConnectionString = "Server=localhost;Port=5432;User Id=test;Password=test;Database=test_db";

sqlCnctn = new NpgsqlConnection(sqlConnectionString);
sqlCnctn.Open();
if (sqlCnctn.State == ConnectionState.Open)

String strSetDatestyle = "SET datestyle = 'ISO, DMY'";

using (var cmd = new NpgsqlCommand(strSetDatestyle, sqlCnctn))

cmd.ExecuteNonQuery();





unfortunately the datetype will not be changed in the database afterwards. When I call show datestyle, the value remains at "MDY". In PgAdmin this works like this:




SET datestyle = 'ISO, DMY';




How can I achieve this with Npgsql in C#?



The SQL Create Statements for my table



CREATE TABLE public.t_plate_history (
id integer NOT NULL,
plate integer NOT NULL,
"timestamp" timestamp with time zone NOT NULL,
direction boolean NOT NULL,
"position" integer NOT NULL
);


The Timestamp is created in a sorted list with the DateTime.Now command.



SortedList<string, object> values = new SortedList<string, object>

TblPlateHistory.FieldDirection, insert,
TblPlateHistory.FieldPlate, LocalDataSet.Plate.Id,
TblPlateHistory.FieldPosition, LocalDataSet.PlatePosition.Id,
TblPlateHistory.FieldTimestamp, DateTime.Now
;


TblPlateHistory.FieldTimestamp, DateTime.Now


The access via Npgsql then looks like this, the INSERT statement given in string sqlCmd is the following:



 INSERT INTO t_plate_history(plate,timestamp,direction,position) VALUES(1359,'09.02.2018 15:02:08' ,true,705)


An finally the code where i make the transaction with Npgsql:



public bool RunNonQueryCommand(string sqlCmd, NpgsqlTransaction transAction, NpgsqlConnection connection)

NpgsqlCommand sqlCom = new NpgsqlCommand(sqlCmd, connection);
bool rt = true;
try

sqlCom.Transaction = transAction;
sqlCom.ExecuteNonQuery();


...

return rt;












share|improve this question
























  • What do you mean when you say "the datetype will not be changed in the database afterwards", what exactly are you trying to accomplish? The DateStyle parameter only manages the textual representation of timestamps, and has no effect on what Npgsql sends or receives from PostgreSQL.

    – Shay Rojansky
    Nov 15 '18 at 16:38











  • I want to programmatically change the behavior of the database. In the Query Tool of pg Admin I can send this SQL statement and it changes the Date and Time format. Currently, when I send my timestamps and the format is "MDY", an exception occurs.

    – selbolder
    Nov 16 '18 at 7:43











  • Please post a full SQL code sample, including the code that sends/receives the timestamp.

    – Shay Rojansky
    Nov 16 '18 at 10:41











  • Ok Shay, I added that information.

    – selbolder
    Nov 17 '18 at 10:00











  • Why do you want to change the date format? I can see your insert statement has the DMY format. If the insert statement is not accepted because of date format, I think it might be easier just to generate insert statements in the expected format in the first place. In .NET you can use have DateTime.ToString("xxx") with the correct format expected by the db. This would be better than setting the date format for every new connection.

    – devdimi
    Nov 19 '18 at 12:40
















0















I want to change the datetype to th "DMY" Format in a postgres sql database. I use the Npgsql library as follows:



 public void SetDateSytleDB()

string sqlConnectionString = "Server=localhost;Port=5432;User Id=test;Password=test;Database=test_db";

sqlCnctn = new NpgsqlConnection(sqlConnectionString);
sqlCnctn.Open();
if (sqlCnctn.State == ConnectionState.Open)

String strSetDatestyle = "SET datestyle = 'ISO, DMY'";

using (var cmd = new NpgsqlCommand(strSetDatestyle, sqlCnctn))

cmd.ExecuteNonQuery();





unfortunately the datetype will not be changed in the database afterwards. When I call show datestyle, the value remains at "MDY". In PgAdmin this works like this:




SET datestyle = 'ISO, DMY';




How can I achieve this with Npgsql in C#?



The SQL Create Statements for my table



CREATE TABLE public.t_plate_history (
id integer NOT NULL,
plate integer NOT NULL,
"timestamp" timestamp with time zone NOT NULL,
direction boolean NOT NULL,
"position" integer NOT NULL
);


The Timestamp is created in a sorted list with the DateTime.Now command.



SortedList<string, object> values = new SortedList<string, object>

TblPlateHistory.FieldDirection, insert,
TblPlateHistory.FieldPlate, LocalDataSet.Plate.Id,
TblPlateHistory.FieldPosition, LocalDataSet.PlatePosition.Id,
TblPlateHistory.FieldTimestamp, DateTime.Now
;


TblPlateHistory.FieldTimestamp, DateTime.Now


The access via Npgsql then looks like this, the INSERT statement given in string sqlCmd is the following:



 INSERT INTO t_plate_history(plate,timestamp,direction,position) VALUES(1359,'09.02.2018 15:02:08' ,true,705)


An finally the code where i make the transaction with Npgsql:



public bool RunNonQueryCommand(string sqlCmd, NpgsqlTransaction transAction, NpgsqlConnection connection)

NpgsqlCommand sqlCom = new NpgsqlCommand(sqlCmd, connection);
bool rt = true;
try

sqlCom.Transaction = transAction;
sqlCom.ExecuteNonQuery();


...

return rt;












share|improve this question
























  • What do you mean when you say "the datetype will not be changed in the database afterwards", what exactly are you trying to accomplish? The DateStyle parameter only manages the textual representation of timestamps, and has no effect on what Npgsql sends or receives from PostgreSQL.

    – Shay Rojansky
    Nov 15 '18 at 16:38











  • I want to programmatically change the behavior of the database. In the Query Tool of pg Admin I can send this SQL statement and it changes the Date and Time format. Currently, when I send my timestamps and the format is "MDY", an exception occurs.

    – selbolder
    Nov 16 '18 at 7:43











  • Please post a full SQL code sample, including the code that sends/receives the timestamp.

    – Shay Rojansky
    Nov 16 '18 at 10:41











  • Ok Shay, I added that information.

    – selbolder
    Nov 17 '18 at 10:00











  • Why do you want to change the date format? I can see your insert statement has the DMY format. If the insert statement is not accepted because of date format, I think it might be easier just to generate insert statements in the expected format in the first place. In .NET you can use have DateTime.ToString("xxx") with the correct format expected by the db. This would be better than setting the date format for every new connection.

    – devdimi
    Nov 19 '18 at 12:40














0












0








0


1






I want to change the datetype to th "DMY" Format in a postgres sql database. I use the Npgsql library as follows:



 public void SetDateSytleDB()

string sqlConnectionString = "Server=localhost;Port=5432;User Id=test;Password=test;Database=test_db";

sqlCnctn = new NpgsqlConnection(sqlConnectionString);
sqlCnctn.Open();
if (sqlCnctn.State == ConnectionState.Open)

String strSetDatestyle = "SET datestyle = 'ISO, DMY'";

using (var cmd = new NpgsqlCommand(strSetDatestyle, sqlCnctn))

cmd.ExecuteNonQuery();





unfortunately the datetype will not be changed in the database afterwards. When I call show datestyle, the value remains at "MDY". In PgAdmin this works like this:




SET datestyle = 'ISO, DMY';




How can I achieve this with Npgsql in C#?



The SQL Create Statements for my table



CREATE TABLE public.t_plate_history (
id integer NOT NULL,
plate integer NOT NULL,
"timestamp" timestamp with time zone NOT NULL,
direction boolean NOT NULL,
"position" integer NOT NULL
);


The Timestamp is created in a sorted list with the DateTime.Now command.



SortedList<string, object> values = new SortedList<string, object>

TblPlateHistory.FieldDirection, insert,
TblPlateHistory.FieldPlate, LocalDataSet.Plate.Id,
TblPlateHistory.FieldPosition, LocalDataSet.PlatePosition.Id,
TblPlateHistory.FieldTimestamp, DateTime.Now
;


TblPlateHistory.FieldTimestamp, DateTime.Now


The access via Npgsql then looks like this, the INSERT statement given in string sqlCmd is the following:



 INSERT INTO t_plate_history(plate,timestamp,direction,position) VALUES(1359,'09.02.2018 15:02:08' ,true,705)


An finally the code where i make the transaction with Npgsql:



public bool RunNonQueryCommand(string sqlCmd, NpgsqlTransaction transAction, NpgsqlConnection connection)

NpgsqlCommand sqlCom = new NpgsqlCommand(sqlCmd, connection);
bool rt = true;
try

sqlCom.Transaction = transAction;
sqlCom.ExecuteNonQuery();


...

return rt;












share|improve this question
















I want to change the datetype to th "DMY" Format in a postgres sql database. I use the Npgsql library as follows:



 public void SetDateSytleDB()

string sqlConnectionString = "Server=localhost;Port=5432;User Id=test;Password=test;Database=test_db";

sqlCnctn = new NpgsqlConnection(sqlConnectionString);
sqlCnctn.Open();
if (sqlCnctn.State == ConnectionState.Open)

String strSetDatestyle = "SET datestyle = 'ISO, DMY'";

using (var cmd = new NpgsqlCommand(strSetDatestyle, sqlCnctn))

cmd.ExecuteNonQuery();





unfortunately the datetype will not be changed in the database afterwards. When I call show datestyle, the value remains at "MDY". In PgAdmin this works like this:




SET datestyle = 'ISO, DMY';




How can I achieve this with Npgsql in C#?



The SQL Create Statements for my table



CREATE TABLE public.t_plate_history (
id integer NOT NULL,
plate integer NOT NULL,
"timestamp" timestamp with time zone NOT NULL,
direction boolean NOT NULL,
"position" integer NOT NULL
);


The Timestamp is created in a sorted list with the DateTime.Now command.



SortedList<string, object> values = new SortedList<string, object>

TblPlateHistory.FieldDirection, insert,
TblPlateHistory.FieldPlate, LocalDataSet.Plate.Id,
TblPlateHistory.FieldPosition, LocalDataSet.PlatePosition.Id,
TblPlateHistory.FieldTimestamp, DateTime.Now
;


TblPlateHistory.FieldTimestamp, DateTime.Now


The access via Npgsql then looks like this, the INSERT statement given in string sqlCmd is the following:



 INSERT INTO t_plate_history(plate,timestamp,direction,position) VALUES(1359,'09.02.2018 15:02:08' ,true,705)


An finally the code where i make the transaction with Npgsql:



public bool RunNonQueryCommand(string sqlCmd, NpgsqlTransaction transAction, NpgsqlConnection connection)

NpgsqlCommand sqlCom = new NpgsqlCommand(sqlCmd, connection);
bool rt = true;
try

sqlCom.Transaction = transAction;
sqlCom.ExecuteNonQuery();


...

return rt;









c# postgresql npgsql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 17 '18 at 9:59







selbolder

















asked Nov 15 '18 at 15:47









selbolderselbolder

9311




9311












  • What do you mean when you say "the datetype will not be changed in the database afterwards", what exactly are you trying to accomplish? The DateStyle parameter only manages the textual representation of timestamps, and has no effect on what Npgsql sends or receives from PostgreSQL.

    – Shay Rojansky
    Nov 15 '18 at 16:38











  • I want to programmatically change the behavior of the database. In the Query Tool of pg Admin I can send this SQL statement and it changes the Date and Time format. Currently, when I send my timestamps and the format is "MDY", an exception occurs.

    – selbolder
    Nov 16 '18 at 7:43











  • Please post a full SQL code sample, including the code that sends/receives the timestamp.

    – Shay Rojansky
    Nov 16 '18 at 10:41











  • Ok Shay, I added that information.

    – selbolder
    Nov 17 '18 at 10:00











  • Why do you want to change the date format? I can see your insert statement has the DMY format. If the insert statement is not accepted because of date format, I think it might be easier just to generate insert statements in the expected format in the first place. In .NET you can use have DateTime.ToString("xxx") with the correct format expected by the db. This would be better than setting the date format for every new connection.

    – devdimi
    Nov 19 '18 at 12:40


















  • What do you mean when you say "the datetype will not be changed in the database afterwards", what exactly are you trying to accomplish? The DateStyle parameter only manages the textual representation of timestamps, and has no effect on what Npgsql sends or receives from PostgreSQL.

    – Shay Rojansky
    Nov 15 '18 at 16:38











  • I want to programmatically change the behavior of the database. In the Query Tool of pg Admin I can send this SQL statement and it changes the Date and Time format. Currently, when I send my timestamps and the format is "MDY", an exception occurs.

    – selbolder
    Nov 16 '18 at 7:43











  • Please post a full SQL code sample, including the code that sends/receives the timestamp.

    – Shay Rojansky
    Nov 16 '18 at 10:41











  • Ok Shay, I added that information.

    – selbolder
    Nov 17 '18 at 10:00











  • Why do you want to change the date format? I can see your insert statement has the DMY format. If the insert statement is not accepted because of date format, I think it might be easier just to generate insert statements in the expected format in the first place. In .NET you can use have DateTime.ToString("xxx") with the correct format expected by the db. This would be better than setting the date format for every new connection.

    – devdimi
    Nov 19 '18 at 12:40

















What do you mean when you say "the datetype will not be changed in the database afterwards", what exactly are you trying to accomplish? The DateStyle parameter only manages the textual representation of timestamps, and has no effect on what Npgsql sends or receives from PostgreSQL.

– Shay Rojansky
Nov 15 '18 at 16:38





What do you mean when you say "the datetype will not be changed in the database afterwards", what exactly are you trying to accomplish? The DateStyle parameter only manages the textual representation of timestamps, and has no effect on what Npgsql sends or receives from PostgreSQL.

– Shay Rojansky
Nov 15 '18 at 16:38













I want to programmatically change the behavior of the database. In the Query Tool of pg Admin I can send this SQL statement and it changes the Date and Time format. Currently, when I send my timestamps and the format is "MDY", an exception occurs.

– selbolder
Nov 16 '18 at 7:43





I want to programmatically change the behavior of the database. In the Query Tool of pg Admin I can send this SQL statement and it changes the Date and Time format. Currently, when I send my timestamps and the format is "MDY", an exception occurs.

– selbolder
Nov 16 '18 at 7:43













Please post a full SQL code sample, including the code that sends/receives the timestamp.

– Shay Rojansky
Nov 16 '18 at 10:41





Please post a full SQL code sample, including the code that sends/receives the timestamp.

– Shay Rojansky
Nov 16 '18 at 10:41













Ok Shay, I added that information.

– selbolder
Nov 17 '18 at 10:00





Ok Shay, I added that information.

– selbolder
Nov 17 '18 at 10:00













Why do you want to change the date format? I can see your insert statement has the DMY format. If the insert statement is not accepted because of date format, I think it might be easier just to generate insert statements in the expected format in the first place. In .NET you can use have DateTime.ToString("xxx") with the correct format expected by the db. This would be better than setting the date format for every new connection.

– devdimi
Nov 19 '18 at 12:40






Why do you want to change the date format? I can see your insert statement has the DMY format. If the insert statement is not accepted because of date format, I think it might be easier just to generate insert statements in the expected format in the first place. In .NET you can use have DateTime.ToString("xxx") with the correct format expected by the db. This would be better than setting the date format for every new connection.

– devdimi
Nov 19 '18 at 12:40













2 Answers
2






active

oldest

votes


















1














If you have access to the server you can configure it in postgresql.conf.



Otherwise try setting the PGDATESTYLE environment variable on server or client as stated in the documentation.



Your way of setting it might work for the current transaction only.
You can try to reuse the same open connection and check if it has effect.



The real question is why do you want to change this setting?
Instead you can use insert statements like this:



INSERT INTO t_plate_history(id, plate,timestamp,direction,position) 
VALUES(1, 1359, timestamp '2018-02-09 15:02:08' ,true,705)


To get the this date format you can use:



DateTime dateTime = new DateTime(2018, 2, 9, 15, 2, 8); // get you datetime object
dateTime.ToString("yyyy-MM-dd HH:mm:ss")


Even better would be to use prepared statements, where you only set the DateTime object and the provider takes care of the rest. Check out the documentation






share|improve this answer
































    2














    What you're doing still isn't clear - there are multiple code fragments in your question and it's not clear how they interact. Please consider posting a single, minimal function with only the necessary code reproducing your issue.



    Nevertheless, from the first fragment it seems that you are opening an NpgsqlConnection, setting the DateStyle parameter and then abandoning that connection (the function exits). Setting the DateStyle in a connection will only change the setting for that connection, and not other connections. In addition, the moment that connection is closed (i.e. returned to the connection pool), its state will be reset and the DateStyle change will be undone.



    If you're trying to change the DateStyle for all connections to the database, you can follow @devdimi's suggestion to set the parameter in postgresql.conf or via the PGDATESTYLE environment variable. Or you must take care to set the parameter every time after you open a connection.



    Note that if you don't close or dispose a connection after opening it, like you do in your first code sample, you are leaking connections. You must always close connections, possibly via the C# using statement.






    share|improve this answer























    • Did I understand that correctly when I send a SET command (SET datestyle = 'ISO, DMY';) in C# with Npgsql and then query the state of datestyle with "show datestyle" in pgAdmin, then the state of datestyle is reset because it is a new or different connection? Would it work with the command ALTER DATABASE database_name SET datestyle TO "ISO, MDY"; , is that possible, and would it be persistent?

      – selbolder
      Nov 19 '18 at 10:00






    • 1





      Yes, parameter changes via SET are specific to your specific connection, and Npgsql's pool resets them to avoid state leakage. Using ALTER DATABASE should work, as it sets the default parameter value for new connections on that database.

      – Shay Rojansky
      Nov 19 '18 at 10:15










    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323082%2fhow-to-set-datestyle-in-postgres-db-in-c-sharp%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    If you have access to the server you can configure it in postgresql.conf.



    Otherwise try setting the PGDATESTYLE environment variable on server or client as stated in the documentation.



    Your way of setting it might work for the current transaction only.
    You can try to reuse the same open connection and check if it has effect.



    The real question is why do you want to change this setting?
    Instead you can use insert statements like this:



    INSERT INTO t_plate_history(id, plate,timestamp,direction,position) 
    VALUES(1, 1359, timestamp '2018-02-09 15:02:08' ,true,705)


    To get the this date format you can use:



    DateTime dateTime = new DateTime(2018, 2, 9, 15, 2, 8); // get you datetime object
    dateTime.ToString("yyyy-MM-dd HH:mm:ss")


    Even better would be to use prepared statements, where you only set the DateTime object and the provider takes care of the rest. Check out the documentation






    share|improve this answer





























      1














      If you have access to the server you can configure it in postgresql.conf.



      Otherwise try setting the PGDATESTYLE environment variable on server or client as stated in the documentation.



      Your way of setting it might work for the current transaction only.
      You can try to reuse the same open connection and check if it has effect.



      The real question is why do you want to change this setting?
      Instead you can use insert statements like this:



      INSERT INTO t_plate_history(id, plate,timestamp,direction,position) 
      VALUES(1, 1359, timestamp '2018-02-09 15:02:08' ,true,705)


      To get the this date format you can use:



      DateTime dateTime = new DateTime(2018, 2, 9, 15, 2, 8); // get you datetime object
      dateTime.ToString("yyyy-MM-dd HH:mm:ss")


      Even better would be to use prepared statements, where you only set the DateTime object and the provider takes care of the rest. Check out the documentation






      share|improve this answer



























        1












        1








        1







        If you have access to the server you can configure it in postgresql.conf.



        Otherwise try setting the PGDATESTYLE environment variable on server or client as stated in the documentation.



        Your way of setting it might work for the current transaction only.
        You can try to reuse the same open connection and check if it has effect.



        The real question is why do you want to change this setting?
        Instead you can use insert statements like this:



        INSERT INTO t_plate_history(id, plate,timestamp,direction,position) 
        VALUES(1, 1359, timestamp '2018-02-09 15:02:08' ,true,705)


        To get the this date format you can use:



        DateTime dateTime = new DateTime(2018, 2, 9, 15, 2, 8); // get you datetime object
        dateTime.ToString("yyyy-MM-dd HH:mm:ss")


        Even better would be to use prepared statements, where you only set the DateTime object and the provider takes care of the rest. Check out the documentation






        share|improve this answer















        If you have access to the server you can configure it in postgresql.conf.



        Otherwise try setting the PGDATESTYLE environment variable on server or client as stated in the documentation.



        Your way of setting it might work for the current transaction only.
        You can try to reuse the same open connection and check if it has effect.



        The real question is why do you want to change this setting?
        Instead you can use insert statements like this:



        INSERT INTO t_plate_history(id, plate,timestamp,direction,position) 
        VALUES(1, 1359, timestamp '2018-02-09 15:02:08' ,true,705)


        To get the this date format you can use:



        DateTime dateTime = new DateTime(2018, 2, 9, 15, 2, 8); // get you datetime object
        dateTime.ToString("yyyy-MM-dd HH:mm:ss")


        Even better would be to use prepared statements, where you only set the DateTime object and the provider takes care of the rest. Check out the documentation







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 19 '18 at 14:28

























        answered Nov 15 '18 at 15:53









        devdimidevdimi

        2,2211716




        2,2211716























            2














            What you're doing still isn't clear - there are multiple code fragments in your question and it's not clear how they interact. Please consider posting a single, minimal function with only the necessary code reproducing your issue.



            Nevertheless, from the first fragment it seems that you are opening an NpgsqlConnection, setting the DateStyle parameter and then abandoning that connection (the function exits). Setting the DateStyle in a connection will only change the setting for that connection, and not other connections. In addition, the moment that connection is closed (i.e. returned to the connection pool), its state will be reset and the DateStyle change will be undone.



            If you're trying to change the DateStyle for all connections to the database, you can follow @devdimi's suggestion to set the parameter in postgresql.conf or via the PGDATESTYLE environment variable. Or you must take care to set the parameter every time after you open a connection.



            Note that if you don't close or dispose a connection after opening it, like you do in your first code sample, you are leaking connections. You must always close connections, possibly via the C# using statement.






            share|improve this answer























            • Did I understand that correctly when I send a SET command (SET datestyle = 'ISO, DMY';) in C# with Npgsql and then query the state of datestyle with "show datestyle" in pgAdmin, then the state of datestyle is reset because it is a new or different connection? Would it work with the command ALTER DATABASE database_name SET datestyle TO "ISO, MDY"; , is that possible, and would it be persistent?

              – selbolder
              Nov 19 '18 at 10:00






            • 1





              Yes, parameter changes via SET are specific to your specific connection, and Npgsql's pool resets them to avoid state leakage. Using ALTER DATABASE should work, as it sets the default parameter value for new connections on that database.

              – Shay Rojansky
              Nov 19 '18 at 10:15















            2














            What you're doing still isn't clear - there are multiple code fragments in your question and it's not clear how they interact. Please consider posting a single, minimal function with only the necessary code reproducing your issue.



            Nevertheless, from the first fragment it seems that you are opening an NpgsqlConnection, setting the DateStyle parameter and then abandoning that connection (the function exits). Setting the DateStyle in a connection will only change the setting for that connection, and not other connections. In addition, the moment that connection is closed (i.e. returned to the connection pool), its state will be reset and the DateStyle change will be undone.



            If you're trying to change the DateStyle for all connections to the database, you can follow @devdimi's suggestion to set the parameter in postgresql.conf or via the PGDATESTYLE environment variable. Or you must take care to set the parameter every time after you open a connection.



            Note that if you don't close or dispose a connection after opening it, like you do in your first code sample, you are leaking connections. You must always close connections, possibly via the C# using statement.






            share|improve this answer























            • Did I understand that correctly when I send a SET command (SET datestyle = 'ISO, DMY';) in C# with Npgsql and then query the state of datestyle with "show datestyle" in pgAdmin, then the state of datestyle is reset because it is a new or different connection? Would it work with the command ALTER DATABASE database_name SET datestyle TO "ISO, MDY"; , is that possible, and would it be persistent?

              – selbolder
              Nov 19 '18 at 10:00






            • 1





              Yes, parameter changes via SET are specific to your specific connection, and Npgsql's pool resets them to avoid state leakage. Using ALTER DATABASE should work, as it sets the default parameter value for new connections on that database.

              – Shay Rojansky
              Nov 19 '18 at 10:15













            2












            2








            2







            What you're doing still isn't clear - there are multiple code fragments in your question and it's not clear how they interact. Please consider posting a single, minimal function with only the necessary code reproducing your issue.



            Nevertheless, from the first fragment it seems that you are opening an NpgsqlConnection, setting the DateStyle parameter and then abandoning that connection (the function exits). Setting the DateStyle in a connection will only change the setting for that connection, and not other connections. In addition, the moment that connection is closed (i.e. returned to the connection pool), its state will be reset and the DateStyle change will be undone.



            If you're trying to change the DateStyle for all connections to the database, you can follow @devdimi's suggestion to set the parameter in postgresql.conf or via the PGDATESTYLE environment variable. Or you must take care to set the parameter every time after you open a connection.



            Note that if you don't close or dispose a connection after opening it, like you do in your first code sample, you are leaking connections. You must always close connections, possibly via the C# using statement.






            share|improve this answer













            What you're doing still isn't clear - there are multiple code fragments in your question and it's not clear how they interact. Please consider posting a single, minimal function with only the necessary code reproducing your issue.



            Nevertheless, from the first fragment it seems that you are opening an NpgsqlConnection, setting the DateStyle parameter and then abandoning that connection (the function exits). Setting the DateStyle in a connection will only change the setting for that connection, and not other connections. In addition, the moment that connection is closed (i.e. returned to the connection pool), its state will be reset and the DateStyle change will be undone.



            If you're trying to change the DateStyle for all connections to the database, you can follow @devdimi's suggestion to set the parameter in postgresql.conf or via the PGDATESTYLE environment variable. Or you must take care to set the parameter every time after you open a connection.



            Note that if you don't close or dispose a connection after opening it, like you do in your first code sample, you are leaking connections. You must always close connections, possibly via the C# using statement.







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Nov 17 '18 at 10:51









            Shay RojanskyShay Rojansky

            7,00421845




            7,00421845












            • Did I understand that correctly when I send a SET command (SET datestyle = 'ISO, DMY';) in C# with Npgsql and then query the state of datestyle with "show datestyle" in pgAdmin, then the state of datestyle is reset because it is a new or different connection? Would it work with the command ALTER DATABASE database_name SET datestyle TO "ISO, MDY"; , is that possible, and would it be persistent?

              – selbolder
              Nov 19 '18 at 10:00






            • 1





              Yes, parameter changes via SET are specific to your specific connection, and Npgsql's pool resets them to avoid state leakage. Using ALTER DATABASE should work, as it sets the default parameter value for new connections on that database.

              – Shay Rojansky
              Nov 19 '18 at 10:15

















            • Did I understand that correctly when I send a SET command (SET datestyle = 'ISO, DMY';) in C# with Npgsql and then query the state of datestyle with "show datestyle" in pgAdmin, then the state of datestyle is reset because it is a new or different connection? Would it work with the command ALTER DATABASE database_name SET datestyle TO "ISO, MDY"; , is that possible, and would it be persistent?

              – selbolder
              Nov 19 '18 at 10:00






            • 1





              Yes, parameter changes via SET are specific to your specific connection, and Npgsql's pool resets them to avoid state leakage. Using ALTER DATABASE should work, as it sets the default parameter value for new connections on that database.

              – Shay Rojansky
              Nov 19 '18 at 10:15
















            Did I understand that correctly when I send a SET command (SET datestyle = 'ISO, DMY';) in C# with Npgsql and then query the state of datestyle with "show datestyle" in pgAdmin, then the state of datestyle is reset because it is a new or different connection? Would it work with the command ALTER DATABASE database_name SET datestyle TO "ISO, MDY"; , is that possible, and would it be persistent?

            – selbolder
            Nov 19 '18 at 10:00





            Did I understand that correctly when I send a SET command (SET datestyle = 'ISO, DMY';) in C# with Npgsql and then query the state of datestyle with "show datestyle" in pgAdmin, then the state of datestyle is reset because it is a new or different connection? Would it work with the command ALTER DATABASE database_name SET datestyle TO "ISO, MDY"; , is that possible, and would it be persistent?

            – selbolder
            Nov 19 '18 at 10:00




            1




            1





            Yes, parameter changes via SET are specific to your specific connection, and Npgsql's pool resets them to avoid state leakage. Using ALTER DATABASE should work, as it sets the default parameter value for new connections on that database.

            – Shay Rojansky
            Nov 19 '18 at 10:15





            Yes, parameter changes via SET are specific to your specific connection, and Npgsql's pool resets them to avoid state leakage. Using ALTER DATABASE should work, as it sets the default parameter value for new connections on that database.

            – Shay Rojansky
            Nov 19 '18 at 10:15

















            draft saved

            draft discarded
















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323082%2fhow-to-set-datestyle-in-postgres-db-in-c-sharp%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Top Tejano songwriter Luis Silva dead of heart attack at 64

            政党

            天津地下鉄3号線