Receiving results from a stored procedure

[Mike]

I'm coming up empty, in vb.net cf - how do I call a stored procedure located
on a remote sqlserver/msde database - which returns a recordset? I'd like
to then take that recordset and load the contents into my local sqlce
database. How is this done?

Can you point me in the right direction?

Thanks! -Mike

 

[Alex Feinman [MVP]]

Loading should be the same as on the desktop. Create an SqlDataAdapter based
on a SqlCommand. SqlCommand should have commandd type "stored procedure" and
text set to the SP name. Add appropriate parameters, set their values and
use DataAdapter to fill a DataTable.

 

[Mike]

I guess I'm close then... I've not done this using .net though. I used vb6
and a adodb.command object to set the params of, and call the, stored
procedure. The recordset returned from the stored procedure was saved to an
object variable.

Am I basically just replacing the object variable with a DataTable?

Let me know if I'm completely missing the boat here.

Thanks for the help,

Mike

 

[Mike]

When I said that I was close then I meant that I have been filling a DataSet
with my sql sproc dataadapter. I'm trying to find the difference between
using a dataset and a datatable. Does a DataTable allow me to dump the
data more easily to a sqlce database or something?

I basically want to take the output of my stored procedure and put it into
my sqlce database table. Then the handheld will be disconnected from the
network connection and used mobile, connectionless. I know I can use
datasets like this - but if anything goes wrong with the program, or if the
user exits the program, the data is lost. Keeping it in a sqlce database
prevents this - so long as ram, or the sdcard, remain safe.

Is getting the data from a sqlserver stored procedure to a sqlce ppc
database best handled still, using a data adapter and a datatable/dataset?

If so, why would I use a datatable over a dataset? Pardon my ignorance.

-Mike

 

[Mike]

I want to add too, that the sqlce database table on the ppc is an extremely
paired down version of what columns actually exist in the sqlserver table.
I don't need all the same data - so what I'm saying is that its not going to
be a column to column, table to table, match when moving data from the
sqlserver sproc to the sqlce database. Too much info in the table, and
limited memory on the device.

Feel free to tell me if I'm all washed up on my approach here tho.

 

[Sergey Bogdanov]

I think the best approach in your case is to use Replication which
provides the System.Data.SqlServerCe.SqlCeReplication. Replication
features allow you to synchronize data between SQL Server CE and SQL
Server 2000.

How to use Replication refer to
http://msdn.microsoft.com/library/d...tm/_lce_repl_prog_programming_replication.asp

Hope this help,
Sergey

 

[Mike]

Seems that replication is Only going to work when both database tables are
an exact match, no?? There's a lot of info in several of these tables from
which the mobile app only needs a limited amount - why load the whole table
if I don't have to?

 

[Sergey Bogdanov]

Replication offers row and column filtering, which enable you to define
subsets of data that can be published. Refer to [1] for more information.

[1]
http://msdn.microsoft.com/library/d...m/_lce_repl_intro_introducing_replication.asp

Sergey

 

[Alex Feinman [MVP]]

Using replication will only make sense if the data need to be synchronized
back to the server. If all you need is getting some data down to the SQL CE
database, you need RDA. It fits the bill exactly for this scenario and does
not carry painful complexity of replication process.
http://msdn.microsoft.com/msdnmag/issues/03/01/sqlserverce20/

 

[Mike]

Both methods sound ideal really, as I do need to update data back to the
tables. But only if I had control over the server box. Unfortunatley, IIS
is not installed, and won't be. Its a very locked environment.

I'm able to make a call now to the stored procedure and load a dataset - it
sounds like loading the dataset from the sproc, and then taking that data to
dump into the sqlce database, is the best way for me then. ? A bit
conveluted I suppose, but nothing different than the old days of loading a
recordset, and then loading that data into a onboard database.

If IIS is the only other way, I suppose I'll have to use my dataset
workaround. I will, for future projects, be looking at these two methods
though. I appreciate the feedback and suggestions - I'd still be
interested in any other input you might add though.

Thanks !

Mike