Random Dev Notes

June 2, 2013

Using Named Parameters when Accessing Visual FoxPro Data from .Net

Filed under: .Net Development — Tags: — Tom Brothers @ 9:07 am

One thing that has always annoyed me is the way passing parameters in a sql statement work when working with Visual FoxPro data from .Net using OleDbCommand. 

Here are a few things in particular that annoy me:

  • Use of question marks place holders instead of named parameters.
  • Need to add parameters to the command in the order that they appear in the sql statement.
  • Need to add a parameter for each question make place holder even if a parameter has already been added with the same value.

Problem

In this example of the problem, I have a simple query that spans multiple tables looking for records that match a name or id.  Ideally such a query would only require two parameters to be added to the command.  But as you can see there were 12 parameters added to satisfy the ordinal place holder requirements.  Admittedly, adding multiple parameters with the same values isn’t a huge problem nor is making sure the question marks line up with the correct parameter value.  But I do find that dealing with this annoying mundane task causes a pause in my development as I try to work things out mentally. 

   1:  using (var connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {
   2:      using (var command = connection.CreateCommand()) {
   3:   
   4:          command.CommandText = @"
   5:  SELECT CAST('Supplier' as v(254)) Type, CompanyName Name 
   6:      FROM Suppliers WHERE UPPER(ALLTRIM(CompanyName)) like ? OR SupplierId = ?
   7:  UNION SELECT CAST('Shipper' as v(254)), CompanyName 
   8:      FROM Shippers WHERE UPPER(ALLTRIM(CompanyName)) like ? OR ShipperId = ?
   9:  UNION SELECT CAST('Customer' as v(254)), CompanyName 
  10:      FROM Customers WHERE UPPER(ALLTRIM(CompanyName)) like ?
  11:  UNION SELECT CAST('Product' as v(254)), ProductName 
  12:      FROM Products WHERE UPPER(ALLTRIM(ProductName)) like ? OR ProductId = ?
  13:  UNION SELECT CAST('Category' as v(254)), CategoryName 
  14:      FROM Categories WHERE UPPER(ALLTRIM(CategoryName)) like ? OR CategoryId = ?
  15:  UNION SELECT CAST('Employee' as v(254)), ALLTRIM(FirstName) + ' ' + ALLTRIM(LastName) 
  16:      FROM Employees WHERE UPPER(ALLTRIM(FirstName)) like ? OR UPPER(ALLTRIM(LastName)) like ? OR EmployeeId = ?
  17:  Order by 2";
  18:   
  19:          command.Parameters.AddWithValue("@Name1", "%AB%");
  20:          command.Parameters.AddWithValue("@Id1", 1);
  21:          command.Parameters.AddWithValue("@Name2", "%AB%");
  22:          command.Parameters.AddWithValue("@Id2", 1);
  23:          command.Parameters.AddWithValue("@Name3", "%AB%");
  24:          command.Parameters.AddWithValue("@Name4", "%AB%");
  25:          command.Parameters.AddWithValue("@Id3", 1);
  26:          command.Parameters.AddWithValue("@Name5", "%AB%");
  27:          command.Parameters.AddWithValue("@Id4", 1);
  28:          command.Parameters.AddWithValue("@Name6", "%AB%");
  29:          command.Parameters.AddWithValue("@Name7", "%AB%");
  30:          command.Parameters.AddWithValue("@Id5", 1);
  31:   
  32:          var dataAdapter = new OleDbDataAdapter(command);
  33:          var dataTable = new DataTable();
  34:   
  35:          dataAdapter.Fill(dataTable);
  36:      }
  37:  }

Solution

Switching from using OleDbCommand to using VfpCommand (found in VfpClient) will allow you to use named parameters.

   1:  using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {
   2:      using (var command = connection.CreateCommand()) {
   3:   
   4:          command.CommandText = @"
   5:  SELECT CAST('Supplier' as v(254)) Type, CompanyName Name 
   6:      FROM Suppliers WHERE UPPER(ALLTRIM(CompanyName)) like @Name OR SupplierId = @Id
   7:  UNION SELECT CAST('Shipper' as v(254)), CompanyName 
   8:      FROM Shippers WHERE UPPER(ALLTRIM(CompanyName)) like @Name OR ShipperId = @Id
   9:  UNION SELECT CAST('Customer' as v(254)), CompanyName 
  10:      FROM Customers WHERE UPPER(ALLTRIM(CompanyName)) like @Name
  11:  UNION SELECT CAST('Product' as v(254)), ProductName 
  12:      FROM Products WHERE UPPER(ALLTRIM(ProductName)) like @Name OR ProductId = @Id
  13:  UNION SELECT CAST('Category' as v(254)), CategoryName 
  14:      FROM Categories WHERE UPPER(ALLTRIM(CategoryName)) like @Name OR CategoryId = @Id
  15:  UNION SELECT CAST('Employee' as v(254)), ALLTRIM(FirstName) + ' ' + ALLTRIM(LastName) 
  16:      FROM Employees WHERE UPPER(ALLTRIM(FirstName)) like @Name OR UPPER(ALLTRIM(LastName)) like @Name OR EmployeeId = @Id
  17:  Order by 2
  18:  ";
  19:   
  20:          command.Parameters.AddWithValue("@Name", "%AB%");
  21:          command.Parameters.AddWithValue("@Id", 1);
  22:   
  23:          var dataAdapter = new VfpDataAdapter(command);
  24:          var dataTable = new DataTable();
  25:   
  26:          dataAdapter.Fill(dataTable);
  27:      }
  28:  }

* I personally find the example using VfpCommand much easier to understand and work with compared to the example using OleDbCommand.


* Example code can be found in Visual FoxPro .Net Examples.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: