Scott M.
11/23/2007 9:29:00 PM
I don't really need to do much investigation into the connection managment
or when to use TSQL vs. CLR SP's, I just need to know the syntax for
consuming a SQLDataRecord returned by a CLR SP.
"William (Bill) Vaughn" <billvaRemoveThis@betav.com> wrote in message
news:B1EE01E6-AB34-4E4E-BC8F-B6D78F9C10F0@microsoft.com...
> Chapter 14 of my new book discusses this at length if you still can't find
> the answer. There are a dozen issues involved with this approach including
> serializers and managing an internal connection--not to mention the wizdom
> of executing a SP that does what TSQL can do better...
>
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> -----------------------------------------------------------------------------------------------------------------------
> "Scott M." <smar@nospam.nospam> wrote in message
> news:%235HX0hfLIHA.3940@TK2MSFTNGP05.phx.gbl...
>> Actually, I'm now reading that a command object now has an "ExecuteRow"
>> method, which returns a SQLDataRecord. I think this is what I've been
>> looking for.
>>
>>
>> "Charles Wang[MSFT]" <changliw@online.microsoft.com> wrote in message
>> news:0TQwxlNLIHA.360@TK2MSFTNGHUB02.phx.gbl...
>>> Hi Scott,
>>> Thanks for your response.
>>>
>>> Once you have created a CLR stored procedure, just think it as a normal
>>> user defined stored procedure in SQL Server. You can use common ADO.NET
>>> technology to access it. One data row also means a tabular result, so
>>> you
>>> can use SqlDataReader and SqlDataAdapter to retrieve the result.
>>> For example:
>>> Imports System
>>> Imports System.Data
>>> Imports System.Data.SqlClient
>>>
>>> Module Module1
>>> Sub Main()
>>> Dim cn As New
>>> SqlConnection("server=Charles\wow;database=GT;Integrated Security=SSPI")
>>> Dim cmd As New SqlCommand
>>> Dim rd As SqlDataReader
>>> Dim adp As New SqlDataAdapter
>>>
>>> 'Test1: Use SqlDataReader to read the data came from a CLR stored
>>> procedure
>>> cmd.CommandText = "USPTest"
>>> cmd.CommandType = CommandType.StoredProcedure
>>> cmd.Connection = cn
>>> cn.Open()
>>> rd = cmd.ExecuteReader(CommandBehavior.CloseConnection)
>>> Console.WriteLine("Test1: Use SqlDataReader to read the data came
>>> from a CLR stored procedure")
>>> While rd.Read()
>>> Console.WriteLine(String.Format("{0},{1}", rd.GetString(0),
>>> rd.GetString(1)))
>>> End While
>>> rd.Close()
>>>
>>> 'Test2: Use SqlAdapter and DataSet to read the data came from a
>>> CLR
>>> stored procedure
>>> adp.SelectCommand = cmd
>>> Dim table As New DataTable()
>>> adp.Fill(table)
>>> Console.WriteLine("Test2: Use SqlAdapter and DataSet to read the
>>> data came from a CLR stored procedure")
>>> For Each item As DataRow In table.Rows
>>> Console.WriteLine(String.Format("{0},{1}", item(0), item(1)))
>>> Next
>>> Console.Read()
>>> End Sub
>>> End Module
>>>
>>> Hope this helps. If you have any other questions or concerns, please
>>> feel
>>> free to let me know.
>>>
>>>
>>> Best regards,
>>> Charles Wang
>>> Microsoft Online Community Support
>>> =====================================================
>>> When responding to posts, please "Reply to Group" via
>>> your newsreader so that others may learn and benefit
>>> from this issue.
>>> ======================================================
>>> This posting is provided "AS IS" with no warranties, and confers no
>>> rights.
>>> ======================================================
>>>
>>>
>>>
>>>
>>
>>
>