CODEWITHSUNDEEP
c#stored-proceduresfirebird
gera_moran
gera_moran

Reputation: 21

Update table in firebird stored procedure called from c#

I have this stored procedure in firebird:

create or alter procedure "LKS_CambiaEstadoAgenda" (
    "param_id" integer)
as
begin
    update
        "Agenda"
    set
        "Agenda".estatus = 2
    where
        "Agenda"."idAgenda" = :"param_id";
end

and this C# code:

public bool cambioEstadoAgenda(int idAgenda)
{
        ConectarBD();

        try
        {
            comando.CommandType = CommandType.StoredProcedure;
            comando.CommandText = "\"LKS_CambiaEstadoAgenda\"";

            comando.Parameters.Clear();
            comando.Parameters.AddWithValue("@param_id", idAgenda);

            comando.Connection = conexion;

            if (Convert.ToInt32(comando.ExecuteScalar()) > 0)
                return true;
            else
                return false;
        }
        catch (Exception ex)
        {
            MessageBox.Show("ups. un error\n" + ex.Message);
            return false;
        }
        finally
        {
            comando.Parameters.Clear();
            DesconectarBD();
        }
}

When I execute it, it does not make the change in the database. What is the error in my code or what recommendation can you give me?

Upvotes: 0

Views: 626

Answers (1)

Mr. Nice
Mr. Nice

Reputation: 417

You need to commit the transaction. Example can be seen here under 2. point (UPDATE)

2. Update a text blob field

public static void Main(string[] args)
{
// Set the ServerType to 1 for connect to the embedded server
string connectionString =
"User=SYSDBA;" +
"Password=masterkey;" +
"Database=SampleDatabase.fdb;" +
"DataSource=localhost;" +
"Port=3050;" +
"Dialect=3;" +
"Charset=NONE;" +
"Role=;" +
"Connection lifetime=15;" +
"Pooling=true;" +
"Packet Size=8192;" +
"ServerType=0";

FbConnection myConnection = new FbConnection(connectionString);
myConnection.Open();

FbTransaction myTransaction = myConnection.BeginTransaction();

FbCommand myCommand = new FbCommand();

myCommand.CommandText =
"UPDATE TEST_TABLE_01 SET CLOB_FIELD = @CLOB_FIELD WHERE INT_FIELD = @INT_FIELD";
myCommand.Connection = myConnection;
myCommand.Transaction = myTransaction;

myCommand.Parameters.Add("@INT_FIELD", FbType.Integer, "INT_FIELD");
myCommand.Parameters.Add("@CLOB_FIELD", FbType.Text, "CLOB_FIELD");

myCommand.Parameters[0].Value = 1;
myCommand.Parameters[1].Value = GetFileContents(@"GDS.CS");

// Execute Update
myCommand.ExecuteNonQuery();

// Commit changes
myTransaction.Commit();

// Free command resources in Firebird Server
myCommand.Dispose();

// Close connection
myConnection.Close();
}

public static string GetFileContents(string fileName)
{
  StreamReader reader = new StreamReader(new FileStream(fileName, FileMode.Open));
  string contents = reader.ReadToEnd();
  reader.Close();
  return contents;
}

Upvotes: 1

Related Questions