[Mono-list] Automatic Binding of Variables to Parameters in ADO.NET
Piers Haken
piersh@friskit.com
Mon, 27 Jan 2003 05:45:35 -0800
This is a multi-part message in MIME format.
------_=_NextPart_001_01C2C60A.5E4B21F0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Microsoft ships a tool called xsd.exe that generates type-safe DataSets
that do basically this. You give it an XML Schma for your table and it
generates a source file for a class that drives from DataSet but which
has typesafe accessors and events. It might be a little heavy-handed for
what you want, though.
Piers.
> -----Original Message-----
> From: Daniel Morgan [mailto:danmorg@sc.rr.com]=20
> Sent: Monday, January 27, 2003 4:52 AM
> To: Mono-List
> Subject: [Mono-list] Automatic Binding of Variables to=20
> Parameters in ADO.NET
>=20
>=20
> Hello,
>=20
> How could I automatically bind variables as paramters in SQL,=20
> execute the SQL, and get the results automatically. Would=20
> this involve creating new attributes to handle this. I know=20
> Glade# uses attributes to bind variables.
>=20
> Let's say we have a database table SOMETABLE with the following data:
>=20
> ANUM AMONEYVALUE ADATETIME ABOOLEAN AVALUE
> =3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=3D
> 5 152.32 2002-12-31 12:34:56 False 9
> 6 36.45 2001-01-23 05:12:23 True 8
>=20
> Here is a struct that will contain the returned values.
>=20
> public struct MyStruct
> {
> int someNumber;
> double someMonetaryValue;
> DateTime someDataTime;
> bool someBoolean;
> }
>=20
> Here is the sample code that demonstrates what I am asking.
>=20
> [SomeMagicMethodParameterDataBind("someValue"]
> public MyStruct GetData (IDbConnection dbcon, int someValue)
> {
> [SomeMagicDataBind("mystruct"]
> MyStruct mystruct;
>=20
> string sql =3D
> "SELECT aNum, aMoneyValue, " +
> " aDateTime, aBoolean " +
> "FROM sometable " +
> "WHERE :someValue " +
> "INTO mystruct.someNumber, mystruct.someMonetaryValue, " +
> " mystruct.someDateTime, mystruct.someBoolean";
>=20
> SomeMagicalClass magic =3D new SomeMagicalClass(dbcon, sql);
> magic.ExecuteSQL();
>=20
> return mystruct;
> }
>=20
> If I call GetData() with someValue set to 9, I should get a=20
> MyStruct struct that has the following resuls:
>=20
> MyStrcut mystruct =3D GetData(dbcon, 9);
>=20
> // expected results
> mystruct.someNumber =3D 5
> mystruct.someMonetaryValue =3D 152.32
> mystruct.someDateTime =3D "2002-12-31 12:34:56"=20
> mystruct.someBoolean =3D false
>=20
> This is what I am interested in having. Now, how do I get=20
> this? I'm sure I would need to use reflection heavily and=20
> attributes. There would be parsing of SQL for parameters,=20
> create parameters based on the parameters in the SQL, update=20
> the parameters with information from variables currently in=20
> scope that match the name of the parameter, set the value of=20
> any input or input/output parameters from the variable that=20
> have been bounded earlier, execute the SQL, set the values of=20
> any return, output, or input/output variables based on the=20
> results in the parameters.
>=20
> Any ideas?
>=20
> If you ever used RAD programming languages like Delphi,=20
> Centura/Gupta SQL Windows, PowerBuilder, or Visual Basic, you=20
> would understand how powerful this can be for database applications.
>=20
> Thanks Daniel
>=20
> _______________________________________________
> Mono-list maillist - Mono-list@lists.ximian.com=20
> http://lists.ximian.com/mailman/listinfo/mono-list
>=20
------_=_NextPart_001_01C2C60A.5E4B21F0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.0.4417.0">
<TITLE>RE: [Mono-list] Automatic Binding of Variables to Parameters in =
ADO.NET</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=3D2>Microsoft ships a tool called xsd.exe that generates =
type-safe DataSets that do basically this. You give it an XML Schma for =
your table and it generates a source file for a class that drives from =
DataSet but which has typesafe accessors and events. It might be a =
little heavy-handed for what you want, though.</FONT></P>
<P><FONT SIZE=3D2>Piers.</FONT>
</P>
<P><FONT SIZE=3D2>> -----Original Message-----</FONT>
<BR><FONT SIZE=3D2>> From: Daniel Morgan [<A =
HREF=3D"mailto:danmorg@sc.rr.com">mailto:danmorg@sc.rr.com</A>] </FONT>
<BR><FONT SIZE=3D2>> Sent: Monday, January 27, 2003 4:52 AM</FONT>
<BR><FONT SIZE=3D2>> To: Mono-List</FONT>
<BR><FONT SIZE=3D2>> Subject: [Mono-list] Automatic Binding of =
Variables to </FONT>
<BR><FONT SIZE=3D2>> Parameters in ADO.NET</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> Hello,</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> How could I automatically bind variables as =
paramters in SQL, </FONT>
<BR><FONT SIZE=3D2>> execute the SQL, and get the results =
automatically. Would </FONT>
<BR><FONT SIZE=3D2>> this involve creating new attributes to handle =
this. I know </FONT>
<BR><FONT SIZE=3D2>> Glade# uses attributes to bind variables.</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> Let's say we have a database table SOMETABLE =
with the following data:</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> ANUM AMONEYVALUE =
ADATETIME =
ABOOLEAN AVALUE</FONT>
<BR><FONT SIZE=3D2>> =3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=3D</FONT>
<BR><FONT SIZE=3D2>> 5 =
152.32 2002-12-31 12:34:56 =
False 9</FONT>
<BR><FONT SIZE=3D2>> 6 =
36.45 2001-01-23 05:12:23 =
True 8</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> Here is a struct that will contain the returned =
values.</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> public struct MyStruct</FONT>
<BR><FONT SIZE=3D2>> {</FONT>
<BR><FONT SIZE=3D2>> int someNumber;</FONT>
<BR><FONT SIZE=3D2>> double someMonetaryValue;</FONT>
<BR><FONT SIZE=3D2>> DateTime someDataTime;</FONT>
<BR><FONT SIZE=3D2>> bool someBoolean;</FONT>
<BR><FONT SIZE=3D2>> }</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> Here is the sample code that demonstrates what I =
am asking.</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> =
[SomeMagicMethodParameterDataBind("someValue"]</FONT>
<BR><FONT SIZE=3D2>> public MyStruct GetData (IDbConnection dbcon, =
int someValue)</FONT>
<BR><FONT SIZE=3D2>> {</FONT>
<BR><FONT SIZE=3D2>> =
[SomeMagicDataBind("mystruct"]</FONT>
<BR><FONT SIZE=3D2>> MyStruct =
mystruct;</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> string sql =
=3D</FONT>
<BR><FONT =
SIZE=3D2>> =
"SELECT aNum, aMoneyValue, " +</FONT>
<BR><FONT =
SIZE=3D2>> =
" aDateTime, aBoolean " =
+</FONT>
<BR><FONT =
SIZE=3D2>> =
"FROM sometable " +</FONT>
<BR><FONT =
SIZE=3D2>> =
"WHERE :someValue " +</FONT>
<BR><FONT =
SIZE=3D2>> =
"INTO mystruct.someNumber, mystruct.someMonetaryValue, " =
+</FONT>
<BR><FONT =
SIZE=3D2>> =
" mystruct.someDateTime, =
mystruct.someBoolean";</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> SomeMagicalClass =
magic =3D new SomeMagicalClass(dbcon, sql);</FONT>
<BR><FONT SIZE=3D2>> =
magic.ExecuteSQL();</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> return =
mystruct;</FONT>
<BR><FONT SIZE=3D2>> }</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> If I call GetData() with someValue set to 9, I =
should get a </FONT>
<BR><FONT SIZE=3D2>> MyStruct struct that has the following =
resuls:</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> MyStrcut mystruct =3D GetData(dbcon, 9);</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> // expected results</FONT>
<BR><FONT SIZE=3D2>> mystruct.someNumber =3D 5</FONT>
<BR><FONT SIZE=3D2>> mystruct.someMonetaryValue =3D 152.32</FONT>
<BR><FONT SIZE=3D2>> mystruct.someDateTime =3D "2002-12-31 =
12:34:56" </FONT>
<BR><FONT SIZE=3D2>> mystruct.someBoolean =3D false</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> This is what I am interested in having. =
Now, how do I get </FONT>
<BR><FONT SIZE=3D2>> this? I'm sure I would need to use =
reflection heavily and </FONT>
<BR><FONT SIZE=3D2>> attributes. There would be parsing of SQL for =
parameters, </FONT>
<BR><FONT SIZE=3D2>> create parameters based on the parameters in the =
SQL, update </FONT>
<BR><FONT SIZE=3D2>> the parameters with information from variables =
currently in </FONT>
<BR><FONT SIZE=3D2>> scope that match the name of the parameter, set =
the value of </FONT>
<BR><FONT SIZE=3D2>> any input or input/output parameters from the =
variable that </FONT>
<BR><FONT SIZE=3D2>> have been bounded earlier, execute the SQL, set =
the values of </FONT>
<BR><FONT SIZE=3D2>> any return, output, or input/output variables =
based on the </FONT>
<BR><FONT SIZE=3D2>> results in the parameters.</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> Any ideas?</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> If you ever used RAD programming languages like =
Delphi, </FONT>
<BR><FONT SIZE=3D2>> Centura/Gupta SQL Windows, PowerBuilder, or =
Visual Basic, you </FONT>
<BR><FONT SIZE=3D2>> would understand how powerful this can be for =
database applications.</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> Thanks Daniel</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> =
_______________________________________________</FONT>
<BR><FONT SIZE=3D2>> Mono-list maillist - =
Mono-list@lists.ximian.com </FONT>
<BR><FONT SIZE=3D2>> <A =
HREF=3D"http://lists.ximian.com/mailman/listinfo/mono-list">http://lists.=
ximian.com/mailman/listinfo/mono-list</A></FONT>
<BR><FONT SIZE=3D2>> </FONT>
</P>
</BODY>
</HTML>
------_=_NextPart_001_01C2C60A.5E4B21F0--