Inserting data into Oracle by Stored Procedure with C#

Question

I am trying to insert data into Oracle database with OracleDataAdapter, I am using stored procedure. I am not getting any error, data is also not committing into DB.

public DeviceType AddDeviceType(DeviceType deviceType)
        {
            DeviceType objDeviceType = new DeviceType();
            using (cmd = new OracleCommand("SP_DMS_DEVICE_TYPE_INSERT", con))
            {
                try
                {
                    cmd.CommandType = CommandType.StoredProcedure;                    
                    //cmd.Parameters.Add("p_typename", OracleType.VarChar).Direction = ParameterDirection.Input;
                    cmd.Parameters.AddWithValue("p_typename", deviceType.DeviceTypeName).Direction = ParameterDirection.Input;
                    cmd.Parameters.AddWithValue("p_createdby", deviceType.CreatedBy).Direction = ParameterDirection.Input;
                    cmd.Parameters.AddWithValue("p_createdon", deviceType.CreatedOn).Direction = ParameterDirection.Input;
                    cmd.Parameters.AddWithValue("p_updatedby", deviceType.UpdatedBy).Direction = ParameterDirection.Input;
                    cmd.Parameters.AddWithValue("p_updatedon", deviceType.UpdatedOn).Direction = ParameterDirection.Input;

                    con.Open();
                    adap = new OracleDataAdapter();
                    adap.InsertCommand = cmd;                    
                }
                catch (Exception)
                {
                    con.Close();
                }
                return objDeviceType;
            }
        }

Stored Procedure

CREATE OR REPLACE PROCEDURE DEV_INV.SP_DMS_DEVICE_TYPE_INSERT
(
   //--p_id IN DMS_DEVICE_TYPE.ID%TYPE,
   p_typename IN DMS_DEVICE_TYPE.DEVICE_TYPE_NAME%Type,
   p_createdby IN DMS_DEVICE_TYPE.CREATED_BY%Type,
   p_createdon IN DMS_DEVICE_TYPE.CREATED_ON%TYPE,
   p_updatedby IN DMS_DEVICE_TYPE.UPDATED_BY%Type,
   p_updatedon IN DMS_DEVICE_TYPE.UPDATED_ON%Type
)
AS

   BEGIN
     INSERT INTO DMS_DEVICE_TYPE ("ID","DEVICE_TYPE_NAME","CREATED_BY","CREATED_ON","UPDATED_BY","UPDATED_ON")
     VALUES (DMS_DEVICE_TYPE_S.NextVal, p_typename, p_createdby, p_createdon, p_updatedby, p_updatedon);
     COMMIT;
   END SP_DMS_DEVICE_TYPE_INSERT;

You're not doing anything with the exception you catch (if there is one) outside of closing the connection, and the using statement will do that for you when the block is exited. — Tim
– Tim, Commented Feb 2, 2018 at 6:36

John Woo · Accepted Answer · 2018-02-02 06:34:17Z

1

You don't need to use OracleDataAdapter in your case. All you have to do is to call the execute method.

// other codes here

con.Open();
int recordsAffected = cmd.ExecuteNonQuery();

answered Feb 2, 2018 at 6:34

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abdul Over a year ago

then what OracleDataAdapter .InserCommad do ?

John Woo Over a year ago

OracleDataAdapter is another class specifically used to fill dataset. InsertCommand property is used to set sql statement or stored procedure called during inserting of new record.

saidfagan · Accepted Answer · 2018-02-02 06:34:13Z

0

You can call cmd.ExecuteNonQuery() method to execute stored procedure. So replace this:

con.Open();
adap = new OracleDataAdapter();
adap.InsertCommand = cmd;

With this

con.Open();
cmd.ExecuteNonQuery();

answered Feb 2, 2018 at 6:34

saidfagan

8422 gold badges9 silver badges27 bronze badges

1 Comment

saidfagan Over a year ago

You could achieve what you wanted with InsertCommand too. You needed to call adap.InsertCommand.ExecuteNonQuery(); method. But obviously it is bulky. And when you are calling a single procedure there is no need to create an adapter.

Collectives™ on Stack Overflow

Inserting data into Oracle by Stored Procedure with C#

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related