I thnk you are getting into muddy waters here, Jon. I do something like what you are doing. Query from SQL Server, get datasets and then persist those to xml on the local machine for offline use.
Some of the data is just lists and I can use them in their entirety. So my SQL Query creates the resultset that I need and I store that resultset as XML. I open that xml into a datatable and I have the data I need. Other data is not like that. I need to pick stuff OUT of there. The way I do that is open up the xml into a datatable, and then poke around in it pulling the data I want out into an object. When I'm finished working on the object I open the xml back up into a new dataset, update that from the object then savexml on the dataset again. Now I know you are doing complex queries which is why you want to stick with TSQL, but perhaps you need to think uot of the box a little bit - especially if you are talking about small amounts of data. If you must stick with your SQL queries, then at least consider MSDE rather than Jet. If this is not yet going into production, maybe even SQL 2005 Express. If you are doing this on pocket pc's then it's a bit of a different story. Julie -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Jon Rothlander Sent: Wednesday, July 06, 2005 2:07 PM To: [email protected] Subject: Re: [ADVANCED-DOTNET] ADO.Net How do you get access to ADOX, which seems to be the only way to create an MDB file? -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Pardee, Roy Sent: Wednesday, July 06, 2005 11:31 AM To: [email protected] Subject: Re: [ADVANCED-DOTNET] ADO.Net There is no client-side SQL engine in ado.net. I think the thing that comes closest is the jet engine (the thing that gets exercised when you move data in & out of an .mdb file). Jet has a pretty nice implementation of SQL, tho there are some annoying limitations (subqueries come to mind). I'd advise stashing your offline data in an .mdb and just swapping out DataAdapters as users connect & disconnect, except that I think that would mess up ado.net's updating logic--you'll have to manage RowStates your own self when time comes to push offline changes back to the db. I don't know if that's more trouble than its worth. And since your disconnected users will only ever be able to get a subset of the data they got from their last connected query, do you really want to encourage the impression that they can run queries when offline? That sounds like an invitation to confusion to me... HTH, -Roy -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Jon Rothlander Sent: Wednesday, July 06, 2005 8:52 AM To: [email protected] Subject: [ADVANCED-DOTNET] ADO.Net I've doing what I would think would be a very common use of ADO.Net, but I cannot find much info on this in any of my book or online. What I'm doing seems pretty simple. I have an app that can run in both a disconnted and connected state. When it is disconnected, it is disconnected from the SQL DB. My client machine does not have access to a local database, so I am using XML on the client. I am loading the XML into datasets to be processed. The problem I'm running into is that I cannot easily perform the same SELECT statements against the datasets that I can perform against SQL Server. For example, lets say that I have the following SQL SELECT statement... SELECT * FROM table1 If I bring a copy of table1 from SQL server to the client machine and save it as XML, I can reload the dataset using the local copy of XML. Reload the XML will recreate the same dataset as the SQL SELECT statement created with I was connected to the Server. However, take the following SQL statement.. SELECT ID, rtrim(ltrim(FirstName + ' ' + LastName, FROM table1 WHERE userid = '1' OR userid = NULL What options do I have in loading table1 into a dataset and them recreating this same SQL statement? What if the SQL statement was even more complex with 3 or 4 tables, a few JOINS, etc? Do I have to build the dataset tables individually, then set up relationships, and them perform simple SQL statements one at a time while creating new datasets with each result? That seems to be the way the books show you. However, that seems to be a major pain. I might as well right XML parsing code to handle it. Maybe creating a new dataset from SQL when the user is last connected, but create one with the JOINS already in place. Then just use multiple table().Select () statements to drill into the records I need. What do you think is the best way to handle this sort of logic? Any ideas would be very much appreicated. I want to make sure I approach this one correctly so I do not need to rewrite it down the road. I also want to understand what options I have to make sure I am not missing something that would make this pretty easy to code. Best regards, Jon =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor. http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor. http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
