[Mono-list] Automatic Binding of Variables to Parameters in ADO.NET
Piers Haken
piersh@friskit.com
Mon, 27 Jan 2003 12:58:47 -0800
This is a multi-part message in MIME format.
------_=_NextPart_001_01C2C646.E2A3BE6C
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I had a look at this briefly last night. It looks like the xsd.exe tool
does quite a bit more than I thought.
=20
from the link:
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/cptool=
s
/html/cpconxmlschemadefinitiontoolxsdexe.asp
<http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/cptoo=
l
s/html/cpconxmlschemadefinitiontoolxsdexe.asp>=20
=20
Xsd.exe performs the following operations:=20
XDR to XSD=20
Generates an XML schema from an XML-Data-Reduced schema file. XDR is an
early XML-based schema format.=20
XML to XSD=20
Generates an XML schema from an XML file.=20
XSD to DataSet=20
Generates common language runtime DataSet
<http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataDataS
etClassTopic.asp> classes from an XSD schema file. The generated
classes provide a rich object model for regular XML data.=20
XSD to Classes=20
Generates runtime classes from an XSD schema file. The generated classes
can be used in conjunction with System.XML.Serialization.XMLSerializer
<http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemXmlSerial
izationXmlSerializerClassTopic.asp> to read and write XML code that
follows the schema.=20
Classes to XSD=20
Generates an XML schema from a type or types in a runtime assembly file.
The generated schema defines the XML format used by
System.XML.Serialization.XMLSerializer.=20
Obviously the middle one is what we're talking about here.
=20
I don't think the tool should be too difficult to write in itself, it's
just a question of how complete are the required parts of the framework:
=20
1) XML Schema support
2) Metadata in DataSets
3) CodeDom code generation
4) System.Data.TypedDataSetGenerator - this is the main missing
component that does most of the work.
=20
Piers.
=20
-----Original Message-----
From: Daniel Morgan [mailto:danmorg@sc.rr.com]
Sent: Monday, January 27, 2003 5:33 AM
To: Piers Haken; Mono-List
Subject: RE: [Mono-list] Automatic Binding of Variables to Parameters in
ADO.NET
Maybe that's what we need then - for someone to start working on the
xsd.exe tool for creating type-safe DataSets.
=20
What would be involved in creating this tool?
=20
Any takers?
=20
-----Original Message-----
From: Piers Haken [mailto:piersh@friskit.com]
Sent: Monday, January 27, 2003 8:46 AM
To: Daniel Morgan; Mono-List
Subject: RE: [Mono-list] Automatic Binding of Variables to Parameters in
ADO.NET
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.=20
> -----Original Message-----=20
> From: Daniel Morgan [ mailto:danmorg@sc.rr.com
<mailto:danmorg@sc.rr.com> ]=20
> Sent: Monday, January 27, 2003 4:52 AM=20
> To: Mono-List=20
> Subject: [Mono-list] Automatic Binding of Variables to=20
> Parameters in ADO.NET=20
>=20
>=20
> Hello,=20
>=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
>=20
> Let's say we have a database table SOMETABLE with the following data:=20
>=20
> ANUM AMONEYVALUE ADATETIME ABOOLEAN AVALUE=20
> =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=20
> 5 152.32 2002-12-31 12:34:56 False 9=20
> 6 36.45 2001-01-23 05:12:23 True 8=20
>=20
> Here is a struct that will contain the returned values.=20
>=20
> public struct MyStruct=20
> {=20
> int someNumber;=20
> double someMonetaryValue;=20
> DateTime someDataTime;=20
> bool someBoolean;=20
> }=20
>=20
> Here is the sample code that demonstrates what I am asking.=20
>=20
> [SomeMagicMethodParameterDataBind("someValue"]=20
> public MyStruct GetData (IDbConnection dbcon, int someValue)=20
> {=20
> [SomeMagicDataBind("mystruct"]=20
> MyStruct mystruct;=20
>=20
> string sql =3D=20
> "SELECT aNum, aMoneyValue, " +=20
> " aDateTime, aBoolean " +=20
> "FROM sometable " +=20
> "WHERE :someValue " +=20
> "INTO mystruct.someNumber, mystruct.someMonetaryValue, " +=20
> " mystruct.someDateTime, mystruct.someBoolean";=20
>=20
> SomeMagicalClass magic =3D new SomeMagicalClass(dbcon, sql);=20
> magic.ExecuteSQL();=20
>=20
> return mystruct;=20
> }=20
>=20
> If I call GetData() with someValue set to 9, I should get a=20
> MyStruct struct that has the following resuls:=20
>=20
> MyStrcut mystruct =3D GetData(dbcon, 9);=20
>=20
> // expected results=20
> mystruct.someNumber =3D 5=20
> mystruct.someMonetaryValue =3D 152.32=20
> mystruct.someDateTime =3D "2002-12-31 12:34:56"=20
> mystruct.someBoolean =3D false=20
>=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
>=20
> Any ideas?=20
>=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
>=20
> Thanks Daniel=20
>=20
> _______________________________________________=20
> Mono-list maillist - Mono-list@lists.ximian.com=20
> http://lists.ximian.com/mailman/listinfo/mono-list
<http://lists.ximian.com/mailman/listinfo/mono-list> =20
>=20
------_=_NextPart_001_01C2C646.E2A3BE6C
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE>RE: [Mono-list] Automatic Binding of Variables to Parameters in =
ADO.NET</TITLE>
<META content=3D"MSHTML 6.00.2800.1126" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D390153420-27012003><FONT face=3DArial color=3D#800080 =
size=3D2>I had=20
a look at this briefly last night. It looks like the xsd.exe tool does =
quite a=20
bit more than I thought.</FONT></SPAN></DIV>
<DIV><SPAN class=3D390153420-27012003><FONT face=3DArial color=3D#800080 =
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D390153420-27012003><FONT face=3DArial color=3D#800080 =
size=3D2>from=20
the link:</FONT></SPAN></DIV>
<DIV><SPAN class=3D390153420-27012003><FONT face=3DArial color=3D#800080 =
size=3D2><A=20
href=3D"http://msdn.microsoft.com/library/default.asp?url=3D/library/en-u=
s/cptools/html/cpconxmlschemadefinitiontoolxsdexe.asp">http://msdn.micros=
oft.com/library/default.asp?url=3D/library/en-us/cptools/html/cpconxmlsch=
emadefinitiontoolxsdexe.asp</A></FONT></SPAN></DIV>
<DIV><SPAN class=3D390153420-27012003><FONT face=3DArial color=3D#800080 =
size=3D2></FONT></SPAN> </DIV><SPAN class=3D390153420-27012003>
<P>Xsd.exe performs the following operations:=20
<DL>
<DT>XDR to XSD=20
<DD>Generates an XML schema from an XML-Data-Reduced schema file. XDR =
is an=20
early XML-based schema format.=20
<DT>XML to XSD=20
<DD>Generates an XML schema from an XML file.=20
<DT>XSD to DataSet=20
<DD>Generates common language runtime <A=20
=
href=3D"http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDat=
aDataSetClassTopic.asp">DataSet</A>=20
classes from an XSD schema file. The generated classes provide a rich =
object=20
model for regular XML data.=20
<DT>XSD to Classes=20
<DD>Generates runtime classes from an XSD schema file. The generated =
classes=20
can be used in conjunction with <A=20
=
href=3D"http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemXml=
SerializationXmlSerializerClassTopic.asp">System.XML.Serialization.XMLSer=
ializer</A>=20
to read and write XML code that follows the schema.=20
<DT>Classes to XSD=20
<DD>Generates an XML schema from a type or types in a runtime assembly =
file.=20
The generated schema defines the XML format used by=20
<B>System.XML.Serialization.XMLSerializer</B>. </DD></DL>
<DIV><SPAN class=3D390153420-27012003><SPAN =
class=3D390153420-27012003><FONT=20
face=3DArial color=3D#800080 size=3D2>Obviously the middle one is what =
we're talking=20
about here.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=3D390153420-27012003><SPAN =
class=3D390153420-27012003><FONT=20
face=3DArial color=3D#800080 size=3D2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=3D390153420-27012003><SPAN =
class=3D390153420-27012003><FONT=20
face=3DArial color=3D#800080 size=3D2>I don't think the tool should be =
too difficult=20
to write in itself, it's just a question of how complete are the =
required parts=20
of the framework:</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=3D390153420-27012003><SPAN =
class=3D390153420-27012003><FONT=20
face=3DArial color=3D#800080 size=3D2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=3D390153420-27012003><SPAN =
class=3D390153420-27012003><FONT=20
face=3DArial color=3D#800080 size=3D2>1) XML Schema =
support</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=3D390153420-27012003><SPAN =
class=3D390153420-27012003><FONT=20
face=3DArial color=3D#800080 size=3D2>2) Metadata in=20
DataSets</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=3D390153420-27012003><SPAN =
class=3D390153420-27012003><FONT=20
face=3DArial color=3D#800080 size=3D2>3) CodeDom code=20
generation</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=3D390153420-27012003><SPAN =
class=3D390153420-27012003><FONT=20
face=3DArial color=3D#800080 size=3D2>4) =
System.Data.TypedDataSetGenerator - this is=20
the main missing component that does most of the=20
work.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=3D390153420-27012003><SPAN =
class=3D390153420-27012003><FONT=20
face=3DArial color=3D#800080 size=3D2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=3D390153420-27012003><SPAN =
class=3D390153420-27012003><FONT=20
face=3DArial color=3D#800080 =
size=3D2>Piers.</FONT></SPAN></SPAN></DIV></SPAN>
<DIV><SPAN class=3D390153420-27012003></SPAN><SPAN=20
class=3D390153420-27012003></SPAN><SPAN class=3D390153420-27012003><FONT =
face=3DArial=20
color=3D#800080 size=3D2></FONT></SPAN> </DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> Daniel Morgan=20
[mailto:danmorg@sc.rr.com]<BR><B>Sent:</B> Monday, January 27, 2003 =
5:33=20
AM<BR><B>To:</B> Piers Haken; Mono-List<BR><B>Subject:</B> RE: =
[Mono-list]=20
Automatic Binding of Variables to Parameters in =
ADO.NET<BR><BR></FONT></DIV>
<DIV><SPAN class=3D385273113-27012003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2>Maybe that's what we need then - for someone to start working =
on the=20
xsd.exe tool for creating type-safe DataSets.</FONT></SPAN></DIV>
<DIV><SPAN class=3D385273113-27012003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D385273113-27012003><FONT face=3DArial =
color=3D#0000ff size=3D2>What=20
would be involved in creating this tool?</FONT></SPAN></DIV>
<DIV><SPAN class=3D385273113-27012003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D385273113-27012003><FONT face=3DArial =
color=3D#0000ff size=3D2>Any=20
takers?</FONT></SPAN></DIV>
<DIV><SPAN class=3D385273113-27012003><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><FONT face=3DTahoma size=3D2>-----Original =
Message-----<BR><B>From:</B> Piers=20
Haken [mailto:piersh@friskit.com]<BR><B>Sent:</B> Monday, January 27, =
2003=20
8:46 AM<BR><B>To:</B> Daniel Morgan; Mono-List<BR><B>Subject:</B> RE:=20
[Mono-list] Automatic Binding of Variables to Parameters in=20
ADO.NET<BR><BR></FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px"><!-- Converted from =
text/plain format -->
<P><FONT size=3D2>Microsoft ships a tool called xsd.exe that =
generates=20
type-safe DataSets that do basically this. You give it an XML Schma =
for your=20
table and it generates a source file for a class that drives from =
DataSet=20
but which has typesafe accessors and events. It might be a little=20
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>>=20
From: Daniel Morgan [<A=20
href=3D"mailto:danmorg@sc.rr.com">mailto:danmorg@sc.rr.com</A>]=20
</FONT><BR><FONT size=3D2>> Sent: Monday, January 27, 2003 4:52 =
AM</FONT>=20
<BR><FONT size=3D2>> To: Mono-List</FONT> <BR><FONT size=3D2>> =
Subject:=20
[Mono-list] Automatic Binding of Variables to </FONT><BR><FONT =
size=3D2>>=20
Parameters in ADO.NET</FONT> <BR><FONT size=3D2>> =
</FONT><BR><FONT=20
size=3D2>> </FONT><BR><FONT size=3D2>> Hello,</FONT> <BR><FONT =
size=3D2>>=20
</FONT><BR><FONT size=3D2>> How could I automatically bind =
variables as=20
paramters in SQL, </FONT><BR><FONT size=3D2>> execute the SQL, =
and get the=20
results automatically. Would </FONT><BR><FONT size=3D2>> =
this involve=20
creating new attributes to handle this. I know =
</FONT><BR><FONT=20
size=3D2>> Glade# uses attributes to bind variables.</FONT> =
<BR><FONT=20
size=3D2>> </FONT><BR><FONT size=3D2>> Let's say we have a =
database table=20
SOMETABLE with the following data:</FONT> <BR><FONT size=3D2>>=20
</FONT><BR><FONT size=3D2>> ANUM AMONEYVALUE=20
=
ADATETIME =20
ABOOLEAN AVALUE</FONT> <BR><FONT size=3D2>> =3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
=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>>=20
5 152.32 2002-12-31 =
12:34:56=20
False 9</FONT> <BR><FONT size=3D2>> =
6 =20
36.45 2001-01-23 05:12:23=20
True 8</FONT> <BR><FONT size=3D2>> =
</FONT><BR><FONT=20
size=3D2>> Here is a struct that will contain the returned =
values.</FONT>=20
<BR><FONT size=3D2>> </FONT><BR><FONT size=3D2>> public struct =
MyStruct</FONT> <BR><FONT size=3D2>> {</FONT> <BR><FONT=20
size=3D2>> int someNumber;</FONT> <BR><FONT=20
size=3D2>> double someMonetaryValue;</FONT> <BR><FONT =
size=3D2>> DateTime someDataTime;</FONT> <BR><FONT=20
size=3D2>> bool someBoolean;</FONT> <BR><FONT =
size=3D2>>=20
}</FONT> <BR><FONT size=3D2>> </FONT><BR><FONT size=3D2>> Here =
is the=20
sample code that demonstrates what I am asking.</FONT> <BR><FONT =
size=3D2>>=20
</FONT><BR><FONT size=3D2>>=20
[SomeMagicMethodParameterDataBind("someValue"]</FONT> <BR><FONT =
size=3D2>>=20
public MyStruct GetData (IDbConnection dbcon, int someValue)</FONT>=20
<BR><FONT size=3D2>> {</FONT> <BR><FONT=20
size=3D2>> =20
[SomeMagicDataBind("mystruct"]</FONT> <BR><FONT size=3D2>>=20
MyStruct mystruct;</FONT> <BR><FONT=20
size=3D2>> </FONT><BR><FONT size=3D2>> =
=20
string sql =3D</FONT> <BR><FONT=20
size=3D2>> =
"SELECT=20
aNum, aMoneyValue, " +</FONT> <BR><FONT=20
size=3D2>> =20
" aDateTime, aBoolean " +</FONT> =
<BR><FONT =
size=3D2>> =20
"FROM sometable " +</FONT> <BR><FONT=20
size=3D2>> =
"WHERE=20
:someValue " +</FONT> <BR><FONT=20
size=3D2>> =
"INTO=20
mystruct.someNumber, mystruct.someMonetaryValue, " +</FONT> =
<BR><FONT=20
size=3D2>> =20
" mystruct.someDateTime,=20
mystruct.someBoolean";</FONT> <BR><FONT size=3D2>> =
</FONT><BR><FONT=20
size=3D2>> SomeMagicalClass magic =
=3D new=20
SomeMagicalClass(dbcon, sql);</FONT> <BR><FONT=20
size=3D2>> =
magic.ExecuteSQL();</FONT>=20
<BR><FONT size=3D2>> </FONT><BR><FONT=20
size=3D2>> return mystruct;</FONT> =
<BR><FONT=20
size=3D2>> }</FONT> <BR><FONT size=3D2>> </FONT><BR><FONT =
size=3D2>> If I=20
call GetData() with someValue set to 9, I should get a =
</FONT><BR><FONT=20
size=3D2>> MyStruct struct that has the following resuls:</FONT> =
<BR><FONT=20
size=3D2>> </FONT><BR><FONT size=3D2>> MyStrcut mystruct =3D =
GetData(dbcon,=20
9);</FONT> <BR><FONT size=3D2>> </FONT><BR><FONT size=3D2>> // =
expected=20
results</FONT> <BR><FONT size=3D2>> mystruct.someNumber =3D =
5</FONT>=20
<BR><FONT size=3D2>> mystruct.someMonetaryValue =3D 152.32</FONT> =
<BR><FONT=20
size=3D2>> mystruct.someDateTime =3D "2002-12-31 12:34:56" =
</FONT><BR><FONT=20
size=3D2>> mystruct.someBoolean =3D false</FONT> <BR><FONT =
size=3D2>>=20
</FONT><BR><FONT size=3D2>> This is what I am interested in =
having. =20
Now, how do I get </FONT><BR><FONT size=3D2>> this? I'm =
sure I would=20
need to use reflection heavily and </FONT><BR><FONT size=3D2>> =
attributes.=20
There would be parsing of SQL for parameters, </FONT><BR><FONT =
size=3D2>>=20
create parameters based on the parameters in the SQL, update=20
</FONT><BR><FONT size=3D2>> the parameters with information from =
variables=20
currently in </FONT><BR><FONT size=3D2>> scope that match the =
name of the=20
parameter, set the value of </FONT><BR><FONT size=3D2>> any input =
or=20
input/output parameters from the variable that </FONT><BR><FONT =
size=3D2>>=20
have been bounded earlier, execute the SQL, set the values of=20
</FONT><BR><FONT size=3D2>> any return, output, or input/output =
variables=20
based on the </FONT><BR><FONT size=3D2>> results in the =
parameters.</FONT>=20
<BR><FONT size=3D2>> </FONT><BR><FONT size=3D2>> Any =
ideas?</FONT>=20
<BR><FONT size=3D2>> </FONT><BR><FONT size=3D2>> If you ever =
used RAD=20
programming languages like Delphi, </FONT><BR><FONT size=3D2>>=20
Centura/Gupta SQL Windows, PowerBuilder, or Visual Basic, you=20
</FONT><BR><FONT size=3D2>> would understand how powerful this =
can be for=20
database applications.</FONT> <BR><FONT size=3D2>> =
</FONT><BR><FONT=20
size=3D2>> Thanks Daniel</FONT> <BR><FONT size=3D2>> =
</FONT><BR><FONT=20
size=3D2>> _______________________________________________</FONT> =
<BR><FONT=20
size=3D2>> Mono-list maillist - =
Mono-list@lists.ximian.com=20
</FONT><BR><FONT size=3D2>> <A=20
=
href=3D"http://lists.ximian.com/mailman/listinfo/mono-list">http://lists.=
ximian.com/mailman/listinfo/mono-list</A></FONT>=20
<BR><FONT size=3D2>> =
</FONT></P></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
------_=_NextPart_001_01C2C646.E2A3BE6C--