Select * From Database and Search Value in List OR Dictionary

Question

I have a bit of C# code in which am querying an Oracle database table (let's call it T1) and also a SQL Server database table (let's call it T2), and am placing the data from the T1 into a dictionary and also a list (I can use either). I am only adding the deptRef from T1 into the dictionary/list.

What I am trying to achieve is checking if the data from T2 exists in the dictionary OR list in the SELECT statement I am using e.g.

SELECT deptRef, deptName 
FROM T2 
WHERE deptRef IN List

So far I have this:

string strDeptName = "";
string strDeptRef = "";

string connstring = Convert.ToString(Dts.Variables["$Project::ORACLEConnectionString"].Value);
var sql = "SELECT d_reference AS DeptRef, d_name AS DeptName FROM T1 ORDER BY 1 ASC";

OleDbConnection t1con;
t1con = new OleDbConnection(connstring);

DataSet _ds = new DataSet();
DataTable _dt = new DataTable();

t1con.Open();
OleDbCommand cmd = new OleDbCommand(sql, t1con);
cmd.Connection = t1con;
cmd.CommandType = CommandType.Text;
        
OleDbDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
    strDeptRef = dr.GetString(0).ToString();
    strDeptName = dr.GetString(1);
    
    // ADD TO LIST
    List<string> T1DeptsList = new List<string>();

    foreach (string dept in T1DeptsList)
    {
        T1DeptsList.Add(dept);
    }
            
    // Add to DICTIONARY
    Dictionary<string, int> T1DeptsDict = new Dictionary<string, int>();

    int ii = 0;
    T1DeptsDict.Add(strDeptRef, ii);
    ii++;
}
        
// This is the second query
var sqlcon = Convert.ToString(Dts.Variables["$Project::SQLConnectionString"].Value);
         
OleDbConnection mwconnstring;
sqlconnstring = new OleDbConnection(sqlcon);
sqlconnstring.Open();

var mwsql = @"SELECT d_name FROM T2 WHERE d_reference IN T1DeptsList or T1DeptsDict....."; //this is the part where I'd like to query the List/Dictionary

OleDbCommand cmd2 = new OleDbCommand(mwsql, sqlconnstring);
cmd2.Connection = sqlconnstring;
cmd2.CommandText = mwsql;
cmd2.CommandType = System.Data.CommandType.Text;

The queries both work in that they return the data I expect but the part I'm having an issue with is referencing either the list OR the dictionary values. Either the list or the dictionary - it doesn't have to be both but just included them in case anyone advised which of the two is the more preferred.

Any help would be greatly appreciated.

Answer 1

You cannot easily pass a list from C# to Oracle (since, for some unknown reason, C# only supports Oracle PL/SQL associative arrays, which cannot be used in SQL statements, and does not support passing Oracle SQL collections, which can be used in SQL statements). Even if you could do that you probably don't want to as passing collections is a relatively expensive operation. While you could use string concatenation to build the IN expression-list, that is considered bad practice as it leaves your statement open to SQL injection attacks.

It will be simpler (and more efficient) to use an OUTER JOIN and query both T1 and T2 at the same time in a single SQL statement and then populate the dictionary later:

SELECT t1.d_reference as DeptRef,
       t1.d_name as DeptName,
       t2.d_name AS DeptName2
FROM   T1
       LEFT OUTER JOIN T2
       ON t1.d_reference = t2.d_reference
ORDER BY t1.d_reference ASC

If your data is on different servers then you can look at setting up a database link between them.

Then in C#:

• For each row:
  • If the DeptRef is different to the previous row's DeptRef then add it to the list and the dictionary.
  • If the DeptName2 is not NULL then do whatever processing you need to do on it.

Note: You have not described the logic that you want to implement but you should probably create the list and dictionary once outside of the loop and then append to it inside the loop rather than creating a new list and dictionary with each loop that overwrites the previous values.