CODEWITHSUNDEEP
c#datatabledatasetparameter-passingnpgsql
marky
marky

Reputation: 5068

C# Parameterized query - parameters not being replaced with set value

I'm passing a query and parameter from a WinForm to a database class. The

The code on the Form looks like this:

string selectedComp = "CPSI";
string catsQuery = "SELECT id, category, old_value, old_desc, new_value, new_desc, reference1, reference2 FROM masterfiles.xref WHERE company_name = '@company' ORDER BY category, old_value";

Db categoriesData = new Db();
dgvCategories.DataSource = categoriesData.GetData(catsQuery, selectedComp);

And in my database class my code to populate the datatable/set is this:

public DataTable GetData(string selectQuery, string selectedComp)
{
    NpgsqlConnection conn = new NpgsqlConnection(connString);
    DataSet ds = new DataSet();

    NpgsqlCommand cmd = new NpgsqlCommand(selectQuery, conn);

    cmd.Parameters.Add(new NpgsqlParameter("@company", selectedComp));

    //cmd.Parameters.AddWithValue("@company", selectedComp);
    //cmd.Parameters.Add("@company", NpgsqlDbType.Text);
    //cmd.Parameters["@company"].Value = selectedComp;

    try
    {
        conn.Open();
        NpgsqlDataAdapter da = new NpgsqlDataAdapter(selectQuery, conn);
        conn.Close();

        da.Fill(ds);
        return ds.Tables[0];
    }
}

But putting a breakpoint at NpgsqlDataAdapter da = new NpgsqlDataAdapter(selectQuery, conn);, selecctQuery hasn't changed - the '@company' is still in the query.

What am I missing?

Upvotes: 0

Views: 322

Answers (1)

D Stanley
D Stanley

Reputation: 152626

The root problem is that you're passing the query to the data adapter instead of the command. Change

NpgsqlDataAdapter da = new NpgsqlDataAdapter(selectQuery, conn);

to 

NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);

I would also use using to dispose of all objects, and don't close the connection until the dataset is filled:

using(NpgsqlConnection conn = new NpgsqlConnection(connString))
using(NpgsqlCommand cmd = new NpgsqlCommand(selectQuery, conn))
{

    cmd.Parameters.Add(new NpgsqlParameter("company", selectedComp));

    conn.Open();
    using(NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
    {
        DataSet ds = new DataSet();
        da.Fill(ds);
    }
    conn.Close();

    return ds.Tables[0];
}

Upvotes: 3

Related Questions