0

I am trying to insert a row into an oracle data base that contains fields that have single quotes, double quotes and probably a few other characters that aren't allowed. I have tried the following :

internal int insertRomance(List<romance> romanceCopy)
    {
          string connect = "Data Source=DB;User ID=DBAPP;Password=*****;pooling=true;min pool size=5;Max Pool Size=60";
        string sql = "";

        foreach (romance r in romanceCopy)
        {
            sql = "Insert into TEXT_ITEM (VENDOR_ABBREV,VENDOR_MODEL,VENDOR_PART_NUM,SKU,PRODUCT_DES,WEB_DESCRIPTION,DEPARTMENT,ACLASS,DEPT_CLASS_DESC,PRICE,BILL_OUT_COST,WEIGHT,HEIGHT,WIDTH,ITEM_DEPTH,READY_TO_ASSEMBLE,LEAD_TIME) values ('" +
            r.VENDOR_ABBREV + "','" +
            r.VENDOR_MODEL + "','" +
            r.VENDOR_PART_NUM + "','" +
            r.SKU + "',q'{" +
            r.PRODUCT_DES + "}',q'{" +
            r.WEB_DESCRIPTION + "}','" +
            r.DEPARTMENT + "','" +
            r.ACLASS + "','" +
            r.DEPT_CLASS_DESC + "','" +
            r.PRICE + "','" +
            r.BILL_OUT_COST + "','" +
            r.WEIGHT + "','" +
            r.HEIGHT + "','" +
            r.WIDTH + "','" +
            r.ITEM_DEPTH + "','" +
            r.READY_TO_ASSEMBLE + "','" +
            r.LEAD_TIME + "');";
            int x = this.DataInsertX(sql, connect, 0);
        }
        throw new NotImplementedException();  //will fix this once I get the code running
    }


  public int DataInsertX(string sql, string connect, int appendFlag)
    {
        //for testing
        OracleTransaction objtrans = null;
        int LastID = 0;
        string query = sql;
        if (appendFlag == 1)
        {
            query += ";SELECT @@Identity;";
        }


        this.OpenX(connect);
        try
        {
            if (oConn2.State.ToString() == "Open")
            {
                objtrans = oConn2.BeginTransaction();
                OracleCommand cmd = new OracleCommand(query, oConn2);

                if (appendFlag == 1)
                {
                    LastID = this.ToInt(cmd.ExecuteScalar());
                }
                else
                {
                    cmd.ExecuteNonQuery();
                    LastID = 1;
                    objtrans.Commit();

                }
            }
            this.CloseX();
            return this.ToInt(LastID);
        }
        catch (Exception ex)
        {
            ex.Message.ToString();
            objtrans.Rollback();
            this.CloseX();
            return 0;
        }

The issue is two fields web_Description, And product_description Both of those could have characters that need to be escaped (or they might not) My romance class is basically a mirror of the fields in the table. This should work according to documentation but i might have an older version of Oracle that does not recognize the

q'{'Test's text}' 

escape command. I have been trying to see if there was any documentation on maybe binding using

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

But documentation is limited with no examples. I could either use some direction to a good documentation with good examples for binding or an Oracle guru who knows what I should look into. Worse comes to worse I will just substitute the characters ( ie switch a ' for @$), but I want to avoid this as much as possible because the individuals using the data stored in this table would have to make sure to revert the symbols

Danimal
  • 315
  • 1
  • 16

0 Answers0