C# Oracle带参数执行Sql语句

1、封装执行函数，入参：sql语句和参数数组

public int UpdateRecord(string sql, params OracleParameter[] parameters)
{
     int iResult = 0;
     using (var conn = new OracleConnection(TeJianConn))
     {
           conn.Open();
           OracleCommand command = conn.CreateCommand();
           command.CommandType = CommandType.Text;
           command.CommandText = sql;
           command.Parameters.AddRange(parameters);
           iResult = Convert.ToInt32(command.ExecuteNonQuery());
           conn.Close();
     }
     return iResult;
}

2、定义参数，调用方法，返回执行行数

    // 根据集成平台出参回写记录
    OracleParameter SEND_TIME = new OracleParameter(":SEND_TIME", OracleDbType.Date);
    OracleParameter SEND_FLAG = new OracleParameter(":SEND_FLAG", OracleDbType.Varchar2);
    OracleParameter SEND_MSG = new OracleParameter(":SEND_MSG", OracleDbType.Clob);
    OracleParameter RESPON_MSG = new OracleParameter(":RESPON_MSG", OracleDbType.Clob);
    OracleParameter REPORT_NO = new OracleParameter(":REPORT_NO", OracleDbType.Varchar2);
    SEND_TIME.Value = DateTime.Now;
    SEND_FLAG.Value = "2";
    SEND_MSG.Value = xmlString.ToString();
    RESPON_MSG.Value = "待调试";
    REPORT_NO.Value = dr["REPORTNO"].ToString();
    int iUpdateResult = UpdateRecord("UPDATE TEJIAN.PDFUPLOADTOESB SET SEND_TIME =:SEND_TIME,SEND_FLAG =:SEND_FLAG,SEND_MSG =:SEND_MSG,RESPON_MSG =:RESPON_MSG WHERE REPORT_NO =:REPORT_NO",
                                 SEND_TIME, SEND_FLAG, SEND_MSG, RESPON_MSG, REPORT_NO);

 

注意：这里有个坑，参数类型要和表字段类型保持一致，比如如果是Date类型，那一定也要定义成Date，并且不要在sql语句中使用TO_DATE函数，严格使用对应的类型，要不然会出现各种问题。