Thread: The connection is dead

The connection is dead

From
pairat@rachada.com
Date:
Hi,

I'm running PostgreSQL 8.1.1 on the WindowsXP box with following
information:

1. Programming Language C# MS .NET Framework
2. ADO NET connection through PostgresODBC
3. Using "Connection.BeginTransaction(IsolationLevel.ReadCommitted) and
Transaction.Commit to update database.
4. Update record by record.
5. There is no problem if the records is less than 1000 records.
6. If records is over 1000 records, I got the error message "The
connection is dead".

How can I configure the PostgreSQL 8.1.1 to handle it.

Thank you for any suggestion.

Pairat


Re: The connection is dead

From
"Jonel Rienton"
Date:
What does your code look like? And have you tried using Npgsql instead of
the ODBC driver?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of pairat@rachada.com
Sent: Saturday, January 07, 2006 4:39 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] The connection is dead

Hi,

I'm running PostgreSQL 8.1.1 on the WindowsXP box with following
information:

1. Programming Language C# MS .NET Framework 2. ADO NET connection through
PostgresODBC 3. Using
"Connection.BeginTransaction(IsolationLevel.ReadCommitted) and
Transaction.Commit to update database.
4. Update record by record.
5. There is no problem if the records is less than 1000 records.
6. If records is over 1000 records, I got the error message "The connection
is dead".

How can I configure the PostgreSQL 8.1.1 to handle it.

Thank you for any suggestion.

Pairat


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.15/223 - Release Date: 1/6/2006



Re: The connection is dead

From
pairat@rachada.com
Date:
I use ODBC instead of Npgsql because the ODBC can use with MS Access,
MS SQL and PostgreSQL database.

Here is the code.

System.DateTime dateTimeNow = System.DateTime.Now;
int i=0;
dataView1.RowFilter = "Posted = true";
dataView1.Sort = "GLTran, ID"; //GLTran Table
int n = this.BindingContext[dataView1].Count - 1;
string _Journal;
DataRowView DataRowView1;

this.cmdInsert.Transaction = _Transaction;
this.cmdDelete.Transaction = _Transaction;
this.cmdDelete1.Transaction = _Transaction;
this.cmdUpdate1.Transaction = _Transaction;
this.cmdUpdate2.Transaction = _Transaction;

