using(PgSqlCommand sql = PostgreSQL.CreateCommadn()) & Store Procedure

[mr-owl]

Hi,

I have problem with my application, I have 2 tables: rm_bag & rm_bag_item

Code: Select all

CREATE TABLE rm_bag
(
  bag_id serial NOT NULL,
  bag_computer text NOT NULL,
  bag_type character(1) NOT NULL,
  bag_sortcode integer NOT NULL,
  report_id integer,
  bag_items_count integer NOT NULL DEFAULT 0,
  bag_weight integer NOT NULL DEFAULT 0,
  bag_start_time timestamp without time zone NOT NULL DEFAULT now(),
  bag_done boolean NOT NULL DEFAULT false,
  bag_done_time timestamp without time zone
)
WITH (OIDS=FALSE);

Code: Select all

CREATE TABLE rm_bag_item
(
  bag_item_id serial NOT NULL,
  bag_id integer NOT NULL,
  bag_item_weight integer NOT NULL,
  bag_item_date timestamp without time zone NOT NULL DEFAULT now()
)
WITH (OIDS=FALSE);

plpgsql function

Code: Select all

CREATE OR REPLACE FUNCTION rm_reset_bag(id integer) RETURNS void AS $$
BEGIN
  DELETE FROM rm_bag_item WHERE bag_id=id;
  DELETE FROM rm_bag WHERE bag_id=id;
END;
$$ LANGUAGE 'plpgsql';

and C# code

Code: Select all

if(dataGridView.CurrentRow.Index >= 0 && PostgreSQL.State == ConnectionState.Open)
{
  using (PgSqlCommand sql = PostgreSQL.CreateCommand())
  {
    sql.CommandType = CommandType.StoredProcedure;
    sql.Parameters.Add("id", PgSqlType.Int).Value = (int)dataGridView[0, dataGridView.CurrentRow.Index].Value;

    sql.CommandText = @"rm_reset_bag";

    try
    {
      sql.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
    }
  }
}

When I am executing rm_reset_bag function from pgAdmin3 function is working OK but from C# code they don't remove records from tables. What is wrong?

mr-owl

PostgreSQL 8.3.1, Visual Studio 2005 Standard SP1, PostgreSQLDirect .NET 3.50.25

[Alexey.mdr]

Did you get any exception?
Try checking all values in debug mode.
It seems like the procedure receives some wrong argument value.

[mr-owl]

Did you get any exception?
Try checking all values in debug mode.
It seems like the procedure receives some wrong argument value.

When I replace code to:

Code: Select all

if(dataGridView.CurrentRow.Index >= 0 && PostgreSQL.State == ConnectionState.Open)
{
    using (PgSqlCommand sql = PostgreSQL.CreateCommand())
    {
        sql.CommandType = CommandType.Text;
        sql.CommandText = String.Format("SELECT * FROM rm_reset_bag({0});", (int)dataGridView[0, dataGridView.CurrentRow.Index].Value);

        try
        {
            sql.ExecuteScalar();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

everythink working fine. I don't know why my old version doesn't work. I have another store procedure with this same name but with different parameter type (text) and when I run DBMonitor I see that yours driver call wrong function.

Code: Select all

sql.Parameters.Add("id", PgSqlType.Int).Value = (int)dataGridView[0, dataGridView.CurrentRow.Index].Value;

This look that is something wrong with the driver.

mr-owl

P.S. Sorry, I don't speak English very well

[Alexey.mdr]

When working with overloaded stored procedures, use following syntax for executing specific overloaded routine: "StoredProcName:1" or "StoredProcName:5". First example executes first overloaded stored procedure, while second example executes fifth overloaded procedure. This notation affects only describing procedures if ParameterCheck is true.

For more details please see CommandText Property of PgSqlCommand in the documentation.

[mr-owl]

Hi,

Thank You for explanation, I didn't know about that.

mr-owl

[Alexey.mdr]

This is just the way overloads are handled in PostgreSQLDirect .NET.
Feel free to contact us if you encounter any other issue with the product.