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