MSResultSetGenerator - Create Partial Class of Typed Result Set to select data subset.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

doug@goroute.com - 14 Mar 2008 23:56 GMT

Using VS2005
Sqlce 3.1
I have changed the dataset designer to generate ResultSets. The
generated code seems to open the table with no ability to select the
items i want. I want to select a subset within the tables (Select *
from Item where prodline = "foo") .I have tried creating a partial
class but i cant seem to get it to work. Below is an example

Partial Class DetailRS
Inherits DetailResultSet <--- this one is generated by
MSResultGenerator...
Private resultSetOptions As
System.Data.SqlServerCe.ResultSetOptions
Public Sub New(ByVal SqlQuery As String)
Dim cmd As New System.Data.SqlServerCe.SqlCeCommand
resultSetOptions =
System.Data.SqlServerCe.ResultSetOptions.Scrollable
resultSetOptions = (resultSetOptions Or
System.Data.SqlServerCe.ResultSetOptions.Sensitive)
resultSetOptions = (resultSetOptions Or
System.Data.SqlServerCe.ResultSetOptions.Updatable)
cmd.Connection = cn
cmd.CommandText = SqlQuery
cmd.ExecuteResultSet(Me.resultSetOptions)
End Sub
End Class

Reply to this Message

Ginny Caughey [MVP] - 15 Mar 2008 12:03 GMT

I usually just add some constructors to the generated ResultSets in a
partial class that take parameters indicating what data I want. I also add
additonal Open methods too.

Jim Wilson did a good webcast on using SQL Compact and LINQ that illustrates
the technique:
http://www.microsoft.com/events/EventDetails.aspx?CMTYSvcSource=MSCOMMedia&Param
s=~CMTYDataSvcParams%5E~arg+Name%3D%22ID%22+Value%3D%221032358821%22%2F%5E~arg+N
ame%3D%22ProviderID%22+Value%3D%22A6B43178-497C-4225-BA42-DF595171F04C%22%2F%5E~
arg+Name%3D%22lang%22+Value%3D%22en%22%2F%5E~arg+Name%3D%22cr%22+Value%3D%22US%2
2%2F%5E~sParams%5E~%2FsParams%5E~%2FCMTYDataSvcParams%5E

Signature

Ginny Caughey
Device Application Development MVP

www.wasteworks.com
Software for Waste Management

> Using VS2005
> Sqlce 3.1
[quoted text clipped - 22 lines]
> End Sub
> End Class

Reply to this Message

doug@goroute.com - 15 Mar 2008 22:02 GMT

Greetings, Thank's for the pointers.
I got it to work by creating a partial class with exactly the same
name as the result set defined by the MSResultSetGenerator. So for
ITEMResultSet, I created the following below.

This allows me to scroll up and back, use my existing connection,
update lines, use typed values. Very cool.

Thanks again for your help.

Partial Class ITEMResultSet
Public Sub New(ByVal SqlQuery As String)
resultSetOptions =
System.Data.SqlServerCe.ResultSetOptions.Scrollable
resultSetOptions = (resultSetOptions Or
System.Data.SqlServerCe.ResultSetOptions.Sensitive)
resultSetOptions = (resultSetOptions Or
System.Data.SqlServerCe.ResultSetOptions.Updatable)
Me.Open(SqlQuery)
End Sub
Sub Open(ByVal sqlQuery As String)
cmd = New SqlServerCe.SqlCeCommand
cmd = cn.CreateCommand
cmd.CommandText = sqlQuery
cmd.CommandType = CommandType.Text
cmd.ExecuteResultSet(Me.resultSetOptions, Me)
End Sub
End Class

Reply to this Message