How do I use prepared statements with an OleDbDataAdapter?

Question

I'm writing an API that will query a database given certain parameters. Some of those parameters will be supplied by the calling app (which should be trustworthy), but some of them will be supplied from user input.

If I were getting a small number of results I would normally use an OleDbCommand and use prepared statements via the Parameters field, as seen here

string sql = "Select * from [Table1] where Empid = ?";
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbParameter tableParam = new OleDbParameter("@fieldvalue", value);
cmd.Parameters.Add(tableParam);
cmd.Prepare();

However, since I will be getting potentially many results (possibly the entire contents of a table), I want to use an OleDbDataAdapter to fill a DataTable to make handling and returning the data more convenient:

DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter(sql, connstring);
da.Fill(dt);

The problem is that I don't know how to use prepared statements with an OleDbDataAdapter. How do I do that, or is there another way to guarantee database security when using an OleDbDataAdapter?

The DataAdapter can take a Command object as a parameter. So `... new OleDbDataAdapter(cmd);` — LarsTech, Sep 30 '20 at 22:08

score 4 · Accepted Answer · answered Sep 30 '20 at 22:27

The OleDbDataAdapter exposes the SelectCommand property (an OleDbCommand) built by the adapter with your query text. You can use it to 'prepare' the command used by the OleDbDataAdapter supplying the parameter required by the command text.

DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter(sql, connstring);
da.SelectCommand.Parameters.Add("@fieldvalue", OleDbType.Integer).Value = value;
da.Fill(dt);

How do I use prepared statements with an OleDbDataAdapter?

1 Answers1