I'm trying to read table data from ORACLE with Parameter binding in c#. While executing the dataAdapter I'm getting ORA-12571 exception.
Below is my code for reading multiple data at the same time.
public DataTable SelectFromServer(string qualifiedDBName, DataTable dataTable) {
try
{
if (this.Con.State == ConnectionState.Closed)
{
this.OpenConnection();
}
DataTable resultTable = new DataTable();
dataTable.TableName = qualifiedDBName;
DbProviderFactory factory = DbProviderFactories.GetFactory(this.Con);
using (DbCommand command = factory.CreateCommand())
{
command.Connection = this.Con;
command.CommandText = this.GenerateSqlToSelect(factory,command,dataTable);
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
adapter.Fill(resultTable);
return resultTable;
}
}
catch (Exception exc)
{
throw exc;
}
}
Below is the method where I used to generate Select Query
private string GenerateSqlToSelect(DbProviderFactory factory, DbCommand command, DataTable table)
{
//var values1 = new List<string>();
var SelectQuery = new StringBuilder();
var data = table.ToArray();
var syntax = new OracleSyntax();
command.GetType().GetProperty("ArrayBindCount").SetValue(command, table.Rows.Count, null);
for (var i = 0; i < table.Columns.Count; i++)
{
var names = new StringBuilder();
var values = new StringBuilder();
var column = table.Columns[i];
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = column.ColumnName;
parameter.Direction = ParameterDirection.Input;
parameter.DbType = column.DataType.GetDbType();
parameter.Value = data[i];
parameter.ArrayBindSize = GetDataLength(data[i]);
if (SelectQuery.Length > 0)
{
SelectQuery.Append(" and ");
}
names.AppendFormat("{0}", column.ColumnName);
values.AppendFormat("{0}{1}", syntax.ParameterPrefix, column.ColumnName);
SelectQuery.AppendFormat("{0} = {1}", names, values);
command.Parameters.Add(parameter);
}
string operationString = "SELECT * FROM";
string sqlQuery = string.Format("{0} {1} WHERE {2}", operationString, this.FormatByQuote(syntax,table.TableName), SelectQuery);
return sqlQuery;
}
private int[] GetDataLength(object[] objs)
{
List<int> dataLengthIterator = new List<int>();
foreach (object obj in objs)
{
dataLengthIterator.Add(obj.ToString().Length);
}
return dataLengthIterator.ToArray();
}
this.FormatByQuote() method is nothing but getting a quoted TableName.
Output of GenerateSqlToSelect() method is
SELECT * FROM "CUSTOMER_MASTER" WHERE USER_ID= :USER_ID and LINE_NUMBER = :LINE_NUMBER
The Query is generating based upon the DataTable input
Tried multiple workarounds but could not get the reason why the exception is occurring.