Using two stored procs instead of two queries?

[Roy]

Hey all,
Here's a small VB codeblock that connects to a database and uses 2 SQL
queries then forms a relation for a master/detail view on the aspx side:

Private Sub Binddata(ByVal name As String)

Dim myconn As New
SqlConnection("server=localhost;uid=ser;pwd=none;database=et")

Dim mycom As New SqlCommand("select * from tbl1;select * from tbl2",
myconn)

myconn.Open()
Dim newadp As New SqlDataAdapter(mycom)
Dim newds As New DataSet
newadp.Fill(newds)
newds.Relations.Add("nested", newds.Tables(0).Columns("bookingnum"),
newds.Tables(1).Columns("bookingnum"))

main_list.DataSource = newds.Tables(0).DefaultView
myconn.Close()
main_list.DataBind()

End Sub


Now, my question to the group mind is how can I substitute 2 Stored
Procedures in place of those two queries above? This doesn't work,
though I would think it would:

Private Sub Binddata(ByVal name As String)
Dim myconn As New
SqlConnection("server=localhost;uid=user;pwd=none;database=et")

Dim mycom As New
SqlCommand("et.dbo.user_data_top;et.dbo.user_data_nested", myconn)

With mycom
..CommandType = CommandType.StoredProcedure
..Parameters.Add(New SqlParameter("@name", SqlDbType.NVarChar, 12)).Value
= name
End With

myconn.Open()
Dim newadp As New SqlDataAdapter(mycom)
Dim newds As New DataSet
newadp.Fill(newds)
newds.Relations.Add("nested", newds.Tables(0).Columns("bookingnum"),
newds.Tables(1).Columns("bookingnum"))
main_list.DataSource = newds.Tables(0).DefaultView

myconn.Close()
main_list.DataBind()
End Sub

 

[Marina Levit [MVP]]

I imagine you would have to keep the command type as just text, and do
something like 'exec mysproc1; exec mysproc2' to actually run the stored
procedures.

 

[Guest]

Hello Roy,

I might be tempted to put both queries into one stored procedure, fill the
dataset, and then establish the relationship (putting both queries into the
stored proc would cut down on a round trip). Something like this (sorry it's
C#, but it's what I know):

Store Proc:
CREATE PROCEDURE dbo.CombinedQuery
(
@Parm1 int;
@Parm2 int;
)
AS
SET NOCOUNT ON
SELECT * FROM Table1 WHERE col > @Parm1
SELECT * FROM Table2 WHERE col < @Parm2
RETURN
=-=-=-=-=-=-=-
Then fill the dataset and add the relationship:
public DataSet SelectCombinedQuery()
{
ConnectionStringSettings connectionStringSettings =
ConfigurationManager.ConnectionStrings["MyConnectionString"];

// Create Instance of Connection and Command Object
SqlConnection sqlConn = new
SqlConnection(connectionStringSettings.ConnectionString);
SqlCommand sqlCommand = new SqlCommand("CombinedQuerry", sqlConn);

sqlCommand.Parameters.AddWithValue("@Parm1", parm1);
sqlCommand.Parameters.AddWithValue("@Parm2", parm2);

// Mark the Command as a SPROC
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlConn.Open();

SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
DataSet ds = new DataSet();

da.Fill(ds);

ds.Tables[0].TableName = "Table1";
ds.Tables[1].TableName = "Table2";

ds.Relations.Add("Table1Table2", ds.Tables["Table1"].Columns["ID1"],
ds.Tables["Table2"].Columns["ID1"]);
return ds;
}

--
The stored procedure will return 2 rowsets in the two tables in the dataset
when you call fill on the dataadapter. On return from the stored proc call,
you name the tables and then add the relationship between them. I know I've
changed your question a bit but hope this helps.

enjoy - brians
http://www.limbertech.com