Typed Dataset/Resultset in SQL Mobile?

  • Thread starter Thread starter Tim Brooks
  • Start date Start date
T

Tim Brooks

All,

I'm working on a SQL Mobile app in VS2005 -- awesome product, too (as an
asided, I wish it were easier to get data into the sdf from the desktop as
we're not all planning on using rep/sync [but I was able to put together a
nice little DTS package to suck and pump my data from SQLServer to
SDF]...but I digress).

Looks as though strongly typed datasetsets are supported but my concern is,
using the built in xsd editor, that if I try to add input parameters (if
even possible) in the tool generated code that the tool will overwrite
anything I change. But conversely, I can't figured out how to make the
editor accept an input parameter; it only seems to allow parameterless
queries.

Am I missing something? Is it doable? Any example code someone could point
me to that would show me how? Of course, I'm doing all this rather than
just using an untyped 'database.cs' type object approach because my
understanding is my database access would perform better using a strongly
typed approach. Or is the standard SQLCE2.0 sort of data access (e.g. just
hand build everything to use either concatenated SQL strings....or even use
command objects with parameters outside the tool) still the only route?
Basically, I want to use the power of the tool but not sacrifice parameters.
I'm sure it supports it but there seems to be zero documentation on this
topic, yet......

thanks and I hope my question makes sense,
tim
 
Tim,

Using strongly typed datasets won't necessarily give you better performance,
but it does give you compile time checking and intellisense. I don't know if
the XSD tool will support what you want in later versions of VS2005, but you
could create your own class that inherits from the generated code to avoid
the problem of the tool blowing away your changes.

I agree that not everybody wants to use merge replication or RDA to move
data to/from SQL Mobile. Good thinking getting DTS to do what you want!

Ginny Caughey
..NET Compact Framework MVP
 
Thanks Ginny,

Wow. You are all over this newsgroup. My thanks from a newbie for all the excellent advice to myself and others....

I also realized after asking that I was thinking of the typed dataset the wrong way. Because I was mostly after being able to do something like:

DataSet _myDS = dsFactory.GetCustomerFromID(int intCustFromListChoice); // Method based on parameterized query

....so, no really squirrelly queries but mostly just the ability to extract a list of ID/name pairs from the db...paint a list or grid, and then go back and get the details from the main table. Having not used typed datasets much in the past, I didn't realize that the xsd tool will auto create functions like this for each key....so no need for simple or obvious record matching functions to do this via parameterized queries.

So...what I wanted to do is already possible with the xsd generated code like so (notice it also now supports the new DataTable class):
--------------------------------------------------------------
// Db Name
string const DB_NAME = "PalmIsEvil.sdf";

// Connection
string strConnect = String.Format(System.Globalization.CultureInfo.InvariantCulture, @"Data Source = {0}\{1}", Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase), DB_NAME.ToString())); // Yuck!!!!!!!!!!!!!!

// SQL statement
string SQL = "SELECT CustomerID, CustomerName FROM tblCustomers";

// DataTable instance
CustomerDataTable dtCust = new CustomerDataTable(); // Typed DataTable!!

// Adapter to fill table
SqlCeDataAdapter scdaTemp = new SqlCeDataAdapter(SQL, strConnect);

// Use try/catch and exceptions in real-life!!!!!
scdaTemp.Fill(dtCust);

......

////////// This is where I came in .... this is what typed dataset does that I didn't see yesterday
CustomerRow dtCust = dtCust.FindByCustomerID(intCustFromListChoice); // Auto-generated Row object and DataTable function!!!

--------------------------------------------------------------
.... Very cool!!!

Too bad the query builder in the xsd tool -- at least when developing for CF -- doesn't seem to support parameters. Seems to be a strange oversight because the query gets generated into a series of objects and methods that aren't connected to the actual database at all, so the fact that SQLMobile doesn't support Sprocs should not prevent use of parameters.

That said, I'm very glad typed datasets were added to CF2.0.

I'm also a bit sad to see that the xsd tool doesn't support the SqlCeResultSet...from all I've been reading it seems the way to go for performance. But I suppose it makes sense in that it seems to be more like an ADO recordset and therefore more connection oriented than the other ADO.Net objects like dataset or datatable (and xsd doesn't support DataReader either).

All in all, VS2005 and CF2.0 are just incredible advances. The VS team is hands down my favorite team/product line at MS.....these guys & gals can show 'nuff code!

tim

Ginny Caughey said:
Tim,

Using strongly typed datasets won't necessarily give you better performance,
but it does give you compile time checking and intellisense. I don't know if
the XSD tool will support what you want in later versions of VS2005, but you
could create your own class that inherits from the generated code to avoid
the problem of the tool blowing away your changes.

I agree that not everybody wants to use merge replication or RDA to move
data to/from SQL Mobile. Good thinking getting DTS to do what you want!

Ginny Caughey
.NET Compact Framework MVP

Tim Brooks said:
All,

I'm working on a SQL Mobile app in VS2005 -- awesome product, too (as an
asided, I wish it were easier to get data into the sdf from the desktop as
we're not all planning on using rep/sync [but I was able to put together a
nice little DTS package to suck and pump my data from SQLServer to
SDF]...but I digress).

Looks as though strongly typed datasetsets are supported but my concern
is, using the built in xsd editor, that if I try to add input parameters
(if even possible) in the tool generated code that the tool will overwrite
anything I change. But conversely, I can't figured out how to make the
editor accept an input parameter; it only seems to allow parameterless
queries.

Am I missing something? Is it doable? Any example code someone could
point me to that would show me how? Of course, I'm doing all this rather
than just using an untyped 'database.cs' type object approach because my
understanding is my database access would perform better using a strongly
typed approach. Or is the standard SQLCE2.0 sort of data access (e.g.
just hand build everything to use either concatenated SQL strings....or
even use command objects with parameters outside the tool) still the only
route? Basically, I want to use the power of the tool but not sacrifice
parameters. I'm sure it supports it but there seems to be zero
documentation on this topic, yet......

thanks and I hope my question makes sense,
tim
Click to expand...
Click to expand...
 
Tim,

Glad to hear you've got things all figured out. There are some features of the XSD editior that aren't working quite yet and some that are a bit hidden.SqlCeResultSet should work if you know the trick: If you right-click on the xsd file in the Soluiton Explorer and click on Properties, look at Custom Tool and it will say something like MSDataSetGenerator. If you change that to MSDataSetResultSetGenerator you should get strong typed classes for data set AND result set. You can select either as the DataSource property for any databound controls.

HTH,

Ginny
Thanks Ginny,

Wow. You are all over this newsgroup. My thanks from a newbie for all the excellent advice to myself and others....

I also realized after asking that I was thinking of the typed dataset the wrong way. Because I was mostly after being able to do something like:

DataSet _myDS = dsFactory.GetCustomerFromID(int intCustFromListChoice); // Method based on parameterized query

...so, no really squirrelly queries but mostly just the ability to extract a list of ID/name pairs from the db...paint a list or grid, and then go back and get the details from the main table. Having not used typed datasets much in the past, I didn't realize that the xsd tool will auto create functions like this for each key....so no need for simple or obvious record matching functions to do this via parameterized queries.

So...what I wanted to do is already possible with the xsd generated code like so (notice it also now supports the new DataTable class):
--------------------------------------------------------------
// Db Name
string const DB_NAME = "PalmIsEvil.sdf";

// Connection
string strConnect = String.Format(System.Globalization.CultureInfo.InvariantCulture, @"Data Source = {0}\{1}", Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase), DB_NAME.ToString())); // Yuck!!!!!!!!!!!!!!

// SQL statement
string SQL = "SELECT CustomerID, CustomerName FROM tblCustomers";

// DataTable instance
CustomerDataTable dtCust = new CustomerDataTable(); // Typed DataTable!!

// Adapter to fill table
SqlCeDataAdapter scdaTemp = new SqlCeDataAdapter(SQL, strConnect);

// Use try/catch and exceptions in real-life!!!!!
scdaTemp.Fill(dtCust);

.....

////////// This is where I came in .... this is what typed dataset does that I didn't see yesterday
CustomerRow dtCust = dtCust.FindByCustomerID(intCustFromListChoice); // Auto-generated Row object and DataTable function!!!

--------------------------------------------------------------
... Very cool!!!

Too bad the query builder in the xsd tool -- at least when developing for CF -- doesn't seem to support parameters. Seems to be a strange oversight because the query gets generated into a series of objects and methods that aren't connected to the actual database at all, so the fact that SQLMobile doesn't support Sprocs should not prevent use of parameters.

That said, I'm very glad typed datasets were added to CF2.0.

I'm also a bit sad to see that the xsd tool doesn't support the SqlCeResultSet...from all I've been reading it seems the way to go for performance. But I suppose it makes sense in that it seems to be more like an ADO recordset and therefore more connection oriented than the other ADO.Net objects like dataset or datatable (and xsd doesn't support DataReader either).

All in all, VS2005 and CF2.0 are just incredible advances. The VS team is hands down my favorite team/product line at MS.....these guys & gals can show 'nuff code!

tim

Ginny Caughey said:
Tim,

Using strongly typed datasets won't necessarily give you better performance,
but it does give you compile time checking and intellisense. I don't know if
the XSD tool will support what you want in later versions of VS2005, but you
could create your own class that inherits from the generated code to avoid
the problem of the tool blowing away your changes.

I agree that not everybody wants to use merge replication or RDA to move
data to/from SQL Mobile. Good thinking getting DTS to do what you want!

Ginny Caughey
.NET Compact Framework MVP

Tim Brooks said:
All,

I'm working on a SQL Mobile app in VS2005 -- awesome product, too (as an
asided, I wish it were easier to get data into the sdf from the desktop as
we're not all planning on using rep/sync [but I was able to put together a
nice little DTS package to suck and pump my data from SQLServer to
SDF]...but I digress).

Looks as though strongly typed datasetsets are supported but my concern
is, using the built in xsd editor, that if I try to add input parameters
(if even possible) in the tool generated code that the tool will overwrite
anything I change. But conversely, I can't figured out how to make the
editor accept an input parameter; it only seems to allow parameterless
queries.

Am I missing something? Is it doable? Any example code someone could
point me to that would show me how? Of course, I'm doing all this rather
than just using an untyped 'database.cs' type object approach because my
understanding is my database access would perform better using a strongly
typed approach. Or is the standard SQLCE2.0 sort of data access (e.g.
just hand build everything to use either concatenated SQL strings....or
even use command objects with parameters outside the tool) still the only
route? Basically, I want to use the power of the tool but not sacrifice
parameters. I'm sure it supports it but there seems to be zero
documentation on this topic, yet......

thanks and I hope my question makes sense,
tim
Click to expand...
Click to expand...
 
What can I say? *Right again!* Thanks. (Playing with it now)....
Tim,

Glad to hear you've got things all figured out. There are some
features of the XSD editior that aren't working quite yet and some
that are a bit hidden.SqlCeResultSet should work if you know the
trick: If you right-click on the xsd file in the Soluiton Explorer and
click on Properties, look at Custom Tool and it will say something
like MSDataSetGenerator. If you change that to
MSDataSetResultSetGenerator you should get strong typed classes for
data set AND result set. You can select either as the DataSource
property for any databound controls.

HTH,

Ginny

message Thanks Ginny,

Wow. You are all over this newsgroup. My thanks from a newbie
for all the excellent advice to myself and others....

I also realized after asking that I was thinking of the typed
dataset the wrong way. Because I was mostly after being able to do
something like:

DataSet _myDS = dsFactory.GetCustomerFromID(int
*intCustFromListChoice*); // Method based on parameterized query

