I'm trying to understand if I am thinking about ADO.NET correctly.
since it is supposed to be an advancement of DAO or ADO.
With DAO, I can rewrite a query using VBA and a querydef object.
Isn't the querydef object a 'Data Access Object'??
So that would mean there is a better approach for this in ADO.NET - right?
What is it?
Would it be better to do this manual loop from the main database, or from a
first resulting dataset?
...And for writing loops would you use a DataReader instead?
Could you or someone in this newsgroup give show me how to write a loop that
would give me the result that my 2nd layer gives:
It basically says in psuedo SQL, Select the Member if the member has a
'Blank' Thru-Date. (so if the member has multiple - blank Thru-Dates they
should all appear)
If the member has no blank Thru-Dates, it should pick the latest one.
If there is a fast way to do this all at once, that would be nice.
If there was a way to rewrite my query in Access from ADO.NET and refer to
that rewritten query in the DataAdapter, (using a view and not a table) that
would also be nice.
"Miha Markic [MVP C#]" wrote:
> Hi,
>
> No, DataSet is not a substitute for an in-memory database and it has limited
> capabilites for processing.
> You might do a manual loop and fill resulting datatable or, if I recally
> properly, somebody advertised a product (in this ng) that does SQL
> statements on datasets.
>
> --
> Miha Markic [MVP C#]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>
> "jonefer" <(E-Mail Removed)> wrote in message
> news:90A36CCC-B62D-45CE-A840-(E-Mail Removed)...
> >I have a paramaterized command for a data adapter
> >
> > SELECT MRN, Name, Sex, DOB, SSN
> > FROM dbo.Membership
> > WHERE (MRN = @MRN or @MRN is null)
> > AND (Name like @Name or @Name is null)
> > AND (Sex = @Sex or @Sex is null)
> > etc.
> > The name of that result set is from a stored procedure called:
> > qMembershipSelect
> >
> > for 1.4 million records, this ensures that the data will come up quickly
> > because all I'm doing is filtering between 1-4 parameters.
> >
> > But after the first select is done, I'd like to apply additional filtering
> > to that result set
> > that weeds out other stuff like duplicate records and selects a true
> > Current
> > member:
> >
> > SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
> > [FROM-DT], [THRU-DT]
> > FROM qMembershipSelect AS Y
> > GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
> > HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
> > qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
> > From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
> > ORDER BY MemNAME, [FROM-DT] DESC;
> >
> > Obviously this 2nd layer of filtering wouldn't be so tedious to a smaller
> > dataset as it would to 1.4 million records at the start.
> >
> > Are you able to do this in ADO.NET with a DataTable?... if not, how do you
> > go about it?
> >
> > In DAO, what I would do is write the First Select Statement
> > qdf.SQL = "Select...."
> >
> > That would become qMembershipSelect
> >
> > and then refer to the rewritten query as if it were a table.
> > it would be nice if qMembershipSelect could be referred to with this
> > complexity with another dataadapter?
> >
> > I don't see in ADO.NET being able to write complex SQL statements that
> > refer
> > to a DataTable (equivalent to a query in Access)?
> >
> >
> >
>
>
>
Similar Threads
