S
Scott M.
How do you consume the result of a SQLCLR stored procedure when it returns a
SQLDataRow?
Thanks.
SQLDataRow?
Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
W
William Vaughn
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)
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)
S
Scott M.
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.
this only discuss how to create the SQLCLR stored proc. and stop at how to
consume it.
Thanks.
C
Charles Wang[MSFT]
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.
======================================================
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.
======================================================
S
Scott M.
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
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
Charles Wang said: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.
======================================================
C
Charles Wang[MSFT]
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.
======================================================
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.
======================================================
S
Scott M.
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.
method, which returns a SQLDataRecord. I think this is what I've been
looking for.
W
William \(Bill\) Vaughn
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)
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)
S
Scott M.
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.
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.
C
Charles Wang[MSFT]
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.
======================================================
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.
======================================================
S
Scott M.
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
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
C
Charles Wang[MSFT]
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.
======================================================
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.
======================================================
S
Scott M.
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
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
C
Charles Wang[MSFT]
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.
======================================================
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.
======================================================
C
Charles Wang[MSFT]
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.
======================================================
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.
======================================================
S
Scott M.
Thanks Charles. I haven't had a chance to try them yet. I'll get back to
you if I have further questions.
you if I have further questions.