In my WinForms project written in C# I'm connecting to a PostgreSQL database using the Npgsql framework.
I'm populating a couple of DGVs with a DataTable from the PG database. I'm also trying to implement a search feature that, when initiated by the user, searches the DGV's DataTable for the search string and then rebuilds the DGV based on those results. As the process searches the DataTable, it's building a list of record IDs (ex, "1234,2345,3456") that will be passed to the db query to repopulate the DGV with just the records that match the user's search value.
Here's my search function that builds the list of IDs and the db query:
private void btnSearch_Click(object sender, EventArgs e)
{
string searchText = txtSearchCatsGrid.Text;
string idToAddToList = "";
string tempIdList = "";
string idListForQuery = "";
DataTable dt = ((DataTable)dgvCategories.DataSource);
string cellValue;
int rowIndex;
int columnIndex;
for (rowIndex = 0; rowIndex <= dt.Rows.Count - 1; rowIndex++)
{
for (columnIndex = 0; columnIndex <= dt.Columns.Count - 1; columnIndex++)
{
cellValue = dt.Rows[rowIndex][columnIndex].ToString();
if (searchText == cellValue)
{
// Set corresponding dgvCategories cell's background color to yellow
dgvCategories[columnIndex, rowIndex].Style.BackColor = Color.Yellow;
idToAddToList = dt.Rows[rowIndex][0].ToString();
}
}
if (!string.IsNullOrEmpty(idToAddToList) && !tempIdList.Contains(idToAddToList)) tempIdList += idToAddToList + ",";
}
if (!string.IsNullOrEmpty(tempIdList))
{
// Remove the trailing comma that was added the last time an id was added to the list
idListForQuery = tempIdList.Remove(tempIdList.Length - 1, 1);
}
Console.WriteLine("idListForQuery: " + idListForQuery);
// Refresh dgvCategories with just rows from search, using idListForQuery
string companyFilter = cboSelectCompany.Text;
string categoryFilter = cboSelectCategory.Text;
db categoriesData = new db();
if (categoryFilter == "All Categories")
{
string catsQuery = "SELECT id, category, source_company, old_value, old_desc, new_value, new_desc, reference1, reference2 " +
"FROM masterfiles.xref"+
" WHERE company_name = @company" +
" AND id IN (@idList)" +
//" AND id IN (SELECT UNNEST(@idList))" +
" ORDER BY category, old_value, source_company";
this.dtCategories = categoriesData.GetDgvData(catsQuery, companyFilter, categoryFilter, idListForQuery);
}
else
{
string catsQuery = "SELECT id, category, source_company, old_value, old_desc, new_value, new_desc, reference1, reference2 " +
"FROM masterfiles.xref" +
" WHERE company_name = @company" +
" AND category = @category" +
" AND id IN (@idList)" +
//" AND id IN (SELECT UNNEST(@idList))" +
" ORDER BY old_value, source_company";
this.dtCategories = categoriesData.GetDgvData(catsQuery, companyFilter, categoryFilter, idListForQuery);
}
dgvCategories.DataSource = this.dtCategories;
if (dtCategories.Rows.Count == 0)
{
return;
}
else
{
dgvCategories.Columns[0].Visible = false;
dgvCategories.Rows[0].Cells[0].Selected = false;
}
}
Then in my db class, the GetDgvData() function looks like this:
public DataTable GetDgvData(string selectQuery, string companyFilter, string categoryFilter, string idFilter)
{
using (NpgsqlConnection conn = new NpgsqlConnection(connString))
using (NpgsqlCommand cmd = new NpgsqlCommand(selectQuery, conn))
{
cmd.Parameters.Add(new NpgsqlParameter("company", companyFilter));
if (!string.IsNullOrEmpty(idFilter)) cmd.Parameters.Add(new NpgsqlParameter("idList", idFilter);
//if (!string.IsNullOrEmpty(idFilter)) cmd.Parameters.Add("idList", NpgsqlDbType.Array | NpgsqlDbType.Integer).Value = idFilter;
//if (!string.IsNullOrEmpty(idFilter)) cmd.Parameters.Add(new NpgsqlParameter("idList", "ARRAY[" + idFilter + "]"));
if (categoryFilter != "All Categories") cmd.Parameters.Add(new NpgsqlParameter("category", categoryFilter));
conn.Open();
DataSet ds = new DataSet();
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
da.Fill(ds);
}
return ds.Tables[0];
}
}
I'm having trouble with using that list of IDs as a parameter in the query.
You'll notice that I have several lines commented out in both code blocks. Those are various attempts at passing the list, all of which generate an error at the line da.Fill(ds);
When I just use the uncommented line in the query building code, AND id IN (@idList) and the "regular" method of adding a parameter, if (!string.IsNullOrEmpty(idFilter)) cmd.Parameters.Add(new NpgsqlParameter("idList", idFilter);, also ucommented in the GetDgvData() function, the error at da.Fill(ds); is an NpgsqlPostgresException, "operator does not exist: integer = text'".
After some research I tried the UNNEST() function in the query code and left the parameter code. When I try that, I get an Exception saying that function doesn't exist.
After some more research, I found a code example that casts the Array db type to an Integer db Type, which is the first line of commented code in the parameters section in the GetDgvData() function. When I tried that, the Exception thrown was, "System.InvalidCastException: 'Can't write type System.String as an array of System.Int32'".
After yet more research, I tried the third commented line in the GetDgvData() function, that surrounds the parameter with "Array[]". The Exception I get for that is, "operator does not exist: integer = text'". I tried that with and without the UNNEST() function in the query. Without UNNEST() I get a Npgsql.PostgresException, "operator does not exist: integer = text'". Using UNNEST() I get the PostgresException, "function unnest(text) does not exist'".
I also built the ids as a List<> and tried passing it to GetDgvData() but I'm not passing it correctly or something because I'm getting a null reference exception in GetDgvData() when it gets to the line where I try to do something with the List<> - including just trying to look at the contents in the Console.
So I really don't know what else to try from here.
Others have successfully used the above code in their respective solutions, so I can only assume I'm missing something, but what it could be, I have NO idea.