...so, no really squirrelly queries but mostly just the ability to
extract a list of ID/name pairs from the db...paint a list or
grid, and then go back and get the details from the main table.
Having not used typed datasets much in the past, I didn't realize
that the xsd tool will auto create functions like this for each
key....so no need for simple or obvious record matching functions
to do this via parameterized queries.

So...what I wanted to do is already possible with the xsd
generated code like so (notice it also now supports the new
DataTable class):
--------------------------------------------------------------
// Db Name
string const DB_NAME = "PalmIsEvil.sdf";

// Connection
string strConnect =
String.Format(System.Globalization.CultureInfo.InvariantCulture,
@"Data Source = {0}\{1}",
Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase),
DB_NAME.ToString())); // Yuck!!!!!!!!!!!!!!

// SQL statement
string SQL = "SELECT CustomerID, CustomerName FROM tblCustomers";

// DataTable instance
CustomerDataTable dtCust = new CustomerDataTable(); // Typed
DataTable!!

// Adapter to fill table
SqlCeDataAdapter scdaTemp = new SqlCeDataAdapter(SQL, strConnect);

// Use try/catch and exceptions in real-life!!!!!
scdaTemp.Fill(dtCust);

*.....*

////////// This is where I came in .... this is what typed
dataset does that I didn't see yesterday
CustomerRow dtCust =
dtCust.FindByCustomerID(*intCustFromListChoice);* //
Auto-generated Row object and DataTable function!!!

--------------------------------------------------------------
... Very cool!!!

Too bad the query builder in the xsd tool -- at least when
developing for CF -- doesn't seem to support parameters. Seems to
be a strange oversight because the query gets generated into a
series of objects and methods that aren't connected to the actual
database at all, so the fact that SQLMobile doesn't support Sprocs
should not prevent use of parameters.

That said, I'm very glad typed datasets were added to CF2.0.

I'm also a bit sad to see that the xsd tool doesn't support the
SqlCeResultSet...from all I've been reading it seems the way to go
for performance. But I suppose it makes sense in that it seems to
be more like an ADO recordset and therefore more connection
oriented than the other ADO.Net objects like dataset or datatable
(and xsd doesn't support DataReader either).

All in all, VS2005 and CF2.0 are just incredible advances. The VS
team is hands down my favorite team/product line at MS.....these
guys & gals can show 'nuff code!

tim

"Ginny Caughey [MVP]" <[email protected]
Tim,

Using strongly typed datasets won't necessarily give you better performance,
but it does give you compile time checking and intellisense. I don't know if
the XSD tool will support what you want in later versions of VS2005, but you
could create your own class that inherits from the generated code to avoid
the problem of the tool blowing away your changes.

I agree that not everybody wants to use merge replication or RDA to move
data to/from SQL Mobile. Good thinking getting DTS to do what you want!

Ginny Caughey
.NET Compact Framework MVP

All,

I'm working on a SQL Mobile app in VS2005 -- awesome product, too (as an
asided, I wish it were easier to get data into the sdf from the desktop as
we're not all planning on using rep/sync [but I was able to put together a
nice little DTS package to suck and pump my data from SQLServer to
SDF]...but I digress).

Looks as though strongly typed datasetsets are supported but my concern
is, using the built in xsd editor, that if I try to add input parameters
(if even possible) in the tool generated code that the tool will overwrite
anything I change. But conversely, I can't figured out how to make the
editor accept an input parameter; it only seems to allow parameterless
queries.

Am I missing something? Is it doable? Any example code someone could
point me to that would show me how? Of course, I'm doing all this rather
than just using an untyped 'database.cs' type object approach because my
understanding is my database access would perform better using a strongly
typed approach. Or is the standard SQLCE2.0 sort of data access (e.g.
just hand build everything to use either concatenated SQL strings....or
even use command objects with parameters outside the tool) still the only
route? Basically, I want to use the power of the tool but not sacrifice
parameters. I'm sure it supports it but there seems to be zero
documentation on this topic, yet......

thanks and I hope my question makes sense,
tim
Click to expand...
Click to expand...
Click to expand...
 
Back
Top