How to use query parameters to access a datatable?

Dean

I have a datatable called "PostCodes", and I'm trying to retrieve a row that matches a name for the suburb that is currently displayed in a combo box. The code is very simple:

DataRow[] result = PostCodes.Select("Locality = '" + cbSuburb.Text + "'");
foreach (DataRow row in result)
        {
                tbPostCode.Text = row[0].ToString();
                tbState.Text = row[2].ToString();
        }

However even though this works quite well, I want to use query parameters to get around the age-old problem of quotation marks in the Locality field.

Is there a simple way to do this? I can't seem to find anything relating to using query parameters on a datatable.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hemant Srivastava14y ago
I don't think, you could get rid of of those quotation marks in the Filter expression part of the DataTable.Select() method.

As MSDN Says,
--------------------------------------------------------------
public DataRow[] Select(string filterExpression)

To create the filterExpression argument, use the same rules that apply to the DataColumn class's Expression property value for creating filters

--------------------------------------------------------------

And In DataColumn class's Expression property, all the expressions are created with quotation marks. You may refer to:
http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

If it helps, Please make it accepted answer.

Thanks
Sandeep Singh Shekhawat14y ago
As per my understanding you should get DataTable which have desire result using parameterize query.

Here I am creating an common function which will return data Table according to parameter name and parameter value with query.

public static DataTable GetDataTableWithParameter(string Query, string ParamName,
                                                                        string ParamValue)
{
string myConnectionString = WebConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
         SqlConnection oSqlConnection = new SqlConnection(myConnectionString);
try
{
         SqlCommand cmd = new SqlCommand(Query, oSqlConnection);
         SqlParameter param1 = new SqlParameter();
         param1.ParameterName = ParamName;
         if (!string.IsNullOrEmpty(ParamValue))
                 {
                         param1.Value = ParamValue;
                         cmd.Parameters.Add(param1);
                 }
         SqlDataAdapter oSqlDataAdapter = new SqlDataAdapter(cmd);

        //DataTable which will be return
         DataTable DataTable_To_Fill = new DataTable();
         oSqlDataAdapter.Fill(DataTable_To_Fill);

         return DataTable_To_Fill;
}
finally
{
         oSqlConnection.Close();
}
}
Sudhakar Chaudhary14y ago
http://www.c-sharpcorner.com/UploadFile/0f68f2/querying-a-data-table-using-select-method-and-lambda-express/