Stored Procedure and SQL String????

[Guest]

Can someone please tell me if it is possible to get the SQL statement from a
stored procedure in ASP.NET?

Dim Myconn As New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

I would like to beable to get the SQL statement of "MenuItems" as a string
and use it in another part of my code... How do I do this???


EG:
Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")


...CODE...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then

Dim Myconn As New
SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

Myconn.Open()

Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")

'OK
Dim oDataReader As SqlDataReader = cmd.ExecuteReader()
Dim oMenuItem As skmMenu.MenuItem = Nothing
Dim iOrdinal As Integer = -1
Dim myID As Long = 0
While oDataReader.Read
oMenuItem = New skmMenu.MenuItem
iOrdinal = oDataReader.GetOrdinal("Text")
oMenuItem.Text = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ToolTip")
oMenuItem.ToolTip = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("Url")
oMenuItem.Url = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ID")
myID = Long.Parse(oDataReader.GetValue(iOrdinal).ToString)
iOrdinal = oDataReader.GetOrdinal("SubMenuItemsCount")
If oDataReader.GetInt32(iOrdinal) > 0 Then
LoadSubMenu(oMenuItem, baseSQL, myID)
End If
mnuMain.Items.Add(oMenuItem)
End While
mnuMain.CssClass = "menustyle"
mnuMain.HighlightTopMenu = True
mnuMain.Opacity = "100"
mnuMain.zIndex = 1000
mnuMain.Cursor = skmMenu.MouseCursor.Pointer

oDataReader.Close()
Myconn.Close()
End If
End Sub

 

[Guest]

Instead of replacing use this:

cmd.Parameters.Add("@ParentID", SqlDbType.Integer);
cmd.Parameters["@ParentID"].Value = 0;

 

[Eliyahu Goldin]

No, there is no such a thing. You can use SQL Query Analyzer do modify the
sp to accommodate more parameters if you wish and can.

Eliyahu

 

[Guest]

Kostadin,

Thank you for your response! The bigger problem is how do I get my SQL
string from the stored procedure into my function?

LoadSubMenu(oMenuItem, baseSQL, myID)

BaseSQL should be the SQL string in the stored procedure!

For example BaseSQL should equal

SELECT (CASE WHEN ImgLeft IS NULL THEN '' ELSE '<img src="' + ImgLeft + '"
/> ' END) + [Text] +
(CASE WHEN ImgRight IS NULL THEN '' ELSE ' <img src="' + ImgRight + '" />'
END) AS [Text],
ISNULL(ToolTip, '') AS ToolTip,
ISNULL(Url, '') AS Url,
[ID],
(SELECT COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS
SubMenuItemsCount
FROM APP_Menu A
WHERE(Display = 1)
AND ParentId = @ParentID
ORDER BY ParentID, DisplayOrder

Do you know of a way to put this sql string into the function without having
to put it in my actual code???

Thanks

Instead of replacing use this:

cmd.Parameters.Add("@ParentID", SqlDbType.Integer);
cmd.Parameters["@ParentID"].Value = 0;

Can someone please tell me if it is possible to get the SQL statement from a
stored procedure in ASP.NET?

Dim Myconn As New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

I would like to beable to get the SQL statement of "MenuItems" as a string
and use it in another part of my code... How do I do this???


EG:
Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")


..CODE...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then

Dim Myconn As New
SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

Myconn.Open()

Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")

'OK
Dim oDataReader As SqlDataReader = cmd.ExecuteReader()
Dim oMenuItem As skmMenu.MenuItem = Nothing
Dim iOrdinal As Integer = -1
Dim myID As Long = 0
While oDataReader.Read
oMenuItem = New skmMenu.MenuItem
iOrdinal = oDataReader.GetOrdinal("Text")
oMenuItem.Text = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ToolTip")
oMenuItem.ToolTip = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("Url")
oMenuItem.Url = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ID")
myID = Long.Parse(oDataReader.GetValue(iOrdinal).ToString)
iOrdinal = oDataReader.GetOrdinal("SubMenuItemsCount")
If oDataReader.GetInt32(iOrdinal) > 0 Then
LoadSubMenu(oMenuItem, baseSQL, myID)
End If
mnuMain.Items.Add(oMenuItem)
End While
mnuMain.CssClass = "menustyle"
mnuMain.HighlightTopMenu = True
mnuMain.Opacity = "100"
mnuMain.zIndex = 1000
mnuMain.Cursor = skmMenu.MouseCursor.Pointer

oDataReader.Close()
Myconn.Close()
End If
End Sub

 

[IPGrunt]

Kostadin,

Thank you for your response! The bigger problem is how do I get my SQL
string from the stored procedure into my function?

LoadSubMenu(oMenuItem, baseSQL, myID)

BaseSQL should be the SQL string in the stored procedure!

For example BaseSQL should equal

SELECT (CASE WHEN ImgLeft IS NULL THEN '' ELSE '<img src="' + ImgLeft + '"
/> ' END) + [Text] +
(CASE WHEN ImgRight IS NULL THEN '' ELSE ' <img src="' + ImgRight + '" />'
END) AS [Text],
ISNULL(ToolTip, '') AS ToolTip,
ISNULL(Url, '') AS Url,
[ID],
(SELECT COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS
SubMenuItemsCount
FROM APP_Menu A
WHERE(Display = 1)
AND ParentId = @ParentID
ORDER BY ParentID, DisplayOrder

Do you know of a way to put this sql string into the function without having
to put it in my actual code???

Thanks

Instead of replacing use this:

cmd.Parameters.Add("@ParentID", SqlDbType.Integer);
cmd.Parameters["@ParentID"].Value = 0;

Can someone please tell me if it is possible to get the SQL statement from a
stored procedure in ASP.NET?

Dim Myconn As New SqlConnection (ConfigurationSettings.AppSettings("strConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

I would like to beable to get the SQL statement of "MenuItems" as a string
and use it in another part of my code... How do I do this???


EG:
Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")


..CODE...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then

Dim Myconn As New
SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

Myconn.Open()

Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")

'OK
Dim oDataReader As SqlDataReader = cmd.ExecuteReader ()
Dim oMenuItem As skmMenu.MenuItem = Nothing
Dim iOrdinal As Integer = -1
Dim myID As Long = 0
While oDataReader.Read
oMenuItem = New skmMenu.MenuItem
iOrdinal = oDataReader.GetOrdinal("Text")
oMenuItem.Text = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ToolTip")
oMenuItem.ToolTip = oDataReader.GetString (iOrdinal)
iOrdinal = oDataReader.GetOrdinal("Url")
oMenuItem.Url = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ID")
myID = Long.Parse(oDataReader.GetValue (iOrdinal).ToString)
iOrdinal = oDataReader.GetOrdinal ("SubMenuItemsCount")
If oDataReader.GetInt32(iOrdinal) > 0 Then
LoadSubMenu(oMenuItem, baseSQL, myID)
End If
mnuMain.Items.Add(oMenuItem)
End While
mnuMain.CssClass = "menustyle"
mnuMain.HighlightTopMenu = True
mnuMain.Opacity = "100"
mnuMain.zIndex = 1000
mnuMain.Cursor = skmMenu.MouseCursor.Pointer

oDataReader.Close()
Myconn.Close()
End If
End Sub

The system stored procedure SP_HELPTEXT will return the contents of a
sproc.

I haven't called a system sproc from ADO.NET, but I would imagine it
is no different than calling a user sproc.

-- ipgrunt

 

[Guest]

Thanks I will give it a go and let you know!