Help! Stored Procs don't show as Data Sources In Data Source Window

[Scott M.]

a. I have some SQL CLR stored procedures and a connection to that SQL
database in my Server Explorer.

b. I use the Add Data Source Wizard and select my stored procedures as the
items I'd like to add.

c. When done, I get a DataSet with the DataSet designer showing one
TableAdapter containing the (3) stored procedures I had selected.

d. If I right click on any of these stored procedures and choose to
"Preview", I am able to do so and get good results.

So far so good, right?

Even though the DataSet shows a TableAdapter containing my stored
procedures, nothing shows up in the Data Sources Window!

So, how am I supposed to use the results of these stored procedures as a
Data Source for bound controls on a Windows Form (or Web Form assuming that
the Data Sources Window and Wizard operate the same in those projects).?

By the way, I have tried this on different machines, several times (starting
with brand new projects each time, just to be sure I didn't have any
"baggage" from a previous attempt) and get the same results each time.

Help!

 

[Charles Wang[MSFT]]

Hi Scott,
Thanks for your response.

I understand that you could not show the result of your CLR SP in your
datagridview. You used Add Data Source Wizard to select your CLR SPs as the
items.
If I have misunderstood, please let me know.

I reproduced your issue at my side. This issue did not occur if I selected
a static table as its datasource. Currently I think that it might be a by
design issue and I will try to consult the product team for the
confirmation. Anyway per my test, now you can workaround this issue by
setting the datasource NONE and then write ADO.NET code to bind the data
source.
For example:
//CLR stored procedure
================================================================
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 usp_getUsers()
{
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT * From Users",
connection);
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
}
}
};
================================================================

//Bind data source
===============================================================
SqlConnection cn = new
SqlConnection("server=Charles\\wow;database=GT;Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = @"dbo.usp_getUsers";
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
DataTable table = new DataTable();
try
{
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmd;
adp.Fill(this.gTDataSet);
this.dataGridView1.DataSource = this.gTDataSet.Tables[0];
}
finally
{
cn.Close();
}
=============================================================

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure 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.
======================================================

 

[Scott M.]

Hi Charles,

Thanks for your reply.

You are correct that if I select a static table as my DataSource, it shows
up in the Data Source window and all is well, but when I choose CLR SP's
(although they show up in the DataSet Designer) they do not show up in the
DataSource window.

I would really appreciate it if you would get back to me once you've heard
from the product team on this as it does not seem to be very usefull to show
the SP's in a TableAdapter in the DataSet Design window, but not be able to
do anything with them!

Basically, you're saying that to invoke SP's (at least CLR SP's), we have to
do it the "old fasioned" way and code it?

-Scott

Hi Scott,
Thanks for your response.

I understand that you could not show the result of your CLR SP in your
datagridview. You used Add Data Source Wizard to select your CLR SPs as
the
items.
If I have misunderstood, please let me know.

I reproduced your issue at my side. This issue did not occur if I selected
a static table as its datasource. Currently I think that it might be a by
design issue and I will try to consult the product team for the
confirmation. Anyway per my test, now you can workaround this issue by
setting the datasource NONE and then write ADO.NET code to bind the data
source.
For example:
//CLR stored procedure
================================================================
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 usp_getUsers()
{
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT * From Users",
connection);
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
}
}
};
================================================================

//Bind data source
===============================================================
SqlConnection cn = new
SqlConnection("server=Charles\\wow;database=GT;Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = @"dbo.usp_getUsers";
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
DataTable table = new DataTable();
try
{
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmd;
adp.Fill(this.gTDataSet);
this.dataGridView1.DataSource = this.gTDataSet.Tables[0];
}
finally
{
cn.Close();
}
=============================================================

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure 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.
======================================================

 

[Charles Wang[MSFT]]

Hi Scott,
Thanks for your feedback.

Maybe "common fashion" is more appropriate here.

Currently I recommend that you use it. Per my analysis, this may be related
to the implementation mechanism of Datagridview. It provides a default data
binding function that may use CommandBuilder. In that case, it cannot
automatically generate INSERT/UPDATE/DELETE statements from the stored
procedure. In other words, the update/insert/delete function could not be
done on the control. So the default behavior of this control does not work
and you need to manually implement all the functions you want. Think about
that even if it displays the data by default, you still need to write some
codes to implement update/delete/insert functions.

Anyway that is just my assumption and I need to ask the product team to
confirm this. When I get the response from the product team, I will both
post it here and send an email notification to you. If you have any other
questions or concerns, please also 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.
======================================================