From DataSet To SqlDataReader in a CLR Stored Procedure

Hi all,
I'm writing a CLR stored procedure that just execute a query using 2 parameters.

SqlContext.Pipe.Send can send a SqlDataReader, but if I've got a DataSet?
How can I obtain a SqlDataReader from a DataSet?

Dim command As New SqlCommand(.......)
.....
Dim ds As New DataSet()
Dim adapter As New SqlDataAdapter(command)

adapter.Fill(ds, "MyTable")

... 'manipulating the ds.Tables("MyTable")

At this moment I have to send the table...but
ds.Tables("MyTable").CreateDataReader()
just give me a DataTableReader, and i can't send it with SqlContext.Pipe.Send(...

Help me please!
The DataSet.CreateDataReader method can be used to generate a data reader that reads data from a dataset.|||DataSet.CreateDataReader() is the same of dataSet.Table(x).CreateDataReader()...it returns a DataTableReader!
There is also an overload for dataSet.CreateDataReader that get an array of dataTable as parameter!|||Sorry about that. For some reason, I was quite convinced that SqlPipe.Send accepted an IDataReader rather than a SqlDataReader. Looks like you'll need to use SqlPipe.SendResultsRow. See http://msdn2.microsoft.com/en-US/library/microsoft.sqlserver.server.sqlpipe.sendresultsrow(VS.80).aspx for details and sample code.|||Thank you, but i don't think that can be the "best practice", it's not in .net style!!!
I'll search better!|||? It used to accept IDataReader during the betas, all the way up to one of the final CTPs -- then that functionality was removed for some reason :( -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Nicole Calinoiu@.discussions.microsoft.com> wrote in message news:7725b945-d2c1-44d0-a698-fa9a344a48d4@.discussions.microsoft.com...Sorry about that. For some reason, I was quite convinced that SqlPipe.Send accepted an IDataReader rather than a SqlDataReader. Looks like you'll need to use SqlPipe.SendResultsRow. See http://msdn2.microsoft.com/en-US/library/microsoft.sqlserver.server.sqlpipe.sendresultsrow(VS.80).aspx for details and sample code.|||

Well, at least I wasn't imagining things. ;)

Based on the current implementation, I'd have to guess that the change may have been made for 2 main reasons: improved performance and enhanced metadata extraction. However, I can't see any reason why an overload that accepts IDataReader couldn't have been left in since the "improved" SqlDataReader implementation would still be used where possible. A wee bit odd...

From dataset into Report

First of all:

Thanks for the time lost in trying to resolve my problems,
I have 2 Diferent Databases and N Tables in each one.

--------------
Database1 - TableA
CliCod -- CliName -- SalesMan
() () ()
--------------

--------------
Database2 - TableA
UniqueKey -- CliName -- Value
() () ()
--------------

My problem is not to create the SQL Statement but where to put it (implement it).
Select a.*
From database2.TableA A
Inner join Database1.TableA B ON A.CliName == B.CliName
Where A.SalesMan= @.Input String from User.

Please ber with me :(

All the examples I found are for 1 database - N Tables.
I want ->2<- databases - N tables

I think the best choice is to use the Push Model, so that I create a dataset to pass to crystal report, (and crystal report viewer).

But how can I create a dataset from 2 databases when the data adapter connectionString wants just one database to connect to.

How can I cross reference two Tables in different Databases into the same DataSet, so that I can pass this dataset into the Crystal Report.

I will be very grateful you could help me, because I can not find an example doing this any where.
The SQL statement would look something like this:

Thanks for your time.simplest way would be to creat a temporary dataset, and then connect to that:
SQL sample as follows, to get data from table1 and table2 and store in tableTemp

SELECT table1.PDATE, table1.PLINE, table1.PSHIFT, table1.RUNTIME, table2.RTIME INTO tableTemp
FROM table1 INNER JOIN table2 ON table1.PDATE = table2.PDATE
WHERE (table1.PDATE=#11/19/2004#);

That gives you your server-side table ready for connection.

dave|||I'm not sure how a dataset works...

I use VB6 and Crystal Reports 8.5...

In VB 6, I use ADO to get my data, then I pass the recordset to Crystal Reports (using RDC). See this link (http://www.dev-archive.com/forum/showthread.php?t=309534&highlight=ado) for an example. Doing it this way would allow you to easily pull data from more than one table or database.|||Have you tried using the Merge method of the dataset?...you can generate
2 datasets from the 2 databases...and then merge these dataset into a single one and bind your crystal report to it..just make sure that the 2 datasets have the same structure.

dataset1.merge(dataset2)

yourReport.setdatasource(dataset1)

i hope this helps :)

Friendly DataSet Table Names

I have a stored procedure that returns a set of tables based on data in a table for a company. These tables are being used to create drop down lists for criteria selection for the client. We have a Javascript based control that will make use of these based on the control name. So... ideally, I would like the stored procedure to return tables named "EmployeeID", "ResourceName", so that I can accurately name the controls when they are being created. The data that is returned is not static, so, for example, client 1 may see the EmployeeID drop down, while client 2 may not.

I searched and didnt see anyone that was able to accomplish this in this way, but wanted to post something here before I moved forward in a different direction.

I am thinking that I will likely return an extra table with the field names and their corresponding tables (i.e. "EmployeeID"/"Table0", "ResourceName"/"Table1", etc...)

Thanks,

Josh

Have you tried giving alias to the returned tables (result sets) as well as columns? For example:

create proc sp_RtnAlias
as
select EmployeeID EID from Employees as EmployeeID
go

Then you can access the result set using EmployeeID.EID

Hope I haven't misundstood you.

|||

Here's what I do... and I haven't decided if it is cheesey or not...

declare

@.tbltable(Ordinalintidentity(1,1),tableName varchar(50))

/* Insert a row into the temp table for each table returned by the query*/

INSERTINTO @.tblVALUES('Prospects')

INSERTINTO @.tblVALUES('AccountExecs')

INSERTINTO @.tblVALUES('Regions')

INSERTINTO @.tblVALUES('Products')

INSERTINTO @.tblVALUES('ProspectDescriptors')

INSERTINTO @.tblVALUES('Steps')

INSERTINTO @.tblVALUES('ProspectTypes')

INSERTINTO @.tblVALUES('VendorTypes')

INSERTINTO @.tblVALUES('Clients')

-- Index table

SELECT Ordinal, TableName

FROM @.Tbl

ORDERBY Ordinal

The rest of the sproc is a series of selects in the same order as they appear in the index table. I have method in the program that loops through the table objects and assigns each one the appropriate name from the index table. Note that the index table does not contain a reference to itself and can be disposed of when the other table names have been assigned...