|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Reading a SQLCLR Stored Proc. ResultHow do you consume the result of a SQLCLR stored procedure when it returns a
SQLDataRow? Thanks. This requires use of a (de)serializer. I have a 100+ page chapter in my book
that discusses CLR executables and this aspect in particular. There are a dozen issues that are too complex to detail here. Perhaps someone else would volunteer their time for this... -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant, Dad, Grandpa Microsoft MVP INETA Speaker www.betav.com www.betav.com/blog/billva 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) ----------------------------------------------------------------------------------------------------------------------- Show quote "Scott M." <s-mar@nospam.nospam> wrote in message news:e$z44SWJIHA.484@TK2MSFTNGP06.phx.gbl... > How do you consume the result of a SQLCLR stored procedure when it returns > a SQLDataRow? > > Thanks. > Any ideas where I might find details online? All the searches I've done on
this only discuss how to create the SQLCLR stored proc. and stop at how to consume it. Thanks. Show quote "William Vaughn" <billvaNoSPAM@betav.com> wrote in message news:O$hQhVWJIHA.5764@TK2MSFTNGP06.phx.gbl... > This requires use of a (de)serializer. I have a 100+ page chapter in my > book that discusses CLR executables and this aspect in particular. There > are a dozen issues that are too complex to detail here. Perhaps someone > else would volunteer their time for this... > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant, Dad, Grandpa > Microsoft MVP > INETA Speaker > www.betav.com > www.betav.com/blog/billva > 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." <s-mar@nospam.nospam> wrote in message > news:e$z44SWJIHA.484@TK2MSFTNGP06.phx.gbl... >> How do you consume the result of a SQLCLR stored procedure when it >> returns a SQLDataRow? >> >> Thanks. >> > Hi Scott,
What is your meaning of SQLDataRow here? There is a recordset type named SQLDataRecord in SQL CLR data types. Did you refer to it? It is easy to create/deploy a CLR stored procedure within Visual Studio 2005. After that, you can invoke it just as the SPs created in SQL Server Management Studio (SSMS). For example, I created a SQL CLR project with C#. The following code is the CLR SP I created: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void USPTest() { // Create a record object that represents an individual row, including it's metadata. SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("stringcol1", SqlDbType.NVarChar, 128), new SqlMetaData("stringcol2",SqlDbType.NVarChar,50) }); // Populate the record. record.SetSqlString(0, "Hello World!"); record.SetSqlString(1, "Test"); // Send the record to the client. SqlContext.Pipe.Send(record); } }; Then I built the project, right clicked the project and clicked Deploy, and then I could directly run the SP in SSMS: USE <database name> Go EXEC USPTest The result is as follows: stringcol1 stringcol2 Hello World! Test In addition to directly deploying the CLR procedure in VS2005, if you like, you can manually deploy your CLR procedure via CREATE ASSEMBLY and CREATE PROCEDURE. The following article talks very detailed of creating a CLR procedure and manually deploying a CLR procedure: CLR Stored Procedures http://msdn2.microsoft.com/en-us/library/ms131094.aspx Hope this helps. If you have any other questions or concerns, please feel free to let me know. Have a nice day! 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. ====================================================== Hi Charles,
Your answer just deepens my frustration with this issue (not your fault - I must not be explaining myself clear enough). Yes, it is a SQLDataRecord I'm talking about. Your example below shows how to create the stored proc (which is not what I'm asking about). You show how I can execute it in SSMS, but that is not what I'm asking for either. What I want to know is AFTER the stored proc that returns a SQLDataRow is built and deployed, how do I "consume" or "call" that sp and digest the SQLDataRow that it returns in a VB .NET host application (even a Console app would be sufficient). My frustration with this is that (as you've done here), all the samples I can find just talk about how to create the SP and then they all stop short of showing how to call and consume its return value. I can do this when the SP returns a DataReader, but what about a SQLDataRecord? -Scott Show quote "Charles Wang[MSFT]" <chang***@online.microsoft.com> wrote in message news:xRL2t$BLIHA.5204@TK2MSFTNGHUB02.phx.gbl... > Hi Scott, > What is your meaning of SQLDataRow here? There is a recordset type named > SQLDataRecord in SQL CLR data types. Did you refer to it? > > It is easy to create/deploy a CLR stored procedure within Visual Studio > 2005. After that, you can invoke it just as the SPs created in SQL Server > Management Studio (SSMS). > For example, I created a SQL CLR project with C#. The following code is > the > CLR SP I created: > using System; > using System.Data; > using System.Data.SqlClient; > using System.Data.SqlTypes; > using Microsoft.SqlServer.Server; > > > public partial class StoredProcedures > { > [Microsoft.SqlServer.Server.SqlProcedure] > public static void USPTest() > { > // Create a record object that represents an individual row, > including it's metadata. > SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] { new > SqlMetaData("stringcol1", SqlDbType.NVarChar, 128), new > SqlMetaData("stringcol2",SqlDbType.NVarChar,50) }); > > // Populate the record. > record.SetSqlString(0, "Hello World!"); > record.SetSqlString(1, "Test"); > > // Send the record to the client. > SqlContext.Pipe.Send(record); > > > } > }; > > Then I built the project, right clicked the project and clicked Deploy, > and > then I could directly run the SP in SSMS: > USE <database name> > Go > EXEC USPTest > > The result is as follows: > stringcol1 stringcol2 > Hello World! Test > > In addition to directly deploying the CLR procedure in VS2005, if you > like, > you can manually deploy your CLR procedure via CREATE ASSEMBLY and CREATE > PROCEDURE. The following article talks very detailed of creating a CLR > procedure and manually deploying a CLR procedure: > CLR Stored Procedures > http://msdn2.microsoft.com/en-us/library/ms131094.aspx > > Hope this helps. If you have any other questions or concerns, please feel > free to let me know. Have a nice day! > > 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. > ====================================================== > > > > > > > > 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. ====================================================== 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. Show quote "Charles Wang[MSFT]" <chang***@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. > ====================================================== > > > > 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) ----------------------------------------------------------------------------------------------------------------------- Show quote "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]" <chang***@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. >> ====================================================== >> >> >> >> > > 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. Show quote "William (Bill) Vaughn" <billvaRemoveT***@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]" <chang***@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. >>> ====================================================== >>> >>> >>> >>> >> >> > Hi Scott,
Thanks for your response. However there is no ExecuteRow method in SqlCommand. Could you please let me know where you find it and how you use it? 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. ====================================================== Hmmm.
From: "Professional VB 2005 with .NET 3.0", WROX 2007 (ISBN: 978-0-470-12470-3) Page 346, second paragraph, third sentence: "In ADO.NET 2.0, the ExecuteRow method has been added, which returns a single row of data in the form of a SQLRecord object." This just keeps getting better and better. -Scott Show quote "Charles Wang[MSFT]" <chang***@online.microsoft.com> wrote in message news:QMytxg$LIHA.7908@TK2MSFTNGHUB02.phx.gbl... > Hi Scott, > Thanks for your response. > > However there is no ExecuteRow method in SqlCommand. Could you please let > me know where you find it and how you use it? > > 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. > ====================================================== > > Hi Scott,
That is interesting. I guess that there might be some mistake. I also include other engineers for discussion, and we believe that in ADO.NET 1.0 and 2.0 RTM there is no ExecuteRow method for SqlCommand. This seems to be an old function in Beta edition since I found the following feedback from Microsoft Connect site: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?Feedbac kID=111211 It is easy to confirm this if you write a simple project in Visual 2005 IDE. This method is also not included in MSDN Help documents. Anyway could you please let me know why you like using this method rather than ExecuteReader,ExecuteScalar and SqlDataAdapter.Fill methods? 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. ====================================================== Hi Charles,
I haven't tried this method myself and I do believe that you are correct that this is a mistake in my book. I also have not yet had a chance to try consuming a SQLDataRecord yet, but if that is the object type being returned then how would ExecuteReader and ExecuteScalar help? -Scott Show quote "Charles Wang[MSFT]" <chang***@online.microsoft.com> wrote in message news:iGctRzAMIHA.7908@TK2MSFTNGHUB02.phx.gbl... > Hi Scott, > That is interesting. I guess that there might be some mistake. I also > include other engineers for discussion, and we believe that in ADO.NET 1.0 > and 2.0 RTM there is no ExecuteRow method for SqlCommand. This seems to be > an old function in Beta edition since I found the following feedback from > Microsoft Connect site: > http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?Feedbac > kID=111211 > > It is easy to confirm this if you write a simple project in Visual 2005 > IDE. This method is also not included in MSDN Help documents. > > Anyway could you please let me know why you like using this method rather > than ExecuteReader,ExecuteScalar and SqlDataAdapter.Fill methods? > > 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. > ====================================================== > > > > > > Hi Scott,
Thanks for your response. So you wanted to directly get the result with SQLDataRecord data type, right? Unfortunately there is no way to do this currently, however why would you like to directly use it as the return data type? DataSet/DataTable can handle tabular result very well. You can also use ExecuteReader to get each field value. ExecuteScalar can only get the first row first column field value, but whether to use it depends on your real requirement. Please feel free to let me know if you have any other questions or concerns. Have a nice day! 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. ====================================================== Hi Scott,
Would you mind letting me know the result of the suggestions? If you need further assistance, feel free to let me know. I will be more than happy to be of assistance. 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. ======================================================  |
|||||||||||||||||||||||
Other interesting topics