Parametrizing an DbCommand object
A DbCommand object can be constructed in a «parameterized» way. This allows to execute the same DbCommand multiple times, each time with a different set of parameter-values.
The benefits of parameterized
DbCommand objects are:
- it helps to prevent SQL injection
- a cached SQL execution plan can be reused
The format of parameter-placeholders is dependent on the Data provider:
ExecuteScalar()
ExecuteScalar() provides a convenient way to query a value from a single-row, single-column record set, typically an
aggregate function.
The following example counts the record in a table. For simplicity's sake, I have chosen a
CSV file for the table and an
OLE DB provider to query from it.
using System;
using System.Data.Common;
using System.Data.OleDb;
using System.IO;
class Prg {
static void Main () {
string csvFilePath = Directory.GetCurrentDirectory();
string provider =
// "Microsoft.Jet.OLEDB.4.0"
"Microsoft.ACE.OLEDB.12.0";
string connectionString =
$"Provider={provider};" +
$"Data Source={csvFilePath};" +
$"Extended Properties='text';";
using OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
DbCommand command = new OleDbCommand("select count(*) from data.csv", connection);
//
// Of course, the constructor returns an OleDbCommand object. Thus, the
// following line would probably be more correct. But because this
// program serves at demonstrating the basic functionality of ExecuteScalar() ,
// I have not done sol
//
// OleDbCommand command = new OleDbCommand("select id, num, txt from data.csv", connection);
Console.WriteLine("The csv file contains {0} records", (Int32) command.ExecuteScalar());
}
}
For the record: the CSV with which I tested the program is:
num,txt
4,four
2,two
9,nine