CODEWITHSUNDEEP
c#asp.net-mvcoracle-databasestored-procedures
Abdul
Abdul

Reputation: 1476

Inserting data into Oracle by Stored Procedure with C#

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;

Upvotes: 0

Views: 2318

Answers (2)

John Woo
John Woo

Reputation: 263703

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();

Upvotes: 1

saidfagan
saidfagan

Reputation: 841

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();

Upvotes: 0

Related Questions