I agree with your approach here. Thank you for taking the time to offer me suggestions. I think that this is the most promising and most reasonable solution for what I am doing and based on the fact that I already have 8 of the 12 written this way and the other 4 are mostly written this way. I am taking Roy's suggestion of reducing the logic in the SELECT statements to just pull everything and not filter anything. I am then logging this as XML. Then I load the XML and add the filtering logic. I think this is very similar to your approach.
However, I am having a problem with this, but I think it's just the lack of documentation in all of my books and MSDN. I am trying to filter the dataset into another dataset or a dataview. Then I am trying to filter it again and maybe a third time. How do I recycle the resulting dataset or dataview back into another dataset or dataview? For example, take the following pseudo vb.net code. Dim ds as new dataset Dim dv1 as dataview Dim dv2 as dataview Ds = SomethingToFillTheDataset Dataview1 = ds.tables(0) dv1.filter(1stFilterString) Dv2 = dv1 dv2.filter(2ndFilterString) Return dv2 (I would like to be able to return a dataset if possible) Ok, something like this would not work and ignores the 1st filter. How do I run multiple filters against the save dataset or dataview? If I can set up multiple filters, then I should be able to one step at a time, filter out the extra records. -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Julia Lerman Sent: Wednesday, July 06, 2005 5:14 PM To: [email protected] Subject: Re: [ADVANCED-DOTNET] ADO.Net omg - that select statement!! LOL So before I even explore that, my point is to actually persist the data in an already queried format. So backing up from your (omg <g>) query... and keeping in mind that you said there will only be about 12 rows of data per table, why not do a query in SQL that will bring back a (or some) resultsets like the one your are getting but for all of the data that you are interested in. A smipler example: in sql you have these 4 files with the obvious relationships Clients Addresses (billing and shipping) Invoices Invoice Details Iin an online scenario you would likely pass in an invoice number to a query that has two select statements in it. 1) select invoice.*,clients.clientname, addresses.* from invoice,clients,addresses where invoice.clientid= blah blah blah 2) select invoicedetails.* from invoicedetails where invoiceid=12345 then in ADO.NET you would suck those into a dataset then create a datarelation between the first and second table. Then in the offline scenario (emulating your small amount of data) I might only ever want the past month's invoices. What I would do is have a query like above, but instead of passing in an invoice, I would pass in the daterange, or maybe today's date and just calculate "past month". I would then return one result that is a colletion of all the invoices headers with their client and address info and the second result set would be ALL of the line items for ALL of those month's worth of invoices. Now, I can take those two result sets and persist them as two xml files. Then my business logic would be able to put those back into dataset/datatables/datarelation. Find the correct invoice header - store it's info (date, clientname, clientid, etc etc) into the fields of an invoice object. Then I can get the childRows from the 2nd talbe and put them into a list/collection/additional datatable/whatever. Does that make sense? Then my app will work with the objects, not the datatables. Now - the key here is that this is the offline scenario. Whether you query the database directly for one record and then dump the results into your object(s) or open up some xml files and find the proper rows and dump them into your objects, the rest of your app can be the same - and doesn't care WHERE the data came from. That's basically how I'm doing it. When offline, my app goes for the xml files. When online it goes for web services which return the data. Does that help? Julie -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Jon Rothlander Sent: Wednesday, July 06, 2005 5:41 PM To: [email protected] Subject: Re: [ADVANCED-DOTNET] ADO.Net Julie, I would very much like to take your approach. I am getting lost where you say that you pick out what you need from the datatable. That is what I am really struggling to do. Take a look at my SQL select statement (below) that I am struggle to convert to using a dataset or just XML parsing. Would this be something that you could rewrite and pick out of a datatable? I think that you could, but it seems to be pretty complex. I have about 12 or 13 SQL statements of which about 8 of them I have already written dataset equilvants of and they work fine. Now I have 4 more that I need to write that are some what complex. The sample below is the most complex version. One of the post today mentioned that I should not do a lot of the tasks inside the SQL command, but instead return the data and then pick it apart. That makes a lot of sense to me. So I can see takening out some of the complexities of this select statement. However, I would have to leave in the JOINs and I would have to think through how to remove the WHERE clauses and add them back as filters. What do you think about this? Would this be something that you would want to take on if you were me? Having never taken on something like this, I do not have a good feeling about this approach or the Access approach. I was hoping that one or the other would stand out as the best option. Greg I guess the idea would be to create the SELECT to return all of the data. Then I would add filters to remove anything that is unwanted. Is that the basic idea? Return ExecuteSQLCommand( _ "SELECT ltrim(rtrim(convert(nvarchar,str(EvalResults.EvalID)))) as EvalID, Selected AS CkMark, EvalResults.Status, EvalCategories.CategoryShort, EvalCategories.CategoryID, " + _ " RatingCode, RatingOrder, EvalResults.Comment, EvalCategories.CategoryOrder " + _ "FROM (EvalCategories RIGHT JOIN EvalResults " + _ "ON EvalCategories.CategoryID = EvalResults.CategoryID) LEFT JOIN EvalRatings ON EvalResults.RatingID = EvalRatings.RatingID " + _ "WHERE ((EvalResults.EvaluateeID)= strEvaluateeID) " & strParam & + _ "UNION " + _ "SELECT 'Cat' + ltrim(rtrim(convert(nvarchar,str(EvalCategories.CategoryID)))) AS EvalID, '' AS CkMark, '' as Status, EvalCategories.CategoryShort, EvalCategories.CategoryID, ' '' AS RatingCode, '' AS RatingOrder, '' AS Comment, EvalCategories.CategoryOrder " + _ "FROM EvalCategories " + _ "WHERE (EvalCategories.DomainID = 3) " + _ "AND (EvalCategories.CategoryID) Not In " + _ " (SELECT EvalResults.CategoryID " + _ "FROM EvalResults " + _ "WHERE ((EvalResults.EvaluateeID)= strEvaluateeID) " & strParam & + _ "ORDER BY EvalCategories.CategoryOrder, EvalID DESC") -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Julia Lerman Sent: Wednesday, July 06, 2005 1:33 PM To: [email protected] Subject: Re: [ADVANCED-DOTNET] ADO.Net 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 =================================== 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