for(i=0; i<=n; i++)
{
    this.BindingContext[dataView1].Position = i;
    DataRowView1 = (DataRowView) this.BindingContext[dataView1].Current;
                            this.cmdInsert.Parameters["Employee_id"].Value =
DataRowView1["Employee_id"];
    this.cmdInsert.Parameters["GJournal"].Value =
DataRowView1["GJournal"];
    this.cmdInsert.Parameters["GJournalDT"].Value =
DataRowView1["GJournalDT"];
    this.cmdInsert.Parameters["GLAccount"].Value =
DataRowView1["GLAccount"];
    this.cmdInsert.Parameters["GLDescription"].Value =
DataRowView1["GLDescription"];
    this.cmdInsert.Parameters["GLCredit"].Value =
DataRowView1["GLCredit"];
    this.cmdInsert.Parameters["GLDebit"].Value = DataRowView1["GLDebit"];
    this.cmdInsert.Parameters["GLTran"].Value = DataRowView1["GLTran"];
    this.cmdInsert.Parameters["TransactionDate"].Value = dateTimeNow;
    this.cmdDelete.Parameters["ID"].Value = DataRowView1["ID"];
    try
    {
        this.cmdInsert.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
        this.RollBack(ex.Message);
    }
    try
    {
        this.cmdDelete.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
        this.RollBack(ex.Message);
    }

    _Journal = DataRowView1["GJournal"].ToString().Trim();
    if (_Journal == "JP" | _Journal == "JR" )
    {
        this.cmdDelete1.Parameters["GLTran"].Value = DataRowView1["GLTran"];
        try
        {
            this.cmdDelete1.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            this.RollBack(ex.Message);
        }
        this.cmdUpdate1.Parameters["Posted"].Value = true;
        this.cmdUpdate1.Parameters["GLTran"].Value = DataRowView1["GLTran"];

        try
        {
            this.cmdUpdate1.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            this.RollBack(ex.Message);
        }
    }

        if (_Journal == "JP" | _Journal == "JR  )
        {
            this.cmdUpdate2.Parameters["Posted"].Value = true;
            this.cmdUpdate2.Parameters["GLTran"].Value = ataRowView1["GLTran"];
            try
            {
                this.cmdUpdate2.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                this.RollBack(ex.Message);
            }
        }
    }

Thank you for your comment.

Pairat


Re: The connection is dead

From
pairat@rachada.com
Date:
Hi Jonel

Thank you for your response. I use ODBC instead of Npgsql because the
program can use with MS Access, MS SQL and PostgreSQL.

Here is the code.


int n = this.BindingContext[dataView1].Count - 1;
string _Journal;
DataRowView DataRowView1;
this.cmdInsert.Transaction = _Transaction;
this.cmdDelete.Transaction = _Transaction;
this.cmdDelete1.Transaction = _Transaction;
this.cmdUpdate1.Transaction = _Transaction;
this.cmdUpdate2.Transaction = _Transaction;

for(i=0; i<=n; i++)
{
    this.BindingContext[dataView1].Position = i;
    DataRowView1 = (DataRowView) this.BindingContext[dataView1].Current;
    this.cmdInsert.Parameters["CustomerID"].Value = string.Empty;
    this.cmdInsert.Parameters["Employee_id"].Value =
DataRowView1["Employee_id"];
    this.cmdInsert.Parameters["GJournal"].Value =
DataRowView1["GJournal"];
    this.cmdInsert.Parameters["GJournalDT"].Value =
DataRowView1["GJournalDT"];
                this.cmdInsert.Parameters["TransactionDate"].Value =
dateTimeNow;

                this.cmdDelete.Parameters["ID"].Value =
DataRowView1["ID"];
    try
    {
         this.cmdInsert.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
          this.RollBack(ex.Message);
    }
    try
    {
          this.cmdDelete.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
          this.RollBack(ex.Message);
    }

    _Journal = DataRowView1["GJournal"].ToString().Trim();
    if (_Journal == "JP" | _Journal == "JR" )
    {
          this.cmdDelete1.Parameters["GLTran"].Value =
DataRowView1["GLTran"];
    try
    {
          this.cmdDelete1.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
          this.RollBack(ex.Message);
    }

    this.cmdUpdate1.Parameters["Posted"].Value = true;
    this.cmdUpdate1.Parameters["GLTran"].Value = DataRowView1["GLTran"];

    try
    {
            this.cmdUpdate1.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
            this.RollBack(ex.Message);
    }
}


Re: The connection is dead

From
pairat@rachada.com
Date:
Thank you for your response. I use ODBC instead of Npgsql because I can
use ODBC with other database such as MS Access, and MS SQL.

Here is the code.

System.DateTime dateTimeNow = System.DateTime.Now;
int i=0;
dataView1.RowFilter = "Posted = true";
dataView1.Sort = "GLTran, ID"; //GLTran Table
int n = this.BindingContext[dataView1].Count - 1;
string _Journal;
DataRowView DataRowView1;

this.cmdInsert.Transaction = _Transaction;
this.cmdDelete.Transaction = _Transaction;
this.cmdDelete1.Transaction = _Transaction;
this.cmdUpdate1.Transaction = _Transaction;
this.cmdUpdate2.Transaction = _Transaction;

for(i=0; i<=n; i++)
{
    this.BindingContext[dataView1].Position = i;
    DataRowView1 = (DataRowView) this.BindingContext[dataView1].Current;
                            this.cmdInsert.Parameters["Employee_id"].Value =
DataRowView1["Employee_id"];
    this.cmdInsert.Parameters["GJournal"].Value =
DataRowView1["GJournal"];
    this.cmdInsert.Parameters["GJournalDT"].Value =
DataRowView1["GJournalDT"];
    this.cmdInsert.Parameters["GLAccount"].Value =
DataRowView1["GLAccount"];
    this.cmdInsert.Parameters["GLDescription"].Value =
DataRowView1["GLDescription"];
    this.cmdInsert.Parameters["GLCredit"].Value =
DataRowView1["GLCredit"];
    this.cmdInsert.Parameters["GLDebit"].Value = DataRowView1["GLDebit"];
    this.cmdInsert.Parameters["GLTran"].Value = DataRowView1["GLTran"];
    this.cmdInsert.Parameters["TransactionDate"].Value = dateTimeNow;
    this.cmdDelete.Parameters["ID"].Value = DataRowView1["ID"];
    try
    {
        this.cmdInsert.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
        this.RollBack(ex.Message);
    }
    try
    {
        this.cmdDelete.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
        this.RollBack(ex.Message);
    }

    _Journal = DataRowView1["GJournal"].ToString().Trim();
    if (_Journal == "JP" | _Journal == "JR" )
    {
        this.cmdDelete1.Parameters["GLTran"].Value = DataRowView1["GLTran"];
        try
        {
            this.cmdDelete1.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            this.RollBack(ex.Message);
        }
        this.cmdUpdate1.Parameters["Posted"].Value = true;
        this.cmdUpdate1.Parameters["GLTran"].Value = DataRowView1["GLTran"];

        try
        {
            this.cmdUpdate1.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            this.RollBack(ex.Message);
        }
    }

        if (_Journal == "JP" | _Journal == "JR  )
        {
            this.cmdUpdate2.Parameters["Posted"].Value = true;
            this.cmdUpdate2.Parameters["GLTran"].Value = ataRowView1["GLTran"];
            try
            {
                this.cmdUpdate2.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                this.RollBack(ex.Message);
            }
        }
    }