[Mono-list] Automatic Binding of Variables to Parameters in ADO.NET

Daniel Morgan danmorg@sc.rr.com
Mon, 27 Jan 2003 07:52:19 -0500


How could I automatically bind variables as paramters in SQL, execute the
SQL, and get the results automatically.  Would this involve creating new
attributes to handle this.  I know Glade# uses attributes to bind variables.

Let's say we have a database table SOMETABLE with the following data:

==== =========== =================== ======== ======
5    152.32      2002-12-31 12:34:56 False    9
6    36.45       2001-01-23 05:12:23 True     8

Here is a struct that will contain the returned values.

public struct MyStruct
  int someNumber;
  double someMonetaryValue;
  DateTime someDataTime;
  bool someBoolean;

Here is the sample code that demonstrates what I am asking.

public MyStruct GetData (IDbConnection dbcon, int someValue)
	MyStruct mystruct;

	string sql =
         "SELECT aNum, aMoneyValue, " +
         "       aDateTime, aBoolean " +
         "FROM sometable " +
         "WHERE :someValue " +
         "INTO mystruct.someNumber, mystruct.someMonetaryValue, " +
         "     mystruct.someDateTime, mystruct.someBoolean";

     SomeMagicalClass magic = new SomeMagicalClass(dbcon, sql);

     return mystruct;

If I call GetData() with someValue set to 9, I should get a MyStruct struct
has the following resuls:

MyStrcut mystruct = GetData(dbcon, 9);

// expected results
mystruct.someNumber = 5
mystruct.someMonetaryValue = 152.32
mystruct.someDateTime = "2002-12-31 12:34:56"
mystruct.someBoolean = false

This is what I am interested in having.  Now, how do I get this?  I'm sure I
would need to use reflection heavily and attributes. There would be parsing
of SQL for parameters, create parameters based on the parameters in the SQL,
update the parameters with information from variables currently in scope
that match the name of the parameter, set the value of any input or
input/output parameters from the variable that have been bounded earlier,
execute the SQL, set the values of any return, output, or input/output
variables based on the results in the parameters.

Any ideas?

If you ever used RAD programming languages like Delphi, Centura/Gupta SQL
Windows, PowerBuilder, or Visual Basic, you would understand how powerful
this can be for database applications.

Thanks Daniel